Business Metrics Definition and Strategy Questions

Emphasizes defining meaningful metrics and measurement frameworks that answer business questions and drive decisions. Candidates should be able to distinguish between count metrics, ratio metrics, and rate metrics; select appropriate observation windows and time alignment for retention, churn, and conversion analyses; account for multiple user touch points and events when attributing actions; and identify leading versus lagging indicators. This topic covers designing metric definitions that avoid double counting, selecting denominators and numerators that match the business question, segmenting users for insight, and documenting business logic to ensure consistency. At senior levels expect discussion of trade offs between simplicity and fidelity, governance of metric definitions, and how to prioritize which metrics matter for different stakeholders.

EasyTechnical

26 practiced

Define leading and lagging indicators in a business context. Provide two specific examples of leading indicators and two lagging indicators for an e-commerce business, and explain how you would use leading indicators to proactively reduce churn.

EasyTechnical

33 practiced

A product metric asks for 'total purchases' in the last 30 days, but users can make multiple purchases and orders can be updated. Describe concrete steps (rules and implementation ideas) you would take to avoid double counting purchases in the metric. Include how you would handle order updates and refunds.

Sample Answer

Situation: We need a reliable "total purchases (last 30 days)" metric that doesn’t double count repeated purchase events or updated orders.

Concrete rules and implementation:

1. Canonical identifier- Use a single order_id (or transaction_id) as the dedupe key. If multiple items per order, count at order-level or line-level per spec.

2. Define canonical event/state model- Only count an order when it reaches a definitive state (e.g., status IN ('completed','paid') and payment_confirmed = true).- Ignore transient states (created, pending).

3. Idempotent ingestion & latest-state logic- Store events in a ledger (event time, event_type, order_id). Build a deterministic materialized view that selects the latest event per order_id by event_time or version_number.- SQL example:

sql

WITH latest AS (
  SELECT DISTINCT ON (order_id) *
  FROM orders_events
  WHERE event_time >= now() - interval '35 days'
  ORDER BY order_id, event_time DESC
)
SELECT COUNT(*) AS total_purchases
FROM latest
WHERE status = 'completed' AND event_time >= now() - interval '30 days';

4. Handle updates- Use "last event wins": derive order attributes from the latest event. If an order was completed within 30 days but later updated to completed earlier/later, the latest state determines inclusion.- For attribute changes (amount, items), store both order-level totals and line-level details; aggregate from latest snapshot.

5. Handle refunds and cancellations- Treat refunds/cancellations as negative state: if latest state is refunded/cancelled, exclude or subtract from metric. For partial refunds, subtract refunded amount or mark order as partially_refunded and count net purchases (depending on business definition).

6. Windowing & event-time vs ingestion-time- Use event_time for correctness; allow a small lookback buffer (e.g., 5 days) in materialized views to avoid late-arriving events causing discrepancies; surface a “finalized” metric and a “near-real-time” preliminary metric with caveats.

7. Monitoring & audit- Instrument dashboards to show: total unique order_ids counted, number excluded due to status, late arrivals, and reconciliation queries vs raw events.- Add alerting for sudden spikes in excluded events to catch data issues.

This approach ensures deterministic, idempotent counts, clear handling of updates/refunds, and traceability for audits.

MediumTechnical

27 practiced

Write a SQL query that computes monthly churn rate defined as: percent of active subscribers at the beginning of the month who are not active at the start of the next month. Use schema:

subscriptions(user_id STRING, period_start DATE, period_end DATE, status STRING)

Discuss handling reactivations and partial-period subscriptions.

Sample Answer

Approach: determine for each user and calendar month whether they were active at the month start (i.e., they had a subscription row whose period covers the first day of that month and status = 'active'). Then check whether the same user is active at the first day of the next month. Monthly churn = (users active at month_start but not active at next_month_start) / (users active at month_start).

Example (ANSI/BigQuery-style SQL):

sql

WITH months AS (
  -- generate month starts between data range
  SELECT DATE_TRUNC(MIN(period_start), MONTH) + INTERVAL x MONTH AS month_start
  FROM subscriptions, UNNEST(GENERATE_ARRAY(0, 60)) AS x  -- adjust range as needed
  GROUP BY 1
),
user_month_activity AS (
  SELECT
    m.month_start,
    s.user_id,
    1 AS active_at_month_start
  FROM months m
  JOIN subscriptions s
    ON DATE(m.month_start) BETWEEN s.period_start AND s.period_end
   AND s.status = 'active'
  GROUP BY m.month_start, s.user_id
),
user_month_next AS (
  SELECT
    a.month_start,
    a.user_id,
    a.active_at_month_start,
    CASE WHEN b.user_id IS NOT NULL THEN 1 ELSE 0 END AS active_next_month_start
  FROM user_month_activity a
  LEFT JOIN user_month_activity b
    ON a.user_id = b.user_id
   AND DATE_ADD(a.month_start, INTERVAL 1 MONTH) = b.month_start
)
SELECT
  month_start,
  COUNT(*) AS active_begin,
  SUM(CASE WHEN active_next_month_start = 0 THEN 1 ELSE 0 END) AS churned,
  SAFE_DIVIDE(SUM(CASE WHEN active_next_month_start = 0 THEN 1 ELSE 0 END), COUNT(*)) AS churn_rate
FROM user_month_next
GROUP BY month_start
ORDER BY month_start;

Key points / reasoning:- Active at month start is defined by a subscription row covering the first day of that month with status='active'.- Reactivations: this calculation treats churn as “not active at next month start” even if the user was active again mid-month — this aligns to many product definitions that consider churn at the next reporting boundary. If you prefer to treat reactivations within the next month as non-churn, adjust to check any active period overlapping the next month (not just the first day).- Partial-periods: using BETWEEN period_start AND period_end ensures partial subscriptions that include the month start count as active. If your product bills daily or prorates mid-month, consider using any overlap with the month (e.g., period_end >= month_start AND period_start < month_end).- Edge cases: users with multiple overlapping rows should be deduped (GROUP BY), cancelled vs. paused statuses should be normalized, and month generation range should cover the data span.

EasyTechnical

27 practiced

Given a users table with fields (user_id, created_at, utm_source, country) and an events table (user_id, event_type, occurred_at), write a SQL query that returns: per acquisition channel (utm_source) — new users in the last 30 days, 7-day conversion to 'purchase', and average revenue per converting user. Discuss how you would handle unknown or null utm_source values.

Sample Answer

Approach: identify users acquired in the last 30 days, join events to detect purchases within 7 days of their created_at, aggregate counts and average revenue per converting user. I assume purchase events carry a numeric revenue column (amount); if not available, join to an orders table instead.

sql

WITH new_users AS (
  SELECT
    user_id,
    COALESCE(NULLIF(utm_source, ''), 'unknown') AS utm_source,
    created_at
  FROM users
  WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
),

purchases AS (
  SELECT
    e.user_id,
    MIN(e.occurred_at) AS first_purchase_at,
    SUM(e.amount) AS total_revenue  -- assumes amount exists only for purchases
  FROM events e
  WHERE e.event_type = 'purchase'
  GROUP BY e.user_id
),

conversions AS (
  SELECT
    n.user_id,
    n.utm_source,
    CASE WHEN p.first_purchase_at IS NOT NULL
              AND p.first_purchase_at <= n.created_at + INTERVAL '7 days'
         THEN 1 ELSE 0 END AS converted_within_7d,
    CASE WHEN p.first_purchase_at IS NOT NULL
              AND p.first_purchase_at <= n.created_at + INTERVAL '7 days'
         THEN p.total_revenue ELSE 0 END AS revenue_if_converted
  FROM new_users n
  LEFT JOIN purchases p ON p.user_id = n.user_id
)

SELECT
  utm_source,
  COUNT(*) AS new_users_last_30d,
  SUM(converted_within_7d) AS conversions_7d,
  CASE WHEN SUM(converted_within_7d) = 0 THEN 0
       ELSE ROUND( SUM(revenue_if_converted)::numeric / SUM(converted_within_7d), 2)
  END AS avg_revenue_per_converting_user
FROM conversions
GROUP BY utm_source
ORDER BY new_users_last_30d DESC;

Key points:- COALESCE(NULLIF(...,''),'unknown') normalizes null/empty UTM to a single "unknown" bucket for reporting.- If revenue isn't in events, join an orders table keyed by user_id and order_id.- Consider deduplicating multi-purchase users (we use first purchase date for conversion window; revenue sums only for users who converted within 7 days).- Validate timezones on timestamp comparisons and ensure indexes on users.created_at and events.user_id/occurred_at for performance.

MediumTechnical

33 practiced

Late-arriving events and backfills are destabilizing your dashboards: some metrics jump days after being reported. Propose pipeline and reporting strategies to reduce dashboard volatility while still surfacing corrections when they matter. Mention detection, correction windows, and UX for dashboard consumers.

Sample Answer

Situation: Dashboards are noisy because events arrive late or backfills change historical aggregates days after they were shown.

Approach (goals): reduce surprise volatility for consumers, keep correctness, and surface important corrections.

Detection- Instrument ingestion with event-time vs processing-time and maintain watermarks per source. Flag records where event_time < watermark by more than X (e.g., 24–72 hrs).- Run nightly delta-checks: compare aggregates for recent N days (e.g., 7–14) to prior snapshot; surface percent-change anomalies (> threshold) as incidents.- Maintain a “correction log” table capturing row-level deltas (pk, event_time, processed_time, old_value, new_value).

Pipeline & reporting strategies- Staging + idempotent apply: ingest into a staging table, reconcile to dimension/transaction tables using upserts so backfills are applied deterministically.- Event-time windowing: compute metrics by event_time with watermarks and use late-data policy (allowed lateness) to decide automatic reprocessing window.- Correction window (policy): define a default “soft window” (e.g., 48–72 hours) where metrics are considered provisional and can be updated automatically, and a “hard window” (e.g., 30 days) where changes require explicit backfill jobs and approval.- Incremental re-aggregation: store pre-aggregated daily shards so reprocessing touches only affected days, reducing cost and enabling fast corrections.- Separate views: expose two surfaces—“stable” (finalized older than soft window) and “live/provisional” (last 72 hrs). Let users pick context.

UX for dashboard consumers- Visual cues: badge/time-since-finalized indicator (e.g., “provisional — may change for 48h”), color-coding for provisional vs finalized numbers.- Change history: a small “corrections” pane showing recent backfill events with magnitude and direction (e.g., +5% for date X), and link to correction log.- Confidence bands / expected variance: show confidence or historical volatility for metrics so consumers interpret small fluctuations correctly.- Alerts & subscriptions: auto-notify owners when a correction exceeds a business-impact threshold (absolute or relative).- Toggle: allow users to view “as-reported at time T” vs “current corrected” to reproduce decisions made earlier.

Trade-offs & governance- Shorter windows reduce consumer confusion but may delay visibility. Pick windows by metric criticality (revenue vs pageviews).- Require SLA and ownership for backfills; maintain audit trail and approval for hard-window reprocesses.

Why this works- Detection prevents silent corrections; staging/upserts ensure correctness; correction windows balance speed vs stability; UX signals preserve trust while surfacing material changes.

Unlock Full Question Bank

Get access to hundreds of Business Metrics Definition and Strategy interview questions and detailed answers.

Join thousands of developers preparing for their dream job.