Traffic source: dimensions & metrics (GA4)
4 min read

Traffic source: dimensions & metrics (GA4)

The most important traffic source dimensions and metrics for GA4 BigQuery export, like source, medium, campaign name, default channel grouping and full referrer.
Traffic source: dimensions & metrics (GA4)

A lot of Google Analytics 4 data 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 & metrics you are used to in your Google Analytics analysing and reporting are a bit harder to access. You’ll have to calculate them, using your own queries.

As I’ve tried to sort out the most used dimensions and metrics already, I hope I can be of help with the following example queries. There are plenty other, probably even better ways to achieve these results, but here is my two cents.

If you only need one dimension or metric, look at the -- comments in the example query for names and definitions and copy the part you need from the SELECT clause. Make sure that you also add any additional conditions (i.e. with, from, where, group by, having and order by) that are necessary to calculate the results correctly.

Default traffic source dimensions

  • traffic_source.name (user)
  • traffic_source.medium (user)
  • traffic_source.source (user)
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 '20201101' and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))

Calculated traffic source dimensions

  • 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
    '20201101' 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
        `ga4bigquery.analytics_250794857.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

Enjoying this post? Subscribe to the GA4BigQuery newsletter!

You will receive regular updates (max 1x/week) straight in your inbox. You can unsubscribe anytime.