NEWSLETTER
ANTERIOR
Working with large volumes of marketing data—whether it’s web traffic, keywords, users, or campaigns—can feel overwhelming. These data sets often aren’t organized or categorized in a useful way, and facing them can feel like trying to understand a conversation in an unfamiliar language.
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
was displayed.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.
