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.
Practice this in a free lesson on learn.querylab.io: Revenue by item.
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:
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
| Row | after_unnest | item_total | real_revenue |
|---|---|---|---|
| 1 | 230.0 | 130.0 | 130.0 |
Sum item_revenue after an unnest, and purchase_revenue without one.
Revenue by product
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
| Row | item_name | units | revenue |
|---|---|---|---|
| 1 | hat | 2 | 70.0 |
| 2 | 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 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, 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:
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
| Row | orders_left |
|---|---|
| 1 | 3 |
Keep those rows out of the deduplication:
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
| Row | event_timestamp | transaction_id | revenue |
|---|---|---|---|
| 1 | 100 | T1 | 100.0 |
| 2 | 200 | T2 | 30.0 |
| 3 | 300 | (not set) | 50.0 |
| 4 | 400 | (not set) | 20.0 |
| 5 | 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 covers what happens before the order, and GA4 attribution covers where the order came from.