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

0:00 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.

0:39 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.

1:12 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.

1:53 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.

2:30 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.

3:07 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.

3:42 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.

4:15 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.

4:52 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.

5:34 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.

6:14 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.

6:49 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.

7:12 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.

7:40 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.

8:10 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.