15 January, 2025 BY Laura Cloquell

BigQuery + GA4: Page Navigation Report

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:

UA - Navigation Summary

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: 

BQ - Query Results-1

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:

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?

 

 

 
PREVIOUS
NEXT

TIPS DE EXPERTOS

Suscríbete para impulsar tu negocio.

LATESTS ARTICLES

16 July, 2025 POR Alba Fiorella Aviles
crm Data management AI Automation

The seventh wave of AI is redefining CRM and data strategy

Artificial intelligence is not just another improvement: it is, in the words of George Colony, CEO of Forrester, the seventh wave of transformation that will redefine the technology sector. This change directly affects CRM, analytics, and marketing automation, forcing companies to adapt or be left behind.File:George Colony in 2011.jpg
SEGUIR LEYENDO
14 July, 2025 POR Luz Castillejo Flor
Marketing strategy Tracking data stratergy third-party cookies

How the end of third-party cookies impacts your marketing strategy

The announcement of the definitive elimination of third-party cookies marks a turning point in the digital ecosystem. This is not just a technical adjustment in browsers: we are talking about a structural change in the way companies collect data, activate advertising campaigns, and manage customer relationships.

And although it may seem like a distant issue or one exclusive to large corporations, the reality is that it affects any business that uses digital advertising, email marketing, retargeting strategies, or affiliate programs.
That’s why understanding its impact and knowing how to prepare is key to staying competitive.

What are third-party cookies and why are they disappearing?

Third-party cookies: the foundation of digital marketing until now

A third-party cookie is a file placed on your browser by a provider other than the website you are visiting.
For example, if you visit a blog that uses Google or Facebook ads, those systems install cookies that track your behavior—even when you browse other sites.

Thanks to those cookies, advertisers could:

  • Follow you throughout your browsing.

  • Show you ads based on your interests and behavior.

  • Measure the impact of their campaigns.

  • Build detailed profiles without requiring you to register or provide data.

In short: third-party cookies were the backbone of programmatic advertising and retargeting.

Why are they being eliminated?

The official reason is user privacy protection.
More and more users demand control over their personal data and how it’s used. Regulations like GDPR in Europe and CCPA in California have forced major players (Google, Apple, Mozilla) to move toward a more privacy-friendly model.

But there is another angle:
Google, owner of Chrome and a leader in digital advertising, is redefining the game to maintain market control and limit competition. By eliminating third-party cookies, Google ensures that only those who manage first-party data or operate within its platforms can effectively reach users.

The three major pillars changing after the elimination of cookies

1. Campaign measurement and attribution

Until now, measuring the impact of a multichannel campaign (ads, email, web visits) relied on attribution models based on cookies.
For example:

If a user saw an ad on Instagram, clicked on a Google ad, and then made a purchase on the website, cookies helped trace that path.

What happens without third-party cookies?

  • Conversions attributed to third parties will decrease.

  • The user journey will be harder to track.

  • “Last-click” or “multi-touch” measurement becomes less reliable.

How to adapt?

  • Prioritize first-party data measurement by connecting your CRM with analytics platforms.

  • Implement solutions like Google Enhanced Conversions or server-side tagging, which allow more accurate measurement without relying on cookies.

  • Explore proprietary attribution models, such as integrating sales or CRM systems with analytics tools.

2. Audience segmentation and activation

The end of retargeting as we knew it.
Without third-party cookies, platforms can no longer create audiences based on behavior across different websites. This directly affects:

  • Programmatic advertising.

  • Dynamic retargeting campaigns.

  • Affiliate campaigns based on cross-site tracking.

How to adapt?

  • Enhance your first-party data: encourage registration, subscriptions, and account creation.

  • Use activation tools like Customer Match (Google Ads) or Audiences (Meta), which let you upload your own data to reach those users on their platforms.

  • Work on lookalike strategies based on your own customer data, not third-party data.

  • Leverage contextual advertising by showing ads related to the content being consumed—without needing to know the user’s identity.

3. First-party data management and value

The direct consequence of this change is that first-party data becomes the most valuable asset of a digital company.
Without the ability to buy audiences based on cookies, you need to build your own database with real, interested users with whom you can maintain a direct relationship.

This means:

  • Developing acquisition strategies based on value: lead magnets, quality content, incentives for registration.

  • Creating automated, personalized communication flows from your CRM.

  • Focusing on the quality of the relationship, not just the quantity of impacts.

How to adapt?

  • Strengthen your lead generation strategies and improve your registration forms.

  • Implement a CDP (Customer Data Platform) if you handle large volumes, or ensure your CRM is well integrated with your marketing platforms.

  • Take care of the user experience to avoid intrusive practices like aggressive pop-ups or forced capture.

What alternatives does the market propose after the elimination of cookies?

  • FLoC and Privacy Sandbox (Google): Google proposes alternative systems based on cohorts, where users are grouped by interests without being individually identified. These proposals still generate debate over their effectiveness and privacy.

  • Data Clean Rooms: Secure environments where data from different parties (advertisers, platforms) can be matched without revealing user identities. Costly but necessary for major advertisers.

  • Contextual advertising: Making a comeback. Showing ads related to the content being visited, with no need to know who the user is.

  • Server-side models: Collecting and activating data from the server side is a technical alternative for measuring and segmenting without relying on traditional cookies.

What should companies do to adapt (and not just survive)?

  • Invest in a data strategy:
    Organize, structure, and connect your databases with your marketing tools.
    First-party data is a strategic asset—not just a list of emails.

  • Train your teams:
    Not just the marketing department. Sales, customer service, IT… everyone needs to understand the value of data and how it’s managed.

  • Strengthen customer trust:
    Transparency and good privacy management will be differentiators. Clearly explaining how you use data builds trust and, in the long term, conversion.

  • Commit to personalized omnichannel experiences:
    The CRM should be the center of a strategy where the user receives coherent impacts across all channels (web, email, app, social).

  • Prepare for new measurement methods:
    Invest in server-side solutions, predictive models, and tools that allow you to measure impact beyond cookies.

Conclusion: Threat or opportunity?

The end of third-party cookies is not the end of advertising or digital marketing.
It is the beginning of a new paradigm where companies that invest in:

  • Building their first-party data.

  • Truly integrating their systems.

  • Personalizing based on a deep understanding of the customer.

… will be the ones to take the biggest slice of the pie.

Because if one thing is clear, it’s that data remains important…
You just have to earn it now.

Schedule a meeting

SEGUIR LEYENDO
9 July, 2025 POR Luz Castillejo Flor
data base data architecture data quality

No solid base, no AI performance: the challenge of the Data Foundation

In a business context where AI has become the new standard for efficiency and scalability, many organizations face a paradox: they have advanced technology, but they fail to achieve consistent results. The issue usually isn’t the algorithm—it’s the foundation. The Data Foundation is the true determinant of success or failure for any AI, automation, or CRM strategy.

This is confirmed by the latest TDWI (Transforming Data With Intelligence) study, published in June 2025, which warns that more than 49% of companies still lack a database ready to scale artificial intelligence projects.

The Data Foundation: more than just infrastructure

Having a modern data platform doesn’t mean having a solid foundation. The TDWI study emphasizes that an effective Data Foundation must meet three conditions:

  • Data quality and governance from the source
  • Scalable and connected architecture
  • Real-time activation capability

When a company fails in any of these three areas, AI becomes more of a promise than a real business lever.

Key findings from the study

Here are some of the main conclusions of the report:

Only 10% of companies claim to have a fully operational Data Foundation.
40% report severe limitations due to poor data quality, silos, or outdated processes.
Most organizations suffer from fragmentation across data sources, preventing a 360-degree view of the customer.
55% of companies already using AI operationally do so despite their technical limitations, not because of their strengths.

In other words, many companies are running with a backpack full of ballast. And that limits the performance of their AI, automation, or CRM tools.

Why does this matter for your CRM or marketing?

At Hike&Foxter, we see it frequently: companies investing in advanced CRMs, analytics platforms, or generative AI engines… without first securing the technical and structural foundation of their data.

The result:

  • AI models that fail in production.
  • Automations triggered incorrectly.
  • Unreliable analytics reports.
  • Inconsistent customer segmentations.

All of this can be avoided with a well-designed Data Foundation, connected to key processes and with controlled data flows.

How to build a real Data Foundation

These are the phases we recommend implementing if you want to turn your data architecture into a competitive advantage:

1. Technical and functional audit

Before incorporating AI, it's important to review:

What data sources exist and how they are integrated
The degree of duplication, obsolescence, or noise they contain
Where the main bottlenecks are (latency, format, access)

2. Standardization and governance

Without a common taxonomy and control rules, any automation attempt will be fragile. This involves:

Defining unified structures (customers, products, interactions…)
Establishing automatic validation rules
Creating clear roles: who creates, modifies, or validates data?

3. Connected and flexible architecture

A data warehouse alone is no longer enough. You need to:

Connect CRM with analytics, automation, and digital channels
Use scalable environments (Snowflake, BigQuery, Azure Fabric)
Consider data mesh or federated architecture if there are multiple business units

4. Real-time activation

The value of AI lies not just in predictive analysis but in its ability to act.

Therefore:

Connect your Data Foundation with activation tools (such as Customer Data Platforms, personalization engines, RPA)
Ensure data flows in real time
Prioritize use cases with direct business impact (retention, up-selling, lead scoring…)

Conclusion

Investing in AI, automation, or CRM platforms without a solid Data Foundation is like building a house on sand.
Before thinking about “which model to use,” you should ask yourself “what data feeds it and how is it governed?”

A robust and well-connected infrastructure not only improves your current projects but also prepares you for what’s next: autonomous agents, contextual decisions, predictive personalization, and end-to-end automation.

Want to strengthen your Data & Tech Foundation?

At Hike&Foxter, we help you build the digital foundations your business needs to grow with confidence.

Schedule a meeting

SEGUIR LEYENDO
9 July, 2025 POR Luz Castillejo Flor
Artificial Intelligence digital transformation Google I/O search engine

Google transforms its search engine with Artificial Intelligence.

In May 2025, Google took a decisive step toward transforming the world’s leading search engine.
At its highly anticipated annual developer event, Google I/O, the Mountain View-based company unveiled a host of innovations powered by artificial intelligence (AI) that not only enhance user experience but are set to redefine how we interact with digital information.
SEGUIR LEYENDO
VIEW MORE
data
Mallorca 184, 08036
Barcelona, Spain