Docs/Google Analytics 4/Ecommerce
Google Analytics 4

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:

order revenue summed after UNNESTOpen in Querylab.io
12345678910
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
Rowafter_unnestitem_totalreal_revenue
1230.0130.0130.0
1 row

Sum item_revenue after an unnest, and purchase_revenue without one.

Revenue by product

units and revenue per itemOpen in Querylab.io
12345678
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
Rowitem_nameunitsrevenue
1hat270.0
2shoe260.0
2 rows

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:

deduplicating on every IDOpen in Querylab.io
1234567891011
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
Roworders_left
13
1 row

Keep those rows out of the deduplication:

deduplicating real IDs onlyOpen in Querylab.io
1234567891011121314
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
Rowevent_timestamptransaction_idrevenue
1100T1100.0
2200T230.0
3300(not set)50.0
4400(not set)20.0
5500null10.0
5 rows

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.

Open the playground. It is the real app on public datasets and needs no sign-in.