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

93 practiced

Explain the churn rate for a subscription product. Provide the mathematical formula for monthly churn (simple aggregate churn), and compute churn from this small dataset: 1000 subscribers at start of month, 120 new subscribers, 80 cancellations during the month. Show your steps and assumptions.

HardTechnical

75 practiced

Discuss leading indicators of revenue for a SaaS product (e.g., trial-to-paid conversion, trial engagement). For two suggested leading indicators, explain how you'd validate empirically that they predict future revenue and how you'd implement the validation in SQL or Python.

Sample Answer

Suggested leading indicators:1) Trial-to-paid conversion rate within X days2) Trial engagement intensity (e.g., average weekly active feature-usage events per trial user in first 7 days)

Validation approach (common principles)- Define cohorts by trial start date and measure indicator in an early window (e.g., first 7 days). Measure revenue outcome in a future window (e.g., paid within 90 days or LTV at 180 days).- Use cohort-level and user-level tests: correlation, predictive modeling, and uplift/lift charts. Control for confounders (segment, MRR tier, source) and check temporal precedence.- Use metrics: AUC/ROC for classification (will convert), R² / RMSE for continuous revenue, calibration, and uplift by quantiles. Use holdout/time-split to avoid leakage.

Indicator 1 — Trial-to-paid conversionEmpirical test:- Build dataset: each trial user -> binary label converted_by_90d and feature conv_7d (converted within 7d).- Fit logistic regression and compute AUC; compute odds-ratio and lift of top decile.

SQL to create labeled table (Postgres-style):

sql

WITH trials AS (
  SELECT user_id, trial_start
  FROM events
  WHERE event='trial_start'
),
conversions AS (
  SELECT user_id, MIN(event_time) AS paid_at
  FROM events WHERE event='paid'
  GROUP BY user_id
)
SELECT t.user_id,
       CASE WHEN c.paid_at IS NOT NULL AND c.paid_at <= t.trial_start + INTERVAL '90 days' THEN 1 ELSE 0 END AS converted_90d,
       CASE WHEN c.paid_at IS NOT NULL AND c.paid_at <= t.trial_start + INTERVAL '7 days' THEN 1 ELSE 0 END AS converted_7d
FROM trials t
LEFT JOIN conversions c USING (user_id);

Python modeling:

python

import pandas as pd
from sklearn.linear_model import LogisticRegression
from sklearn.metrics import roc_auc_score

df = pd.read_parquet('trial_labels.parquet')
X = df[['converted_7d']]
y = df['converted_90d']
model = LogisticRegression().fit(X, y)
print('AUC', roc_auc_score(y, model.predict_proba(X)[:,1]))

Indicator 2 — Trial engagement intensityEmpirical test:- Compute engagement features in first 7 days: events_count, distinct_features_used, avg_session_length.- Fit regression predicting revenue_180d (log-transform skewed revenue), evaluate R² and RMSE; use SHAP or coefficients to interpret.

SQL to aggregate engagement:

sql

WITH trial AS (
  SELECT user_id, trial_start FROM events WHERE event='trial_start'
),
engagement AS (
  SELECT e.user_id,
         COUNT(*) FILTER (WHERE e.event_time <= t.trial_start + INTERVAL '7 days') AS events_7d,
         COUNT(DISTINCT e.feature) FILTER (WHERE e.event_time <= t.trial_start + INTERVAL '7 days') AS features_7d
  FROM events e
  JOIN trial t ON e.user_id = t.user_id
  GROUP BY e.user_id
)
SELECT e.*, r.revenue_180d
FROM engagement e
LEFT JOIN revenue_table r USING (user_id);

Python regression:

python

import numpy as np
from sklearn.ensemble import RandomForestRegressor
from sklearn.metrics import r2_score, mean_squared_error

X = df[['events_7d','features_7d']]
y = np.log1p(df['revenue_180d'])
model = RandomForestRegressor(n_estimators=100).fit(X, y)
pred = model.predict(X_test)
print('R2', r2_score(np.log1p(y_test), pred), 'RMSE', np.sqrt(mean_squared_error(np.log1p(y_test), pred)))

Additional checks and productionization- Time-split validation, backtesting across monthly cohorts to ensure stability.- Do sensitivity/causal checks: propensity-score matching or instrumental variables if intervention data exists.- Instrument these features into the ETL: compute and store per-trial-window features, track model performance, and alert on drift.- Document assumptions (window sizes, exclusions) and monitor for changes in product or acquisition mix that could break predictiveness.

MediumTechnical

101 practiced

Describe steps to detect whether a sudden spike in a metric is caused by an instrumentation bug. Provide a systematic checklist of queries and pipeline checks, and explain what evidence would convince you it is an instrumentation issue rather than real user behavior.

Sample Answer

Framework: treat the spike as either data or real. Triage quickly, then dig with reproducible queries and pipeline checks.

Immediate triage (first 10–30m)- Check dashboard anomalies: which metric, granularity, hosts, regions, user segments.- Correlate with upstream signals: alerts, deploys, SDK releases, config changes, external events.

Systematic checklist — Queries to run- Time-series sanity: - SELECT time_bucket('1m', ts) ts, SUM(metric) FROM events WHERE ts BETWEEN t0-1h AND t0+1h GROUP BY 1 ORDER BY 1;- Cardinality and distinct keys: - SELECT COUNT(*) total, COUNT(DISTINCT user_id) users, COUNT(DISTINCT event_id) event_ids FROM events WHERE ts BETWEEN t0-15m AND t0;- Distribution by dimension: - SELECT country, SUM(metric) FROM events WHERE ts IN spike_window GROUP BY country ORDER BY 2 DESC LIMIT 20;- Backfill / duplicate detection: - SELECT event_id, COUNT(*) FROM events WHERE ts IN spike_window GROUP BY event_id HAVING COUNT(*)>1 LIMIT 10;- Schema / malformed records: - SELECT COUNT(*) FROM raw_events WHERE parsed=false AND ts IN spike_window;- Compare ingest vs. processed counts: - SELECT SUM(raw_count) FROM raw_ingest WHERE interval=…; SELECT SUM(processed_count) FROM fact_table WHERE interval=…

Pipeline & infra checks- Recent deployments: check CI/CD timeline, database migrations, SDK releases, feature flags.- Consumer lag & retries: Kafka consumer group lag, SQS/DLQ rates, Spark job failures/restarts.- ETL job logs: parsing exceptions, increased error rows, schema evolution warnings.- Time skew: host clocks, event timestamps vs. ingestion timestamps.- Sampling & dedup config: any changes to sampling rates or dedup keys.- Host-level: surge in API gateway requests, bot IPs, health-check spikes.

What convinces me it’s instrumentation (evidence)- Sudden appearance of malformed or unparsed raw events aligned with spike.- Large increase in duplicate event_ids or identical payloads from same client_id.- Spike confined to a single SDK/agent version, client platform, or region where a new release rolled out.- No matching increase in downstream business indicators (revenue, orders) or in independent telemetry (web server logs) while ingestion metrics jump.- ETL logs showing parser changes, schema-auto-updates, or transformation errors introduced at deploy time.- Time skew or timezone bug producing concentrated timestamps.

What convinces me it’s real behavior- Concordant signals across independent systems (frontend logs, backend API metrics, DB write rates).- Increase in unique users and session lengths, not just event counts.- External events (marketing campaign, outage elsewhere) matching timing.

Next steps once confirmed- If instrumentation: roll back offending deploy/feature flag, backfill cleansed data, add test coverage and monitoring (synthetic events, schema contracts, alerting on duplicate rates).- If real: scale resources, notify stakeholders, update dashboards and pipelines for higher volume.

Communicate findings: list evidence, confidence level, immediate mitigation, and permanent fixes.

EasyTechnical

58 practiced

Write a precise metric definition for 'Daily Active User' (DAU) and 'Monthly Active User' (MAU') for a cross-device social app. Include: event(s) that count as activity, deduplication rules (per-user, per-device), timezone considerations, session timeouts, and how you handle users with multiple accounts.

Sample Answer

Definition summary:- DAU: count of unique authenticated users who performed ≥1 qualifying activity within a UTC day window (00:00–23:59 UTC).- MAU: count of unique authenticated users who performed ≥1 qualifying activity within the preceding calendar month in UTC.

Qualifying activity events:- Primary events that qualify (any one): app_open / app_foreground, successful feed_view, content_interaction (like/comment/share), message_send, media_upload, or any server-side heartbeat that indicates active presence.- Exclude passive background pings older than 30s unless paired with user-visible action.

Deduplication rules:- Primary identity = user_id (authenticated). Use user_id for deduplication across devices.- If no user_id (anonymous), fall back to device_id but mark as provisional and exclude from MAU/DAU unless later linked to user_id.- Per-user: count each user at most once per metric window (day or month), regardless of number of devices.- Per-device: do not double-count if same user triggers multiple devices; device-level counts are a separate metric.

Timezone:- Use UTC as canonical reporting timezone for DAU/MAU to avoid user-local DST ambiguity. If product requirement demands local-day DAU, compute an alternate metric using user_profile.timezone or last_known_device_timezone with clear labeling.

Session timeouts:- Define session_start on app_foreground or first qualifying event after inactivity >= 30 minutes. Activity within session does not increment DAU beyond the one-per-day user dedupe.- Session timeout used for session metrics only; it does not affect DAU deduplication except to filter background heartbeats (<30s) as non-qualifying.

Multiple accounts:- Treat each authenticated account (unique user_id) separately for DAU/MAU.- When accounts are merged (user consolidates accounts), backfill historical metrics by mapping previous user_ids to the canonical user_id and re-computing aggregates for affected windows (or annotate merged_count change).- For SSO-linked aliases, maintain an identity graph; derive counts from canonical_user_id for reporting.

Implementation notes (data engineer):- In ingestion, normalize events to include user_id, device_id, event_timestamp (ISO8601 UTC), event_type, session_id.- Deduplicate upstream by (user_id,event_date) with last-write-wins for same event timestamps.- Maintain audit logs for anonymous-to-auth transitions to re-assign historic device events when permissible.- Document assumptions and provide both UTC and user-local variants in BI for stakeholders.

MediumTechnical

67 practiced

Write an SQL query to compute a 7-day rolling average of daily DAU given an events table (events(user_id, occurred_at)). The output should be: date, dau, dau_7day_avg. Use ANSI SQL and ensure deduplication per user per day.

Sample Answer

Approach: first deduplicate events to one row per user per day, aggregate to get daily DAU, then compute a 7-day rolling average using a window function. If you need to include calendar days with zero DAU, generate or join to a calendar table (optional).

sql

-- 1) dedupe per user per day, 2) compute daily DAU, 3) 7-day rolling avg
WITH daily_users AS (
  SELECT
    CAST(occurred_at AS DATE) AS day,
    user_id
  FROM events
  GROUP BY CAST(occurred_at AS DATE), user_id
),
daily_dau AS (
  SELECT
    day,
    COUNT(*) AS dau
  FROM daily_users
  GROUP BY day
)
SELECT
  day AS date,
  dau,
  ROUND(
    AVG(dau) OVER (
      ORDER BY day
      ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    )::numeric
  , 2) AS dau_7day_avg
FROM daily_dau
ORDER BY day;

Key points:- Deduplication: grouping by CAST(occurred_at AS DATE) and user_id ensures each user counts once per day.- Rolling window: AVG(...) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) computes the average of the current day and six prior rows (7-day window).- If dates can be missing and you need true calendar continuity, join daily_dau to a calendar table (or generate_series) so the window includes zeros for missing days.Edge cases:- Time zones: ensure occurred_at cast uses correct timezone.- Sparse days: use calendar join to avoid averaging over fewer than 7 days unless that's intended.- Large data: pre-aggregate and partition appropriately for performance.

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.