ServicesAboutNotesContact Get in touch →
EN FR
Note

GA4 Ecommerce Checkout Funnel Pattern

Session-based checkout funnel analysis from GA4 BigQuery data — counting distinct sessions at each funnel stage from view_item through purchase.

Planted
ga4bigqueryanalytics

A checkout funnel answers the most basic e-commerce question: where are we losing people? GA4 tracks the standard e-commerce flow through a sequence of events, and building a funnel from BigQuery data requires combining the composite session key with COUNT(DISTINCT CASE WHEN ...) aggregation.

This pattern uses session-level funnel analysis — counting how many sessions reached each stage, not how many events fired. The distinction matters because a user might view the same product five times in one session; that’s one session that viewed a product, not five funnel entries.

The Full Checkout Funnel Query

WITH funnel_events AS (
  SELECT
    CONCAT(
      user_pseudo_id, '-',
      (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')
    ) AS session_id,
    event_name,
    event_timestamp
  FROM `project.dataset.events_*`
  WHERE event_name IN (
    'view_item', 'add_to_cart', 'begin_checkout',
    'add_shipping_info', 'add_payment_info', 'purchase'
  )
    AND _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
)


SELECT
  COUNT(DISTINCT CASE WHEN event_name = 'view_item' THEN session_id END) AS sessions_viewed_item,
  COUNT(DISTINCT CASE WHEN event_name = 'add_to_cart' THEN session_id END) AS sessions_added_to_cart,
  COUNT(DISTINCT CASE WHEN event_name = 'begin_checkout' THEN session_id END) AS sessions_began_checkout,
  COUNT(DISTINCT CASE WHEN event_name = 'add_shipping_info' THEN session_id END) AS sessions_added_shipping,
  COUNT(DISTINCT CASE WHEN event_name = 'add_payment_info' THEN session_id END) AS sessions_added_payment,
  COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN session_id END) AS sessions_purchased,
  SAFE_DIVIDE(
    COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN session_id END),
    COUNT(DISTINCT CASE WHEN event_name = 'view_item' THEN session_id END)
  ) AS overall_conversion_rate
FROM funnel_events

How the Pattern Works

The CTE extracts only the funnel-relevant events, keeping the query efficient by filtering early. The CONCAT builds the composite session key — user_pseudo_id alone isn’t granular enough (one user has many sessions), and ga_session_id alone isn’t unique across users.

The main query uses COUNT(DISTINCT CASE WHEN event_name = '...' THEN session_id END) for each stage. The CASE WHEN returns the session ID only for matching events; COUNT(DISTINCT ...) then counts unique sessions at that stage. Sessions that didn’t reach a stage return NULL from the CASE, which COUNT(DISTINCT) ignores.

SAFE_DIVIDE prevents division-by-zero errors when the top of the funnel has zero sessions (possible in narrow date ranges or heavily filtered queries).

Reading the Results

A healthy checkout funnel shows gradual step-down rates. Typical patterns:

  • view_item to add_to_cart: 5-15% is normal for most e-commerce. Below 5% suggests pricing, product page, or trust issues.
  • add_to_cart to begin_checkout: 30-50%. Drop-off here often points to cart abandonment — shipping cost surprise, account creation requirements, or unclear next steps.
  • begin_checkout to purchase: 50-70%. Significant drop-off here typically indicates payment friction, unexpected costs revealed at checkout, or technical failures in the payment flow.

When a particular step shows outsized drop-off compared to benchmarks, investigate the page-level experience at that stage. The funnel tells you where people leave; figuring out why requires qualitative analysis or A/B testing.

Adding Product-Level Funnel Analysis

The basic funnel shows overall flow. To see which products have the best or worst conversion, add the items UNNEST:

SELECT
  item.item_name,
  item.item_category,
  COUNTIF(event_name = 'view_item') AS product_views,
  COUNTIF(event_name = 'add_to_cart') AS add_to_carts,
  COUNTIF(event_name = 'purchase') AS purchases,
  SAFE_DIVIDE(
    COUNTIF(event_name = 'add_to_cart'),
    COUNTIF(event_name = 'view_item')
  ) AS view_to_cart_rate,
  SAFE_DIVIDE(
    COUNTIF(event_name = 'purchase'),
    COUNTIF(event_name = 'add_to_cart')
  ) AS cart_to_purchase_rate
FROM `project.dataset.events_*`,
     UNNEST(items) AS item
WHERE event_name IN ('view_item', 'add_to_cart', 'purchase')
  AND _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
GROUP BY item.item_name, item.item_category
HAVING product_views > 100
ORDER BY product_views DESC

Notice this uses COUNTIF rather than COUNT(DISTINCT CASE WHEN ...). At the product level, event counts (not session counts) are more natural — you want to know how many times a product was viewed, added to cart, and purchased. The CROSS JOIN on items is safe here because we filter to ecommerce events that always have items populated.

The HAVING product_views > 100 filter eliminates products with too few views for meaningful conversion rates. A product with 3 views and 1 purchase has a 33% view-to-cart rate, but that number is meaningless.

Temporal Funnel Analysis

To track funnel performance over time, add a date dimension:

WITH funnel_events AS (
  SELECT
    PARSE_DATE('%Y%m%d', event_date) AS event_date,
    CONCAT(
      user_pseudo_id, '-',
      (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')
    ) AS session_id,
    event_name
  FROM `project.dataset.events_*`
  WHERE event_name IN ('view_item', 'add_to_cart', 'begin_checkout', 'purchase')
    AND _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
)


SELECT
  event_date,
  COUNT(DISTINCT CASE WHEN event_name = 'view_item' THEN session_id END) AS sessions_viewed,
  COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN session_id END) AS sessions_purchased,
  SAFE_DIVIDE(
    COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN session_id END),
    COUNT(DISTINCT CASE WHEN event_name = 'view_item' THEN session_id END)
  ) AS conversion_rate
FROM funnel_events
GROUP BY event_date
ORDER BY event_date

This gives you a daily conversion rate trend line. Spikes and dips correlate with promotions, site changes, or tracking issues. If conversion rate drops suddenly without a known cause, check whether any funnel events stopped firing — a broken checkout tag is a common culprit.

Important Caveats

This is a loose funnel, not a strict sequence. The query counts sessions that have each event, regardless of order. A session where someone purchased and then viewed another item still counts as having both view_item and purchase. For strict sequential funnel analysis (where step N must occur after step N-1), you need event timestamp ordering and window functions — a significantly more complex query.

Revenue should come from the ecommerce RECORD, not items. If you add revenue to this funnel, use ecommerce.purchase_revenue on the purchase event, not SUM(item.item_revenue) after unnesting items. The latter requires careful handling to avoid double-counting when a transaction has multiple items. See GA4 E-commerce Schema in BigQuery for the revenue calculation patterns.

Sessions can span midnight. A session that starts at 11:55 PM and purchases at 12:05 AM has events in two different event_date values. The temporal funnel above counts by event date, not session date. For session-date attribution, join against a session table that assigns a single date to each session.