Find each user's first time at the first step, then look for later events of the next steps within the time limit, and count distinct users at each step. Conversion for a step is its count divided by the count at the start.
Query
WITH views AS (
SELECT user_id, MIN(event_ts) AS view_ts
FROM events WHERE event_type = 'view'
GROUP BY user_id
),
carts AS (
SELECT v.user_id, MIN(e.event_ts) AS cart_ts
FROM views v
JOIN events e
ON e.user_id = v.user_id
AND e.event_type = 'cart'
AND e.event_ts >= v.view_ts
AND e.event_ts < v.view_ts + INTERVAL '7 days'
GROUP BY v.user_id
),
buys AS (
SELECT c.user_id
FROM carts c
JOIN events e
ON e.user_id = c.user_id
AND e.event_type = 'purchase'
AND e.event_ts >= c.cart_ts
AND e.event_ts < c.cart_ts + INTERVAL '7 days'
GROUP BY c.user_id
)
SELECT (SELECT COUNT(*) FROM views) AS viewed,
(SELECT COUNT(*) FROM carts) AS carted,
(SELECT COUNT(*) FROM buys) AS purchased;Reading the result
If 10,000 users viewed, 2,000 carted and 500 purchased, then view to cart is 20 percent, cart to purchase is 25 percent, and overall conversion is 5 percent. Divide step N by step 1 for overall conversion, and by step N-1 for the drop at that step.
Decisions to say out loud
- Order matters. A cart before the first view does not count. Each step joins to the previous step's timestamp.
- Window start. The 7 days can start from the first view (as above) or from each previous step. Pick one and state it.
- No orphan purchases. Because each step starts from the previous step's users, a purchase by someone who never viewed is not counted. If you counted raw purchase events you would get conversion above what the funnel says.
- Distinct users, not events. One user adding to cart 5 times is one conversion.
A compact alternative on large data: one pass, per user, with MIN(CASE WHEN event_type = 'view' THEN event_ts END) for each step and a filter comparing the timestamps. It avoids repeated joins.