Pièges des fonctions de fenêtrage GA4

Les fonctions de fenêtrage sont le moteur de la sessionisation à grain événement. Le contexte de session — pages d’atterrissage, sources de trafic, indicateurs de conversion — est propagé à chaque événement d’une session via FIRST_VALUE, LAST_VALUE, MAX et ROW_NUMBER. La structure d’événements éparses de GA4 crée trois modes de défaillance spécifiques qui ne sont pas évidents depuis la documentation SQL générale.

Piège 1 : Le piège de cadrage LAST_VALUE

LAST_VALUE a un cadre par défaut qui surprend presque tous ceux qui le rencontrent pour la première fois : par défaut, le cadre se termine à la ligne courante, pas à la fin de la partition.

-- INCORRECT : Retourne la page de la ligne courante, pas la dernière page de la session
LAST_VALUE(page__path) OVER (
    PARTITION BY session__key
    ORDER BY event__timestamp_utc
)

Avec le cadre par défaut (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), la « dernière valeur » de chaque ligne est sa propre valeur. Vous avez écrit une opération sans effet.

La correction est la spécification explicite du cadre :

-- CORRECT : Retourne la dernière page réelle de toute la 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 étend le cadre jusqu’à la fin de la partition. Maintenant chaque événement de la session voit la même page de sortie — la dernière page vue dans cette session.

FIRST_VALUE n’a pas ce problème parce que son cadre par défaut (UNBOUNDED PRECEDING jusqu’à CURRENT ROW) inclut naturellement le premier élément de la partition depuis n’importe quelle ligne. Mais LAST_VALUE nécessite UNBOUNDED FOLLOWING explicite à chaque fois.

Lors de l’utilisation de fenêtres nommées pour éviter la répétition, définissez le cadre au niveau de la fenêtre :

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

Les références FIRST_VALUE et LAST_VALUE utilisant cette fenêtre se comporteront alors correctement.

Piège 2 : IGNORE NULLS pour les données d’événements éparses

GA4 n’enregistre pas page_location pour chaque type d’événement. Les événements d’engagement, les événements personnalisés et de nombreux événements standard n’ont pas d’URL de page — seuls les événements page_view le renseignent de manière fiable. Utiliser FIRST_VALUE sans IGNORE NULLS retourne souvent null.

-- INCORRECT : Peut retourner NULL si le premier événement n'a pas de page location
FIRST_VALUE(page__path) OVER (session_window) AS session__landing_page

-- CORRECT : Retourne la première page non nulle de la session
FIRST_VALUE(page__path IGNORE NULLS) OVER (session_window) AS session__landing_page

Sans IGNORE NULLS, vous n’obtenez pas la page d’atterrissage — vous obtenez la valeur du premier événement chronologiquement dans la session, qui est souvent null quand session_start se déclenche sans contexte de page.

La même règle s’applique aux champs de source de trafic. Les sessions GA4 peuvent avoir un événement session_start sans paramètres UTM (trafic direct), suivi d’événements où source/medium sont renseignés depuis event_params. IGNORE NULLS garantit que vous capturez la source réelle lorsqu’elle existe :

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

Le COALESCE ici gère deux emplacements différents pour les champs source : event__source (depuis event_params) et session__source (depuis collected_traffic_source). Prenez la première valeur non nulle de l’un ou l’autre emplacement.

Piège 3 : MAX pour la propagation des indicateurs booléens

Les indicateurs de conversion à portée de session — si un événement de la session était un achat, un ajout au panier, une inscription — doivent apparaître sur chaque ligne d’événement de la session. L’implémentation naturelle utilise MAX sur la partition de session :

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

Cela fonctionne parce que MAX sur une fenêtre sans ORDER BY scanne toute la partition, pas seulement les lignes précédentes. Chaque événement de la session voit la valeur maximale — qui est 1 si un événement était un achat, 0 sinon.

L’équivalent avec FIRST_VALUE ou LAST_VALUE nécessiterait une clause ORDER BY et une spécification de cadre :

-- Fonctionne aussi, mais plus verbeux
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 sans ORDER BY est l’approche plus simple et plus rapide. Pas d’ordonnancement requis. Pas de spécification de cadre nécessaire.

Note sur la performance des requêtes

Les fenêtres nommées réduisent à la fois la verbosité et le risque d’erreurs de cadrage :

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)

)

Deux fenêtres nommées : w pour les calculs ordonnés (FIRST_VALUE, LAST_VALUE, ROW_NUMBER) avec le cadre complet, et p pour les agrégats de partition non ordonnés (MAX, SUM, COUNT). Utiliser la bonne fenêtre pour chaque type de fonction clarifie à la fois l’intention et évite les erreurs de cadrage accidentelles.

BigQuery évalue les fonctions de fenêtrage efficacement même lorsque vous définissez plusieurs fenêtres nommées — elles ne résultent pas en plusieurs passages sur les données, sauf si le PARTITION BY ou l’ORDER BY diffèrent.

Résumé