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 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.

Ecommerce dimensions

• product sku
• product
• product category

Ecommerce metrics

• quantity
• unique purchases
• avg. price
• product revenue
• avg. quantity

Example query

-- 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