All lessons

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.

Key points, meanings and formulas on one page: choose "Save as PDF" in the print box.

Module 8 · about 20 minutes · uses Modules 4 and 7. You’ll be able to produce a correct KPI summary from order lines.

KPIs come from order lines, but some count orders and customers, not lines. Compute each from the right grain, and compute the total row from totals, never by averaging averages.

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

Pythonorders.csv

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

Pythonorders.csv

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

Pythonorders.csv

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 lines.
order_idstorerevenue
1S0120
1S0110
2S0130
3S0260
Per store. agg(revenue=('revenue', 'sum'), orders=('order_id', 'nunique')) → S01 60, 2; S02 60, 1.
AOV. S01 60 ÷ 2 = 30; S02 60 ÷ 1 = 60.
Total, done wrong. Mean of 30 and 60 = 45.
Total, done right. 120 ÷ 3 orders = 40.
storerevenueordersAOV
S0160230
S0260160
Total120340

4. Your turn

Your turnorders.csv
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:

AOVall=∑srevenues∑sorderss≠1S∑sAOVs

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

  1. Gross margin % per category (needs products.csv for cost).
  2. Why is “average revenue per line” not AOV?
  3. Which is the better headline: AOV or revenue per customer? It depends on what question?
Answers
  1. Merge cost in, then margin = (revenue - qty * cost); margin % = sum of margin / sum of revenue per category.
  2. An order has 1.65 lines on average here, so per-line revenue is smaller and changes when basket size changes.
  3. 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

1How do you compute average order value (AOV) correctly?
2Why must ratios in a total row be recalculated, not summed or averaged?
3Which KPI is NOT additive across stores?

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.