BigQuery + GA4: Page Navigation Report

If you've been working with GA4 for some time, you may have noticed that certain dimensions and metrics that were available in Universal Analytics are not present in GA4. For example, the Navigation report, where we could select a URL from our website and it would show the previous and next page paths in percentages, is no longer available:

Neither of the two dimensions you see — Previous Page Path and Next Page — exist in GA4. However, the navigation report that existed in Universal Analytics was always very useful when we wanted to understand general behavior during navigation, focusing on specific content. It helps to understand user flow and improve the navigation experience or content strategy for the website.

In this post, we will explain how to generate this report from BigQuery.

Don't be scared! If you haven’t yet ventured into BigQuery, even though you already have the connection between GA4 and BigQuery set up, don’t worry, we will make it easy for you. You’ll only need to copy and paste the query we provide and make a few adjustments.

We’ll explain what each part of the function does, in case you're interested. But if not, just scroll down to the query section and copy it.

This BigQuery query uses data from Google Analytics 4 to analyze user navigation on a website, specifically around a given page URL. It is done in several stages:

"prep" subquery:

• Selects 'user_pseudo_id' (an anonymous user identifier), 'session_id' (user session identifier), 'page' (URL of the page viewed), and 'event_timestamp' (the timestamp when the page view occurred).
• Filters to include only page view events (event_name = 'page_view').
• The data is extracted from the event tables for a specific date range in the Google Analytics 4 dataset (in this case, for the year 2023).

"prep_navigation" subquery:

• Uses the 'prep' temporary table to retrieve a sequence of pages visited by each user and session.
• Applies LAG and LEAD window functions to get the previous ('previous_page') and next ('next_page') pages, respectively, for each page view, ordered by 'event_timestamp' in ascending order. This is partitioned by 'user_pseudo_id' and 'session_id', meaning the page sequence is specific to each user's session.

Main query:

• Replaces null previous and next pages with '(entrance)' and '(exit)' respectively, indicating that if there is no previous page, the page is the entry point, and if there is no next page, it is the exit.
• Counts the number of unique sessions ('count') where a specific page has been visited, using 'COUNT(DISTINCT ...)' on the concatenation of 'user_pseudo_id' and 'session_id'.
• Filters to show data only for the specific URL of the page you want to analyze.
• Groups the results by 'previous_page', 'page', and 'next_page'.
• Filters to ensure that the page is not the same as the previous or next page to avoid self-references.
• Orders the results by 'count' in descending order.

Here’s the query you need:

WITH
  prep AS (
  SELECT
    user_pseudo_id,
    (
    SELECT
      value.int_value
    FROM
      UNNEST(event_params)
    WHERE
      event_name = 'page_view'
      AND KEY = 'ga_session_id') AS session_id,
    (
    SELECT
      value.string_value
    FROM
      UNNEST(event_params)
    WHERE
      event_name = 'page_view'
      AND KEY = 'page_location') AS page,
    event_timestamp
  FROM
    -- Aquí pon el nombre de tu conjunto de datos de GA4.En events_2023* puedes poner una fecha concreta: _20231001 (1 de octubre de 2023), _202310* (todo octubre de 2023), _2023* (todo lo que llevamos de 2023)...
    `<project>.<dataset>.events_2023*`
  WHERE
    event_name = 'page_view'
  ),
  prep_navigation AS (
  SELECT
    user_pseudo_id,
    session_id,
    LAG(page,1) OVER (PARTITION BY user_pseudo_id, session_id ORDER BY event_timestamp ASC)AS previous_page,
    page,
    LEAD(page,1) OVER (PARTITION BY user_pseudo_id, session_id ORDER BY event_timestamp ASC)AS next_page,
    event_timestamp
  FROM
    prep
  )
SELECT
  IFNULL(previous_page,'(entrance)') AS previous_page,
  page,
  IFNULL(next_page,'(exit)') AS next_page,
  COUNT(DISTINCT CONCAT(user_pseudo_id,session_id)) AS count
FROM
  prep_navigation
WHERE
  -- Copia y pega abajo la url de la página que quieres consultar.
  page = "https://www.hikeproject.com/como-visualizar-porcentajes-en-un-scorecard-de-data-studio/"
GROUP BY
  previous_page,
  page,
  next_page
HAVING
  page != previous_page
  AND page != next_page
ORDER BY
  count desc

To query for a specific date range, for example, from September 2nd to October 15th, you need to replace the query snippet:

FROM
  -- Aquí pon el nombre de tu conjunto de datos de GA4.En events_2023* puedes poner una fecha concreta: _20231001 (1 de octubre de 2023), _202310* (todo octubre de 2023), _2023* (todo lo que llevamos de 2023)...
  `<project>.<dataset>.events_2023*`
WHERE
  event_name = 'page_view'),

Y lo sustituyes por: 

FROM
  `<project>.<dataset>.events_*`
WHERE
  event_name = 'page_view'
  AND _TABLE_SUFFIX BETWEEN '20230902'
  AND '20231015'),

And that’s it!

Once you run the query, you'll get a table like this:

(img: BQ - Query Results)