Growth and Product Metrics Analysis Questions

Analysis skills specific to growth and product contexts: interpreting funnel metrics, cohort and retention analyses, attribution of acquisition versus activation, detecting seasonality and external event impacts, and diagnosing conversion or engagement changes. Candidates should be able to form hypotheses about what drove changes, propose targeted follow up analyses or A B tests, and identify which additional metrics are needed to evaluate unit economics and growth efficiency.

MediumTechnical

41 practiced

On Feb 10 you observe a 20% drop in signup→activation conversion for one country. Outline a step-by-step diagnostic plan: which queries, segments, and visualizations you would run first; possible root causes; and short-term mitigations to propose to stakeholders.

Sample Answer

High-level approach: triage quickly to confirm the drop, narrow scope (country, date, cohort), identify correlated signals, generate hypotheses, run targeted queries/visuals, propose short-term mitigations and next steps.

1) Confirm & scope- Run basic KPI time series to confirm magnitude and timing:

sql

-- daily signup→activation rate by country
SELECT date(created_at) AS day, country,
  SUM(CASE WHEN status='activated' THEN 1 ELSE 0 END)::float / COUNT(*) AS activation_rate,
  COUNT(*) AS signups
FROM users
WHERE date(created_at) BETWEEN '2025-01-25' AND '2025-02-20'
GROUP BY day, country
ORDER BY day;

Visualization: line chart with CI and traffic volume (dual axis).

2) Segment & compare- By device, OS, browser, acquisition channel, signup method, language, time-of-day.- Example query for device/OS:

sql

SELECT device_type, os, 
  SUM(activated)::float/COUNT(*) AS rate, COUNT(*) AS n
FROM users
WHERE country='X' AND date(created_at) BETWEEN '2025-02-01' AND '2025-02-11'
GROUP BY device_type, os
ORDER BY n DESC;

Visualization: stacked bar or heatmap to spot buckets with sharp drops.

3) Cohort & funnel diagnostics- Funnel step-level conversion (signup → email sent → email opened → activation).- Check event pipeline/log delays.- Query for event completion times and error rates (API errors, 4xx/5xx).

4) Compare control groups- Compare same-country prior week and neighboring countries same dates to see if it's regional or global.- AB-test overlap: did an experiment target that country or rollout change?

5) Data quality & instrumentation- Validate data freshness and schema changes: missing events, duplicate user IDs, timezone issues.- Check ETL/job failures on Feb 10.

6) Likely root causes- Product regressions: release on/around Feb 10 introducing a bug (signup button, activation email send).- Third-party outages: email provider, SMS, verification service.- Traffic quality: sudden change in acquisition source with lower intent or bots.- Localization/regulatory change: blocked domains or legal restrictions.- Data/instrumentation bug: dropped activation events or mis-tagged country field.

7) Short-term mitigations to stakeholders (first 24–48h)- Roll back suspicious deploys or pause experiments for the country.- Pause/inspect traffic from new acquisition channels or geosource if low quality suspected.- Switch to backup provider if third-party outage (email/SMS).- Manually confirm activation path for a sample of signups; open support monitoring to assist users.- Communicate: send concise alert with magnitude, affected segment, immediate actions, and ETA for deeper RCA.

8) Next steps & monitoring- Set alert on signup→activation rate, device/OS segments, and third-party error metrics.- Deep RCA: correlate server logs, SDK versions, release notes, campaign starts.- Post-mortem with owners and implement preventive tests (synthetic signups, better monitoring of upstream services).

This plan focuses on rapid validation, targeted segmentation, instrumentation checks, and actionable mitigations while preserving evidence for a full root-cause analysis.

EasyTechnical

50 practiced

You have the following small dataset to compute a retention matrix in Excel:

User | signup_date | activity_dateA | 2024-01-01 | 2024-01-01A | 2024-01-01 | 2024-01-08B | 2024-01-03 | 2024-01-10C | 2024-01-03 | 2024-01-03

Explain step-by-step how to build a weekly retention matrix in Excel using pivot tables and formulas. What Excel features (functions or pivots) would you use to convert dates into cohort-week and activity-week columns?

Sample Answer

Situation: You need a weekly retention matrix (cohort = users by signup week; columns = weeks since signup; values = users active that week). Here’s a clear step-by-step Excel approach using helper columns, a PivotTable (with Distinct Count via Data Model) and simple date math.

1) Prepare raw table- Put your rows in an Excel Table (select range → Insert → Table). Name it tblActivity.

2) Add helper columns in the Table- CohortWeekStart (the Monday/first day of the signup week): =[@signup_date] - MOD(WEEKDAY([@signup_date],2)-1,7) (WEEKDAY(...,2) makes Monday=1. This returns the week-start date for each signup.)- WeeksSinceSignup (activity-week index: 0 = same signup week, 1 = next week, etc.): =INT(([@activity_date] - [@CohortWeekStart]) / 7) (Returns 0,1,2... — use MAX(0,...) if negative dates possible.)- CohortLabel (readable cohort, e.g. "2024-01-01"): =TEXT([@CohortWeekStart],"yyyy-mm-dd")

3) Add table to Data Model for Distinct Count- Select any cell in the table → Insert → PivotTable → check “Add this data to the Data Model” and place pivot on a new sheet. Adding to Data Model enables Distinct Count aggregation.

4) Build the PivotTable (cohort retention)- Rows: CohortLabel (or CohortWeekStart)- Columns: WeeksSinceSignup- Values: User → Value Field Settings → Distinct Count (this counts unique users active in that cohort-week)- Optional: Format values as percentages by dividing by cohort size (you’ll need cohort sizes as a separate measure).

5) Calculate cohort size and retention %- Create a second Pivot or use measures: - CohortSize: in the Pivot, put User as Distinct Count but filter Columns to WeeksSinceSignup = 0 (or in Power Pivot create a measure counting users where WeeksSinceSignup=0). - Retention % formula (outside pivot): =PivotValue / CohortSize Or add a calculated field in Power Pivot: Retention = DISTINCTCOUNT(User) / CALCULATE(DISTINCTCOUNT(User), FILTER(tblActivity, WeeksSinceSignup=0))

6) Presentation- Format columns 0,1,2,... as week 0, week 1, etc.- Convert numbers to percentages and optionally apply conditional formatting heatmap.

Key Excel features used:- Table (structured references)- WEEKDAY (with mode 2) + simple arithmetic to get week start- INT((date1 - date2)/7) to compute week index- PivotTable with “Add this data to the Data Model” to use Distinct Count- Power Pivot / Data Model measures (optional) for clean retention % calculations- Conditional formatting for visualization

Edge notes:- If your business defines weeks differently (Sunday start or ISO weeks) adjust WEEKDAY mode or use WEEKNUM.- If Excel version lacks Data Model, you can get unique counts via helper column marking first activity per user-week and then SUM with Pivot, or use Power Query to deduplicate user-week pairs before pivoting.

MediumTechnical

36 practiced

You must design an A/B test: baseline conversion 5%, you want to detect a 10% relative uplift (i.e., from 5.0% to 5.5%) with 80% power and α=0.05. Walk through how to compute required sample size and how to translate sample size into test duration given average daily traffic of 100,000 eligible users. Explain trade-offs of MDE, power, and test duration for growth teams.

Sample Answer

Approach (formula + reasoning)To size a two-arm A/B test for proportions we use the normal-approximation sample-size formula:n_per_group = ((z_{1-α/2} + z_{1-β})^2 * [p1(1-p1) + p2(1-p2)]) / (p2 - p1)^2This comes from requiring the z-statistic for the difference in proportions to exceed the α threshold with power 1-β.

Calculation (numbers)- Baseline p1 = 0.05- Target p2 = 0.055 (10% relative uplift → absolute d = 0.005)- α = 0.05 → z_{1-α/2} = 1.96- Power 80% → z_{1-β} = 0.84

\`\`\`pythonimport mathp1, p2 = 0.05, 0.055z_alpha, z_beta = 1.96, 0.84d = p2 - p1num = (z_alpha + z_beta)**2 * (p1*(1-p1) + p2*(1-p2))n_per = num / d**2n_per, n_per*2\`\`\`

Result: n_per ≈ 31,228 per group → total ≈ 62,456 users.

Translate to durationWith 100,000 eligible users/day and 50/50 split, you expose ~50,000 users/day per arm → total ~100,000/day. You need ~62k users total, so run time ≈ 0.63 days. Practical considerations (ramp, weekly cycles, QA) mean you should run at least 7–14 days to cover weekday/weekend behavior, ensure stability, and observe retention/secondary metrics.

Trade-offs for growth teams- MDE vs sample size: smaller MDE (more sensitivity) → n grows ~1/d^2. Detecting tiny relative lifts is expensive.- Power vs duration: higher power increases n (z_{1-β} grows), longer tests or larger traffic required.- α choice: lower α (stricter) increases n; for many quick experiments teams sometimes accept higher α for faster learning but risk false positives.- Operational constraints: seasonality, novelty effects, QA ramps, and multiple experiments reduce effective traffic; account for these.- Alternatives: sequential methods (group sequential, alpha spending), Bayesian A/B, or increase metric sensitivity (e.g., use improved metrics or covariate adjustment) to reduce required sample size.

RecommendationCompute raw sample size as above, then inflate for noncompliance, bots, and to cover temporal effects. If the required duration is impractically long, consider raising MDE, accepting lower power, or using more sensitive metrics/analysis techniques.

MediumTechnical

42 practiced

Given two years of daily signups, describe methods to detect seasonality and quantify the impact of an external event (e.g., a marketing promotion or holiday). Discuss time-series decomposition, intervention analysis, and which visualization(s) you would show to stakeholders.

Sample Answer

Approach overview:- Start by exploring the raw series, then use decomposition and statistical intervention methods to quantify the event effect and test significance. Provide clear visuals and simple metrics (lift %, cumulative incremental signups) for stakeholders.

Step 1 — exploratory checks- Plot daily signups with a 7‑day and 30‑day rolling average to reveal weekly and monthly patterns.- Plot weekday boxplots and monthly boxplots to confirm intra-week and monthly seasonality.- Compute ACF/PACF to detect periodic lags (e.g., strong lag-7 indicates weekly seasonality).

Step 2 — time-series decomposition- Use STL decomposition (robust to changing seasonality): obs = trend + seasonal + remainder.- Show decomposition panels: observed, trend, seasonal, residual. This isolates baseline trend and repeating patterns so the event’s deviation shows up in the residual.

Step 3 — quantify event impact (intervention analysis)- Simple: Interrupted time series (ITS) regression: - Model: signups_t = β0 + β1*time + β2*post_event + β3*time_post + seasonal dummies + ε - β2 estimates immediate level change; β3 estimates slope change after event.- ARIMA/GARCH with intervention dummy if residual autocorrelation persists; use SARIMAX with exogenous event dummy.- Bayesian / counterfactual: use Google’s CausalImpact or Bayesian structural time series to estimate the counterfactual and credible intervals.- If you have a control group (geography/segment), run difference-in-differences to control for concurrent shocks.

Step 4 — metrics and statistical significance- Report immediate lift (%) = (observed_post - predicted_counterfactual_post)/predicted.- Report cumulative incremental signups and p-values / credible intervals.- Check residual diagnostics (white noise) and sensitivity to window choice.

Visualizations to show stakeholders- Top: annotated daily time-series with event window highlighted and 7‑day/30‑day smoothed lines.- Decomposition plot (observed, trend, seasonal, residual) to explain seasonality separation.- Counterfactual vs actual plot with shaded confidence/credible band showing estimated incremental impact.- Weekly heatmap or weekday boxplots to illustrate recurring patterns.- Table or small bar chart summarizing immediate lift %, cumulative incremental signups, and statistical significance.

Key considerations and caveats- Control for other concurrent events (campaign overlap, product changes).- Use multiple approaches (ITS + SARIMAX + CausalImpact) to validate robustness.- Communicate uncertainty clearly — show confidence/credible intervals and sensitivity analyses.

HardTechnical

48 practiced

A weekly funnel computation on a table with 1B+ event rows is too slow. Describe SQL and engineering optimizations to compute the funnel efficiently: materialized views, pre-aggregations, approximate distincts (HyperLogLog), partitioning, clustering, and incremental pipelines. Provide example SQL patterns or architecture suggestions.

Sample Answer

To compute a weekly funnel over 1B+ event rows efficiently, combine data-model, SQL and pipeline optimizations: incremental pre-aggregation, partitioning/clustering, approximate aggregations, materialized views, and streaming/batch pipelines. Approach: pre-aggregate user-event-day (or session) level records offline, maintain incremental updates, and run funnel logic on the much-smaller aggregation table. Use approximate distincts for user counts when high cardinality is acceptable.

Example pipeline/architecture:- Raw events -> partitioned append-only table (by event_date)- Daily job: aggregate to user_day_events(user_id, event_date, event_set_bitmap or stage_flags, hll_user) and write to partitioned preagg table- Weekly funnel job: join/sequence on preagg table (orders of magnitude smaller)

Example SQL patterns:

1) Daily pre-aggregation (compute user-stage flags + HLL)

sql

INSERT INTO preagg.user_day_events PARTITION(event_date)
SELECT
  user_id,
  event_date,
  MAX(CASE WHEN event_type='view' THEN 1 ELSE 0 END) AS seen_view,
  MAX(CASE WHEN event_type='add_to_cart' THEN 1 ELSE 0 END) AS seen_cart,
  MAX(CASE WHEN event_type='purchase' THEN 1 ELSE 0 END) AS seen_purchase,
  HLL_ADD_AGG(hll_hash(user_id)) AS users_hll
FROM raw.events
WHERE event_date = CURRENT_DATE - INTERVAL '1' DAY
GROUP BY user_id, event_date;

2) Weekly funnel using preagg + HLL to estimate uniques

sql

WITH week AS (
  SELECT user_id,
         MAX(seen_view) AS view,
         MAX(seen_cart) AS cart,
         MAX(seen_purchase) AS purchase
  FROM preagg.user_day_events
  WHERE event_date BETWEEN '2025-11-24' AND '2025-11-30'
  GROUP BY user_id
)
SELECT
  HLL_COUNT(HLL_UNION_AGG(hll_hash(user_id))) FILTER (WHERE view=1) AS est_views,
  HLL_COUNT(HLL_UNION_AGG(hll_hash(user_id))) FILTER (WHERE cart=1) AS est_carts,
  HLL_COUNT(HLL_UNION_AGG(hll_hash(user_id))) FILTER (WHERE purchase=1) AS est_purchases
FROM week;

3) Exact funnel with ordering (if sequence matters) run on preagg user-session rows (smaller):

sql

SELECT
  COUNT(DISTINCT user_id) FILTER (WHERE step1_time IS NOT NULL) AS step1,
  COUNT(DISTINCT user_id) FILTER (WHERE step2_time IS NOT NULL AND step2_time > step1_time) AS step2,
  ...
FROM (
  SELECT user_id,
         MIN(CASE WHEN event_type='view' THEN event_ts END) AS step1_time,
         MIN(CASE WHEN event_type='add_to_cart' THEN event_ts END) AS step2_time,
         MIN(CASE WHEN event_type='purchase' THEN event_ts END) AS step3_time
  FROM preagg.user_session_events
  WHERE event_date BETWEEN ...
  GROUP BY user_id, session_id
) s;

Engineering optimizations and notes:- Partition raw and preagg tables by event_date to prune scans; cluster by user_id/session_id to speed GROUP BY and joins.- Use incremental materialized views or scheduled/CDC-based pipelines to update preagg tables instead of full recompute.- Use HLL (HyperLogLog) for weekly unique-user estimates; keep exact counts for key cohorts if required.- Use bitmaps/tiny bitsets for multi-stage presence per user/day to encode stages compactly.- For sequence-sensitive funnels, precompute first-timestamps per (user, stage) to avoid scanning raw events.- Monitor HLL error (~1-2%) and provide confidence intervals; fallback to exact queries for small cohorts.- Use query hints, proper stats, and limit cross-partition joins. Consider OLAP engines (BigQuery/Redshift/Snowflake) features: clustering keys, materialized views, result caching, and autoscaling.

Edge cases:- Late-arriving events: implement watermarking and reprocessing windows.- User identity changes: deduplicate user_id upstream.- Small segment accuracy: avoid HLL for tiny cohorts; compute exact.

This reduces scan volume from billions to millions, enabling weekly funnel runs in minutes rather than hours.

Unlock Full Question Bank

Get access to hundreds of Growth and Product Metrics Analysis interview questions and detailed answers.

Join thousands of developers preparing for their dream job.