GA4 purchase funnel SQL: view_item to purchase with step conversion rates

The GA4 funnel exploration is fine until you want to change one thing about it, at which point you are fighting the interface. In BigQuery the whole funnel is one query, and once it is yours you can move the steps, change the window, or split it by anything the export carries. This is the four-step ecommerce funnel: view_item, add_to_cart, begin_checkout, purchase, counted as distinct users at each step with the drop-off between them.

The approach is conditional aggregation, not joins. Chaining four self-joins on user_pseudo_id is slow and it multiplies rows in ways that quietly inflate counts; counting distinct users per event in a single pass is faster, simpler, and far harder to get subtly wrong. It is read-only.

Before you start

• The export dataset is named analytics_XXXXX, where XXXXX is your GA4 property ID; daily tables are events_YYYYMMDD, queried with the events_* wildcard.
• Replace your_project.analytics_XXXXX with your own project and dataset.
• This counts the standard GA4 ecommerce event names. If your implementation renamed any step, edit the event names in the query to match.
• The _TABLE_SUFFIX date filter controls how many daily tables BigQuery scans, and therefore the cost. Keep the window to the period you actually want to report.

The query

One scan, four counts. Each step's MAX(IF(...)) flags the users who fired that event and the SUM counts them; the rates divide each step by the one before, and by the top of the funnel:

-- Ecommerce funnel: distinct users per step and step-to-step rates.
-- Replace your_project.analytics_XXXXX with your project and dataset.
-- Conditional aggregation over one pass, no self-joins.
WITH user_steps AS (
  SELECT
    user_pseudo_id,
    MAX(IF(event_name = 'view_item', 1, 0))     AS did_view_item,
    MAX(IF(event_name = 'add_to_cart', 1, 0))   AS did_add_to_cart,
    MAX(IF(event_name = 'begin_checkout', 1, 0)) AS did_begin_checkout,
    MAX(IF(event_name = 'purchase', 1, 0))      AS did_purchase
  FROM `your_project.analytics_XXXXX.events_*`
  -- _TABLE_SUFFIX controls how many daily tables are scanned, and the cost.
  WHERE _TABLE_SUFFIX BETWEEN '20260601' AND '20260607'
    AND event_name IN
      ('view_item', 'add_to_cart', 'begin_checkout', 'purchase')
  GROUP BY user_pseudo_id
),
steps AS (
  SELECT
    SUM(did_view_item)     AS view_item_users,
    SUM(did_add_to_cart)   AS add_to_cart_users,
    SUM(did_begin_checkout) AS begin_checkout_users,
    SUM(did_purchase)      AS purchase_users
  FROM user_steps
)
SELECT step, users, rate_from_previous, rate_from_top FROM (
  SELECT 1 AS ord, 'view_item' AS step, view_item_users AS users,
         1.0 AS rate_from_previous, 1.0 AS rate_from_top
  FROM steps
  UNION ALL
  SELECT 2, 'add_to_cart', add_to_cart_users,
         SAFE_DIVIDE(add_to_cart_users, view_item_users),
         SAFE_DIVIDE(add_to_cart_users, view_item_users)
  FROM steps
  UNION ALL
  SELECT 3, 'begin_checkout', begin_checkout_users,
         SAFE_DIVIDE(begin_checkout_users, add_to_cart_users),
         SAFE_DIVIDE(begin_checkout_users, view_item_users)
  FROM steps
  UNION ALL
  SELECT 4, 'purchase', purchase_users,
         SAFE_DIVIDE(purchase_users, begin_checkout_users),
         SAFE_DIVIDE(purchase_users, view_item_users)
  FROM steps
)
ORDER BY ord;

Set it up

1. Open the BigQuery console on the project holding your export dataset.
2. Paste the query and replace your_project.analytics_XXXXX with your project and dataset.
3. If any of your ecommerce events use non-standard names, edit them in both the IN (...) list and the four MAX(IF(...)) lines.
4. Set the _TABLE_SUFFIX dates, check the estimated scan size, then run.
5. Read the result top to bottom: rate_from_previous is the step-to-step conversion, rate_from_top is cumulative from view_item.

How to read it, and what it does not tell you

rate_from_previous is where the funnel leaks; a big drop from add_to_cart to begin_checkout points at the cart, a drop from begin_checkout to purchase at the checkout itself. rate_from_top is the honest end-to-end figure you can quote.

Some limitations to keep in mind, because this counts unique users, not ordered journeys:

• It is not path-ordered. A user is counted at a step if they ever fired that event in the window, even if they purchased before they viewed the specific item. Over a reasonable window this is close to the ordered funnel, but it is not identical; for strict ordering you need event-timestamp sequencing, which is a heavier query.
• The window truncates journeys. A user who viewed on the last day and bought the next is split across the boundary. Widen the range if your consideration cycle is long, remembering that widens the scan cost too.
• user_pseudo_id is a device, not a person, so cross-device and cookie-cleared journeys fragment, and consent-denied traffic may be modelled or absent. The rates are directionally reliable and useful for spotting the leak; treat them as that, not as an exact count of shoppers.