Google Analytics 4 Exit Page Report in BigQuery

Follow me on LinkedIn - AI, GA4, BigQuery

Use the following SQL (automatically generated via a text prompt in ChatGPT) to create a Google Analytics 4 Exit Page report in BigQuery:

-- Calculating Exit Pages and Exits including totals.

WITH
  base_events AS (
    SELECT
      user_pseudo_id,
      SAFE_CAST(
        (
          SELECT value.int_value
          FROM UNNEST(event_params)
          WHERE key = 'ga_session_id'
        )
        AS INT64) AS ga_session_id,
      (
        SELECT value.string_value
        FROM UNNEST(event_params)
        WHERE key = 'page_location'
      ) AS page_location,
      event_bundle_sequence_id,
      event_timestamp
    FROM `dbrt-ga4.analytics_207472454.events_*`
    WHERE
      event_name = 'page_view'
      AND _TABLE_SUFFIX BETWEEN '20241001' AND '20241031'
  ),
  last_page_views AS (
    SELECT
      user_pseudo_id,
      ga_session_id,
      CASE
        WHEN
          page_location IS NULL
          OR page_location = ''
          OR page_location = 'https://optimizesmart.com/'
          THEN '(not set)'
        ELSE page_location
        END AS exit_page,
      ROW_NUMBER()
        OVER (
          PARTITION BY user_pseudo_id, ga_session_id
          ORDER BY event_bundle_sequence_id DESC, event_timestamp DESC
        ) AS row_num
    FROM base_events
  ),
  exit_counts AS (
    SELECT
      exit_page,
      COUNT(DISTINCT CONCAT(user_pseudo_id, ga_session_id)) AS exits
    FROM last_page_views
    WHERE row_num = 1
    GROUP BY exit_page
  ),
  final_table AS (
    SELECT
      exit_page AS exit_pages,
      exits
    FROM exit_counts
  )
SELECT
  'Total' AS exit_pages,
  SUM(exits) AS exits
FROM final_table
UNION ALL
SELECT *
FROM final_table
ORDER BY exits DESC;

I am not a fan of cookie-cutter reports. But if you want one, this one could be for you.

All you have to do is supply your table ID to generate SQL that works for you.

If you want to customize this report according to your unique data analysis requirements then you need to understand the “logic” used behind calculating the various dimensions and metrics used in this report.

Once you understand the logic, customizing this report is easy peasy.

I teach you such logic in my GA4 BigQuery course, where you can learn to automate SQL generation via text prompts in ChatGPT.

There could be ‘N’ use cases, and it won’t be possible to provide ready-made SQL code for every possible case.

So, it is better that you understand the logic to scale SQL generation to the moon.

The future belongs to those who can query raw GA4 data in BigQuery, apply SQL logic, automate SQL generation via AI and drive insights beyond what GA4 UI can offer.

Related Articles:

Himanshu Sharma