# 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.

<!--
Guidance: How to use this template
- One metric per document. If two teams compute "the same" metric
  differently, write two documents with different names, then decide
  which one is official.
- Write the plain-language definition (section 2) before the formula. If
  it cannot be explained in two sentences, the metric is probably two
  metrics.
- Every filter and exclusion belongs in section 6. Hidden filters inside
  dashboards are the main reason numbers disagree.
- Treat changes to the definition like code changes: reviewed,
  versioned, and announced (section 11).
-->

| Field | Value |
|---|---|
| Metric ID | [MET-RET-030] |
| Metric name | [30-day customer retention rate] |
| Short name / code | [`retention_30d`] |
| Version | [1.1] |
| Status | [Proposed / Certified / Deprecated] |
| Metric owner | [@metric-owner], [team] |
| Technical owner | [@analytics-engineer] |
| Type | [KPI / Health metric / Guardrail / Diagnostic] |
| Unit | [Percent] |
| Desired direction | [Higher is better] |
| Certified in | [Semantic layer or BI tool name and object] |
| Last updated | YYYY-MM-DD |

## 1. Business question

<!-- Guidance: State the decision or question the metric informs and who uses it. A metric nobody acts on should not be a KPI. -->

[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."]

| Audience | Decision informed |
|---|---|
| *Example:* Growth team | Keep or roll back onboarding experiments |
| *Example:* Leadership | Allocation between acquisition and retention spending |
| [Audience] | [Decision] |

## 2. Definition in plain words

<!-- Guidance: Two or three sentences a non-analyst understands. Name the population, the event, and the time window. -->

[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

<!-- Guidance: Give the exact formula with every symbol defined. Note how edge cases (zero denominator, partial windows) are handled. -->

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:

$$
\text{Retention}_{30}(w) = \frac{\left|\{\, c \in C_w : \exists \text{ purchase by } c \text{ in } (t_c,\; t_c + 30\text{ days}] \,\}\right|}{|C_w|} \times 100\%
$$

| Symbol | Meaning |
|---|---|
| $C_w$ | Customers with first valid purchase in ISO week $w$ (UTC) |
| $t_c$ | Timestamp of the first valid purchase of customer $c$ |
| Valid purchase | Order with status not `cancelled`, see section 6 |

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

$$
\text{Retention}_{30}(\text{2026-W14}) = \frac{1\,470}{4\,820} \times 100\% \approx 30.5\%
$$

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

<!-- Guidance: State the grain at which the metric is computed and which dimensions it may be sliced by. List dimensions that must not be used, and why. -->

| Item | Value |
|---|---|
| Grain | *Example:* Weekly first-purchase cohort |
| Allowed dimensions | *Example:* Acquisition channel, country, first-order product category, platform |
| Not allowed | *Example:* Customer age: not reliably collected; small cohorts reveal individuals |
| Aggregation across dimensions | *Example:* Recompute from customer level; never average percentages |
| Minimum cohort size for display | *Example:* 100 customers |

## 5. Source tables and SQL sketch

<!-- Guidance: Name the certified source tables and give a query sketch that shows joins, windows and filters. The certified implementation in the semantic layer is the reference; this sketch documents the logic. -->

| Table | Role | Contract |
|---|---|---|
| `analytics.fct_orders` | Purchases | dc-fct-orders-001 |
| `core.customers` | Customer attributes, test-account flag | dc-customers-003 |
| [Table] | [Role] | [Contract] |

```sql
-- 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

<!-- Guidance: List every row that is excluded and why. If a filter is applied in a dashboard rather than in the definition, it is a different metric. -->

| ID | Exclusion | Reason |
|---|---|---|
| EX-01 | *Example:* Cancelled orders | Not a completed purchase |
| EX-02 | *Example:* Test and employee accounts | Not real customers |
| EX-03 | *Example:* Orders fully refunded within 24 hours | Treated as cancelled by finance policy |
| EX-04 | *Example:* B2B accounts | Different buying cycle; tracked by MET-RET-B2B |
| [EX-nn] | [Exclusion] | [Reason] |

## 7. Freshness and availability

<!-- Guidance: Say when the number is updated and when a period is final. -->

| Item | Value |
|---|---|
| Refresh | *Example:* Daily by 07:00 UTC |
| Latency to final | *Example:* A cohort week is final 37 days after the week starts |
| Upstream dependency | *Example:* `fct_orders` pipeline DPS-ORDERS-001 |
| Restatement policy | *Example:* Final values change only with a definition version change, announced in section 11 |

## 8. Targets and thresholds

<!-- Guidance: Give the target, the alerting thresholds and who set them. Separate goals from alerts. -->

| Item | Value | Set by | Valid until |
|---|---|---|---|
| Target | *Example:* at least 32% by Q4 | [@leadership] | YYYY-MM-DD |
| Healthy range | *Example:* 28% to 36% | [@metric-owner] | Ongoing |
| Alert | *Example:* Weekly value more than 3 points below 8-week median | [@analytics] | Ongoing |
| Benchmark | *Example:* 29.5% average in the previous year | Historical data | n/a |

## 9. Known caveats

<!-- Guidance: Record everything that could mislead a reader, with the date it was found. -->

- [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

<!-- Guidance: Link inputs, outputs and guardrails so readers can interpret movement. -->

| Metric | Relationship |
|---|---|
| *Example:* MET-RET-090 90-day retention | Longer-horizon version |
| *Example:* MET-ACQ-001 New customers | Denominator size; mix shifts can move retention |
| *Example:* MET-REV-020 Revenue per retained customer | Value of retention |
| [Metric ID] | [Relationship] |

## 11. Change log

<!-- Guidance: Every change to the formula, filters or sources gets a new version. Note whether history was restated. -->

| Version | Date | Author | Change | History restated |
|---|---|---|---|---|
| 1.0 | YYYY-MM-DD | [@name] | *Example:* First certified definition | n/a |
| 1.1 | YYYY-MM-DD | [@name] | *Example:* Added EX-03 (refunds within 24 h) after finance review | Yes, from YYYY-MM-DD |
| [x.y] | YYYY-MM-DD | [@name] | [Change] | [Yes / No] |

## 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.