At first glance, it seems simple: want to analyze sales? Just use item_id and start counting. But GA4 wouldn’t be GA4 if it didn’t make even the basics a bit trickier.
First: item_id isn’t a standalone field. It lives inside the items array. So every time you want to see what exactly the user bought, you’ll need to UNNEST(items). Welcome to the SQL carousel.
Second: items holds more than just item_id.
It can include:
• item_name
• item_category
• price
• quantity
• item_brand
But to access those fields, you’ll have to dig into the structure. No simple SELECT * will do the trick here.
Let’s say you want to count how many times each product was sold. You’ll need to:
• UNNEST the items array
• Filter for purchase events (or view_item, if you're analyzing views)
• Group by item_id
• Don’t forget to sum quantity — otherwise, you're counting orders, not actual units sold
Sounds a bit complex? Maybe. But the upside is full flexibility.
For example, you can:
• Compare performance across product categories
• Track top-selling items by brand
• Identify “dead” products no one’s viewing or buying (hi, procurement team)
Bottom line: GA4 gives you pretty reports — but only in the interface. BigQuery takes more effort, but it shows you the real story behind item_id.
And with just a few lines of SQL, you can learn more about your products than from an entire year of sales reports.
Want to get all my top Linkedin content? I regularly upload it to one Notion doc.
Go here to download it for FREE.
