Analytics & Tracking

GA4 BigQuery Export for eCommerce: When You Need It and Your First Queries

When an online store needs the GA4 BigQuery export, what it costs, the limits on standard properties, and the first three SQL queries to run on it.

TL;DR
  • The GA4 BigQuery export copies raw, event-level data into your own Google Cloud project. Use it when you need full history, unsampled answers, or to join web behavior with orders, costs and margin.
  • Standard properties get a daily export capped at 1 million events per day, plus a streaming option with no event limit that costs extra. The free BigQuery sandbox works for testing, but its tables expire after 60 days.
  • Start with three queries: purchase reconciliation, revenue by source and item revenue. Then join your order margin data so you report contribution, not just revenue.

What is the GA4 BigQuery export, and what does it give you that GA4 reports do not?

The BigQuery export sends every event that Google Analytics 4 collects into BigQuery, Google’s cloud data warehouse. Each row is one event: a page view, an add to cart, a purchase. Event details sit in nested fields you query with SQL.

That is very different from the GA4 interface. Reports in GA4 are aggregated, can hit thresholds, and explorations only reach back as far as your data retention setting allows. In the export, you own a copy of the raw data. You can keep it as long as you pay for storage, and you can join it with anything else you hold.

One timing point matters more than any other: the native export starts on the day you link it. It does not backfill raw events from before that date. If you think you might need it next year, link it this month.

When does an online store actually need it?

Most brands under a few million in revenue can run on GA4 reports, Shopify data and a Looker Studio dashboard. The export earns its place when you hit one of these walls.

Situation Can GA4 reports handle it? Why the export helps
You need more than 14 months of event history No, explorations stop at the retention limit You keep raw history as long as you store it
You want profit by channel, not revenue by channel No, GA4 has no COGS or fees Join purchases to your order and cost tables
Reports show thresholding or “(other)” rows Partly Raw rows are not aggregated or thresholded
You need custom attribution or path analysis Limited You can build your own session and path logic
You want to audit tracking (duplicate purchases, missing IDs) Hard Every purchase event is visible with its transaction ID
You want a simple weekly sales view Yes Not needed

If none of the first five rows apply, wait. If two or more do, set it up now so history starts accumulating.

What are the limits and costs today?

Google’s help center sets out these export options:

Export type Standard GA4 property Analytics 360 Notes
Daily export Up to 1 million events per day Up to 20 billion events per day The stable, complete dataset to query
Streaming export No volume limit No volume limit Best effort, may contain gaps, billed at $0.05 per GB
Fresh daily Not available Available for most 360 properties Faster daily tables for 360

Google estimates 1 GB of streamed data at roughly 600,000 events, though it varies with event size. If a standard property consistently exceeds the 1 million daily limit, Google pauses the daily export, and missed days are not reprocessed. You can stay under the limit by excluding events you never analyze, such as scroll or noisy custom events, in the link settings.

For a sense of scale (illustrative numbers): a store with 30,000 sessions a day and around 20 events per session sends about 600,000 events daily. That fits the standard limit. Double the traffic and you need to trim events or add streaming.

The BigQuery sandbox

You can export into the BigQuery sandbox without a billing account. According to Google Cloud’s documentation, the sandbox gives you 10 GiB of lifetime storage and 1 TiB of query processing per month. Tables, views and partitions expire after 60 days, and the sandbox does not support streaming or DML statements. Exports beyond the sandbox limits incur charges.

The 60-day expiry is the trap. A sandbox is fine for learning the schema. It is not an archive. Once you decide to keep the data, enable billing and remove the default table expiration.

What you actually pay for

With billing on, you pay for storage and for the bytes each query scans. Most stores in the $1M to $50M range spend little on storage. Query costs grow when someone runs SELECT * across every daily table. Two habits keep costs low: always filter on _TABLE_SUFFIX to limit dates, and select only the columns you need.

How do you set it up without creating problems later?

The setup has three parts: a Google Cloud project with the BigQuery API enabled, a link in GA4 under Admin, Product links, BigQuery links, and your choice of data streams, events and export types.

  • [ ] Create a dedicated Google Cloud project for analytics, owned by a company account, not a freelancer’s
  • [ ] Enable the BigQuery API and add billing (or start in the sandbox and plan the switch)
  • [ ] Pick the data location carefully: you cannot change it on an existing dataset without relinking and risking a gap
  • [ ] Select your web data stream and exclude events you never use
  • [ ] Turn on daily export; add streaming only if you need same-day data
  • [ ] Remove default table expiration once billing is on
  • [ ] Check the next day that an events_YYYYMMDD table appeared
  • [ ] Document who owns the project and who can query it

Your ecommerce tracking must be solid first. The export copies what GA4 collects, including mistakes. A purchase event without transaction_id or with the wrong value is just as wrong in BigQuery.

What does the exported data look like?

You get one dataset per property, named analytics_ plus your property ID. Inside it:

  • events_YYYYMMDD: the full daily export. Query these tables for reporting.
  • events_intraday_YYYYMMDD: a staging table for streaming data, deleted when the daily table is complete.

The fields eCommerce teams use most:

  • event_name and event_date
  • user_pseudo_id, the GA4 device identifier
  • event_params, an array of key and value pairs you unnest to read things like page_location
  • ecommerce, a record with transaction_id and purchase_revenue
  • items, an array of the products in each ecommerce event

Traffic source data comes in several forms. traffic_source holds the user’s first acquisition source. collected_traffic_source holds raw campaign values on each event. session_traffic_source_last_click, added in 2024, holds session-level source data using last non-direct attribution, which is the closest match to GA4’s session reports. Older tables will not have it.

What are your first three queries?

Replace your-project.analytics_123456789 with your own project and dataset. Each query covers October 2026.

Query 1: Daily orders and revenue, with duplicate check

This is your reconciliation query. Compare its output with Shopify or WooCommerce orders for the same days.

WITH purchases AS (
  SELECT
    ecommerce.transaction_id AS order_id,
    MIN(PARSE_DATE('%Y%m%d', event_date)) AS day,
    MAX(ecommerce.purchase_revenue) AS revenue,
    COUNT(*) AS purchase_events
  FROM `your-project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20261001' AND '20261031'
    AND event_name = 'purchase'
  GROUP BY order_id
)
SELECT
  day,
  COUNT(*) AS orders,
  SUM(revenue) AS revenue,
  COUNTIF(purchase_events > 1) AS duplicated_orders
FROM purchases
GROUP BY day
ORDER BY day

If duplicated_orders is above zero, your purchase event fires more than once for some orders. A null order_id row means purchases are arriving without a transaction ID. Both are tracking bugs to fix at the source. Our guide to the GA4 and Shopify revenue gap covers the usual causes.

Query 2: Orders and revenue by session source

SELECT
  session_traffic_source_last_click.cross_channel_campaign.source AS source,
  session_traffic_source_last_click.cross_channel_campaign.medium AS medium,
  COUNT(DISTINCT ecommerce.transaction_id) AS orders,
  SUM(ecommerce.purchase_revenue) AS revenue
FROM `your-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20261001' AND '20261031'
  AND event_name = 'purchase'
GROUP BY source, medium
ORDER BY revenue DESC

Totals here will sit close to, but rarely exactly on, GA4’s traffic acquisition report. Small gaps come from late hits, consent and processing differences.

Query 3: Units and revenue by product

SELECT
  item.item_id,
  item.item_name,
  SUM(item.quantity) AS units,
  SUM(item.item_revenue) AS item_revenue
FROM `your-project.analytics_123456789.events_*`,
  UNNEST(items) AS item
WHERE _TABLE_SUFFIX BETWEEN '20261001' AND '20261031'
  AND event_name = 'purchase'
GROUP BY item.item_id, item.item_name
ORDER BY item_revenue DESC

Swap purchase for view_item or add_to_cart to see which products get attention but do not sell.

How do you turn revenue queries into profit queries?

This is where the export pays for itself. GA4 knows the path. Your store knows the margin. Join them on transaction ID.

Load a daily orders table into the same BigQuery project from Shopify, WooCommerce or your ERP, with one row per order: order ID, net sales, COGS, shipping cost, payment fees and discounts. Then join it to the purchase events from Query 2 and group by source. Use the store’s order table as the source of truth for money, since GA4 will always miss some orders (blocked scripts, declined consent). Use GA4 only to assign each order a source.

Worked example (illustrative numbers)

Source / medium Orders Net sales COGS + shipping + fees Contribution margin before ads Margin %
google / cpc 1,200 $96,000 $57,600 $38,400 40%
facebook / paid 900 $63,000 $41,600 $21,400 34%
klaviyo / email 700 $49,000 $26,500 $22,500 46%

Revenue ranking puts paid social second. Margin ranking puts it last, because those orders lean on discounted bundles. Subtract ad spend from each row and the picture sharpens further. That is the number to bring to budget meetings, and it can feed cohort analysis by first-order source next.

FAQ

Is the GA4 BigQuery export free?

The link itself has no fee for standard properties. You pay BigQuery for storage and queries once you leave the sandbox, and streaming export adds a per-gigabyte charge. For most mid-size stores, costs stay modest if queries filter by date and select only the columns they need.

Can I get historical GA4 data into BigQuery?

Not as raw events. The native export starts on the day you link it. Some third-party connectors and Google’s own transfer tools can pull aggregated report data for earlier dates, but that is summary data, not event-level history. This is why linking early matters.

Do I need a data engineer to use it?

Not to start. Someone comfortable with basic SQL can run the queries above and connect results to Looker Studio. You do need technical help when you build recurring order and cost pipelines, scheduled queries and modeled tables that others rely on.

Turn this into a plan for your store

The Free Profit Audit scans your store, sizes your biggest profit leaks and builds a 90-day plan.

Free Profit Audit