Create a GA4 Page Performance Report in BigQuery

Generate a list of the top 10 most viewed pages on your site, counting views, users and views per user.

Query overview

This query will provide you with a list of the top pages on your website, based on the number of unique users, total views and views per user.

Dimensions:

• Page (URL)

Metrics:

• Views
• Total Users
• Views per User

Time Period:

• Last 7 days (you can modify this in the query)

Note: refer to the comments inline below for an explanation of the code.

SELECT
  -- extract the page URL from event_params
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page,
  -- count the no. of events
  COUNT(*) AS views,
  -- count the no. of unique users
  COUNT(DISTINCT user_pseudo_id) AS total_users,
  -- divide views by total users and round the result to 1 decimal place
  ROUND(COUNT(*) / COUNT(DISTINCT user_pseudo_id), 1) AS views_per_user
-- update with your project and dataset here
FROM `<project>.<dataset>.events_*`
-- update timeframe here (currently set to last 7 days)
WHERE _TABLE_SUFFIX BETWEEN
  FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 7 day))
  AND FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 day))
-- filter to page_view events only
AND event_name = 'page_view'
-- aggregate results by the 1st column (page)
GROUP BY 1
-- order by the 2nd column (views) in descending order
ORDER BY 2 DESC
-- limit results to the top 10
LIMIT 10