💡 BigQuery Tip of the Week: Use QUALIFY to find the latest event for each user

Need the most recent event for every GA4 user?

You could create a subquery with ROW_NUMBER() and then filter it. But BigQuery gives you a cleaner option: QUALIFY.

SELECT
  user_pseudo_id,
  event_name,
  event_timestamp
FROM `project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260901' AND '20260907'
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY user_pseudo_id
  ORDER BY event_timestamp DESC
) = 1;

ROW_NUMBER() ranks each user's events from newest to oldest.

QUALIFY then keeps only #1, the user's most recent event.

Why this is useful with GA4 data

You can use the same pattern to find:

✓ A user's latest event
✓ Their most recent traffic source
✓ Their last purchase
✓ The latest event in each session
✓ The most recent value of a user property

The BigQuery trick: WHERE filters rows before window functions are calculated. QUALIFY lets you filter based on the result of a window function.

Cleaner SQL. Fewer unnecessary subqueries.

Thank you,
Anil Batra, Founder Optizent

P.S. Want to become more confident with BigQuery? Enroll in my BigQuery for Marketers and Marketing Analysts course.