Google Analytics 4 provides a wealth of data that can be accessed directly from tables without needing any complex calculations. However, there may be certain dimensions and metrics that require some advanced SQL skills to access and calculate. I have learned some valuable insights on the best practices for calculating these dimensions and metrics that I wanted to share.
I will provide a combined query for default dimensions and metrics, as well as an example query for each non-default dimension or metric. If you only need to access one default dimension or metric, you can refer to the example query and simply copy the corresponding part. However, make sure to include any additional conditions required to accurately calculate the results, such as with, from, where, group by, having, and order by. It’s important to note that the query I am providing is ungrouped, which means that each row corresponds to an event and may contain duplicate rows.
Here are the default dimensions and metrics. For non-default dimensions and metrics, please refer to the example query.
| Dimension | Description |
|---|---|
| items.item_id | The ID of the product or item being purchased |
| items.item_name | The name of the product or item being purchased |
| items.item_brand | The brand of the product or item being purchased |
| items.item_variant | The variant of the product or item being purchased |
| items.item_category | The category of the product or item being purchased |
| items.item_category2 | The second category of the product or item |
| items.item_category3 | The third category of the product or item |
| items.item_category4 | The fourth category of the product or item |
| items.item_category5 | The fifth category of the product or item |
| items.coupon | The coupon code used for the purchase |
| items.affiliation | The affiliation code for the purchase |
| items.location_id | The ID of the location where the purchase occurred |
| items.item_list_id | The ID of the list that the item was a part of |
| items.item_list_name | The name of the list that the item was a part of |
| items.item_list_index | The position of the item in the list |
| items.promotion_id | The ID of the promotion that was applied |
| items.promotion_name | The name of the promotion that was applied |
| items.creative_name | The name of the creative that was displayed |
| items.creative_slot | The position of the creative that was displayed |
| Metric | Description |
|---|---|
| items.price_in_usd | The price of the item in US dollars |
| items.price | The price of the item |
| items.quantity | The quantity of the item purchased |
| items.item_revenue_in_usd | The revenue generated by the item in US dollars |
| items.item_revenue | The revenue generated by the item |
| items.item_refund_in_usd | The refund amount in US dollars for the item |
| items.item_refund | The refund amount for the item |
This can be used to select dimensions and metrics related to items from a Google Analytics 4 export in BigQuery
SELECT
-- Dimensions:
-- The ID of the item
items.item_id,
-- The name of the item
items.item_name,
-- The brand of the item
items.item_brand,
-- The variant of the item
items.item_variant,
-- The category of the item
items.item_category,
-- The sub-category of the item
items.item_category2,
-- The sub-sub-category of the item
items.item_category3,
-- The sub-sub-sub-category of the item
items.item_category4,
-- The sub-sub-sub-sub-category of the item
items.item_category5,
-- Metrics:
-- The price of the item, in USD
items.price_in_usd,
-- The price of the item in local currency
items.price,
-- The quantity of the item
items.quantity,
-- The revenue of this item, calculated as price_in_usd * quantity, in USD
-- (populated for purchase events only)
items.item_revenue_in_usd,
-- The revenue of this item, calculated as price * quantity, in local currency
-- (populated for purchase events only)
items.item_revenue,
-- The refund value of this item, calculated as price_in_usd * quantity, in USD
-- (populated for refund events only)
items.item_refund_in_usd,
-- The refund value of this item, calculated as price * quantity, in local currency
-- (populated for refund events only)
items.item_refund,
-- Additional dimensions:
-- The coupon code applied to this item
items.coupon,
-- A product affiliation to designate a supplying company or brick and mortar store location
items.affiliation,
-- The location associated with the item
items.location_id,
-- The ID of the list in which the item was presented to the user
items.item_list_id,
-- The name of the list in which the item was presented to the user
items.item_list_name,
-- The position of the item in a list
items.item_list_index,
-- The ID of a product promotion
items.promotion_id,
-- The name of a product promotion
items.promotion_name,
-- The name of a creative used in a promotional spot
items.creative_name,
-- 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 the 'items' column to make its fields available as separate columns
UNNEST(items) AS items
This query selects various dimensions and metrics related to e-commerce events, such as item IDs, names, brands, categories, and revenue information. The data is obtained from a Google Analytics 4 export location in BigQuery.
This query would be useful when analysing e-commerce activity and performance, such as identifying popular items, revenue trends, and promotions that are effective in driving sales. It can also be used to gain insights into customer behaviour and preferences by examining the characteristics of items purchased, such as categories and brands. The data can be further analysed and visualised using various tools and techniques to help inform business decisions and improve e-commerce strategies.