Metric Definition Worksheet
Define a metric’s business meaning, grain, population, formula, date rules, dimensions, source lineage, tests, owner, and change policy with a blank worksheet.
On this page · 4 sections
Use this when
- Situation
- Use this before implementing a shared KPI, reconciling conflicting definitions, or changing a metric that already has downstream consumers.
- Steps
- 3 to fill it in
How to use it
Use this before implementing a shared KPI, reconciling conflicting definitions, or changing a metric that already has downstream consumers.
- 01Complete the business meaning and population before writing SQL.
- 02Name the grain and date rule explicitly; both commonly change the answer.
- 03Review one worked example and one exclusion with the metric owner.
Template
Copy it. Replace the brackets. Delete what does not apply.
# Metric definition: [metric name]
## Ownership and status
- **Business owner:** [person or team accountable for meaning]
- **Technical owner:** [person or team accountable for implementation]
- **Status:** [draft / approved / deprecated]
- **Effective date:** [YYYY-MM-DD]
- **Review date:** [YYYY-MM-DD]
- **Version:** [v1.0]
## Decision this metric supports
[What decision, alert, workflow, or comparison uses this metric?]
## Plain-language definition
[Explain what the metric measures without referring to table or column names.]
## Grain and population
- **Entity or event:** [customer, order, subscription, session, invoice, etc.]
- **Output grain:** [one value per…]
- **Included population:** [explicit inclusion rules]
- **Excluded population:** [explicit exclusions and why]
- **Zero-activity entities:** [included / excluded / not applicable]
## Formula
```text
[numerator or aggregation]
--------------------------------
[denominator, if applicable]
```
- **Aggregation:** [sum / count distinct / average / median / ratio / other]
- **Units:** [USD / customers / percentage / days / etc.]
- **Rounding:** [rule and precision]
- **Currency conversion:** [source, rate date, or not applicable]
## Time rules
- **Reporting timestamp:** [event field that assigns the period]
- **Timezone:** [UTC or named business timezone]
- **Period boundary:** [calendar month, fiscal week, rolling 28 days, etc.]
- **Late-arriving records:** [restatement or cutoff rule]
- **Refunds/reversals:** [event date, original event date, or other rule]
## Dimensions allowed
| Dimension | Source | Historical behavior | Notes |
|---|---|---|---|
| [region] | [model.column] | [current / point-in-time] | [unknown handling] |
## Source and lineage
```text
[source systems] → [staging] → [intermediate logic] → [metric model] → [consumers]
```
- **Authoritative source:** [model/table]
- **Primary key or grain check:** [test/query]
- **Refresh expectation:** [schedule and promised availability]
## Null, unknown, and error rules
- **Null numerator:** [rule]
- **Null denominator:** [rule]
- **Division by zero:** [rule]
- **Unknown dimension:** [rule]
- **Deleted or corrected records:** [rule]
## Validation
- [ ] Reconciles to [independent source or prior report] within [tolerance].
- [ ] Key is unique at the declared grain.
- [ ] Required fields are not null.
- [ ] Filters match the written inclusion and exclusion rules.
- [ ] One-to-many joins do not duplicate the numerator or denominator.
- [ ] Boundary dates, zero values, and late records have worked examples.
## Worked examples
### Included
[Small input and expected result.]
### Excluded or handled differently
[Small input, rule applied, and expected result.]
## Consumers and change policy
- **Known consumers:** [dashboards, models, alerts, teams]
- **Breaking changes:** [what counts as breaking]
- **Approval required from:** [owner(s)]
- **Migration window:** [timeline]
- **Deprecation communication:** [channel and notice period]
## Open questions
- [Only unresolved business decisions; remove this section when none remain.]
Completed instructor example
Instructor example: net payment value
A completed definition for the ecommerce payment measure used by the public revenue take-home.
# Metric definition: net payment value
## Ownership and status
- **Business owner:** Fictional finance operations
- **Technical owner:** Analytics engineering
- **Status:** Approved for the v1.0.0 teaching case
- **Effective date:** 2026-09-06
- **Version:** v1.0
## Decision this metric supports
Reproduce the monthly value of successful customer payments after refunds and investigate period changes at order grain.
## Plain-language definition
The amount successfully collected for qualifying orders, less every refund linked to those orders.
## Grain and population
- **Entity:** Order
- **Output grain:** One value per order month
- **Included population:** Successful payment attempts and all recorded refunds linked to their orders
- **Excluded population:** Failed or pending payment attempts
- **Zero-activity periods:** Included only when a date spine is added
## Formula
```text
SUM(successful payment amount by order)
-
SUM(refund amount by order)
```
- **Aggregation:** Sum
- **Units:** USD
- **Rounding:** Two decimals for display
## Time rules
- **Reporting timestamp:** orders.ordered_at
- **Timezone:** UTC
- **Period boundary:** Calendar month
- **Late-arriving records:** A linked refund restates the original order month
- **Refunds/reversals:** Assigned to the original order month
## Source and lineage
```text
payments ─┐
├─ order-grain payment model ─┐
refunds ──┘ ├─ monthly net payment value
orders ────────────────────────────────┘
```
- **Authoritative release:** Ecommerce Sales & Returns v1.0.0
- **Grain check:** One row per order after payment and refund aggregation
- **Refresh expectation:** Static versioned teaching release
## Validation
- [x] Failed payments are excluded.
- [x] Multiple refunds do not duplicate payment amounts.
- [x] Monthly values reconcile to the published checked output.
- [x] Orders without refunds remain in the population.
## Worked examples
If an order has $120 in successful payments and two refunds of $20 and $10, its net payment value is $90. A failed $120 payment contributes $0.
## Consumers and change policy
- **Known consumers:** Revenue take-home and ecommerce dataset walkthrough
- **Breaking changes:** Changing payment status, refund treatment, or reporting timestamp
- **Approval required from:** Business and technical owners
- **Migration window:** Publish a new version; do not silently rewrite v1.0
Why these decisions were made
- The reporting date is the original order date, not payment or refund processing date.
- The name avoids implying GAAP revenue recognition.
- Refund treatment is explicit so late refunds restate the original order month.