Building product-level reports from BigQuery ecommerce events

Building product-level reports from BigQuery ecommerce events

You can build reliable product-level ecommerce reports from GA4 event exports in BigQuery using a small set of repeatable SQL patterns. This guide supplies:

• The UNNEST pattern that turns nested item arrays into usable rows
• _TABLE_SUFFIX filtering to control which tables get scanned (and what you pay)
• An item performance query, a customers-who-bought-X query, and an average-spend-per-session query
• Cost and deduplication tips that stop you double-counting revenue
• A pointer to a free sample dataset so you can test everything before touching production

Pro Tip: Run every example against bigquery-public-data.ga4_obfuscated_sample_ecommerce first, and always restrict _TABLE_SUFFIX to a narrow range. It costs nothing and stops a badly written query from scanning months of production data by accident.

Key Takeaways

Reliable product-level ecommerce reporting from GA4 in BigQuery depends on separating transaction-grain revenue from unnested item-grain detail before aggregating anything.

Point Details Respect the two-layer schema Keep transaction fields in the ecommerce RECORD separate from unnested item fields to avoid confusion. Avoid double-counting revenue Aggregate purchase_revenue at transaction grain, or dedupe by transaction_id, before summing across unnested rows. Control cost with *TABLE_SUFFIX Restrict date-sharded table scans to the range you need rather than querying the full events** wildcard. Build a two-table reporting layer Pair a transaction summary table with an unnested item mart, joined on transaction_id. Test on the sample dataset first Validate every query against bigquery-public-data.ga4_obfuscated_sample_ecommerce before running it on production data.

Getting item-level reporting right in BigQuery only tells you what happened on-site. Closing the loop on why products get returned, and stopping those returns before they happen, is where Garmcheck’s virtual try-on and size recommendation tools come in. Once your item mart shows which SKUs carry high return rates, pairing that data with accurate sizing at the point of purchase is one of the more direct ways to move the revenue number your BigQuery reports are already tracking. See how it fits your Shopify stack on the Garmcheck product page .

Table of Contents

• How does the BigQuery ecommerce events schema work?
• Core SQL patterns for querying GA4 ecommerce events
• What ecommerce reports can you build from GA4 events?
• How do you control BigQuery costs on large GA4 datasets?
• How do you design a product reporting layer from GA4 events?
• Annotated example queries you can run today
• Should you join GA4 events with Shopify or ERP data?
• Where can you safely test GA4 ecommerce queries?
• What actually matters once you’re past the schema basics
• Frequently asked questions
• Sources

How does the BigQuery ecommerce events schema work?

GA4’s export lands in BigQuery as a two-layer structure, and understanding that layout is the difference between a report that’s right and one that’s silently wrong. At the top sits the ecommerce RECORD, which holds transaction-level fields: transaction_id , purchase_revenue , shipping_value , tax_value , total_item_quantity . Nested inside every event row sits items , a REPEATED RECORD containing one entry per product touched during that event, with fields like item_id , item_name , price , quantity , and item_revenue .

The pitfall almost everyone hits early: UNNEST(items) expands a single purchase event into multiple rows, one per product. Sum purchase_revenue across those unnested rows and you’ll multiply transaction revenue by however many products were in the basket. The fix is to aggregate ecommerce.purchase_revenue at transaction grain first, or deduplicate by transaction_id before summing anything from the ecommerce RECORD.

Field Lives in Populated on transaction_id ecommerce RECORD purchase, refund purchase_revenue ecommerce RECORD purchase item_id, item_name items REPEATED RECORD add_to_cart, begin_checkout, purchase item_revenue items REPEATED RECORD purchase, refund

Core SQL patterns for querying GA4 ecommerce events

A handful of idioms cover most of what you’ll write against this schema. Learn these once and every subsequent query becomes a variation on a theme.

• UNNEST(items) : use the comma-style implicit cross join ( FROM events, UNNEST(items) AS item ) for readability in simple queries; switch to explicit CROSS JOIN UNNEST(items) when you’re joining multiple nested arrays or want clearer precedence in longer queries.
• event_params access : parameters sit in a repeated key/value structure, so pull values with a scalar subquery: (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') . Always match the right value column ( int_value , string_value , double_value ) to the parameter type.
• _TABLE_SUFFIX : filtering with a WHERE _TABLE_SUFFIX clause restricting to specific date ranges against events_* restricts which date-sharded tables get scanned , which is the single biggest lever you have over query cost.
• SAFE_DIVIDE : wrap every ratio calculation (conversion rates, cart-to-detail, revenue-per-session) in SAFE_DIVIDE(numerator, denominator) so a zero denominator returns null instead of crashing the query.
• Deduplication : when joining unnested items back to transaction totals, dedupe on transaction_id so refunds or replayed events don’t inflate counts.

Pro Tip: Replace hard-coded dates with a DECLARE variable at the top of the script, and save intermediate CTEs as scheduled views. Re-scanning the same events_* wildcard every morning for a dashboard that refreshes hourly is one of the fastest ways to burn through a monthly bytes-billed quota.

Roughly a third of the queries analysts write against this export are variations on “revenue by X, filtered by date range.” Getting the _TABLE_SUFFIX and UNNEST pattern right once means copying and adapting it for almost everything else.

What ecommerce reports can you build from GA4 events?

Most requests from marketing or merchandising teams boil down to five report types. Each has a distinct grain, and picking the wrong one is the most common reason a report looks “off” compared with the GA4 interface.

• Item/product performance : unnest items, carry transaction_id through, and compute item revenue, unique purchases, and efficiency ratios like cart-to-detail and buy-to-detail rate.
• Funnel and session counts : count distinct sessions hitting begin_checkout versus purchase , joined on session ID, to measure checkout drop-off.
• Unique purchasers : COUNT(DISTINCT transaction_id) from the ecommerce RECORD, never from unnested item rows, or you’ll overcount.
• Cross-sell / customers-who-bought-X : build a CTE of purchasers of a target item, then ARRAY_AGG or join their other purchased items.
• Cohort and segment cuts : filter by first-purchase date or acquisition channel, then join onto the item mart for behaviour-by-cohort views.

A working rule of thumb: transaction-grain summaries answer “how much did we make and from how many orders”, while item-grain (unnested) tables answer “which products drove it.” Most dashboards need both, joined on transaction_id , rather than one giant flattened table trying to do both jobs at once.

How do you control BigQuery costs on large GA4 datasets?

Cost control isn’t an afterthought here. It decides whether your team can run ad-hoc queries freely or has to ration them.

• Always filter with _TABLE_SUFFIX (or use a date-partitioned export where your project has one) rather than scanning the full events_* wildcard.
• Point dashboards at a saved table or view instead of re-querying raw events every refresh.
• Use APPROX_COUNT_DISTINCT for high-cardinality counts like unique users where near-exact is good enough.
• Cluster and partition any large derived table you build on event_date and a common filter column like event_name .

Pro Tip: Before running anything on production data, use BigQuery’s dry-run option in the query editor. It shows bytes billed before you spend a penny, which is invaluable when a colleague asks “can you just quickly check…” on a multi-terabyte table.

How do you design a product reporting layer from GA4 events?

The pattern that scales best pairs a session-grain purchase summary with an unnested item table , rather than one sprawling flattened view trying to serve every use case.

• Stage raw events : filter to relevant event_name values ( purchase , refund , add_to_cart , begin_checkout ) into a lightweight staging view.
• Build the transaction summary : one row per transaction_id , pulling revenue, tax, and shipping straight from the ecommerce RECORD.
• Build the item mart : UNNEST(items) into its own table, one row per item per event, keyed by transaction_id and item_id .
• Schedule incremental loads : append new dates only, using _TABLE_SUFFIX windows, with deduplication logic that keeps the flow idempotent.

Pro Tip: Key every load on transaction_id plus event_timestamp , and write loads as idempotent merges rather than blind appends. GA4 exports can replay or backfill data, and without a stable key you’ll get duplicate transactions creeping into revenue totals within weeks.

Annotated example queries you can run today

These three cover most requests analysts get asked for. Swap bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_* for your own project.dataset.events_* once you’ve confirmed results on the sample.

• Item performance : build a CTE that unnests items and carries transaction_id from the parent ecommerce RECORD. Aggregate to get unique_purchases ( COUNT(DISTINCT transaction_id) where event_name = 'purchase' ), item_revenue , and avg_purchase_price . Compute cart-to-detail as views-to-cart-adds and buy-to-detail as cart-adds-to-purchases, both wrapped in SAFE_DIVIDE . This matches the structure recommended for product performance reporting .
• Customers who bought X : first CTE isolates purchasers of the target item_id . Second CTE unnests all purchase events for those same users and aggregates other items bought, using ARRAY_AGG(DISTINCT item_name) to keep results readable. Google’s own advanced query examples use this CTE-plus-ARRAY_AGG pattern rather than repeated self-joins, which keeps bytes billed lower on large datasets.
• Average spend per purchase session : extract ga_session_id from event_params with a scalar subquery, then aggregate purchase_revenue per session before averaging. Note that ga_session_id can be missing on some hits, particularly cross-device or consent-restricted traffic, so filter nulls out explicitly rather than letting them skew the average.

• Set a narrow _TABLE_SUFFIX window before running any of these against real data.
• Dry-run first to check bytes billed, especially on the customers-who-bought-X query, which scans wider than the other two.

Pro Tip: Test the item performance query against a single, well-known product first. If the item revenue figure doesn’t match what you can see in Shopify’s own order export, you’ve likely got a double-counting bug in the aggregation, not a data quality problem.

Should you join GA4 events with Shopify or ERP data?

GA4’s export is an event stream, not a ledger. It’s excellent for behaviour, weaker on canonical order truth, refunds processed after the fact, and SKU master data. Joining it to Shopify or your ERP gives you accurate revenue, real refund status, and product data GA4 never sees. Garmcheck’s own guidance on server-side tracking for Shopify covers a related piece of this puzzle: getting events into BigQuery reliably before you even get to the join.

• Join on transaction_id /order ID, but confirm both systems format it identically. Shopify order numbers and GA4 transaction IDs sometimes carry different prefixes.
• Normalise currency and timestamps before joining. A five-minute clock offset between systems can shift a transaction across a daily partition boundary.
• Reconcile totals weekly, not just at go-live. Schema drift and duplicate orders creep in quietly over months.

Pro Tip: A managed ETL or CDC tool handles schema drift and deduplication far more reliably than a hand-rolled script that nobody remembers to update when Shopify changes a field name.

Where can you safely test GA4 ecommerce queries?

The ga4_obfuscated_sample_ecommerce dataset gives you three months of obfuscated real-shape data (1 November 2020 to 31 January 2021), free to query and safe to break.

• Clone the example queries above and run them unmodified against the sample dataset first.
• Confirm the output shape and numbers look sensible before touching your own project.
• Apply the same query to a single day of your own data using a tight _TABLE_SUFFIX window.
• Once verified, promote the query into a saved view or scheduled table rather than leaving it as an ad-hoc script.

• Dry-run every query to estimate bytes before it runs for real.
• Stay on the free tier or a sandbox project for initial development.
• Never write test output to a production-facing table, even “just to check.”

What actually matters once you’re past the schema basics

Most of the pain in this work isn’t the SQL, it’s deciding what to build first. Start with the transaction summary and item mart before anything fancier. Attribution models and cohort joins are worthless if the underlying revenue numbers are quietly double-counted from an unnested query written in week one. Instrument missing IDs, particularly ga_session_id gaps, before you trust any session-grain metric. Only once that foundation holds does joining to Shopify or ERP data become worth the engineering time.

Next steps, in order: test against the sample dataset, build the item mart, schedule incremental loads with deduplication, then reconcile totals against your transactional system weekly.

Frequently asked questions

What is the ga4_obfuscated_sample_ecommerce dataset used for? It’s a public BigQuery dataset containing three months of obfuscated GA4 export data, useful for testing SQL patterns and checking query costs before running anything against a live GA4 property.

Why does summing item revenue give me a different number than the GA4 UI? Unnesting items creates one row per product per event. Summing revenue directly off those rows without deduplicating by transaction_id multiplies transaction totals, which is the most common cause of mismatched numbers when analysing BigQuery ecommerce events.

How do I limit which tables BigQuery scans in a GA4 export? Filter with _TABLE_SUFFIX in the WHERE clause against the events_* wildcard tables, restricting it to the date range you actually need rather than scanning every day ever exported.

Can I build a customers-who-bought-X report directly in BigQuery? Yes. Build a CTE of purchasers of the target item, then unnest their other purchase events and aggregate with ARRAY_AGG , following the pattern in Google’s own advanced query documentation.

Should I join GA4 event data to my Shopify order data? For accurate revenue, refund status, and SKU-level detail, yes. GA4’s export is strong on behavioural detail but weaker as a canonical source of order truth, so joining on transaction ID against Shopify or ERP data closes that gap.

Sources

• Developers
• GA4 e-commerce schema in BigQuery (Adrienne Vermorel notes)
• Build a GA4 item and product performance report in BigQuery (Datalad)
• How to use BigQuery wildcard tables to query multiple date-sharded tables (oneuptime blog)

Recommended

• Virtual Try-On for Fashion Retailers | GarmCheck
• Server-side tracking for Shopify: a UK fashion brand guide — GarmCheck
• Top virtual try-on tools for UK ecommerce in 2026 — GarmCheck
• Plus size try-on for Shopify merchants: a practical guide — GarmCheck