Patterns d'attribution SQL

L’attribution marketing assigne le crédit de conversion aux points de contact qui l’ont précédée. Les outils de plateforme (GA4, Meta Ads Manager) font cela pour vous, mais ils souffrent du biais des jardins clos, de l’aveuglement cross-device et des replis de méthodologie silencieux. Construire l’attribution en SQL donne de la transparence, une visibilité cross-platform et un contrôle total sur la logique.

Chaque modèle d’attribution opère sur la même entrée : une table de points de contact qui joint les interactions marketing aux conversions dans une fenêtre de lookback. Les modèles ne diffèrent que dans la façon dont ils distribuent le revenu d’une conversion sur ces points de contact.

La table des points de contact

Tous les patterns ci-dessous supposent une table de points de contact de cette forme :

Le filtre de fenêtre de lookback (WHERE touchpoint_timestamp >= TIMESTAMP_SUB(conversion_timestamp, INTERVAL N DAY)) délimite les points de contact qui sont éligibles. Définissez-le selon votre cycle de vente : 7 à 14 jours pour l’e-commerce impulsif, 30 à 45 jours pour les achats réfléchis, 90 à 180 jours pour le B2B.

First-touch et last-touch

Les modèles à point de contact unique assignent 100 % du crédit à un point de contact. Le pattern SQL utilise ROW_NUMBER() pour sélectionner soit le point de contact le plus ancien soit le plus récent par conversion.

WITH touchpoints_numbered AS (
  SELECT
    user_id,
    transaction_id,
    channel,
    revenue,
    touchpoint_timestamp,
    ROW_NUMBER() OVER (
      PARTITION BY user_id, transaction_id
      ORDER BY touchpoint_timestamp ASC  -- DESC pour last-touch
    ) AS touch_position
  FROM touchpoints
  WHERE touchpoint_timestamp >= TIMESTAMP_SUB(
    conversion_timestamp, INTERVAL 30 DAY
  )
)
SELECT
  user_id,
  transaction_id,
  channel,
  revenue AS attributed_revenue
FROM touchpoints_numbered
WHERE touch_position = 1

First-touch (ASC) répond à « quels canaux alimentent le haut du funnel ? » Utilisez-le pour l’analyse de notoriété de marque et d’acquisition. Last-touch (DESC) répond à « qu’est-ce qui conclut les deals ? » Utilisez-le pour l’évaluation de la réponse directe et du remarketing. La limitation des deux : ils ignorent tous les autres points de contact dans le parcours.

Attribution linéaire

L’attribution linéaire distribue le revenu équitablement sur tous les points de contact. Au lieu de choisir un gagnant, chaque point de contact obtient la même part.

WITH touchpoints_counted AS (
  SELECT
    user_id,
    transaction_id,
    channel,
    revenue,
    COUNT(*) OVER (
      PARTITION BY user_id, transaction_id
    ) AS total_touches
  FROM touchpoints
  WHERE touchpoint_timestamp >= TIMESTAMP_SUB(
    conversion_timestamp, INTERVAL 30 DAY
  )
)
SELECT
  user_id,
  transaction_id,
  channel,
  revenue / total_touches AS attributed_revenue
FROM touchpoints_counted

Cinq points de contact sur une conversion de 100 € reçoivent chacun 20 €. L’attribution linéaire fournit une vue multi-touch équilibrée et fonctionne bien comme valeur par défaut quand vous n’avez pas d’hypothèse forte sur les positions du parcours qui comptent le plus. L’inconvénient : elle sur-crédite les points de contact à faible impact dans les longs parcours où une simple ouverture d’e-mail se retrouve aux côtés d’un clic de recherche payante à forte intention.

Basé sur la position (en U)

Les modèles basés sur la position pondèrent les points de contact selon leur emplacement dans le parcours. Le modèle en U standard assigne 40 % au premier touch, 40 % au dernier touch, et divise les 20 % restants équitablement entre les points de contact intermédiaires.

WITH touchpoints_positioned AS (
  SELECT
    user_id,
    transaction_id,
    channel,
    revenue,
    ROW_NUMBER() OVER (
      PARTITION BY user_id, transaction_id
      ORDER BY touchpoint_timestamp ASC
    ) AS position,
    COUNT(*) OVER (
      PARTITION BY user_id, transaction_id
    ) AS total_touches
  FROM touchpoints
  WHERE touchpoint_timestamp >= TIMESTAMP_SUB(
    conversion_timestamp, INTERVAL 30 DAY
  )
)
SELECT
  user_id,
  transaction_id,
  channel,
  CASE
    WHEN total_touches = 1 THEN 1.0
    WHEN total_touches = 2 THEN 0.5
    WHEN position = 1 THEN 0.4
    WHEN position = total_touches THEN 0.4
    ELSE 0.2 / (total_touches - 2)
  END * revenue AS attributed_revenue
FROM touchpoints_positioned

La clause CASE gère d’abord les cas limites : les conversions à un seul point de contact obtiennent 100 %, les conversions à deux points de contact se partagent 50/50. C’est seulement ensuite que la logique 40-20-40 s’applique. Sauter la gestion des cas limites produit des erreurs de division par zéro et des poids incorrects.

Variations : les équipes SaaS B2B utilisent souvent un modèle en W (30-30-30-10) qui ajoute du poids à un événement clé du milieu comme une demande de démo. Les produits à forte considération peuvent passer à 45-10-45 pour accentuer davantage les extrémités.

Attribution time-decay

L’attribution time-decay assigne plus de crédit aux points de contact proches de la conversion en utilisant une décroissance exponentielle avec une demi-vie configurable :

Poids(point_de_contact) = 2^(-temps_avant_conversion / demi_vie)

Avec une demi-vie de 7 jours, un point de contact d’il y a 7 jours obtient 50 % de poids, 14 jours obtient 25 %, 21 jours obtient 12,5 %.

WITH decay_weights AS (
  SELECT
    user_id,
    transaction_id,
    channel,
    revenue,
    POW(
      0.5,
      TIMESTAMP_DIFF(conversion_timestamp, touchpoint_timestamp, HOUR)
        / (7.0 * 24)  -- demi-vie de 7 jours en heures
    ) AS raw_weight
  FROM touchpoints
  WHERE touchpoint_timestamp >= TIMESTAMP_SUB(
    conversion_timestamp, INTERVAL 30 DAY
  )
),
normalized AS (
  SELECT
    *,
    SUM(raw_weight) OVER (
      PARTITION BY user_id, transaction_id
    ) AS total_weight
  FROM decay_weights
)
SELECT
  user_id,
  transaction_id,
  channel,
  (raw_weight / total_weight) * revenue AS attributed_revenue
FROM normalized

L’étape de normalisation (diviser par total_weight) assure que le revenu attribué totalise le revenu réel par conversion. Sans elle, les poids bruts sont relatifs, pas absolus.

Choisissez la demi-vie pour correspondre à votre cycle de vente : 3 à 7 jours pour l’e-commerce impulsif, 7 à 14 jours pour le B2C réfléchi, 14 à 30 jours pour le B2B mid-market. Une demi-vie trop courte sous-crédite les canaux de notoriété ; trop longue, cela se rapproche de l’attribution linéaire.

Modèles algorithmiques : chaînes de Markov et valeurs de Shapley

Les modèles heuristiques (tout ce qui précède) assignent le crédit selon des hypothèses sur la position ou le timing. Les modèles algorithmiques calculent le crédit à partir des patterns de conversion observés.

L’attribution par chaînes de Markov modélise les parcours comme des transitions d’état entre canaux. Elle mesure l’effet de suppression : de combien la probabilité de conversion baisse-t-elle quand un canal est complètement supprimé ? Si supprimer la recherche payante fait baisser la probabilité globale de conversion de 50 % à 35 %, la recherche payante obtient un effet de suppression de 30 %. Les effets de suppression sont normalisés pour totaliser 100 % des conversions.

La partie SQL extrait les chemins de parcours et calcule les probabilités de transition entre les états de canaux. Les opérations matricielles pour le calcul de l’effet de suppression passent généralement en Python (packages ChannelAttribution ou marketing-attribution-models), puisque SQL n’est pas bien adapté à l’algèbre matricielle itérative.

L’attribution par valeurs de Shapley vient de la théorie des jeux coopératifs. Elle calcule la contribution marginale moyenne de chaque canal sur tous les sous-ensembles possibles de canaux. Les valeurs de Shapley satisfont de fortes propriétés d’équité (les crédits totalisent le total, les contributeurs égaux reçoivent un crédit égal, les canaux à valeur nulle reçoivent zéro), mais le calcul évolue en 2^n pour n canaux. L’approximation Monte Carlo rend cela praticable pour des nombres de canaux réels.

Les deux approches nécessitent plus de données que les modèles heuristiques : visez au minimum plusieurs centaines de conversions, et regroupez les canaux à faible volume (moins de 2 % des points de contact) dans un groupe « Autres » pour garder les matrices de transition stables. Voir Stratégie de regroupement des canaux d’attribution pour des conseils sur les décisions de regroupement de canaux.

Choisir un modèle

Exécuter plusieurs modèles en parallèle et utiliser le désaccord comme signal est une approche courante. Quand tous les modèles classent un canal de manière similaire, la confiance est plus élevée. Quand les classements divergent fortement, la divergence peut révéler soit un insight genuinement pertinent soit un problème de qualité des données qui mérite investigation.

Validation

Chaque modèle multi-touch devrait passer une vérification d’intégrité de base : le revenu attribué par conversion doit totaliser le revenu réel. Dans dbt, un test singulier détecte la dérive :

-- tests/attribution_revenue_integrity.sql
SELECT transaction_id, SUM(attributed_revenue) AS attributed, MAX(revenue) AS actual
FROM {{ ref('mrt__attribution__linear') }}
GROUP BY transaction_id
HAVING ABS(SUM(attributed_revenue) - MAX(revenue)) > 0.01

Pour les modèles d’attribution incrémentaux, appliquez un buffer de lookback (retraiter les N derniers jours) pour gérer les conversions arrivant en retard et les mises à jour rétroactives des points de contact qui créeraient autrement des lacunes silencieuses dans le revenu attribué.