Lesson 2 of 11 · 22 min
Defining KPIs precisely
By the end of this lesson you can write a precise KPI definition (numerator, denominator, inclusions, exclusions, grain and period) and implement it so that the numerator and the denominator are computed over the same population.
A KPI (key performance indicator) is a number the business has agreed to watch. Most KPIs are ratios: something that happened divided by the opportunities for it to happen. That makes them fragile. Change the numerator’s filter without changing the denominator’s and the ratio silently means something else.
A KPI definition worth publishing answers six questions:
| part | example: return rate |
|---|---|
| Numerator | net amount of order lines later returned |
| Denominator | net amount of all order lines |
| Inclusions / exclusions | all channels; guest checkouts included |
| Grain | one value per category (or per month, per channel …) |
| Period | by order date, calendar year |
| Unit and rounding | percent, 2 decimals |
Notice the choice hidden in the numerator: value returned, not number of lines returned. A returned TV and a returned phone case are one line each, but very different amounts of money. Neither choice is wrong; not writing it down is.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons