You've successfully subscribed to GA4BigQuery
Success! Your account is fully activated, you now have access to all content.
Success! Your billing info is updated.
Billing info update failed.

How to replicate the 'Conversions | (Enhanced) Ecommerce | Sales performance' report

Join 2,600+ other professionals and receive a monthly newsletter packed with GA4 & BigQuery news, tips & tricks

Great! Check your inbox and click the link to confirm your subscription
Please enter a valid email address!
Already have an account? Sign in
💡
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.

Sales performance report

In the Conversions | (Enhanced) Ecommerce | Sales performance report you'll find data about the performance of your enhanced ecommerce activities, segmented by transaction id and date.

Let's query!

select
  hits.item.transactionid,
  -- date,
  ifnull(sum(hits.transaction.transactionrevenue)/1000000,0) as revenue,
  ifnull(sum(hits.transaction.transactiontax)/1000000,0) as tax,
  ifnull(sum(hits.transaction.transactionshipping)/1000000,0) as shipping,
  ifnull(sum(hits.refund.refundAmount),0) as refund_amount,
  ifnull(sum(case when hits.ecommerceaction.action_type = '6' then productquantity else null end),0) as 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.item.transactionid is not null
group by
  transactionid
  -- ,date
order by
  revenue desc