-- 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
Ich verwende Cookies auf meiner Website, um Ihnen das beste Nutzererlebnis zu bieten. Indem Sie auf "Akzeptieren" klicken, erklären Sie sich mit der Verwendung von ALLEN Cookies einverstanden.
Diese Website verwendet Cookies, um Ihre Erfahrung zu verbessern, während Sie durch die Website navigieren. Von diesen werden die als notwendig eingestuften Cookies auf Ihrem Browser gespeichert, da sie für das Funktionieren der grundlegenden Funktionen der Website unerlässlich sind. Wir verwenden auch Cookies von Drittanbietern, die uns helfen zu analysieren und zu verstehen, wie Sie diese Website nutzen. Diese Cookies werden nur mit Ihrer Zustimmung in Ihrem Browser gespeichert. Sie haben auch die Möglichkeit, diese Cookies abzulehnen. Das Ablehnen einiger dieser Cookies kann jedoch Ihr Surferlebnis beeinträchtigen.
Necessary cookies are absolutely essential for the website to function properly. These cookies ensure basic functionalities and security features of the website, anonymously.
Cookie
Dauer
Beschreibung
cookielawinfo-checbox-analytics
11 months
This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checbox-functional
11 months
The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checbox-others
11 months
This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-necessary
11 months
This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-performance
11 months
This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy
11 months
The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.
Functional cookies help to perform certain functionalities like sharing the content of the website on social media platforms, collect feedbacks, and other third-party features.
Performance cookies are used to understand and analyze the key performance indexes of the website which helps in delivering a better user experience for the visitors.
Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics the number of visitors, bounce rate, traffic source, etc.
Advertisement cookies are used to provide visitors with relevant ads and marketing campaigns. These cookies track visitors across websites and collect information to provide customized ads.