How to Use SQL with GA4 in BigQuery: 10 Real Examples for Marketers

Posted on Posted in BigQuery Tagged with
How to Use SQL with GA4 in BigQuery: 10 Real Examples for Marketers

If you’re working with GA4 data in BigQuery — welcome. It can feel overwhelming at first, but once you get a few core queries in place, everything starts making sense.

I’ve put together 10 simple, reusable queries that I use all the time when building reports and dashboards. Each one solves a specific task — cleanly and clearly.

Let’s go.

1. Count Events

How many times each event was triggered?

SELECT event_name, COUNT(*) AS event_count  
FROM `your_project.dataset.events_*`  
WHERE _TABLE_SUFFIX BETWEEN '20240601' AND '20240630'  
GROUP BY event_name  
ORDER BY event_count DESC

Read more: Full article • Watch video

2. Extract Event Parameters

How to get values like item_name or page_location from nested fields.

SELECT  
  event_name,  
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'item_name') AS item_name  
FROM `your_project.dataset.events_*`  
WHERE event_name = 'view_item'

Read more: Full article • Watch video

3. Top Pages by Event

Which pages triggered a specific event the most?

SELECT  
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page,  
  COUNT(*) AS count  
FROM `your_project.dataset.events_*`  
WHERE event_name = 'scroll'  
GROUP BY page  
ORDER BY count DESC

Read more: Full article • Watch video

4. Count Conversions

Track how many times a key event happened — e.g. purchase.

SELECT COUNT(*) AS conversions  
FROM `your_project.dataset.events_*`  
WHERE event_name = 'purchase'

Read more: Full article • Watch video

5. Conversions with Parameters

Need to pull values from conversions (like revenue)?

SELECT  
  COUNT(*) AS purchases,  
  SUM(CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'value') AS FLOAT64)) AS total_value  
FROM `your_project.dataset.events_*`  
WHERE event_name = 'purchase'

Read more: Full article • Watch video

6. Users by Device

Filter users based on device category.

SELECT  
  user_pseudo_id,  
  device.category AS device_type  
FROM `your_project.dataset.events_*`  
WHERE device.category = 'mobile'  
GROUP BY user_pseudo_id, device_type

Read more: Full article • Watch video

7. New vs Returning Users

Separate new and returning users in BigQuery.

SELECT  
  user_pseudo_id,  
  user_first_touch_timestamp,  
  MIN(event_timestamp) AS first_event  
FROM `your_project.dataset.events_*`  
GROUP BY user_pseudo_id, user_first_touch_timestamp

Read more: Full article • Watch video

8. Users by Channel

Group users by source/medium.

SELECT  
  user_pseudo_id,  
  traffic_source.source,  
  traffic_source.medium  
FROM `your_project.dataset.events_*`  
WHERE event_name = 'session_start'

Read more: Full article • Watch video

9. Scheduled Queries

Not SQL — but automation matters.
Once your queries are ready, schedule them to run daily inside BigQuery UI.
Takes 2 minutes, saves hours of manual updates.

Read more: Full article • Watch video

10. Sessions + Users by Source

Break down your top traffic sources using GA4 export.

SELECT  
  traffic_source.source,  
  COUNT(DISTINCT user_pseudo_id) AS users,  
  COUNT(DISTINCT CONCAT(user_pseudo_id, CAST(event_bundle_sequence_id AS STRING))) AS sessions  
FROM `your_project.dataset.events_*`  
WHERE event_name = 'session_start'  
GROUP BY source  
ORDER BY users DESC

Read more: Full article • Watch video

Outro: You don’t need to know everything about GA4 export to get value from it. Just a few clean queries — and suddenly your reporting is faster, more flexible, and tailored to your business.

Feel free to copy, test, and adapt these for your own setup.

Want to go deeper?
✅ Visit my full dashboard store: https://gaillereports.com/looker-studio-dashboards-template-store/
✅ Get monthly tips & bonus content — no spam, just once a month: https://gaillereports.com/bonus-for-subscription/

If you found this helpful, feel free to share it with someone who’s navigating GA4 + BigQuery right now.