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

<!--
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).
- This document defines one metric. The model it is computed from
  (entities, dimensions, measures, joins) belongs in the Semantic Model
  Specification (`data/semantic-model-specification`); where the metric
  is shown belongs in the Dashboard Specification
  (`data/dashboard-specification`).
-->

## Document control

<!-- Guidance: Identify the metric, its owners and its certification state. The business owner answers for the definition; the technical owner answers for the implementation. Pitfall: a metric with a technical owner only, so nobody can approve a definition change. -->

| Field | Value |
|---|---|
| Metric ID | [MET-RET-030] |
| Metric name | [30-day customer retention rate] |
| Short name / code | [`retention_30d`] |
| Document version | [1.2] |
| Status | [Proposed / Certified / Deprecated] |
| Metric type (business) | [KPI / Health metric / Guardrail / Diagnostic] |
| Semantic layer metric type | [simple / ratio / cumulative / derived / conversion] (see section 12) |
| Unit | [Percent] |
| Desired direction | [Higher is better] |
| Business owner | [@metric-owner], [team] |
| Technical owner | [@analytics-engineer] |
| Data steward | [@steward] |
| Certification status | [Not certified / Certified / Certification suspended] |
| Certified by and date | [@certifier], YYYY-MM-DD |
| Next certification review | YYYY-MM-DD |
| Certified in | [Semantic layer or BI tool name and object] |
| Semantic model | [Semantic model ID, see `data/semantic-model-specification`] |
| Approvers/reviewers | [@metric-owner], [@steward], [@finance-partner] |
| Classification | [Internal] |
| Last updated | YYYY-MM-DD |

### Approvals

| Role | Name | Decision | Date |
|---|---|---|---|
| Business owner | [@name] | [Approve / Reject] | YYYY-MM-DD |
| Technical owner | [@name] | [Approve / Reject] | YYYY-MM-DD |
| Data steward (certification) | [@name] | [Certify / Reject] | YYYY-MM-DD |

### Revision history

| Version | Date | Author | Change summary |
|---|---|---|---|
| 1.0 | YYYY-MM-DD | [@name] | *Example:* First certified definition |
| 1.2 | 2026-10-24 | Template maintainers | Updated to add Document control, owner and certification fields, the mapping to MetricFlow metric types, and the Corvid Orders contract IDs |
| [x.y] | YYYY-MM-DD | [@name] | [Change summary] |

## Contents

- [1. Business question](#1-business-question)
- [2. Definition in plain words](#2-definition-in-plain-words)
- [3. Formula](#3-formula)
- [4. Grain and dimensions](#4-grain-and-dimensions)
- [5. Source tables and SQL sketch](#5-source-tables-and-sql-sketch)
- [6. Filters and exclusions](#6-filters-and-exclusions)
- [7. Freshness and availability](#7-freshness-and-availability)
- [8. Targets and thresholds](#8-targets-and-thresholds)
- [9. Known caveats](#9-known-caveats)
- [10. Related metrics](#10-related-metrics)
- [11. Change log](#11-change-log)
- [12. Semantic layer mapping](#12-semantic-layer-mapping)
- [13. Ownership and certification](#13-ownership-and-certification)
- [Standard and licence note](#standard-and-licence-note)

## 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 |
|---|---|---|
| `lakehouse.sales.orders` | Purchases (Orders data product, output port `orders` 2.1.0) | `corvid-orders-orders` |
| `core.customers` | Customer attributes, test-account and employee flags | `corvid-customers-customers` |
| [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 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

<!-- 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:* Orders data product, port `orders` (contract `corvid-orders-orders`, previous day complete by 06:00 UTC per SLA-ORD-003), built by pipeline DPS-PIPELINE-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] |

## 12. Semantic layer mapping

<!-- Guidance: Record how the metric is implemented in the semantic layer so the certified object and this document cannot drift apart. Pitfall: re-implementing the formula in each dashboard instead of querying the certified metric. -->

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.

| MetricFlow type | Use when | Example at Corvid Commerce |
|---|---|---|
| simple | The metric aggregates one measure, optionally filtered | *Example:* Count of confirmed orders |
| ratio | The metric divides one metric by another, each with its own filter | *Example:* Average order value: net revenue divided by order count |
| cumulative | The metric accumulates a measure over a window or since a start date | *Example:* Rolling 28-day active customers |
| derived | The metric is an expression over other metrics, including offsets in time | *Example:* Week-over-week change in net revenue |
| conversion | The metric counts entities that perform a base event and then a conversion event within a window | *Example:* Share of first-time buyers who buy again within 30 days |

| Item | Value |
|---|---|
| Semantic layer | [Example: dbt Semantic Layer with MetricFlow] |
| Metric object name | [`retention_30d`] |
| Metric type | *Example:* conversion (base event: first valid purchase; conversion event: repeat valid purchase; entity: customer; window: 30 days) |
| Alternative implementation | *Example:* ratio of two simple metrics over a cohort model, if conversion metrics are not available |
| Base and conversion measures | *Example:* base measure `first_purchases` (one per customer, from a first-purchase model); conversion measure `repeat_purchases` (valid orders after the first) |
| Input metrics or measures | [Names] |
| Semantic model | [ID and link, see `data/semantic-model-specification`] |
| Filters implemented in the layer | *Example:* EX-01 to EX-04 from section 6 |
| Validation | *Example:* Layer output equals the SQL sketch in section 5 within 0.1 points for five recent cohorts |

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

<!-- Guidance: State who may change the metric, what certification means and how it is lost. A certified badge with no review date is a promise nobody keeps. -->

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.

| Criterion | Evidence required |
|---|---|
| Definition approved by the business owner | Approval row in Document control |
| Implementation reproduces section 3 | Validation record in section 3 and section 12 |
| Source datasets under contract | Data Contract (`data/data-contract`) IDs in section 5 |
| Data quality checks in place | Rules per the Data Quality Standard (`data/data-quality-standard`) |
| Owners and steward named | Document control |

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.