Product analytics · SQL + Python
Queried 40K users and 303K events in PostgreSQL to find the trial-to-paid drop-off and the revenue it put at risk, then proved an onboarding fix with a controlled experiment rather than a hunch.
The question
A subscription business can look healthy on installs and still bleed at the trial-to-paid seam. The task was to locate the leak in the funnel, put a dollar figure on it, and decide whether an intervention was worth shipping.
The diagnosis
Built the analysis in SQL with CTEs and window functions to trace retention cohorts and isolate the drop-off, rather than reading a single top-line number.
Retention against churn
cohort analysis · PostgreSQL
The 37% churn translated to roughly $43K in at-risk monthly recurring revenue, which reframed the problem from a metric into a budget line.
The experiment
Rather than ship on intuition, I evaluated an onboarding variant as a controlled A/B test: a sample-ratio-mismatch check to trust the split, a two-proportion z-test for the effect, and an ARPU guardrail so a conversion win could not hide a revenue loss.
Trial-to-paid conversion, control vs variant
two-proportion z-test · SRM check · ARPU guardrail · widths illustrative
+2.9pp conversion lift, significant at p<0.001, with the ARPU guardrail intact. The recommendation was a full rollout.
The call
Ship the onboarding change, and move acquisition spend off paid. The channel comparison showed paid buying the users who converted and retained worst, while organic carried the healthiest cohorts.
How I'd measure it
Limits and next steps
Re-read the A/B result over a longer window to confirm the lift holds past the first exposure.
Confirm the paid-vs-organic quality gap survives a fair lookback, not a last-click artifact.
Split the 37% by plan and cohort so the fix targets the segment actually leaking.
Track ARPU post-rollout so the conversion win does not quietly erode revenue per user.