# GA4 BigQuery export for production store catalogues

Link a Magento, Shopify or WooCommerce property to BigQuery, pick daily versus streaming, and reconcile purchase rows to the order admin.

- Date: 2026-09-22
- Category: Analytics

GA4 BigQuery export is how I get raw purchase and item rows out of a Magento, Shopify or WooCommerce property so I can reconcile them to the order admin. I am a Gold Coast web developer with 18 years on those catalogues. The GA4 interface is useful. It is not the ledger.

If `purchase` is still fiction, fix that first. Use my notes on [GA4 ecommerce events that match real revenue](https://alanvo.com/blog/ga4-ecommerce-events/) and on [GA4 item-scoped custom dimensions](https://alanvo.com/blog/ga4-item-scoped-custom-dimensions/). This note assumes the items array already fires, and you now need a queryable copy of those events.

## Why GA4 BigQuery export matters on a live catalogue

GA4 BigQuery export matters on a live catalogue because merchandisers argue about SKUs, not about a modelled Purchases card.

Google's [BigQuery Export help](https://support.google.com/analytics/answer/9358801) is explicit: the export is raw event data. Linked product data from Merchant Center does not come along. You cannot re-export a day you already sent. The UI can still apply modelling, thresholds and last-click presentation that the warehouse will not invent for you. That is the point. I want the hit that arrived, with the `items` array the theme actually sent.

On a Magento catalogue that is the difference between "GA4 says gold sold" and a join from `item_id` to the simple SKU in admin. On Shopify it is the difference between a thank-you pixel and the order name in Admin. On WooCommerce it is the increment ID versus a plugin that fired `purchase` twice. Explorations hide that. `events_YYYYMMDD` does not.

I also want consent-mode cookieless pings in the same table as identified hits. Google says those pings, plus `user_id` and custom dimensions you already collect, land in the export when Consent Mode is on. That is how I see what the CMP dropped instead of guessing from a quieter UI.

The other reason is the 1 million event daily cap on a standard property. A furniture grid that fires `view_item_list` on every scroll will blow that cap. The daily export then pauses. Previous days are not backfilled. I would rather know that from Admin than from a merchandiser who thinks last Tuesday vanished.

## How GA4 BigQuery export actually works

GA4 BigQuery export actually works as a product link: an Editor on the Analytics property and an owner on a Google Cloud project send raw events into a dataset, then you query daily tables with SQL.

The [setup guide](https://support.google.com/analytics/answer/9823238) is the Admin path I follow. Enable the BigQuery API. In Analytics, Product links, BigQuery links, choose the project and a dataset location. Location is locked after create unless you use Google's cross-region dataset replication, or you delete, copy and relink and accept a gap.

![GA4 BigQuery export charts on a widescreen monitor](../../images/blog/ga4-bigquery-export-catalogues-1.jpg)

You pick streams and events to exclude at link time. That filter is how a standard property stays under 1 million events a day. I exclude noisy enhanced-measurement events before I exclude `view_item`. I never exclude `purchase`.

Export type is the next decision. Daily export is the complete previous day, last click observed, no modelling. Google says it typically lands mid-afternoon in the property timezone and can slip later. Streaming writes `events_intraday_YYYYMMDD` through the day and deletes that table when `events_YYYYMMDD` is complete. It has no event-volume cap. It drops new-user and new-session traffic source fields, and user attribution needs about 24 hours, so do not use streaming for attribution. Fresh Daily is 360 only, for Normal and Large properties, same schema, often useful by 5am. Each type bills storage and query. Streaming adds a documented $0.05 per gigabyte, roughly 600,000 events depending on payload size.

The [developer overview](https://developers.google.com/analytics/bigquery/overview) and the [export schemas](https://developers.google.com/analytics/bigquery/schemas) are the contract. Event tables hold `event_name`, `event_timestamp`, `user_pseudo_id`, optional `user_id`, `event_params`, `items`, and traffic source structs. Parameters are not columns. You `UNNEST(event_params)` and read `string_value`, `int_value`, `float_value` or `double_value`. Google's [basic queries](https://developers.google.com/analytics/bigquery/basic-queries) do that for `value` on `purchase`, and they `UNNEST(items)` for `item_id` on `add_to_cart`. `transaction_id` is an event parameter. Join it to Magento increment IDs, Shopify order names, or WooCommerce order numbers.

Query `events_*` with `_TABLE_SUFFIX` between dates. Do not query `events_intraday_*` for a closed day. Daily tables can still receive late Measurement Protocol or Firebase events for two calendar days plus today. A 360 property also gets a completeness signal in Cloud Logging when you search for "export complete". I wait for that before I tell finance the day is closed.

If the property is already linked to Firebase, you cannot point Analytics at a second Cloud project. Export into the same project and join there.

## Production checklist for GA4 BigQuery export

A store is ready for GA4 BigQuery export when yesterday's `events_YYYYMMDD` table exists, a test `purchase` has a `transaction_id` and an items array, and that `transaction_id` matches one order in admin.

1. Confirm `purchase` fires once with a real `transaction_id` and items. DebugView first. The warehouse will copy whatever the tag already lies about.
2. Create or pick a Cloud project with billing, not only the sandbox, if the catalogue will grow past 10 GB. Sandbox exports stop when quota is full.
3. Enable the BigQuery API. Use an account that is Editor or above on the Analytics property and can set IAM on the Cloud project.
4. In Admin, Product links, BigQuery links, link the project. Pick a dataset region you can defend with legal. I use `australia-southeast1` unless the Ads join already lives elsewhere.
5. Export only the web stream that is the storefront. Drop a stale app stream or a staging stream here, not later in a WHERE clause you forget.
6. Exclude chatty events until a standard property is comfortably under 1 million events a day. Keep `purchase`, `refund`, `add_to_cart`, `begin_checkout` and the item list events you actually report.
7. Turn on Daily export. Add Streaming only if someone will query today before the daily table lands, and they accept missing new-user source fields.
8. Leave Fresh Daily to 360 properties that already pay for it. Do not promise 5am completeness on a standard link.
9. Wait up to 24 hours. Confirm `analytics_<property_id>.events_YYYYMMDD` exists for yesterday.
10. Run Google's date-range count for `page_view`, `session_start` and `purchase`. Then run an items unnest for yesterday's `purchase` rows and compare ten `transaction_id` values to admin.
11. If you use Google Ads, plan the [Ads transfer join](https://developers.google.com/analytics/bigquery/basic-queries) on `collected_traffic_source.gclid`. wBRAID and gBRAID are not in the export.
12. Write the closed-day rule in the runbook: query `events_YYYYMMDD`, wait for the completeness log on 360, and do not treat streaming as the finance number.

![Spreadsheet and coffee while reconciling revenue](../../images/blog/ga4-bigquery-export-catalogues-2.jpg)

Do not turn on every export type "just in case". You pay for storage three times and you will query the wrong table.

## What breaks when the export looks complete

An export that looks complete still breaks catalogue reporting when you query the wrong table, compare it to the UI, or blow the daily cap.

The first break is treating BigQuery as a clone of Ecommerce purchases. Google says the export excludes value-adds the interface applies. Modelled conversions, thresholding and some attribution views will not match a raw `COUNT(*)` of `purchase`. I reconcile to the order admin, not to the GA4 card. If those two disagree, the tag is wrong. If admin and BigQuery agree and the UI is higher, I look for modelling, not another pixel.

![Analytics graphs on a laptop in a dark office](../../images/blog/ga4-bigquery-export-catalogues-3.jpg)

The second break is `events_intraday`. It is tempting at 11am. It is incomplete, may miss late hits, and Google deletes it when the daily table is ready. I have watched a morning dashboard lose afternoon wallet checkouts because someone materialised the intraday table and never switched.

The third break is the 1 million event pause. Editors get email when a standard property is over. Ignore it and daily export stops. Streaming can still run, so the property looks alive while yesterday never lands. Filter streams and events. Infinite-scroll `view_item_list` is usually the leak.

Timezone changes break a day. The export window is the property timezone. Shift it mid-campaign and you get a short or long day. Google lists that as an export failure mode. Change timezone on a quiet Monday.

Region and IAM breaks look like empty datasets. An organisation policy that blocks the United States will fail a US location. A deleted robot account or a missing BigQuery User grant on the built-in resource identity means Analytics cannot write tables. Older links still use `firebase-measurement@system.gserviceaccount.com`. New links use a built-in identity. Check the identity on the link details page before you blame the tag.

Duplicates still explode `UNNEST(items)`. Two purchase tags mean two rows with the same `transaction_id`. Deduplicate in SQL with `transaction_id` before you sum quantity. Empty items arrays pass the event count and fail the SKU join. Parent configurable IDs on `view_item` and simple SKUs on `purchase` make BigQuery look like a different catalogue from Magento admin. That is a payload bug, not an export bug.

Late Measurement Protocol refunds can land in a daily table for two extra days. If you snapshot the table at 4pm the day after and never refresh, refunds look missing. I re-query D-2 before I call a week closed.

## How to measure after you turn on the export

You measure after you turn on the export by matching `transaction_id` and `item_id` to the order admin, not by celebrating that a dataset appeared.

Start with Google's own count of `purchase` events by `event_date`. Compare the count to distinct `transaction_id` values. If event count is double distinct IDs, you have duplicate tags. If distinct IDs exceed admin orders, you are counting failed payments or test hits. If admin exceeds BigQuery, the thank-you path is missing wallets, or consent denied `analytics_storage` and you only have cookieless pings with no items.

![Team looking at a dashboard on a wall screen](../../images/blog/ga4-bigquery-export-catalogues-4.jpg)

Then unnest items. Sum `quantity` by `item_id` for a known SKU that sold yesterday. Magento simples, Shopify variant SKUs and WooCommerce variation SKUs have to be the same string you send on the items array. This is where [item-scoped custom dimensions](https://alanvo.com/blog/ga4-item-scoped-custom-dimensions/) pay off: metal or fabric is already on the item, so you can cut the warehouse the same way merchandising cuts the storefront.

Google's [advanced queries](https://developers.google.com/analytics/bigquery/advanced-queries) show the catalogue question I actually get: other products purchased by people who bought a hero SKU. That is `UNNEST(items)` plus a buyer set. Average spend per purchase session is the other official sample I use when someone says AOV moved.

Keep a weekly ten-order sheet. Date, `transaction_id`, admin total, BigQuery `value`, item count, item IDs. Timezone first. Tax and shipping second. Currency third. I do not invent a conversion lift from turning the export on. When the storefront itself changed I quote published figures only: Their Nibs at 31 percent more conversions and 48 percent more orders, and Magento retail conversion work at $2.5 million year-on-year sales.

Watch Cloud billing. A streaming link plus wide `SELECT *` on `events_*` without `_TABLE_SUFFIX` is how a mid-market store gets a surprise invoice. Partition on the suffix. Select the columns you need.

## Related work on this site

Related work on this site is the catalogues where revenue and SKU identity already had to survive a messy stack. [Retail conversion at scale](https://alanvo.com/work/retail-conversion/) is Magento operations across three stores, where increment IDs and simples are the daily language. [Their Nibs](https://alanvo.com/work/their-nibs/) is Shopify sleepwear where the order admin and the pixel have to tell the same SKU story. [Tamannaah Fine Jewellery](https://alanvo.com/work/tamannaah-fine-jewellery/) is Shopify Plus jewellery, where metal only helps if `item_id` is stable enough to join. If the events are still wrong, start with [GA4 ecommerce events](https://alanvo.com/blog/ga4-ecommerce-events/) before you pay for another export type.

## GA4 BigQuery export FAQ

GA4 BigQuery export questions I get on catalogue work are about 360, the UI gap, intraday tables and the 1 million event cap.

### Do I need Analytics 360 for GA4 BigQuery export on a catalogue?

You do not need Analytics 360 for GA4 BigQuery export on a catalogue. Daily and streaming export work on a standard property. You do need 360 for Fresh Daily, for the 20 billion event daily ceiling, and for a documented completeness signal. A mid-market Magento or Shopify store usually starts on daily export and filters events to stay under 1 million.

![Phone and laptop showing the same store metrics](../../images/blog/ga4-bigquery-export-catalogues-5.jpg)

### Why does GA4 BigQuery export revenue not match the UI?

GA4 BigQuery export revenue does not match the UI because the warehouse is raw events and the interface can apply modelling, thresholds and presentation the export will not copy. Compare BigQuery `purchase` rows to the order admin. If those two agree, the UI gap is not a missing tag. If they disagree, fix the payload.

### Should I query events_intraday for GA4 BigQuery export purchases?

You should not query `events_intraday` for closed-day GA4 BigQuery export purchases. Google says that table is a staging file and is deleted when `events_YYYYMMDD` is complete. Use streaming only for same-day operations, and accept that new-user traffic source fields are missing there.

### How do I keep a standard property under the GA4 BigQuery export limit?

You keep a standard property under the GA4 BigQuery export limit by exporting one storefront stream and excluding events you will never join to an order. Google pauses daily export when a standard property stays over 1 million events, and it will not reprocess those days. Infinite-scroll `view_item_list` is the usual leak on a catalogue.



HTML version: https://alanvo.com/blog/ga4-bigquery-export-catalogues/