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

83 practiced

Write an SQL query (specify dialect: PostgreSQL) to compute a weekly active users (WAU) metric from an events table. Schema:

events(event_id PK, user_id INT, event_name TEXT, occurred_at TIMESTAMP)

Calculate WAU per calendar week and show sample results for the last 8 weeks. Deduplicate users per week.

Sample Answer

Approach: group events by calendar week (week start via date_trunc), count distinct users per week, and limit to the last 8 calendar weeks.

sql

-- PostgreSQL: weekly active users (WAU) per calendar week (week starts on Monday)
SELECT
  date_trunc('week', occurred_at)::date AS week_start,  -- calendar week start (Monday)
  COUNT(DISTINCT user_id) AS wau
FROM events
WHERE occurred_at >= date_trunc('week', current_date) - INTERVAL '7 weeks'  -- last 8 weeks including current
  AND occurred_at < date_trunc('week', current_date) + INTERVAL '1 week'    -- exclude future timestamps this week if any
GROUP BY 1
ORDER BY 1 DESC
LIMIT 8;

Key points:- COUNT(DISTINCT user_id) deduplicates users per week.- date_trunc('week', ...) gives the calendar week start (Monday) in PostgreSQL.- The WHERE clause restricts to the last 8 calendar weeks for performance.

Sample output (illustrative):week_start | wau2025-11-17 | 4,3212025-11-10 | 4,1052025-11-03 | 4,5122025-10-27 | 4,2202025-10-20 | 3,9802025-10-13 | 4,0502025-10-06 | 3,9102025-09-29 | 3,875

Edge cases / notes:- If your platform stores UTC and users are in other timezones, convert occurred_at to the desired timezone first: date_trunc('week', occurred_at AT TIME ZONE 'UTC' AT TIME ZONE 'America/Los_Angeles').- For very large tables, pre-aggregate daily or use an indexed materialized view to speed queries.- Decide whether "calendar week" should start on Sunday; adjust logic if needed.

MediumBehavioral

47 practiced

Behavioral: Describe a time when a dashboard you delivered was misinterpreted and led to a bad decision. How did you discover the misinterpretation, what actions did you take to correct the dashboard and communication, and what process changes did you implement to reduce future risk?

EasyTechnical

41 practiced

Describe how you would set data freshness and update cadence for three different dashboard types: executive weekly summary, manager daily operations, and on-call real-time alerting. Explain the business drivers that justify each cadence and the instrumentation implications.

HardTechnical

47 practiced

You notice two metrics: 'avg-revenue-per-user' (ARPU) and 'conversion-rate' moving in opposite directions. Describe three possible explanations (data or business) and a systematic approach using dashboards and data queries to determine which explanation is correct.

Sample Answer

Possible explanations (data or business):1) Cohort mix shift — cheaper or higher-LTV users changing in proportion: conversion rate rises because more low-ticket users convert, while ARPU falls because those converters spend less on average.2) Pricing or product change — a promotion or lower-priced plan increases conversion rate but reduces revenue per converted user.3) Data/measurement issue — denominator/numerator mismatch (e.g., new tracking counts more “users” or double-counts conversions), or revenue ETL lag causes ARPU to appear depressed while conversion is up-to-date.

Systematic diagnostic approach1) High-level check (dashboard): create a time-series dashboard showing ARPU, conversion rate, user count, paying-user count, total revenue. Add annotations for releases/promotions. - Query: SELECT date, users, paying_users, conversions, revenue FROM metrics_daily;2) Segment analysis: build segmented dashboards by cohort (acquisition source, campaign, geography, device) and by user lifecycle (new vs returning). - Queries: GROUP BY acquisition_channel, cohort_week: compute conversion_rate and avg_revenue_per_payer. - Look for cohorts where conversion ↑ but ARPU ↓ — indicates mix.3) Funnel & price-product analysis: dashboard by product/price tier showing conversion and avg spend per tier. Check promotion usage and coupon redemptions. - Query: revenue_by_plan = SELECT plan, SUM(revenue), COUNT(payers)...4) Data integrity checks: compare raw events vs aggregated tables, check for ETL latency, and validate currency/ refunds adjustments. - Queries: compare event-level purchases in events table to aggregated revenue for same period; check distinct user counts.5) Temporal lag and retention: analyze whether conversions are immediate but revenue recognized later (trial-to-paid delays). - Query: cohort retention and LTV over 30/60/90 days.Decision rules- If segmentation shows lower-spend cohorts driving conversion, explanation 1.- If plan-level ARPU falls with increased promo usage, explanation 2.- If aggregates mismatch or ETL lags found, explanation 3.Actionable next steps: surface findings in an executive dashboard, recommend targeted experiments (e.g., lift tests on pricing or targeted offers) and fixes (data pipeline corrections, add revenue-recognition flag).

HardTechnical

49 practiced

Hard technical: Provide a reproducible method to validate a streaming metric that counts 'active-sessions' defined as sessions with any event in the last 30 minutes. How would you test correctness, latency, and resilience to out-of-order events in a streaming pipeline?

Sample Answer

Approach summary: create a reproducible test harness that feeds deterministic event streams (controlled timestamps, IDs) into the production-like streaming pipeline and compares the pipeline’s “active-sessions (any event in last 30m)” output to a ground-truth reference implementation (batch/golden) using the same event inputs and windowing rules. Measure correctness, end-to-end latency, and behavior under out-of-order / late events.

Steps

1) Define precise spec- Active-session at time t = session_id has at least one event with event_time ∈ (t-30m, t].- Define watermark/allowed-lateness semantics used by pipeline.

2) Generate deterministic test streams- Small scenario files (CSV/JSON) with: event_id, session_id, event_time, ingest_time.- Include cases: single event, multiple events per session, events spaced >30m, bursts, duplicates, out-of-order (event_time < previous), very-late events (beyond allowed lateness).

Example test snippet (CSV rows):sessionA,2025-01-01T10:00:00Z,ingest 10:00:00sessionA,2025-01-01T10:20:00Z,ingest 10:20:00sessionB,2025-01-01T09:25:00Z,ingest 10:05:00 (out-of-order/late)

3) Ground-truth batch evaluator- For each emission timestamp T produced by the pipeline, compute expected active set with simple batch logic: expected_active_sessions(T) = distinct session_ids with any event_time in (T-30m, T]- Implement this in SQL or a small Python evaluator reading the same test file.

Example SQL (for a specific T):

sql

SELECT COUNT(DISTINCT session_id) AS expected_active
FROM events
WHERE event_time > TIMESTAMP '2025-01-01 09:30:00' AND event_time <= TIMESTAMP '2025-01-01 10:00:00';

4) Correctness assertions- For each pipeline emission (time T, metric value), assert equality to batch expected_active.- Run over many T values (every minute) to catch edge conditions.- Report mismatch rows with diff and offending events.

5) Latency measurement- Tag events with ingest_time and measure time between ingest_time and first pipeline emission that counts the session as active.- Compute P50/P90/P99 latencies; assert against SLA (e.g., P95 < 10s).- Also measure time-to-correction for late arrivals (how long until metric reflects corrected state).

6) Out-of-order / lateness resilience tests- Vary watermark and allowed-lateness: inject events out-of-order within allowed lateness → expected to be counted correctly.- Inject events beyond allowed lateness → decide expected behavior (ignored or special handling) and assert pipeline matches spec.- Test duplicates: ensure de-duplication (idempotency) does not over-count.

7) Automation & CI- Package tests as reproducible scenarios runnable in CI against dev/qa pipeline (local Flink/Beam runner or containerized pipeline).- Fail build on assertion violations; store logs and sample events for debugging.

8) Monitoring & Alerts (prod)- Add data quality checks: daily reconciliation job that compares streaming metric vs batch rollup (hourly) and alerts if >X% delta.- Instrument watermarks, lateness histogram, and count of late events.

Why this works- Deterministic input + independent batch oracle gives provable correctness.- Controlled timing and measured ingest→emit latencies quantify real SLAs.- Explicit late/out-of-order scenarios validate resilience and document expected behavior.

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.