Three Numbers, One Metric
Before you wire activation_rate_7d to a dashboard tile or an experiment guardrail, commit a spec file that answers five questions in SQL-shaped detail. Not a slide sentence. Not a funnel label. A contract another team can replay without calling you.
metric: activation_rate_7d
owner: finance-analytics
version: 1
That header is incomplete until the body pins numerator, denominator, identity, window, and late-data behaviour. One canonical name may cover one contract. If product needs a device-level funnel and finance needs ledger truth, you need two names or you will eventually argue three percentages that share one English word.
The examples below use composite company names. The contracts are the kind real warehouses and experiment platforms enforce.
Five lines decide whether your metric is real
Numerator. Name the event or relation, the filters, and the grain. first_purchase_completed is still vague. fct_orders where order_status = 'paid', first row per user_id, test accounts excluded, is a contract someone can diff.
Denominator. Name who is eligible and who is out. Invited beta users, employees, bot signups, and incomplete auth rows belong here explicitly. Denominator drift moves the rate when the numerator never changed.
Identity. State the key that ties numerator to denominator. Device, user, account, household. If a merge job collapses three devices into one user, the rate moves. Ship merge version as part of metric version.
Window. Anchor event, length, timezone, inclusive bounds. Seven calendar days in America/Los_Angeles and seven rolling UTC days from account_created_at diverge at month boundaries.
Late data. Restate through T+10, or freeze at T+7. Either works. Mixing policies across consumers of the same name does not.
A complete starter spec for a finance-owned board metric:
metric: activation_rate_7d
owner: finance-analytics
version: 1
identity:
key: user_id
merge_job: identity_graph_v2
numerator:
relation: fct_orders
filter: order_status = 'paid' and is_test = false
grain: first paid order per user_id
denominator:
relation: dim_accounts
filter: account_type = 'customer'
window:
anchor: account_created_at
length_days: 7
timezone: UTC
late_arrival: { policy: restate, through: T+10 }
proxies: [activation_rate_7d_device_funnel, activation_proxy_same_session]
The proxies block lists related metrics that may not borrow this name.
Fill the spec before you debate SQL in Slack. Analyst time goes to implementation and tests, not to rediscovering that finance uses user_id and product uses device_id.
Numerator mistakes we block in review
| Vague spec line | What breaks | Spec line that survives review |
|---|---|---|
| "First purchase" | Client event fires before ledger row | fct_orders, order_status = 'paid', first per user_id |
| "Signup" | OTP retries duplicate denominators | account_created with bot filter explicit |
| "Activation proxy" | Click counts as money | Separate metric name; proxy listed in proxies |
Denominator mistakes
"All users" silently includes test accounts, internal staff, and invited beta cohorts you meant to exclude. Write the exclusion list. If marketing later adds a cohort, denominator change triggers a version bump, not a surprise exec email.
Window mistakes
Calendar local windows suit product funnels tied to local campaigns. UTC rolling windows suit finance close. Pick one per canonical name. Document the anchor event. "Seven days from signup" is ambiguous until signup is an event with a timestamp column name.
Why finance, product, and growth need three names
Product, finance, and growth often need different latency and different sources. That is normal. The failure mode is calling each path activation_rate_7d.
| Consumer need | Typical source | Name pattern |
|---|---|---|
| Board, forecast, payout | dbt mart, ledger joins | activation_rate_7d (signed spec) |
| Copy iteration, funnel drops | Real-time funnel builder | activation_rate_7d_device_funnel |
| Same-session UX checks | Experiment SDK proxy | activation_proxy_same_session |
Finance keeps the canonical name because the board and payouts read that contract. Product and growth keep speed. They lose the shared shorthand.
When someone asks "what is activation," the answer becomes "which spec ID?" not "trust the funnel."
Identity merge rewrites history without a deploy
Do not treat identity merge as downstream magic. Write the merge job name and version into the YAML. When identity_graph_v3 ships, bump metric version and publish a restatement note before anyone reads a step change as a product regression.
One customer on three devices can produce three denominator rows at device grain and one row at user grain. Same week, same humans, different rates. The spec prevents the debate from starting in Slack.
Instrumentation drift belongs in the same layer. Maintain an alias map in git, not in a BI tooltip:
# metrics/contracts/signup_events.yml
events:
- canonical: signup_complete
aliases:
- registration_finished
required_properties:
- device_id
owner: mobile-platform
CI fails when alias volume drops without an approved rename. The event owner fixes the SDK or the map. Analytics does not patch silently in a dashboard layer.
Property nulls need the same discipline. If order_paid arrives without user_id before login completes, route rows to a quarantine table and document whether the canonical metric includes or excludes them. Silent inclusion creates a numerator path finance cannot reconcile.
Batch truth and second-level proxies
Finance marts run on a schedule and carry refund logic. Product funnels run in minutes and carry alias maps. Growth proxies run in seconds and carry session boundaries. You need all three speeds. You do not need one name.
| Path | Latency | Holds canonical name? |
|---|---|---|
| dbt mart with ledger joins | Hours | Yes, when spec signed |
| Funnel builder | Minutes | No, suffix _device_funnel |
| Experiment SDK proxy | Seconds | No, prefix activation_proxy_ |
When product asks to "use the same number as finance" on a hourly copy test, point them at the proxy name and correlate in analysis. Correlation is not identity.
Late-data policy belongs in the spec for batch paths only. Real-time proxies either freeze at session end or carry a _frozen suffix when marketing needs a snapshot that must not restate.
late_arrival:
policy: restate
through: T+10
notify: '#metrics-restatements'
When restatement moves a mature cohort by more than 0.3pp, post the diff with spec version. Executives learn to read version footnotes instead of treating every dip as product failure.
Experiments ship on spec IDs, not funnel labels
Primary experiment metrics reference spec and version, or the readout carries a visible non-canonical flag. Waivers expire in one quarter.
{
"experiment_id": "exp_onboard_checklist_v4",
"primary_metric": {
"spec": "activation_rate_7d",
"version": 1,
"source": "finance_mart"
},
"diagnostic_metrics": [
"activation_proxy_same_session",
"time_to_first_paid_order_hours"
]
}
Diagnostic metrics stay. They debug mechanism. They do not declare wins on names finance has not signed.
Exposure logging uses the same identity key as the spec. Log experiment_eligible only after the denominator predicate passes. Persist spec_version on exposure rows. Join outcomes on user_id when the spec says user_id, not on anonymous_id because the UI default is faster. The diagram above shows the full path; the query below is the hinge reviewers diff in PRs.
-- replay: spec_version + user_id join + 7d window from anchor
select e.variant, count(*) as eligible_exposed, count(a.user_id) as activated
from fct_experiment_exposure e
join dim_accounts el on el.user_id = e.user_id and el.account_type = 'customer'
left join fct_orders a
on a.user_id = e.user_id
and a.order_status = 'paid'
and a.paid_at between el.account_created_at and el.account_created_at + interval '7 days'
where e.experiment_id = 'exp_onboard_checklist_v4' and e.spec_version = 1
group by 1;
If the experiment UI proposes simpler SQL, require a diff against this query in the PR.
When the aggregate win lies
Aggregate lift can hide segment harm. Register slices before merge, not in the post-hoc explorer after a disappointing chart.
For activation work, a minimal pre-registration set:
platform (web, ios, android)
region bucket (EU, US, rest)
acquisition channel (paid, organic, referral)
Block launch when any pre-registered segment inverts direction relative to control, even if the aggregate p-value clears. Ad hoc analysis continues. Product ship does not, unless you run a new experiment.
The CI gate that blocks silent fork
Definitions change in PRs like code.
When a PR touches an event schema, a model under metrics/, or a tile wired to a canonical name:
CI renders a definition diff (numerator, denominator, identity, window).
A fourteen-day shadow query runs against the prior version.
The metric owner signs, one named human in CODEOWNERS.
Merge blocks when the central estimate moves more than two percentage points without a breaking-change label. Shadow queries run in the warehouse, not in a notebook.
Tiles import by git SHA and forbid local SQL override:
tile: exec_activation_weekly
metric_spec: activation_rate_7d
metric_version: 1
warehouse_model: mart.finance.activation_rate_7d
forbidden: local_sql_override
New canonical metrics start as a one-page RFC: decision the number drives, population, failure modes, proxy map, migration plan. If the RFC cannot fill a failure-mode table, the metric stays diagnostic until it can.
Decision. What choice will this number drive in the next two quarters? If the answer is "awareness," it is not ready for canonical status.
Population. Who is in, explicitly who is out. Link to the denominator filter in plain language.
Failure modes. How can this rate rise while the business gets worse? Fill the table before the tile exists.
| Failure mode | Symptom | Detection |
|---|---|---|
| Denominator inflation | Rate drops while signups flat | Reconciliation bucket B-only denom |
| Numerator lag | Rate spikes after pipeline delay | T+7 vs T+10 restatement delta |
| Identity collapse | Step change on merge job ship | Version bump plus backfill alert |
| Bot leakage | Rate rises, chargebacks follow | is_test filter test on numerators |
Proxy map. List near-real-time metrics that may correlate but may not share the name.
Migration. Shadow period length, tile rename owners, and the date the old name dies.
dbt tests enforce the contract on every merge:
models:
- name: activation_rate_7d
columns:
- name: spec_version
tests:
- accepted_values:
values: [1]
- name: user_id
tests:
- not_null
- unique
Enforce the YAML in Go before merge
YAML expresses intent. A small Go binary in CI rejects incomplete specs before they reach the catalog. Pair declarative spec with executable validation.
type Spec struct {
Metric string `yaml:"metric"`
Version int `yaml:"version"`
Owner string `yaml:"owner"`
Identity struct{ Key, MergeJob string }
Numerator struct{ Relation, Filter, Grain string }
Denominator struct{ Relation, Filter string }
Window struct{ Anchor string; LengthDays int; Timezone string }
}
func (s *Spec) Validate() error {
switch {
case s.Metric == "":
return errors.New("metric: name required")
case s.Version < 1 || s.Owner == "":
return fmt.Errorf("metric %q: version and owner required", s.Metric)
case s.Identity.Key == "":
return fmt.Errorf("metric %q: identity.key required", s.Metric)
case s.Numerator.Relation == "" || s.Denominator.Relation == "":
return fmt.Errorf("metric %q: numerator and denominator relations required", s.Metric)
case s.Window.LengthDays < 1 || s.Window.Anchor == "":
return fmt.Errorf("metric %q: window anchor and length_days required", s.Metric)
}
return nil
}
Run metricspec validate metrics/definitions/activation_rate_7d.yaml in CI alongside the shadow query. Fail the PR when the struct validates but the shadow delta exceeds two points without a breaking-change label.
Three percentages, one English word
Skip the postmortem narrative. The shape is always the same. Three valid queries, one label.
Product prints 34.2% from a device-level funnel on first_purchase_completed. Finance prints 28.7% from order_paid on merged user_id. Growth prints 31.0% from a same-session proxy on flag_seen. Nobody changed the product between the prints. The name hid three contracts.
That week cost a roadmap argument and one rolled-back experiment. The fix was not a better pipeline. It was renaming two paths and signing the third.
When two teams disagree after the gate exists, run reconciliation before debate: spec A verbatim, spec B verbatim, then a bucket table for A-only denom rows, B-only denom rows, numerator timing skew, and property nulls. The table names the delta. It does not pick a winner in chat.
Query A runs the signed spec. Query B runs the challenger. Query C lists entity keys in one numerator set but not the other for the same week. Store the notebook on the PR. Production dashboards still read dbt models only.
Catalog ownership lives in git. Specs sit under metrics/definitions/. CODEOWNERS routes review to one named human. Zombie metrics with no owner for two quarters leave the catalog. Copy-paste revives a fourth silent dialect under an old name.
One mega-query cannot serve ledger close and sub-second funnels
We would not force one job to serve sub-second funnels and ledger close in the same query. Proxies stay proxies.
We would not skip specs for "temporary" exec metrics. Temporary tiles become permanent footnotes.
We would not let experiment platforms reuse canonical names for proxy events without a waiver.
Lightweight rules fit local campaign readouts that never compare across teams. Full gate fits board metrics, payout inputs, and experiment ship decisions.
When a full spec is optional
Campaign postmortems may use a one-off SQL file with a dated filename. They do not enter the canonical catalog. Internal team dashboards that no other team compares against may use a lighter template: still document numerator and denominator, skip shadow gate until the name appears on a shared deck.
Require the full gate when any of these is true:
The metric appears on a recurring exec deck
The metric gates an experiment ship decision
The metric feeds a forecast or payout
Two teams already disagree about its value
Shadow fortnights buy quiet quarters
Honest contracts slow the first dashboard. Shadow queries spend warehouse credits. Segment blocks frustrate teams chasing a headline win.
You buy fewer quarter-long arguments about whose SQL is "more true," fewer ship decisions on proxies masquerading as ledger truth, and a version footnote when identity merge restates history instead of a panic about conversion.
If you add one habit this week, add the spec file before the tile. Rename every other path until the name matches the contract. The rate will move again when merge jobs or event aliases change. Version bumps make that move visible. Unsigned names make it a surprise.
Cutover week without the surprise email
Run these stages in order on the next metric that will reach the exec deck. Each stage blocks a class of silent fork.
| Stage | Artifact | Owner | Gate |
|---|---|---|---|
| RFC | One-page memo with failure-mode table filled | Metric sponsor | Staff review |
| Spec | metrics/definitions/*.yaml v1, proxies named |
Analytics eng | LoadAndValidate + CODEOWNERS |
| Shadow | New model beside old name, tile unchanged | Analytics eng | 14-day delta ≤ 2pp |
| Tile PR | Imports spec ID, forbids local SQL override | Dashboard owner | Metric owner sign-off |
| Experiment template | Primary metric points at spec ID; diagnostics listed | Growth eng | Non-canonical flag on proxies |
| Rename | Legacy funnel and proxy paths suffixed | Product + finance | No shared English name |
| Cutover note | Version, restatement policy, support channel | Metric owner | Posted before deck refresh |
Skip a stage and you recreate the conditions where three teams print three rates under one label. The composite week above is what that skip looks like after the fact. You do not need to live it to implement the contract.
Field: Data & Analytics
