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.
If you've been messing around with GA4, you will have already noticed that there are certain dimensions and metrics that were in Universal Analytics, and that are not in GA4, for example, the Navigation report in which we chose a url of our website and indicated in percentages the path of the previous page and the path of the next page:
None of the two dimensions you see: Previous Page Path and Next Page exist for GA4. However, this navigation report that existed for Universal Analytics has always been very useful when we want to know the general behavior during navigation focusing on a specific content. It serves to understand the user flow and improve the browsing experience or the content strategy of the website.
In this post we are going to explain how to get this report from BigQuery.
We don't want to scare you, if you have not yet thought of entering BigQuery to make any query even though you have already made the connection between GA4 and BigQuery, don't run away because in this post we are going to make it easy for you, you will only have to copy and paste the query that we are going to give you and change a number of things.
Yes we are going to explain what each part of the function does in case you are interested, but if you are not just go down to where the query is and copy it.
This BigQuery query uses Google Analytics 4 data to analyze user navigation on a website, specifically around a given page URL. It is performed in several stages:
1. "prep" sub-query:
- Select 'user_pseudo_id' (an anonymous identifier for the user), 'session_id' (a user's session identifier), 'page' (the URL of the page viewed), and 'event_timestamp' (the time at which the page view occurred).
- Filters to include only events that are pageviews (event_name = 'page_view').
- Data is extracted from the event tables for a specific date range of the Google Analytics 4 dataset (in this case, for the year 2023).
2. "prep_navigation" sub-query:
- Uses the temporary table 'prep' to get a sequence of pages visited by each user and session.
- Apply the window functions LAG and LEAD to get the previous page ('previous_page') and next page ('next_page') respectively, for each page view, sorted by 'event_timestamp' in ascending order. This is partitioned by 'user_pseudo_id' and 'session_id', which means that the sequence of pages is specific to each session of a user.
3. Main query:
- Replace null previous and next pages with '(entrance)' and '(exit)' respectively, which indicates that if there is no previous page, the page in question is the entrance to the site, and if there is no next page, it is the exit.
- Count the number of unique sessions ('count') where the specific page has been visited, using 'COUNT(DISTINCT ..)' on the concatenation of 'user_pseudo_id' and 'session_id'.
- Filter to display information only for the URL of the specific page you want to query. .
- Aggregate the results by 'previous_page', 'page' and 'next_page'.
- Filter to ensure that the page is not equal to 'previous_page' or 'next_page' to not count self-references.
- Sort the results by 'count' in descending order.
Attention. This is 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
-- 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)...
``tests-bigquery-351807.analytics_313378230.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
-- Copy and paste below the url of the page you want to consult.
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
desc
If you want a "closed" date range, for example from September 2 to October 15.
Take this snippet from the query above:
from
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)...
`tests-bigquery-351807.analytics_313378230.events_2023*`
where
event_name = 'page_view'),
And you replace it with:
from
`tests-bigquery-351807.analytics_313378230.events_*``
where
event_name = 'page_view'
and _TABLE_SUFFIX between '20230902' and '20231015'),
And that's it!
When you run the query you would get a table like this:
The central column corresponds to the url selected to analyze (Page), the column to its left indicates the previous page and the column to the right, the next page.
The central column corresponds to the url selected to analyze (Page), the column to its left indicates the previous page and the column to the right, the next page.
This way we see that the post:
https://www.hikeproject.com/como-visualizar-porcentajes-en-un-scorecard-de-data-studio/
In the period from September 2 to October 15 there have been:
- 33 sessions in which the user has entered the website through that url and then left .
- 3 sessions in which the user has entered the website directly to that url and has reloaded the page
- 1 session in which the user has entered the website directly to that url and then read https://www.hikeproject.com/funcion-case-en-data-studio-categoriza-limpia-o-reescribe-para-personalizar-tu-dashboard/
With this type of reports you can not only make the analysis of a specific content of your website, they are also very useful to analyze user behavior in a process or a task, for example in a flight booking page, you can analyze what users do after performing a search, what percentage of them return to the home page perhaps to perform another search, what percentage of users go to the next screen to choose fares ...
It is also very useful to make an analysis of the home page of the website especially for websites that have more than one objective in their home, returning to the example of a flight booking page: search for flights, perform the check in of a flight of a reservation, find information about a flight already purchased, contact for an incidence or doubt...
Were you using the navigation reports in Universal Analytics, and were you missing them in GA4?
...
Were you missing them in GA4?
