As a Google Analytics user you are probably quite attached to the default reports in the user interface of Google Analytics 4. It can be hard to make sense of the data in the BigQuery export tables.
Let me enable you to replicate the report you're familiar with. I'll try to keep it as basic as possible here.
User acquisition report
In the Life cycle | Acquisition | User acquisition report you'll find a table with data about acquisition at user level (source, medium, campaign) and some engagement metrics. We will try to replicate this table with the query below.
Good to know
The main query contains all primary dimensions that are available in the BigQuery export. By default only the first one is selected, the other dimensions are 'hidden' by a single line comment. Remove the -- part to select these dimensions. Don't forget to adjust the group by clause by removing the corresponding -- as well.
It is expected that the results will not match (exactly) with your reports in the Google Analytics 4 user interface. Don't worry about it, it can occur for a variety of reasons.
-- subquery to prepare the data with prep as ( select user_pseudo_id, ifnull(traffic_source.medium,'(not set)') as medium, ifnull(traffic_source.source,'(not set)') as source, ifnull(traffic_source.name,'(not set)') as name, (select value.int_value from unnest(event_params) where key = 'ga_session_id') as session_id, max((select value.int_value from unnest(event_params) where event_name = 'session_start' and key = 'ga_session_number')) as session_number, 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, -- change event_name to the event(s) you want to count countif(event_name = 'click') as event_count, -- change event_name to the conversion event(s) you want to count countif(event_name = 'purchase') as conversions, sum(ecommerce.purchase_revenue) as total_revenue from -- change this to your google analytics 4 export location in bigquery `ga4bigquery.analytics_250794857.events_*` where -- change the date range by using static and/or dynamic dates _table_suffix between '20210101' and format_date('%Y%m%d',date_sub(current_date(), interval 1 day)) group by user_pseudo_id, medium, source, name, session_id) -- main query select medium as user_medium, -- source as user_source, -- concat(source,' / ',medium) as user_source_medium, -- name as user_campaign, count(distinct case when session_number = 1 then user_pseudo_id else null end) as new_users, count(distinct case when session_engaged = '1' then concat(user_pseudo_id,session_id) end) as engaged_sessions, safe_divide(count(distinct case when session_engaged = '1' then concat(user_pseudo_id,session_id) end),count(distinct concat(user_pseudo_id,session_id))) as engagement_rate, safe_divide(count(distinct case when session_engaged = '1' then concat(user_pseudo_id,session_id) end),count(distinct user_pseudo_id)) as engaged_sessions_per_user, safe_divide(sum(engagement_time_msec/1000),count(distinct case when session_engaged = '1' then concat(user_pseudo_id,session_id) end)) as average_engagement_time_seconds, sum(event_count) as event_count, sum(conversions) as conversions, ifnull(sum(total_revenue),0) as total_revenue from prep group by user_medium --,user_source --,user_source_medium --,user_campaign order by new_users desc
Enjoying this post? Subscribe to the GA4BigQuery newsletter!
You will receive regular updates (max 1x/week) straight in your inbox. You can unsubscribe anytime.