GA4 Window Function Pitfalls

Window functions are the engine of event-grain sessionization. Session context — landing pages, traffic sources, conversion flags — gets propagated to every event in a session through FIRST_VALUE, LAST_VALUE, MAX, and ROW_NUMBER. GA4’s event-sparse structure creates three specific failure modes that aren’t obvious from general SQL documentation.

Pitfall 1: The LAST_VALUE Framing Trap

LAST_VALUE has a default frame that surprises almost everyone who first encounters it: by default, the frame ends at the current row, not at the end of the partition.

-- WRONG: Returns the current row's page, not the session's last page
LAST_VALUE(page__path) OVER (
    PARTITION BY session__key
    ORDER BY event__timestamp_utc
)

With the default frame (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), every row’s “last value” is its own value. You’ve written a no-op.

The fix is explicit frame specification:

-- CORRECT: Returns the actual last page in the entire session
LAST_VALUE(page__path IGNORE NULLS) OVER (
    PARTITION BY session__key
    ORDER BY event__timestamp_utc
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

UNBOUNDED FOLLOWING extends the frame to the end of the partition. Now every event in the session sees the same exit page — the last page viewed in that session.

FIRST_VALUE doesn’t have this problem because its default frame (UNBOUNDED PRECEDING to CURRENT ROW) naturally includes the first element of the partition from any row. But LAST_VALUE requires explicit UNBOUNDED FOLLOWING every time.

When using named windows to avoid repetition, set the frame at the window level:

WINDOW
    session_window AS (
        PARTITION BY session__key
        ORDER BY event__timestamp_utc
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    )

Then both FIRST_VALUE and LAST_VALUE references using this window will behave correctly.

Pitfall 2: IGNORE NULLS for Sparse Event Data

GA4 doesn’t record page_location for every event type. Engagement events, custom events, and many standard events don’t have a page URL — only page_view events reliably populate it. Using FIRST_VALUE without IGNORE NULLS often returns null.

-- WRONG: May return NULL if the first event has no page location
FIRST_VALUE(page__path) OVER (session_window) AS session__landing_page

-- CORRECT: Returns the first non-null page in the session
FIRST_VALUE(page__path IGNORE NULLS) OVER (session_window) AS session__landing_page

Without IGNORE NULLS, you’re not getting the landing page — you’re getting whatever value the chronologically first event in the session has, which is often null when session_start fires without page context.

The same applies to traffic source fields. Sessions in GA4 can have a session_start event without UTM parameters (direct traffic), followed by events where source/medium are populated from event_params. IGNORE NULLS ensures you capture the actual source when it exists:

FIRST_VALUE(
    COALESCE(event__source, session__source) IGNORE NULLS
) OVER (session_window) AS session__source_final

The COALESCE here handles two different source field locations: event__source (from event_params) and session__source (from collected_traffic_source). Take the first non-null value from either location.

Pitfall 3: MAX for Boolean Flag Propagation

Session-level conversion flags — whether any event in the session was a purchase, an add-to-cart, a signup — need to appear on every event row in the session. The natural implementation uses MAX over the session partition:

MAX(CASE WHEN event__name = 'purchase' THEN 1 ELSE 0 END)
    OVER (PARTITION BY session__key) AS session__has_purchase

This works because MAX over a window without ORDER BY scans the entire partition, not just the preceding rows. Every event in the session sees the maximum value — which is 1 if any event was a purchase, 0 otherwise.

The equivalent with FIRST_VALUE or LAST_VALUE would require an ORDER BY clause and frame specification:

-- Also works, but more verbose
LAST_VALUE(
    CASE WHEN event__name = 'purchase' THEN 1 ELSE 0 END
) OVER (
    PARTITION BY session__key
    ORDER BY CASE WHEN event__name = 'purchase' THEN 1 ELSE 0 END
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

MAX without ORDER BY is the simpler, faster approach. No ordering required. No frame specification needed.

A Note on Query Performance

Named windows reduce both verbosity and the risk of framing errors:

WITH with_session_metrics AS (

    SELECT
        e.*,

        FIRST_VALUE(page__path IGNORE NULLS) OVER w AS session__landing_page,
        LAST_VALUE(page__path IGNORE NULLS) OVER w AS session__exit_page,
        MAX(CASE WHEN event__name = 'purchase' THEN 1 ELSE 0 END) OVER p
            AS session__has_purchase

    FROM events e

    WINDOW
        w AS (
            PARTITION BY session__key
            ORDER BY event__timestamp_utc
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ),
        p AS (PARTITION BY session__key)

)

Two named windows: w for ordered computations (FIRST_VALUE, LAST_VALUE, ROW_NUMBER) with the full frame, and p for unordered partition aggregates (MAX, SUM, COUNT). Using the right window for each function type both clarifies intent and avoids accidental framing errors.

BigQuery evaluates window functions efficiently even when you define multiple named windows — they don’t result in multiple passes over the data unless the PARTITION BY or ORDER BY differ.

Summary