BigQuery Window Functions Are Your Most Powerful Analytical Tool

Writing GROUP BY queries for behavioural analysis often adds unnecessary complexity. Window functions compute session-level, user-level, and time-series metrics without collapsing the event grain.

LAG/LEAD for inter-event timing and sequence detection. FIRST_VALUE/LAST_VALUE for entry/exit page attribution within sessions (give LAST_VALUE an explicit ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING frame, or it simply returns the current row). NTILE for percentile-based segmentation. ARRAY_AGG with ORDER BY for building complete user journeys as arrays. ROW_NUMBER with PARTITION BY for deduplication and sequence numbering.

The real power emerges when you layer windows: rank pages by exit rate within sessions in one CTE, then aggregate those ranks across users to find pages that consistently terminate high-value sessions. This is one SQL query, not a multi-step pipeline.

Here's a practical example: finding pages that kill conversions. GA4 has no session_id column — a session is identified by user_pseudo_id plus the ga_session_id event parameter — so use LEAD(event_name) OVER (PARTITION BY user_pseudo_id, ga_session_id ORDER BY event_timestamp) to see what happens after each page_view. Filter for page views with no next event (the GA4 web export has no session_end event). Group by page_location, compute the exit rate, and join with conversion data. You now have a ranked list of pages that most frequently end sessions without a conversion — your optimisation priority list.

Window functions also enable sessionisation without pre-computed session IDs: compute time_gap with LAG(event_timestamp), then use SUM(CASE WHEN time_gap IS NULL OR time_gap > threshold THEN 1 ELSE 0 END) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) to generate session boundaries on the fly (remember that event_timestamp is in microseconds).