Module 8: Analyses employers ask for · Lesson 8.2 · about 20 min
A KPI summary table
Build the KPI table a manager asks for: revenue, orders, customers, average order value and units per order for each store and month, with this year against last year and a total row.
Module 8 · about 20 minutes · uses Modules 4 and 7. You’ll be able to produce a correct KPI summary from order lines.
In everyday terms: a class average: you cannot average the averages of a class of 10 and a class of 30 and call it the school average; the bigger class must count more.
1. The KPIs from order lines
The table has one row per order line. An order with three products has three lines, so orders must be nunique on order_id, not a count of rows.
2. The total row, done right
The All stores row is computed from all the rows again. Its customer count is smaller than the sum of the stores (people shop in more than one store), and its AOV is not the average of the store AOVs.
3. This year against last year
Only January to June exists for 2026, so both years are cut to the same months: like with like (Lesson 7.4).
- Revenue
- Sum of line revenue.
- Orders
- order_id.nunique(), not the number of lines.
- Average order value
- Revenue ÷ orders.
- Total row
- Recompute from summed revenue and summed orders.
- Physical meaning
- A ratio KPI (AOV, conversion) is one total divided by another. The overall ratio is total ÷ total, which weights each store by its size; the mean of the store ratios gives a small store the same say as a big one.
“AOV is the mean of the revenue column.” That is the average line value. An order with three lines would count three times. Divide revenue by the number of distinct orders.
Watch it happen, one step at a time:
| order_id | store | revenue |
|---|---|---|
| 1 | S01 | 20 |
| 1 | S01 | 10 |
| 2 | S01 | 30 |
| 3 | S02 | 60 |
agg(revenue=('revenue', 'sum'), orders=('order_id', 'nunique')) → S01 60, 2; S02 60, 1.| store | revenue | orders | AOV |
|---|---|---|---|
| S01 | 60 | 2 | 30 |
| S02 | 60 | 1 | 60 |
| Total | 120 | 3 | 40 |
4. Your turn
How it works
Principle: KPIs come in three kinds. Additive ones (revenue, units) can be summed over any split. Semi-additive or distinct ones (customers, stock levels) cannot be summed across some splits. Ratios (AOV, conversion, margin %) must be rebuilt from their parts at every level:
This is exactly why DAX uses measures instead of summed columns (Power BI course), and why pivot_table(margins=True) can mislead for ratios.
5. Practice
- Gross margin % per category (needs products.csv for cost).
- Why is “average revenue per line” not AOV?
- Which is the better headline: AOV or revenue per customer? It depends on what question?
Answers
- Merge cost in, then
margin = (revenue - qty * cost); margin % = sum of margin / sum of revenue per category. - An order has 1.65 lines on average here, so per-line revenue is smaller and changes when basket size changes.
- AOV answers “how big is a basket?”; revenue per customer answers “how much is a customer worth over the period?”, which also grows with visit frequency.
Try it on your own data: CSV profiler gives a quick first look at any table before you build KPIs.
New words: KPI, AOV, additive, distinct count, total row, like for like.
- Count orders with nunique, not len.
- AOV = revenue ÷ orders.
- Total row: divide totals; never average the averages.
- Compare this year with last year over the same dates.
Check yourself
Sources
- Kimball, R. and Ross, M. (2013), The Data Warehouse Toolkit, 3rd edition, Wiley (grain and additive facts)
- McKinney, W. (2022), Python for Data Analysis, 3rd edition, O'Reilly (open access) https://wesmckinney.com/book/
Your ticks stay in this browser. Save them as a file on the course home.
Updated 2026-11-22. All datasets are made up. Python runs in your browser (the first run downloads it once, about 20 MB); nothing you type is sent anywhere.