-- subquery to define static and/or dynamic start and end date for the whole querywith date_range as (select'20211224'asstart_date, format_date('%Y%m%d',date_sub(current_date(), interval 1day)) as end_date),-- subquery to prepare and calculate page view datapages as (select user_pseudo_id, (select value.int_value from unnest(event_params) where event_name ='page_view'andkey='ga_session_id') as session_id, event_timestamp, event_name, (select device.web_info.hostname from unnest(event_params) where event_name ='page_view'andkey='page_location') as hostname, (select value.string_value from unnest(event_params) where event_name ='page_view'andkey='page_location') aspage,lag((select value.string_value from unnest(event_params) where event_name ='page_view'andkey='page_location'), 1) over (partitionby user_pseudo_id,(select value.int_value from unnest(event_params) where event_name ='page_view'andkey='ga_session_id') order by event_timestamp asc) as previous_page,casewhen split(split((select value.string_value from unnest(event_params) where event_name ='page_view'andkey='page_location'),'/')[safe_ordinal(4)],'?')[safe_ordinal(1)] =''thennullelseconcat('/',split(split((select value.string_value from unnest(event_params) where event_name ='page_view'andkey='page_location'),'/')[safe_ordinal(4)],'?')[safe_ordinal(1)]) endas pagepath_level_1,casewhen split(split((select value.string_value from unnest(event_params) where event_name ='page_view'andkey='page_location'),'/')[safe_ordinal(5)],'?')[safe_ordinal(1)] =''thennullelseconcat('/',split(split((select value.string_value from unnest(event_params) where event_name ='page_view'andkey='page_location'),'/')[safe_ordinal(5)],'?')[safe_ordinal(1)]) endas pagepath_level_2,casewhen split(split((select value.string_value from unnest(event_params) where event_name ='page_view'andkey='page_location'),'/')[safe_ordinal(6)],'?')[safe_ordinal(1)] =''thennullelseconcat('/',split(split((select value.string_value from unnest(event_params) where event_name ='page_view'andkey='page_location'),'/')[safe_ordinal(6)],'?')[safe_ordinal(1)]) endas pagepath_level_3,casewhen split(split((select value.string_value from unnest(event_params) where event_name ='page_view'andkey='page_location'),'/')[safe_ordinal(7)],'?')[safe_ordinal(1)] =''thennullelseconcat('/',split(split((select value.string_value from unnest(event_params) where event_name ='page_view'andkey='page_location'),'/')[safe_ordinal(7)],'?')[safe_ordinal(1)]) endas pagepath_level_4, (select value.string_value from unnest(event_params) where event_name ='page_view'andkey='page_title') as page_title,casewhen (select value.int_value from unnest(event_params) where event_name ='page_view'andkey='entrances') =1then (select value.string_value from unnest(event_params) where event_name ='page_view'andkey='page_location') endas landing_page,casewhen (select value.int_value from unnest(event_params) where event_name ='page_view'andkey='entrances') =1thenlead((select value.string_value from unnest(event_params) where event_name ='page_view'andkey='page_location'), 1) over (partitionby user_pseudo_id,(select value.int_value from unnest(event_params) where event_name ='page_view'andkey='ga_session_id') order by event_timestamp asc) elsenullendas second_page,casewhen (select value.string_value from unnest(event_params) where event_name ='page_view'andkey='page_location') =first_value((select value.string_value from unnest(event_params) where event_name ='page_view'andkey='page_location')) over (partitionby user_pseudo_id,(select value.int_value from unnest(event_params) where event_name ='page_view'andkey='ga_session_id') order by event_timestamp desc) then ( select value.string_value from unnest(event_params) where event_name ='page_view'andkey='page_location') elsenullendas exit_pagefrom-- change this to your google analytics 4 export location in bigquery`privat-327611.analytics_266663932.events_*`, date_rangewhere _table_suffix between date_range.start_date and date_range.end_dateand event_name ='page_view')-- main queryselect-- 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(distinctconcat(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_ratefrom pages, date_rangegroup 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