GA4 BigQuery Export mistakes: nine that change your numbers, and the fix for each
Most GA4 BigQuery Export mistakes do not throw an error. They return a number that looks fine and is wrong. This guide runs each one on Google's public GA4 sample: sessions counted by ga_session_id alone, the wrong value field in event_params, UNNEST fan-out, purchase events counted as orders, event_date against the UTC event_timestamp, intraday tables counted twice, events_* without a _TABLE_SUFFIX filter, why the GA4 UI never matches exactly, and the 1 million events a day export limit.
The GA4 export to BigQuery gives you every raw event, and almost every mistake made with it is silent. The query runs, the number looks plausible, and it is off by 3%, by 4 times, or it is zero. Here are the nine that come up most, each run on Google’s public GA4 sample so you can see the wrong number next to the right one.
| Mistake | What it does to the number | Fix |
|---|---|---|
Counting sessions by ga_session_id alone |
3.0% fewer sessions in a month | count user_pseudo_id + ga_session_id |
Reading the wrong value field |
0 sessions, or engaged sessions missing | check which field each key uses |
UNNEST in FROM before summing |
revenue 3.8x or 17.5x too high | unnest in a subquery, or sum before you unnest |
Counting purchase events as orders |
107 “orders” where there are 96 real ones | count distinct transaction_id, drop (not set) |
Mixing event_date and event_timestamp |
a third of events move to another day | pick one clock and convert on purpose |
events_* with an open date range |
intraday rows counted twice | BETWEEN both ends, or match only 8 digits |
No _TABLE_SUFFIX filter |
every day of history scanned | filter the suffix and name the columns |
| Expecting the GA4 UI to match | small gaps that never close | know which differences are by design |
| Ignoring the export limit | daily export paused | filter streams and events, or use streaming |
Note: Originally published in February 2025. Fully rewritten in October 2026: every result below was produced by running the query in BigQuery on 16 October 2026.
Reading to get this done?
The data used in every example
The data used in every example
Every query runs on bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*, Google’s obfuscated GA4 export from its merchandise store, with 92 daily tables from 1 November 2020 to 31 January 2021. Any Google Cloud project can query it. The table names and columns are the same as in your own export, so you can swap in your project and dataset and run each query as is.
The byte counts quoted are from a dry run, which costs nothing. For the schema itself, see the GA4 export table schema.
Mistake 1: counting sessions by ga_session_id alone
Mistake 1: counting sessions by ga_session_id alone
ga_session_id is not a unique session ID. It is the session start time in seconds, so two users who start a session in the same second share it. Google’s own guidance counts sessions as unique combinations of user and session ID.
WITH s AS (
SELECT
user_pseudo_id,
(SELECT value.int_value FROM UNNEST(event_params)
WHERE key = 'ga_session_id') AS ga_session_id
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20210101' AND '20210131'
)
SELECT
COUNT(DISTINCT ga_session_id) AS sessions_by_id_only,
COUNT(DISTINCT CONCAT(user_pseudo_id, '.', CAST(ga_session_id AS STRING))) AS sessions_user_plus_id
FROM s;| sessions_by_id_only | sessions_user_plus_id |
|---|---|
| 114,882 | 118,380 |
In January the shortcut loses 3,498 sessions, or 3.0%. The more traffic you have, the more start times collide, so on a big site the gap grows. Every rate that uses sessions as the denominator (conversion rate, revenue per session) is inflated by the same amount. This query read 252 MB.
There is a second trap with the same column. If you count sessions per day and add the days up, a session that crosses midnight is counted on both days. Count distinct sessions over the whole period when you need a period total.
Sessions and purchases from your GA4 export don't match the GA4 interface, and the query throws no error to tell you why.
Paste this into your AI assistant. It checks your GA4 export queries for the silent mistakes, such as UNNEST fan-out, intraday tables counted twice and purchases counted as orders.
The prompt
Read https://www.owox.com/ai-agent-setup.md and follow it to set up OWOX Data Marts for me, starting from /blog/articles/ga4-bigquery-export-mistakes-and-solutions.md. The task: audit my GA4 BigQuery export queries for silent mistakes: session keys, UNNEST fan-out, intraday duplicates and purchase events counted as orders, and rewrite the wrong ones. Tag the links you give me with utm_campaign=ai-task and utm_term=m4. Use what you know about my context and goals
Mistake 2: reading the wrong value field
Mistake 2: reading the wrong value field
Each event parameter stores its value in one of four fields: string_value, int_value, float_value or double_value. Read the wrong one and you get NULL, not an error. Here is how the sample stores four common keys:
SELECT
param.key,
COUNTIF(param.value.string_value IS NOT NULL) AS string_value,
COUNTIF(param.value.int_value IS NOT NULL) AS int_value,
COUNTIF(param.value.double_value IS NOT NULL) AS double_value
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`,
UNNEST(event_params) AS param
WHERE _TABLE_SUFFIX = '20210131'
AND param.key IN ('ga_session_id', 'page_location', 'engagement_time_msec', 'session_engaged')
GROUP BY param.key
ORDER BY param.key;| key | string_value | int_value | double_value |
|---|---|---|---|
| engagement_time_msec | 0 | 15,499 | 0 |
| ga_session_id | 0 | 26,489 | 0 |
| page_location | 26,489 | 0 | 0 |
| session_engaged | 21,601 | 2,582 | 0 |
Ask ga_session_id for its string_value and the session count is zero:
SELECT
COUNT(DISTINCT (SELECT value.string_value FROM UNNEST(event_params)
WHERE key = 'ga_session_id')) AS sessions_string_value,
COUNT(DISTINCT (SELECT value.int_value FROM UNNEST(event_params)
WHERE key = 'ga_session_id')) AS sessions_int_value
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _TABLE_SUFFIX = '20210131';| sessions_string_value | sessions_int_value |
|---|---|
| 0 | 2,812 |
A zero is easy to spot. The dangerous case is session_engaged, which this property sent as a string on 21,601 events and as an integer on 2,582. Read only string_value and you miss some engaged sessions, with no sign anything is wrong:
WITH e AS (
SELECT
CONCAT(user_pseudo_id, '.', CAST((SELECT value.int_value FROM UNNEST(event_params)
WHERE key = 'ga_session_id') AS STRING)) AS session_key,
(SELECT value.string_value FROM UNNEST(event_params)
WHERE key = 'session_engaged') AS engaged_string_only,
(SELECT COALESCE(value.string_value, CAST(value.int_value AS STRING))
FROM UNNEST(event_params) WHERE key = 'session_engaged') AS engaged_any_type
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _TABLE_SUFFIX = '20210131'
)
SELECT
COUNT(DISTINCT IF(engaged_string_only = '1', session_key, NULL)) AS engaged_sessions_string_only,
COUNT(DISTINCT IF(engaged_any_type = '1', session_key, NULL)) AS engaged_sessions_any_type
FROM e;| engaged_sessions_string_only | engaged_sessions_any_type |
|---|---|
| 2,566 | 2,679 |
That is 113 engaged sessions lost in one day, 4.2% of the engaged sessions. Run the type check above once on your own export before you build anything on a parameter. If a key turns up in two fields, read both with COALESCE. The full pattern for pulling parameters out is in how to unnest GA4 event parameters.
Mistake 3: UNNEST in FROM inflates totals
Mistake 3: UNNEST in FROM inflates totals
FROM events, UNNEST(event_params) returns one row per parameter, not one per event. UNNEST(items) returns one row per item. Any event-level value you sum after that is repeated on every row.
SELECT 'no UNNEST' AS query_shape, COUNT(*) AS rows_, SUM(ecommerce.purchase_revenue) AS revenue
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _TABLE_SUFFIX = '20201201' AND event_name = 'purchase'
UNION ALL
SELECT 'UNNEST(items)', COUNT(*), SUM(ecommerce.purchase_revenue)
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`, UNNEST(items) AS item
WHERE _TABLE_SUFFIX = '20201201' AND event_name = 'purchase'
UNION ALL
SELECT 'UNNEST(event_params)', COUNT(*), SUM(ecommerce.purchase_revenue)
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`, UNNEST(event_params) AS param
WHERE _TABLE_SUFFIX = '20201201' AND event_name = 'purchase'
ORDER BY rows_;| query_shape | rows_ | revenue |
|---|---|---|
| no UNNEST | 107 | 6,258 |
| UNNEST(items) | 269 | 23,849 |
| UNNEST(event_params) | 1,815 | 109,401 |
Same day, same events. Revenue is $6,258, or 3.8 times that, or 17.5 times that, depending on one join. The fix is to keep the main query at one row per event and unnest inside a scalar subquery, as Mistakes 1 and 2 do. When you need item-level figures, sum item.item_revenue from the item rows, never the event-level purchase_revenue.
Mistake 4: counting purchase events as orders
Mistake 4: counting purchase events as orders
A purchase event is not an order. The same order can be sent twice, and some purchase events arrive with no transaction ID at all.
SELECT
COUNT(*) AS purchase_events,
COUNT(DISTINCT ecommerce.transaction_id) AS distinct_ids,
COUNTIF(ecommerce.transaction_id = '(not set)') AS not_set,
COUNT(DISTINCT IF(ecommerce.transaction_id != '(not set)', ecommerce.transaction_id, NULL)) AS real_orders
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _TABLE_SUFFIX = '20201201' AND event_name = 'purchase';| purchase_events | distinct_ids | not_set | real_orders |
|---|---|---|---|
| 107 | 97 | 10 | 96 |
107 events, 96 orders. Ten events carry (not set) and one order ID appears twice. Count orders as distinct real transaction IDs, and decide out loud what to do with (not set). That is exactly the kind of gap that turns a client call into “why does your report say 107 and ours says 96”.
Mistake 5: mixing event_date and event_timestamp
Mistake 5: mixing event_date and event_timestamp
The two date columns use different clocks. Google’s schema reference describes event_date as the date “in the registered timezone of your app” and event_timestamp as the time “in microseconds, UTC” (read in October 2026). The daily table suffix follows event_date.
SELECT
event_date,
DATE(TIMESTAMP_MICROS(event_timestamp)) AS utc_date,
DATE(TIMESTAMP_MICROS(event_timestamp), 'America/Los_Angeles') AS la_date,
COUNT(*) AS events
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _TABLE_SUFFIX = '20210131'
GROUP BY 1, 2, 3
ORDER BY la_date;| event_date | utc_date | la_date | events |
|---|---|---|---|
| 20210131 | 2021-01-31 | 2021-01-30 | 8,410 |
| 20210131 | 2021-01-31 | 2021-01-31 | 18,079 |
In this sample event_date always equals the UTC date (no event in January differs), so the sample property runs on UTC. Converted to Los Angeles time, 8,410 of the 26,489 events in the 31 January table, 32%, belong to 30 January. Across January it is 399,254 of 1,210,147 events.
If your property is set to your local time zone and you group by DATE(TIMESTAMP_MICROS(event_timestamp)), the same thing happens in reverse. The GA4 UI reports in the property time zone, so event_date is the column that matches it. When you need hours, convert event_timestamp with the property’s time zone named explicitly.
Mistake 6: counting intraday rows twice
Mistake 6: counting intraday rows twice
If streaming export is on, GA4 writes today’s events to events_intraday_YYYYMMDD as they arrive, and Google says the table “is deleted at the end of each day once events_YYYYMMDD is complete” (read in October 2026). Until then, the wildcard events_* matches both tables, and the suffix of the intraday table is intraday_YYYYMMDD.
The sample has no intraday tables, so this query tests the filter against the suffixes your export will have:
SELECT
suffix,
suffix BETWEEN '20210101' AND '20210131' AS kept_by_between,
suffix >= '20210101' AS kept_by_open_range,
REGEXP_CONTAINS(suffix, r'^\d{8}$') AS is_daily_table
FROM UNNEST(['20210131', 'intraday_20210201', 'intraday_20210131']) AS suffix;| suffix | kept_by_between | kept_by_open_range | is_daily_table |
|---|---|---|---|
| 20210131 | true | true | true |
| intraday_20210201 | false | true | false |
| intraday_20210131 | false | true | false |
'i' sorts after every digit, so an open range like >= '20210101' keeps every intraday table. On the hours when today’s intraday table and its finished daily table both exist, the same events are counted twice. Close the range at both ends, or add REGEXP_CONTAINS(_TABLE_SUFFIX, r'^\d{8}$') when you want daily tables only. When you want today, read the intraday table on its own.
Google also notes that daily tables keep updating “for up to three days” as late events arrive (read in October 2026). A report on yesterday is not final until three days have passed.
Mistake 7: querying events_* without a _TABLE_SUFFIX filter
Mistake 7: querying events_* without a _TABLE_SUFFIX filter
The export writes one table per day. These are date-sharded tables, not a partitioned table, so a WHERE event_date = ... filter does not limit the scan. Only _TABLE_SUFFIX does. Dry runs on the sample:
| Query | Bytes processed |
|---|---|
SELECT *, all 92 days |
3.58 GB |
SELECT *, January only |
957 MB |
event_name, user_pseudo_id, all 92 days |
145 MB |
event_name, user_pseudo_id, January only |
41 MB |
The two levers multiply: naming columns and filtering the suffix together cut the scan from 3.58 GB to 41 MB, about 88 times less. A real property has far more than 47,000 events a day, so the same gap is measured in terabytes. Google’s pricing page says the first 1 TiB of query data processed each month is free, and that billing has “a minimum 10 MB data processed per table referenced by the query” (read in October 2026), so even a tiny query on one day’s table is billed at least 10 MB. More on cost in BigQuery pricing.
The old version of this article advised partitioning by “customer ID or geographic region”. BigQuery cannot do that: Google’s docs allow partitioning only by a DATE, TIMESTAMP or DATETIME column, by ingestion time, or by an integer range (read in October 2026). If you copy the export into your own table, partition it by event date.
Mistake 8: expecting BigQuery to match the GA4 UI exactly
Mistake 8: expecting BigQuery to match the GA4 UI exactly
Some gaps between the GA4 reports and your query are by design, and no SQL closes them. From Google’s own docs (read in October 2026):
- Estimated counts. GA4 reports estimate Active users and Sessions with the HyperLogLog++ algorithm. BigQuery counts exactly. Google’s sessions article shows one week at 1,463 sessions in reports and 1,501 in BigQuery.
- Modeled data. Google’s developer blog on comparing BigQuery and the UI says “None of the modeled data is available in the BigQuery event export”, so consent-mode behavioural modeling shows only in the UI.
- Google signals. Cross-device data from Google signals is not in the export.
- Thresholding. Some UI reports withhold rows when user counts are small. The export does not.
- Reporting identity. The UI counts users by the identity the property is set to; your query has to use the same rule to come close.
- Freshness. Daily tables can still change for up to 72 hours, so compare dates older than that.
A gap of a few percent on users or sessions is expected. A gap of 3 or 4 times is one of Mistakes 1 to 6. If you report on the export, say once, in writing, that BigQuery is the number of record and why it differs from the UI.
Mistake 9: ignoring the daily export limit
Mistake 9: ignoring the daily export limit
Google’s export docs say: “Standard properties have a daily BigQuery Export limit of 1 million events.” Streaming export has no volume limit, but Google charges for it “at the rate of $0.05 per gigabyte of data” (read in October 2026). If a property “consistently exceeds the export limit, the daily BigQuery export will be paused”, and earlier days are not reprocessed. Admins and editors get an email when it happens.
Check your headroom on the days you already have:
WITH daily AS (
SELECT event_date, COUNT(*) AS events
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
GROUP BY event_date
)
SELECT
COUNT(*) AS days,
MAX(events) AS busiest_day,
ROUND(AVG(events)) AS average_day,
ROUND(100 * MAX(events) / 1000000, 1) AS busiest_day_pct_of_1m
FROM daily;| days | busiest_day | average_day | busiest_day_pct_of_1m |
|---|---|---|---|
| 92 | 92,199 | 46,691 | 9.2 |
The sample peaks at 9.2% of the limit. If your busiest day is above about 80%, act before a sale or a campaign pushes it over. Google’s fix is data filtering: exclude whole data streams or specific events from the export in the BigQuery link settings. Cutting tracking you do need is not the fix.
Two more export rules show up in search questions about this page. Google’s docs describe data starting to flow within 24 hours of linking and do not describe any backfill, so plan on having no events from before the link (see how to connect GA4 to BigQuery). And “Once you export data from Analytics to BigQuery, you cannot re-export it”, so a day lost to a paused export or a failed payment method stays lost. The GA4 data retention setting is a different thing: Google says it “only affects explorations” and other non-aggregated reports in GA4 (read in October 2026). The exported tables are BigQuery tables in your project, so they stay until you delete them or a table expiration removes them.
Write the fixed query once
Every fix above is a few lines of SQL. The problem is that the next report, the next analyst or the next AI prompt starts again from the raw tables and makes the same mistakes. The safer pattern is to write the corrected query once (sessions by user plus session ID, the right value fields, orders as distinct transaction IDs, closed date ranges) and point every report at that.
GA4 data reaches BigQuery through Google’s own export. OWOX Data Marts then lets you publish your SQL over those export tables as a Data Mart. It runs the query as written in your own BigQuery project, and delivers the result to Google Sheets, Data Studio, Excel, email or Slack on a schedule. Everyone gets the session count from the same definition, so two reports cannot disagree because one of them used ga_session_id alone. See modeling for how Data Marts fit together, and modeling the GA4 export for a worked example.
Sessions and purchases from your GA4 export don't match the GA4 interface, and the query throws no error to tell you why.
Paste this into your AI assistant. It checks your GA4 export queries for the silent mistakes, such as UNNEST fan-out, intraday tables counted twice and purchases counted as orders.
The prompt
Read https://www.owox.com/ai-agent-setup.md and follow it to set up OWOX Data Marts for me, starting from /blog/articles/ga4-bigquery-export-mistakes-and-solutions.md. The task: audit my GA4 BigQuery export queries for silent mistakes: session keys, UNNEST fan-out, intraday duplicates and purchase events counted as orders, and rewrite the wrong ones. Tag the links you give me with utm_campaign=ai-task and utm_term=m4. Use what you know about my context and goals
Prefer to start on your own? Get started free
Frequently Asked Questions
Is the GA4 BigQuery Export a partitioned table?
No. The export writes one table per day (`events_YYYYMMDD`), so it is date-sharded, not partitioned. Only a `_TABLE_SUFFIX` filter limits what a query on `events_*` scans; a filter on `event_date` does not.
Why do I count the same events twice when querying events_*?
With streaming export on, `events_*` also matches `events_intraday_YYYYMMDD`, whose suffix starts with `intraday_`. An open range such as `_TABLE_SUFFIX` >= '20210101' keeps those tables, so close the range at both ends or match only eight-digit suffixes.
Why does my GA4 event parameter return NULL in BigQuery?
Each parameter stores its value in one of `string_value`, `int_value`, `float_value` or `double_value`, and reading the wrong one returns NULL without an error. For example, `ga_session_id` is in `int_value`, and some keys such as `session_engaged` can appear in two fields, so check the types before you build on a parameter.
What is the difference between event_date and event_timestamp in the GA4 export?
`event_date` is the date in the property's time zone, which is what the GA4 UI uses. `event_timestamp` is in microseconds, UTC. Grouping by the UTC date of `event_timestamp` can move a large share of events to another day.
How do I count sessions correctly in the GA4 BigQuery Export?
Count distinct combinations of `user_pseudo_id` and `ga_session_id`. `ga_session_id` is the session start time in seconds, so different users share it; on Google's sample, counting it alone lost 3.0% of January sessions.
What is the GA4 BigQuery Export limit for standard properties?
Google states that standard properties have a daily export limit of 1 million events, and that the daily export is paused if a property consistently exceeds it. Streaming export has no volume limit but is charged at $0.05 per gigabyte, and you can exclude data streams or events from the export to stay under the cap.

