Metrics Selection and Dashboard Storytelling Questions

Focuses on selecting metrics and designing dashboards and reports that directly support stakeholder decision making. Candidates should be able to identify distinct audiences and the specific decisions each audience must make, choose actionable metrics rather than vanity metrics, and balance leading indicators with lagging indicators as well as strategic metrics with operational metrics. This topic covers defining key performance indicators and targets and justifying each metric by the decision it enables, setting data freshness requirements and update cadence, and ensuring instrumentation and data quality to make metrics reliable. It includes dashboard architecture and visual narrative design such as layering from high level summaries to detailed drill down, tailoring views for executives, managers, and operational teams, selecting appropriate visualizations and annotations to guide interpretation, and enabling root cause analysis. Reporting practices are covered, including formatting, distribution channels, and alerting. Governance and metric definition topics include creating a single source of truth, assigning ownership, documenting definitions, and change control. Candidates must also recognize metric interactions and common pitfalls that can make metrics misleading such as aggregation bias, sampling issues, correlation versus causation, and perverse incentives, and propose mitigations. Interview questions typically ask candidates to design metric sets and dashboards for hypothetical scenarios, explain why metrics were chosen based on decisions they support, and describe cadence, distribution, drilling, and governance approaches.

EasyTechnical

58 practiced

How would you assign metric ownership and document metric definitions to create a single source of truth? Describe the fields you would include in a metric registry (for example: name, definition, SQL, owner, cadence, SLA, lineage). Explain why each field is important.

Sample Answer

Approach: I’d create a centralized metric registry and assign clear domain ownership using a RACI model (Owner = accountable, Steward = implementer/technical, Consumers = informed). Owners are typically product or business leads who understand the metric’s intent; stewards are data engineers/analysts who implement and maintain the SQL/ETL. I’d enforce an initial review and quarterly certification to keep the registry a single source of truth.

Recommended fields for each metric and why they matter:- Metric Name: concise, standardized (e.g., orders_completed_30d) — consistent references across teams.- Business Definition: plain-language intent and business rule — ensures shared understanding.- Technical Definition / Formula: explicit math or logic (e.g., COUNT(distinct order_id) WHERE status='complete') — avoids ambiguity.- Canonical SQL or Query: vetted, runnable query or reference to model — enables reproducibility.- Unit: (count, %, dollars) — prevents misinterpretation.- Owner (business): accountable person — decision authority and dispute resolver.- Steward (technical): contact for implementation/fixes — who to ping for data issues.- Cadence / Refresh Frequency: how often metric updates (daily/hourly) — sets expectations for staleness.- SLA / Data Freshness Requirement: acceptable lag and availability targets — supports operational alerting.- Source Lineage: source tables, ingestion pipelines, transformations — for debugging and trust.- Sensitivity / Access Level: PII/Confidential flags — controls access and compliance.- Status & Versioning: draft/approved/deprecated + change log — tracks evolution and prevents silent breaks.- Dashboards / Owners of Visualizations: links to canonical dashboards — directs consumers to approved views.- Tags / Domain: product, finance, marketing — aids discovery and governance.- Last Reviewed / Next Review Date: governance cadence reminder.

Why this matters: Explicit owners + clear technical and business definitions reduce duplicated metrics, fix “whose metric is right” debates, and improve trust. Lineage and SQL make metrics auditable; cadence and SLAs drive operational monitoring; sensitivity and access protect compliance. Together these fields turn ad-hoc numbers into a reliable single source of truth.

EasyTechnical

57 practiced

Using PostgreSQL, write a query to compute daily active users (DAU) for the last 14 days. Schema: events(user_id BIGINT, event_name TEXT, occurred_at TIMESTAMP). Return one row per date (date) with dau (distinct users) and include dates with zero activity.

Sample Answer

Approach: generate a series of the last 14 dates, left join aggregated distinct-user counts from events (by date), and coalesce to zero for days with no activity.

sql

WITH days AS (
  -- last 14 days including today
  SELECT generate_series::date AS date
  FROM generate_series(current_date - INTERVAL '13 days', current_date, INTERVAL '1 day')
),
dau AS (
  -- distinct user count per date
  SELECT
    occurred_at::date AS date,
    COUNT(DISTINCT user_id) AS dau
  FROM events
  WHERE occurred_at >= current_date - INTERVAL '13 days'
    AND occurred_at < current_date + INTERVAL '1 day'  -- optional guard
  GROUP BY 1
)
SELECT
  d.date,
  COALESCE(a.dau, 0) AS dau
FROM days d
LEFT JOIN dau a USING (date)
ORDER BY d.date;

Key points:- generate_series produces every date so days with zero activity appear.- occurred_at::date groups events by calendar date (adjust if you need timezone-aware grouping).- COUNT(DISTINCT user_id) yields unique active users per day.Edge cases:- If your events timestamps are in UTC but you want local dates, convert with occurred_at AT TIME ZONE '<tz>' before casting to date.- For very large tables, consider pre-aggregated tables or adding an index on (occurred_at, user_id) for performance.

MediumTechnical

45 practiced

Using BigQuery or ANSI SQL, write a query that computes weekly retention cohorts for the last 12 weeks. Schema: events(user_id STRING, event_name STRING, occurred_at DATE). Output: cohort_week (date of first event week), week_offset (0..n), retention_rate as distinct users in week_offset / distinct users in cohort. Explain key steps in comments or prose.

Sample Answer

To compute weekly retention cohorts (cohort = week of first event, retention by week offset) for the last 12 weeks in BigQuery / ANSI SQL:

sql

-- 1) Determine each user's cohort: the week (date) of their first event
WITH first_events AS (
  SELECT
    user_id,
    DATE_TRUNC(MIN(occurred_at), WEEK) AS cohort_week
  FROM events
  GROUP BY user_id
),

-- 2) Keep cohorts within the last 12 weeks (including current week)
recent_cohorts AS (
  SELECT *
  FROM first_events
  WHERE cohort_week >= DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 11 WEEK), WEEK)
),

-- 3) Join events to cohorts and compute week_offset (0 = cohort week)
events_with_offset AS (
  SELECT
    rc.cohort_week,
    e.user_id,
    DATE_TRUNC(e.occurred_at, WEEK) AS event_week,
    DATE_DIFF(DATE_TRUNC(e.occurred_at, WEEK), rc.cohort_week, WEEK) AS week_offset
  FROM recent_cohorts rc
  JOIN events e
    ON e.user_id = rc.user_id
  -- only consider events on/after cohort week
  WHERE e.occurred_at >= rc.cohort_week
),

-- 4) Count distinct users per cohort_week and week_offset
cohort_counts AS (
  SELECT
    cohort_week,
    week_offset,
    COUNT(DISTINCT user_id) AS active_users
  FROM events_with_offset
  WHERE week_offset >= 0  -- ignore negative offsets
  GROUP BY cohort_week, week_offset
),

-- 5) Baseline cohort sizes
cohort_sizes AS (
  SELECT
    cohort_week,
    COUNT(DISTINCT user_id) AS cohort_size
  FROM recent_cohorts
  GROUP BY cohort_week
)

-- 6) Final retention rates (as fraction or percentage). You can limit week_offset to e.g., 0..11 as needed.
SELECT
  c.cohort_week,
  c.week_offset,
  c.active_users,
  s.cohort_size,
  SAFE_DIVIDE(c.active_users, s.cohort_size) AS retention_rate
FROM cohort_counts c
JOIN cohort_sizes s
  ON c.cohort_week = s.cohort_week
ORDER BY c.cohort_week DESC, c.week_offset;

Key steps explained:- Compute each user's first event week → cohort_week.- Filter to cohorts from the last 12 weeks (current week and previous 11).- Join events back to cohort users and compute week_offset using DATE_TRUNC + DATE_DIFF.- Aggregate distinct users per cohort/week_offset and divide by cohort size to get retention_rate.- SAFE_DIVIDE avoids divide-by-zero. Adjust DATE_TRUNC WEEK behavior if your DB uses Sunday-based weeks — BigQuery's DATE_TRUNC(..., WEEK) uses Monday as week start.

MediumTechnical

51 practiced

Sampling is used to reduce cost when querying a very large events table. Describe how sampling can bias metric calculations. Outline methods to detect sampling bias, adjust calculations to correct bias (weighting, stratified sampling), and practical guidelines to communicate sampling caveats to dashboard consumers.

Sample Answer

Sampling can bias metrics because the sample may not represent the population—leading to under/overestimation of rates, means, or trends. Common biases: time-based sampling that misses peak periods, user-segment undercoverage (e.g., mobile vs. desktop), and event-size bias (large sessions more/less likely to be sampled).

Detecting sampling bias:- Compare sample vs full-population on known fields (time-of-day, geography, user cohort) and look for systematic differences.- Compute sample completion rates vs historical unsampled baselines (if available).- Run A/B checks: run periodic full scans for a small window and compare metric differences to sampled queries.- Use visualization (histograms, QQ plots) to spot distribution shifts.

Adjusting calculations:- Weighting: apply inverse-probability weights w = 1 / p(sample|stratum). If mobile users are sampled at 50% and desktop at 10%, weight mobile events by 2 and desktop by 10 to recover unbiased estimates.- Post-stratification: partition by strata (time, platform, geography), compute stratum-level estimates, then aggregate weighted by stratum population proportions.- Stratified sampling design: enforce proportional or Neyman allocation up front to reduce variance for important strata.- Variance estimation: account for weights when computing standard errors (use weighted bootstrap or survey formulas).

Practical guidelines for dashboards:- Annotate dashboards with sampling rate, sampling method, and last full-scan baseline.- Show confidence intervals or margin-of-error computed from weighted variance.- Flag low-volume strata or unstable estimates and avoid showing absolute numbers when sample fraction < threshold.- Provide an “unsampled” toggle or periodic full-run snapshot for critical decisions.- Educate consumers: explain when sampled metrics are directional vs production-quality and document limitations in metadata.

This approach helps detect bias, correct estimates where possible, and keep stakeholders informed so decisions account for sampling uncertainty.

HardTechnical

58 practiced

You are asked to forecast next quarter's revenue and present it in a dashboard with confidence intervals and leading indicators that feed the forecast. Outline your modeling approach (candidate models), required features and data, validation strategy, how to present uncertainty to executives, and which dashboard elements would let users explore forecast drivers.

Sample Answer

Modeling approach (candidate models)- Start with a baseline: decomposition + linear regression on trend/seasonality (interpretable).- Time-series models: SARIMA/ETS for univariate baseline.- Machine-learning: Gradient Boosting (XGBoost/LightGBM) on lag features and external covariates for nonlinearity.- Probabilistic models: Bayesian Structural Time Series (BSTS) or Prophet (seasonal + holidays + uncertainty).- Hybrid: ensemble of SARIMA/ETS + GBM + BSTS to combine strengths and produce calibrated intervals.

Required features and data- Historical revenue by period (weekly/daily/monthly) — cleaned, consistent calendar.- Granular drivers: units sold, average price, returns.- Leading indicators: web traffic, MQLs/leads, ad spend, promotions calendar, inventory levels, supply delays.- Macroeconomic: CPI, consumer sentiment, seasonality flags, holidays.- Campaign metadata: channel spend, creative changes, targeting.- Data quality: null handling, outlier tags, backfilled corrections, consistent business rules.

Validation strategy- Time-series cross-validation (rolling-origin / expanding window).- Backtest on multiple historical quarters; measure MAPE, sMAPE, RMSE and calibration of intervals (coverage).- Feature importance and partial dependence to check model behavior.- Test scenario/what-if: simulate changes in leading indicators to ensure responsiveness.- Calibration: use quantile regression or bootstrapping to get well-calibrated CIs; check empirical coverage.

Presenting uncertainty to executives- Show point forecast with 50% and 90% confidence bands; annotate likelihood statements (e.g., “70% chance revenue between X and Y”).- Translate uncertainty into business actions: downside scenario with required mitigations, upside opportunity with resource asks.- Use simple visuals: single-line plot with shaded bands + three scenario cards (best/base/worst) with numeric impacts and recommended actions.

Dashboard elements for exploring drivers- Main time-series with selectable confidence bands and model version toggle.- Driver breakdown: waterfall or contribution chart showing how each leading indicator shifts forecast vs baseline.- Interactive what-if controls (sliders) for top 3 drivers (ad spend, traffic, price) that recompute near-real-time forecasts.- Feature importance / SHAP summary panel and PDP for top features.- Backtest tab: historical prediction vs actual with rolling error metrics and coverage statistics.- Data quality indicators and provenance links for transparency.

This approach balances interpretability, robustness, and actionable storytelling so executives can see both the forecast and the levers to move it.

Unlock Full Question Bank

Get access to hundreds of Metrics Selection and Dashboard Storytelling interview questions and detailed answers.

Join thousands of developers preparing for their dream job.