Build a GA4 Item and Product Performance Report in BigQuery

GA4’s item reporting tells you how products move through the funnel, but the moment you want it defined your way, on the grain you choose, feeding your own dashboard, the interface runs out of room. The raw export does not. This code-along builds a custom item and product performance report straight from the export, with every metric calculated transparently. As with any export query, it will not match a GA4 screen exactly, because the documentation, the interface, and the raw data each define things slightly differently, and that is the value. You own the definitions, and the result drops cleanly into a spreadsheet, a saved table, or Looker Studio.

There is one important prerequisite to flag before anything else. The item-scoped parameters used for item-level custom dimensions were only added to the BigQuery export in late October 2023, so those particular fields will be empty for any date range before that. Everything else works across the full history of your export.

What the report contains

The dimensions identify the product: the date, the item id, name, brand, and variant, the five item category levels, and the item-scoped custom dimensions for both string and integer values. The metrics fall into two groups. First, the funnel event counts at item level: view promotion, view item, view item list, select item, add to wishlist, add to cart, remove from cart, view cart, begin checkout, add payment info, add shipping info, purchase, and refund. Second, the commercial outcomes: unique purchases, average purchase price, average purchase quantity, item quantity, item revenue, and item refund, finished off with two efficiency ratios, the cart-to-detail rate and the buy-to-detail rate.

The key idea that makes this report different from the others in the series is scope. Items live in a nested array on each event, so a single event can carry many products. To analyse products we have to unnest that array, which turns one event into one row per item it contained. After that, everything is a matter of counting the right rows.

Step one: unnest the items

We read the export and expand the items array, so each output row represents a single item as it appeared in a single event, carrying that item’s attributes alongside the event name and the transaction id.

WITH item_events AS (
  SELECT
    PARSE_DATE('%Y%m%d', event_date) AS event_dt,
    event_name,
    ecommerce.transaction_id AS transaction_id,
    item.item_id,
    item.item_name,
    item.item_brand,
    item.item_variant,
    item.item_category,
    item.item_category2,
    item.item_category3,
    item.item_category4,
    item.item_category5,
    item.quantity      AS quantity,
    item.item_revenue  AS item_revenue,
    (SELECT ip.value.string_value FROM UNNEST(item.item_params) ip WHERE ip.key = 'your_item_string_dimension') AS item_cd_string,
    (SELECT ip.value.int_value    FROM UNNEST(item.item_params) ip WHERE ip.key = 'your_item_int_dimension')    AS item_cd_int
  FROM `your-project.analytics_XXXXXX.events_*`,
  UNNEST(items) AS item
  WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
)

The comma followed by UNNEST(items) is the cross join that expands the array, so each item within each event becomes its own row, with item referring to the current product. The item attributes, quantity, and revenue are plain fields on that record, while the item-scoped custom dimensions are pulled from the item’s own parameter array, exactly mirroring how event parameters work but one level deeper. Replace the two placeholder keys with your real item custom dimensions. The transaction id comes from the ecommerce record, which we will need to count unique purchases, and the table suffix filter sets the window and contains the scan.

Step two: the report

With the items flattened, the whole report is a single aggregation grouped by the product dimensions. The funnel metrics are just counts of rows by event name, and the commercial metrics sum the quantity and revenue from the relevant events.

SELECT
  event_dt AS date,
  item_id, item_name, item_brand, item_variant,
  item_category, item_category2, item_category3, item_category4, item_category5,
  item_cd_string, item_cd_int,
  COUNT(DISTINCT IF(event_name = 'purchase', transaction_id, NULL)) AS unique_purchases,
  ROUND(SAFE_DIVIDE(SUM(IF(event_name = 'purchase', item_revenue, 0)),
                    SUM(IF(event_name = 'purchase', quantity, 0))), 2) AS avg_purchase_price,
  ROUND(SAFE_DIVIDE(SUM(IF(event_name = 'purchase', quantity, 0)),
                    COUNT(DISTINCT IF(event_name = 'purchase', transaction_id, NULL))), 2) AS avg_purchase_quantity,
  SUM(IF(event_name = 'purchase', quantity, 0))     AS item_quantity,
  SUM(IF(event_name = 'purchase', item_revenue, 0)) AS item_revenue,
  SUM(IF(event_name = 'refund',   item_revenue, 0)) AS item_refund,
  COUNTIF(event_name = 'view_promotion')   AS view_promotion_events,
  COUNTIF(event_name = 'view_item')        AS view_item_events,
  COUNTIF(event_name = 'view_item_list')   AS view_item_list_events,
  COUNTIF(event_name = 'select_item')      AS select_item_events,
  COUNTIF(event_name = 'add_to_wishlist')  AS add_to_wishlist_events,
  COUNTIF(event_name = 'add_to_cart')      AS add_to_cart_events,
  COUNTIF(event_name = 'remove_from_cart') AS remove_from_cart_events,
  COUNTIF(event_name = 'view_cart')        AS view_cart_events,
  COUNTIF(event_name = 'begin_checkout')   AS begin_checkout_events,
  COUNTIF(event_name = 'add_payment_info') AS add_payment_info_events,
  COUNTIF(event_name = 'add_shipping_info') AS add_shipping_info_events,
  COUNTIF(event_name = 'purchase')         AS purchase_events,
  COUNTIF(event_name = 'refund')           AS refund_events,
  ROUND(SAFE_DIVIDE(COUNTIF(event_name = 'add_to_cart'), COUNTIF(event_name = 'view_item')), 4) AS cart_to_detail_rate,
  ROUND(SAFE_DIVIDE(COUNTIF(event_name = 'purchase'),    COUNTIF(event_name = 'view_item')), 4) AS buy_to_detail_rate
FROM item_events
GROUP BY
  date, item_id, item_name, item_brand, item_variant,
  item_category, item_category2, item_category3, item_category4, item_category5,
  item_cd_string, item_cd_int
ORDER BY item_revenue DESC

The funnel counts are the heart of it: because each row is one item in one event, counting rows by event name gives the number of times the product appeared in a view, an add to cart, a purchase, and so on, which is exactly item-scoped event counting. The commercial metrics sum quantity and item revenue from purchase events, and refund value from refund events. Unique purchases count the distinct transactions in which the item appeared, which is why we carried the transaction id. Average purchase price is item revenue divided by quantity, and average purchase quantity is quantity divided by unique purchases, both wrapped in SAFE_DIVIDE so a product with no sales returns null rather than erroring. The two ratios at the end measure funnel efficiency, the cart-to-detail rate being adds to cart over item views, and the buy-to-detail rate being purchases over item views, each a direct read on how well a product converts once seen.

Conclusion

The item-scoped custom dimensions only exist from late October 2023, so leave those columns out, or expect them empty, for earlier dates. Grouping by all five category levels and both custom dimensions at once is very granular, so in practice trim the dimension list to the level you report on, often just item id, name, and brand. Because we unnested the items array, every count here is genuinely item-scoped, which is different from event-level counts elsewhere, an add-to-cart event with three products contributes to three item rows. The revenue and refund fields are the per-item amounts the export already calculates. The export timestamps are UTC, producing small day-boundary differences from a locally timed interface. And as always, the totals will not match the GA4 screen exactly, which is the sign that you now control the definitions.

The full ecommerce funnel sits in one table, every metric is defined in SQL you can read, and the same query feeds a one-off export, a scheduled table, or a live dashboard. From here you can rank products by their drop-off between view and cart, segment performance by brand or category, or join it to your traffic acquisition report to see which channels sell which products.

See you soon.