Traffic source and user acquisition dimensions & metrics (GA4)

A lot of Google Analytics 4 dimensions and metrics can be derived straight from the tables without performing calculations on the data. With the help of unnest and parse for dates or timestamps we can do the trick.

Other dimensions and metrics that you might need are a bit harder to access. You will have to (re)calculate them, which can require some serious SQL-skills.

While designing the course Query GA4 Data In Google BigQuery I've learned a lot about best practices to calculate dimensions and metrics. To share this knowledge with you I will provide a combined query for default dimensions and metrics, and a single example query for every non-default dimension or metric.

As always, drop a line in the comments if you have any questions, feedback or suggestions related to this article.

Default traffic source and user acquisition dimensions

• traffic_source.name (campaign, based on total users)
• traffic_source.medium (based on total users)
• traffic_source.source (based on total users)

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
    `ga4bigquery.analytics_250794857.events_*`
where
    -- define static and/or dynamic start and end date
    _table_suffix between '20220901'
    and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))

Calculated traffic source and user acquisition dimensions

• campaign (based on sessions)

with prep as (
select
    user_pseudo_id,
    (select value.int_value from unnest(event_params) where key = 'ga_session_id') as session_id,
    array_agg((select value.string_value from unnest(event_params) where key = 'campaign') ignore nulls order by event_timestamp)[safe_offset(0)] as campaign,
from
    -- change this to your google analytics 4 export location in bigquery
    `ga4bigquery.analytics_250794857.events_*`
where
    -- define static and/or dynamic start and end date
    _table_suffix between '20220901'
    and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))
group by
    user_pseudo_id,
    session_id)

select
    -- session campaign (dimension | the value of a campaign associated with a session)
    coalesce(campaign,'(not set)') as campaign_session,
    count(distinct concat(user_pseudo_id,session_id)) as sessions
from
    prep
group by
    campaign_session
order by
    sessions desc

• medium (based on session)

with prep as (
select
	user_pseudo_id,
    (select value.int_value from unnest(event_params) where key = 'ga_session_id') as session_id,
    array_agg((select value.string_value from unnest(event_params) where key = 'medium') ignore nulls order by event_timestamp)[safe_offset(0)] as medium
from
    -- change this to your google analytics 4 export location in bigquery
    `ga4bigquery.analytics_250794857.events_*`
where
    -- define static and/or dynamic start and end date
    _table_suffix between '20220901'
    and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))
group by
    user_pseudo_id,
    session_id)

select
	-- session medium (dimension | the value of a medium associated with a session)
    coalesce(medium,'(none)') as medium_session,
    count(distinct concat(user_pseudo_id,session_id)) as sessions
from
    prep
group by
    medium_session
order by
    sessions desc

• source (based on session)

with prep as (
select
	user_pseudo_id,
    (select value.int_value from unnest(event_params) where key = 'ga_session_id') as session_id,
    array_agg((select value.string_value from unnest(event_params) where key = 'source') ignore nulls order by event_timestamp)[safe_offset(0)] as source
from
    -- change this to your google analytics 4 export location in bigquery
    `ga4bigquery.analytics_250794857.events_*`
where
    -- define static and/or dynamic start and end date
    _table_suffix between '20220901'
    and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))
group by
    user_pseudo_id,
    session_id)

select
    -- session source (dimension | the value of a source associated with a session)
    coalesce(source,'(direct)') as source_session,
    count(distinct concat(user_pseudo_id,session_id)) as sessions
from
    prep
group by
    source_session
order by
    sessions desc

• source / medium (based on user)

select
    concat(coalesce(traffic_source.source,'(direct)'),' / ',coalesce(traffic_source.medium,'(none)')) as source_medium_user,
    count(distinct user_pseudo_id) as users
from
    -- change this to your google analytics 4 export location in bigquery
    `ga4bigquery.analytics_250794857.events_*`
where
    -- define static and/or dynamic start and end date
    _table_suffix between '20220901'
    and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))
group by
    source_medium_user
order by
    users desc

• source / medium (based on session)

with prep as (
select
    user_pseudo_id,
    (select value.int_value from unnest(event_params) where key = 'ga_session_id') as session_id,
    array_agg((select value.string_value from unnest(event_params) where key = 'source') ignore nulls order by event_timestamp)[safe_offset(0)] as source,
    array_agg((select value.string_value from unnest(event_params) where key = 'medium') ignore nulls order by event_timestamp)[safe_offset(0)] as medium
from
    -- change this to your google analytics 4 export location in bigquery
    `ga4bigquery.analytics_250794857.events_*`
where
    -- define static and/or dynamic start and end date
    _table_suffix between '20220901'
    and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))
group by
    user_pseudo_id,
    session_id)

select
    -- session source / medium (dimension | the value of a source and medium associated with a session)
    concat(coalesce(source,'(direct)'),' / ',coalesce(medium,'(none)')) as source_medium_session,
    count(distinct concat(user_pseudo_id,session_id)) as sessions
from
    prep
group by
    source_medium_session
order by
    sessions desc

• default channel grouping (based on user)

select
    -- user default channel grouping (dimension | the channel group associated with an user's first session)
    case 
        when traffic_source.source = '(direct)' and (traffic_source.medium in ('(not set)','(none)')) then 'Direct'
        when regexp_contains(traffic_source.name, 'cross-network') then 'Cross-network'
        when (regexp_contains(traffic_source.source,'alibaba|amazon|google shopping|shopify|etsy|ebay|stripe|walmart')
            or regexp_contains(traffic_source.name, '^(.*(([^a-df-z]|^)shop|shopping).*)$'))
            and regexp_contains(traffic_source.medium, '^(.*cp.*|ppc|paid.*)$') then 'Paid Shopping'
        when regexp_contains(traffic_source.source,'baidu|bing|duckduckgo|ecosia|google|yahoo|yandex')
            and regexp_contains(traffic_source.medium,'^(.*cp.*|ppc|paid.*)$') then 'Paid Search'
        when regexp_contains(traffic_source.source,'badoo|facebook|fb|instagram|linkedin|pinterest|tiktok|twitter|whatsapp')
            and regexp_contains(traffic_source.medium,'^(.*cp.*|ppc|paid.*)$') then 'Paid Social'
        when regexp_contains(traffic_source.source,'dailymotion|disneyplus|netflix|youtube|vimeo|twitch|vimeo|youtube')
            and regexp_contains(traffic_source.medium,'^(.*cp.*|ppc|paid.*)$') then 'Paid Video'
        when traffic_source.medium in ('display', 'banner', 'expandable', 'interstitial', 'cpm') then 'Display'
        when regexp_contains(traffic_source.source,'alibaba|amazon|google shopping|shopify|etsy|ebay|stripe|walmart')
            or regexp_contains(traffic_source.name, '^(.*(([^a-df-z]|^)shop|shopping).*)$') then 'Organic Shopping'
        when regexp_contains(traffic_source.source,'badoo|facebook|fb|instagram|linkedin|pinterest|tiktok|twitter|whatsapp')
            or traffic_source.medium in ('social','social-network','social-media','sm','social network','social media') then 'Organic Social'
        when regexp_contains(traffic_source.source,'dailymotion|disneyplus|netflix|youtube|vimeo|twitch|vimeo|youtube')
            or regexp_contains(traffic_source.medium,'^(.*video.*)$') then 'Organic Video'
        when regexp_contains(traffic_source.source,'baidu|bing|duckduckgo|ecosia|google|yahoo|yandex')
            or traffic_source.medium = 'organic' then 'Organic Search'
        when regexp_contains(traffic_source.source,'email|e-mail|e_mail|e mail')
            or regexp_contains(traffic_source.medium,'email|e-mail|e_mail|e mail') then 'Email'
        when traffic_source.medium = 'affiliate' then 'Affiliates'
        when traffic_source.medium = 'referral' then 'Referral'
        when traffic_source.medium = 'audio' then 'Audio'
        when traffic_source.medium = 'sms' then 'SMS'
        when traffic_source.medium like '%push'
            or regexp_contains(traffic_source.medium,'mobile|notification') then 'Mobile Push Notifications'
        else 'Unassigned' end as channel_grouping_user,
    count(distinct user_pseudo_id) as users
from
    -- change this to your google analytics 4 export location in bigquery
    `ga4bigquery.analytics_250794857.events_*`
where
    -- define static and/or dynamic start and end date
    _table_suffix between '20220901'
    and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))
group by
    channel_grouping_user
order by
    users desc

• default channel grouping (based on session)

with prep as (
select
    user_pseudo_id,
    (select value.int_value from unnest(event_params) where key = 'ga_session_id') as session_id,
    array_agg((select value.string_value from unnest(event_params) where key = 'source') ignore nulls order by event_timestamp)[safe_offset(0)] as source,
    array_agg((select value.string_value from unnest(event_params) where key = 'medium') ignore nulls order by event_timestamp)[safe_offset(0)] as medium,
    array_agg((select value.string_value from unnest(event_params) where key = 'campaign') ignore nulls order by event_timestamp)[safe_offset(0)] as campaign
from
    -- change this to your google analytics 4 export location in bigquery
    `ga4bigquery.analytics_250794857.events_*`
where
    -- define static and/or dynamic start and end date
    _table_suffix between '20220901'
    and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))
group by
    user_pseudo_id,
    session_id)

select
    -- session default channel grouping (dimension | the channel group associated with a session) 
    case 
        when source = '(direct)' and (medium in ('(not set)','(none)')) then 'Direct'
        when regexp_contains(campaign, 'cross-network') then 'Cross-network'
        when (regexp_contains(source,'alibaba|amazon|google shopping|shopify|etsy|ebay|stripe|walmart')
            or regexp_contains(campaign, '^(.*(([^a-df-z]|^)shop|shopping).*)$'))
            and regexp_contains(medium, '^(.*cp.*|ppc|paid.*)$') then 'Paid Shopping'
        when regexp_contains(source,'baidu|bing|duckduckgo|ecosia|google|yahoo|yandex')
            and regexp_contains(medium,'^(.*cp.*|ppc|paid.*)$') then 'Paid Search'
        when regexp_contains(source,'badoo|facebook|fb|instagram|linkedin|pinterest|tiktok|twitter|whatsapp')
            and regexp_contains(medium,'^(.*cp.*|ppc|paid.*)$') then 'Paid Social'
        when regexp_contains(source,'dailymotion|disneyplus|netflix|youtube|vimeo|twitch|vimeo|youtube')
            and regexp_contains(medium,'^(.*cp.*|ppc|paid.*)$') then 'Paid Video'
        when medium in ('display', 'banner', 'expandable', 'interstitial', 'cpm') then 'Display'
        when regexp_contains(source,'alibaba|amazon|google shopping|shopify|etsy|ebay|stripe|walmart')
            or regexp_contains(campaign, '^(.*(([^a-df-z]|^)shop|shopping).*)$') then 'Organic Shopping'
        when regexp_contains(source,'badoo|facebook|fb|instagram|linkedin|pinterest|tiktok|twitter|whatsapp')
            or medium in ('social','social-network','social-media','sm','social network','social media') then 'Organic Social'
        when regexp_contains(source,'dailymotion|disneyplus|netflix|youtube|vimeo|twitch|vimeo|youtube')
            or regexp_contains(medium,'^(.*video.*)$') then 'Organic Video'
        when regexp_contains(source,'baidu|bing|duckduckgo|ecosia|google|yahoo|yandex')
            or medium = 'organic' then 'Organic Search'
        when regexp_contains(source,'email|e-mail|e_mail|e mail')
            or regexp_contains(medium,'email|e-mail|e_mail|e mail') then 'Email'
        when medium = 'affiliate' then 'Affiliates'
        when medium = 'referral' then 'Referral'
        when medium = 'audio' then 'Audio'
        when medium = 'sms' then 'SMS'
        when medium like '%push'
            or regexp_contains(medium,'mobile|notification') then 'Mobile Push Notifications'
        else 'Unassigned' end as channel_grouping_session,
    count(distinct concat(user_pseudo_id,session_id)) as sessions
from
    prep
group by
    channel_grouping_session
order by
    sessions desc

• page referrer (based on user)

with prep as (
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 = 'page_referrer')) as page_referrer
from
    -- change this to your google analytics 4 export location in bigquery
    `ga4bigquery.analytics_250794857.events_*`
where
    -- define static and/or dynamic start and end date
    _table_suffix between '20220901'
    and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))
group by
    user_pseudo_id,
    session_id),

rank as (
select
    user_pseudo_id,
    session_id,
    page_referrer,
    rank() over (partition by user_pseudo_id order by session_id) as rank
from
    prep
qualify
    rank = 1)

select
    -- user page referrer (dimension | the full referring url of a user's first session (within selected date range), including the hostname and path)
    coalesce(page_referrer,'(not set)') as page_referrer_user,
    count(distinct user_pseudo_id) as users
from
    rank
group by
    page_referrer_user
order by
    users desc

• page referrer (based on session)

with prep as (
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 = 'page_referrer')) as page_referrer
from
    -- change this to your google analytics 4 export location in bigquery
    `ga4bigquery.analytics_250794857.events_*`
where
    -- define static and/or dynamic start and end date
    _table_suffix between '20220901'
    and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))
group by
    user_pseudo_id,
    session_id)

select
    -- session page referrer (dimension | the full referring url of a session, including the hostname and path)
    coalesce(page_referrer,'(not set)') as page_referrer_session,
    count(distinct concat(user_pseudo_id,session_id)) as sessions
from
    prep
group by
    page_referrer_session
order by
    sessions desc