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.

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_flags

MAX(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.

All BigQuery lessons