Enhanced Ecommerce products: dimensions & metrics

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 hits.item fields.

This example query contains all following Google Analytics enhanced 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 from, where, group by and order by) that are necessary to calculate the results correctly.

Enhanced Ecommerce dimensions

• product sku
• product
• product category (enhanced ecommerce)
• product brand
• product variant

Enhanced Ecommerce metrics

• quantity
• unique purchases
• product revenue
• avg. price
• avg. quantity
• buy-to-detail rate
• cart-to-detail rate
• product adds to cart
• product checkouts
• product detail views
• product refunds
• product removes from cart
• refund amount

Example query 1

select
  -- product sku (dimension)
  productsku as product_sku,
  -- product (dimension)
  v2productname as product,
  -- product variant (dimension)
  productvariant as product_variant,
  -- product brand (dimension)
  productbrand as product_brand,
  -- product category (enhanced ecommerce) (dimension)
  v2productcategory as product_category_enhanced_ecommerce,
  -- unique purchases (metric)
  count(hits.transaction.transactionid) as unique_purchases,
  -- quantity (metric)
  sum(productquantity) as quantity,
  -- product revenue (metric)
  sum(productrevenue)/1000000 as product_revenue,
  -- avg. price
  sum(productrevenue)/1000000 / sum(productquantity) as avg_price,
  -- avg. qty
  sum(productquantity) / count(hits.transaction.transactionid) as avg_quantity
from
  `bigquery-public-data.google_analytics_sample.ga_sessions_20160801`,
  unnest(hits) as hits,
  unnest(product) as product
where
  totals.visits = 1
  and hits.ecommerceaction.action_type = '6'
group by
  product_sku,
  product,
  product_variant,
  product_brand,
  product_category_enhanced_ecommerce
order by
  product_revenue desc

Example query 2

select
  -- product sku (dimension)
  productsku as product_sku,
  -- unique purchases (metric)
  count(case when hits.ecommerceaction.action_type = '6' then hits.transaction.transactionid else null end) as unique_purchases,
  -- cart-to-detail rate (metric)
  case when count(case when hits.ecommerceaction.action_type = '2' then fullvisitorid else null end) = 0 then 0 else count(case when hits.ecommerceaction.action_type = '3' then fullvisitorid else null end) / count(case when hits.ecommerceaction.action_type = '2' then fullvisitorid else null end) end as cart_to_detail_rate,
  -- buy-to-detail rate (metric)
  case when count(case when hits.ecommerceaction.action_type = '2' then fullvisitorid else null end) = 0 then 0 else count(case when hits.ecommerceaction.action_type = '6' then hits.transaction.transactionid else null end) / count(case when hits.ecommerceaction.action_type = '2' then fullvisitorid else null end) end as buy_to_detail_rate,
  -- product detail views (metric)
  count(case when hits.ecommerceaction.action_type = '2' then fullvisitorid else null end) as product_detail_views,
  -- product adds to cart (metric)
  count(case when hits.ecommerceaction.action_type = '3' then fullvisitorid else null end) as product_adds_to_cart,
  -- product removes from cart (metric)
  count(case when hits.ecommerceaction.action_type = '4' then fullvisitorid else null end) as product_removes_from_cart,
  -- product checkouts (metric)
  count(case when hits.ecommerceaction.action_type = '5' then fullvisitorid else null end) as product_checkouts,
  -- product refunds (metric)
  count(case when hits.ecommerceaction.action_type = '7' then fullvisitorid else null end) as product_refunds
from
  `bigquery-public-data.google_analytics_sample.ga_sessions_20160801`,
  unnest(hits) as hits,
  unnest(product) as product
where
  totals.visits = 1
  and isimpression is null
group by
  product_sku
order by
  unique_purchases desc