Data Investigation and Root Cause Analysis Questions

Direct answer. When a marketing campaign or a gradual feature rollout ran at the same time a metric moved and a simple before/after comparison isn't convincing, the fix is to find or construct a comparison group and measure the CHANGE relative to that group, rather than the absolute level, so whatever else was happening in the world during that window gets netted out.

Structured elaboration. For a canaried rollout, the rollout percentage itself often creates a natural comparison: users who received the change versus users who didn't yet, ideally similar in composition since a gradual rollout is often close to random in who gets exposed first. For a marketing campaign with no natural control, look for a comparable market or segment the campaign didn't reach, and compare its trend over the same window as a proxy for what would have happened anyway. A difference-in-differences comparison, the treated group's change minus the untreated group's change over the same period, is more defensible than either group's raw before/after change alone, because it cancels out shared trends. Where a canary and a concurrent campaign overlap (both plausible causes running at once), using the rollout's own randomization as an instrument, comparing outcomes ACROSS rollout-percentage tiers while holding campaign exposure constant, can isolate the canary's effect specifically, separate from the campaign's.

Worked example. Error rates rose after a gradual rollout that overlapped with an active marketing campaign driving unusually high traffic. Comparing error rates between users who received the new rollout version and users still on the old version, WITHIN the same traffic surge (both groups experiencing the campaign's load equally), isolates the rollout's effect from the campaign's: if the new-version group's error rate is meaningfully higher than the old-version group's even under the same load conditions, that's strong evidence the rollout, not the traffic surge, is responsible, since both groups experienced the surge but only one experienced the new code.

Trade-offs and pitfalls. This only works cleanly if rollout assignment is genuinely independent of whatever else is happening (a canary that intentionally targets power users, for instance, isn't a clean comparison group anymore); check the rollout's assignment mechanism before trusting it as a natural experiment, and disclose that assumption explicitly when presenting the conclusion.

Direct answer. When an experiment's own analysis and what actually happened in production tell conflicting stories, the discrepancy itself is the thing to investigate, not a reason to distrust either signal by default; the goal is finding the specific mechanism that would make a controlled test and real-world rollout diverge.

Structured elaboration. Common mechanisms behind this exact pattern: a difference between the experiment's traffic and the FULL rollout's traffic (the experiment may have run on a subset that isn't representative of the full population, or excluded a segment that behaves differently at scale); a difference between what the experiment MEASURED and what the production regression is measuring (the experiment's primary metric may be a proxy that diverges from the downstream business metric under full-scale conditions, like server load or inventory effects that only appear past a certain traffic volume); instrumentation or assignment issues specific to the experiment (a leak between control and treatment, or a logging gap that didn't affect the full rollout the same way); or genuine heterogeneous effects, where the experiment's AVERAGE effect was null but the full population contains a subgroup with a real, sizeable negative effect that a full rollout exposes more of than the experiment's smaller, possibly-skewed sample did. Diagnosing which mechanism applies means checking each: does the production regression's affected population resemble the experiment's treatment arm demographically, was the experiment's metric definition identical to what's showing the regression in production, and does a post-hoc segment-level look at the experiment's own data (even underpowered) show a hint of the same pattern the production regression shows.

Worked example. An A/B test shows no significant effect on key metrics, but a large regression appears in production after full rollout of the same change. Checking segment-level results within the original experiment's own (underpowered but directionally informative) data shows a small, non-significant negative trend concentrated in exactly the segment that's driving the production regression; the experiment simply didn't have enough of that segment in its sample to detect the effect at conventional significance, while a full rollout exposed the entire segment at once. The conclusion isn't 'the experiment was wrong,' it's 'the experiment was underpowered specifically for this segment,' a materially different and more useful finding for how to run the next experiment.

Trade-offs and pitfalls. The instinct to distrust the experiment entirely once production diverges from it throws away a genuinely useful signal (the experiment's overall null result may still be roughly correct for the AVERAGE user); the more precise and more useful conclusion is almost always about which SPECIFIC condition (scale, segment, metric definition) caused the divergence, not a blanket verdict on either measurement.

Direct answer. A cohort retention table groups users by the period (typically week) they first showed up, then computes, for each subsequent period, what share of that original cohort is still active. The two edge cases that most often break a naive version of this query are users appearing under more than one identifier (multiple devices) and cohorts too recent to have reached later retention windows yet.

Structured elaboration. The query needs three pieces: (1) a canonical, deduplicated identity per real person, since a user active on two devices under two raw IDs should count once, not twice, in both the cohort-size denominator and the retained-users numerator; (2) a cohort assignment, the earliest period each canonical identity appears in, truncated to the chosen grain (week here); (3) a retention computation, for each cohort and each subsequent week offset, the count of distinct canonical identities with any activity in that offset week, divided by the cohort's total size. Partial or incomplete recent cohorts should either be excluded from the offsets they haven't reached yet, or explicitly marked, rather than silently shown as a retention curve that will still change.

Worked example (executed against DuckDB).

WITH cohort AS (
  SELECT canonical_user_id, MIN(DATE_TRUNC('week', signup_at)) AS cohort_week
  FROM users GROUP BY canonical_user_id
),
active_weeks AS (
  SELECT DISTINCT u.canonical_user_id,
         DATE_DIFF('week', c.cohort_week, DATE_TRUNC('week', e.event_time)) AS week_offset
  FROM events e
  JOIN users u ON u.user_id = e.user_id
  JOIN cohort c ON c.canonical_user_id = u.canonical_user_id
  WHERE DATE_DIFF('week', c.cohort_week, DATE_TRUNC('week', e.event_time)) >= 0
)
SELECT c.cohort_week, aw.week_offset, COUNT(DISTINCT aw.canonical_user_id) AS retained_users,
       (SELECT COUNT(DISTINCT canonical_user_id) FROM cohort c2 WHERE c2.cohort_week = c.cohort_week) AS cohort_size
FROM cohort c JOIN active_weeks aw ON aw.canonical_user_id = c.canonical_user_id
GROUP BY c.cohort_week, aw.week_offset ORDER BY 1,2;

Run against a small synthetic dataset (4 raw user rows, but 2 of them, user_id 1 and 2, share canonical_user_id=1, i.e. the same person on two devices, both signing up the same week), the query correctly collapses them: cohort size for the week of 2025-01-06 comes out as 3 (not 4), and week-1 retention is 3/3 = 100%, week-2 retention is 1/3 = 33% (only canonical user 1 returned in week 2). Without the canonical-ID join, the naive version would report a cohort size of 4 and silently double-count the multi-device user's activity in both the numerator and denominator, understating true per-person retention.

Trade-offs and pitfalls. The most common defect in a query like this: joining events to users on the RAW user_id for cohort assignment but then counting DISTINCT user_id (not the canonical ID) in the retention numerator, which looks correct but silently inflates numbers for any multi-device user. A second common defect is including week offsets that a recent cohort hasn't reached yet without flagging them, which makes the tail of the chart look artificially strong (only die-hard early returners have had time to show up) rather than genuinely better retention.

Direct answer. For a 10% month-over-month drop in a core conversion metric, the right posture is a structured but time-efficient triage: validate the number, run the highest-signal segmentation and funnel checks, and communicate progress WHILE still investigating, since a month-over-month cadence gives more breathing room than an overnight spike but still needs a credible interim answer well before the investigation fully concludes.

Structured elaboration. Initial validation: pull the raw conversion numerator and denominator from source events and confirm they roughly match the reporting layer's numbers, and check whether the metric's definition (attribution window, what counts as a 'conversion') changed between the two months being compared, since a silent definitional change is a common, easy-to-miss cause of a month-over-month move. Cohort and funnel analysis: break the funnel down by step to see which specific step lost the most relative conversion, and compare cohorts by acquisition month to see whether NEW users specifically convert worse than the established base, which would point at an acquisition-quality or onboarding cause rather than something affecting everyone. Instrumentation checks: verify no tracking change, software development kit (SDK) update, or schema migration landed inside the comparison window. Communication: share an interim update early describing what's confirmed and what's still being tested, distinct from a final root-cause conclusion, so stakeholders aren't waiting in silence for a full month-long analysis to finish.

Worked example. A SaaS trial-to-paid conversion rate drops from 12% to 10.8% month over month. Funnel-step analysis shows the drop is concentrated almost entirely between 'trial started' and 'payment method added,' not later in the funnel. Cohort analysis shows the drop is present in BOTH new and returning trial cohorts equally, ruling out an acquisition-quality story. Checking the release log finds a payment-form redesign shipped roughly in the middle of the comparison window. That combination (one specific step, present across cohorts, timed with a release touching exactly that step) is strong enough for an interim update naming the payment-form change as the leading hypothesis while a more definitive A/B-style comparison (pre- vs. post-redesign conversion at that step, holding traffic mix constant) confirms it.

Trade-offs and pitfalls. Month-over-month comparisons are more exposed to definitional drift (metric definitions, attribution windows, or segment eligibility rules quietly changing between releases of the reporting logic) than day-over-day comparisons are, so that check deserves more weight here than it would for an overnight spike. Resist presenting the FIRST plausible correlated release as a confirmed cause in the interim update; frame it explicitly as the leading hypothesis pending confirmation, since a month-long window increases the odds that more than one thing changed.

Direct answer. A funnel query needs to answer two related but distinct questions per step: what fraction of users convert to the next step (localizing WHERE users are lost), and how LONG it takes them to get there (localizing whether the experience itself has gotten slower, even when the eventual conversion rate looks unchanged); a single query can compute both, and the two can tell very different stories about the same funnel.

Structured elaboration. For conversion, take the first occurrence of each step event per user per cohort, count distinct users at each step, and compute the ratio between consecutive steps; users who skip a step or arrive out of order need an explicit rule (usually: only count a step as reached if its timestamp is at or after the previous step's timestamp for that user, dropping or flagging out-of-order sequences rather than silently mis-ordering them). For velocity, join each user's timestamp at one step to their timestamp at a later step and compute the time difference, then take the MEDIAN (and optionally the 90th percentile) per cohort rather than the mean, since time-between-steps is typically right-skewed by a small number of very slow or stalled users. Missing timestamps should exclude that user from the velocity calculation for that specific step pair without dropping them from the conversion-rate calculation, since the two metrics have different data requirements.

Worked example (executed in DuckDB against a synthetic dataset). Two signup cohorts one week apart, both similar in size (500 users each): the earlier cohort's activation-to-purchase conversion rate is 68.9%, the later cohort's is actually slightly HIGHER at 73.4%, a conversion-rate-only view would show no regression at all, arguably a mild improvement. But median activation-to-purchase VELOCITY goes from 1.49 hours in the earlier cohort to 8.48 hours in the later cohort, a 469% slowdown. The conversion rate alone completely misses a real, severe regression in how long the experience takes, which is exactly why this topic treats velocity as a distinct, necessary signal rather than a secondary detail: a synthetic delay injected into the later cohort's purchase timing (representing, for example, a slower payment flow) doesn't show up in the eventual conversion rate at all, only in the time-to-convert.

Trade-offs and pitfalls. Reporting only conversion rate on a funnel dashboard, the far more common default, can leave a real, user-visible regression completely invisible until it eventually shows up downstream as lower same-day revenue or worse near-term retention, by which point it's much harder to trace back to its root cause; a velocity panel alongside every conversion-rate panel closes that blind spot directly.