Attribution first-touch, last-touch et linéaire en SQL

L’attribution des plateformes publicitaires est pratique, jusqu’au moment où vous avez besoin de lui faire confiance. Meta ne voit pas vos touchpoints Google. Le Data-Driven Attribution de Google est une boîte noire qui bascule silencieusement en last-click quand les seuils de données ne sont pas atteints. Et avec un taux d’opt-in iOS ATT autour de 35 %, une part significative des interactions mobiles échappe complètement au tracking.

Construire l’attribution dans votre data warehouse résout ces problèmes. Vous maîtrisez la méthodologie, voyez chaque touchpoint et pouvez auditer la logique. Vous pouvez aussi intégrer les données de toutes les plateformes dans un parcours client unifié. Pour une vision plus large de l’attribution dans le warehouse, consultez mon guide d’attribution.

Cet article couvre les trois modèles d’attribution fondamentaux : first-touch, last-touch et linéaire.

Pourquoi ces modèles restent pertinents

Selon les études, seuls 14 % des marketeurs estiment que l’attribution last-click est efficace, et pourtant elle reste le défaut sur la plupart des plateformes. Les modèles simples fonctionnent bien quand on comprend leurs hypothèses. Ils échouent quand on les applique aveuglément.

L’attribution first-touch donne 100 % du crédit au premier touchpoint. Elle répond à la question : « Quels canaux amènent les gens dans notre funnel ? » C’est utile pour les campagnes de notoriété, l’acquisition de nouveaux clients et l’optimisation du haut de funnel. Le biais : elle ignore tout ce qui s’est passé entre la découverte et la conversion.

L’attribution last-touch donne 100 % du crédit au dernier touchpoint avant conversion. Elle répond à : « Qu’est-ce qui conclut la vente ? » C’est adapté aux campagnes de réponse directe, aux cycles d’achat courts et à l’évaluation du retargeting. Le biais : elle ignore complètement les canaux de découverte et de nurturing.

L’attribution linéaire distribue le crédit à parts égales sur tous les touchpoints. Elle répond à : « Quels touchpoints contribuent au parcours ? » C’est une vision multi-touch équilibrée quand vous estimez que chaque touchpoint compte. Le biais : elle peut surévaluer des touchpoints à faible impact dans les parcours longs.

Aucun de ces modèles n’est « correct ». Ce sont des prismes, chacun révélant un aspect différent de l’efficacité de votre marketing.

Prérequis en termes de données

L’attribution nécessite des données au niveau du touchpoint avec des champs spécifiques. Voici ce qu’il vous faut :

Le champ user_id mérite une attention particulière. Le user_pseudo_id de GA4 est lié au device : la même personne sur mobile, laptop et desktop apparaît comme trois utilisateurs distincts sans résolution d’identité. Pour une attribution fiable, vous devrez à terme joindre les user_id authentifiés aux identifiants anonymes de device.

Fenêtres de lookback par secteur

La fenêtre de lookback détermine jusqu’où remonter dans le temps pour les touchpoints précédant une conversion. Elle varie considérablement selon le cycle d’achat :

Une fenêtre de lookback mal calibrée exclut des touchpoints pertinents (trop courte) ou attribue du crédit à du marketing sans rapport (trop longue).

Attribution first-touch en SQL

L’attribution first-touch attribue tout le crédit au premier touchpoint du parcours client. Le pattern SQL utilise ROW_NUMBER() pour identifier le touchpoint le plus ancien par conversion.

WITH touchpoints_numbered AS (
  SELECT
    user_id,
    transaction_id,
    channel,
    revenue,
    touchpoint_timestamp,
    conversion_timestamp,
    ROW_NUMBER() OVER (
      PARTITION BY user_id, transaction_id
      ORDER BY touchpoint_timestamp ASC
    ) 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

Quelques points à noter :

• PARTITION BY user_id, transaction_id regroupe les touchpoints par conversion
• ORDER BY touchpoint_timestamp ASC place les touchpoints les plus anciens en premier
• WHERE touch_position = 1 ne garde que le premier touchpoint
• Le filtre de fenêtre de lookback (30 jours ici) délimite les touchpoints éligibles

Vous obtenez une ligne par conversion, avec la totalité du revenu attribuée au canal de découverte.

Attribution last-touch en SQL

L’attribution last-touch est quasi identique. Il suffit de remplacer ASC par DESC dans le tri :

WITH touchpoints_numbered AS (
  SELECT
    user_id,
    transaction_id,
    channel,
    revenue,
    touchpoint_timestamp,
    conversion_timestamp,
    ROW_NUMBER() OVER (
      PARTITION BY user_id, transaction_id
      ORDER BY touchpoint_timestamp DESC
    ) 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

Ce seul changement (DESC au lieu de ASC) bascule de « premier touchpoint » à « dernier touchpoint avant conversion ».

Le last-touch reste le défaut sur la plupart des plateformes parce qu’il corrèle bien avec la performance en réponse directe. Pour les cycles d’achat courts où le déclencheur de conversion compte le plus, c’est souvent le bon choix.

Attribution linéaire en SQL

L’attribution linéaire distribue le revenu à parts égales entre tous les touchpoints du parcours. Au lieu de désigner un gagnant, chaque touchpoint reçoit la même part.

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

Comment fonctionne la répartition :

• COUNT(*) OVER (PARTITION BY ...) calcule le nombre total de touchpoints par conversion
• revenue / total_touches divise la valeur de conversion à parts égales
• Chaque touchpoint a sa propre ligne avec un crédit fractionnaire

Un client ayant eu cinq touchpoints avant un achat de 100 $ produirait cinq lignes, chacune avec 20 $ de revenu attribué.

Agrégation pour le reporting par canal

Les requêtes ci-dessus produisent une attribution au niveau du touchpoint. Pour un reporting de performance par canal, agrégez les résultats :

SELECT
  channel,
  COUNT(DISTINCT transaction_id) AS conversions_touched,
  SUM(attributed_revenue) AS total_attributed_revenue,
  SUM(attributed_revenue) / COUNT(DISTINCT transaction_id) AS avg_attributed_value
FROM linear_attribution_results
GROUP BY channel
ORDER BY total_attributed_revenue DESC

Pour les modèles first-touch et last-touch, conversions_touched correspond au nombre réel de conversions puisque chaque conversion est mappée à un seul canal. Pour l’attribution linéaire, conversions_touched représente le nombre de conversions auxquelles le canal a participé, ce qui est utile pour comprendre les conversions « assistées ».

Structure des couches dbt

Ces modèles d’attribution s’intègrent naturellement dans le pattern base, intermediate, marts de dbt :

models/
├── base/
│   └── base__ga4__events.sql               # Nettoyage des événements bruts
├── intermediate/
│   ├── int__touchpoints_mapped.sql         # Extraction des touchpoints marketing
│   └── int__conversion_paths_joined.sql    # Construction des parcours utilisateur
├── marts/attribution/
│   ├── mrt__attribution__first_touch.sql
│   ├── mrt__attribution__last_touch.sql
│   ├── mrt__attribution__linear.sql
│   └── mrt__attribution__comparison.sql

Couche base

La couche base nettoie les événements bruts. Pour les exports BigQuery de GA4, cela signifie unnester les structures imbriquées et standardiser les noms de colonnes (voir mes modèles dbt GA4 pour un guide plus détaillé) :

-- base__ga4__events.sql
SELECT
  user_pseudo_id,
  (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
  event_timestamp,
  event_name,
  traffic_source.source,
  traffic_source.medium,
  traffic_source.name AS campaign
FROM {{ source('ga4', 'events') }}

Couche intermediate

La couche intermediate construit la table de touchpoints que les modèles d’attribution consomment. C’est ici que vous définissez ce qui constitue un « touchpoint » et joignez les événements de touchpoint aux conversions depuis le même modèle base :

-- int__touchpoints_mapped.sql
WITH touchpoint_events AS (
  SELECT
    user_pseudo_id,
    session_id,
    event_timestamp,
    COALESCE(source, '(direct)') AS source,
    COALESCE(medium, '(none)') AS medium,
    CONCAT(COALESCE(source, '(direct)'), ' / ', COALESCE(medium, '(none)')) AS channel
  FROM {{ ref('base__ga4__events') }}
  WHERE session_id IS NOT NULL
),

conversion_events AS (
  SELECT
    user_pseudo_id,
    event_timestamp,
    transaction_id,
    revenue
  FROM {{ ref('base__ga4__events') }}
  WHERE event_name = 'purchase'
)

SELECT
  t.user_pseudo_id AS user_id,
  c.transaction_id,
  t.session_id,
  t.event_timestamp AS touchpoint_timestamp,
  c.event_timestamp AS conversion_timestamp,
  t.source,
  t.medium,
  t.channel,
  c.revenue
FROM touchpoint_events t
JOIN conversion_events c
  ON t.user_pseudo_id = c.user_pseudo_id
  AND t.event_timestamp <= c.event_timestamp
  AND t.event_timestamp >= TIMESTAMP_SUB(c.event_timestamp, INTERVAL 30 DAY)

Couche marts

La couche marts applique la logique d’attribution. Chaque modèle a son propre fichier :

-- mrt__attribution__first_touch.sql
WITH touchpoints_numbered AS (
  SELECT
    user_id,
    transaction_id,
    channel,
    source,
    medium,
    touchpoint_timestamp,
    revenue,
    ROW_NUMBER() OVER (
      PARTITION BY user_id, transaction_id
      ORDER BY touchpoint_timestamp ASC
    ) AS touch_position
  FROM {{ ref('int__touchpoints_mapped') }}
)
SELECT
  user_id,
  transaction_id,
  channel,
  source,
  medium,
  revenue AS attributed_revenue,
  'first_touch' AS attribution_model
FROM touchpoints_numbered
WHERE touch_position = 1

Tester que l’attribution s’additionne correctement

Ajoutez un test singulier dbt pour vérifier que les crédits d’attribution correspondent au revenu réel. Ce test renvoie des lignes uniquement quand les totaux divergent, ce que dbt traite comme un échec :

-- tests/attribution_revenue_sums_correctly.sql
WITH attribution_totals AS (
  SELECT SUM(attributed_revenue) AS total_attributed
  FROM {{ ref('mrt__attribution__linear') }}
),
actual_totals AS (
  SELECT SUM(revenue) AS total_actual
  FROM {{ ref('base__ga4__events') }}
  WHERE event_name = 'purchase'
)
SELECT
  total_attributed,
  total_actual
FROM attribution_totals
CROSS JOIN actual_totals
WHERE ABS(total_attributed - total_actual) >= 0.01

Comparer les modèles côte à côte

Créez une vue de comparaison qui fait l’union de tous les modèles avec un identifiant :

-- mrt__attribution__comparison.sql
SELECT *, 'first_touch' AS model FROM {{ ref('mrt__attribution__first_touch') }}
UNION ALL
SELECT *, 'last_touch' AS model FROM {{ ref('mrt__attribution__last_touch') }}
UNION ALL
SELECT *, 'linear' AS model FROM {{ ref('mrt__attribution__linear') }}

Cela permet de filtrer dans vos dashboards pour basculer entre les modèles et observer comment le crédit se redistribue entre les canaux. Quand l’email performe bien en last-touch mais faiblement en first-touch, vous apprenez quelque chose sur son rôle dans le funnel.

Au-delà des bases

First-touch, last-touch et linéaire sont des briques de construction. Une fois cette fondation en place :

Les modèles position-based et time-decay attribuent un crédit pondéré plutôt qu’égal. Le position-based (comme le modèle « U-shaped » 40-20-40) met l’accent sur les premier et dernier touchpoints, tandis que le time-decay accorde plus de crédit aux interactions récentes. Je couvre les deux dans le prochain article de cette série.

Les modèles data-driven utilisent les chaînes de Markov ou les valeurs de Shapley pour calculer le crédit en fonction des probabilités réelles de conversion. Ils nécessitent davantage de données (des centaines de conversions au minimum) mais offrent la vision la plus nuancée de la contribution de chaque canal.

Les patterns de code présentés ici (window functions pour le positionnement, agrégations pour le comptage, jointures pour la construction des parcours) s’appliquent à toutes ces approches plus sophistiquées. Maîtrisez ces fondamentaux et les modèles avancés deviennent de simples extensions.

Points clés

Construire l’attribution en SQL vous donne une transparence et un contrôle que les outils des plateformes ne peuvent pas égaler. Commencez par ces trois modèles, comprenez ce que chacun révèle sur votre marketing, et laissez cela guider le choix du modèle adapté à votre contexte.

Le code est plus simple qu’on ne le pense. Quelques window functions, une table de touchpoints bien structurée, et vous avez une attribution que vous comprenez réellement.