
Recreating GA4 Traffic Acquisition Report in BigQuery
Google Analytics 4 allows exporting all the raw data and events to BigQuery automatically, and use SQL syntax to query and model the data. When you export GA4 data to BigQuery, you own that data, and you can analyze users and their traffic on your property.
To make sense of the data and create reports, you have to write your own SQL queries. Some reports are straightforward to build and require simple structure, however, there are many metrics that require custom calculations, nested queries, and joining multiple datasets.
Today, I replicate the traffic acquisition report in GA4 to analyze the source/medium of users who visited my blog. Here’s the schema of my query:
Dimension:
- Source / Medium
Metrics
- Users
- Sessions
- Engaged Sessions
- Bounces
- Bounce Rate

Here’s the SQL query, make sure to replace with your own table.
WITH
source_medium AS (
SELECT
(
SELECT
value.int_value
FROM
UNNEST(event_params)
WHERE
event_name = 'page_view'
AND key = 'ga_session_id') AS session_id,
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,
--Replace with your own table
FROM `civic-bruin-2222.analytics_258822732.events_*`
GROUP BY
1,
2,
3 ),
engaged_sessions 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 = 'session_engaged')) AS session_engaged,
FROM
`civic-bruin-237115.analytics_258822732.events_*`
GROUP BY
1,
2 )
SELECT
CONCAT(source,' / ',medium) AS source_medium,
COUNT(DISTINCT a.session_id) AS sessions,
COUNT(DISTINCT user_pseudo_id) users,
COUNT(DISTINCT
CASE
WHEN session_engaged = '1' THEN CONCAT(user_pseudo_id,b.session_id)
END
) AS engaged_sessions,
COUNT(DISTINCT
CASE
WHEN session_engaged = '0' THEN CONCAT(user_pseudo_id,b.session_id)
END
) AS bounces,
COUNT(DISTINCT
CASE
WHEN session_engaged = '0' THEN CONCAT(user_pseudo_id,b.session_id)
END
) / COUNT(DISTINCT a.session_id) AS bounce_rate
FROM
source_medium a
LEFT JOIN
engaged_sessions b
ON
a.session_id = b.session_id
GROUP BY
1
ORDER BY
sessions DESC

Ex-Google with advanced digital analytics and marketing automation skills. If you are looking for a data-driven professional that will help you save time & money and grow cost-efficiently, you have come to the right person. Contact Me!