Docs/Google Analytics 4/Funnels
Google Analytics 4

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:

events against sessionsOpen in Querylab.io
123456789101112
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
Rowview_eventscart_eventscart_sessions
1331
1 row

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 stageOpen in Querylab.io
1234567891011121314151617181920
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
Rowviewedcartedpurchased
1331
1 row

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 timestampOpen in Querylab.io
12345678910111213141516171819202122
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
Rowviewedcarted_after_viewpurchased_after_cart
1311
1 row

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 conversionOpen in Querylab.io
12345678910111213
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
Rowstagesessionsfrom_previousfrom_first
1view_item1000null1.0
2add_to_cart1800.180.18
3begin_checkout900.50.09
4purchase450.50.045
4 rows

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).

Open the playground. It is the real app on public datasets and needs no sign-in.