Modèle d'événements sessionisés GA4

Le modèle d’événements sessionisés est le pilier d’un projet dbt pour GA4. Il prend les événements de base nettoyés et typés, puis ajoute le contexte de session à chaque ligne — page d’atterrissage, source de trafic, indicateurs de conversion, durée de session — via des fonctions de fenêtrage. Tout ce qui est en aval lit depuis ce modèle.

La philosophie est la sessionisation à grain événement : préserver le grain d’une ligne par événement tout en rendant l’analyse au niveau session triviale grâce au pré-calcul du contexte de session. Cette note couvre les détails d’implémentation.

Configuration du modèle

-- models/intermediate/ga4/int__ga4__events_sessionized.sql

{{
    config(
        materialized='incremental',
        incremental_strategy='insert_overwrite',
        partition_by={
            "field": "event__date",
            "data_type": "date",
            "granularity": "day"
        },
        cluster_by=['user__pseudo_id', 'session__key', 'event__name']
    )
}}

{% set lookback_days = var('ga4_static_incremental_days', 3) %}

Le même pattern de lookback statique s’applique ici. insert_overwrite remplace les partitions de date de manière atomique. Le lookback de 3 jours garantit que les fonctions de fenêtrage se réexécutent sur des sessions qui peuvent chevaucher la frontière de partition — une session qui commence le jour N-3 et a des événements les jours N-2 et N-1 a besoin de tous ses événements présents pour un contexte de session correct.

Le clustering par user__pseudo_id, session__key et event__name améliore les performances des requêtes pour les patterns d’accès les plus courants : analyse par utilisateur, par session et filtrage par type d’événement.

Structure des CTE

Le modèle utilise un pipeline CTE en cinq étapes :

events → with_clean_urls → with_session_metrics → with_channel → final
                                     ↑
                              event_items (jointure dans final)

Étape 1 : Sélection depuis la base

WITH events AS (
    SELECT
        event__key,
        session__key,
        event__date,
        event__timestamp_utc,
        user__pseudo_id,
        user__id,
        session__ga_id,
        session__number,
        event__name,
        page__location,
        page__title,
        event__source,
        event__medium,
        event__campaign,
        session__source,
        session__medium,
        session__campaign,
        event__engagement_time_msec,
        device__category,
        geo__country,
        transaction__revenue
    FROM {{ ref('base__ga4__events') }}

    {% if is_incremental() %}
    WHERE event__date >= DATE_SUB(CURRENT_DATE(), INTERVAL {{ lookback_days }} DAY)
    {% endif %}
)

La sélection explicite de colonnes plutôt que SELECT * rend le contrat du modèle clair. L’ajout d’une nouvelle colonne au modèle de base n’apparaîtra pas silencieusement dans les modèles en aval.

Étape 2 : Nettoyage des URL

with_clean_urls AS (
    SELECT
        *,
        REGEXP_REPLACE(
            SPLIT(page__location, '?')[SAFE_OFFSET(0)],
            r'#.*$',
            ''
        ) AS page__path
    FROM events
)

page__path supprime les paramètres de requête et les fragments de page__location. L’analyse des pages d’atterrissage avec des URL complètes est bruitée — la même /blog/post visitée via différentes combinaisons UTM apparaîtrait comme des pages distinctes. L’utilisation du chemin seul rend les agrégations de pages d’atterrissage pertinentes.

SPLIT(url, '?')[SAFE_OFFSET(0)] obtient tout ce qui précède le ?. Le REGEXP_REPLACE suivant supprime les fragments (#nom-de-section). SAFE_OFFSET évite les erreurs lorsque l’URL n’a pas de ?.

Étape 3 : Métriques de session via les fonctions de fenêtrage

with_session_metrics AS (
    SELECT
        e.*,

        -- Navigation
        FIRST_VALUE(page__path IGNORE NULLS) OVER w AS session__landing_page,
        FIRST_VALUE(page__location IGNORE NULLS) OVER w AS session__landing_page_full,
        LAST_VALUE(page__path IGNORE NULLS) OVER w AS session__exit_page,

        -- Source de trafic (première valeur non nulle dans la session)
        FIRST_VALUE(
            COALESCE(event__source, session__source) IGNORE NULLS
        ) OVER w AS session__source_final,
        FIRST_VALUE(
            COALESCE(event__medium, session__medium) IGNORE NULLS
        ) OVER w AS session__medium_final,
        FIRST_VALUE(
            COALESCE(event__campaign, session__campaign) IGNORE NULLS
        ) OVER w AS session__campaign_final,

        -- Compteurs d'engagement
        COUNT(*) OVER p AS session__events,
        COUNT(CASE WHEN event__name = 'page_view' THEN 1 END) OVER p
            AS session__pageviews,
        SUM(event__engagement_time_msec) OVER p AS session__engagement_time_msec,

        -- Durée de session
        TIMESTAMP_DIFF(
            MAX(event__timestamp_utc) OVER p,
            MIN(event__timestamp_utc) OVER p,
            SECOND
        ) AS session__duration_seconds,

        -- Indicateurs de conversion
        MAX(CASE WHEN event__name = 'purchase' THEN 1 ELSE 0 END) OVER p
            AS session__has_purchase,
        MAX(CASE WHEN event__name = 'add_to_cart' THEN 1 ELSE 0 END) OVER p
            AS session__has_add_to_cart,
        MAX(CASE WHEN event__name = 'begin_checkout' THEN 1 ELSE 0 END) OVER p
            AS session__has_checkout,
        MAX(CASE WHEN event__name = 'sign_up' THEN 1 ELSE 0 END) OVER p
            AS session__has_signup,
        MAX(CASE WHEN event__name = 'generate_lead' THEN 1 ELSE 0 END) OVER p
            AS session__has_lead,

        -- Revenus
        SUM(transaction__revenue) OVER p AS session__revenue,

        -- Séquençage des événements
        ROW_NUMBER() OVER w AS event__sequence_number,
        CASE
            WHEN ROW_NUMBER() OVER w = 1 THEN TRUE
            ELSE FALSE
        END AS event__is_session_start,
        CASE
            WHEN ROW_NUMBER() OVER (
                PARTITION BY session__key ORDER BY event__timestamp_utc DESC
            ) = 1 THEN TRUE
            ELSE FALSE
        END AS event__is_session_end,

        -- Temps entre les événements
        TIMESTAMP_DIFF(
            event__timestamp_utc,
            LAG(event__timestamp_utc) OVER w,
            SECOND
        ) AS event__seconds_since_previous

    FROM with_clean_urls 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 encodent les deux types de calculs à portée de session :

w (ordonnée) — pour FIRST_VALUE, LAST_VALUE, ROW_NUMBER, LAG. Nécessite un ordre et le cadre complet.
p (partition seulement) — pour MAX, COUNT, SUM. Pas d’ordre requis ; scanne naturellement toute la partition.

Voir Pièges des fonctions de fenêtrage GA4 pour expliquer pourquoi le cadre ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING est requis pour LAST_VALUE.

Étape 4 : Groupement de canaux

with_channel AS (
    SELECT
        *,
        {{ default_channel_grouping('session__source_final', 'session__medium_final') }}
            AS session__channel_grouping
    FROM with_session_metrics
)

Le groupement de canaux est une CTE séparée parce que la macro génère une instruction CASE multiligne. L’isoler rend le modèle lisible et facilite le remplacement par une macro de groupement de canaux personnalisée.

Étape 5 : Jointure d’agrégation des articles

event_items AS (
    SELECT
        event__key,
        COUNT(*) AS event__items,
        SUM(item__revenue) AS event__items_revenue
    FROM {{ ref('int__ga4__event_items') }}
    {% if is_incremental() %}
    WHERE event__date >= DATE_SUB(CURRENT_DATE(), INTERVAL {{ lookback_days }} DAY)
    {% endif %}
    GROUP BY event__key
),

final AS (
    SELECT
        e.*,
        COALESCE(i.event__items, 0) AS event__items,
        COALESCE(i.event__items_revenue, 0) AS event__items_revenue
    FROM with_channel e
    LEFT JOIN event_items i ON e.event__key = i.event__key
)

SELECT * FROM final

Les données d’articles sont agrégées au grain événement avant la jointure, évitant l’expansion de lignes. Le COALESCE garantit que les événements non-e-commerce affichent 0 article plutôt que null.

Résolution de la source de trafic

Le pattern COALESCE(event__source, session__source) gère deux emplacements différents où GA4 stocke les informations de source :

event__source — depuis event_params, présent sur les événements balisés avec des paramètres UTM
session__source — depuis collected_traffic_source, la source de session au niveau session de GA4

Les implémentations GA4 modernes (juillet 2024+) utilisent session_traffic_source_last_click pour une attribution de session propre. Pour la compatibilité avec les données plus anciennes ou les propriétés non mises à jour, l’approche COALESCE capte le champ source qui est renseigné. Voir Champs de source de trafic GA4 pour la vue complète des champs d’attribution de GA4.

Ce que les modèles en aval obtiennent

Avec int__ga4__events_sessionized comme source, les modèles de mart n’ont besoin que de simples agrégations :

-- Mart de session : simple GROUP BY
SELECT
    session__key,
    ANY_VALUE(session__landing_page) AS session__landing_page,
    ANY_VALUE(session__channel_grouping) AS session__channel_grouping,
    ANY_VALUE(session__has_purchase) AS session__has_purchase,
    COUNT(*) AS session__events
FROM int__ga4__events_sessionized
GROUP BY session__key

Les questions au niveau événement qui nécessiteraient des jointures contre des tables de session séparées fonctionnent maintenant comme de simples filtres :

-- Événements dans les sessions convertissantes
SELECT * FROM int__ga4__events_sessionized
WHERE session__has_purchase = 1
  AND event__name != 'purchase'
ORDER BY session__key, event__sequence_number

Un modèle intermédiaire avec le contexte de session pré-calculé permet une analyse flexible au niveau événement et au niveau session sans jointures dans les modèles en aval.