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: