How to Query GA4 Data in BigQuery

July 26, 2026 · By the SaturnSQL team

The GA4 interface answers the questions Google decided to build a report for. The BigQuery export answers the rest. It gives you every raw event as a row of SQL-queryable data, unsampled, and for most sites it costs nothing beyond what you would already pay for BigQuery. This is the guide to turning that export into answers: how to enable it, how the schema actually works, and four complete queries you can run today.

Why the export beats the GA4 UI and API

The GA4 UI applies sampling to large date ranges and only ever shows you the dimensions and metrics its reports were built for. The Data API removes some of that sampling but is still a fixed set of report shapes. The BigQuery export skips both limits: every event row, every parameter, unsampled, joinable against anything else in your warehouse. The catch is that you write SQL instead of clicking through a report builder, which is exactly the tradeoff this guide is for.

The export is free for standard GA4 properties, with one limit worth knowing: the daily export caps out at 1 million events per day. Below that, there is no charge from GA4, only BigQuery's own storage and query pricing, which for a small site is close to nothing.

Enabling the export

In GA4, go to Admin → Product Links → BigQuery Links and link the property to a BigQuery project. You will be asked to choose daily, streaming, or both. Daily export writes one table per day, named events_YYYYMMDD, and is the free option almost every site should start with. Streaming export additionally writes events within minutes into a rolling events_intraday_YYYYMMDD table, which is useful if you need near-real-time numbers, but it is a separate paid feature billed by event volume.

After linking, expect the first daily table to take up to about 24 hours to appear. From then on, each day's table typically lands a day behind, backfilled once that day is complete.

The schema, explained

Every table is one row per event: a page view, a click, a purchase, whatever you or GA4 chose to log. There is no separate sessions or users table; sessions and users are both derived from the event stream itself.

Most of what makes an event interesting lives in event_params, a repeated STRUCT: an array of key/value pairs, where the value is itself a struct with string_value, int_value, float_value, and double_value columns (only one is ever populated). Because it is repeated, you cannot just write event_params.page_location; you have to UNNEST it and pick out the key you want. This is the one pattern you will reuse in almost every query against this export:

(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location')

For dates, you have two tools that look similar but do different jobs. event_date is an ordinary STRING column on every row, formatted YYYYMMDD, and it is what you GROUP BY or display. _TABLE_SUFFIX is a pseudo-column that only exists when you query the events_* wildcard, and it is what determines which daily tables BigQuery actually opens. Filtering on event_date alone does not limit the scan, since it is just a normal column BigQuery has to read from every shard the wildcard matches; filtering on _TABLE_SUFFIX is what keeps the query cheap. The examples below always filter on _TABLE_SUFFIX to bound the scan; the daily active users query also selects event_date because the output needs to show the date.

Four queries you can run today

Replace project.analytics_XXXXX with your own export's project and dataset, found on the same BigQuery Links page where you enabled it.

SELECT
  event_date,
  COUNT(DISTINCT user_pseudo_id) AS active_users
FROM `project.analytics_XXXXX.events_*`
WHERE _TABLE_SUFFIX BETWEEN
    FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY))
    AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
GROUP BY event_date
ORDER BY event_date;

SELECT
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
  COUNT(*) AS views
FROM `project.analytics_XXXXX.events_*`
WHERE _TABLE_SUFFIX BETWEEN
    FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))
    AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
  AND event_name = 'page_view'
GROUP BY page_location
ORDER BY views DESC
LIMIT 20;

SELECT
  collected_traffic_source.manual_source AS source,
  collected_traffic_source.manual_medium AS medium,
  COUNT(*) AS sign_ups
FROM `project.analytics_XXXXX.events_*`
WHERE _TABLE_SUFFIX BETWEEN
    FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY))
    AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
  AND event_name = 'sign_up'
GROUP BY source, medium
ORDER BY sign_ups DESC;

WITH cohort AS (
  SELECT DISTINCT user_pseudo_id
  FROM `project.analytics_XXXXX.events_*`
  WHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))
    AND event_name = 'first_visit'
),
returned AS (
  SELECT DISTINCT user_pseudo_id
  FROM `project.analytics_XXXXX.events_*`
  WHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', CURRENT_DATE())
)
SELECT
  (SELECT COUNT(*) FROM cohort) AS cohort_size,
  COUNT(*) AS returned_day_7,
  ROUND(SAFE_DIVIDE(COUNT(*), (SELECT COUNT(*) FROM cohort)) * 100, 1) AS retention_pct
FROM cohort
JOIN returned USING (user_pseudo_id);

Controlling cost on the wildcard table

Three habits keep the export cheap. First, always filter _TABLE_SUFFIX: querying events_* with no date bound opens every daily table you have ever exported. Second, never SELECT * from events_*: the repeated event_params, items, and user_properties columns are the largest in the table by far, and BigQuery bills for every column your query touches, whether or not you use its values. Select only the fields you need.

Third, if the BigQuery console or your client lets you cap a query's maximum bytes billed as a safety net, a low ceiling like 10 GB goes a long way for a small site. A single day's events_* shard for a modest site is typically a few megabytes to a few hundred megabytes across the columns a normal query touches, so a well-pruned query covering weeks or months of history stays far under that ceiling, while an unbounded SELECT * across a year of tables is exactly the kind of accident such a cap is meant to catch. On-demand BigQuery pricing is currently $6.25 per TB scanned, so the difference between a pruned query and an unbounded one is usually the difference between fractions of a cent and real money.

Frequently asked questions