How to replicate the 'Conversions | (Standard) 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 (standard) ecommerce performance, segmented by product, product sku and product category.
💡
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 | (Standard) Ecommerce | Product performance report you'll find data about the performance of your ecommerce activities, segmented by product, product sku and product category.
Let's query!
-- this query will return no data as there is no standard ecommerce product data available in the sample data set
select
hits.item.productname as product,
-- hits.item.productsku,
-- hits.item.productcategory,
sum(hits.item.itemquantity) AS quantity,
count(case when hits.item.productsku is not null then hits.transaction.transactionid else null end) as unique_purchases,
sum(hits.item.itemrevenue)/1000000 as product_revenue,
ifnull(safe_divide(sum(hits.item.itemrevenue)/1000000,sum(hits.item.itemquantity)),0) as avg_price,
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
and hits.item.productname is not null
group by
product
-- ,productsku
-- ,productcategory
order by
product_revenue desc