If you analyze website traffic in Google Analytics 4, sooner or later you'll run into the limitations, and often the plain shortcomings, of its interface. It turns out the only reliable route to trustworthy, meaningful data goes through Google BigQuery.
Adam Brzostek
Reading time: 4:00
GA4 traffic sources in Google BigQuery
As if that weren't enough, and hard as it is to believe, until recently Google made the job even harder in BQ itself.
Even though I find the structure of the GA4 export to BQ much simpler, reporting traffic sources correctly took ninja skills and was sometimes downright impossible. Strangest of all, the problem mostly affected google / cpc campaigns. That source barely shows up in BQ at all, which has nothing to do with reality.
The problem
One widely described way to get traffic sources is to use the event parameters attached to the session_start event:
select
concat(user_pseudo_id, (select value.int_value from unnest(event_params) where key = 'ga_session_id')) as session_id,
user_pseudo_id,
max((select value.string_value from unnest(event_params) where key = 'campaign')) as campaign,
max((select value.string_value from unnest(event_params) where key = 'source')) as source,
max((select value.string_value from unnest(event_params) where key = 'medium')) as medium,
from `bettersteps.analytics_269424883.events_*`
group by 1,2In theory, this query should return the source, medium and campaign for a given session ID, which is a concatenation of user_pseudo_id and ga_session_id.
The query does return this data, but a quick comparison with the GA4 interface shows right away that something is off.
You can see immediately that google / cpc is missing from BQ entirely, and the proportions of the other sources are so skewed that the data becomes completely unreliable.
I find it hard to understand how Google failed to solve something as fundamental as traffic source reporting, especially since it was clear from the moment GA4 was designed that advanced reporting wouldn't be possible without BQ.
The very idea of a session that can have several sources raises serious doubts, and on top of that you could say Google was trying to make us blindly trust whatever the interface shows.
Finally, some good news
The problem described above was widely known, and almost three years after launch, Google's engineers finally listened to the people. With the GA4 update of July 19, 2024, new fields with session-level traffic source information were added to the BQ export.
This update partly solves the problem, but writing the query still takes some gymnastics.
For the google source, the cpc medium isn't given directly in the medium field. To determine which sessions come from google / cpc, you have to use the session_traffic_source_last_click.google_ads_campaign.campaign_name field and add a condition: if it has a value, the medium is set to cpc.
The query should look like this:
SELECT DISTINCT
user_pseudo_id as ga_client_id,
cast(event_date as date format 'yyyymmdd') as partition_date,
event_name,
event_timestamp,
(select value.int_value from unnest(event_params) where key = 'ga_session_id') as session_id,
Concat(user_pseudo_id,(select value.int_value from unnest(event_params) where key = 'ga_session_id') ) as ga_session_id,
(case when session_traffic_source_last_click.google_ads_campaign.campaign_name is not null then "google" else
session_traffic_source_last_click.manual_campaign.`source` end) as source,
(case when session_traffic_source_last_click.google_ads_campaign.campaign_name is not null then "cpc" else session_traffic_source_last_click.manual_campaign.medium
end) as medium,
session_traffic_source_last_click.manual_campaign.campaign_name,
session_traffic_source_last_click.google_ads_campaign.campaign_name as campaign_ads,
FROM `project.property.events_*`Is the GA4 sessions metric reliable at all?
When I compared data from the GA interface with BQ data generated using the method above, I noticed another discrepancy.
I knew the session counts wouldn't match because of reporting identity settings, modeled data and GA4's other mysterious ways of counting sessions, but I expected the traffic source proportions to stay the same.
Since they differed slightly, I started looking into it in more detail.
It turned out that in BQ a single session has more than one source assigned.
That wouldn't be unusual in itself, but it turned out the same session is counted twice in the GA4 interface
This happens when, for reasons I don't yet know, the user_engagement event fires before the session_start event.
This means we should remove the (not set) source from our data.
Here's an example query that does it.
with
raw_bq as (
SELECT DISTINCT
user_pseudo_id as ga_client_id,
cast(event_date as date format 'yyyymmdd') as partition_date,
select value.int_value from unnest(event_params) where key = 'ga_session_id') as session_id,
Concat(user_pseudo_id,(select value.int_value from unnest(event_params) where key = 'ga_session_id') ) as ga_session_id,
(case when session_traffic_source_last_click.google_ads_campaign.campaign_name is not null then "google" else
session_traffic_source_last_click.manual_campaign.`source` end) as source,
(case when session_traffic_source_last_click.google_ads_campaign.campaign_name is not null then "cpc" else session_traffic_source_last_click.manual_campaign.medium
end) as medium,
session_traffic_source_last_click.manual_campaign.campaign_name,
session_traffic_source_last_click.google_ads_campaign.campaign_name as campaign_ads,
FROM `project.property.events_*`
order by 4
),
raw_bq_mark_duplicates as
(
SELECT DISTINCT
*,
(case when source != "(not set)" then 1 else 2 end) as score
from raw_bq
),
raw_bq_ranking as
(
SELECT
*,
row_number() over (partition by ga_session_id order by score) as ranking
from raw_bq_mark_duplicates
order by 4,10
)
SELECT
CONCAT(source, " / ", medium) as source_medium,
campaign_name as campaign,
campaign_ads,
count(distinct ga_session_id) as sessions
from raw_bq_ranking
where ranking = 1
group by 1,2,3
order by 4 desc
Once we excluded (not set) from both datasets, the session counts matched more closely.
The split by source still isn't identical, but for analysis purposes we can finally treat the traffic source data from BQ as reasonably reliable.
It does, however, call into question whether the sessions metric in the GA4 interface can be taken seriously at all. I think you should treat it with a lot of caution.
If you'd like your company to move to BigQuery-based reporting, where you can combine data from different systems, get in touch. We've been doing this for years and we know how to do it.