GA4’s interface makes everything look beautiful. You open a report, see a neat metric like first_purchase, smile, and move on. But in BigQuery, things are a little more honest — and, as usual, a bit more complicated.
But hey, we’re not here for the easy way out, are we?
So, what counts as a new purchase? It’s when a user buys from you for the very first time. Not the second, not the third—the first.
That magical moment when a marketer’s heart skips a beat.
And a repeat purchase? That’s any transaction that comes after the first one. Maybe less exciting, but way more reliable. And let’s be honest—reliability is everything.
How do you calculate this in BigQuery?
Let’s say you’ve got your purchase events. Then:
1. Group all purchases by user_pseudo_id
2. Find the first purchase date for each user
That’s it. A little window function magic, and boom — you’ve got a clear split between new customers and your regulars.
Why bother?
• To know how many people actually bought for the first time
• To measure retention in revenue, not just in pretty percentages
• So your sales team doesn’t mistake 1 customer with 3 orders for 3 new clients
Bottom line:
GA4 will show you a “first purchase” metric — but it won’t tell you the story.
BigQuery gives you the full picture: who bought, when, how often, and how much revenue they’ve brought in.
SQL isn’t pain — it’s just the truth, plain and simple.
Want to get all my top Linkedin content? I regularly upload it to one Notion doc.
Go here to download it for FREE.
