GA4 BigQuery Export: Setup, Schema, and 5 Essential SQL Queries.

GA4's BigQuery export is one of its most powerful features — and one of the least used. It gives you a complete, row-level copy of every event GA4 collects, without data sampling, without session limits, queryable in SQL. GA4's built-in interface caps reports at 100k rows, applies sampling above thresholds, and doesn't let you join data across tables. BigQuery removes all three constraints.

This guide covers how to enable the export, what data you get, how the schema works, and the 5 SQL queries every analyst needs on day one.

What GA4 Sends to BigQuery

Each day, GA4 exports an events_YYYYMMDD table to your BigQuery dataset. Each row is one event. There are no session tables, no user tables — everything is an event, and session and user dimensions are reconstructed via SQL.

Alongside the daily table, GA4 creates an events_intraday_YYYYMMDD table that is appended throughout the current day. It is not final — it can be replaced or updated as the day progresses, so avoid building reports on it directly. Once the day closes, the intraday table is superseded by the finalized daily table.

If you have enabled user data export in your GA4 property settings, GA4 also writes a pseudonymous_users_YYYYMMDD table containing user-level lifetime metrics — total revenue, first and last seen dates, and predicted metrics if you have Predictive Audiences enabled. This table is opt-in and disabled by default.

How to Enable the BigQuery Export

Enabling the export takes about ten minutes. You need Editor or Administrator access to both your GA4 property (and its data stream) and the Google Cloud project you're linking to.

Step 1: Create a Google Cloud project. If you don't have one, go to console.cloud.google.com, create a project, and enable the BigQuery API. You'll need a billing account attached to the project — BigQuery has a generous free tier, but billing must be enabled to create datasets.

Step 2: In GA4, open Admin → Property Settings → BigQuery Links and click "Link".

Step 3: Select your Google Cloud project. The project must be in the same Google account ecosystem your GA4 property is managed under. Click Next.

Step 4: Choose a data location. Options are US or EU. This determines where BigQuery stores your data — pick based on your users' geography and your GDPR requirements. EU residency is required if you're subject to strict data-residency obligations. This cannot be changed after the link is created.

Step 5: Configure export settings. You have two frequency options: Daily (GA4 exports once per day, typically between 12:00–4:00 AM in your property's reporting timezone) or Streaming (near real-time updates throughout the day, charged at approximately $0.05/GB — meaningful at scale, overkill for most sites). Enable "Include advertising identifiers" only if you need GCLID or DCLID values in your export; this adds a collected_traffic_source field with click IDs.

Step 6: Click Link. GA4 will begin exporting within 24 hours. The first export creates your BigQuery dataset automatically. One important caveat: historical data is not exported. The export starts from the day you enable it; there is no backfill mechanism for events before that date.

The GA4 BigQuery Schema

The schema takes some getting used to because GA4's event-parameter model doesn't map cleanly to a flat relational table. Here are the key field groups you'll work with daily.

Top-level event fields

These are flat columns on every row:

• event_date — a string in YYYYMMDD format (e.g. '20260601'), not a DATE type. This is what you filter on with _TABLE_SUFFIX.
• event_timestamp — microseconds since the Unix epoch. Divide by 1,000,000 to get Unix seconds. Use TIMESTAMP_MICROS(event_timestamp) to cast to a BigQuery TIMESTAMP.
• event_name — the name of the GA4 event, e.g. page_view, purchase, session_start.
• user_pseudo_id — the cookie-based anonymous user identifier. Stable per device/browser unless the user clears cookies. This is the closest BigQuery gets to a user ID unless you pass a user_id.
• user_id — if your GA4 implementation passes a first-party user ID (e.g. a logged-in user's hashed ID), it lands here. Not present if you haven't implemented it.

event_params

This is the most important field to understand. event_params is a REPEATED RECORD — an array of key-value structs. Each event parameter GA4 records for an event (page location, session ID, engagement time, transaction ID, value, etc.) appears as one element in this array.

The struct shape is:

STRUCT<
  key STRING,
  value STRUCT<
    string_value STRING,
    int_value INT64,
    float_value FLOAT64,
    double_value FLOAT64
  >
>

To read any single parameter, you unnest the array and filter by key. For example, to get page_location:

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

The correct value type depends on what GA4 stored. page_location is a string. ga_session_id is an int. value (purchase revenue) is a double. Check the GA4 documentation or run a quick exploratory query to confirm which type each parameter uses.

user_properties

User-scoped custom dimensions follow the same REPEATED RECORD pattern as event_params. To read a user property named membership_tier:

(SELECT value.string_value FROM UNNEST(user_properties) WHERE key = 'membership_tier')

device and geo

These are nested STRUCTs, not arrays, so you read them with dot notation — no unnesting required:

• device.category — desktop, mobile, or tablet
• device.operating_system — e.g. iOS, Android, Windows
• device.browser — e.g. Chrome, Safari
• geo.country — full country name, e.g. United States
• geo.city — e.g. Stockholm

5 Essential SQL Queries

Replace project.dataset with your actual Google Cloud project ID and BigQuery dataset name. Adjust the date range in _TABLE_SUFFIX to match the period you're analysing. The wildcard table events_* with a _TABLE_SUFFIX BETWEEN filter is the standard pattern — it tells BigQuery which partitions to scan so you don't accidentally query your entire history.

1. Daily active users (DAU)

Count distinct user_pseudo_id per day. This is the foundational metric for any product analytics work.

SELECT
  event_date,
  COUNT(DISTINCT user_pseudo_id) AS dau
FROM `project.dataset.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260601' AND '20260630'
GROUP BY event_date
ORDER BY event_date

2. Sessions

GA4 doesn't have a sessions table. A session is defined by the combination of user_pseudo_id and ga_session_id (an integer stored in event_params). Concatenate the two for a globally unique session identifier.

SELECT
  event_date,
  COUNT(DISTINCT CONCAT(
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')
  )) AS sessions
FROM `project.dataset.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260601' AND '20260630'
GROUP BY event_date
ORDER BY event_date

3. Top 10 pages by pageviews

Filter to page_view events and extract page_location from event_params. Note: page_location is the full URL including query strings — you may want to use REGEXP_EXTRACT to strip query parameters if you want clean path-level aggregation.

SELECT
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page,
  COUNT(*) AS pageviews
FROM `project.dataset.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260601' AND '20260630'
  AND event_name = 'page_view'
GROUP BY page
ORDER BY pageviews DESC
LIMIT 10

4. Purchase revenue by source

traffic_source.source and traffic_source.medium are top-level STRUCT fields (no unnesting). Revenue is stored in the value parameter of the purchase event as a double.

SELECT
  traffic_source.source,
  traffic_source.medium,
  COUNT(DISTINCT (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'transaction_id')) AS purchases,
  SUM((SELECT value.double_value FROM UNNEST(event_params) WHERE key = 'value')) AS revenue
FROM `project.dataset.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260601' AND '20260630'
  AND event_name = 'purchase'
GROUP BY 1, 2
ORDER BY revenue DESC

5. Funnel conversion: view_item to purchase

A simple top-of-funnel conversion rate. Both events are queried in a single pass using conditional aggregation — no join required.

SELECT
  COUNT(DISTINCT CASE WHEN event_name = 'view_item' THEN user_pseudo_id END) AS viewers,
  COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_pseudo_id END) AS buyers,
  ROUND(
    100 * COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_pseudo_id END) /
    NULLIF(COUNT(DISTINCT CASE WHEN event_name = 'view_item' THEN user_pseudo_id END), 0),
    2
  ) AS conversion_pct
FROM `project.dataset.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260601' AND '20260630'
  AND event_name IN ('view_item', 'purchase')

Costs and Limits

BigQuery pricing has two components: storage and query processing. Storage is cheap — $0.02 per GB per month for active storage. A mid-size site exporting 1–5 GB of GA4 events per month will spend cents on storage. Even at 50 GB/month it's around $1.

Query processing is where costs can grow. BigQuery charges $5 per TB of data scanned (on the on-demand pricing model). The free tier covers your first 1 TB of queries per month. For most small-to-medium businesses, the entire month's analytics workload fits comfortably within that free tier — a query scanning 30 days of GA4 data for a site with 100k monthly users will typically scan 2–5 GB, well under 1 TB.

Two habits that keep costs low:

• Always filter with _TABLE_SUFFIX BETWEEN. The wildcard table events_* without a suffix filter scans your entire history. On a three-year-old property that could be hundreds of GBs.
• Select only the columns you need. BigQuery is columnar — SELECT * scans everything. Selecting event_name, user_pseudo_id, event_params instead cuts the scan to a fraction of the full row size.

One cost gotcha specific to GA4: the events_intraday_YYYYMMDD table is updated throughout the day. If you query it repeatedly via a dashboard refresh or an automated script, each query does a full re-scan of the table. For intraday monitoring at any significant data volume, use the streaming export and a partitioned table rather than querying the intraday table on a tight refresh cycle.

FAQ

How do I enable GA4 BigQuery export?

Go to GA4 Admin → Property Settings → BigQuery Links and click "Link". You'll need a Google Cloud project with the BigQuery API enabled and a billing account attached. The export starts within 24 hours of linking and creates your dataset automatically. Historical data is not included — the export begins from the day you enable it.

What data does GA4 export to BigQuery?

Every event GA4 collects, one row per event, in a daily events_YYYYMMDD table. Event parameters are stored as a REPEATED RECORD (array of key-value pairs) rather than flat columns. If you enable user data export, GA4 also writes a pseudonymous_users_YYYYMMDD table with user-level lifetime metrics.

How does the GA4 BigQuery schema work?

The top-level fields include event_date, event_timestamp, event_name, user_pseudo_id, and nested STRUCTs for device and geo. Event parameters and user properties are stored as REPEATED RECORDs (arrays), which means you use UNNEST(event_params) inside a subquery to extract any individual parameter value.

How do I query event_params in BigQuery?

Use a correlated subquery with UNNEST. For a string parameter: (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'). For an integer: use value.int_value. For revenue: use value.double_value. Each parameter only populates one of the four value type fields — the others are null.

What is the events_intraday table in GA4 BigQuery?

The events_intraday_YYYYMMDD table is a running export of today's events, updated throughout the day. It is not final — GA4 can add, update, or replace it. Once the day closes, the finalized events_YYYYMMDD table supersedes it. Use the daily tables for historical analysis; use the intraday table only for real-time monitoring where latency matters more than finality.

Is GA4 BigQuery export free?

The export itself is free — GA4 does not charge to send data to BigQuery. You pay BigQuery's standard storage ($0.02/GB/month) and query ($5/TB scanned) rates. BigQuery's free tier covers 10 GB of storage and 1 TB of queries per month. For most small-to-medium businesses, the entire monthly GA4 BigQuery workload fits within the free tier.

Can I get historical data from GA4 in BigQuery?

No. The BigQuery export starts from the day you enable it — there is no backfill. Events before that date exist only inside GA4's own reporting interface (subject to its retention settings and 14-month maximum). If historical unsampled data is important, enable the export as early as possible, even if you don't plan to query it immediately.

How do I count sessions in GA4 BigQuery?

Concatenate user_pseudo_id with the ga_session_id integer from event_params, then count distinct values: COUNT(DISTINCT CONCAT(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id'))). The ga_session_id alone is not unique across users — the combination with user_pseudo_id makes it globally unique.