GA4 funnels in BigQuery
Build a GA4 funnel by turning events into one row per session, with a flag for each stage. An open funnel counts sessions that reached a stage. A closed funnel uses timestamps to require the stages in order.
Practice this in a free lesson on learn.querylab.io: The purchase funnel.
A funnel in the GA4 export starts by turning events into one row per session, with a 0 or 1 for each stage: MAX(IF(event_name = 'add_to_cart', 1, 0)). Summing each flag gives the number of sessions that reached that stage. This is an open funnel: a session counts at a stage even if it skipped the ones before. A closed funnel compares timestamps, so a stage counts only if it happened after the previous one.
Session keys are built as in GA4 sessions. The standard ecommerce events are view_item, add_to_cart, begin_checkout and purchase. Filter to those in the first step, because events you don’t need add rows to shuffle, though not bytes to bill.
Count sessions, not events
A session that adds three products sends three add_to_cart events. Counting events makes it look as if every viewer added to cart:
WITH events AS ( SELECT * FROM UNNEST([ STRUCT('a-1' AS session_key, 'view_item' AS event_name), ('a-1', 'add_to_cart'), ('a-1', 'add_to_cart'), ('a-1', 'add_to_cart'), ('b-1', 'view_item'), ('c-1', 'view_item') ]) ) SELECT COUNTIF(event_name = 'view_item') AS view_events, COUNTIF(event_name = 'add_to_cart') AS cart_events, COUNT(DISTINCT IF(event_name = 'add_to_cart', session_key, NULL)) AS cart_sessions FROM events
| Row | view_events | cart_events | cart_sessions |
|---|---|---|---|
| 1 | 3 | 3 | 1 |
An open funnel
The per_session step collapses each session to one row of flags, and the outer query adds them up:
WITH events AS ( SELECT * FROM UNNEST([ STRUCT('a-1' AS session_key, 'view_item' AS event_name), ('a-1', 'add_to_cart'), ('a-1', 'purchase'), ('b-1', 'view_item'), ('b-1', 'add_to_cart'), ('c-1', 'view_item'), ('d-1', 'add_to_cart'), ('d-1', 'add_to_cart') ]) ), per_session AS ( SELECT session_key, MAX(IF(event_name = 'view_item', 1, 0)) AS viewed, MAX(IF(event_name = 'add_to_cart', 1, 0)) AS carted, MAX(IF(event_name = 'purchase', 1, 0)) AS purchased FROM events GROUP BY session_key ) SELECT SUM(viewed) AS viewed, SUM(carted) AS carted, SUM(purchased) AS purchased FROM per_session
| Row | viewed | carted | purchased |
|---|---|---|---|
| 1 | 3 | 3 | 1 |
carted equals viewed, although only two of the three viewing sessions added anything. Session d-1 added to cart without a view_item, for example from a list page. In an open funnel a later stage can be larger than an earlier one, and that is usually a sign of a missing tag or of a route you didn’t plan for.
A closed funnel
Keep the time of each stage in place of a flag, then require the order. b-1 added to cart before viewing, and c-1 bought without a cart event, so only a-1 completes the funnel:
WITH events AS ( SELECT * FROM UNNEST([ STRUCT('a-1' AS session_key, 10 AS ts, 'view_item' AS event_name), ('a-1', 20, 'add_to_cart'), ('a-1', 30, 'purchase'), ('b-1', 10, 'add_to_cart'), ('b-1', 20, 'view_item'), ('c-1', 10, 'view_item'), ('c-1', 20, 'purchase') ]) ), stage_times AS ( SELECT session_key, MIN(IF(event_name = 'view_item', ts, NULL)) AS viewed_at, MIN(IF(event_name = 'add_to_cart', ts, NULL)) AS carted_at, MAX(IF(event_name = 'purchase', ts, NULL)) AS purchased_at FROM events GROUP BY session_key ) SELECT COUNT(viewed_at) AS viewed, COUNTIF(carted_at > viewed_at) AS carted_after_view, COUNTIF(carted_at > viewed_at AND purchased_at > carted_at) AS purchased_after_cart FROM stage_times
| Row | viewed | carted_after_view | purchased_after_cart |
|---|---|---|---|
| 1 | 3 | 1 | 1 |
Take MIN for the early stages and MAX for the last. A session that views an item again after its purchase should still count, and the first view is the one that matters. Comparing a NULL gives NULL, which COUNTIF doesn’t count, so sessions missing a stage drop out with no extra condition. MIN can also drop a valid session: one that adds to cart, views an item, then adds to cart again fails the check. If that matters for your site, use the first cart event after the first view.
To let a funnel span several visits, build the same rows per user_pseudo_id and limit the window, such as 7 days from the first stage.
Rates between stages
With one row per stage, LAG gives the step rate and FIRST_VALUE the overall rate:
WITH stages AS ( SELECT * FROM UNNEST([ STRUCT(1 AS step, 'view_item' AS stage, 1000 AS sessions), (2, 'add_to_cart', 180), (3, 'begin_checkout', 90), (4, 'purchase', 45) ]) ) SELECT stage, sessions, ROUND(sessions / LAG(sessions) OVER (ORDER BY step), 3) AS from_previous, ROUND(sessions / FIRST_VALUE(sessions) OVER (ORDER BY step), 3) AS from_first FROM stages ORDER BY step
| Row | stage | sessions | from_previous | from_first |
|---|---|---|---|---|
| 1 | view_item | 1000 | null | 1.0 |
| 2 | add_to_cart | 180 | 0.18 | 0.18 |
| 3 | begin_checkout | 90 | 0.5 | 0.09 |
| 4 | purchase | 45 | 0.5 | 0.045 |
To get from the single row of sums to one row per stage, use UNPIVOT. Dividing two integers with / gives a float in BigQuery, so there is no need for a cast.
Cost
The stages need event_name, event_timestamp and the session parameter. As shown in GA4 event_params, the session parameter is what makes this query cost more than a top-level column. WHERE event_name IN (...) doesn’t reduce those bytes on the raw shards, but it does on a table clustered by event name.
In Querylab.io, the GA4 Templates menu on a GA4 dataset opens a ready-made Purchase Funnel query (details).