Pattern de mart utilisateur GA4

Le mart utilisateur répond à la question que l’analyse au niveau session ne peut pas : à quoi ressemble le parcours complet d’un utilisateur sur l’ensemble de ses sessions ? Premier canal d’acquisition, revenus sur la durée de vie, récence, nombre de sessions — tout cela nécessite d’agréger les données de session au grain utilisateur et de résoudre le problème d’identité inhérent aux identifiants liés à l’appareil dans GA4.

Le problème d’identité

Le user_pseudo_id de GA4 est un identifiant au niveau appareil — un cookie first-party lié au navigateur ou à l’instance d’application. La même personne utilisant son téléphone et son ordinateur portable crée deux valeurs user_pseudo_id. Les sessions de différents appareils sont séparées dans le modèle de données de GA4.

Lorsque les utilisateurs s’authentifient (connexion, achat, inscription), GA4 peut enregistrer un user_id aux côtés du user_pseudo_id. Cet identifiant authentifié fournit le pont entre les appareils. Un utilisateur qui navigue sur mobile et achète sur ordinateur apparaît comme deux IDs d’appareil anonymes jusqu’à ce que l’authentification les relie.

Le mart utilisateur gère deux scénarios :

1. Utilisateurs non authentifiés : Seulement user_pseudo_id. Chaque appareil est un « utilisateur » distinct dans le mart.
2. Utilisateurs authentifiés : Un user_id peut être réconcilié sur plusieurs valeurs user_pseudo_id.

Le modèle

-- models/marts/ga4/mrt__analytics__users.sql

{{
    config(
        materialized='table',
        cluster_by=['user__first_seen_at']
    )
}}

WITH user_sessions AS (

    SELECT
        user__pseudo_id,
        user__id,
        session__key,
        event__date,
        session__started_at,
        session__channel_grouping,
        device__category,
        geo__country,
        session__has_purchase,
        session__revenue
    FROM {{ ref('mrt__analytics__sessions') }}

),

user_first_last AS (

    SELECT
        user__pseudo_id,

        -- Première vue
        MIN(event__date) AS user__first_seen_at,
        FIRST_VALUE(session__channel_grouping) OVER (
            PARTITION BY user__pseudo_id
            ORDER BY event__date
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS user__first_channel,
        FIRST_VALUE(device__category) OVER (
            PARTITION BY user__pseudo_id
            ORDER BY event__date
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS user__first_device,

        -- Dernière vue
        MAX(event__date) AS user__last_seen_at,
        LAST_VALUE(session__channel_grouping) OVER (
            PARTITION BY user__pseudo_id
            ORDER BY event__date
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS user__last_channel,

        -- Agrégats
        COUNT(DISTINCT session__key) AS user__sessions,
        MAX(session__has_purchase) AS user__has_purchased,
        SUM(session__revenue) AS user__lifetime_revenue

    FROM user_sessions
    GROUP BY user__pseudo_id

),

-- Réconciliation de l'identité authentifiée
user_identity AS (

    SELECT DISTINCT
        user__pseudo_id,
        FIRST_VALUE(user__id IGNORE NULLS) OVER (
            PARTITION BY user__pseudo_id
            ORDER BY event__date DESC
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS user__stitched_id
    FROM user_sessions
    WHERE user__id IS NOT NULL

)

SELECT
    u.user__pseudo_id,
    i.user__stitched_id AS user__id,
    u.user__first_seen_at,
    u.user__last_seen_at,
    u.user__first_channel,
    u.user__first_device,
    u.user__last_channel,
    u.user__sessions,
    u.user__has_purchased,
    u.user__lifetime_revenue,

    -- Métriques dérivées
    DATE_DIFF(u.user__last_seen_at, u.user__first_seen_at, DAY)
        AS user__lifespan_days,
    DATE_DIFF(CURRENT_DATE(), u.user__last_seen_at, DAY)
        AS user__days_since_last_visit

FROM user_first_last u
LEFT JOIN user_identity i ON u.user__pseudo_id = i.user__pseudo_id

La logique de réconciliation d’identité

La CTE user_identity implémente une forme simple de rétrocouture utilisateur GA4 :

FIRST_VALUE(user__id IGNORE NULLS) OVER (
    PARTITION BY user__pseudo_id
    ORDER BY event__date DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS user__stitched_id

ORDER BY event__date DESC avec FIRST_VALUE signifie « prendre le user_id le plus récent associé à ce user_pseudo_id ». IGNORE NULLS saute les sessions où l’utilisateur n’était pas authentifié. Le DISTINCT dans la requête externe déduplique le résultat en une ligne par user_pseudo_id.

Cette approche gère le cas où un utilisateur s’authentifie avec un user_id en cours d’histoire — toutes ses sessions, même les antérieures anonymes, reçoivent l’identité réconciliée.

La LEFT JOIN vers user_first_last garantit que les utilisateurs qui ne se sont jamais authentifiés apparaissent quand même dans le mart, avec un user__id null. Les utilisateurs non authentifiés ne sont pas perdus — ils n’ont simplement pas d’identifiant réconcilié.

Attribution premier et dernier contact

Les fonctions de fenêtrage sur user__first_channel et user__last_channel fournissent les deux questions d’attribution les plus courantes au grain utilisateur :

• user__first_channel : Quel canal a initialement acquis cet utilisateur ? Utile pour l’analyse de cohortes d’acquisition — comment les utilisateurs issus de la recherche payante se comportent-ils par rapport à l’organique sur leur durée de vie ?
• user__last_channel : Quel canal a généré la visite la plus récente ? Utile pour l’analyse de réengagement.

Les deux utilisent FIRST_VALUE et LAST_VALUE respectivement, avec un cadrage explicite de partition complète. Le pattern reproduit la logique de page d’atterrissage/sortie au niveau session du modèle sessionisé.

Note : ce sont le premier et le dernier contact au niveau session — le canal de la première/dernière session, pas le premier/dernier paramètre UTM d’événement. Pour une attribution plus fine, il faudrait regarder les sources au niveau événement depuis la table sessionisée.

Métriques dérivées

Deux colonnes dérivées rendent le mart immédiatement utile pour l’analyse de rétention :

DATE_DIFF(u.user__last_seen_at, u.user__first_seen_at, DAY)
    AS user__lifespan_days

Les utilisateurs avec user__lifespan_days = 0 sont des utilisateurs d’une seule session — ils ont visité une fois et ne sont jamais revenus. Une longue durée de vie avec peu de sessions suggère des utilisateurs fidèles mais peu fréquents. Une longue durée de vie avec beaucoup de sessions constitue votre cœur engagé.

DATE_DIFF(CURRENT_DATE(), u.user__last_seen_at, DAY)
    AS user__days_since_last_visit

C’est la récence dans un cadre RFM (Récence, Fréquence, Montant). Combinée à user__sessions (fréquence) et user__lifetime_revenue (montant), le mart dispose des trois composantes pour la segmentation RFM sans aucune jointure supplémentaire.

Limitations

Un mart par user_pseudo_id, pas par personne. Le mart regroupe par user_pseudo_id, qui est lié à l’appareil. Une seule personne utilisant trois appareils apparaît comme trois lignes, sauf si elle s’est authentifiée sur les trois. La résolution d’identité inter-appareils au-delà de la réconciliation user_id nécessite une correspondance probabiliste ou des données first-party non disponibles dans les exports GA4.

Le mart capture l’état actuel. Avec materialized='table', le mart est entièrement reconstruit à chaque exécution. Les métriques sur la durée de vie sont toujours à jour. Si vous avez besoin de snapshots à un moment précis (quelle était la valeur vie client d’un utilisateur à une date spécifique ?), vous devrez ajouter une logique incrémentielle basée sur la date ou maintenir l’historique via des snapshots dbt.

Pas de suivi SCD. Les attributs utilisateur comme user__first_channel sont calculés depuis des données historiques et stables. Mais user__last_channel et user__days_since_last_visit changent à chaque exécution. Si le suivi des changements d’attribution est important, implémentez un SCD de Type 2 via les snapshots dbt.