Project: sales dashboard for a coffee chain

Goal: act as the analyst for Brew & Bean, which now has three outlets in Bengaluru: Indiranagar, Koramangala and HSR Layout. The owner asks: "How are we doing, which store and which items matter, and what should I do next?"

The finished result: (1) a sales.csv data file, (2) a short script that checks the data, (3) SQL queries that answer the owner's questions, (4) a one-page dashboard with KPI cards, a monthly trend, store comparison, category split, top items and filters, and (5) a half-page insight summary a non-technical owner can act on. Together these make a strong portfolio piece.

Prerequisites: the earlier lessons in this track (SQL for analysts, Cleaning messy data, Business metrics and KPIs, Dashboards with Power BI and Looker Studio, Data storytelling). You need Python 3 with pandas (pip install pandas; Google Colab has it) and either Power BI Desktop (Windows) or a Google account for Looker Studio.

Note: the data is made up for learning. It does not describe any real business, and the patterns in it come from the settings in the generator script.

Real analysts rarely get a perfect file, but a clean, known dataset lets you focus on the analysis. The script below creates one row per item in each order for the first six months of 2026 (181 days), for three stores, using the menu from the Brew & Bean project. It is seeded, so you get the same file each time. Weekend days get more orders, and Indiranagar is busiest, Koramangala next, HSR Layout smallest.

import csv
import random
from datetime import date, timedelta

random.seed(42)

MENU = [
    ("espresso", "Espresso", "coffee", 120),
    ("cappuccino", "Cappuccino", "coffee", 180),
    ("cold-brew", "Cold Brew", "coffee", 220),
    ("filter-coffee", "Filter Coffee", "coffee", 80),
    ("masala-chai", "Masala Chai", "tea", 90),
    ("croissant", "Butter Croissant", "bakery", 150),
    ("banana-bread", "Banana Bread", "bakery", 130),
    ("veg-sandwich", "Veg Grilled Sandwich", "food", 160),
]
STORES = {"Indiranagar": 1.0, "Koramangala": 0.85, "HSR Layout": 0.6}
WEIGHTS = [10, 18, 9, 16, 15, 9, 6, 8]

start = date(2026, 1, 1)
rows = []
order_id = 1000
for d in range(181):
    day = start + timedelta(days=d)
    weekend = day.weekday() >= 5
    for store, factor in STORES.items():
        orders = int(random.randint(35, 50) * factor * (1.25 if weekend else 1.0))
        for _ in range(orders):
            order_id += 1
            for _ in range(random.randint(1, 3)):
                item = random.choices(MENU, weights=WEIGHTS)[0]
                qty = random.choice([1, 1, 1, 2])
                rows.append([order_id, day.isoformat(), store, item[0], item[1],
                             item[2], item[3], qty, item[3] * qty])

with open("sales.csv", "w", newline="", encoding="utf-8") as f:
    w = csv.writer(f)
    w.writerow(["order_id", "order_date", "store", "item_id", "item_name",
                "category", "unit_price", "qty", "revenue"])
    w.writerows(rows)
print(len(rows), "rows")

Run python generate_sales.py. It writes sales.csv (about 2.8 MB) in the same folder.

If your row count differs, you probably edited the script or changed the seed. That is fine, but your numbers later will differ from the ones shown in this lesson. Open sales.csv in a spreadsheet and look at the first few rows so you know what you are working with.

I have a CSV called sales.csv with columns order_id, order_date, store, item_id, item_name, category, unit_price, qty, revenue. One row is one item in an order. Write pandas code that checks: row count, missing values, duplicate rows, whether revenue equals unit_price * qty, the date range, and the distinct values of store and category. Print the results clearly. Do not state any numbers, I will run it.

What to expect: Compare with the reference script below. Check that the AI used distinct order_id for order counts, not row counts.

import pandas as pd

df = pd.read_csv("sales.csv", parse_dates=["order_date"])

print("Rows:", len(df))
print("Missing values per column:")
print(df.isna().sum())
print("Duplicate rows:", df.duplicated().sum())
print("Revenue != unit_price * qty:", (df["revenue"] != df["unit_price"] * df["qty"]).sum())
print("Date range:", df["order_date"].min().date(), "to", df["order_date"].max().date())
print("Stores:", sorted(df["store"].unique()))
print("Categories:", sorted(df["category"].unique()))
print("Distinct orders:", df["order_id"].nunique())

Requires pandas. Duplicate rows may be greater than zero here: the same item can appear twice in one order, which is legitimate in this data.

The owner asks six things: overall KPIs, revenue by month, performance by store, category mix, top items, and weekday versus weekend. You will load the CSV into SQLite, a database built into Python, and write one query for each. SQLite keeps this free and local; the same SQL runs with small changes in PostgreSQL or MySQL.

import sqlite3

import pandas as pd

df = pd.read_csv("sales.csv")
conn = sqlite3.connect("brew.db")
df.to_sql("sales", conn, if_exists="replace", index=False)


def show(title, sql):
    print(title)
    print(pd.read_sql_query(sql, conn).to_string(index=False))
    print()


show("KPIs", """
SELECT SUM(revenue) AS total_revenue,
       COUNT(DISTINCT order_id) AS orders,
       ROUND(1.0 * SUM(revenue) / COUNT(DISTINCT order_id), 2) AS avg_order_value
FROM sales
""")

show("Revenue by month", """
SELECT strftime('%Y-%m', order_date) AS month,
       SUM(revenue) AS revenue
FROM sales
GROUP BY month
ORDER BY month
""")

show("By store", """
SELECT store,
       SUM(revenue) AS revenue,
       COUNT(DISTINCT order_id) AS orders,
       ROUND(1.0 * SUM(revenue) / COUNT(DISTINCT order_id), 2) AS avg_order_value
FROM sales
GROUP BY store
ORDER BY revenue DESC
""")

show("By category", """
SELECT category,
       SUM(revenue) AS revenue,
       ROUND(100.0 * SUM(revenue) / (SELECT SUM(revenue) FROM sales), 1) AS pct_of_revenue
FROM sales
GROUP BY category
ORDER BY revenue DESC
""")

show("Top items", """
SELECT item_name, SUM(qty) AS units, SUM(revenue) AS revenue
FROM sales
GROUP BY item_name
ORDER BY revenue DESC
LIMIT 5
""")

show("Weekday vs weekend, per day", """
SELECT CASE WHEN strftime('%w', order_date) IN ('0', '6') THEN 'Weekend' ELSE 'Weekday' END AS day_type,
       COUNT(DISTINCT order_date) AS days,
       ROUND(1.0 * SUM(revenue) / COUNT(DISTINCT order_date), 0) AS revenue_per_day
FROM sales
GROUP BY day_type
""")

conn.close()

Run python analysis.py. It creates brew.db and prints six result tables. In SQLite, strftime('%w', date) gives the weekday number, 0 for Sunday and 6 for Saturday.

Read these results as an analyst, before you build anything:

- February is lower than other months, but it has only 28 days. A fairer comparison is revenue per day. - Average order value is almost the same in all three stores (about ₹342 to ₹344). So HSR Layout earns less because it has fewer orders, not because customers spend less per order. - Coffee brings in about 62% of revenue. Cold Brew sells about half as many units as Cappuccino but earns a lot per unit. - A weekend day earns more than a weekday: roughly ₹44,000 against ₹36,000 across all stores.

These observations will become your dashboard's story.

Choose Power BI Desktop or Looker Studio. Follow the steps in *Dashboards with Power BI and Looker Studio*, using sales.csv as the source (for Looker Studio, upload the CSV with the File Upload connector or put it in Google Sheets; the file has about 40,000 rows, which Sheets can handle). Use the same three measures: total revenue, distinct orders, and average order value as revenue divided by orders.

I'm building a one-page dashboard for the owner of a 3-outlet coffee chain, who looks at it each Monday. Metrics: revenue, orders, average order value. Dimensions: month, store, category, item. Here is my planned layout: [describe where each card and chart goes]. Tell me what to move, remove or retitle so the owner can see within 10 seconds how the business is doing and where to look. Do not add more charts.

What to expect: Take only suggestions that make the page simpler. Reject any that add clutter.

A dashboard shows what happened. The owner also needs to know what it means and what to do. Use the answer, evidence, recommendation, caveat structure from *Data storytelling and presenting insights*. Put the summary at the top of your project README or in a text box beside the dashboard.

I am an analyst writing for the owner of Brew & Bean, a 3-outlet coffee shop in Bengaluru. Using ONLY the facts below, write a 120-word summary with a headline, three evidence bullets, one recommendation and two caveats. Do not add numbers or causes that I did not give.

Facts:
- Total revenue Jan to Jun 2026 was ₹68.9 lakh from 20,089 orders; average order value about ₹343.
- Average order value is about the same in all three stores, so HSR Layout's lower revenue comes from fewer orders (4,917 against 8,211 in Indiranagar).
- Coffee is about 62% of revenue. Cappuccino is the top item by revenue.
- A weekend day earns about ₹44,000 versus about ₹36,000 for a weekday.
- This is sample data covering six months.

What to expect: Check that every number matches your outputs, and rewrite any sentence that claims a cause. A good recommendation could be to try a limited-time offer at HSR Layout aimed at getting more orders, measured by orders per day, not average order value. Remember that this data cannot tell you why HSR Layout gets fewer orders.

Here is one reference version. Compare it with yours.

Headline: Revenue is steady at about ₹10 to 12 lakh a month, and HSR Layout's gap is about the number of orders, not the size of each bill.

Evidence: Average order value is about ₹343 at every store. HSR Layout had 4,917 orders against 8,211 at Indiranagar. Coffee is 62% of revenue; weekend days earn about 24% more than weekdays.

Recommendation: Run a two-week offer at HSR Layout designed to bring in more orders (for example a pickup combo), and compare orders per day with the previous two weeks.

Caveats: Six months of sample data; we cannot yet say why HSR Layout has fewer orders (location, footfall and opening hours were not measured); a before-and-after comparison is weaker than a test (see *A/B testing and experiments*).

Before you publish, use the routine from *Using AI as an analyst, and checking its work*: recompute totals a second way, look for double counting, and ask an AI tool to act as a sceptical reviewer of your summary. Do not share the AI's text without checking each number.