---
title: "The GA4 BigQuery export: setup, schema and SQL you will…"
description: "Why the export changes what you can do The GA4 interface is aggregated, thresholded, sometimes sampled and limited by retention. The BigQuery export…"
url: https://optimizeall.com/learn/web-analytics-with-ga4/bigquery-export-and-sql
updated: 2026-10-05
---

Web Analytics with Google Analytics 4 · BigQuery export and AI-assisted analysis · lesson 19 of 20 · 8 min

# The GA4 BigQuery export: setup, schema and SQL you will reuse

## Why the export changes what you can do

The GA4 interface is aggregated, thresholded, sometimes sampled and limited by retention. The **BigQuery export** gives you raw, event-level data in your own Google Cloud project: every event, with its parameters, device, geography and traffic-source fields. With it you can keep history beyond the retention window, build unsampled funnels, join GA4 with CRM, cost or order data, and schedule health checks.

## Export options and limits (verify current terms)

| Option | What it does | Notes |
|---|---|---|
| Daily export | One `events_YYYYMMDD` table per day | Standard properties have a daily limit of **1 million events** for this export; consistently exceeding it can pause the export |
| Streaming export | Near real-time `events_intraday_YYYYMMDD` tables | No event limit; costs apply; some fields (e.g., `session_traffic_source_last_click`) are not in intraday tables |
| Fresh Daily (GA4 360) | Faster, more complete daily tables for 360 properties | 360 only |
| User data export | `users_` and `pseudonymous_users_` tables | User-level attributes and predictions where available |

Setup: link the property in **Admin > Product links > BigQuery links**, choose the Google Cloud project, the data location (choose a region that suits your data-residency obligations) and the export types. The export is not retroactive: data starts from the day you link. Link early, even if you do not query it yet. BigQuery has a free tier and a sandbox; beyond that you pay for storage and for bytes scanned by queries, so filter by date with `_TABLE_SUFFIX` and select only the columns you need.

## The schema in five ideas

1. **One row per event.** `event_name`, `event_timestamp` (microseconds, UTC), `event_date` (property time zone).
2. **Parameters are nested.** `event_params` is a repeated record of `key` plus a `value` with `string_value`, `int_value`, `double_value` or `float_value`. Read one with a subquery over `UNNEST(event_params)`.
3. **Users and sessions are derived.** `user_pseudo_id` identifies the browser/app instance; a session is `user_pseudo_id` plus the `ga_session_id` event parameter.
4. **Traffic source has three layers.** `traffic_source` (the user's first source), `collected_traffic_source` (values collected on the event, such as UTMs and click ids) and `session_traffic_source_last_click` (session-scoped last-click attribution matching the session acquisition reports; added in 2024, not in intraday tables).
5. **Privacy fields.** `privacy_info.analytics_storage` and `privacy_info.ads_storage` show consent state (`Yes`, `No`, or null). Consent-denied rows in advanced consent mode have no `user_pseudo_id`. The export contains **no modeled data**.

## Hands-on: five queries you will reuse

**1. Sessions, users and purchases per day**

```sql
SELECT
  event_date,
  COUNT(DISTINCT user_pseudo_id) AS users,
  COUNT(DISTINCT CONCAT(user_pseudo_id, '-',
        CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING))) AS sessions,
  COUNTIF(event_name = 'purchase') AS purchases
FROM `my-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260901' AND '20260930'
  AND user_pseudo_id IS NOT NULL
GROUP BY event_date
ORDER BY event_date;
```

**2. Revenue by session channel (last click)**

```sql
SELECT
  session_traffic_source_last_click.cross_channel_campaign.default_channel_group AS channel,
  COUNT(DISTINCT ecommerce.transaction_id) AS orders,
  ROUND(SUM(ecommerce.purchase_revenue), 2) AS revenue
FROM `my-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260901' AND '20260930'
  AND event_name = 'purchase'
GROUP BY channel
ORDER BY revenue DESC;
```

**3. Landing pages with key-event rate** (first `page_view` of each session)

```sql
WITH pv AS (
  SELECT user_pseudo_id,
         (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS sid,
         (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page,
         event_timestamp, event_name
  FROM `my-project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260901' AND '20260930' AND user_pseudo_id IS NOT NULL
), sessions AS (
  SELECT user_pseudo_id, sid,
         ARRAY_AGG(IF(event_name = 'page_view', page, NULL) IGNORE NULLS ORDER BY event_timestamp LIMIT 1)[SAFE_OFFSET(0)] AS landing,
         COUNTIF(event_name IN ('purchase', 'generate_lead')) > 0 AS converted
  FROM pv GROUP BY 1, 2
)
SELECT REGEXP_REPLACE(landing, r'\?.*$', '') AS landing_page,
       COUNT(*) AS sessions,
       ROUND(AVG(IF(converted, 1, 0)) * 100, 2) AS key_event_rate_pct
FROM sessions
GROUP BY landing_page
HAVING sessions >= 100
ORDER BY sessions DESC
LIMIT 25;
```

**4. Consent share** (see module 4) and **5. Duplicate transactions** (see module 2) complete the starter kit.

## Why the numbers will not match the interface

Expect differences: the interface can include modeled data, applies thresholds, may use different identity (user ID, device, modeling), counts users and sessions with approximation algorithms, and attributes in its own way. Decide which source is authoritative for which purpose, document it, and stop trying to reconcile to the last unit.

## Worked example: an e-commerce brand in Lahore

A Lahore home-goods brand links BigQuery, schedules a daily summary table (date, channel, sessions, orders, revenue), and joins it with its order system on `transaction_id` to add **delivered** revenue for cash-on-delivery orders. For the first time the team can report channel performance on delivered revenue, not just orders placed, and it discovers that one marketplace-referral channel has a much higher refusal rate than others.

## Second worked example: a UK publisher beyond 14 months

A London publisher needs three-year content trends, but GA4 exploration retention is at most 14 months on a standard property. Because the export was linked early, the analyst builds year-over-year and three-year views of engaged sessions by content group directly from BigQuery, and feeds a Data Studio dashboard from a summary table.

## Pitfalls

- Linking the export late (no backfill).
- Querying `events_*` without a `_TABLE_SUFFIX` filter and paying to scan years of data.
- Counting sessions without `ga_session_id`, or users without handling consent-denied null ids.
- Treating the export as "wrong" because it does not match modeled interface numbers.

## Video lecture: The GA4 BigQuery export: setup, schema and SQL you will reuse

Lecture coming soon · 15 chapters · about 8 minutes. Read the full transcript below.

1. BigQuery export and SQL
2. Why it matters
3. Export options
4. Cost hygiene
5. Schema ideas 1 to 3
6. Schema ideas 4 and 5
7. Example 1: London publisher
8. Example 2: Lahore home goods
9. Watch me do it, part 1
10. Watch me do it, part 2
11. Why numbers differ
12. Common mistakes
13. Using it well
14. Recap
15. Try this now

## Lecture transcript

### BigQuery export and SQL

The G A four interface is like a restaurant menu: curated, convenient, and limited to what the kitchen decided to serve. The BigQuery export is the keys to the kitchen: every raw ingredient, every event, with every parameter, in your own cloud project. In this lecture you'll learn what the export gives you, its options and limits, the five ideas you need to read the schema, five reusable SQL queries, why the numbers won't match the interface, and how teams use it to answer questions G A four alone can't.

### Why it matters

Why does this matter? Because the interface is aggregated, thresholded, sometimes sampled, and limited by retention. With the export you can keep history beyond fourteen months, build unsampled funnels, join G A four with C R M, cost or order data, and schedule health checks that alert you before stakeholders notice problems. It's the difference between reading reports and owning your data. And since the export isn't retroactive, the most important step is simply linking it early.

### Export options

Here are the options, and please verify current terms. The daily export writes one events table per day. Standard properties have a daily limit of one million events for this export, and consistently exceeding it can pause the export. The streaming export writes near real-time intraday tables with no event limit, but costs apply, and some fields aren't included. Fresh Daily is for G A four three sixty properties. And the user data export adds user-level tables. You link it in admin, under product links, choosing the cloud project, data location and export types.

### Cost hygiene

Costs are manageable if you're careful. BigQuery has a free tier and a sandbox. Beyond that, you pay for storage and for the bytes each query scans. The single biggest cost mistake is querying events star without a date filter, and scanning years of data to answer a question about last week. So always filter with the table suffix, select only the columns you need, and build small summary tables for dashboards. Choose a data location that suits your data-residency obligations when you link.

### Schema ideas 1 to 3

Now the schema, in five ideas. One: one row per event, with event name, a timestamp in microseconds in UTC, and an event date in the property's time zone. Two: parameters are nested in a repeated field called event params, each a key with a string, integer or double value, so you read one with a subquery over UNNEST. Three: users and sessions are derived. User pseudo id identifies the browser or app instance, and a session is that id plus the ga session id parameter.

### Schema ideas 4 and 5

Four: traffic source has three layers. Traffic source is the user's first source. Collected traffic source holds values collected on each event, like UTMs and click ids. And session traffic source last click holds session-scoped last-click attribution that matches the session acquisition reports; it was added in twenty twenty-four and isn't in intraday tables. Five: privacy fields. Privacy info shows consent state as Yes, No or null. Consent-denied rows have no user pseudo id. And the export contains no modeled data.

### Example 1: London publisher

First example, a simple one. A London publisher needs three-year content trends, but G A four exploration retention is at most fourteen months on a standard property. Because the export was linked early, the analyst builds year-over-year and three-year views of engaged sessions by content group straight from BigQuery, and feeds a Data Studio dashboard from a summary table. The retention setting stopped mattering for trend analysis, because the raw history lives in their own project.

### Example 2: Lahore home goods

Second example, a business case. A Lahore home-goods brand links BigQuery, schedules a daily summary table with date, channel, sessions, orders and revenue, and joins it with its order system on transaction id, adding delivered revenue for cash on delivery orders. For the first time, the team can report channel performance on delivered revenue rather than orders placed. And it discovers that one marketplace referral channel has a much higher refusal rate than the others, which changes how much it's worth paying for that traffic.

### Watch me do it, part 1

Watch me write the first query. Sessions, users and purchases per day for September. I select event date. Users: count distinct user pseudo id. Sessions: count distinct user pseudo id joined with the ga session id, which I pull from event params with a small UNNEST subquery. Purchases: count if the event name is purchase. I filter the table suffix to September, and exclude rows with a null user pseudo id, because consent-denied rows can't be counted as users or sessions. Group by date, order by date. It runs in seconds and scans only one month.

### Watch me do it, part 2

Next, revenue by channel. I use the session traffic source last click record's default channel group, count distinct transaction ids as orders, and sum purchase revenue, for purchase events in September. Then landing pages. That one is a little longer: for each session, I take the first page view's location as the landing page, strip the query string, and flag whether the session had a purchase or lead. Then I compute sessions and key event rate per landing page, keeping pages with at least a hundred sessions, so small numbers don't mislead me.

### Why numbers differ

Now, why won't these numbers match the interface? Because the interface can include modeled data, applies thresholds, may use different identity like user id or modeling, counts users and sessions with approximation algorithms, and attributes in its own way. So don't try to reconcile to the last unit. Decide which source is authoritative for which purpose, for example the interface for executive trend reporting and BigQuery for funnels and joins, write that down, and label every dashboard with its source.

### Common mistakes

Common mistakes. Linking the export late, because there's no backfill. Querying events star without a table suffix filter, and paying to scan years of data. Counting sessions without the ga session id, or users without handling consent-denied null ids. And treating the export as wrong because it doesn't match modeled interface numbers.

### Using it well

How do you know you're using the export well? Three signs. Your key funnels and KPIs have scheduled summary tables with history beyond the retention window. At least one decision has been made using a join G A four couldn't do alone, like delivered revenue or lead quality. And your monthly BigQuery bill is predictable, because queries are filtered and dashboards read small tables.

### Recap

Recap. The export gives you raw, event-level data in your own project. Know the options and the one million events per day limit for standard daily exports. Remember the five schema ideas: one row per event, nested parameters, derived sessions, three traffic source layers, and privacy fields with no modeling. Filter by date to control cost. Build summary tables. And choose, document and label an authoritative source for each purpose.

### Try this now

Try this now. If your property isn't linked to BigQuery, link it today, even if you won't query it yet, because there's no backfill. If it is linked, run the lesson's first three queries for last month, compare the results with the interface, and write down the differences and the reasons you think explain them.

## Key takeaways

- Link the BigQuery export early: it is not retroactive, and it removes sampling and retention limits for analysis.
- Standard properties have a 1 million events/day limit on the daily export; streaming has no event limit but costs apply.
- Read parameters with UNNEST(event_params); sessions are user_pseudo_id plus ga_session_id; the export has no modeled data.
- Filter by _TABLE_SUFFIX, build summary tables, and label which source is authoritative for which purpose.

## Try it

Link the BigQuery export if it is not linked; otherwise run the lesson's first three queries for last month and document how and why they differ from the interface.

- [Previous: Running analytics as an ongoing practice](https://optimizeall.com/learn/web-analytics-with-ga4/analytics-operating-rhythm)
- [Next: AI-assisted GA4 analysis: Ask Advisor, Gemini, MCP and a verification ladder](https://optimizeall.com/learn/web-analytics-with-ga4/ai-assisted-ga4-analysis)
- [All lessons of Web Analytics with Google Analytics 4](https://optimizeall.com/learn/web-analytics-with-ga4)