When you're trying to understand what a user actually did on your site - what steps they took and in what order, and why they eventually dropped off (or converted) - the GA4 interface just shrugs and says - “Here’s Last Click Attribution. Enjoy.”
But we’re not easily discouraged. We go to BigQuery. And there we meet today’s hero: ARRAY_AGG.
What does ARRAY_AGG do?
It turns rows into an array. And if you use ORDER BY, you can sort those events chronologically to reconstruct the full sequence—like: “Viewed product → Added to cart → Paused → Left.”
Simple plan:
1. Filter by the user (or set of users) you want to analyze
2. Sort events by event_timestamp
3. Use ARRAY_AGG(event_name ORDER BY event_timestamp) — and "voilà", you get a timeline of events in order
Now you can clearly see that the user started on home_page, then got stuck on product_view, and suddenly jumped to checkout. Or maybe they just hit the back button 7 times in a row. It happens.
Watch out for a few things:
• Time zones. Make sure everything is in UTC (or properly converted), or you’ll end up with checkout showing up before product_view - and start suspecting a time machine.
• Duplicates. The same event can happen multiple times. If you only want unique actions, wrap it with DISTINCT.
• Volume. If your site has Amazon-level traffic, aggregating event arrays for every user will be rough - both on your cluster and your budget.
Takeaway:
• GA4 gives you a simple click/no-click view.
• BigQuery gives you the full story—but you’ll need to piece it together.
• And ARRAY_AGG is your front-row ticket to the movie, where the user is the main character.
Still wondering why someone bailed on the third page of your catalog? Rebuild their journey—and pass it on to your UX team. With sarcasm. And an array.
Want to get all my top Linkedin content? I regularly upload it to one Notion doc.
Go here to download it for FREE.
