What are the top products and variants by purchases and revenue? - GA4 BigQuery
Learn to query Google Analytics 4 data from BigQuery using SQL.
With Google Analytics 4 (GA4), you will now be able to store your data in Google BigQuery which opens up lots of interesting possibilities with your data. Ask questions of your data with SQL.
Query: What are the top products and variants by purchases and revenue?
select items.item_name as item_name, items.item_variant as item_variant, sum(ecommerce.total_item_quantity) as total_item_quantity, sum(ecommerce.purchase_revenue) as revenue from `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`, unnest(items) as items where _TABLE_SUFFIX between '20210101' and '20210107' and event_name = 'purchase' group by item_name, item_variant order by total_item_quantity desc limit 10
- item_name -> product
- item_variant -> variant
- total_item_quantity -> purchases
- purchase_revenue -> revenue
- All queries reference the BigQuery sample GA4 data set. You can try this for free and Google provides a substantial free credit.
- The dataset contains data from 1 Nov 2020 to 31 Jan 2021.
- You will need to update the data source if you want to use this query with your own data.
- You can update the date range to suit your needs. I have chosen 1 Jan 2021 to 7 Jan 2021 for this example.
- Occasionally the results may not line up perfectly when querying this sample dataset.
Want to learn how to get more from your data? Get in touch with me on LinkedIn.