How to replicate the 'Conversions | (Enhanced) Ecommerce | Product performance' report
A query to generate the Google Analytics product performance report in BigQuery. In this report you'll find data about your (enhanced) ecommerce performance, segmented by product, product sku, product category and product brand.
💡
This article is about GA3 - Universal Analytics
As a Google Analytics user you are probably quite attached to the default reports in the user interface of Universal Analytics. It can be hard to make sense of the data in the BigQuery export tables.
Let me enable you to replicate the reports you're familiar with. I'll try to keep it basic here.
Product performance report
In the Conversions | (Enhanced) Ecommerce | Product performance report you'll find data about the performance of your enhanced ecommerce activities, segmented by product, product sku, product category and product brand.
Let's query!
select
v2productname as product,
-- productsku,
-- v2productcategory as product_category,
-- productbrand,
ifnull(sum(case when hits.ecommerceaction.action_type = '6' then productrevenue else null end)/1000000,0) as product_revenue,
count(case when hits.ecommerceaction.action_type = '6' then hits.transaction.transactionid else null end) as unique_purchases,
ifnull(sum(case when hits.ecommerceaction.action_type = '6' then productquantity else null end),0) as quantity,
ifnull(safe_divide(sum(case when hits.ecommerceaction.action_type = '6' then productrevenue else null end)/1000000,sum(case when hits.ecommerceaction.action_type = '6' then productquantity else null end)),0) as avg_price,
ifnull(safe_divide(sum(case when hits.ecommerceaction.action_type = '6' then productquantity else null end),count(case when hits.ecommerceaction.action_type = '6' then hits.transaction.transactionid else null end)),0) as avg_quantity,
ifnull(safe_divide(count(case when hits.ecommerceaction.action_type = '3' then fullvisitorid else null end),count(case when hits.ecommerceaction.action_type = '2' and product.isimpression is null then fullvisitorid else null end)),0) as cart_to_detail_rate,
ifnull(safe_divide(count(case when hits.ecommerceaction.action_type = '6' then hits.transaction.transactionid else null end),count(case when hits.ecommerceaction.action_type = '2' and product.isimpression is null then fullvisitorid else null end)),0) as buy_to_detail_rate
from
`bigquery-public-data.google_analytics_sample.ga_sessions_20160802`,
unnest(hits) as hits,
unnest(product) as product
where
totals.visits = 1
group by
product
-- ,productsku
-- ,product_category
-- ,productbrand
order by
product_revenue desc