User Path Analysis in BigQuery: Understanding Navigation Sequences

What Path Analysis Reveals

Path analysis answers questions like: what pages do converters visit before purchasing? What page sequences lead to bounce? How many steps do users take before a form submission? GA4's Path Exploration report provides basic path analysis but with sampling limitations. BigQuery enables unsampled, custom path analysis using window functions.

Building a Session Page Sequence

WITH session_pages AS (
  SELECT
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'session_id') AS session_id,
    event_timestamp,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_url,
    ROW_NUMBER() OVER (
      PARTITION BY user_pseudo_id,
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'session_id')
      ORDER BY event_timestamp
    ) AS step_number
  FROM `project.analytics_PROPERTY.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260131'
    AND event_name = 'page_view'
)
SELECT
  step_number,
  page_url,
  COUNT(*) as visits_at_step
FROM session_pages
WHERE step_number <= 5
GROUP BY step_number, page_url
ORDER BY step_number, visits_at_step DESC

Paths Before Conversion

-- Pages visited in the 3 steps before purchase:
WITH purchase_sessions AS (
  SELECT
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'session_id') AS session_id,
    event_timestamp as purchase_time
  FROM `project.analytics_PROPERTY.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260131'
    AND event_name = 'purchase'
),
page_steps AS (
  SELECT
    e.user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(e.event_params) WHERE key = 'session_id') AS session_id,
    (SELECT value.string_value FROM UNNEST(e.event_params) WHERE key = 'page_location') AS page_url,
    e.event_timestamp,
    ROW_NUMBER() OVER (
      PARTITION BY e.user_pseudo_id,
        (SELECT value.int_value FROM UNNEST(e.event_params) WHERE key = 'session_id')
      ORDER BY e.event_timestamp DESC
    ) AS steps_before_purchase
  FROM `project.analytics_PROPERTY.events_*` e
  JOIN purchase_sessions p
    ON e.user_pseudo_id = p.user_pseudo_id
    AND (SELECT value.int_value FROM UNNEST(e.event_params) WHERE key = 'session_id') = p.session_id
    AND e.event_timestamp <= p.purchase_time
  WHERE e._TABLE_SUFFIX BETWEEN '20260101' AND '20260131'
    AND e.event_name = 'page_view'
)
SELECT steps_before_purchase, page_url, COUNT(*) as frequency
FROM page_steps
WHERE steps_before_purchase <= 3
GROUP BY steps_before_purchase, page_url
ORDER BY steps_before_purchase, frequency DESC

Summary

BigQuery path analysis uses ROW_NUMBER() window functions to sequence page views within sessions, revealing step-by-step navigation patterns. The conversion path query (steps before purchase) identifies which pages most commonly appear in the journey to purchase — these are your highest-impact pages for optimisation. Unsampled BigQuery path analysis provides more reliable results than GA4's sampled Path Exploration for high-traffic properties.

See our BigQuery Setup service for path analysis development.

Need user path analysis in BigQuery? Contact Adslytics.