Funnel Analysis in BigQuery: Tracking Multi-Step Conversion Paths

Why Funnel Analysis in BigQuery

GA4's built-in funnel exploration can sample data in high-traffic properties and limits funnel complexity. BigQuery funnel analysis runs on 100% of data, supports complex multi-step funnels with custom logic, and allows segmentation by any dimension including custom parameters not available in GA4's interface.

Simple Funnel: Step-by-Step Counts

For a checkout funnel (view_item → add_to_cart → begin_checkout → purchase):

SELECT
  'Step 1: Product View' as step,
  COUNT(DISTINCT user_pseudo_id) as users
FROM `project.analytics_PROPERTY.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260131'
  AND event_name = 'view_item'

UNION ALL

SELECT 'Step 2: Add to Cart', COUNT(DISTINCT user_pseudo_id)
FROM `project.analytics_PROPERTY.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260131'
  AND event_name = 'add_to_cart'

UNION ALL

SELECT 'Step 3: Begin Checkout', COUNT(DISTINCT user_pseudo_id)
FROM `project.analytics_PROPERTY.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260131'
  AND event_name = 'begin_checkout'

UNION ALL

SELECT 'Step 4: Purchase', COUNT(DISTINCT user_pseudo_id)
FROM `project.analytics_PROPERTY.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260131'
  AND event_name = 'purchase'

Strict Funnel: Ordered Steps per User

The above query counts users who triggered each event but does not require them to follow the steps in order. A strict funnel — where Step 2 only counts users who also completed Step 1 — requires a more complex query:

WITH user_events AS (
  SELECT
    user_pseudo_id,
    MAX(IF(event_name='view_item', 1, 0)) as did_step1,
    MAX(IF(event_name='add_to_cart', 1, 0)) as did_step2,
    MAX(IF(event_name='begin_checkout', 1, 0)) as did_step3,
    MAX(IF(event_name='purchase', 1, 0)) as did_step4
  FROM `project.analytics_PROPERTY.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260131'
    AND event_name IN ('view_item', 'add_to_cart', 'begin_checkout', 'purchase')
  GROUP BY user_pseudo_id
)
SELECT
  SUM(did_step1) as step1_product_view,
  SUM(IF(did_step1=1 AND did_step2=1, 1, 0)) as step2_add_cart,
  SUM(IF(did_step1=1 AND did_step2=1 AND did_step3=1, 1, 0)) as step3_checkout,
  SUM(IF(did_step1=1 AND did_step2=1 AND did_step3=1 AND did_step4=1, 1, 0)) as step4_purchase
FROM user_events

Funnel Segmented by Device

Add device.category to segment funnel performance by device type — critical for understanding mobile vs desktop conversion differences.

Summary

BigQuery funnel analysis provides unsampled funnel metrics on 100% of GA4 event data. Simple UNION ALL funnels count unique users per step independently. Strict funnels require a CTE (Common Table Expression) that flags completion per user, then sums users who completed each step in sequence. Funnel queries can be extended with segmentation by device, traffic source, or any custom dimension to identify where specific segments drop off.

See our BigQuery Setup service for funnel analysis development.

Need funnel analysis in BigQuery? Contact Adslytics.