BigQuery Abfrage für Google Analytics 4 (GA4): Page Tracking: Dimensionen und Metriken

-- subquery to define static and/or dynamic start and end date for the whole query
with date_range as (
select
    '20211224' 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
    `privat-327611.analytics_266663932.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