Google Analytics 4 Unique Pageviews in BigQuery

There are two methods to calculate unique pageviews in GA4 BigQuery:

Method-1 (Popular but incorrect): Count the unique combination of ‘user_pseudo_id’ and ‘ga_session_id’ where event_name is ‘page_view’ for each Page Title. 

Method-2 (Correct): Count the unique combination of ‘user_pseudo_id’, ‘ga_session_id’, and ‘page_title’ where event_name is ‘page_view’ for each Page Title. 

Your unique pageviews are only as accurate as the “KEY” you use for their calculations.

Most BigQuery users use the following key for calculating unique pageviews: 

‘user_pseudo_id’ + ‘ga_session_id’

The following is the correct key for calculating unique pageviews in GA4 BigQuery:

user_pseudo_id + ga_session_id + page_title

If a session contains multiple views of different pages, using ‘user_pseudo_id’ + ‘ga_session_id’ alone will group all pages viewed in the same session as one unique pageview for the session. This can lead to undercounting unique pageviews at the page level.

Use the following SQL (automatically generated via a text prompt in ChatGPT) to calculate Google Analytics 4 Unique Pageviews in BigQuery:

-- Calculate Page Titles, Page Paths, Pageviews, Unique Pageviews including totals.

WITH
  page_data AS (
    SELECT
      IFNULL(
        (
          SELECT value.string_value
          FROM UNNEST(event_params)
          WHERE key = 'page_title'
        ),
        '(not set)') AS page_title,
      IFNULL(
        (
          SELECT value.string_value
          FROM UNNEST(event_params)
          WHERE key = 'page_location'
        ),
        '(not set)') AS page_path,
      COALESCE(user_pseudo_id, 'unknown_user') AS user_id,
      COALESCE(
        CAST(
          (
            SELECT value.int_value
            FROM UNNEST(event_params)
            WHERE key = 'ga_session_id'
          )
          AS STRING),
        'unknown_session') AS session_id
    FROM
      `dbrt-ga4.analytics_207472454.events_*`
    WHERE
      _TABLE_SUFFIX BETWEEN '20241001' AND '20241031'
      AND event_name = 'page_view'
  ),
  aggregated_data AS (
    SELECT
      page_title,
      page_path,
      COUNT(*) AS views,
      COUNT(DISTINCT CONCAT(user_id, session_id, page_title))
        AS unique_pageviews
    FROM
      page_data
    GROUP BY
      page_title, page_path
  ),
  total_data AS (
    SELECT
      'Total' AS page_title,
      '' AS page_path,  -- Use an empty string to represent an empty value
      SUM(views) AS views,
      SUM(unique_pageviews) AS unique_pageviews
    FROM
      aggregated_data
  )
SELECT
  page_title,
  page_path,
  views,
  unique_pageviews
FROM
  (
    SELECT * FROM total_data
    UNION ALL
    SELECT * FROM aggregated_data
  )
ORDER BY
  views 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 Unique pageviews 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.