Metrics and KPI Fundamentals Questions

Core principles and practical fluency for defining, measuring, and interpreting metrics and key performance indicators, applicable across any professional domain. Candidates should be able to select meaningful metrics aligned to business objectives rather than vanity metrics, explain the difference between a metric and a target, and distinguish leading indicators from lagging indicators. Coverage includes decomposing complex outcomes into actionable component metrics, writing precise metric definitions (for example what counts as an active user, a completed case, a qualified lead, or a resolved ticket, depending on the domain), calculating common rate-based metrics such as engagement rate, churn rate, conversion rate, cycle time, or utilization rate, establishing baselines and sensible targets, and interpreting signal versus noise including awareness of statistical variability. Also includes using segmentation and cohort analysis to diagnose metric movements, and recommending two to three meaningful metrics for a hypothetical problem in the candidate's own domain with justification and action plans.

EasyTechnical

75 practiced

Explain leading indicators vs lagging indicators. For a subscription product, give three examples of each (e.g., trial activations as leading; churn as lagging). Describe how you would use leading indicators to forecast a lagging metric and who in the organization should monitor them.

MediumTechnical

51 practiced

Given subscription_events(user_id, event_type ENUM('start','cancel','renew'), event_date DATE), write SQL to calculate monthly customer churn rate using a cohort-aware definition. Explain how you will treat reactivations (customers who cancel then restart within the month) and provide SQL or pseudo-SQL implementing your choices.

Sample Answer

Approach (brief): define cohort by user’s first start month. For each cohort and calendar month, compute cohort population (users who were active at beginning of the month) and count churned users as those who had a cancel event in that month and did NOT reactivate (start/renew) within the same month (or within an agreed reactivation window). This treats rapid reactivations within the month as not churned. Provide a 30-day reactivation grace example and a simpler same-month rule.

SQL (Postgres-style, same-month reactivation treated as NOT churned):

WITH events AS ( SELECT user_id, event_type, event_date, date_trunc('month', event_date)::date AS event_month FROM subscription_events),first_start AS ( SELECT user_id, MIN(event_date) AS first_start_date, date_trunc('month', MIN(event_date))::date AS cohort_month FROM events WHERE event_type = 'start' GROUP BY user_id),monthly_events AS ( SELECT e.*, f.cohort_month FROM events e JOIN first_start f USING (user_id)),-- users who had a cancel in month but no start/renew in same month -> churnpotential_churn AS ( SELECT user_id, cohort_month, event_month AS churn_month, MAX(CASE WHEN event_type = 'cancel' THEN 1 ELSE 0 END) AS had_cancel, MAX(CASE WHEN event_type IN ('start','renew') THEN 1 ELSE 0 END) AS had_reactivation_same_month FROM monthly_events GROUP BY user_id, cohort_month, event_month),churn_flags AS ( SELECT user_id, cohort_month, churn_month, CASE WHEN had_cancel = 1 AND had_reactivation_same_month = 0 THEN 1 ELSE 0 END AS is_churn FROM potential_churn),-- cohort population: users from cohort who were active at start of month-- assume active if they had any start before or on month start and not canceled before month start permanently.-- Simplify: consider users in cohort as cohort size for first month; for subsequent months, count users who had not churned earlier.cohort_months AS ( -- list months to report SELECT generate_series(min(cohort_month), date_trunc('month', now())::date, interval '1 month')::date AS month FROM first_start),cohort_population AS ( SELECT f.cohort_month, m.month, COUNT(DISTINCT f.user_id) FILTER (WHERE f.first_start_date <= m.month + interval '1 month' - interval '1 day') AS cohort_size FROM first_start f CROSS JOIN cohort_months m WHERE f.first_start_date <= m.month + interval '1 month' - interval '1 day' -- only cohorts that exist by that month GROUP BY f.cohort_month, m.month),cohort_churn AS ( SELECT cf.cohort_month, cf.churn_month AS month, SUM(is_churn) AS churned_users FROM churn_flags cf GROUP BY cf.cohort_month, cf.churn_month)SELECT cp.cohort_month, cp.month, cp.cohort_size, COALESCE(cc.churned_users,0) AS churned_users, CASE WHEN cp.cohort_size = 0 THEN NULL ELSE round(100.0 * COALESCE(cc.churned_users,0) / cp.cohort_size,4) END AS churn_rate_pctFROM cohort_population cpLEFT JOIN cohort_churn cc ON cp.cohort_month = cc.cohort_month AND cp.month = cc.monthORDER BY cp.cohort_month, cp.month;

Explanation of choices:- Cohort = month of first start so we can track retention/churn by cohort over time.- Reactivations: here I treat any start/renew in the same calendar month as a reactivation (not churn). This avoids counting users who cancel and immediately restart.- Alternate: use a 30-day grace window — require checking next 30 days for a start; adjust potential_churn by searching for start_date <= cancel_date + interval '30 days'.Edge cases & notes:- If subscriptions have state (active until cancel effective date), prefer a subscriptions table with active intervals to compute active-at-start precisely.- Users with only cancel and never started shouldn't be in cohorts.- Define business rule for grace window (same-month vs 30/7 days). Adjust SQL accordingly.

HardTechnical

52 practiced

Provide a complete event specification for an 'order_completed' event to support revenue, funnel, and LTV metrics. Include required fields (user_id, order_id, revenue_amount, currency, timestamp), recommended dedupe keys (idempotency_token), data types, validation rules, and how to represent refunds, partial charges, and subscription renewals.

Sample Answer

Event: order_completedPurpose: Single source-of-truth event to calculate revenue, funnel conversion, and LTV. Emit on finalization of a charge/transaction (including subscription renewals). Include idempotency and refund/partial-charge representation.

Required top-level fields- event_name: "order_completed" (string)- event_version: "1.0" (string) — increment when schema changes- timestamp: ISO 8601 UTC (string) — required, e.g. "2025-11-22T12:34:56Z"- user_id: stable user identifier (string) — customer id used for LTV- order_id: merchant order id (string) — unique per customer order- revenue_amount: integer in smallest currency unit (e.g., cents) (integer) — positive for charge- currency: ISO 4217 3-letter code (string) — e.g., "USD"- idempotency_token: UUID or hash (string) — recommended dedupe key for ingestion

Recommended additional fields- payment_provider: enum/string (e.g., "stripe")- payment_intent_id: provider-specific id (string)- items: array of { sku:string, name:string, quantity:int, price_amount:int, category:string } — optional for basket-level analysis- is_subscription: boolean- subscription_id: string (if subscription)- billing_period_start / billing_period_end: ISO 8601 strings (if subscription renewal)- source: enum (web, mobile, pos)- metadata: free-form map

Refunds and partial charges- Representation: Emit one event per money movement. - order_completed with revenue_amount > 0 for charges/renewals. - order_refunded (preferred) or order_completed with revenue_amount < 0 for refunds/chargebacks.- Recommended pattern: Use dedicated event type order_refunded with fields: - refund_id (string), original_order_id, refund_amount (integer, cents, positive), currency, refund_reason (string), refunded_at (timestamp), idempotency_token.- Partial refunds: refund_amount < original revenue_amount; keep original order and items unchanged; compute net revenue by summing charges minus refunds by order_id.

Subscription renewals- Emit order_completed per renewal with is_subscription=true and subscription_id populated. Include billing_period_start/end and invoice_number. Treat renewals as normal revenue events for LTV; link by subscription_id and user_id.

Validation rules (ingestion layer)- timestamp present and parseable ISO 8601- user_id and order_id non-empty strings- revenue_amount is integer (no floats), currency is valid ISO 4217- idempotency_token unique within 24–72 hrs (or retention window) to dedupe duplicates- If is_subscription=true then subscription_id required- If revenue_amount <= 0, require event_type = "order_refunded" and refund_reason present (or use negative revenue only if whole system agreed)- items[].quantity > 0, items[].price_amount * quantity sum equals revenue_amount (allow rounding tolerance for discounts/taxes; record discount_amount, tax_amount)

Dedupe & idempotency- Primary dedupe key: idempotency_token- Secondary keys: (payment_provider, payment_intent_id) or (order_id, event_name, timestamp_window)- Store ingestion hash of payload for additional dedupe checks- Idempotency TTL: align with payment provider guarantees; commonly 7–30 days for late retry suppression

Schema example (concise JSON)

json

{
  "event_name":"order_completed",
  "event_version":"1.0",
  "timestamp":"2025-11-22T12:34:56Z",
  "user_id":"user_123",
  "order_id":"order_456",
  "revenue_amount":1999,
  "currency":"USD",
  "idempotency_token":"uuid-abc",
  "is_subscription":true,
  "subscription_id":"sub_789",
  "billing_period_start":"2025-11-01T00:00:00Z",
  "billing_period_end":"2025-11-30T23:59:59Z"
}

Measurement guidance- Compute gross revenue = sum(order_completed.revenue_amount where event_name=order_completed)- Compute refunds = sum(order_refunded.refund_amount)- Net revenue = gross - refunds- LTV: sum net revenue by user_id over lifetime window- Funnel: count unique user_id per funnel stage; use order_created → order_paid(order_completed) events, dedupe by idempotency_token/order_id

Notes and best practices- Use smallest currency unit integers to avoid float inaccuracies.- Prefer explicit refund events (order_refunded) rather than negative revenue on order_completed to simplify downstream logic.- Include event_version and maintain backward-compatible changes; document migrations.- Ensure payment provider and invoice ids to reconcile with financial systems.

EasyTechnical

56 practiced

Explain the difference between a metric and a target. Provide 2-3 concrete examples relevant to an e-commerce product team (for example, conversion rate vs target conversion percentage). Describe how you would document each item in a metrics registry (owner, canonical formula, data sources, aggregation frequency, time window) and who should own setting and reviewing targets.

MediumTechnical

62 practiced

Write a PostgreSQL query that computes daily DAU, 28-day MAU, and the DAU/MAU ratio for each day over the last 90 days using events(user_id, event_type, occurred_at TIMESTAMP, is_test BOOLEAN). Exclude test users and consider performance for a large events table; suggest optimizations.

Sample Answer

Approach: compute daily DAU (distinct users per day), compute 28-day MAU as distinct users in trailing 28-day window ending on that day, then ratio DAU/MAU. Use windowed aggregates with DISTINCT counts via grouped subqueries and efficient date bucketing. For large tables, filter by last 90 days early, use indexes, and consider pre-aggregation.

sql

WITH events_90 AS (
  SELECT user_id, occurred_at::date AS day
  FROM events
  WHERE is_test = FALSE
    AND occurred_at >= current_date - interval '89 days' -- last 90 days inclusive
),
daily_users AS (
  -- DAU per day
  SELECT day, COUNT(DISTINCT user_id) AS dau
  FROM events_90
  GROUP BY day
),
-- For MAU: expand each event into its day and then use a rolling 28-day distinct count via lateral distinct aggregation
days AS (
  SELECT generate_series(current_date - interval '89 days', current_date, interval '1 day')::date AS day
)
SELECT
  d.day,
  COALESCE(du.dau, 0) AS dau,
  COALESCE(m.mau, 0) AS mau,
  CASE WHEN COALESCE(m.mau,0)=0 THEN 0 ELSE ROUND((du.dau::numeric / m.mau)::numeric, 4) END AS dau_mau_ratio
FROM days d
LEFT JOIN daily_users du ON du.day = d.day
LEFT JOIN LATERAL (
  SELECT COUNT(DISTINCT user_id) AS mau
  FROM events_90 e
  WHERE e.day BETWEEN d.day - INTERVAL '27 days' AND d.day
) m ON TRUE
ORDER BY d.day;

Key points:- events_90 filters early to reduce scanned rows.- COUNT(DISTINCT ...) computes unique users; lateral subquery performs 28-day distinct per day.Performance & optimizations:- Index on (is_test, occurred_at) and/or (occurred_at, user_id) speeds filtering and date scans.- For very large data, maintain a daily_users summary table (date, user_id) or daily distinct user counts and a precomputed 28-day rolling MAU via incremental ETL to avoid repeated DISTINCT scans.- Alternatively use HyperLogLog (pg_trgm or extension/postgres hll) for approximate distinct counts if acceptable.Edge cases:- Days with zero users handled with COALESCE.- Timezones: cast occurred_at to date in correct timezone if needed (e.g., occurred_at AT TIME ZONE 'UTC'::date).

Unlock Full Question Bank

Get access to hundreds of Metrics and KPI Fundamentals interview questions and detailed answers.

Join thousands of developers preparing for their dream job.