Items dimensions & metrics (GA4)
How to query the most important items (product) dimensions and metrics from the GA4 BigQuery export, like item id, item name, item brand, item category, custom item parameters, price, quantity and item revenue.
A lot of Google Analytics 4 dimensions and metrics can be derived straight from the tables without performing calculations on the data. With the help of unnest and parse for dates or timestamps we can do the trick.
Other dimensions and metrics that you might need are a bit harder to access. You will have to (re)calculate them, which can require some serious SQL-skills.
While designing the course Query GA4 Data In Google BigQuery I've learned a lot about best practices to calculate dimensions and metrics. To share this knowledge with you I will provide a combined query for default dimensions and metrics, and a single example query for every non-default dimension or metric.
As always, drop a line in the comments if you have any questions, feedback or suggestions related to this article.
đĄ
Reclaim a significant amount of your time by subscribing to our premium membership. You'll gain immediate access to a ready-to-go GA4 item / product performance report query.
Default items dimensions
- items.item_id
- items.item_name
- items.item_brand
- items.item_variant
- items.item_category
- items.item_category2
- items.item_category3
- items.item_category4
- items.item_category5
- items.item_params.key
- items.item_params.value.string_value
- items.item_params.value.int_value
- items.item_params.value.float_value
- items.item_params.value.double_value
đĄ
item_params were added to the BigQuery export late October 2023. These fields are not available for date ranges before this addition.
- items.coupon
- items.affiliation
- items.location_id
- items.item_list_id
- items.item_list_name
- items.item_list_index
- items.promotion_id
- items.promotion_name
- items.creative_name
- items.creative_slot
Default items metrics
- items.price_in_usd
- items.price
- items.quantity
- items.item_revenue_in_usd
- items.item_revenue
- items.item_refund_in_usd
đĄ
If you only need one default dimension or metric, look at the -- comments in the example query below for names and definitions and copy the part you need from the select clause. Make sure that you also add any additional conditions (i.e. with, from, where, group by, having and order by) that are necessary to calculate the results correctly. E.g, the query below is ungrouped, so every row corresponds to an event and may contain duplicate rows.
Example query
select
-- item id (dimension | the id of the item)
items.item_id,
-- item name (dimension | the name of the item)
items.item_name,
-- item brand (dimension | the brand of the item)
items.item_brand,
-- item variant (dimension | the variant of the item)
items.item_variant,
-- item category (dimension | the category of the item)
items.item_category,
-- item category 2 (dimension | the sub category of the item)
items.item_category2,
-- item category 3 (dimension | the sub category of the item)
items.item_category3,
-- item category 4 (dimension | the sub category of the item)
items.item_category4,
-- item category 5 (dimension | the sub category of the item)
items.item_category5,
-- item_key (dimension | the item parameter's key | change key to select another parameter)
(select key from unnest(item_params) where key = 'dimension1') as item_key,
-- item_string_value (dimension | the string value of the item parameter | change key to select another parameter)
(select value.string_value from unnest(item_params) where key = 'dimension1') as item_string_value,
-- item_int_value (metric | the integer value of the item parameter | change key to select another parameter)
(select value.int_value from unnest(item_params) where key = 'dimension1') as item_int_value,
-- item_float_value (metric | the float value of the item parameter | change key to select another parameter)
(select value.float_value from unnest(item_params) where key = 'dimension1') as item_float_value,
-- item_double_value (metric | the double value of the item parameter | change key to select another parameter)
(select value.double_value from unnest(item_params) where key = 'dimension1') as item_double_value,
-- price in usd (metric | the price of the item, in usd with standard unit)
items.price_in_usd,
-- price (metric | the price of the item in local currency)
items.price,
-- quantity (metric | the quantity of the item)
items.quantity,
-- item revenue in usd (metric | the revenue of this item, calculated as price_in_usd * quantity
-- it is populated for purchase events only, in usd with standard unit)
items.item_revenue_in_usd,
-- item revenue (metric | the revenue of this item, calculated as price * quantity
-- it is populated for purchase events only, in local currency with standard unit)
items.item_revenue,
-- item refund in usd (metric | the refund value of this item, calculated as price_in_usd * quantity
-- it is populated for refund events only, in usd with standard unit)
items.item_refund_in_usd,
-- item refund (metric | the refund value of this item, calculated as price_in_usd * quantity
-- it is populated for refund events only, in local currency with standard unit)
items.item_refund,
-- coupon (dimension | coupon code applied to this item)
items.coupon,
-- affiliation (dimension | a product affiliation to designate a supplying company
-- or brick and mortar store location)
items.affiliation,
-- location id (dimension | the location associated with the item)
items.location_id,
-- item list id (dimension | the id of the list in which the item was presented to the user)
items.item_list_id,
-- item list name (dimension | the name of the list in which the item was presented to the user)
items.item_list_name,
-- item list index (dimension | the position of the item in a list)
items.item_list_index,
-- promotion id (dimension | the id of a product promotion)
items.promotion_id,
-- promotion name (dimension | the name of a product promotion)
items.promotion_name,
-- creative name (dimension | the name of a creative used in a promotional spot)
items.creative_name,
-- creative slot (dimension | the name of a creative slot)
items.creative_slot
from
-- change this to your google analytics 4 export location in bigquery
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20201130`,
unnest(items) as items