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.

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.category

ROW_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.

All BigQuery lessons