How to create a GA4 item / product performance report in BigQuery

This article contains a query that you can use to create a custom item / product ecommerce performance report, based on your Google Analytics 4 export data in BigQuery.

The result of the query will not be an exact replicate of any report in the GA4 user interface. While designing this report, we took into account:

• the available data fields in GA4 export
• current (GA4) and legacy (GA3/UA) user interface reports
• data definitions according to documentation (if available)
• scope (user / session / event / item)
• common sense

This query can be used:

• to create a result table that can be opened in Google Sheets or exported as csv
• to save as a BigQuery table, for further analysis
• to create (and frequently update) a BigQuery table that feeds a dashboard (e.g. Looker Studio)
• to use as a starting point to design your own report

Report dimensions

The report contains the following dimensions:

• date
• item id
• item name
• item brand
• item variant
• item category (1-5)
• item scoped custom dimension (string value)
• item scoped custom dimension (integer value)

Report metrics

The report contains the following metrics:

• unique purchases
• average purchase price
• average purchase quantity
• item quantity
• item revenue
• item refund
• view promotion events
• view item events
• view item list events
• select item events
• add to wishlist events
• add to cart events
• remove from cart events
• view cart events
• begin checkout events
• add payment info events
• add shipping info events
• purchase events
• refund events
• cart to detail rate
• buy to detail rate