Im Ecommerce-Produktleistungs-Bericht finden Sie Daten über die Leistung Ihrer Enhanced Ecommerce-Aktivitäten, aufgeschlüsselt nach Produkt, Produktsku, Produktkategorie und Produktmarke.
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