Architecture DAG dbt pour le Customer 360

Un modèle Customer 360 unifie les données sur un seul client provenant de plusieurs systèmes sources en une seule ligne. Le défi n’est pas la table large finale — c’est la construction du pont d’identité qui connecte les systèmes sans clé partagée. Le pattern standard en trois couches (base, intermédiaire, mart) nécessite une couche de résolution d’identité explicite pour que cela fonctionne.

La structure DAG

L’architecture ajoute une couche d’identité entre les modèles base et mart :

Sources :  ga4.events          → base__ga4__events
           crm.contacts        → base__crm__contacts
           crm.deals           → base__crm__deals

Identité : base__ga4 + base__crm  → int__identity_resolved
                                   → int__identity_device_bridged

Marts :    int__identity + base__ga4        → mrt__marketing__web_sessions
           mrt__web_sessions + base__deals  → mrt__marketing__attributed_touchpoints
           toutes sources + identité        → mrt__core__customer_360

Ce sont toujours les trois mêmes couches, pas quatre. Les modèles d’identité sont des modèles intermédiaires — ils joignent des modèles base ensemble et ajoutent des champs calculés sans changer le grain fondamental (une ligne par paire d’identité). Ils se trouvent simplement dans un sous-répertoire qui rend leur rôle particulier évident.

Modèles base

Les modèles base font le travail standard : aplatir les event_params GA4 (patterns UNNEST pour les UTMs, emplacements de page, IDs de session), renommer les colonnes CRM avec des noms compréhensibles par le métier, et filtrer les enregistrements _fivetran_deleted. Un modèle par table source, matérialisé en vues ou tables.

Pour les événements GA4 spécifiquement, le modèle base extrait les champs clés nécessaires à la résolution d’identité :

-- base__ga4__events.sql
SELECT
    event_date,
    event_timestamp,
    event_name,
    user_pseudo_id,
    user_id,
    (SELECT value.string_value
     FROM UNNEST(event_params)
     WHERE key = 'page_location') AS page_location,
    (SELECT value.int_value
     FROM UNNEST(event_params)
     WHERE key = 'ga_session_id') AS ga_session_id,
    privacy_info.analytics_storage AS consent__analytics_storage,
    -- ... autres champs
FROM {{ source('ga4', 'events') }}
WHERE (SELECT value.int_value
       FROM UNNEST(event_params)
       WHERE key = 'ga_session_id') IS NOT NULL

Noter l’extraction de privacy_info.analytics_storage. Cela devient critique quand les modèles doivent respecter les exigences de consentement — seuls les événements avec un consentement accordé doivent passer dans la résolution d’identité qui se lie aux données CRM.

La couche d’identité

C’est le nouvel élément absent des projets dbt standard. Deux modèles la gèrent :

int__identity_resolved joint les données de soumission de formulaire (où user_pseudo_id a été capturé avec une adresse email), les événements de connexion (où user_id a été défini), et les enregistrements de contacts CRM. La sortie est une table de mapping : une ligne par paire d’identité connue.

-- int__identity_resolved.sql
{{ config(
    materialized='incremental',
    incremental_strategy='merge',
    unique_key=['identifier_type', 'identifier_value']
) }}

WITH form_identities AS (
    -- user_pseudo_id → email → crm_contact_id
    SELECT DISTINCT
        user_pseudo_id,
        crm.contact_id AS crm_contact_id,
        'form_submission' AS identity_source,
        form.submitted_at AS identified_at
    FROM {{ ref('base__form_submissions') }} form
    INNER JOIN {{ ref('base__crm__contacts') }} crm
        ON LOWER(TRIM(form.email)) = LOWER(TRIM(crm.email))
    WHERE form.user_pseudo_id IS NOT NULL
),

login_identities AS (
    -- user_id → user_pseudo_id, lié via backstitching
    SELECT DISTINCT
        events.user_pseudo_id,
        crm.contact_id AS crm_contact_id,
        'login' AS identity_source,
        events.event_timestamp AS identified_at
    FROM {{ ref('base__ga4__events') }} events
    INNER JOIN {{ ref('base__crm__contacts') }} crm
        ON events.user_id = crm.external_id
    WHERE events.user_id IS NOT NULL
),

all_identities AS (
    SELECT * FROM form_identities
    UNION ALL
    SELECT * FROM login_identities
)

SELECT
    user_pseudo_id,
    crm_contact_id,
    identity_source,
    identified_at
FROM all_identities
QUALIFY ROW_NUMBER() OVER (
    PARTITION BY user_pseudo_id
    ORDER BY identified_at DESC
) = 1

int__identity_device_bridged développe ce résultat pour permettre de passer de n’importe quel identifiant au customer_id canonique. Si on utilise dbt_utils.generate_surrogate_key(), hasher une combinaison du système source et de l’ID source :

{{ dbt_utils.generate_surrogate_key(['source_system', 'source_id']) }} AS customer__surrogate_key

Ces modèles utilisent la stratégie incrémentale merge car les mappings d’identité sont relativement petits (au plus une ligne par paire d’identité connue) mais se mettent à jour fréquemment au fur et à mesure que de nouvelles soumissions de formulaires et connexions créent de nouveaux mappings.

Modèles mart

Les modèles mart consomment le pont d’identité. Le modèle clé est mrt__core__customer_360 — la table large qui combine les informations de contact du CRM, les agrégats de deals, les métriques d’engagement et les résumés d’analytique web en une seule ligne par client.

-- mrt__core__customer_360.sql
{{ config(
    materialized='incremental',
    incremental_strategy='merge',
    unique_key='customer_id'
) }}

SELECT
    identity.crm_contact_id AS customer_id,

    -- Champs de contact CRM (résolution basée sur la priorité)
    COALESCE(crm.email, product.email) AS customer__email,
    COALESCE(crm.company_name, product.company_name) AS customer__company_name,
    crm.job_title AS customer__job_title,

    -- Agrégats de deals
    deals.total_deal_value,
    deals.deals_won,
    deals.latest_deal_closed_at,

    -- Résumés d'analytique web
    web.total_sessions,
    web.total_page_views,
    web.first_seen_at,
    web.last_seen_at,
    web.days_active

FROM {{ ref('int__identity_device_bridged') }} identity
LEFT JOIN {{ ref('base__crm__contacts') }} crm
    ON identity.crm_contact_id = crm.contact_id
LEFT JOIN {{ ref('int__deal_aggregates') }} deals
    ON identity.crm_contact_id = deals.contact_id
LEFT JOIN {{ ref('int__web_analytics_summary') }} web
    ON identity.user_pseudo_id = web.user_pseudo_id

Les patterns de résolution de conflits (COALESCE pour la priorité, comparaisons de récence, champs spécifiques à la source) se passent tous dans ce mart ou dans les modèles intermédiaires qui l’alimentent.

mrt__core__customer_360 utilise également merge, avec pour clé le customer_id canonique. C’est une table large mais qui dépasse rarement quelques millions de lignes, donc les rafraîchissements complets sont envisageables si les coûts de merge deviennent préoccupants.

Choix de matérialisation

Les différents types de modèles dans le DAG ont des besoins de matérialisation différents :

Pour les tables mart CRM, partitionner par champs de date (created_at, closed_at) et clusteriser par colonnes fréquemment filtrées (account_id, owner_id). Le clustering seul peut réduire les données scannées de 88% ou plus pour les modèles incrémentiels sur BigQuery.

Le pattern de référence JaffleGaggle

L’implémentation de référence « JaffleGaggle » de dbt Labs suit une architecture similaire : définir des entités (contacts, comptes), extraire des domaines d’email, signaler les emails personnels vs professionnels, utiliser des fichiers seed pour les cas limites qui nécessitent un jugement humain (comme les domaines d’email partagés qui ne sont pas vraiment professionnels), puis agréger les données d’utilisation par entité en tables mart larges.

Le pattern de fichier seed vaut la peine d’être noté. Certains cas limites de résolution d’identité — gmail.com est personnel, acme.com est professionnel, mais freelancer.com pourrait être l’un ou l’autre — nécessitent un jugement humain qui n’a pas sa place dans le SQL. Un fichier seed avec ces classifications garde la logique auditable et maintenable.

Connexion à l’attribution

Avec la résolution d’identité en place, on peut construire une chronologie de points de contact par utilisateur à partir des données de session GA4 (avec source, medium et campagne depuis les champs de source de trafic GA4), puis joindre aux conversions de deals CRM en utilisant le resolved_user_id du pont d’identité et une fenêtre de conversion.

Le modèle mrt__marketing__attributed_touchpoints fait le pont entre les sessions web et les deals CRM :

SELECT
    sessions.resolved_user_id,
    sessions.session_date,
    sessions.session_source,
    sessions.session_medium,
    deals.deal_id,
    deals.deal_amount,
    deals.closed_at
FROM {{ ref('mrt__marketing__web_sessions') }} sessions
INNER JOIN {{ ref('int__identity_device_bridged') }} identity
    ON sessions.user_pseudo_id = identity.user_pseudo_id
INNER JOIN {{ ref('base__crm__deals') }} deals
    ON identity.crm_contact_id = deals.contact_id
WHERE sessions.session_date
    BETWEEN DATE_SUB(deals.closed_at, INTERVAL 90 DAY)
    AND deals.closed_at

Appliquer les modèles d’attribution (premier contact, dernier contact, linéaire, décroissance temporelle) sur la table de points de contact ainsi produite, agréger par canal ou campagne, et on obtient le ROI par source.