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.

Incomplete YAML header without a signed contract body

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

What a signed metric definition must pin down

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

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

Three consumer lanes with distinct metric 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.

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.

Identity merge moves the denominator

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

Batch, funnel, and proxy latency tiers with naming rules

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.

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

Experiment join path on spec identity key

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.

Aggregate winner, segment loser

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.

Metric change gate

When a PR touches an event schema, a model under metrics/, or a tile wired to a canonical name:

1. CI renders a definition diff (numerator, denominator, identity, window).
2. A fourteen-day shadow query runs against the prior version.
3. 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.

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 spec parsed by Go Validate() then CI shadow gate

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.

One metric name, three queries, three answers

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

Cutover pipeline from RFC through cutover note

Run these stages in order on the next metric that will reach the exec deck. Each stage blocks a class of silent fork.

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

References

• dbt: MetricFlow and semantic models
• Kimball Group: Ensuring consistency in BI definitions
• Google Analytics: How metrics are calculated
• Optimizely: Statistical significance and segment analysis
• Pearl: Simpson's paradox and causal interpretation