How to replicate the 'Conversions | (Enhanced) Ecommerce | Sales performance' report
A query to generate the Google Analytics sales performance report in BigQuery. In this report you'll find data about your (enhanced) ecommerce performance, segmented by transaction id and date.
💡
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