Previous and next page path using BigQuery

Introduction

Google Analytics 4 (GA4) is a powerhouse, offering profound insights into user behavior and website performance. To harness GA4’s full potential, mastery of SQL queries is indispensable. This query unravels the intricacies of user interactions, specifically focusing on the paths users take between pages mainy previous and next page path.

The provided code dissects the data, offering detailed insights into previous and next page paths. Uncover the power of GA4 analytics to not only comprehend user behavior but also to optimize website flow, enhance user experiences, and tailor content strategies for maximum impact. Join us on this journey as we decode the intricacies of the code, transforming raw data into actionable intelligence.

The SQL queries provided in this blog post are tailored for execution in BigQuery, Google’s enterprise data warehouse. BigQuery is a cloud-based platform designed for fast and efficient analysis of large datasets.This post is an invitation to wield GA4 and SQL queries as precision instruments, transforming raw data into actionable intelligence for digital success. Happy querying!

The SQL Query:

with events as (
  select
    concat(
      user_pseudo_id,
      (
        select
          value.int_value
        from
          unnest(event_params)
        where
          key = 'ga_session_id'
      )
    ) as unique_session_id,
    event_name,
    event_timestamp,
    regexp_replace(
  regexp_replace(
    (
      select
        p.value.string_value
      from
        unnest(event_params) as p
      where
        p.key = 'page_location'
    ),
    r'^https?://[^/]+',
    ''
  ),
  r'[\?].*',
  ''
) as page_path
  from
    `add_yor_dataset_name_here_202310*`
)
select
  unique_session_id,
  event_name,
  page_path,
  event_timestamp,

  if(
    event_name = 'page_view',
    coalesce(
      last_value(
        if(event_name = 'page_view', page_path, null) ignore nulls
      ) over(
        partition by unique_session_id
        order by
          event_timestamp asc rows between unbounded preceding
          and 1 preceding
      ),
      '(entrance)'
    ),
    null
  ) as previous_page,

  if(
    event_name = 'page_view',
    coalesce(
      first_value(
        if(event_name = 'page_view', page_path, null) ignore nulls
      ) over(
        partition by unique_session_id
        order by
          event_timestamp asc rows between 1 following
          and unbounded following
      ),
      '(exit)'
    ),
    null
  ) as next_page
from
  events

Breaking Down the Query:

1. Common Table Expression (CTE) – events:
   • Organizes raw data into a CTE, creating a structured foundation for subsequent analysis.
   • Extracts essential information, including unique_session_id, event_name, event_timestamp, and sanitized page_path.
2. Extracting Previous and Next Page Paths:
   • Utilizes REGEXP_REPLACE to clean page_path, removing unnecessary URL components and ensuring consistency in data.
   • Implements conditional logic to determine the previous and next page paths for each user session.
3. Query Results:
   • The main query unveils crucial insights:
     • unique_session_id: Unique identifier for each user session.
     • event_name: Type of event (e.g., ‘page_view’).
     • page_path: The current page path.
     • event_timestamp: Timestamp of the event.
     • previous_page: The path of the previous page viewed by the user.
     • next_page: The path of the next page to be viewed by the user.

Output Table:

Conclusion:

This GA4 SQL query goes beyond traditional analytics, shedding light on the user journey by uncovering both previous and next page paths. Businesses can leverage these insights to optimize website flow, enhance user experiences, and tailor content strategies.

Discover a treasure trove of powerful GA4 queries in our primary blog post, “Top Queries within Google Analytics 4.” Go to our blog area here to read more intriguing blogs of this kind.