Attribution position-based et time-decay dans BigQuery

L’attribution linéaire traite chaque touchpoint de la même façon. C’est un point de départ raisonnable, mais ça ignore une évidence : le premier touchpoint qui a fait découvrir un client et le dernier qui a déclenché la conversion comptent probablement plus que l’email ouvert entre les deux.

Les modèles position-based et time-decay répondent à ce problème en attribuant des poids différents aux touchpoints selon leur place dans le parcours ou leur proximité avec la conversion. Ces modèles demandent un SQL un peu plus complexe, mais produisent une attribution qui reflète mieux la réalité du marketing.

Modèles position-based : pondérer selon l’étape du parcours

Les modèles position-based attribuent du crédit en fonction de la position d’un touchpoint dans le parcours client. Le plus courant est le modèle U-shaped, qui met l’accent sur le premier et le dernier contact tout en créditant les étapes intermédiaires.

Le modèle U-shaped (40-20-40)

Le modèle U-shaped distribue le crédit ainsi :

• Premier touchpoint : 40 %
• Dernier touchpoint : 40 %
• Tous les touchpoints intermédiaires : 20 % répartis équitablement

La formule :

Credit(first) = 0.40
Credit(last) = 0.40
Credit(middle_i) = 0.20 / (n - 2)

Ce modèle fonctionne bien pour la plupart des scénarios B2C et B2B où vous souhaitez valoriser à la fois le canal qui a généré la notoriété initiale et celui qui a déclenché la conversion.

Le modèle W-shaped (30-30-30-10)

Les entreprises B2B avec des cycles de vente plus longs utilisent souvent l’attribution W-shaped, qui ajoute du poids à un touchpoint intermédiaire clé comme la création de lead ou la demande de démo :

Credit(first) = 0.30
Credit(key_middle) = 0.30
Credit(last) = 0.30
Credit(other) = 0.10 / (n - 3)

Le W-shaped nécessite d’identifier ce moment intermédiaire clé dans vos données, ce qui ajoute de la complexité à l’implémentation.

Cas limites

Les conversions à un seul touchpoint reçoivent 100 % du crédit. Les conversions à deux touchpoints se répartissent 50/50. Votre SQL doit gérer ces cas explicitement, sinon vous obtiendrez des résultats incorrects.

Attribution U-shaped dans BigQuery

Une implémentation complète du modèle U-shaped 40-20-40 :

WITH touchpoints_positioned AS (
  SELECT
    user_id,
    transaction_id,
    channel,
    revenue,
    touchpoint_timestamp,
    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

Ce que fait chaque partie :

• ROW_NUMBER() identifie la position de chaque touchpoint dans le parcours
• COUNT(*) OVER() donne le nombre total de touchpoints par conversion
• Le CASE gère d’abord les cas limites, puis applique la répartition 40-20-40
• La multiplication du poids par le revenu produit le montant attribué

Pour un client avec 5 touchpoints et une conversion de 100 $ :

• Touch 1 : 40 $ (40 %)
• Touch 2 : 6,67 $ (20 % ÷ 3)
• Touch 3 : 6,67 $ (20 % ÷ 3)
• Touch 4 : 6,67 $ (20 % ÷ 3)
• Touch 5 : 40 $ (40 %)

Variations de poids par industrie

La répartition 40-20-40 n’est pas universelle. Ajustez selon votre activité :

Attribution time-decay : pondérer par la récence

L’attribution time-decay accorde plus de crédit aux touchpoints survenus peu avant la conversion. Un touchpoint d’hier compte plus qu’un touchpoint d’il y a deux semaines.

La formule de décroissance exponentielle

Le time-decay utilise une décroissance exponentielle avec un paramètre de demi-vie :

Weight(touchpoint) = 2^(-days_before_conversion / half_life)

La demi-vie détermine la vitesse à laquelle le crédit diminue. Avec une demi-vie de 7 jours :

• Jour 0 (jour de conversion) : Poids = 1.0 (100 %)
• Jour 7 : Poids = 0.5 (50 %)
• Jour 14 : Poids = 0.25 (25 %)
• Jour 21 : Poids = 0.125 (12,5 %)

Google Analytics utilise une demi-vie de 7 jours par défaut. Ça fonctionne raisonnablement bien pour le e-commerce, mais c’est peut-être trop agressif pour le B2B.

Choisir la bonne demi-vie

La demi-vie devrait correspondre approximativement à la durée de votre cycle de vente :

Une demi-vie trop courte sous-crédite les canaux de haut de funnel. Une demi-vie trop longue se rapproche de l’attribution linéaire, ce qui rend le modèle inutile.

Attribution time-decay dans BigQuery

L’implémentation calcule les poids bruts, puis les normalise pour qu’ils totalisent 1 :

WITH decay_weights AS (
  SELECT
    user_id,
    transaction_id,
    channel,
    revenue,
    conversion_timestamp,
    touchpoint_timestamp,
    POW(
      0.5,
      TIMESTAMP_DIFF(conversion_timestamp, touchpoint_timestamp, MINUTE) / (7.0 * 24 * 60)
    ) 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

Ce que fait chaque partie :

• TIMESTAMP_DIFF(..., MINUTE) calcule l’écart temporel en minutes pour plus de précision
• 7.0 * 24 * 60 convertit la demi-vie de 7 jours en minutes (10 080 minutes)
• POW(0.5, time_ratio) applique la décroissance exponentielle
• La division par total_weight normalise pour que la somme des poids fasse 1

Pour un client avec des touchpoints aux jours -14, -7 et 0 :

• Jour -14 : raw_weight = 0.25, normalisé = 0.25/1.75 = 14,3 %
• Jour -7 : raw_weight = 0.5, normalisé = 0.5/1.75 = 28,6 %
• Jour 0 : raw_weight = 1.0, normalisé = 1.0/1.75 = 57,1 %

Paramétrer la demi-vie

Coder la demi-vie en dur rend l’expérimentation difficile. Utilisez une variable ou une table de configuration :

DECLARE half_life_days FLOAT64 DEFAULT 7.0;

WITH decay_weights AS (
  SELECT
    user_id,
    transaction_id,
    channel,
    revenue,
    conversion_timestamp,
    touchpoint_timestamp,
    POW(
      0.5,
      TIMESTAMP_DIFF(conversion_timestamp, touchpoint_timestamp, HOUR) / (half_life_days * 24)
    ) AS raw_weight
  FROM touchpoints
  WHERE touchpoint_timestamp >= TIMESTAMP_SUB(
    conversion_timestamp,
    INTERVAL 30 DAY
  )
)
-- ... suite de la requête

Cela vous permet de tester différentes valeurs de demi-vie et de comparer les résultats.

Construire les deux modèles dans dbt

Ajoutez ces modèles à votre structure de marts d’attribution existante :

models/
├── intermediate/
│   └── int__touchpoints.sql
├── marts/attribution/
│   ├── mrt__attribution_first_touch.sql
│   ├── mrt__attribution_last_touch.sql
│   ├── mrt__attribution_linear.sql
│   ├── mrt__attribution_position_based.sql   # Nouveau
│   └── mrt__attribution_time_decay.sql       # Nouveau

Modèle position-based avec poids configurables

Utilisez des variables dbt pour rendre les poids configurables :

-- mrt__attribution_position_based.sql
{% set first_touch_weight = var('attribution_first_weight', 0.4) %}
{% set last_touch_weight = var('attribution_last_weight', 0.4) %}
{% set middle_weight = 1.0 - first_touch_weight - last_touch_weight %}

WITH touchpoints_positioned AS (
  SELECT
    user_id,
    transaction_id,
    channel,
    source,
    medium,
    revenue,
    touchpoint_timestamp,
    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 {{ ref('int__touchpoints') }}
)
SELECT
  user_id,
  transaction_id,
  channel,
  source,
  medium,
  touchpoint_timestamp,
  CASE
    WHEN total_touches = 1 THEN 1.0
    WHEN total_touches = 2 THEN 0.5
    WHEN position = 1 THEN {{ first_touch_weight }}
    WHEN position = total_touches THEN {{ last_touch_weight }}
    ELSE {{ middle_weight }} / (total_touches - 2)
  END * revenue AS attributed_revenue,
  'position_based' AS attribution_model
FROM touchpoints_positioned

Configurez les poids dans dbt_project.yml :

vars:
  attribution_first_weight: 0.4
  attribution_last_weight: 0.4
  attribution_half_life_days: 7

Modèle time-decay avec demi-vie configurable

-- mrt__attribution_time_decay.sql
{% set half_life_days = var('attribution_half_life_days', 7) %}

WITH decay_weights AS (
  SELECT
    user_id,
    transaction_id,
    channel,
    source,
    medium,
    revenue,
    touchpoint_timestamp,
    conversion_timestamp,
    POW(
      0.5,
      TIMESTAMP_DIFF(conversion_timestamp, touchpoint_timestamp, HOUR) / ({{ half_life_days }} * 24.0)
    ) AS raw_weight
  FROM {{ ref('int__touchpoints') }}
),
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,
  source,
  medium,
  touchpoint_timestamp,
  (raw_weight / total_weight) * revenue AS attributed_revenue,
  'time_decay' AS attribution_model
FROM normalized

Tester les modèles pondérés

Vos tests doivent vérifier que les poids s’additionnent correctement :

-- tests/attribution_weights_sum_to_revenue.sql
WITH model_totals AS (
  SELECT
    transaction_id,
    SUM(attributed_revenue) AS total_attributed
  FROM {{ ref('mrt__attribution_position_based') }}
  GROUP BY transaction_id
),
actual_revenue AS (
  SELECT
    transaction_id,
    MAX(revenue) AS actual_revenue
  FROM {{ ref('int__touchpoints') }}
  GROUP BY transaction_id
)
SELECT
  m.transaction_id,
  m.total_attributed,
  a.actual_revenue
FROM model_totals m
JOIN actual_revenue a
  ON m.transaction_id = a.transaction_id
WHERE ABS(m.total_attributed - a.actual_revenue) > 0.01

Ce test ne renvoie des lignes que lorsque l’attribution ne correspond pas au revenu réel, ce qui facilite la détection des erreurs de calcul.

Choisir entre position-based et time-decay

Ces modèles reposent sur des hypothèses différentes quant à ce qui compte dans un parcours client.

Utilisez le position-based quand :

• Vous pensez que le premier et le dernier contact ont réellement plus d’impact
• Votre funnel a des moments clairs de “découverte” et de “conversion”
• Vous voulez créditer à la fois les canaux de notoriété et de closing
• Les cycles de vente sont relativement constants en durée

Utilisez le time-decay quand :

• Les touchpoints récents influencent visiblement plus la conversion
• Vous optimisez pour l’impact immédiat sur la conversion
• Les cycles de vente varient beaucoup en durée
• Vous voulez intégrer l’urgence et la récence dans le modèle

Envisagez de faire tourner les deux et de comparer les résultats. Quand position-based et time-decay produisent des classements de canaux similaires, vous pouvez avoir une confiance élevée dans les conclusions. Quand ils divergent significativement, c’est un signal pour investiguer pourquoi.

Vers les modèles data-driven

Le position-based et le time-decay améliorent les simples first/last-touch, mais ils reposent toujours sur des hypothèses quant à la répartition du crédit. Les poids sont des décisions business, pas des valeurs dérivées de vos données réelles.

Les modèles data-driven comme les chaînes de Markov et les valeurs de Shapley calculent les poids à partir des patterns de conversion observés. Ils répondent à la question : « Compte tenu de nos données historiques, quelle a été la contribution réelle de chaque canal aux conversions ? »

Ces modèles nécessitent plus de données (des centaines de conversions au minimum) et une implémentation plus complexe, mais ils fournissent l’attribution la plus défendable quand vous disposez d’un volume suffisant.

Pour la plupart des entreprises, construire des modèles first-touch, last-touch, linéaire, position-based et time-decay solides constitue une base robuste. Faites-les tourner en parallèle, comparez les résultats entre modèles, et utilisez les divergences pour comprendre où votre attribution est fiable et où elle reste incertaine.