Let’s say you want to know which marketing channel actually drives sales, and which one just burns through your budget (and your patience).
In GA4, it’s easy: open the traffic sources report, check the Conversion Rate, sip your latte, move on. But in BigQuery? That’s where you’ll need to think—and write a bit of SQL. Okay, maybe more than a bit.
What is a Conversion Rate, really?
The classic formula: Conversion Rate = Number of Converted users / Number of users.
Now, add “by medium” to that, and you’ve got a fun little SQL puzzle featuring traffic_source.
What do you need to do in BigQuery?
1. Find conversion events—purchase or whatever action you value.
2. Extract the traffic source fields: traffic_source.source, medium, campaign, etc.
3. Group users by channel.
4. Calculate:
• How many users came from each channel
• How many of those users made a purchase
• Divide one by the other (and yes - use SAFE_DIVIDE to avoid division by zero)
Pro tips:
• traffic_source is often only set on the first visit - don’t assume it’s always up to date. By this way you draw sort-of-first-click attribution.
• Want real attribution? You’ll need to extract UTM tags from event_params or traffic source fields.
• If you're using CRM or external data, be careful with duplicates and multi-touch sessions.
Takeaway:
GA4 is great for pretty dashboards. BigQuery shows the truth—sometimes uncomfortable, but always honest. You might even discover that some channels bring in nothing. No sales, no joy. Just CPC and heartbreak. So if you’ve been secretly thinking organic works better than paid...
Now’s your chance to prove it. Or rethink everything.
Want to get all my top Linkedin content? I regularly upload it to one Notion doc.
Go here to download it for FREE.
