When entering the product scope you have to verify if enhanced ecommerce is enabled in Google Analytics. If so, you’re safe to use the
hits.product fields. If only ‘standard’ ecommerce is measured: use the
This example query contains all following Google Analytics ecommerce products dimensions and metrics. If you only need one dimension or metric, look at the
-- comments in the example query and copy the part you need from the
select clause. Make sure that you also add any additional conditions (in the
group by and
order by) that are necessary to calculate the results correctly.
- product sku
- product category
- unique purchases
- avg. price
- product revenue
- avg. quantity
-- this query will return no data as there is no standard ecommerce product data available in the sample data set select -- product sku (dimension) hits.item.productsku as product_sku, -- product (dimension) hits.item.productname as product, -- product category (dimension) hits.item.productcategory as product_category, -- quantity sum(hits.item.itemquantity) as quantity, -- unique purchases (metric) case when hits.item.productsku is not null then count(hits.transaction.transactionid) else null end as unique_purchases, -- product revenue ifnull(sum(hits.item.itemrevenue)/1000000,0) as product_revenue, -- avg. price ifnull(safe_divide(sum(hits.item.itemrevenue)/1000000,sum(hits.item.itemquantity)),0) as avg_price, -- avg. quantity ifnull(safe_divide(sum(hits.item.itemquantity),count(hits.transaction.transactionid)),0) as avg_quantity from `bigquery-public-data.google_analytics_sample.ga_sessions_20160801`, unnest(hits) as hits where totals.visits = 1 group by product_sku, product, product_category having product is not null order by product_revenue desc
Enjoying this post? Subscribe to the GA4BigQuery newsletter!
You will receive regular updates (max 1x/week) straight in your inbox. You can unsubscribe anytime.