Pattern de comparaison d'attribution dbt

Un système d’attribution multi-modèles dans dbt nécessite une séparation nette entre la logique d’attribution et la couche de comparaison. Chaque modèle s’exécute indépendamment, implémentant une approche d’attribution contre les mêmes données de points de contact. Un modèle de comparaison final réunit tout via union avec un discriminateur model_type pour la consommation par le dashboard. Ce pattern suit l’architecture trois couches dbt — les modèles base nettoient les événements GA4, les modèles intermédiaires construisent les chemins de points de contact sessionisés, et les marts implémentent les modèles d’attribution individuels plus l’union de comparaison.

Structure du projet

models/
├── base/
│   └── base__ga4__events.sql           # Nettoyage des événements bruts
├── intermediate/
│   ├── int__events_sessionized.sql     # Sessionisation
│   ├── int__sessions_enriched.sql      # Points de contact marketing
│   └── int__touchpoints_pathed.sql     # Chemins du parcours utilisateur
└── marts/
    └── attribution/
        ├── mrt__attribution__first_touch.sql
        ├── mrt__attribution__last_touch.sql
        ├── mrt__attribution__linear.sql
        ├── mrt__attribution__position_based.sql
        ├── mrt__attribution__time_decay.sql
        ├── mrt__attribution__conversions.sql
        └── mrt__attribution__comparison.sql

Chaque modèle mrt__attribution__* implémente une approche d’attribution et produit un schéma cohérent : conversion__id, touchpoint__channel, touchpoint__attributed_revenue, et toute dimension supplémentaire pour l’analyse (campagne, converted_at, segment utilisateur). Le schéma cohérent est critique — le modèle de comparaison dépend de chaque modèle en amont produisant les mêmes colonnes.

Le modèle mrt__attribution__conversions détient la vérité terrain : les conversions réelles avec les revenus réels. Il sert de table de référence pour les tests de validation.

Selon la convention de nommage, tous les modèles d’attribution vivent sous marts/attribution/ — organisés par domaine métier, avec le préfixe mrt__ rendant la couche évidente.

Le modèle de comparaison

Le modèle de comparaison réunit les modèles d’attribution individuels via union et ajoute un identifiant :

-- mrt__attribution__comparison.sql

WITH first_touch AS (
    SELECT
        conversion__id,
        touchpoint__channel,
        touchpoint__attributed_revenue,
        conversion__converted_at,
        'first_touch' AS model_type
    FROM {{ ref('mrt__attribution__first_touch') }}
),

last_touch AS (
    SELECT
        conversion__id,
        touchpoint__channel,
        touchpoint__attributed_revenue,
        conversion__converted_at,
        'last_touch' AS model_type
    FROM {{ ref('mrt__attribution__last_touch') }}
),

linear AS (
    SELECT
        conversion__id,
        touchpoint__channel,
        touchpoint__attributed_revenue,
        conversion__converted_at,
        'linear' AS model_type
    FROM {{ ref('mrt__attribution__linear') }}
),

position_based AS (
    SELECT
        conversion__id,
        touchpoint__channel,
        touchpoint__attributed_revenue,
        conversion__converted_at,
        'position_based' AS model_type
    FROM {{ ref('mrt__attribution__position_based') }}
),

time_decay AS (
    SELECT
        conversion__id,
        touchpoint__channel,
        touchpoint__attributed_revenue,
        conversion__converted_at,
        'time_decay' AS model_type
    FROM {{ ref('mrt__attribution__time_decay') }}
)

SELECT * FROM first_touch
UNION ALL
SELECT * FROM last_touch
UNION ALL
SELECT * FROM linear
UNION ALL
SELECT * FROM position_based
UNION ALL
SELECT * FROM time_decay

Cela produit une ligne par conversion par canal par modèle. Une seule conversion avec trois points de contact génère 15 lignes (3 points de contact x 5 modèles). La colonne model_type devient le filtre principal dans le dashboard d’attribution.

Pourquoi des CTEs plutôt qu’un UNION ALL direct

Il serait possible d’écrire SELECT *, 'first_touch' AS model_type FROM {{ ref('mrt__attribution__first_touch') }} UNION ALL ... directement. L’approche CTE est plus maintenable pour trois raisons :

Sélection explicite des colonnes. Si un modèle en amont ajoute une colonne, le modèle de comparaison ne changera pas silencieusement de forme. On contrôle exactement quelles colonnes passent.
Débogage plus facile. On peut commenter un CTE pour isoler les problèmes avec un modèle spécifique.
Lisibilité. Lors de l’ajout d’un sixième modèle, le pattern est évident : ajouter un CTE, ajouter un UNION ALL.

Conscience de la multiplication des lignes

La table de comparaison peut devenir volumineuse. Avec 10 000 conversions ayant en moyenne 4 points de contact chacune, un modèle produit 40 000 lignes. Cinq modèles produisent 200 000 lignes. Ce n’est généralement pas un problème pour BigQuery, mais cela importe pour les outils BI avec des limites de lignes et pour l’optimisation des coûts. Pré-agréger aux résumés au niveau du canal dans un modèle en aval réduit considérablement le nombre de lignes pour la consommation par le dashboard.

Ajouter un nouveau modèle

Le pattern rend l’ajout de modèles mécanique :

Créer mrt__attribution__new_model.sql implémentant la logique d’attribution avec le contrat de colonnes standard.
Ajouter un CTE au modèle de comparaison.
Ajouter un UNION ALL référençant le nouveau CTE.
Le nouveau modèle apparaît automatiquement comme option dans les filtres du dashboard.

Pas de changements au dashboard nécessaires. Pas de nouvelles connexions de données. Le menu déroulant model_type dans l’outil BI récupère automatiquement la nouvelle valeur car il lit depuis la table de comparaison.

Validation : test d’intégrité des revenus

Chaque modèle d’attribution devrait passer une vérification d’intégrité fondamentale : les revenus attribués par conversion doivent sommer au revenu réel de conversion. L’étape de normalisation dans le SQL de chaque modèle devrait garantir cela, mais les particularités des données et les cas limites peuvent le briser silencieusement.

Ajouter un test dbt qui vérifie la somme à travers chaque modèle :

models:
  - name: mrt__attribution__comparison
    description: >
      Union de tous les modèles d'attribution avec discriminateur model_type.
      Une ligne par conversion par canal par modèle.
    columns:
      - name: conversion__id
        data_tests:
          - not_null
      - name: model_type
        data_tests:
          - accepted_values:
              values:
                - first_touch
                - last_touch
                - linear
                - position_based
                - time_decay
    tests:
      - dbt_utils.expression_is_true:
          expression: >
            ABS(SUM(touchpoint__attributed_revenue) -
            (SELECT SUM(conversion__revenue)
             FROM {{ ref('mrt__attribution__conversions') }})) < 0.01
          group_by_columns: ['model_type']

Le paramètre group_by_columns exécute le test une fois par modèle. Si l’attribution linéaire somme correctement mais que l’attribution basée sur la position ne le fait pas, on saura exactement quel modèle a le problème. La tolérance de 0,01 gère l’arrondi des nombres flottants.

C’est une assertion de style test singulier utilisant le test générique expression_is_true de dbt-utils. Pour plus de rigueur, écrire un test singulier qui identifie les conversions spécifiques où l’attribution ne s’équilibre pas :

-- tests/assert_attribution_revenue_balances.sql
WITH attributed AS (
  SELECT
    model_type,
    conversion__id,
    SUM(touchpoint__attributed_revenue) AS total_attributed
  FROM {{ ref('mrt__attribution__comparison') }}
  GROUP BY model_type, conversion__id
),

actual AS (
  SELECT
    conversion__id,
    conversion__revenue
  FROM {{ ref('mrt__attribution__conversions') }}
)

SELECT
  a.model_type,
  a.conversion__id,
  a.total_attributed,
  c.conversion__revenue,
  ABS(a.total_attributed - c.conversion__revenue) AS diff
FROM attributed a
JOIN actual c ON a.conversion__id = c.conversion__id
WHERE ABS(a.total_attributed - c.conversion__revenue) > 0.01

Zéro ligne retournée signifie que chaque conversion s’équilibre à travers chaque modèle. Toute ligne retournée identifie exactement quelle conversion et quel modèle ont un problème, rendant le débogage simple.

Contrat de schéma pour les modèles en amont

Pour maintenir le modèle de comparaison fiable, appliquer un schéma cohérent à travers tous les modèles d’attribution. Un contrat de modèle empêche que des changements en amont brisent silencieusement l’union :

models:
  - name: mrt__attribution__first_touch
    config:
      contract:
        enforced: true
    columns:
      - name: conversion__id
        data_type: string
        constraints:
          - type: not_null
      - name: touchpoint__channel
        data_type: string
        constraints:
          - type: not_null
      - name: touchpoint__attributed_revenue
        data_type: numeric
      - name: conversion__converted_at
        data_type: timestamp

Appliquer le même contrat à chaque modèle mrt__attribution__*. Si quelqu’un ajoute une colonne au first-touch sans l’ajouter aux autres, l’approche SELECT * du modèle de comparaison briserait l’union. Les contrats rendent cela explicite plutôt qu’une surprise à l’exécution.

Pré-agrégation pour les performances du dashboard

La table de comparaison au niveau des points de contact est la source de vérité, mais la plupart des vues dashboard ne nécessitent pas une granularité au niveau des points de contact. Un modèle de résumé agrège pour les requêtes dashboard courantes :

-- mrt__attribution__comparison_summary.sql

SELECT
    model_type,
    touchpoint__channel,
    DATE(conversion__converted_at) AS conversion_date,
    COUNT(DISTINCT conversion__id) AS conversions,
    SUM(touchpoint__attributed_revenue) AS attributed_revenue
FROM {{ ref('mrt__attribution__comparison') }}
GROUP BY ALL

Cette table est considérablement plus petite — une ligne par modèle par canal par jour au lieu d’une ligne par conversion par canal par modèle. Looker Studio performe mieux avec moins de lignes, et BigQuery facture les octets scannés, donc la pré-agrégation évite des scans complets répétés de la table depuis la couche BI.

Pointer les dashboards sur cette table de résumé pour les vues standard. Garder la table de comparaison détaillée disponible pour les analyses approfondies au niveau analyste de conversions ou de chemins spécifiques.