An analyst's pre/post query claims a release caused a 20% DAU drop. Find and fix its causal flaw.
The v5.0 app release shipped on 2024-03-10. An analyst offers this query as proof it caused the DAU drop. Constraints: the rollout is gradual by app version (column app_version), and a marketing burst and a public holiday both fell inside the same week.
-- "Proof" that the v5.0 release caused the DAU drop:
SELECT
CASE WHEN event_date < DATE '2024-03-10' THEN 'before' ELSE 'after' END AS period,
COUNT(DISTINCT user_id) / 7.0 AS avg_dau
FROM events
WHERE event_date BETWEEN DATE '2024-03-03' AND DATE '2024-03-16'
GROUP BY 1;Explain why this does not prove causation, and rewrite it as a within-window comparison.
The pre/post split confounds the release with everything else that moved that week — the holiday and the marketing burst — so a lower 'after' number is correlation, not cause. Since rollout is gradual by app version, compare updated versus not-yet-updated users on the same days.
- ✗Reading a pre/post gap as causation despite coincident changes
- ✗Fixing the window size instead of adding a counterfactual group
- ✗Slicing the same pre/post comparison instead of comparing cohorts
- →How would a staged rollout let you estimate the release effect directly?
- →What would you check if all users updated on the same day?
The pre/post split draws the line at the release date, but the release was not the only thing that changed that week: a holiday and a marketing burst landed too. So a lower 'after' average is correlation, not proof of causation. Because rollout is gradual by app_version, it gives a natural control group: users not yet on v5.0 on the same days. Compare them against updated users inside one window — the holiday and campaign, shared by both, cancel out.
-- Within-window: updated (v5.0) vs not-yet-updated, same days → isolates the release
SELECT
CASE WHEN app_version = '5.0' THEN 'updated' ELSE 'not_updated' END AS cohort,
event_date,
COUNT(DISTINCT user_id) AS dau
FROM events
WHERE event_date BETWEEN DATE '2024-03-10' AND DATE '2024-03-16'
GROUP BY 1, 2
ORDER BY event_date, cohort;If DAU falls only in the updated cohort while not_updated holds, the release is implicated. If both fall equally, the cause is a shared factor (holiday/campaign), not the release.
