Adrienne Vermorel

GA4 User Stitching : réconcilier utilisateurs anonymes et authentifiés

30 janvier 2026

Cet article est le 4e d’une série de 5 sur GA4 et BigQuery. Les articles précédents traitaient de la référence du schéma, des patterns d’unnesting, et des tables de sessions.

Quand un utilisateur navigue anonymement sur votre site puis se connecte sur un autre appareil quelques jours plus tard, l’export BigQuery de GA4 les traite comme deux personnes distinctes. Contrairement à l’interface GA4, qui applique l’identity resolution en arrière-plan, BigQuery reçoit les données brutes sans aucune réconciliation. Vos comptages d’utilisateurs sont gonflés, vos modèles d’attribution fragmentés, et vos calculs de lifetime value passent à côté du tableau complet.

La solution consiste à construire votre propre pipeline d’identity resolution. Bien conçue, la réconciliation peut récupérer une partie des sessions qui apparaîtraient autrement comme fragmentées. Mal conçue, elle crée des problèmes d’intégrité des données qui érodent la confiance dans vos analytics.

Cet article présente l’implémentation complète : comprendre le modèle d’identité de GA4, les patterns SQL pour différentes approches de réconciliation, la gestion des cas limites et la construction de modèles dbt prêts pour la production.

Comprendre les deux systèmes d’identité de GA4

GA4 suit les utilisateurs à travers deux identifiants fondamentalement différents qui apparaissent dans chaque ligne d’événement BigQuery. Comprendre leur comportement est essentiel avant d’écrire la moindre logique de réconciliation.

`user_pseudo_id` : l’identifiant de l’appareil

Le user_pseudo_id est l’identifiant au niveau de l’appareil de GA4 (essentiellement le même concept que le Client ID d’Universal Analytics). Sur le web, il est dérivé du cookie first-party _ga avec un format comme 1197596843.1673515099 (nombre aléatoire + timestamp). Sur les applications mobiles, c’est le Firebase App Instance ID, unique par installation.

Cet identifiant persiste jusqu’à deux ans par défaut, mais les fonctionnalités de confidentialité des navigateurs limitent de plus en plus cette durée :

L’ITP de Safari limite les cookies définis par JavaScript à environ 7 jours
Chrome plafonne désormais les cookies à 400 jours
Les sessions de navigation privée génèrent des ID éphémères

Le user_pseudo_id est toujours présent dans BigQuery. C’est l’identifiant de repli de GA4 quand aucune identité authentifiée n’existe.

`user_id` : l’identifiant authentifié

Le user_id est l’identifiant fourni par votre application, envoyé uniquement quand vous l’implémentez explicitement :

// Implémentation gtag.js
gtag('config', 'G-XXXXXXX', {
  'user_id': 'USER_12345'
});

// Ou via le dataLayer GTM
dataLayer.push({
  'user_id': 'USER_12345'
});

Contrairement au user_pseudo_id, GA4 ne génère pas cet identifiant automatiquement. Vous devez le capturer depuis votre système d’authentification. Quand les utilisateurs ne sont pas connectés, ce champ est NULL.

Important : n’envoyez jamais de données personnelles comme des adresses email directement. Utilisez plutôt des ID de base de données hashés ou anonymisés.

-- Examiner les champs d'identité dans vos données
SELECT
    user_pseudo_id,
    user_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
    event_name,
    TIMESTAMP_MICROS(event_timestamp) AS event_time
FROM `project.analytics_XXXXX.events_*`
WHERE _table_suffix = FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)
LIMIT 100

Pourquoi BigQuery diffère des rapports de l’interface GA4

L’interface GA4 applique automatiquement l’identity resolution via le « Reporting Identity ». La plateforme propose trois modes :

Mode	Ordre de résolution
Blended	User ID → Device ID → Modélisation
Observed	User ID → Device ID
Device-based	Device ID uniquement

Pour le travail sur BigQuery, le point clé est que rien de cette logique de réconciliation ne se transfère à votre export. BigQuery reçoit les données brutes, événement par événement, avec le user_id qui était présent au moment de la collecte. Pas de modélisation, pas de résolution cross-device, pas de données Google Signals.

Quand vous comparez l’interface GA4 aux requêtes BigQuery, attendez-vous à des écarts dus à :

La modélisation comportementale dans l’interface (BigQuery n’exporte pas les données modélisées)
Les données Google Signals (jamais présentes dans BigQuery)
L’estimation HyperLogLog++ pour les comptages de forte cardinalité dans l’interface

Pour le travail d’analytics engineering, considérez BigQuery comme votre source de vérité et acceptez que les chiffres de l’interface diffèrent.

Techniques de réconciliation avec BigQuery SQL

Il n’existe pas d’approche de réconciliation « correcte » unique. Le bon choix dépend de votre cas d’usage, de la qualité des données et de votre tolérance aux faux positifs. Voici les quatre patterns principaux.

Attribution last-touch

L’attribution last-touch assigne le user_id connu le plus récent à tous les événements historiques du même user_pseudo_id. Cela gère le flux typique : navigation anonyme suivie d’une authentification.

WITH events_with_last_touch AS (
    SELECT
        event_date,
        user_pseudo_id,
        user_id,
        event_name,
        event_timestamp,
        LAST_VALUE(user_id IGNORE NULLS) OVER (
            PARTITION BY user_pseudo_id
            ORDER BY event_timestamp
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS stitched_user_id
    FROM `project.analytics_XXXXX.events_*`
    WHERE _table_suffix BETWEEN '20250101' AND '20250131'
)

SELECT
    event_date,
    user_pseudo_id,
    user_id AS original_user_id,
    COALESCE(stitched_user_id, user_pseudo_id) AS resolved_user_id,
    event_name,
    TIMESTAMP_MICROS(event_timestamp) AS event_time
FROM events_with_last_touch

La clause IGNORE NULLS est essentielle. Sans elle, LAST_VALUE retourne NULL chaque fois que le user_id de la ligne courante est null. La fenêtre ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW garantit que vous examinez tous les événements précédents de la partition.

Ceci ne réconcilie que les événements avant le moment d’identification. Les événements après l’authentification ont déjà le user_id renseigné. Si vous devez connecter l’activité d’un utilisateur à travers plusieurs sessions authentifiées, vous avez besoin d’un backstitching complet.

Backstitching complet via table de mapping

Pour une identity resolution complète (appliquer un user_id à tous les événements d’un appareil, peu importe quand l’authentification a eu lieu), construisez d’abord une table de mapping :

-- Étape 1 : Construire le mapping d'identité
CREATE OR REPLACE TABLE `project.dataset.user_identity_mapping` AS
WITH identity_pairs AS (
    SELECT
        user_pseudo_id,
        user_id,
        MAX(event_timestamp) AS last_seen_timestamp
    FROM `project.analytics_XXXXX.events_*`
    WHERE user_id IS NOT NULL
      AND user_pseudo_id IS NOT NULL
    GROUP BY user_pseudo_id, user_id
)

SELECT
    user_pseudo_id,
    -- Prendre le user_id le plus récent s'il en existe plusieurs
    ARRAY_AGG(user_id ORDER BY last_seen_timestamp DESC LIMIT 1)[SAFE_OFFSET(0)] AS resolved_user_id,
    ARRAY_AGG(DISTINCT user_id) AS all_user_ids,
    MIN(TIMESTAMP_MICROS(last_seen_timestamp)) AS first_identified,
    MAX(TIMESTAMP_MICROS(last_seen_timestamp)) AS last_identified
FROM identity_pairs
GROUP BY user_pseudo_id

-- Étape 2 : Appliquer la réconciliation via JOIN
SELECT
    e.event_date,
    e.user_pseudo_id,
    e.user_id AS original_user_id,
    COALESCE(m.resolved_user_id, e.user_pseudo_id) AS stitched_user_id,
    e.event_name
FROM `project.analytics_XXXXX.events_*` e
LEFT JOIN `project.dataset.user_identity_mapping` m
    USING (user_pseudo_id)
WHERE e._table_suffix BETWEEN '20250101' AND '20250131'

Cela applique le user_id identifié à chaque événement de cet appareil (passé, présent et futur), permettant une véritable analyse cross-session.

Attribution first-touch

L’attribution first-touch utilise FIRST_VALUE au lieu de LAST_VALUE, assignant le user_id connu le plus ancien à tous les événements. Cette approche convient à l’analyse d’acquisition où vous voulez attribuer l’utilisateur à son identité authentifiée d’origine, pas aux changements de compte ultérieurs.

SELECT
    event_date,
    user_pseudo_id,
    user_id,
    event_name,
    event_timestamp,
    FIRST_VALUE(user_id IGNORE NULLS) OVER (
        PARTITION BY user_pseudo_id
        ORDER BY event_timestamp
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS first_known_user_id
FROM `project.analytics_XXXXX.events_*`

Utilisez le first-touch pour analyser les parcours de conversion d’anonyme à utilisateur connu. Utilisez le last-touch quand l’identité actuelle compte le plus (personnalisation, statut de compte actuel, etc.).

Réconciliation au niveau de la session

La réconciliation au niveau de la session limite la résolution d’identité aux événements d’une même session. Cette approche conservative respecte la frontière naturelle que GA4 trace autour du comportement utilisateur.

WITH session_events AS (
    SELECT
        event_date,
        user_pseudo_id,
        user_id,
        event_name,
        event_timestamp,
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id
    FROM `project.analytics_XXXXX.events_*`
    WHERE _table_suffix = FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)
),

stitched AS (
    SELECT
        *,
        FIRST_VALUE(user_id IGNORE NULLS) OVER (
            PARTITION BY user_pseudo_id, ga_session_id
            ORDER BY event_timestamp
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS session_user_id
    FROM session_events
)

SELECT
    event_date,
    user_pseudo_id,
    user_id,
    event_name,
    event_timestamp,
    ga_session_id,
    session_user_id,
    COALESCE(session_user_id, user_pseudo_id) AS resolved_user_id,
    -- Créer un identifiant de session vraiment unique
    CONCAT(
        COALESCE(session_user_id, user_pseudo_id),
        '_',
        CAST(ga_session_id AS STRING)
    ) AS unique_session_id
FROM stitched

Le ga_session_id n’est pas globalement unique. C’est le timestamp Unix du début de session. Concaténez-le toujours avec le user_pseudo_id (ou votre ID réconcilié) pour créer un identifiant de session vraiment unique.

Choisir la bonne approche

Technique	Portée	Niveau de risque	Idéal pour
Last-touch	Prospectif	Faible	Analytics de base, comportement récent
First-touch	Tous les événements	Moyen	Attribution d’acquisition
Backstitching complet	Tous les événements	Plus élevé	Cross-device, analyse LTV
Session-scoped	Session unique	Le plus faible	Approche conservative, tests

Commencez par la réconciliation au niveau de la session pour valider votre logique, puis passez au backstitching complet une fois la qualité des données confirmée.

Identity resolution cross-device

La réconciliation des utilisateurs devient particulièrement précieuse pour connecter les parcours à travers les appareils. Un même utilisateur authentifié peut avoir trois valeurs user_pseudo_id : une depuis son téléphone, une depuis son laptop et une depuis son ordinateur professionnel. Votre table de mapping d’identité est la base pour unifier tout cela.

Construire une table de mapping d’identité pour la production

Le schéma optimal stocke la relation du user_id vers tous les appareils associés :

CREATE OR REPLACE TABLE `project.dataset.identity_graph` AS
WITH device_user_pairs AS (
    SELECT
        user_id,
        user_pseudo_id,
        MIN(event_timestamp) AS first_seen,
        MAX(event_timestamp) AS last_seen,
        COUNT(*) AS event_count
    FROM `project.analytics_XXXXX.events_*`
    WHERE user_id IS NOT NULL
      AND user_pseudo_id IS NOT NULL
    GROUP BY user_id, user_pseudo_id
)

SELECT
    user_id,
    ARRAY_AGG(
        STRUCT(
            user_pseudo_id,
            TIMESTAMP_MICROS(first_seen) AS first_seen,
            TIMESTAMP_MICROS(last_seen) AS last_seen,
            event_count
        )
        ORDER BY last_seen DESC
        LIMIT 100  -- Éviter l'explosion due aux bugs de tracking
    ) AS devices,
    COUNT(DISTINCT user_pseudo_id) AS device_count,
    MIN(TIMESTAMP_MICROS(first_seen)) AS user_first_seen,
    MAX(TIMESTAMP_MICROS(last_seen)) AS user_last_active
FROM device_user_pairs
GROUP BY user_id

Définissez une limite d’identifiants par utilisateur (environ 100). Si un seul user_id accumule des milliers de valeurs user_pseudo_id, quelque chose ne va pas. Causes possibles : un device ID réinitialisé à chaque chargement de page, ou un compte de test réutilisé.

Pour les requêtes, vous aurez souvent besoin du mapping inverse (appareil → utilisateur) :

CREATE OR REPLACE TABLE `project.dataset.device_to_user_mapping` AS
SELECT
    device.user_pseudo_id,
    user_id,
    device.first_seen,
    device.last_seen
FROM `project.dataset.identity_graph`,
UNNEST(devices) AS device

Gérer les utilisateurs qui ne s’authentifient jamais

Tous les visiteurs ne se connecteront pas. Votre logique de réconciliation doit prendre en charge les utilisateurs anonymes sans difficulté :

SELECT
    COALESCE(m.user_id, e.user_pseudo_id) AS resolved_user_id,
    CASE
        WHEN m.user_id IS NOT NULL THEN 'identified'
        ELSE 'anonymous'
    END AS identity_status,
    e.event_date,
    e.user_pseudo_id,
    e.user_id,
    e.event_name,
    e.event_timestamp
FROM `project.analytics_XXXXX.events_*` e
LEFT JOIN `project.dataset.device_to_user_mapping` m
    USING (user_pseudo_id)

Acceptez que les utilisateurs anonymes restent des identités séparées. Ne forcez pas le matching probabiliste pour gonfler votre pourcentage d’utilisateurs identifiés. Les faux positifs n’en valent probablement pas la peine.

Pourquoi le matching probabiliste échoue avec GA4

Vous pourriez envisager d’augmenter les taux de correspondance via le fingerprinting (matching des utilisateurs par caractéristiques d’appareil, localisation et patterns comportementaux). Cette approche présente des limitations sévères avec les données GA4.

Données manquantes

L’export BigQuery de GA4 exclut intentionnellement les signaux typiquement utilisés pour le matching probabiliste :

Pas d’adresses IP
Pas de chaînes user agent
Pas de canvas fingerprints
Pas d’identifiants hardware

Ce à quoi vous avez accès :

SELECT DISTINCT
    device.category,           -- mobile, desktop, tablet
    device.operating_system,   -- Android, iOS, Windows, etc.
    device.browser,            -- Chrome, Safari, etc.
    device.language,
    geo.country,
    geo.city,
    geo.metro
FROM `project.analytics_XXXXX.events_*`
WHERE _table_suffix = FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)

Ces signaux grossiers créent bien trop de faux positifs pour un usage en production. Combien d’utilisateurs dans une ville moyenne utilisent Chrome sur un MacBook ? Des milliers.

Le coût des faux positifs

Les profils incorrectement fusionnés s’accumulent avec le temps. Quand vous fusionnez l’historique de navigation de l’utilisateur A avec l’historique d’achats de l’utilisateur B :

Les recommandations deviennent incohérentes
La personnalisation email fait référence à des produits jamais consultés
Les modèles d’attribution créditent les mauvais points de contact
Le support client voit un historique d’activité confus

Tenez-vous-en au matching déterministe via le user_id. Le taux de correspondance plus faible vaut l’intégrité des données.

Le Consent Mode V2 de GA4 change fondamentalement les données qui atteignent BigQuery. Quand analytics_storage est refusé, le comportement dépend de votre mode d’implémentation.

Avec le Consent Mode Basic, un consentement refusé signifie aucune collecte de données. Rien n’atteint BigQuery.

Avec le Consent Mode Advanced, GA4 envoie des « cookieless pings » (événements sans identifiants). Dans BigQuery, ils apparaissent avec :

NULL pour le user_pseudo_id
Pas de ga_session_id dans event_params
privacy_info.analytics_storage = 'No'

Ces événements ne peuvent être ni réconciliés ni sessionisés.

-- Vérifier la distribution du consentement dans vos données
SELECT
    privacy_info.analytics_storage AS consent_status,
    COUNT(*) AS events,
    COUNT(DISTINCT user_pseudo_id) AS unique_devices,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 2) AS pct_of_total
FROM `project.analytics_XXXXX.events_*`
WHERE _table_suffix >= FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 30)
GROUP BY privacy_info.analytics_storage
ORDER BY events DESC

La nuance du backstitch sur la même page

Si un utilisateur accorde son consentement sur la même page où il l’avait initialement refusé, GA4 applique rétroactivement le Client ID aux hits précédemment refusés de cette page. Cela apparaît correctement dans les tables journalières mais peut montrer des incohérences dans les tables intraday.

Ce backstitching sur la même page ne fonctionne pas entre les chargements de page. Une fois que quelqu’un navigue ailleurs avec un consentement refusé, ces événements restent définitivement anonymes.

Approche pratique

Pour une analyse propre, filtrez sur les données avec consentement :

SELECT
    event_date,
    user_pseudo_id,
    user_id,
    event_name,
    event_timestamp
FROM `project.analytics_XXXXX.events_*`
WHERE privacy_info.analytics_storage = 'Yes'
   OR privacy_info.analytics_storage IS NULL  -- Données pré-consent-mode

Acceptez que les chiffres BigQuery soient inférieurs aux rapports de l’interface GA4. L’interface applique une modélisation comportementale pour estimer ce qu’auraient fait les utilisateurs ayant refusé le consentement.

Cas limites qui cassent les implémentations naïves

L’identity resolution en production doit gérer des scénarios que les patterns SQL simples ratent.

Appareils partagés

Quand plusieurs utilisateurs s’authentifient sur le même navigateur (ordinateur familial, poste de travail partagé, borne de vente), un seul user_pseudo_id correspond à plusieurs valeurs user_id. La réconciliation last-touch naïve attribue incorrectement la navigation anonyme d’un utilisateur à un autre.

-- Détecter les appareils partagés
SELECT
    user_pseudo_id,
    COUNT(DISTINCT user_id) AS distinct_users,
    ARRAY_AGG(DISTINCT user_id IGNORE NULLS ORDER BY user_id) AS user_ids
FROM `project.analytics_XXXXX.events_*`
WHERE user_id IS NOT NULL
GROUP BY user_pseudo_id
HAVING COUNT(DISTINCT user_id) > 1
ORDER BY distinct_users DESC
LIMIT 100

Définissez un seuil, communément 3 utilisateurs distincts, et soit :

Excluez ces appareils de la réconciliation cross-session entièrement
Marquez-les et n’appliquez que la réconciliation au niveau de la session
Pondérez leur contribution plus faiblement dans les métriques agrégées

-- Créer un flag d'appareil partagé pour votre table de mapping
SELECT
    user_pseudo_id,
    COUNT(DISTINCT user_id) AS user_count,
    COUNT(DISTINCT user_id) > 3 AS is_shared_device
FROM `project.analytics_XXXXX.events_*`
WHERE user_id IS NOT NULL
GROUP BY user_pseudo_id

Erreurs d’implémentation de la déconnexion

Un bug courant consiste à définir le user_id à la chaîne 'null', '-', 'none' ou 'anonymous' au lieu d’un vrai null. Cela fait que GA4 traite toutes les sessions déconnectées comme appartenant à un seul utilisateur nommé « null ».

-- Trouver les valeurs user_id suspectes
SELECT
    user_id,
    COUNT(*) AS events,
    COUNT(DISTINCT user_pseudo_id) AS devices
FROM `project.analytics_XXXXX.events_*`
WHERE user_id IS NOT NULL
GROUP BY user_id
HAVING COUNT(DISTINCT user_pseudo_id) > 1000  -- Suspicieusement élevé
ORDER BY devices DESC

Si vous trouvez cela dans vos données, travaillez avec votre équipe d’implémentation pour corriger le tagging. En attendant, filtrez ces valeurs :

WHERE user_id IS NOT NULL
  AND user_id NOT IN ('null', 'undefined', 'none', '-', 'anonymous', '')
  AND LENGTH(user_id) > 3  -- Filtrer les valeurs manifestement erronées

Plusieurs valeurs `user_id` par personne

Les vrais utilisateurs ont parfois plusieurs identités authentifiées :

Fusion de comptes après une acquisition
Changement d’adresse email
Comptes professionnels et personnels séparés
Comptes de test utilisés en parallèle des vrais comptes

Votre logique de réconciliation a besoin d’une règle de décision claire :

-- Option A : Le user_id le plus récent gagne
ARRAY_AGG(user_id ORDER BY last_seen DESC LIMIT 1)[SAFE_OFFSET(0)]

-- Option B : Le premier user_id gagne (identité stable)
ARRAY_AGG(user_id ORDER BY first_seen ASC LIMIT 1)[SAFE_OFFSET(0)]

-- Option C : Le user_id le plus actif gagne
ARRAY_AGG(user_id ORDER BY event_count DESC LIMIT 1)[SAFE_OFFSET(0)]

Documentez votre choix et appliquez-le de manière cohérente.

Réinitialisations de cookies et fragmentation

Les utilisateurs qui effacent leurs cookies, utilisent la navigation privée ou ont les restrictions ITP de Safari génèrent de nouvelles valeurs user_pseudo_id de manière imprévisible. S’ils s’authentifient plus tard, votre table de mapping reconnecte ces identités. Mais les sessions anonymes avant la réinitialisation restent orphelines. C’est inévitable sans matching probabiliste.

Suivez les taux de fragmentation pour comprendre la qualité de vos données :

-- Utilisateurs avec plusieurs appareils (fragmentation potentielle ou cross-device)
SELECT
    device_count,
    COUNT(*) AS users,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 2) AS pct
FROM (
    SELECT
        user_id,
        COUNT(DISTINCT user_pseudo_id) AS device_count
    FROM `project.analytics_XXXXX.events_*`
    WHERE user_id IS NOT NULL
    GROUP BY user_id
)
GROUP BY device_count
ORDER BY device_count

Implémentation production avec dbt

Pour les pipelines de production, structurez votre identity resolution comme un vrai DAG dbt avec un traitement incrémental.

Le modèle de mapping d’identité

-- models/intermediate/ga4/int__ga4__identity_mapping.sql

{{
    config(
        materialized='incremental',
        unique_key='user_pseudo_id',
        incremental_strategy='merge',
        partition_by={
            'field': 'last_seen_date',
            'data_type': 'date',
            'granularity': 'day'
        },
        cluster_by=['user_pseudo_id']
    )
}}

WITH source_events AS (
    SELECT
        user_pseudo_id,
        user_id,
        event_timestamp,
        PARSE_DATE('%Y%m%d', event_date) AS event_date
    FROM {{ source('ga4', 'events') }}
    WHERE user_pseudo_id IS NOT NULL
      AND user_id IS NOT NULL
      AND user_id NOT IN ('null', 'undefined', 'none', '')
    {% if is_incremental() %}
      AND PARSE_DATE('%Y%m%d', event_date) >= DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY)
    {% endif %}
),

aggregated AS (
    SELECT
        user_pseudo_id,
        ARRAY_AGG(DISTINCT user_id IGNORE NULLS) AS all_user_ids,
        ARRAY_AGG(user_id ORDER BY event_timestamp DESC LIMIT 1)[SAFE_OFFSET(0)] AS latest_user_id,
        MAX(event_timestamp) AS last_seen_timestamp,
        MAX(event_date) AS last_seen_date
    FROM source_events
    GROUP BY user_pseudo_id
)

SELECT
    user_pseudo_id,
    latest_user_id AS resolved_user_id,
    all_user_ids,
    ARRAY_LENGTH(all_user_ids) AS user_id_count,
    ARRAY_LENGTH(all_user_ids) > 1 AS has_multiple_users,
    TIMESTAMP_MICROS(last_seen_timestamp) AS last_seen_at,
    last_seen_date
FROM aggregated

La fenêtre de lookback de 3 jours gère les données arrivant en retard tout en gardant les exécutions incrémentales efficaces.

Le modèle des événements réconciliés

-- models/intermediate/ga4/int__ga4__events_stitched.sql

{{
    config(
        materialized='incremental',
        incremental_strategy='insert_overwrite',
        partition_by={
            'field': 'event_date',
            'data_type': 'date',
            'granularity': 'day'
        },
        cluster_by=['resolved_user_id', 'session_id']
    )
}}

WITH events AS (
    SELECT
        user_pseudo_id,
        user_id,
        event_name,
        event_timestamp,
        event_params,
        PARSE_DATE('%Y%m%d', event_date) AS event_date_parsed,
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id
    FROM {{ source('ga4', 'events') }}
    WHERE TRUE
    {% if is_incremental() %}
      AND PARSE_DATE('%Y%m%d', event_date) >= DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY)
    {% endif %}
),

identity_mapping AS (
    SELECT
        user_pseudo_id,
        resolved_user_id,
        has_multiple_users
    FROM {{ ref('int__ga4__identity_mapping') }}
)

SELECT
    e.user_pseudo_id,
    e.user_id,
    e.event_name,
    e.event_timestamp,
    e.event_date_parsed AS event_date,

    -- Identity resolution
    COALESCE(m.resolved_user_id, e.user_pseudo_id) AS resolved_user_id,
    m.resolved_user_id IS NOT NULL AS is_identified,
    COALESCE(m.has_multiple_users, FALSE) AS is_shared_device,

    -- Identifiants de session
    e.ga_session_id,
    CONCAT(
        COALESCE(m.resolved_user_id, e.user_pseudo_id),
        '_',
        CAST(e.ga_session_id AS STRING)
    ) AS session_id

FROM events e
LEFT JOIN identity_mapping m
    USING (user_pseudo_id)

Structure du DAG

sources/ga4/events
    │
    ├── int__ga4__identity_mapping (incremental, merge)
    │
    └── int__ga4__events_stitched (incremental, insert_overwrite)
            │
            ├── int__ga4__sessions
            │
            └── mrt__analytics__user_journey

Tests de schéma

models:
  - name: int__ga4__identity_mapping
    columns:
      - name: user_pseudo_id
        tests:
          - unique
          - not_null
      - name: resolved_user_id
        tests:
          - not_null
      - name: user_id_count
        tests:
          - dbt_utils.accepted_range:
              min_value: 1
              max_value: 100  # Alerte en cas d'explosion

  - name: int__ga4__events_stitched
    columns:
      - name: session_id
        tests:
          - not_null
      - name: resolved_user_id
        tests:
          - not_null

Validation et monitoring

L’identity resolution nécessite un monitoring continu. Les bugs dans votre logique de réconciliation peuvent corrompre silencieusement des mois de données.

Tableau de bord des métriques clés

-- Métriques quotidiennes de santé de l'identity resolution
WITH daily_stats AS (
    SELECT
        event_date,
        COUNT(*) AS total_events,
        COUNTIF(is_identified) AS identified_events,
        COUNTIF(is_shared_device) AS shared_device_events,
        COUNT(DISTINCT resolved_user_id) AS unique_users,
        COUNT(DISTINCT user_pseudo_id) AS unique_devices,
        COUNT(DISTINCT session_id) AS unique_sessions
    FROM {{ ref('int__ga4__events_stitched') }}
    WHERE event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
    GROUP BY event_date
)

SELECT
    event_date,
    total_events,
    unique_users,
    unique_devices,
    unique_sessions,

    -- Taux de réconciliation : % d'événements avec identité résolue
    ROUND(identified_events * 100.0 / total_events, 2) AS stitch_rate_pct,

    -- Consolidation des appareils : de combien la réconciliation réduit le comptage
    ROUND((unique_devices - unique_users) * 100.0 / unique_devices, 2) AS consolidation_rate_pct,

    -- Exposition aux appareils partagés
    ROUND(shared_device_events * 100.0 / total_events, 2) AS shared_device_pct

FROM daily_stats
ORDER BY event_date DESC

Détection d’anomalies

Configurez des alertes pour les changements soudains qui indiquent des bugs ou des problèmes d’implémentation :

-- Alerte : le taux de réconciliation a chuté de plus de 10% d'une semaine à l'autre
WITH weekly AS (
    SELECT
        DATE_TRUNC(event_date, WEEK) AS week,
        COUNTIF(is_identified) * 100.0 / COUNT(*) AS stitch_rate
    FROM {{ ref('int__ga4__events_stitched') }}
    WHERE event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 8 WEEK)
    GROUP BY 1
)

SELECT
    week,
    stitch_rate,
    LAG(stitch_rate) OVER (ORDER BY week) AS prev_week_rate,
    stitch_rate - LAG(stitch_rate) OVER (ORDER BY week) AS change
FROM weekly
HAVING ABS(change) > 10

Contrôles de qualité des données

-- Identifier les problèmes potentiels de qualité des données
SELECT
    'Excessive devices per user' AS issue,
    COUNT(*) AS affected_users
FROM {{ ref('int__ga4__identity_mapping') }}
WHERE user_id_count > 50

UNION ALL

SELECT
    'Suspiciously common user_id' AS issue,
    COUNT(DISTINCT user_pseudo_id) AS affected_devices
FROM {{ source('ga4', 'events') }}
WHERE user_id IN (
    SELECT user_id
    FROM {{ source('ga4', 'events') }}
    WHERE user_id IS NOT NULL
    GROUP BY user_id
    HAVING COUNT(DISTINCT user_pseudo_id) > 500
)

Quand NE PAS réconcilier

L’identity resolution n’est pas toujours appropriée. Sachez quand utiliser les identifiants bruts à la place.

Préoccupations d’intégrité des données

Ne réconciliez pas quand votre système d’authentification a des cas limites non résolus :

Achat en tant qu’invité avec des ID temporaires/recyclés
Comptes de démonstration utilisés de manière répétée par les équipes commerciales
Tests des employés sur la production
Tracking de partenaires ou affiliés qui réutilise des ID

Validez votre implémentation du user_id avant de construire une logique de réconciliation par-dessus.

Analyses spécifiques aux appareils

Certaines analyses devraient utiliser le user_pseudo_id brut :

Optimisation de performance : temps de chargement par appareil/navigateur
Compatibilité navigateur : support des fonctionnalités et taux d’erreur
Analyse par catégorie d’appareil : taux de conversion mobile vs desktop
Détection de bots : identification des patterns de trafic non humain

Considérations légales et de confidentialité

L’identity resolution crée de nouvelles liaisons de données personnelles. Avant d’implémenter :

Vérifiez si votre politique de confidentialité couvre le tracking cross-device
Confirmez que les mécanismes de consentement couvrent l’identity resolution
Évaluez les implications de la rétention des données du graphe d’identité
Documentez la logique de réconciliation à des fins de conformité

Ceci n’est pas un avis juridique. Impliquez vos équipes juridiques et privacy.

Conclusion

La réconciliation des utilisateurs transforme les données GA4 fragmentées en parcours clients cohérents. Les principes clés :

Commencez prudemment : débutez avec la réconciliation au niveau de la session, passez au cross-session uniquement après avoir validé la qualité des données.
Utilisez uniquement le matching déterministe : résistez à la tentation de gonfler les taux de correspondance avec des approches probabilistes. Les faux positifs n’en valent pas la peine.
Gérez explicitement les cas limites : appareils partagés, bugs de déconnexion et lacunes de consentement nécessitent une logique spécifique, pas des hypothèses.
Monitorez en continu : taux de réconciliation, comptages d’appareils et détection d’anomalies doivent faire partie de votre infrastructure de qualité des données.
Documentez tout : quels marts utilisent des identités réconciliées vs brutes ? Quelle est votre règle de décision pour les valeurs user_id multiples ? Votre futur vous remerciera.

La table de mapping d’identité que vous construisez ici devient fondamentale pour les analyses en aval : les tables de sessions en ont besoin pour une attribution précise, les agrégations au niveau utilisateur en ont besoin pour les calculs LTV, et les modèles d’attribution en ont besoin pour la reconstruction des parcours cross-device.

Dans le dernier article de cette série, nous rassemblerons tout dans un template de projet dbt prêt pour la production, incluant les patterns d’identity resolution de cet article intégrés avec le schéma, l’unnesting et la logique de session des articles précédents.