-- subquery to define static and/or dynamic start and end date for the whole query
with date_range as (
select
'20211222' as start_date,
format_date('%Y%m%d',date_sub(current_date(), interval 1 day)) as end_date),
-- subquery to prepare and calculate engagement data
engagement as (
select
count(distinct case when session_engaged = '1' then concat(user_pseudo_id,session_id) end) as engaged_sessions,
sum(engagement_time_msec)/1000 as engagement_time_seconds,
count(distinct case when session_engaged = '0' then concat(user_pseudo_id,session_id) end) as bounces
from (
select
user_pseudo_id,
(select value.int_value from unnest(event_params) where key = 'ga_session_id') as session_id,
max((select value.string_value from unnest(event_params) where key = 'session_engaged')) as session_engaged,
max((select value.int_value from unnest(event_params) where key = 'engagement_time_msec')) as engagement_time_msec
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
group by
user_pseudo_id,
session_id))
-- main query
select
-- sessions (metric | the total number of sessions)
count(distinct case when event_name = 'session_start' then concat(user_pseudo_id, cast(event_timestamp as string)) end) as sessions,
-- engaged sessions (metric | the number of sessions that lasted longer than 10 seconds, or had a conversion event, or had 2 or more screen or page views)
max(engaged_sessions) as engaged_sessions,
-- engagement rate (metric | the percentage of engaged sessions compared to all sessions)
safe_divide(max(engaged_sessions),count(distinct case when event_name = 'session_start' then concat(user_pseudo_id, cast(event_timestamp as string)) end)) as engagement_rate,
-- engagement time (metric | the average length of time in seconds that the app was in the foreground, or the web site had focus in the browser)
safe_divide(max(engagement_time_seconds),max(engaged_sessions)) as engagement_time,
-- bounces (metric | the total number of non-engaged sessions)
max(bounces) as bounces,
-- bounce rate (metric | bounces divided by total sessions)
safe_divide(max(bounces),count(distinct case when event_name = 'session_start' then concat(user_pseudo_id, cast(event_timestamp as string)) end)) as bounce_rate,
-- event count per session (metric | number of times an individual event (i.e. 'page_view') was triggered divided by all sessions)
safe_divide(count(distinct case when event_name = 'page_view' then concat(user_pseudo_id, cast(event_timestamp as string)) end),count(distinct case when event_name = 'session_start' then concat(user_pseudo_id, cast(event_timestamp as string)) end)) as event_count_per_session
from
-- change this to your google analytics 4 export location in bigquery
`privat-327611.analytics_266663932.events_*`,
date_range,
engagement
where
_table_suffix between date_range.start_date and date_range.end_date
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.