Metric Definition and Implementation Questions

Direct answer: Subscription-level granularity is usually the right default for churn (it directly matches what's being cancelled), user-level double counts a user with multiple subscriptions and undercounts users who consolidate subscriptions, and account-level is right only when the business genuinely treats a multi-subscription account as one relationship (e.g., a family or team plan).

Structured elaboration:

• User-level: treats "did this specific person churn" as the unit; simplest to reason about for a single-subscription-per-user product, but for a product allowing multiple concurrent subscriptions per user, this granularity either double counts (a user with two subscriptions cancelling one looks like "half a churn," awkward to represent at user grain) or requires an arbitrary aggregation rule.
• Subscription-level: treats each individual subscription as the churn unit; precise and directly matches the billing system's own unit of cancellation, at the cost of needing a SEPARATE rollup if the business also cares about "did we lose this CUSTOMER" (a customer downgrading from two subscriptions to one hasn't churned as a customer, but one subscription DID churn).
• Account-level: appropriate when the product's actual commercial relationship is the account (e.g., a company account with several seat-subscriptions); computing churn at the wrong (finer) grain here would show noisy "seat churn" fluctuations that don't reflect the real business risk, which is losing the ACCOUNT entirely.
• Storage/compute cost: subscription-level requires no additional aggregation table for a subscription-centric billing system (the raw grain often already exists); user- or account-level requires an explicit rollup layer that must itself be kept in sync with subscription changes.

Worked example and SQL skeleton (subscription-level, monthly churn):

WITH subs_prior_month AS (
  SELECT subscription_id, user_id
  FROM subscriptions
  WHERE start_date <= :prior_month_end
    AND (end_date IS NULL OR end_date >= :prior_month_start)
),
subs_current_month AS (
  SELECT subscription_id
  FROM subscriptions
  WHERE start_date <= :current_month_end
    AND (end_date IS NULL OR end_date >= :current_month_start)
)
SELECT
  COUNT(*) AS prior_active,
  COUNT(*) - COUNT(cur.subscription_id) AS churned
FROM subs_prior_month p
LEFT JOIN subs_current_month cur ON cur.subscription_id = p.subscription_id;

This reuses the exact same interval-overlap logic established for MAS/MRR earlier in this topic, applied here at subscription grain for churn instead of month-end snapshot for revenue.

Trade-offs & pitfalls: Validate the resulting churn rate against raw billing events directly (does the count of subscriptions with a cancellation event in the period match the "churned" count from this aggregate query) as an independent cross-check, since a subtle grain mismatch (accidentally joining on user_id instead of subscription_id somewhere in a larger pipeline) is the kind of bug that silently changes the number without an obvious symptom.

Direct answer: Bring both teams together to agree on ONE canonical "activations" definition (documenting why the other was rejected, not just declaring a winner), implement it once in the shared metrics layer, migrate both pipelines to read from that single definition, and deprecate the legacy variants on a documented timeline rather than an abrupt cutover.

Structured elaboration, the process:

1. Surface the disagreement concretely: show both teams the ACTUAL numeric gap between "first 7-day key event" and "first login + profile completion" on the same historical data, and what decisions each number has been driving, so the conversation is about impact, not abstract preference.
2. Reconcile to one canonical definition: this usually isn't picking one side wholesale; it may be a genuinely new definition informed by both (e.g., "first 7-day key event, where profile completion IS one of the qualifying key events" if that's what both teams' underlying intents actually converge on), decided by whoever has ultimate accountability for the business outcome activations is meant to represent.
3. Implement once: register the canonical definition in the metrics layer (S58/S59's registry/dbt pattern) with a single metric_id, so both product's and growth's pipelines read from the SAME underlying model rather than maintaining two independently-evolving copies.
4. Migrate consumers: update dashboards, alerts, and any downstream models (e.g., an activation-driven onboarding experiment analysis) to point at the new canonical metric, running the OLD and NEW definitions side by side for a transition window so any consumer relying on a specific legacy number can see exactly how and when it will change.
5. Deprecate legacy variants: mark the old definitions deprecated with a superseded_by pointer (S38's registry schema) rather than deleting them outright, so historical reports referencing the old name remain interpretable, and set a firm sunset date communicated well in advance.

Worked example: if "product's" and "growth's" definitions of activation disagree by 15% on a sample month, and investigation shows the gap is almost entirely users who complete profile setup without an early key event (counted by growth, not by product), the canonical definition decision is really a business judgment call about whether profile completion alone should count as "activated," not a technical dispute at all; framing it as a business decision (with the data laid out clearly) rather than a data-quality argument is what actually resolves it, since neither original definition was technically WRONG.

Trade-offs & pitfalls: Migrating all downstream consumers to a new canonical definition simultaneously is risky (any consumer with an undiscovered hard dependency on the OLD number's specific behavior breaks all at once); running both in parallel for a documented transition window, with active outreach to known consumers, minimizes the disruption this question specifically asks to minimize, at the cost of a period where two numbers legitimately coexist and need to be clearly labeled to avoid re-introducing the exact confusion this whole process exists to resolve.

Direct answer: Define MRR canonically as the sum, across all subscriptions, of a NORMALIZED monthly-equivalent price (annual and other non-monthly plans divided to a monthly rate before summing), snapshotted at month-end using the interval-overlap logic from S2, and compute upgrade/downgrade transitions as a delta against the PRIOR month-end snapshot per subscription so a mid-cycle plan change is captured exactly once, never double-counted across old and new plan rows.

Structured elaboration:

• Cross-product-line standardization: the core fix is normalizing monthly_price upstream (in a dedicated pricing/plan reference table) so a $1200/year plan becomes monthly_price_equivalent = 100, computed the SAME way regardless of which product line's billing cycle produced it; MRR's aggregation logic then never needs to know about billing-cycle differences at all, because they're resolved before MRR even reads the row.
• Proration/upgrade-downgrade without double counting: when a customer upgrades mid-cycle from a $10 to a $25 plan with a partial refund on the old plan, the correct MRR treatment is: the OLD subscription's end_date is set to the change date (so it stops contributing $10 to MRR from that point forward), and the NEW subscription STARTS at the change date (contributing $25 from then on); MRR for the transition month should show this as a contraction of $10 and an expansion of $25, not as two independently-appearing $10 and $25 rows that could be mistakenly summed together if the old row's end_date weren't correctly closed out.
• The partial refund itself: the refund amount does not directly subtract from MRR (which is a forward-looking recurring-value metric, not a cash ledger); it affects the CASH/refund-tracking metrics (as in S50's refund-attribution discussion) separately, and conflating the two would make MRR reflect one-time cash events it isn't designed to represent.

Concrete SQL logic changes:

-- 1. normalize every plan to a monthly-equivalent price once, upstream of MRR
CREATE TABLE plans (
  plan_id TEXT PRIMARY KEY,
  billing_cycle TEXT,              -- 'monthly' | 'annual' | ...
  list_price REAL,
  monthly_price_equivalent REAL    -- annual list_price / 12, computed at plan-creation time
);

-- 2. subscriptions are the MRR unit; an upgrade/downgrade closes the old row
--    and opens a new one on the same day, so they never overlap
CREATE TABLE subscriptions (
  subscription_id TEXT PRIMARY KEY,
  account_id TEXT,
  product_line TEXT,
  plan_id TEXT,
  start_date DATE,
  end_date DATE                    -- NULL = still active
);

-- 3. month-end MRR snapshot, subscription-level, via interval overlap
SELECT SUM(p.monthly_price_equivalent) AS mrr
FROM subscriptions s
JOIN plans p ON p.plan_id = s.plan_id
WHERE s.start_date <= :month_end
  AND (s.end_date IS NULL OR s.end_date > :month_end);

-- 4. invariant test: no account should have overlapping active subscriptions
--    in the same product_line at any point in time
SELECT account_id, product_line, COUNT(*) AS overlapping_active
FROM subscriptions s
WHERE s.start_date <= :month_end
  AND (s.end_date IS NULL OR s.end_date > :month_end)
GROUP BY account_id, product_line
HAVING COUNT(*) > 1;

Run against a customer who upgrades from a $10/month plan to a $25/month plan on the 15th (old row's end_date set to the 15th, new row starting the 15th) plus an unrelated $120/year subscriber (monthly_price_equivalent = 10), the month-end query correctly returns MRR of $35 with 2 active subscriptions (the closed-out $10 plan contributes nothing), and the invariant query returns zero rows, confirming no account is double counted.

Worked example: a customer on a $10/month plan upgrades to $25/month on June 15, with a partial refund of the unused portion of June's $10 charge. The MRR-relevant change: the $10 subscription's end_date becomes June 15; a new $25 subscription starts June 15. June's month-end MRR snapshot (June 30) shows only the $25 subscription as active for this customer, correctly reflecting their end-of-month recurring value; the transition is tagged as +$15 net (expansion) in an MRR-movement breakdown (new MRR, expansion, contraction, churned), which is the standard way finance-facing MRR reporting explains WHY MRR moved, not just that it moved.

Trade-offs & pitfalls: The most consequential bug here is failing to close out the OLD subscription's end_date on an upgrade, which leaves both the old and new subscription rows simultaneously "active" at month-end and double counts the customer's contribution to MRR; validate this specifically by checking that no single user_id (or account) has multiple overlapping active subscription intervals for the SAME product line at any point in time, which should be a documented invariant, tested continuously (as in S18's unit-test pattern) using the invariant query above.

Direct answer: A strong answer here follows Situation-Task-Action-Result: describe a genuinely ambiguous ask ("give me an engagement number"), the concrete steps taken to elicit precision (asking what decision it drives, proposing candidate definitions, showing each candidate's actual number), the trade-offs negotiated, and how the final, documented definition earned stakeholder trust.

Structured elaboration, the STAR shape:

• Situation: a stakeholder (e.g., a VP) requests "an engagement metric" for a new feature launch with no further specification, needed for a leadership review in two weeks.
• Task: translate that into a precise, defensible, reproducible definition in time for the review, without either delaying the review or shipping a definition that gets challenged in the room.
• Action: ask what DECISION the number needs to support (is this feature "working," should we invest more); propose 2-3 concrete candidate definitions (e.g., "any interaction," "completed the core action," "returned to use it again within a week") and show each one's actual value on the launch data so far; walk the stakeholder through the trade-offs explicitly (a broad definition looks more impressive but is less decision-relevant; a narrow one is more meaningful but smaller); document the CHOSEN definition, including the numerator, denominator, and exclusions, in a shared, discoverable place.
• Result: the stakeholder chose the narrower "completed the core action" definition specifically because it mapped to the actual investment decision, the documented definition was later reused (without a repeat of this negotiation) by two other teams reporting on the same feature, and when a skeptical exec later questioned the number in the review, the documented rationale (not an improvised justification) was what resolved the challenge on the spot.

Trust-building specifics: trust came from SHOWING the alternative candidates and their numbers rather than presenting only the final choice as if it were the obvious answer (this makes the choice look considered, not arbitrary), and from writing the definition down where others could find and reuse it (preventing the exact "two teams silently invented different definitions" drift discussed elsewhere in this topic).

Trade-offs & pitfalls: a common weak version of this story skips the "propose multiple candidates and show their numbers" step and jumps straight to a single definition, which is much less convincing in an interview (and much less effective in practice, since it doesn't actually resolve genuine ambiguity, just picks one arbitrarily); the strength of this kind of answer is specifically in demonstrating a repeatable PROCESS for resolving ambiguity, not just landing on a defensible number by instinct.

Direct answer: A metric is a quantitative measurement derived from data (a number that changes over time and can be tracked, like conversion_rate), a dimension is a categorical attribute you can slice a metric by (like user_country), and a KPI is a metric that has been elevated to a target with a business threshold attached (a metric someone is accountable for hitting a number on).

Structured elaboration:

• Metric vs dimension, concretely: page_view_count is a metric (a count you aggregate); user_country is a dimension (a label you group or filter the metric by, e.g., "page views BY country"). The same underlying event row contributes to both: the row itself is one page view (metric contribution), and it also happens to have been generated by a user in a specific country (dimension value).
• Metric vs KPI: conversion_rate is a metric whether or not anyone has set a target for it; it becomes a KPI when leadership says "we are targeting 4.5% conversion rate this quarter and someone owns hitting it." The same number, different organizational weight.
• Event-level vs aggregated: compute at event-level when the question is about individual occurrences (e.g., "which specific sessions converted"); aggregate to daily/user-level when the question is about a rate or trend over a population (e.g., "conversion rate this week").

Worked example: an e-commerce site logs a page_view event with user_id, country, page_type. page_view_count grouped by nothing is a metric (total volume); grouped by country uses country as a dimension to slice that same metric ("page views per country"); if leadership sets a target of "20% month-over-month growth in page views from a new market," page_view_count for that market becomes a KPI.

Trade-offs & pitfalls: Computing at the wrong level causes real bias: a conversion-rate metric computed at event-level (dividing total purchase events by total view events, both event counts) will be wrong if a single user can view the same page multiple times before converting once; that inflates the denominator relative to a user-level definition (unique purchasers / unique viewers), understating the true rate. The fix is not "always aggregate to user-level"; it's to be explicit about which grain the business question is actually asking, and to name that grain in the metric's documentation so nobody re-derives it inconsistently.