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 and change history. Replace every [bracketed] value and every item marked Example. Worked examples use a 30-day customer retention rate.

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 analytics.fct_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

Standard and licence note

Based on common industry practice for metric and KPI definitions in analytics and semantic layers. Original text written for this collection. Template licence: MIT.