1. Home
2. Blog
3. Analytics
4. GA4 BigQuery ecommerce export for store revenue you can audit

Analytics

GA4 BigQuery ecommerce export for store revenue you can audit

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

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 describes the outcome: every raw event lands in BigQuery where you can run SQL.

Export modes from the BigQuery Export types table 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.

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

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

Practice queries on the public ga4_obfuscated_sample_ecommerce 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.

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.

For merchandising, unnest items on purchase and group by item_id, summing quantity * price where present. Compare to 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 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 Magento work, row-level audits helped settle feed versus site revenue arguments without another week of guesswork in Explorations. On Their Nibs sleepwear, item-level questions map cleanly to fabric and fit when those fields ride on the items array. On 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 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.

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.

Keep reading

• GA4 ecommerce events that match real revenue
• Server-side vs client-side analytics for stores
• Retail Conversion case study
• Their Nibs case study
• Offporter case study
• More Analytics notes

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