Approach: compute each user's first page_view timestamp, first signup (from events or users.signup_date), and first purchase timestamp. Then count users who reach each stage within 14 days of their first page_view, compute conversion percentages vs. initial cohort and vs. previous stage, and drop-offs.sql
WITH first_events AS (
-- find first page_view, first signup, first purchase per user
SELECT
u.user_id,
MIN(CASE WHEN e.event_name = 'page_view' THEN e.occurred_at END) AS first_pv,
MIN(CASE WHEN e.event_name = 'signup' THEN e.occurred_at END) AS first_signup_event,
MIN(CASE WHEN e.event_name = 'purchase' THEN e.occurred_at END) AS first_purchase
FROM users u
LEFT JOIN events e
ON u.user_id = e.user_id
GROUP BY u.user_id
),
user_times AS (
SELECT
user_id,
first_pv,
-- prefer event signup timestamp; fall back to users.signup_date (midnight)
CASE
WHEN first_signup_event IS NOT NULL THEN first_signup_event
WHEN u.signup_date IS NOT NULL AND first_pv IS NOT NULL THEN CAST(u.signup_date AS TIMESTAMP)
ELSE NULL
END AS first_signup,
first_purchase
FROM first_events fe
JOIN users u USING (user_id)
),
stage_flags AS (
SELECT
user_id,
first_pv,
first_signup,
first_purchase,
-- within 14 days inclusive of first_pv
CASE WHEN first_pv IS NOT NULL THEN 1 ELSE 0 END AS reached_pv,
CASE WHEN first_signup IS NOT NULL
AND first_signup BETWEEN first_pv AND (first_pv + INTERVAL '14' DAY)
THEN 1 ELSE 0 END AS reached_signup_within_14d,
CASE WHEN first_purchase IS NOT NULL
AND first_purchase BETWEEN first_pv AND (first_pv + INTERVAL '14' DAY)
THEN 1 ELSE 0 END AS reached_purchase_within_14d
FROM user_times
)
SELECT
'page_view (initial)' AS stage,
SUM(reached_pv) AS users,
100.0 AS pct_of_initial,
NULL::numeric AS pct_of_previous,
NULL::numeric AS drop_from_previous
FROM stage_flags
UNION ALL
SELECT
'signup within 14d' AS stage,
SUM(reached_signup_within_14d),
CASE WHEN SUM(reached_pv)=0 THEN 0
ELSE 100.0 * SUM(reached_signup_within_14d) / SUM(reached_pv) END,
CASE WHEN SUM(reached_pv)=0 THEN 0
ELSE 100.0 * SUM(reached_signup_within_14d) / SUM(reached_pv) END,
CASE WHEN SUM(reached_pv)=0 THEN NULL
ELSE 100.0 * (SUM(reached_pv) - SUM(reached_signup_within_14d)) / SUM(reached_pv) END
FROM stage_flags
UNION ALL
SELECT
'purchase within 14d' AS stage,
SUM(reached_purchase_within_14d),
CASE WHEN SUM(reached_pv)=0 THEN 0
ELSE 100.0 * SUM(reached_purchase_within_14d) / SUM(reached_pv) END,
CASE WHEN SUM(reached_signup_within_14d)=0 THEN 0
ELSE 100.0 * SUM(reached_purchase_within_14d) / SUM(reached_signup_within_14d) END,
CASE WHEN SUM(reached_signup_within_14d)=0 THEN NULL
ELSE 100.0 * (SUM(reached_signup_within_14d) - SUM(reached_purchase_within_14d)) / SUM(reached_signup_within_14d) END
FROM stage_flags;
