Population
Which customers, orders or events are eligible?
Foundations · Module 04
Business Metrics.
A metric is not just a formula. It is a business agreement about population, timing, grain, exclusions and meaning.
Core idea
If two analysts can calculate the “same” KPI and get two defensible answers, the problem is usually definition, not SQL.
01
Context
Everyone says revenue. Nobody means the same thing.
Growth reports $1.42M. Operations reports $1.36M. Finance reports $1.18M. All three dashboards say “Revenue.”
None of those numbers has to be wrong. Growth may be showing order value before refunds, Operations may be showing completed-order value, and Finance may be showing recognized net revenue.
The failure is that the metric name hides the business logic.
02
Metric anatomy
Define the parts before defining the KPI.
Numerator
What exactly are you counting or summing?
Denominator
What is the comparison base for a rate?
Time
Which timestamp assigns activity to a period?
Grain
At what level is the metric reported and compared?
Exceptions
What happens to refunds, fraud, tests and late data?
Rule
Never accept a metric name as a definition. Ask what population, event and denominator produce it.
LIVE
Metric denominator · interactive visual
Same numerator. Different denominator. Different story.
Cancelled orders 20 numerator stays fixed
÷
Eligible placed orders All status-event rows 100 260 business population wrong grain / wrong population
=
Cancellation rate 20.0% 7.7%
✓ Metric contract preserved × Plausible number, wrong denominator
The denominator is the population that could have produced the outcome.
The numerator did not change. The story changed because the denominator did.
03
Metric contract
Turn a KPI into something reproducible.
Example contract
Cancellation Rate
- Business meaning
- Share of eligible placed orders that end in a final cancelled state.
- Numerator
- Distinct eligible order_id with final status = cancelled.
- Denominator
- Distinct eligible placed order_id.
- Time attribution
- Order created_at determines the reporting cohort.
- Exclusions
- Test orders, known fraud and internal QA accounts.
- Late status changes
- Recalculate recent periods until status maturity is reached.
- Default cuts
- City, platform, customer type and cancellation reason.
- Owner
- Operations Analytics.
A metric contract does not need to be bureaucratic. It needs to make the important choices visible enough that another analyst — or an AI agent — can reproduce the same answer.
04
Ambiguity
Common metric names hide different questions.
Orders
Placed orders and completed orders are different operational stories.
Revenue
GMV, gross revenue, net revenue and recognized revenue are not interchangeable.
Conversion
Visitors → checkout and checkout → order answer different questions.
Retention
Weekly active retention, repeat purchase and subscription renewal are different metrics.
New customer
Signup date, first order attempt and first completed order create different populations.
Churn
Inactivity, cancellation and non-renewal require different definitions by business model.
05
Reference
Build a small semantic layer.
| Metric | Definition | Time basis | Important rule |
|---|---|---|---|
| Completed Orders | Distinct order_id with final status = completed | completed_at | Exclude test/fraud orders |
| Cancellation Rate | Cancelled eligible orders / placed eligible orders | created_at cohort | Use final order status |
| Gross Merchandise Value | Sum of item value before refunds | completed_at | State tax/fees treatment |
| Net Revenue | Recognized revenue after refunds/discount rules | finance recognition date | Finance-owned definition |
| Average Order Value | GMV / completed orders | same window as GMV | Same order eligibility as denominator |
| Repeat Rate | Customers with 2+ qualifying orders / active customers | customer cohort + observation window | Define qualifying order |
Why this matters for AI
An AI assistant can generate the formula quickly. It cannot infer which of several legitimate business definitions your company has agreed to use unless you provide that context.
06
Challenge
Define retention without using the word retention.
Exercise · 15–20 minutes
Write the contract.
- Who enters the cohort: signup, first placed order or first completed order?
- What activity counts as “returned”?
- What is the retention window: day 7, week 1, month 1, rolling 30 days?
- Are cancelled/refunded orders qualifying activity?
- Do customers need a full observation window to enter the denominator?
- Which timestamp anchors cohort assignment and which timestamp measures return activity?
Reveal one valid retention contract
CohortCustomers by first completed order week
ReturnedAt least one completed order in week +1
NumeratorWeek-0 customers who complete an order in week +1
DenominatorWeek-0 customers with a fully observable week +1
ExclusionsTest/fraud accounts and fully refunded first orders
Result grainCohort week × city × acquisition channel
07
AI assist
Give AI the metric contract, not just the metric name.
Metric-aware AI request
I need to calculate weekly cancellation rate.
Metric contract:
- Eligible order: a non-test, non-fraud order that reached the placed state
- Numerator: distinct eligible order_id whose final status is cancelled
- Denominator: distinct eligible placed order_id
- Reporting cohort: order created_at week
- Final status: latest status event by event_time
- Recent periods may be incomplete because status events arrive late
- Output grain: week × city × customer_type
Before writing SQL:
1. Restate the metric contract.
2. Identify any ambiguity or missing rule.
3. Explain how the denominator will remain stable.
4. Explain how final status is derived without duplicating orders.
5. Then write SQL and provide validation queries. 08
Validation
Questions every KPI should survive.
01
What business decision will this metric influence?
02
What is the numerator?
03
What is the denominator?
04
What event timestamp assigns the metric to a period?
05
What is the grain of the reported result?
06
Which rows are included or excluded?
07
How do refunds, cancellations and late-arriving events behave?
08
Who owns the definition when teams disagree?
PRACTICE THIS NOW ↓
Interactive case · L03
Metric Contract Debugger
Keep the same numerator but swap the denominator and watch the cancellation story change completely.
09
Foundations complete
You can now frame, model, query and define.
Start with the decision and competing hypotheses.
Understand grain, keys and relationships.
Use SQL as a testable transformation workflow.
Turn KPI names into reproducible metric contracts.
Stage 01 takeaway
The foundation of analytics is not a dashboard. It is shared meaning: a clear question, a trustworthy data model, a reproducible query and an agreed metric definition.