Revenue by item
Revenue reports get checked against finance numbers, so double counting is costly. GA4 purchase data has two sources of it, and this lesson handles both.
How it works
First remove duplicate purchase events at transaction grain. Then UNNEST the items of each remaining purchase to item grain, and sum item_revenue by the item dimension you want.
QUALIFY ROW_NUMBER() OVER (PARTITION BY transaction_id ORDER BY event_timestamp) = 1keeps one purchase event per transaction.UNNEST(items)returns one row per item in each remaining transaction.item_revenuebelongs to an item.purchase_revenuebelongs to a transaction, so do not repeat it across items.
Common mistake
If you deduplicate after UNNEST, you can discard real items. If you skip deduplication, purchases that were sent twice count twice.
Worked example
WITH purchases AS (
SELECT 'order-7' AS transaction_id, 10 AS event_timestamp,
[STRUCT('Books' AS category, 12.0 AS revenue), STRUCT('Games' AS category, 25.0 AS revenue)] AS items UNION ALL
SELECT 'order-7', 20, [STRUCT('Books' AS category, 12.0 AS revenue), STRUCT('Games' AS category, 25.0 AS revenue)]
), one_per_transaction AS (
SELECT * FROM purchases
QUALIFY ROW_NUMBER() OVER (PARTITION BY transaction_id ORDER BY event_timestamp) = 1
)
SELECT item.category, SUM(item.revenue) AS item_revenue
FROM one_per_transaction, UNNEST(items) AS item
GROUP BY item.category
ORDER BY item.categoryROW_NUMBER removes the re-sent order-7 purchase while each row is still one transaction.
UNNEST then keeps both real items. Books total 12 and Games total 25.