Graphe d'identité GA4 dans BigQuery

Un utilisateur authentifié peut avoir trois, quatre, voire plus de valeurs user_pseudo_id dans vos données GA4 : une par téléphone, une depuis son ordinateur professionnel, une depuis un ordinateur familial partagé. Le graphe d’identité capture toutes ces relations entre appareils et les relie à une identité métier unique.

Le graphe a deux formes à maintenir : centrée sur l’utilisateur (tous les appareils pour un user_id donné) et centrée sur l’appareil (le user_id pour un user_pseudo_id donné). Les requêtes ont besoin des deux directions.

Le graphe centré sur l’utilisateur

Le schéma de production optimal stocke les appareils sous forme de tableau STRUCT imbriqué dans chaque ligne utilisateur. Cela évite les jointures coûteuses pour la question courante « quels appareils cet utilisateur possède-t-il ? » :

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
      AND user_id NOT IN ('null', 'undefined', 'none', '')
    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  -- Garde-fou contre l'accumulation 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

La LIMIT 100 sur l’agrégation du tableau n’est pas arbitraire — c’est une contrainte de sécurité. Si un user_id accumule des milliers d’appareils, quelque chose ne va pas : l’équipe d’implémentation a réinitialisé l’ID de l’appareil à chaque chargement de page, un compte de test est réutilisé, ou un bug génère continuellement de nouvelles valeurs user_pseudo_id. Plafonner à 100 empêche ces bugs de faire enfler votre graphe d’identité et de dégrader les performances des requêtes. Surveiller les valeurs élevées de device_count est la façon de détecter ces bugs. Voir Monitoring de la résolution d’identité pour le SQL.

La correspondance inverse centrée sur l’appareil

Pour la réconciliation au niveau événement, vous avez besoin de la direction inverse : pour un user_pseudo_id donné, quel est le user_id résolu ? La correspondance centrée sur l’appareil est une table plate que vous joignez à vos événements :

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

C’est la table qui alimente la rétrocouture complète. Joignez-la à vos événements sur user_pseudo_id, et chaque événement d’un appareil qui s’est un jour authentifié porte l’identité résolue :

SELECT
    e.event_date,
    e.user_pseudo_id,
    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_name,
    e.event_timestamp
FROM `project.analytics_XXXXX.events_*` e
LEFT JOIN `project.dataset.device_to_user_mapping` m
    USING (user_pseudo_id)

La colonne identity_status mérite d’être persistée dans votre modèle d’événements réconciliés. Elle vous permet de comprendre quelle fraction des événements a des identités résolues et de suivre les évolutions dans le temps.

Gestion des multiples valeurs user_id par appareil

Les utilisateurs réels apparaissent parfois sous plusieurs valeurs user_id depuis le même appareil : fusions de comptes après acquisition, changements d’email, comptes personnels et professionnels séparés sur le même navigateur, ou comptes de test partageant une session. Votre graphe a besoin d’une règle de décision claire pour déterminer quel user_id prime.

Trois approches courantes :

-- Le plus récent : l'identité actuelle a la priorité
ARRAY_AGG(user_id ORDER BY last_seen DESC LIMIT 1)[SAFE_OFFSET(0)]

-- Le premier vu : identité stable, utile pour les cohortes d'acquisition
ARRAY_AGG(user_id ORDER BY first_seen ASC LIMIT 1)[SAFE_OFFSET(0)]

-- Le plus actif : l'identité avec le plus grand nombre d'événements observés
ARRAY_AGG(user_id ORDER BY event_count DESC LIMIT 1)[SAFE_OFFSET(0)]

Le plus récent est le choix par défaut le plus défendable — il reflète l’état actuel du compte. Documentez votre choix ; c’est une décision métier, pas technique. Les analystes futurs ont besoin de savoir pourquoi la réconciliation fonctionne comme elle le fait.

Détection des appareils partagés

Un user_pseudo_id qui correspond à plusieurs valeurs user_id indique un appareil partagé : un ordinateur familial, une borne interactive, ou un poste où plusieurs personnes se connectent à votre application. La rétrocouture naïve sur les appareils partagés attribue mal les sessions anonymes d’un utilisateur à celui qui s’est connecté en dernier.

La détection est simple :

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

Une fois détectés, ajoutez un indicateur à votre table de correspondance :

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

Le seuil de 3 est un choix pragmatique — un vrai appareil partagé peut avoir 2 utilisateurs, mais une implémentation réellement défectueuse génère typiquement des dizaines. Dans votre modèle d’événements réconciliés, exposez is_shared_device comme colonne afin que les modèles en aval puissent filtrer ou pondérer ces événements de manière appropriée.

Réinitialisations de cookies et sessions orphelines

Les utilisateurs qui effacent leurs cookies, passent en navigation privée, ou rencontrent les restrictions Safari ITP génèrent de nouvelles valeurs user_pseudo_id. S’ils s’authentifient ensuite avec le nouvel identifiant d’appareil, la table de correspondance les reconnecte. Mais les sessions anonymes avant la réinitialisation du cookie restent définitivement orphelines — il n’y a pas d’identifiant pour les relier en arrière.

Les restrictions de confidentialité des navigateurs font que ce n’est pas rare. ITP plafonne les cookies à 7 jours dans de nombreuses configurations. Vous pouvez suivre les taux de fragmentation pour comprendre l’ampleur de la perte d’identité :

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

Un utilisateur avec 5 valeurs user_pseudo_id pourrait signifier une utilisation multi-appareils (normale) ou un effacement agressif des cookies (sessions pré-réinitialisation perdues). La distribution vous indique l’ampleur du phénomène ; elle ne vous dit pas quelle en est la cause. Utilisez-la en parallèle de votre métrique de taux de réconciliation comme signal de santé globale de l’identité.

Limites du graphe d’identité

Le graphe ne capture que ce que GA4 a observé. Les utilisateurs anonymes qui ne s’authentifient jamais restent anonymes. Les sessions antérieures à la première connexion d’un utilisateur sur un appareil donné restent orphelines, à moins que l’utilisateur ne s’authentifie et que la table de correspondance les connecte rétroactivement.

Pour relier les utilisateurs anonymes aux enregistrements CRM ou aux résultats métier, la couche de résolution d’identité Customer 360 va plus loin — capturant les soumissions de formulaires avec des champs user_pseudo_id masqués, en faisant correspondre les identifiants de transaction, et en construisant des liaisons déterministes qui ne nécessitent pas d’événement de connexion GA4.