Google Analytics 4 Page Title, Page Path & Views in BigQuery

Follow me on LinkedIn - AI, GA4, BigQuery

Use the following SQL (automatically generated via a text prompt in ChatGPT) to calculate Google Analytics 4 Page Title, Page Paths and Views report in BigQuery:

-- Calculate Page Titles, Page Paths and 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,
    COUNTIF(event_name = 'page_view') AS views
  FROM
    `dbrt-ga4.analytics_207472454.events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN '20241001' AND '20241031'
  GROUP BY
    page_title, page_path
),

total_views AS (
  SELECT
    'Total' AS page_title,
    '' AS page_path,
    SUM(views) AS views
  FROM
    page_data
)

SELECT
  page_title,
  page_path,
  views
FROM (
  SELECT * FROM total_views
  UNION ALL
  SELECT * FROM page_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 Page Title, Page Path & Views 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