BigQuery SQL Examples For GA4

In this article we’re picking up with the second phase of using GA4 with BigQuery. You’ve already linked GA4 to BigQuery and now you want to know if you can actually access the data in the underlying data table. So let’s see some BigQuery SQL examples that you can actually try.

This article won’t teach you all you need to know about SQL (far from it), but will give you some practical examples that you can test out if you’re looking to learn SQL to query your GA4 data in BigQuery.

Here are those examples to test out. Note: these are all provided by Sam Brand (see his website below) who was the guest instructor in this video demonstration of the same SQL examples.

This article is going to make more sense if you check it out in conjunction with the video above. You can also now use Google Gemini to help you write SQL queries.

See All GA4 Events Stored in BigQuery

See below:

SELECT *
FROM `[your database].[your dataset].events_*`
ORDER BY event_date DESC
LIMIT 1000;

Sanity Check Your Data

See below:

SELECT
 EXTRACT(DATE from TIMESTAMP_MICROS(event_timestamp)) as date,
 COUNT(DISTINCT user_pseudo_id) users,
 COUNT(DISTINCT (concat(user_pseudo_id, ' - ', (select value.int_value from unnest(event_params) where key = 'ga_session_id')))) sessions,
 COUNT(*) as pvs
FROM `[your database].[your dataset].events_*`
WHERE event_name = 'page_view' AND event_date > '20240101'
GROUP BY 1
ORDER BY 1 DESC;

See All Events Since January 2024

SQL query:

SELECT event_date, event_timestamp, timestamp_micros(event_timestamp) time_utc, event_name
FROM `[your database].[your dataset].events_*`
WHERE event_date > '20240101'
ORDER BY event_date DESC
LIMIT 1000;

See Session Flows

See the BigQuery SQL example below:

SELECT
 timestamp_micros(event_timestamp) time_utc, user_pseudo_id,
 (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')) unique_session,
 event_name,
 (select value.string_value from unnest(event_params) where key = 'page_location') AS url
 -- event_params
FROM `[your database].[your dataset].events_*`
WHERE event_date > '20240101' AND (select value.int_value from unnest(event_params) where key = 'ga_session_id') IS NOT null
ORDER BY unique_session, time_utc ASC
LIMIT 1000;

Page Depth Breakdown Across Pages

Note: this would require you have custom scroll depth tracking set up with events named scroll_to_10, scroll_to_50, scroll_to_90.

WITH CTE as (
SELECT
 timestamp_micros(event_timestamp) time_utc, user_pseudo_id,
 (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')) user_session,
 event_name,
 (select value.string_value from unnest(event_params) where key = 'page_location') AS url
 -- event_params
FROM `[your database].[your dataset].events_*`
WHERE event_date > '20240101' AND (select value.int_value from unnest(event_params) where key = 'ga_session_id') IS NOT null
ORDER BY user_session, time_utc ASC
)


SELECT
url,
count(distinct user_session) sessions,
count(distinct if(event_name = 'scroll_to_10', user_session, null)) AS depth_10,
count(distinct if(event_name = 'scroll_to_50', user_session, null)) AS depth_50,
count(distinct if(event_name = 'scroll_to_90', user_session, null)) AS depth_90,
round(count(distinct if(event_name = 'scroll_to_50', user_session, null)) / count(distinct user_session), 2) AS pct_depth_50
FROM CTE
GROUP BY 1
ORDER BY 2 DESC;

For More

These BigQuery SQL examples were provided by Sam Brand. You can find him at his website if you’d like to get in touch.