Approach: treat signup→activate→add_payment→purchase as an ordered funnel. Compute counts and step conversion rates (step-to-step) with 95% CIs, then compute absolute and relative drops between consecutive steps. Use normal approximation for CI (or Wilson) for proportions.Step 1 — counts & rates per stepsql
-- counts per step (one row per user with max step reached)
WITH user_step AS (
SELECT user_id,
MAX(CASE WHEN event='signup' THEN 1 ELSE 0 END) * 1 AS has_signup,
MAX(CASE WHEN event='activate' THEN 1 ELSE 0 END) * 1 AS has_activate,
MAX(CASE WHEN event='add_payment' THEN 1 ELSE 0 END) * 1 AS has_payment,
MAX(CASE WHEN event='purchase' THEN 1 ELSE 0 END) * 1 AS has_purchase
FROM events
WHERE event IN ('signup','activate','add_payment','purchase')
GROUP BY user_id
)
SELECT
SUM(has_signup) AS n_signup,
SUM(has_activate) AS n_activate,
SUM(has_payment) AS n_payment,
SUM(has_purchase) AS n_purchase
FROM user_step;
sql
WITH counts AS (
-- paste results from previous query or compute inline
)
SELECT
'signup->activate' AS step,
n_activate::float / n_signup AS rate,
(n_activate::float / n_signup) - 1.96*sqrt((n_activate::float/n_signup)*(1 - n_activate::float/n_signup)/GREATEST(n_signup,1)) AS lower,
(n_activate::float / n_signup) + 1.96*sqrt((n_activate::float/n_signup)*(1 - n_activate::float/n_signup)/GREATEST(n_signup,1)) AS upper
FROM counts
UNION ALL
-- repeat for activate->add_payment using n_activate as denominator, etc.
sql
WITH rates AS (
-- compute r_signup = n_activate/n_signup, r_activate = n_payment/n_activate, r_payment = n_purchase/n_payment
)
SELECT
'signup->activate' AS transition,
r_signup AS rate_prev,
r_activate AS rate_next,
(r_signup - r_activate) AS absolute_drop,
CASE WHEN r_signup>0 THEN (r_signup - r_activate)/r_signup ELSE NULL END AS relative_drop
FROM rates;
