GA4’s built-in funnel exploration is powerful but limited — you can’t customize the attribution window, include arbitrary event conditions, or export raw funnel data for further analysis. BigQuery gives you full control: you can build any funnel you can define in SQL, with any conditions, any time windows, and any number of steps. This guide shows you how to build conversion funnels from scratch in BigQuery using GA4 event data.

The Funnel Analysis Approach

A funnel analysis answers: “Of users who completed step 1, how many completed step 2? Of those, how many completed step 3?” The key challenge in SQL is defining what counts as completing each step, within what time window, and whether steps must be in order.

-- Basic funnel: Session → Product View → Add to Cart → Purchase
-- Count users at each step

WITH session_events AS (
  SELECT
    user_pseudo_id,
    CONCAT(user_pseudo_id, '_',
      CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)
    ) AS session_id,
    event_name,
    event_timestamp
  FROM `project.dataset.events_*`
  WHERE _TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAYS))
),

-- Step 1: Sessions that had a session_start
step1 AS (
  SELECT DISTINCT session_id
  FROM session_events
  WHERE event_name = 'session_start'
),

-- Step 2: Sessions that viewed a product (after session start)
step2 AS (
  SELECT DISTINCT s.session_id
  FROM session_events s
  INNER JOIN step1 ON s.session_id = step1.session_id
  WHERE s.event_name = 'view_item'
),

-- Step 3: Sessions that added to cart (after viewing product)
step3 AS (
  SELECT DISTINCT s.session_id
  FROM session_events s
  INNER JOIN step2 ON s.session_id = step2.session_id
  WHERE s.event_name = 'add_to_cart'
),

-- Step 4: Sessions that purchased (after adding to cart)
step4 AS (
  SELECT DISTINCT s.session_id
  FROM session_events s
  INNER JOIN step3 ON s.session_id = step3.session_id
  WHERE s.event_name = 'purchase'
)

SELECT
  'Session Start' AS step, COUNT(*) AS sessions, 1.0 AS conversion_rate
FROM step1
UNION ALL
SELECT 'View Product', COUNT(*), SAFE_DIVIDE(COUNT(*), (SELECT COUNT(*) FROM step1))
FROM step2
UNION ALL
SELECT 'Add to Cart', COUNT(*), SAFE_DIVIDE(COUNT(*), (SELECT COUNT(*) FROM step1))
FROM step3
UNION ALL
SELECT 'Purchase', COUNT(*), SAFE_DIVIDE(COUNT(*), (SELECT COUNT(*) FROM step1))
FROM step4;

Ordered Funnel: Steps Must Happen in Sequence

The basic approach above doesn’t enforce step order — a user who purchased before viewing a product would still be counted. For strict ordering, use timestamps:

-- Strict ordered funnel with timestamps
WITH user_step_times AS (
  SELECT
    user_pseudo_id,
    MIN(CASE WHEN event_name = 'session_start' THEN event_timestamp END) AS t_session,
    MIN(CASE WHEN event_name = 'view_item' THEN event_timestamp END) AS t_view,
    MIN(CASE WHEN event_name = 'add_to_cart' THEN event_timestamp END) AS t_cart,
    MIN(CASE WHEN event_name = 'purchase' THEN event_timestamp END) AS t_purchase
  FROM `project.dataset.events_*`
  WHERE _TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAYS))
  GROUP BY user_pseudo_id
)
SELECT
  COUNT(DISTINCT user_pseudo_id) AS reached_session,
  COUNT(DISTINCT CASE WHEN t_view > t_session THEN user_pseudo_id END) AS viewed_product,
  COUNT(DISTINCT CASE WHEN t_cart > t_view AND t_view > t_session THEN user_pseudo_id END) AS added_to_cart,
  COUNT(DISTINCT CASE WHEN t_purchase > t_cart AND t_cart > t_view THEN user_pseudo_id END) AS purchased
FROM user_step_times
WHERE t_session IS NOT NULL;