GA4’s Funnel Exploration report is powerful but caps at a maximum of 10 steps, applies sampling on high-traffic properties, and cannot be scheduled or automated. When you need to analyze a checkout funnel with 12 steps, run the same funnel weekly across different cohorts, or export results to a BI tool for further analysis, BigQuery is the right platform. This guide builds a reusable funnel framework in SQL that handles step ordering, drop-off calculation, and segment comparison without any of GA4’s built-in limitations.

The Funnel Logic Problem

A funnel measures how many users complete each sequential step in a defined process. The tricky part is “sequential” — a user who completes step 3 before step 1 should not be counted as having completed step 1. Also, should the funnel be closed (steps must occur in the same session) or open (steps can occur across multiple sessions)? GA4’s funnel explorer lets you choose; in BigQuery, you implement this logic explicitly.

Building a Closed Funnel

WITH session_steps AS (
  SELECT
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
    event_name,
    event_timestamp
  FROM `your_project.analytics_XXXXXX.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
    AND event_name IN ('page_view', 'view_item', 'add_to_cart', 'begin_checkout', 'purchase')
),
funnel AS (
  SELECT
    user_pseudo_id,
    session_id,
    MAX(IF(event_name = 'page_view', 1, 0)) AS step1,
    MAX(IF(event_name = 'view_item', 1, 0)) AS step2,
    MAX(IF(event_name = 'add_to_cart', 1, 0)) AS step3,
    MAX(IF(event_name = 'begin_checkout', 1, 0)) AS step4,
    MAX(IF(event_name = 'purchase', 1, 0)) AS step5
  FROM session_steps
  GROUP BY user_pseudo_id, session_id
)
SELECT
  COUNT(*) AS step1_sessions,
  COUNTIF(step2 = 1) AS step2_view_item,
  COUNTIF(step2 = 1 AND step3 = 1) AS step3_add_cart,
  COUNTIF(step2 = 1 AND step3 = 1 AND step4 = 1) AS step4_checkout,
  COUNTIF(step2 = 1 AND step3 = 1 AND step4 = 1 AND step5 = 1) AS step5_purchase,
  ROUND(COUNTIF(step2 = 1) / COUNT(*) * 100, 1) AS pct_to_step2,
  ROUND(COUNTIF(step5 = 1 AND step4 = 1 AND step3 = 1 AND step2 = 1) / COUNT(*) * 100, 2) AS overall_conversion_rate
FROM funnel

Adding Segment Comparison

To compare funnel performance across segments — new vs. returning users, mobile vs. desktop, paid vs. organic — add a segmenting dimension to the GROUP BY clause:

WITH session_steps AS (
  SELECT
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
    event_name,
    event_timestamp,
    device.category AS device_category
  FROM `your_project.analytics_XXXXXX.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
    AND event_name IN ('view_item', 'add_to_cart', 'begin_checkout', 'purchase')
),
funnel AS (
  SELECT
    user_pseudo_id,
    session_id,
    MAX(device_category) AS device,
    MAX(IF(event_name = 'view_item', 1, 0)) AS step1,
    MAX(IF(event_name = 'add_to_cart', 1, 0)) AS step2,
    MAX(IF(event_name = 'begin_checkout', 1, 0)) AS step3,
    MAX(IF(event_name = 'purchase', 1, 0)) AS step4
  FROM session_steps
  GROUP BY user_pseudo_id, session_id
)
SELECT
  device,
  COUNT(*) AS entered_funnel,
  COUNTIF(step2 = 1) AS added_to_cart,
  COUNTIF(step3 = 1 AND step2 = 1) AS began_checkout,
  COUNTIF(step4 = 1 AND step3 = 1 AND step2 = 1) AS purchased,
  ROUND(COUNTIF(step4 = 1 AND step3 = 1 AND step2 = 1) / COUNT(*) * 100, 2) AS conversion_pct
FROM funnel
GROUP BY device
ORDER BY entered_funnel DESC

Scheduling the Funnel Report

Save the funnel SQL as a BigQuery scheduled query that writes results to a destination table weekly. Connect the destination table to Looker Studio for a self-refreshing funnel report. This gives stakeholders a consistent weekly funnel view with no sampling, no GA4 row limits, and the ability to look back at any historical period without hitting GA4 Explore’s 500-row export limit.

For the scheduled query, parameterize the date range using BigQuery’s run_time parameter: WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE(‘%Y%m%d’, DATE_SUB(@run_date, INTERVAL 7 DAY)) AND FORMAT_DATE(‘%Y%m%d’, @run_date). This makes the query automatically analyze the previous 7 days relative to the run date without manual date adjustment.