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

57 practiced

A marketing campaign sampled only iOS users for a test and reported conversion uplift. Explain sampling bias and three ways it could mislead stakeholders. Propose two practical steps you would take before presenting results to executives.

EasyTechnical

55 practiced

Write a SQL query (standard SQL) that returns daily active users (DAU) for the last 30 days from an events table with columns: (user_id STRING, event_name STRING, event_timestamp TIMESTAMP). Count unique users per UTC day and return date and dau. Assume events may include duplicates across devices.

Sample Answer

Approach: Convert event_timestamp to a UTC date, deduplicate users per day, then count unique users for each of the last 30 UTC days.

sql

SELECT
  day,
  COUNT(DISTINCT user_id) AS dau
FROM (
  SELECT
    user_id,
    -- convert timestamp to UTC date; if timestamps are already UTC, DATE(event_timestamp) is fine
    DATE(event_timestamp) AS day
  FROM events
  WHERE event_timestamp >= CURRENT_DATE - INTERVAL '29' DAY
    AND event_timestamp < CURRENT_DATE + INTERVAL '1' DAY
) t
GROUP BY day
ORDER BY day;

Key points:- COUNT(DISTINCT user_id) ensures duplicates across devices/sessions are counted once per day.- The WHERE clause limits to the last 30 days (including today). Adjust bounds if you want strictly last 30 completed days (e.g., CURRENT_DATE - INTERVAL '30' DAY to CURRENT_DATE - INTERVAL '1' DAY).- If your DB stores timestamps in a different timezone, convert to UTC explicitly (e.g., CAST(event_timestamp AT TIME ZONE 'UTC' AS DATE') or use timezone-specific functions) before taking DATE().- Edge cases: null user_id rows should be filtered out if present; days with zero events won’t appear — LEFT JOIN to a calendar table if you need zero-filled dates.

HardSystem Design

42 practiced

Design a metric taxonomy and naming convention for an organization to avoid ambiguity (e.g., 'active_users_daily_v1'). Include fields such as metric_id, human-friendly name, owner, formula, aggregation frequency, version, and dependencies. Give two examples illustrating the naming rules.

Sample Answer

Requirements:- Unambiguous, discoverable, and machine- and human-friendly- Supports governance (ownership, lineage), versioning, and automation (metric catalog)- Compatible with BI tools and data pipelines

Metric record schema (fields):- metric_id: stable machine key, format MET-{domain}-{short}-{uuid4suffix} (immutable)- name: human-friendly display name (Title Case) e.g., "Daily Active Users (DAU)"- slug: programmatic short name, snake_case, lowercase, e.g., daily_active_users- owner: team/person responsible (email + role)- steward: data engineer/team for pipeline changes- description: one-sentence purpose and business intent- formula: precise definition with SQL pseudocode and windowing (include time zone)- aggregation_frequency: raw, hourly, daily, weekly, monthly- freshness_sla: e.g., "24h after day end"- datatype: integer/float/percent/currency- tags: domain, product, funnel_stage, pii_flag- dependencies: explicit list of source tables/metrics (by metric_id) and transformation steps- version: semantic versioning, vMAJOR.MINOR.PATCH (MAJOR for definition changes, MINOR for addition of derived flags, PATCH for docs)- created_at / updated_at / change_log: audit trail- approval_status: draft / approved / deprecated- examples_sql: example queries and test vectors- owner_contact and runbook_link

Naming convention rules (slug and metric_id):- slug = {aggregation}_{measure}_{dimension?} e.g., daily_active_users, monthly_revenue_gross- aggregation abbreviations: raw, hourly, daily, weekly, monthly- measure: clear noun (active_users, revenue_gross, conversion_rate)- dimension suffix when scoped: _by_country, _by_plan- avoid ambiguous words (use active vs. engaged; define in glossary)- metric_id immutable; when MAJOR change needed create new metric_id and mark old deprecated with link

Versioning & governance:- Any MAJOR definition change → create new metric (new metric_id) and reference previous version in change_log- MINOR/PATCH updates update version field; pipeline tests must pass before approval_status=approved- Dependency graph auto-generated; breaking changes require owner notification workflow

Two examples:1) DAU (product engagement)- metric_id: MET-prod-active-4f2a- name: Daily Active Users (DAU)- slug: daily_active_users- formula: COUNT(DISTINCT user_id) WHERE event_time BETWEEN day_start AND day_end AND event_type IN ('app_open','purchase')- aggregation_frequency: daily- version: v1.0.0- dependencies: raw.events, user.dim_users- owner: product-eng@example.com

2) Gross Monthly Revenue by Plan- metric_id: MET-fin-rev-9b7c- name: Monthly Gross Revenue — by Plan- slug: monthly_revenue_gross_by_plan- formula: SUM(amount) WHERE payment_status='completed' GROUP BY plan_id, month- aggregation_frequency: monthly- version: v2.0.0 (MAJOR bump because we changed inclusion rules)- dependencies: payments.transactions, billing.plans- owner: finance-analytics@example.com

Rationale: stable ids for tooling, human names for readability, slugs for code/queries, semantic versioning to prevent silent breaking changes, explicit dependencies to enable impact analysis and automated notifications.

HardTechnical

45 practiced

You must migrate metric definitions and dashboards from one BI tool to another while preserving historical continuity. Outline a migration plan that minimizes report breakage, preserves historical meaning, and includes validation steps and rollback contingencies.

Sample Answer

Goal: migrate metric definitions and dashboards to a new BI tool with zero-loss historical continuity, minimal breakage, verifiable correctness, and clear rollback paths.

Plan overview (phases):1. Discover & inventory (2 weeks)- Catalog all dashboards, reports, and metric definitions (semantic name, SQL/logic, ETL sources, owners, last-used, SLAs).- Tag metrics by business criticality and update frequency.- Export current data model, lineage, and sample snapshots.

2. Define mapping & canonical metric layer (1–2 weeks)- Create a canonical metric spec (name, description, calculation, dimensionality, time grain, timezone, null/edge handling).- Produce one-to-one mapping doc: old_metric_id → new_metric_id + transformation notes.- Resolve semantic drift (e.g., cohort vs. rolling window) with stakeholders.

3. Build & implement (2–4 weeks, parallel)- Implement metrics in target BI via a centralized semantic layer (metrics layer or transformed ETL).- Recreate dashboards using templates; preserve filters, default time windows, and visual encodings.- Instrument feature flags to route a percentage of users/queries to new dashboards for live testing.

4. Validation & QA (2 weeks)- Unit tests: run metric SQL on both systems against same historical snapshot and compare row-level and aggregate results (tolerance thresholds defined).- Backtest: compare 12+ months of historical aggregates and deltas; surface exceptions (>0.5% or stakeholder-agreed thresholds).- UAT: business owners validate key KPI dashboards; sign-off checklist per dashboard (data, labels, filters, performance).- Performance/load testing for high-volume queries.

5. Cutover & monitoring (week of cutover)- Phased cutover by non-critical → critical, using redirects and conservative time windows.- Maintain read-only access to legacy dashboards for 30–90 days.- Real-time monitoring dashboards: discrepancy rates, query failures, user adoption, and SLAs.

Validation steps (explicit):- Data parity reports: automated daily comparisons (counts, sums, uniques, null rates) during validation window.- Anomaly detection: alert on sudden drift post-cutover.- Stakeholder sign-off matrix: each critical metric requires owner approval.

Rollback & contingencies:- Pre-cutover snapshot/backups of legacy metadata and dashboard JSON.- Feature-flagged switch to fall back instantly to legacy dashboards or route queries to legacy semantic layer.- If parity fails, halt further cutover, revert feature flags, and open a postmortem with triage SLA (24–72 hours).- Post-rollback, implement fixes in a branch environment, re-run validation before retry.

Governance & communication:- Weekly stakeholder updates, escalation path, and a public migration calendar.- Training sessions, docs, and “what changed” diff reports for end users.- Post-migration audit and deprecation plan for legacy assets after stable monitoring window (recommend 60–90 days).

Success criteria:- 99% parity on critical KPIs within agreed tolerance- <1% increase in query errors or latency- Stakeholder sign-off on top 10 dashboards- No user-facing data regressions after 30 days

This plan balances technical rigor, business ownership, and reversible steps to minimize disruption while preserving historical meaning.

HardTechnical

49 practiced

Design an approach to estimate customer lifetime value (LTV) for a subscription product where cohorts are censored (newer customers haven't churned yet). Discuss methodological choices (e.g., survival analysis, discounted cash flows), assumptions you must document, and how you'd present uncertainty to leadership.

Sample Answer

Approach (summary): estimate cohort LTV by combining survival analysis (to handle right-censoring) with discounted cash flows (DCF) for revenue per active customer. This yields an expected present value of future cash flows per cohort with uncertainty quantification.

Steps:1. Define objective & horizon: e.g., 36–60 months LTV, business vs. customer-perspective.2. Data prep: cohort by signup week/month, track active/subscription status, recurring revenue per user (ARPU), upgrades/downgrades, refunds. Right-censor newer cohorts.3. Model churn with survival analysis: fit a parametric survival model (Weibull or Gompertz) or Cox PH if predictors matter (plan, acquisition channel). Survival models properly handle censored observations and estimate hazard/survival curves S(t).4. Project expected active probability p(t)=S(t). Combine with expected revenue r(t) per active user (use cohort-specific ARPU or model ARPU decay/growth).5. Discount future flows: LTV = sum_t p(t) * r(t) / (1+discount)^t. Include retention-dependent upsell and cost-to-serve subtractions as needed.6. Validate: backtest on older cohorts, holdout.

Key methodological choices and trade-offs:- Parametric vs nonparametric survival: parametric enables longer horizon extrapolation (assumes shape), nonparametric (Kaplan–Meier) is safer short-term.- Cox PH allows covariates but assumes proportional hazards—test this.- Use cohort-specific vs pooled models depending on stability and sample size.

Assumptions to document:- Functional form of survival beyond observed window.- ARPU stability or trajectory assumptions.- Discount rate and whether acquisition/cohort CAC included.- Treatment of billing cadence, promotions, and reactivations.

Presenting uncertainty to leadership:- Provide point estimates plus prediction intervals (e.g., 90% CI) from model bootstrapping or Bayesian posterior intervals.- Scenario analysis: pessimistic/base/optimistic (e.g., ±10–30% retention or ARPU changes) showing business impact (revenue, CAC payback).- Visuals: cohort LTV curve with shaded CI, waterfall of revenue minus costs, sensitivity tornado chart for top drivers (churn rate, ARPU, discount).- Actionable insights: which levers move LTV most, recommended experiments (e.g., retention campaigns), and how soon results would validate assumptions (metrics to monitor).

This approach balances statistical rigor with business clarity and gives leadership both a defensible baseline and a clear view of risks and levers.

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.