Alan Vo

Available for remote roles

Menu
  1. Home
  2. Blog
  3. Analytics
  4. GA4 BigQuery export for production store catalogues

Analytics

GA4 BigQuery export for production store catalogues

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 and on 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 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 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

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 and the export 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 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 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

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

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

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 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 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 is Magento operations across three stores, where increment IDs and simples are the daily language. Their Nibs is Shopify sleepwear where the order admin and the pixel have to tell the same SKU story. 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 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

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.

Keep reading

Contact if you want this kind of work on a live store.