# 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. Source: https://docs.querylab.io/bigquery/ga4-funnels/ Updated: 2026-11-04 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](https://docs.querylab.io/bigquery/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: **events against sessions** ```sql 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 ``` Result in BigQuery (1 row): | view_events | cart_events | cart_sessions | | --- | --- | --- | | 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: **sessions that reached each stage** ```sql 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 ``` Result in BigQuery (1 row): | viewed | carted | purchased | | --- | --- | --- | | 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: **stages in order, by timestamp** ```sql 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 ``` Result in BigQuery (1 row): | viewed | carted_after_view | purchased_after_cart | | --- | --- | --- | | 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: **step and overall conversion** ```sql 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 ``` Result in BigQuery (4 rows): | stage | sessions | from_previous | from_first | | --- | --- | --- | --- | | view_item | 1000 | NULL | 1.0 | | add_to_cart | 180 | 0.18 | 0.18 | | begin_checkout | 90 | 0.5 | 0.09 | | purchase | 45 | 0.5 | 0.045 | To get from the single row of sums to one row per stage, use [`UNPIVOT`](https://docs.querylab.io/bigquery/pivot-and-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](https://docs.querylab.io/bigquery/ga4-event-params/#what-a-parameter-costs), 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](https://docs.querylab.io/bigquery/ga4-export/#when-to-build-your-own-table). In Querylab.io, the GA4 Templates menu on a GA4 dataset opens a ready-made Purchase Funnel query ([details](https://docs.querylab.io/schema-browser/ga4-events/#extra-menu-items-on-a-ga4-dataset)). Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in. ## Sources Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery: - [BigQuery Export schema (Analytics Help)](https://support.google.com/analytics/answer/7029846) - [Basic queries for Google Analytics event data export](https://developers.google.com/analytics/bigquery/basic-queries)