# GA4 BigQuery ecommerce export for store revenue you can audit

Link GA4 to BigQuery, query purchase and items with SQL, and reconcile totals when the UI and warehouse disagree.

- Date: 2026-10-12
- Category: Analytics

## Table of contents

- Why GA4 BigQuery ecommerce export matters on live storefronts
- How GA4 BigQuery ecommerce export is structured
- Production checklist for GA4 BigQuery ecommerce export
- What breaks when you treat BigQuery like the GA4 home screen
- How to measure revenue and items after export
- Related work on this site
- FAQ
  - What is GA4 BigQuery ecommerce export used for on a store?
  - Does GA4 BigQuery ecommerce export match the Purchases card in the interface?
  - Should I enable streaming for GA4 BigQuery ecommerce export?
  - How do I query product rows in GA4 BigQuery ecommerce export?

GA4 BigQuery ecommerce export is how I stop arguing about whether purchase revenue in the interface is wrong or whether the tag is wrong. I am a Gold Coast web developer with 18 years on Magento, Shopify and WooCommerce catalogues. When a merchandiser asks why Tuesday's revenue in GA4 does not match the payment gateway, the UI is often sampling, scoped, or delayed. BigQuery holds the raw events Google Analytics received. That is the audit trail I want before I touch GTM again.

This note is for stores that already send [GA4 ecommerce events that match real revenue](https://alanvo.com/blog/ga4-ecommerce-events/). If `purchase` fires twice or `transaction_id` is missing, exporting to BigQuery only gives you a faster way to see the same mess. Fix the client or server tag first, then link the property.

## Why GA4 BigQuery ecommerce export matters on live storefronts

GA4 BigQuery ecommerce export matters on live storefronts because standard reports and Explorations are built for product managers, not for reconciling line items to ERP rows.

Google's [BigQuery Export help](https://support.google.com/analytics/answer/9358801) is explicit: the warehouse gets raw event and user-level data without the value additions the Analytics interface applies. Totals will not always match the Purchases card you screenshot in a stand-up. That is not a bug you can fix with a filter. It is a reason to query `purchase` yourself and join on `transaction_id`.

On a Magento catalogue with configurable SKUs, I need to know whether revenue drift comes from tax, shipping, partial refunds, or a wallet checkout that skipped the theme pixel. On Shopify, Shop Pay and the custom pixel can disagree with the theme dataLayer. On WooCommerce block checkout, the thank-you page is not always where the event fires. BigQuery lets me group by `event_name`, unnest `items`, and compare `(SELECT value FROM UNNEST(event_params) WHERE key = 'value')` on `purchase` to the same field on `refund`.

Standard GA4 properties also hit a practical ceiling: [BigQuery Export limits](https://support.google.com/analytics/answer/9358801) include one million events per day on Standard, with optional filtering to stay under the cap. A busy store with rich `view_item_list` payloads can burn that budget fast. Knowing the limit before you link saves a panicked Friday when exports stop.

Ownership matters for compliance and for handover. Google notes that when you export to BigQuery, you own the dataset and manage permissions with BigQuery ACLs. Once data is in your project, Analytics cannot re-export it for you. Plan retention and who on the client team gets `bigquery.dataViewer` the same way you plan GTM publish rights.

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

## How GA4 BigQuery ecommerce export is structured

GA4 BigQuery ecommerce export is structured as daily event tables plus optional intraday streaming tables, all keyed to the property time zone.

Linking is done in Admin under Product links, BigQuery links. You pick a Google Cloud project and dataset. Google's [overview guide](https://developers.google.com/analytics/bigquery/overview) describes the outcome: every raw event lands in BigQuery where you can run SQL.

Export modes from the [BigQuery Export types table](https://support.google.com/analytics/answer/9358801) are the decision you make at link time:

1. **Daily export** runs once per day for the previous calendar day in the property time zone. Timing is not guaranteed. Google says it typically lands mid-afternoon in that time zone but can slip to the next day. Standard properties get up to one million events per day. This is the default I recommend when the client only needs morning reconciliation, not minute-by-minute ops.

2. **Streaming export** writes `events_intraday_YYYYMMDD` continuously. Data for the current day shows up within minutes. Streaming is best-effort and may have gaps. It also excludes new-user traffic source fields (`traffic_source.name`, `source`, `medium`) until processing catches up. Google charges about $0.05 per gigabyte for streaming on top of normal BigQuery storage and query costs.

3. **Fresh daily export** is Analytics 360 only, with batched updates through the day and a faster path to complete daily tables. Most of my mid-market clients are on Standard, so I plan around daily plus optional streaming, not Fresh daily.

For each day with daily export enabled, Analytics creates `events_YYYYMMDD`. That is the stable table you should query. Streaming creates `events_intraday_YYYYMMDD`, which is deleted when the daily table for that date is complete. Google's [basic queries guide](https://developers.google.com/analytics/bigquery/basic-queries) shows the `_TABLE_SUFFIX` pattern:

```sql
SELECT
  event_date,
  event_name,
  COUNT(*) AS event_count
FROM `project.dataset.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20261001' AND '20261007'
  AND event_name IN ('view_item', 'add_to_cart', 'purchase', 'refund')
GROUP BY 1, 2;
```

Ecommerce parameters live in the repeated `items` record and in `event_params`. For item-level work you `UNNEST(items)` the way Google's samples do for add_to_cart rankings. Prescribed item fields (`item_id`, `item_name`, `price`, `quantity`, and the category slots) align with the [Measure ecommerce](https://developers.google.com/analytics/devguides/collection/ga4/ecommerce) reference. Custom item parameters you registered as item-scoped dimensions still appear in the export even when the UI lags.

Cookieless pings from Consent Mode and customer-provided data such as `user_id` are included in export, per Google's export notes. That helps when you need to compare consented versus cookieless volume without trusting a single Exploration segment.

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

Practice queries on the public [ga4_obfuscated_sample_ecommerce](https://developers.google.com/analytics/bigquery/web-ecommerce-demo-dataset) dataset before you touch a client project. The sandbox tier is enough to learn `UNNEST` and `_TABLE_SUFFIX` without billing panic.

## Production checklist for GA4 BigQuery ecommerce export

A store is ready for GA4 BigQuery ecommerce export when purchase events in DebugView already carry stable `transaction_id`, currency, and item IDs, and when someone on the client owns a GCP project with billing or sandbox limits understood.

1. Confirm GA4 Admin access and Google Cloud project access for the same Google account or a service account path the client accepts.
2. In GA4 Admin, open BigQuery links, choose Daily export at minimum. Add Streaming only if ops truly needs intraday alerts and the client accepts streaming charges.
3. Set the GA4 property time zone to the business calendar you reconcile against. Google warns that changing time zone after link can skip or skew daily tables.
4. If the property is Standard and traffic is heavy, review the one-million-events-per-day cap. Use Google's filtering options on the link if you must drop noisy events to stay under limit. Document what you filtered so nobody thinks cart adds vanished from reality.
5. Grant BigQuery roles: dataViewer for analysts, jobUser for scheduled queries, and tighter roles for engineers who edit views. Do not give Owner to a vendor mailbox.
6. Create a saved query that counts `purchase` events and sums `value` for yesterday, grouped by `transaction_id`, and schedule it after you know daily export timing for that property.
7. Build a second query that unnests `items` on `purchase` and compares `item_id` counts to the order admin for five known SKUs each week.
8. Add `refund` to the same audit pack if the store issues partial returns. Google's ecommerce guide treats refund as its own event with `transaction_id` and item quantities.
9. Document whether Measurement Protocol or server-side GTM also sends purchases. Duplicate `transaction_id` rows in BigQuery are easier to spot than duplicate tags in the UI.
10. Store the dataset location (EU versus US) to match client data residency expectations.

Link Firebase and GA4 to one BigQuery project only. Google states integrated Firebase and GA4 properties cannot link to separate BigQuery projects.

## What breaks when you treat BigQuery like the GA4 home screen

BigQuery breaks expectations when teams copy Exploration logic verbatim or query intraday tables as if they were final.

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

The first failure is comparing streaming tables to the Purchases report on the same morning. Intraday data is incomplete by design. User-attribution fields for new users are missing from streaming export. Google recommends daily export for attribution work. If marketing pulls campaign ROAS from `events_intraday_*`, they will chase ghosts.

The second failure is ignoring late events. Daily tables can receive updates for up to two calendar days after the table date for events timestamped on that date, plus occasional reprocessing. A scheduled query that runs at 6am property time may undercount yesterday until the afternoon export finishes. Wait for the pattern on that property before you wire Slack alerts.

The third failure is SKU vocabulary drift. `view_item` sends parent IDs on configurable Magento PDPs while `purchase` sends simple SKUs. BigQuery will show the mismatch clearly. The fix is tagging, not SQL. Same for Shopify variant IDs versus feed IDs.

The fourth failure is event volume filtering on Standard. When exports silently stop growing because you exceeded one million events, the UI still looks fine. Monitor row counts or use Cloud Logging completeness signals on 360. On Standard, track table size trends weekly.

The fifth failure is joining Ads without a plan. Google's basic queries doc notes you can join `collected_traffic_source.gclid` from Analytics events to Google Ads transfer tables, but Analytics creates one table per day while Ads transfer uses different shapes. Build the join in a view and test row explosion before you trust it in Looker Studio.

Duplicates still hurt. Theme plus app plus GTM produce two `purchase` rows with the same `transaction_id` but different `value`. In BigQuery, `COUNT(DISTINCT transaction_id)` and `SUM(value)` diverge. Fix deduplication at the tag layer, then add a SQL guard that flags duplicate IDs.

## How to measure revenue and items after export

You measure after export by scheduling two reconciliations: event totals versus gateway totals, and item unnest totals versus a SKU sample from the order admin.

Start with a daily query on `events_YYYYMMDD` for `event_name = 'purchase'`. Extract `value` and `currency` from `event_params`. Compare the sum to the payment gateway for the same calendar date in the property time zone. A five percent gap might be refunds recorded next day. A fifty percent gap is almost always double tagging or missing wallet checkout.

Add a funnel query: distinct `user_pseudo_id` counts for `view_item`, `add_to_cart`, `begin_checkout`, and `purchase` over seven days. BigQuery will not match funnel exploration exactly because explorations apply UI rules, but the shape should be sane. If `add_to_cart` exceeds `view_item`, you are firing cart events on mini-cart widgets without list context.

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

For merchandising, unnest `items` on `purchase` and group by `item_id`, summing `quantity * price` where present. Compare to [GA4 item-scoped custom dimensions](https://alanvo.com/blog/ga4-item-scoped-custom-dimensions/) you registered. If custom parameters appear in BigQuery but not in the Items report, registration lag is the likely cause, not export failure.

For ops, chart row counts by `event_name` per day. A sudden drop in `purchase` with steady sessions is a deploy or consent regression. A spike in `view_item_list` with flat purchases might be an infinite-scroll tag firing hundreds of items per page.

When you outgrow ad hoc SQL, pipe aggregated tables to Looker Studio or Sheets, but keep the raw `events_*` tables as source of truth. Google's [export schemas](https://developers.google.com/analytics/bigquery/schemas) change slowly; pin the doc version in your runbook.

## Related work on this site

BigQuery export paid off most on stores where catalogue complexity already justified strict event design. On [retail conversion at scale](https://alanvo.com/work/retail-conversion/) Magento work, row-level audits helped settle feed versus site revenue arguments without another week of guesswork in Explorations. On [Their Nibs](https://alanvo.com/work/their-nibs/) sleepwear, item-level questions map cleanly to fabric and fit when those fields ride on the `items` array. On [Offporter](https://alanvo.com/work/offporter/) automotive accessories, campaign and SKU questions often need a join path that starts in the warehouse, not in a default report. Pair this note with [server-side vs client-side analytics for stores](https://alanvo.com/blog/server-side-vs-client-analytics/) when Measurement Protocol or sGTM also writes purchases.

## FAQ

### What is GA4 BigQuery ecommerce export used for on a store?

GA4 BigQuery ecommerce export is used to query raw `purchase`, `refund`, and `items` events with SQL so you can reconcile revenue, find duplicate `transaction_id` rows, and audit SKU-level quantity without relying on sampled UI reports.

### Does GA4 BigQuery ecommerce export match the Purchases card in the interface?

GA4 BigQuery ecommerce export often does not match the Purchases card exactly because the interface adds processing and reporting rules that the export omits. Google documents the gap in its BigQuery Export help. Use BigQuery for audit trails and the UI for directional trends unless you rebuild UI logic in SQL.

### Should I enable streaming for GA4 BigQuery ecommerce export?

Enable streaming for GA4 BigQuery ecommerce export only when you need near-real-time event counts and accept extra BigQuery streaming cost plus incomplete new-user attribution fields. For daily finance reconciliation on Standard properties, daily export alone is usually enough.

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

### How do I query product rows in GA4 BigQuery ecommerce export?

Query product rows by filtering `event_name` to ecommerce events, using `_TABLE_SUFFIX` for dates, and applying `UNNEST(items)` on `purchase` and `add_to_cart` events. Google's basic queries page includes add_to_cart examples you can adapt to your dataset name.



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