Metric Definition Template (KPI)

Metric Definition: [Metric name]

Definition of one business metric or KPI, written so that anyone who computes it gets the same number: the question it answers, the plain definition, the formula, grain and dimensions, source tables with a SQL sketch, filters, freshness, targets, caveats, ownership and certification, the mapping to a semantic layer metric type (dbt MetricFlow, Apache-2.0) and change history. Replace every [bracketed] value and every item marked Example. Worked examples use a 30-day customer retention rate at Corvid Commerce, a fictional SaaS retailer.

Document control

Approvals

Revision history

Contents

• 1. Business question
• 2. Definition in plain words
• 3. Formula
• 4. Grain and dimensions
• 5. Source tables and SQL sketch
• 6. Filters and exclusions
• 7. Freshness and availability
• 8. Targets and thresholds
• 9. Known caveats
• 10. Related metrics
• 11. Change log
• 12. Semantic layer mapping
• 13. Ownership and certification
• Standard and licence note

1. Business question

[Example: "Are new customers finding enough value to come back within a month of their first purchase? Used by the growth team to judge onboarding changes and by leadership in the monthly business review."]

2. Definition in plain words

[Example: "Of all customers who made their first purchase in a given week, the share who made at least one more purchase within 30 days after that first purchase."]

3. Formula

For a cohort $C_w$ of customers whose first purchase falls in week $w$, and $t_c$ the time of customer $c$'s first purchase:

Worked example: in week 2026-W14, 4 820 customers made a first valid purchase, and 1 470 of them bought again within 30 days, so

Validation: [Example: "The certified implementation reproduces this value for cohorts 2026-W10 to 2026-W14 within 0.1 points of an independent notebook calculation by a second analyst; evidence in ticket ANL-482."]

Edge cases: [Example: "A cohort is reported only after 30 full days have passed for its last member. If $|C_w| = 0$ the value is null, not zero."]

4. Grain and dimensions

5. Source tables and SQL sketch

-- Sketch: 30-day retention by weekly first-purchase cohort (UTC)
WITH valid_orders AS (
  SELECT o.customer_id, o.order_ts
  FROM lakehouse.sales.orders AS o
  JOIN core.customers AS c ON c.customer_id = o.customer_id
  WHERE o.status <> 'cancelled'
    AND c.is_test_account = FALSE
    AND c.is_employee = FALSE
),
first_purchase AS (
  SELECT customer_id, MIN(order_ts) AS first_ts
  FROM valid_orders
  GROUP BY customer_id
),
cohort AS (
  SELECT customer_id, first_ts, DATE_TRUNC('week', first_ts) AS cohort_week
  FROM first_purchase
  WHERE first_ts < CURRENT_TIMESTAMP - INTERVAL '30 days'
),
returned AS (
  SELECT DISTINCT k.customer_id
  FROM cohort AS k
  JOIN valid_orders AS v
    ON v.customer_id = k.customer_id
   AND v.order_ts >  k.first_ts
   AND v.order_ts <= k.first_ts + INTERVAL '30 days'
)
SELECT
  k.cohort_week,
  COUNT(*)                                  AS cohort_size,
  COUNT(r.customer_id)                      AS retained,
  100.0 * COUNT(r.customer_id) / COUNT(*)   AS retention_30d_pct
FROM cohort AS k
LEFT JOIN returned AS r ON r.customer_id = k.customer_id
GROUP BY k.cohort_week
HAVING COUNT(*) >= 100
ORDER BY k.cohort_week;

Note: the cohort filter above excludes customers with fewer than 30 days of history; the published metric additionally drops whole cohort weeks until all members are complete (section 3).

6. Filters and exclusions

7. Freshness and availability

8. Targets and thresholds

9. Known caveats

• [Example: Customers who check out as guests get a new customer_id each time, so retention is understated by an estimated 2 to 3 points.]
• [Example: Cohorts in late November include holiday shoppers who return less often; compare year over year, not week over week.]
• [Example: Before YYYY-MM-DD, app orders were missing for two days; affected cohorts are flagged.]

10. Related metrics

11. Change log

12. Semantic layer mapping

dbt MetricFlow, the engine behind the dbt Semantic Layer, has been released under the Apache License 2.0 since version 0.209.0. It defines five metric types: simple, ratio, cumulative, derived and conversion. Pick the type that matches the formula in section 3, and record the semantic model it reads from. Exact YAML field names differ between dbt releases, so check docs.getdbt.com for the release you run; other semantic layers use different names for the same ideas.

Model the base event as a first-purchase measure, one row per customer, so that the base and conversion events are different measures. If both events read the same "orders" measure, every repeat purchase also counts as a new base event, and the metric becomes "customers who ordered twice within any 30 days" rather than retention of new customers. Check the conversion window boundaries against section 3: the formula excludes the first purchase itself and includes a purchase exactly 30 days later.

When the semantic layer and the SQL sketch disagree, the SQL sketch and the formula in section 3 are the reference until the difference is explained. Record the explanation in the change log, fix whichever side is wrong, and re-run the validation in the table above before the metric keeps its certified status. Keep the semantic layer object name identical to the short name in Document control, so that search, lineage and dashboards find one object.

The Apache Ossie specification (incubating, formerly Open Semantic Interchange) aims to make such definitions portable between tools. Its core specification is a draft as of October 2026; treat it as emerging and check its maturity before relying on it.

13. Ownership and certification

Certification tells readers that the definition is approved, the implementation matches it, and someone is accountable for both. Display the certified metric only from the semantic layer object in the Document control table; dashboards built to the Dashboard Specification (data/dashboard-specification) reference the metric ID instead of copying the formula.

Certification is suspended when a source contract has a breaking change, a critical quality rule fails for more than [2] consecutive days, or the review date passes. Show the suspended state on every dashboard that uses the metric until it is restored.

Standard and licence note

Based on common industry practice for metric and KPI definitions, with metric type names from dbt MetricFlow (Apache License 2.0, https://github.com/dbt-labs/metricflow). Original text written for this collection; no text is copied from dbt documentation. Check the current dbt and MetricFlow documentation for field names before implementing. Template licence: MIT.