BigQuery + GA4: Output Pages Report

Remember from Universal Analytics the output reports? 

It was a default report that was within the behavioral heading under Content or Site content in which this table

It was a report of the main exit pages of our website, it is the report that is on the other side of the report of entry pages or landing pages. 

It was a report of the main exit pages of our website, it is the report that is on the other side of the report of entry pages or landing pages.

In the report as its name indicated it showed the main urls by which users leave our website.

The purpose of this query was to show the main urls by which users leave our website.

The purpose of this query is to identify which are the most common exit pages on a website, which can provide valuable information about user behavior, potential friction points in website design or user flow, and opportunities to improve user retention or optimize conversions.

We also provide you with a "UA - Exit pages" alt.

We also provide a description of what the query does in case you want to know, but you can skip the explanation and copy and paste the query directly.

This BigQuery query is designed to identify the exit pages of user sessions on a website using Google Analytics 4 data exported to BigQuery. Here's the breakdown of what the query does:

• Subquery "prep":

• Select user identifiers ('user_pseudo_id'), session identifiers ('session_id'), and the URLs of the pages ('page') where a page view event ('page_view') has occurred.
• Pick up the 'event_timestamp' for each page view event.
• Filters to include only events that are pageviews (event_name = 'page_view').
• Sort the results by 'event_timestamp' to get a sequence of events by time.

• Sub-query "prep_exit":

• Use the temporary table 'prep' to select the same fields.
• Apply the window function 'FIRST_VALUE' to get the last page viewed ('exit_page') in each session per user, sorting the events by 'event_timestamp' in a top-down manner, which means it gets the page with the last timestamp as the exit page.

• Main inquiry:

• Select the page ('exit_page') only if it matches the last page viewed in the session (indicating that it is the exit page).
• Count the number of unique sessions ('exits') that ended at each specific exit page, using 'COUNT(DISTINCT ..') on the concatenation of 'user_pseudo_id' and 'session_id'..
• Group the results by 'exit_page'..
• Filters to include only those rows where 'exit_page' is not null, ensuring only exit pages are counted.
• Sort the results by the number of exits in descending order.

We are going to pass you the query you have to use in Bigquery on your GA4 dataset or dataset of your website in order to see this type of report.

Remember that to do this you don't need to know Bigquery, just copy and paste what follows.

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
    -- In events_2023* you can put a specific date: _20231001 (October 1, 2023), _202310* (all of October 2023), _2023* (all of October 2023), _2023* (all of 2023 so far)....
    `<project>.<dataset>.events_2023*``
  WHERE
    event_name = 'page_view'
  ORDER BY
    event_timestamp
),
  prep_exit AS(
  SELECT
    user_pseudo_id,
    session_id,
    page,
    event_timestamp,
    FIRST_VALUE(CONCAT(page, event_timestamp)) OVER (PARTITION BY user_pseudo_id, session_id ORDER BY event_timestamp DESC) AS exit_page
  FROM
    rep
  ORDER BY
    event_timestamp
  )
SELECT
  CASE
    WHEN concat (page, event_timestamp) = exit_page THEN page
  ELSE
  NULL
END
  AS exit_page,
  COUNT(DISTINCT CONCAT(user_pseudo_id,session_id)) AS exits
FROM
  prep_exit
GROUP BY
  exit_page
HAVING
  exit_page IS NOT NULL
ORDER BY
  exits desc

If you want a "closed" date range, for example from August 8 to September 23. 

Take this snippet from the query above: 

FROM
  -- Here you put the name of your GA4 dataset.In events_2023* you can put a specific date: _20231001 (October 1, 2023), _202310* (all of October 2023), _2023* (all of 2023 so far)....
  `<project>.<dataset>.events_2023*``
WHERE
  event_name = 'page_view'

And you replace it with: 

FROM
  `<project>.<dataset>.events_*``
WHERE
  event_name = 'page_view'
  AND _TABLE_SUFFIX BETWEEN '20230808'
  AND '20230923'

And that's it!

When you run the query you would get a table like this:

In the table we can already see the main exit pages of our website and the number of times that users have exited through them in the selected period.

This is the end of today's article. I hope it will be useful and helpful.