The purchase funnel
A funnel shows where visitors stop on the way to buying. Product and marketing teams use it to decide what to fix first.
How it works
Start with event rows and reduce them to a reached or not reached flag for each session. Then add the session flags to get the total for each stage.
MAX(CASE WHEN event_name = stage THEN 1 ELSE 0 END)records whether a session ever reached a stage.GROUP BYthe session identity to get one row per session before you sum the flags.- Decide whether your stages mean independent reach or sequential progress before you read conversion.
Common mistake
Counting raw stage events lets retries and repeated views inflate the number of sessions that reached a stage.
Worked example
WITH raw_events AS (
SELECT 's1' AS session_id, 'view_item' AS event_name UNION ALL
SELECT 's1', 'purchase' UNION ALL SELECT 's1', 'view_item' UNION ALL
SELECT 's2', 'view_item' UNION ALL SELECT 's3', 'purchase'
), session_flags AS (
SELECT session_id,
MAX(CASE WHEN event_name = 'view_item' THEN 1 ELSE 0 END) AS viewed,
MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS purchased
FROM raw_events GROUP BY session_id
)
SELECT SUM(viewed) AS viewing_sessions, SUM(purchased) AS purchasing_sessions
FROM session_flagsMAX(CASE) turns repeated raw events into one flag per session, so the repeated view in s1 counts once.
The rollup shows two viewing sessions and two purchasing sessions.