# GA4 ecommerce data in BigQuery > Purchases in the GA4 export are events with an ecommerce record and a repeated items array. UNNEST(items) repeats event-level revenue on each item, and re-sent purchases need deduplication without losing orders with no transaction ID. Source: https://docs.querylab.io/bigquery/ga4-ecommerce/ Updated: 2026-11-04 In the GA4 export a purchase is an event with `event_name = 'purchase'`. Order-level values are in the `ecommerce` record: `transaction_id`, `purchase_revenue`, `tax_value`, `shipping_value`. Products are in the repeated `items` array, with `item_name`, `quantity` and `item_revenue` for each one. Pick the level before you write the query: sum `ecommerce.purchase_revenue` on the event, or `item_revenue` after `UNNEST(items)`. Mixing the two double-counts revenue. ## Don't sum order revenue after UNNEST `UNNEST(items)` makes one row per product, and the order's revenue is copied onto each. An order of two products is counted twice. Revenue is 130 here, and the sum says 230: **order revenue summed after UNNEST** ```sql WITH purchases AS ( SELECT 'T1' AS transaction_id, 100.0 AS purchase_revenue, [STRUCT('shoe' AS item_name, 60.0 AS item_revenue), ('hat', 40.0)] AS items UNION ALL SELECT 'T2', 30.0, [('hat', 30.0)] ) SELECT SUM(p.purchase_revenue) AS after_unnest, SUM(i.item_revenue) AS item_total, (SELECT SUM(purchase_revenue) FROM purchases) AS real_revenue FROM purchases AS p, UNNEST(p.items) AS i ``` Result in BigQuery (1 row): | after_unnest | item_total | real_revenue | | --- | --- | --- | | 230.0 | 130.0 | 130.0 | Sum `item_revenue` after an unnest, and `purchase_revenue` without one. ## Revenue by product **units and revenue per item** ```sql WITH purchases AS ( SELECT [STRUCT('shoe' AS item_name, 2 AS quantity, 60.0 AS item_revenue), ('hat', 1, 40.0)] AS items UNION ALL SELECT [('hat', 1, 30.0)] ) SELECT i.item_name, SUM(i.quantity) AS units, SUM(i.item_revenue) AS revenue FROM purchases AS p, UNNEST(p.items) AS i GROUP BY i.item_name ORDER BY revenue DESC ``` Result in BigQuery (2 rows): | item_name | units | revenue | | --- | --- | --- | | hat | 2 | 70.0 | | shoe | 2 | 60.0 | Item totals don't always equal order totals. In Google's sample they are close overall, 362,110 against 362,165, but 1,001 of 5,692 purchase events differ, so compare them on your own data before you put both in one report. An [`UNNEST`](https://docs.querylab.io/bigquery/unnest/) with a comma also drops events with an empty `items` array. ## Duplicate purchases The same purchase can be sent twice, for example when a thank-you page reloads. In Google's sample, 4,786 purchase events have a real transaction ID, and only 4,451 of those IDs are different. Keep the first event of each ID with [`QUALIFY`](https://docs.querylab.io/bigquery/qualify/), and deduplicate before you unnest, while one row is still one purchase. The sample also shows the trap in that rule: 883 purchases have the transaction ID `(not set)` and 23 have NULL. These can be different orders, and `PARTITION BY transaction_id` treats each group as one. Here 4 real orders become 3: **deduplicating on every ID** ```sql WITH purchases AS ( SELECT * FROM UNNEST([ STRUCT('T1' AS transaction_id, 100.0 AS revenue), ('T1', 100.0), ('(not set)', 50.0), ('(not set)', 20.0), (NULL, 10.0) ]) ), one_per_id AS ( SELECT revenue FROM purchases QUALIFY ROW_NUMBER() OVER (PARTITION BY transaction_id) = 1 ) SELECT COUNT(*) AS orders_left FROM one_per_id ``` Result in BigQuery (1 row): | orders_left | | --- | | 3 | Keep those rows out of the deduplication: **deduplicating real IDs only** ```sql WITH purchases AS ( SELECT * FROM UNNEST([ STRUCT(100 AS event_timestamp, 'T1' AS transaction_id, 100.0 AS revenue), (900, 'T1', 100.0), (200, 'T2', 30.0), (300, '(not set)', 50.0), (400, '(not set)', 20.0), (500, NULL, 10.0) ]) ) SELECT event_timestamp, transaction_id, revenue FROM purchases QUALIFY transaction_id IS NULL OR transaction_id = '(not set)' OR ROW_NUMBER() OVER (PARTITION BY transaction_id ORDER BY event_timestamp) = 1 ORDER BY event_timestamp ``` Result in BigQuery (5 rows): | event_timestamp | transaction_id | revenue | | --- | --- | --- | | 100 | T1 | 100.0 | | 200 | T2 | 30.0 | | 300 | (not set) | 50.0 | | 400 | (not set) | 20.0 | | 500 | NULL | 10.0 | Whether you trust a purchase with no ID is a business decision. Without an ID, a re-sent event can't be told from a second order. Refunds are separate events, with `ecommerce.refund_value`, so purchase revenue alone is gross revenue. `purchase_revenue` is in local currency and `purchase_revenue_in_usd` is in USD. Use the `_in_usd` fields if a property takes more than one currency. [The purchase funnel](https://docs.querylab.io/bigquery/ga4-funnels/) covers what happens before the order, and [GA4 attribution](https://docs.querylab.io/bigquery/ga4-attribution/) covers where the order came from. Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in. ## Sources Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery: - [BigQuery Export schema (Analytics Help)](https://support.google.com/analytics/answer/7029846) - [BigQuery sample dataset for Google Analytics ecommerce](https://developers.google.com/analytics/bigquery/web-ecommerce-demo-dataset)