Transaction ID : réparer la jointure GA4 et SFCC

Vous avez les données GA4 dans BigQuery. Vous avez les commandes SFCC (Salesforce Commerce Cloud) dans BigQuery. Vous écrivez votre premier LEFT JOIN et obtenez 40 % de NULL là où vous attendiez des correspondances.

La plupart des gens ouvrent alors leur modèle LookML et ajustent les jointures. C’est une erreur : la jointure n’est pas le problème, les identifiants le sont. Cet article passe en revue toutes les façons dont le transaction ID GA4/SFCC peut casser, comment diagnostiquer méthodiquement le mode de panne, et comment le corriger une fois pour tous les dashboards.

Pourquoi le transaction ID est votre chemin critique

Tout le modèle de réconciliation suppose que le transaction_id de GA4 est la même valeur que l’order_id de SFCC pour la même commande. Dans la pratique, cette hypothèse échoue plus souvent qu’on ne le croit, pour des raisons évitables mais rarement documentées. Sans clé de jointure fiable, aucune métrique de réconciliation n’est juste ou mesurable : ni l’écart de revenu, ni la classification des commandes par origine, ni les métriques de gouvernance du reste de cette série.

Les cinq modes de panne

Figure 1 — Arbre de décision des modes de panne du transaction ID. Exécutez les requêtes de diagnostic dans l’ordre pour identifier le mode qui s’applique avant toute correction.

Avant les cinq modes : transaction_id vide ou absent

À vérifier en premier. Google prévient : « Don’t send an empty string as the transaction ID. Google Analytics will deduplicate all purchase events that have transaction_id= » » » (Google : réduire les doublons d’événements clés avec les transaction IDs). Comptez les achats dont ecommerce.transaction_id est NULL, vide ou « (not set) » sur les 30 derniers jours dans events_*.

Mode 1 : des identifiants complètement différents

C’est le mode le plus grave et le plus difficile à corriger, car il demande une décision côté produit. La valeur poussée dans le dataLayer comme transaction_id n’est pas l’order_id SFCC. Ce peut être :

une référence de transaction du moyen de paiement (par exemple un payment intent Stripe, pi_3Abc123), qui peut exister avant ou indépendamment du numéro de commande SFCC ;
un token de session de checkout généré côté front, parfois absent de SFCC ;
un identifiant de panier créé à l’ajout au panier, alors que SFCC crée un nouvel ID de commande à la confirmation du paiement ;
un numéro de confirmation affiché au client, alors que SFCC utilise un numéro de commande interne.

Détection : prenez 20 événements purchase GA4 récents et cherchez à la main les commandes SFCC correspondantes. Si les IDs n’ont aucune structure commune, c’est le mode 1.

Correction : impossible dans BigQuery ou LookML seuls. Il faut changer la valeur poussée dans le dataLayer : générer l’order_id SFCC avant le rendu de la page de confirmation et le passer explicitement. Dans la plupart des implémentations SFCC, l’objet commande est disponible côté serveur à ce moment-là :

// dataLayer push sur la page de confirmation (template ISML SFCC ou headless)
dataLayer.push({
  event: 'purchase',
  ecommerce: {
    transaction_id: '${dw.util.SecureEncoder.forJavaScriptInBlock(pdict.Order.orderNo)}',  // numéro de commande SFCC, PAS la réf. de paiement
    value: ${pdict.Order.adjustedMerchandizeTotalPrice.value},  // articles après remises : ni livraison, ni taxe
    shipping: ${pdict.Order.adjustedShippingTotalPrice.value},
    tax: ${pdict.Order.totalTax.value},
    currency: '${pdict.Order.getCurrencyCode()}',
    items: [...]
  }
});

Trois détails comptent. La value GA4 doit exclure livraison et taxe (« Don’t include shipping or tax », référence des événements GA4), alors que totalGrossPrice de SFCC est le total TTC (SFCC LineItemCtnr) : alignez les définitions. Les valeurs imprimées dans un bloc <script> s’encodent avec SecureEncoder.forJavaScriptInBlock (SFCC SecureEncoder). Les templates SFRA exposent parfois pdict.order plutôt que pdict.Order : vérifiez dans votre cartridge.

Mode 2 : préfixe ou suffixe différent

SFCC stocke ORD-00123456, le dataLayer envoie 00123456 à GA4 (ou l’inverse : 123456 contre #123456 ou order_123456). Très courant quand l’équipe front a formaté l’ID autrement que le back-end SFCC, ou après une migration sans mise à jour du tracking. Comparez la structure des IDs des deux côtés sur 7 jours (préfixe via REGEXP_EXTRACT(order_id, r'^[^0-9]*'), longueur numérique, longueur totale). Si SFCC montre ORD- et GA4 aucun préfixe : REGEXP_REPLACE(sfcc.order_id, r'^ORD-', '').

Mode 3 : la casse

Les comparaisons de chaînes BigQuery sont sensibles à la casse : 'ABC-00123' n’est pas égal à 'abc-00123', et la jointure échoue silencieusement. C’est vicieux : les IDs semblent identiques à l’œil, seul un sous-ensemble d’apparence aléatoire est touché, et le taux de match paraît acceptable (disons 90 %), donc il reste sans diagnostic. Si le taux normalisé (LOWER + TRIM) est nettement supérieur au taux brut, c’est le mode 3 ou 4.

Mode 4 : espaces et caractères invisibles

Espaces en début ou fin de valeur (exports CSV SFCC mal guillemetés, padding dans les réponses d’API, saisie manuelle, copier-coller depuis un tableur), et plus rarement caractères Unicode de largeur nulle, espaces insécables ou retours chariot. Le nettoyage extrême s’écrit en syntaxe RE2 :

SELECT TRIM(LOWER(REGEXP_REPLACE(order_id,
  r'[\x00-\x1F\x7F\x{00A0}\x{200B}-\x{200D}\x{FEFF}]', ''))) AS ultra_clean_id
FROM `project.sfcc.orders`

Mode 5 : des commandes structurellement impossibles à rapprocher

Certaines commandes SFCC n’auront jamais de pendant GA4, non pas à cause d’un problème de qualité de données, mais parce qu’elles naissent dans des environnements où GA4 ne peut pas exister : commandes du centre d’appels et du back-office (pas de navigateur ni de session), commandes B2B par API, synchronisations de caisse (POS), renouvellements d’abonnement automatiques. Ne cherchez pas à corriger le mode 5. Classez ces commandes explicitement dans le modèle de données pour les exclure du calcul du taux de match et en faire un segment à part. L’article sur le dark traffic et les commandes orphelines (partie 5 de cette série) le traite en détail.

La séquence de diagnostic : quatre requêtes

Exécutez-les dans l’ordre avant de construire quoi que ce soit dans Looker. Chacune réduit le champ du problème.

Étape 0 : construire la vue des achats GA4

Les requêtes lisent project.analytics.ga4_purchases, qui n’existe pas dans l’export brut : celui-ci contient des tables quotidiennes events_YYYYMMDD, event_date est une chaîne YYYYMMDD, l’ID est dans ecommerce.transaction_id et l’ID de session est le paramètre d’événement ga_session_id (Google : schéma de l’export BigQuery, Google : sessions). Google Analytics déduplique dans les rapports les achats ayant le même transaction ID (flux web uniquement), mais l’export conserve tous les événements : dédupliquez dans la vue.

CREATE OR REPLACE VIEW `project.analytics.ga4_purchases` AS
SELECT
  PARSE_DATE('%Y%m%d', event_date)  AS event_date,
  event_timestamp,
  ecommerce.transaction_id          AS transaction_id,
  (SELECT value.int_value FROM UNNEST(event_params)
    WHERE key = 'ga_session_id')    AS ga_session_id,
  user_pseudo_id,
  ecommerce.purchase_revenue        AS reported_revenue
FROM `project.analytics_XXXX.events_*`
WHERE event_name = 'purchase'
-- l'export garde les achats renvoyés : on garde le premier événement par transaction
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY LOWER(TRIM(ecommerce.transaction_id))
  ORDER BY event_timestamp) = 1;

Requête 1 : taux de match de base (brut, sans normalisation)

SELECT
  COUNT(DISTINCT g.transaction_id) AS ga4_transactions,
  COUNT(DISTINCT CASE WHEN s.order_id IS NOT NULL THEN g.transaction_id END) AS matched_raw,
  ROUND(COUNT(DISTINCT CASE WHEN s.order_id IS NOT NULL THEN g.transaction_id END)
    / NULLIF(COUNT(DISTINCT g.transaction_id), 0) * 100, 1) AS raw_match_rate_pct
FROM `project.analytics.ga4_purchases` g
LEFT JOIN `project.sfcc.orders` s
  ON g.transaction_id = s.order_id
  AND s.channel IN ('web', 'storefront')  -- filtre dans la jointure, pas dans WHERE
WHERE g.event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY);

Un filtre WHERE sur s.* supprimerait toutes les lignes GA4 sans correspondance et forcerait 100 %. Règles empiriques tirées de mes projets (pas un standard) : au moins 90 %, petit souci de normalisation (modes 3 ou 4) ; entre 70 et 89 %, préfixe probable (mode 2) ; moins de 70 %, IDs structurellement différents (mode 1) ou fort volume hors ligne.

Requêtes 2 à 4

Requête 2, taux brut contre taux normalisé : même population, avec deux jointures (brute, et sur LOWER(TRIM(...)) des deux côtés). Si le taux normalisé dépasse nettement le brut, appliquez LOWER(TRIM()) aux deux côtés dans toutes les jointures. S’il est à peu près égal, le problème est structurel (mode 1 ou 2).

Requête 3, structure des IDs : UNION ALL des deux sources sur 7 jours (préfixe, longueur du préfixe, longueur totale, volume). GA4 sans préfixe et SFCC avec ORD- : REGEXP_REPLACE(sfcc.order_id, r'^ORD-', ''). Longueur totale de 12 côté GA4 contre 18 côté SFCC : IDs structurellement différents (mode 1), à corriger dans le dataLayer.

Requête 4, classer les non-appariés : on part des commandes SFCC sans correspondance GA4 (30 jours) et on les classe avec un CASE : offline_store, offline_callcenter, b2b_wholesale, web_unmatched_dark_traffic ou other, avec le nombre de commandes, le revenu et la part du total. Dans l’exemple d’une implémentation, magasins et B2B forment l’essentiel des non-appariés (attendu, non corrigeable) ; le reste vient du dark traffic web (WebView, consentement, tags bloqués). Au-dessus de 50 % de dark traffic web parmi les non-appariés, c’est un problème d’implémentation du tracking.

Corriger dans LookML : trois motifs

Une fois le mode identifié, centralisez la correction dans LookML ou dans des vues BigQuery, pas dans chaque dashboard. Les relations one_to_one ci-dessous supposent que la vue GA4 est dédupliquée par transaction ID (étape 0) ; sinon, déclarez many_to_one de GA4 vers SFCC.

Motif 1 : normalisation seule (modes 3 et 4)

explore: ga4_purchases {
  join: sfcc_orders {
    type: left_outer
    sql_on: LOWER(TRIM(${ga4_purchases.transaction_id}))
          = LOWER(TRIM(${sfcc_orders.order_id})) ;;
    relationship: one_to_one
  }
}

Motif 2 : retrait du préfixe + normalisation (mode 2). Même jointure, avec REGEXP_REPLACE(${sfcc_orders.order_id}, r'^ORD-', '') du côté SFCC (ou r'^#' du côté GA4 si c’est GA4 qui porte le préfixe).

Motif 3 : des vues normalisées dans BigQuery (le meilleur pour les cas complexes). On crée norm_sfcc_orders (avec norm_order_id : préfixe retiré, caractères de contrôle supprimés, LOWER(TRIM(...)), et exclusion des statuts fraude/test) et norm_ga4_purchases (avec norm_transaction_id), puis Looker joint simplement sur ${ga4_purchases.norm_transaction_id} = ${sfcc_orders.norm_order_id}.

À quoi ressemble un taux de match sain

Après normalisation, voici des cibles par segment. Ce sont des cibles illustratives tirées de mes projets, pas un benchmark sectoriel : mesurez d’abord votre base.

Segment de commandes	Taux cible	Minimum acceptable	Sous le minimum, investiguer
Commandes web, jour même (Chrome/Firefox)	92 à 97 %	88 %	Déclenchement du tag sur la page de confirmation, config Consent Mode
Commandes web mobile	78 à 88 %	70 %	Trafic WebView, navigateur intégré aux apps
Commandes via l’app mobile	50 à 75 %	40 %	SDK Firebase/GA4 absent ou mal configuré
Centre d’appels	0 à 10 %	N/A	Attendu, hors ligne par nature : classer, pas corriger
B2B / grossiste	0 à 20 %	N/A	Attendu, pas de session web : classer, pas corriger
POS / magasin	0 %	N/A	Attendu, entièrement hors ligne : hors périmètre de réconciliation

La prévention du suivi de Safari n’empêche pas le hit d’achat d’arriver : elle touche l’attribution (quelle source est créditée). Vérifiez-la dans une revue de qualité d’attribution, pas dans ce tableau (WebKit : prévention du suivi). Aucun modèle n’atteint 100 % : l’objectif est de connaître votre taux par segment et de distinguer les écarts structurels (non corrigeables) des écarts d’implémentation (corrigeables).

Erreurs courantes à éviter

Normaliser un seul côté de la jointure : il faut le faire des deux côtés.
Traiter le mode 5 comme un échec de jointure : classez ces commandes comme hors ligne et passez à autre chose.
Corriger dans un dashboard plutôt que dans le modèle : un override SQL ne corrige que ce dashboard.
Ne pas revérifier après une mise à jour : le taux de match doit être une métrique suivie, pas un contrôle ponctuel.

La suite de la série

Partie 4 : la gouvernance des métriques LookML, une fois la jointure fonctionnelle (revenu net, métriques d’écart, taux de conversion, avec documentation complète). Partie 5 : dark traffic et commandes orphelines, un système de classification des commandes qui ne s’apparieront jamais, pour les rendre exploitables.

Sources

Pour aller plus loin, lisez la version complète de l’article (en anglais) : The Transaction ID Problem: A Complete Diagnostic Guide to Fixing Your GA4/SFCC Join.

Besoin d’aide sur ce sujet ? Consultant GA4 et audit analytics.

Le problème du transaction ID : guide de diagnostic pour réparer la jointure GA4/SFCC