Page tracking: dimensions & metrics (GA4)
4 min read

Page tracking: dimensions & metrics (GA4)

The most important page tracking dimensions and metrics for GA4 BigQuery export, like hostname, page, previous page path, page path level, page title, landing page, entrances and (unique) pageviews.
Page tracking: dimensions & metrics (GA4)

A lot of Google Analytics 4 data can be derived straight from the tables without performing calculations on the data. With the help of unnest and parse for dates or timestamps we can do the trick.

Other dimensions & metrics you are used to in your Google Analytics analysing and reporting are a bit harder to access. You’ll have to calculate them, using your own queries.

As I’ve tried to sort out the most used dimensions and metrics already, I hope I can be of help with the following example queries. There are plenty other, probably even better ways to achieve these results, but here is my two cents.

If you only need one dimension or metric, look at the -- comments in the example query for names and definitions and copy the part you need from the SELECT clause. Make sure that you also add any additional conditions (i.e. with, from, where, group by, having and order by) that are necessary to calculate the results correctly.

Calculated page tracking dimensions

  • hostname
  • page
  • previous page path
  • page path level 1
  • page path level 2
  • page path level 3
  • page path level 4
  • page title
  • landing page
  • second page
  • exit page

Calculated page tracking metrics

  • entrances
  • pageviews
  • unique pageviews
  • pages / session
  • exits
  • % exit

Example query

-- subquery to define static and/or dynamic start and end date for the whole query
with date_range as (
select
    '20201101' as start_date,
    format_date('%Y%m%d',date_sub(current_date(), interval 1 day)) as end_date),

-- subquery to prepare and calculate page view data
pages as (
select
    user_pseudo_id,
    (select value.int_value from unnest(event_params) where event_name = 'page_view' and key = 'ga_session_id') as session_id,
    event_timestamp,
    event_name,
    (select device.web_info.hostname from unnest(event_params) where event_name = 'page_view' and key = 'page_location') as hostname,
    (select value.string_value from unnest(event_params) where event_name = 'page_view' and key = 'page_location') as page,
    lag((select value.string_value from unnest(event_params) where event_name = 'page_view' and key = 'page_location'), 1) over (partition by user_pseudo_id,(select value.int_value from unnest(event_params) where event_name = 'page_view' and key = 'ga_session_id') order by event_timestamp asc) as previous_page,
    case when split(split((select value.string_value from unnest(event_params) where event_name = 'page_view' and key = 'page_location'),'/')[safe_ordinal(4)],'?')[safe_ordinal(1)] = '' then null else concat('/',split(split((select value.string_value from unnest(event_params) where event_name = 'page_view' and key = 'page_location'),'/')[safe_ordinal(4)],'?')[safe_ordinal(1)]) end as pagepath_level_1,
    case when split(split((select value.string_value from unnest(event_params) where event_name = 'page_view' and key = 'page_location'),'/')[safe_ordinal(5)],'?')[safe_ordinal(1)] = '' then null else concat('/',split(split((select value.string_value from unnest(event_params) where event_name = 'page_view' and key = 'page_location'),'/')[safe_ordinal(5)],'?')[safe_ordinal(1)]) end as pagepath_level_2,
    case when split(split((select value.string_value from unnest(event_params) where event_name = 'page_view' and key = 'page_location'),'/')[safe_ordinal(6)],'?')[safe_ordinal(1)] = '' then null else concat('/',split(split((select value.string_value from unnest(event_params) where event_name = 'page_view' and key = 'page_location'),'/')[safe_ordinal(6)],'?')[safe_ordinal(1)]) end as pagepath_level_3,
    case when split(split((select value.string_value from unnest(event_params) where event_name = 'page_view' and key = 'page_location'),'/')[safe_ordinal(7)],'?')[safe_ordinal(1)] = '' then null else concat('/',split(split((select value.string_value from unnest(event_params) where event_name = 'page_view' and key = 'page_location'),'/')[safe_ordinal(7)],'?')[safe_ordinal(1)]) end as pagepath_level_4,
    (select value.string_value from unnest(event_params) where event_name = 'page_view' and key = 'page_title') as page_title,
    case when (select value.int_value from unnest(event_params) where event_name = 'page_view' and key = 'entrances') = 1 then (select value.string_value from unnest(event_params) where event_name = 'page_view' and key = 'page_location') end as landing_page,
    case when (select value.int_value from unnest(event_params) where event_name = 'page_view' and key = 'entrances') = 1 then lead((select value.string_value from unnest(event_params) where event_name = 'page_view' and key = 'page_location'), 1) over (partition by user_pseudo_id,(select value.int_value from unnest(event_params) where event_name = 'page_view' and key = 'ga_session_id') order by event_timestamp asc) else null end as second_page,
    case when (select value.string_value from unnest(event_params) where event_name = 'page_view' and key = 'page_location') = first_value((select value.string_value from unnest(event_params) where event_name = 'page_view' and key = 'page_location')) over (partition by user_pseudo_id,(select value.int_value from unnest(event_params) where event_name = 'page_view' and key = 'ga_session_id') order by event_timestamp desc) then ( select value.string_value from unnest(event_params) where event_name = 'page_view' and key = 'page_location') else null end as exit_page
from
    -- change this to your google analytics 4 export location in bigquery
    `ga4bigquery.analytics_250794857.events_*`,
    date_range
where
    _table_suffix between date_range.start_date and date_range.end_date
    and event_name = 'page_view')

-- main query
select
    -- hostname (dimension | the hostname from which the tracking request was made)
    hostname,
    -- page (dimension | a page on the website specified by path and/or query parameters)
    page,
    -- previous page path (dimension | a page visited before another page on the same property)
    previous_page,
    -- page path level 1 (dimension | this dimension rolls up all the page paths in the first hierarchical level)
    pagepath_level_1,
    -- page path level 2 (dimension | this dimension rolls up all the page paths in the second hierarchical level)
    pagepath_level_2,
    -- page path level 3 (dimension | this dimension rolls up all the page paths in the third hierarchical level)
    pagepath_level_3,
    -- page path level 4 (dimension | this dimension rolls up all the page paths in the fourth hierarchical level)
    pagepath_level_4,
    -- page title (dimension | the web page's title, multiple pages might have the same page title)
    page_title,
    -- landing page (dimension | the first page in users' sessions)
    landing_page,
    -- second page (dimension | the second page in users' sessions)
    second_page,
    -- exit page (dimension | the last page in users' sessions)
    exit_page,
    -- entrances (metric | the number of entrances to the property measured as the first pageview in a session)
    count(landing_page) as entrances,
    -- pageviews (metric | the total number of pageviews for the property)
    count(page) as pageviews,
    -- unique pageviews (metric | the number of sessions during which the specified page was viewed at least once, a unique pageview is counted for each page url + page title combination)
    count(distinct concat(page,page_title,session_id)) as unique_pageviews,
    -- pages / session (metric | the average number of pages viewed during a session, including repeated views of a single page)
    count(page) / count(distinct session_id) as pages_per_session,
    -- exits (metric | the number of exits from the property)
    count(exit_page) as exits,
    -- exit % (metric | the percentage of exits from the property that occurred out of the total pageviews)
    count(exit_page) / count(page) as exit_rate
from
    pages,
    date_range
group by
    hostname,
    page,
    previous_page,
    pagepath_level_1,
    pagepath_level_2,
    pagepath_level_3,
    pagepath_level_4,
    page_title,
    landing_page,
    second_page,
    exit_page

Enjoying this post? Subscribe to the GA4BigQuery newsletter!

You will receive regular updates (max 1x/week) straight in your inbox. You can unsubscribe anytime.