spyc.ai

Free tool

BigQuery GA4 Query Library

Twelve ready SQL queries for the GA4 BigQuery export, filled in with your dataset and date range.

How it works

What is the BigQuery GA4 Query Library?

The BigQuery GA4 Query Library is a free tool with twelve ready SQL queries for the Google Analytics 4 BigQuery export. Enter your project and dataset and a date range, and each query is filled in for you: users and sessions, top pages, event counts, revenue, traffic source, unnesting event_params, a two-step funnel and first-visit cohorts.

How to use the BigQuery GA4 Query Library

  1. Enter your project and dataset in the form project-id.analytics_123456789.
  2. Pick the start and end dates.
  3. For the funnel query, type the two event names.
  4. Search the library or scroll to the query you need.
  5. Copy the SQL and run it in the BigQuery console.

What is the GA4 BigQuery export?

GA4 can copy its raw event data into Google BigQuery. You link the property under Admin, Product links, BigQuery links, and GA4 creates a dataset named analytics_ followed by your property ID. Each day gets its own table, events_YYYYMMDD, with one row per event. If streaming export is on, there are also events_intraday_YYYYMMDD tables for the current day.

The export is not retroactive: it starts on the day you link. Standard properties have a daily export limit of one million events.

How do I query GA4 data in BigQuery?

Query all daily tables at once with a wildcard, events_*, and limit the days with the _TABLE_SUFFIX pseudo-column: WHERE _TABLE_SUFFIX BETWEEN '20261001' AND '20261031'. Every query in this library does that, using the dates you pick. Because the suffix of an intraday table starts with letters, that filter also leaves the intraday tables out.

The date filter matters for cost too. On-demand pricing is based on the bytes a query reads, and the suffix filter stops BigQuery from scanning tables outside the range.

How do I unnest event_params in BigQuery?

event_params is a repeated field: each event holds a list of key and value pairs, and the value is a record with string_value, int_value, float_value and double_value, only one of which is filled. To read one parameter as a column, use a subquery: (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location').

The library has two queries for this. One lists every parameter key per event and which value field it uses, so you know which to read. The other flattens common parameters into one row per event.

How do I count sessions in the GA4 BigQuery export?

The export has no session table. A session is identified by two things together: user_pseudo_id and the ga_session_id event parameter, which is an integer. Count distinct combinations of the two. Counting session_start events is a common shortcut, but it miscounts when that event is missing.

For engaged sessions, read the session_engaged parameter. It arrives as a string on most events and as an integer on some, so the query here accepts both.

Why do BigQuery numbers not match the GA4 interface?

Several documented reasons. GA4 reports can use approximate counts for large numbers, apply data thresholds, and include modeled data and Google signals, none of which are in the export. Session source in the interface uses attribution that carries a source forward from earlier sessions, which a simple query does not reproduce. event_date follows the property's time zone while event_timestamp is in UTC.

Treat small differences as expected. These queries are written against the documented schema and have not been run on your data, so compare the first results with a GA4 report. For a faster look at a report you already exported, try the GA4 Export Analyzer, and for dashboards on top of the data see the Looker Studio Templates Index.

Questions

Do I need to pay for BigQuery to use these queries?
Not necessarily. BigQuery has a sandbox and a free monthly usage tier. Larger properties will pay for storage and for the data each query reads, so check Google's current pricing.
Which SQL dialect are the queries written in?
BigQuery Standard SQL (GoogleSQL), which is the default in the BigQuery console.
How does the funnel query define a conversion?
A user counts for step two if they fired the second event at any time after their first occurrence of the first event, inside the date range. It is user-scoped and not limited to one session.
Can I change which parameters the queries read?
Yes. Copy the subquery pattern and change the key, and use int_value instead of string_value for numeric parameters.
Is the BigQuery GA4 Query Library free?
Yes, with a free spyc.ai account. It runs entirely in your browser, so nothing you type or paste is sent to a server.

Analytics Tools

More in Analytics Tools

GA4 Event Generator

Pick a GA4 recommended or custom event, add parameters, and copy the gtag snippet and dataLayer push.

Generate event

GA4 Measurement Protocol Builder

Build a GA4 Measurement Protocol request: JSON body, collect and debug URLs, and a curl command.

Build request

GTM dataLayer Generator

Write dataLayer.push code for GA4 ecommerce and custom events, with the GTM variables to create.

Generate dataLayer

Ready when you are

Before your next buyer asks AI about you, ask AI about yourself

One check, four engines, about twenty seconds. We show you the answers before we ask for anything.