Select
-- traffic_source.name (dimension | name of the marketing campaign that first acquired the user)
traffic_source.name,
-- traffic_source.medium (dimension | name of the medium (paid search, organic search, email, etc.) that first acquired the user)
traffic_source.medium,
-- traffic_source.source (dimension | name of the network that first acquired the user)
traffic_source.source
from
-- change this to your google analytics 4 export location in bigquery
`privat-327611.analytics_266663932.events_*`
where
-- define static and/or dynamic start and end date
_table_suffix between '20211222' and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))
Berechnete Traffic-Quellen-Dimensionen
default channel grouping (user or session)
source / medium (user or session)
campaign (user or session)
full referrer
-- subquery to set the start and end date once 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 traffic source data based on user and session id
traffic as (
select
user_pseudo_id,
session_id,
session_number,
concat(source,' / ',medium) as source_medium_session,
case when campaign_session is null then '(direct)' else campaign_session end as campaign_session,
full_referrer,
-- definitions of the channel grouping based on the source / medium of every session
case
when source = '(direct)' and (medium = '(not set)' or medium = '(none)') then 'Direct'
when medium = 'organic' then 'Organic Search'
when regexp_contains(medium, r'^(social|social-network|social-media|sm|social network|social media)$') then 'Social'
when medium = 'email' then 'Email'
when medium = 'affiliate' then 'Affiliates'
when medium = 'referral' then 'Referral'
when regexp_contains(medium, r'^(cpc|ppc|paidsearch)$') then 'Paid Search'
when regexp_contains(medium, r' ^(cpv|cpa|cpp|content-text)$') then 'Other Advertising'
when regexp_contains(medium, r'^(display|cpm|banner)$') then 'Display'
else '(Other)' end as default_channel_grouping_session,
source_medium_user,
campaign_user,
default_channel_grouping_user
from (
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,
(select value.int_value from unnest(event_params) where event_name = 'page_view' and key = 'ga_session_number') as session_number,
event_timestamp,
rank() 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) as rank,
case when (select value.string_value from unnest(event_params) where event_name = 'page_view' and key = 'source') is null then '(direct)' else (select value.string_value from unnest(event_params) where event_name = 'page_view' and key = 'source') end as source,
case when (select value.string_value from unnest(event_params) where event_name = 'page_view' and key = 'medium') is null then '(none)' else (select value.string_value from unnest(event_params) where event_name = 'page_view' and key = 'medium') end as medium,
(select value.string_value from unnest(event_params) where event_name = 'page_view' and key = 'campaign') as campaign_session,
(select value.string_value from unnest(event_params) where event_name = 'page_view' and key = 'page_referrer') as full_referrer,
concat(traffic_source.source,' / ',traffic_source.medium) as source_medium_user,
traffic_source.name as campaign_user,
-- definitions of the channel grouping based on the source / medium of a user's first session
case
when traffic_source.source = '(direct)' and (traffic_source.medium = '(not set)' or traffic_source.medium = '(none)') then 'Direct'
when traffic_source.medium = 'organic' then 'Organic search'
when regexp_contains(traffic_source.medium, r'^(social|social-network|social-media|sm|social network|social media)$') then 'Social'
when traffic_source.medium = 'email' then 'Email'
when traffic_source.medium = 'affiliate' then 'Affiliates'
when traffic_source.medium = 'referral' then 'Referral'
when regexp_contains(traffic_source.medium, r'^(cpc|ppc|paidsearch)$') then 'Paid Search'
when regexp_contains(traffic_source.medium, r' ^(cpv|cpa|cpp|content-text)$') then 'Other Advertising'
when regexp_contains(traffic_source.medium, r'^(display|cpm|banner)$') then 'Display'
else '(Other)' end as default_channel_grouping_user
from
-- change this to your google analytics 4 bigquery export location
`privat-327611.analytics_266663932.events_*`,
date_range
where
_table_suffix between date_range.start_date and date_range.end_date
and (select value.int_value from unnest(event_params) where event_name = 'page_view' and key = 'ga_session_id') is not null)
where
rank = 1)
-- main query
select
-- user default channel grouping (dimension | the channel group associated with an user's first session)
default_channel_grouping_user,
-- user source / medium (dimension | the referral source and type associated with an user's first session)
source_medium_user,
-- user campaign (dimension | the value of a campaign associated with an user's first session)
campaign_user,
-- session default channel grouping (dimension | the channel group associated with a session)
default_channel_grouping_session,
-- session source / medium (dimension | the referral source and type associated with a session)
source_medium_session,
-- session campaign (dimension | the value of a campaign associated with a session)
campaign_session,
-- session full referrer (dimension | the full referring url of a session, including the hostname and path)
full_referrer
from
traffic
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.