Calculating Conversion Paths in GA4 BigQuery

Calculating conversion paths in GA4 BigQuery tells you which page sequences your converting users actually take before they submit a lead form, complete a checkout, or hit any thank you page.

Without it, you are guessing at the journey.

Here is the SQL.

-- Determine the conversion paths used by website visitors to submit leads on your website.

WITH
  page_views AS (
    SELECT
      user_pseudo_id,
      (
        SELECT value.int_value
        FROM UNNEST(event_params)
        WHERE key = 'ga_session_id'
      ) AS session_id,
      TIMESTAMP_MICROS(event_timestamp) AS event_time,
      REGEXP_EXTRACT(
        (
          SELECT value.string_value
          FROM UNNEST(event_params)
          WHERE key = 'page_location'
        ),
        r'^[^?]+') AS normalized_url
    FROM
      `dbrt-ga4.analytics_207472454.events_*`
    WHERE
      event_name = 'page_view'
      AND _TABLE_SUFFIX BETWEEN '20241001' AND '20241031'
  ),
  user_paths AS (
    SELECT
      user_pseudo_id,
      session_id,
      ARRAY_AGG(normalized_url ORDER BY event_time ASC) AS path_array
    FROM
      page_views
    GROUP BY
      user_pseudo_id, session_id
  ),
  filtered_paths AS (
    SELECT
      user_pseudo_id,
      ARRAY_TO_STRING(
        ARRAY(SELECT DISTINCT url FROM UNNEST(path_array) AS url), ' > ')
        AS conversion_path
    FROM
      user_paths
    WHERE
      ARRAY_LENGTH(
        ARRAY(
          SELECT DISTINCT url
          FROM UNNEST(path_array) AS url
          WHERE
            url IN (
              'https://www.optimizesmart.com/contact/',
              'https://www.optimizesmart.com/thankyou/')
        ))
      = 2
  ),
  final_output AS (
    SELECT
      conversion_path,
      COUNT(DISTINCT user_pseudo_id) AS users_followed_conversion_path
    FROM
      filtered_paths
    GROUP BY
      conversion_path
  )
SELECT
  conversion_path AS conversion_paths,
  users_followed_conversion_path
FROM
  final_output
ORDER BY
  users_followed_conversion_path DESC;

Replace the dbrt-ga4.analytics_207472454.events_* table reference with your own project, dataset, and events table.

Replace the dates in _TABLE_SUFFIX with your reporting window.

Replace the two URLs inside the IN (...) list with your form page and your thank you page. If your conversion is defined by three or more URLs that must all appear in the session, add them to the list and update the ARRAY_LENGTH check to match.

What if you want to combine these with other GA4 metrics and dimensions.

The query above returns two outputs: conversion_paths and users_followed_conversion_path.

What if you want to slice these by traffic source, campaign, device, country, landing page, or any other GA4 dimension? What if you want to layer in revenue per path, average session duration, or first-touch channel?

There could be N use cases. It is not possible to provide ready-made SQL for every one of them.

That is why understanding the query logic matters. Once you understand the logic, you can scale across N SQL use cases instead of hunting for a new copy-paste query every time the requirement changes.

Learn the underlying logic for calculating various GA4 dimensions and metrics in BigQuery and leave the actual SQL generation to ChatGPT.

Your GA4 BigQuery data is only as good as the query logic you use. Once you understand the query logic, you can scale across 'N' SQL use cases.

That's what I teach in my GA4 BigQuery Course. The focus is entirely on teaching the underlying logic (instead of the actual SQL).