Implémentation SQL de l'attribution Markov

L’attribution par chaîne de Markov complète nécessite des opérations matricielles qui poussent SQL au-delà de ses limites. L’approche pratique consiste à utiliser SQL pour l’extraction des parcours et le calcul des probabilités de transition, puis à transférer à Python le calcul de l’effet de suppression. SQL effectue la lourde préparation des données ; Python gère l’algèbre linéaire.

Cette note couvre la partie SQL — extraire les parcours depuis les données de points de contact et calculer les comptages de transitions — qui est là où se concentre la majorité de l’effort d’ingénierie.

Extraction des parcours dans BigQuery

La première étape consiste à extraire les parcours depuis vos données de points de contact. Vous avez besoin de deux jeux de données : les parcours qui ont abouti à une conversion et les parcours qui n’ont pas abouti. Les deux sont importants pour le modèle Markov car les parcours sans conversion établissent les transitions vers l’état NULL.

WITH touchpoints AS (
  SELECT
    user_id,
    channel,
    event_timestamp,
    conversion_id
  FROM {{ ref('int__touchpoints') }}
  WHERE event_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
),

converting_paths AS (
  SELECT
    t.user_id,
    t.conversion_id,
    STRING_AGG(t.channel, ' > ' ORDER BY t.event_timestamp) AS journey_path,
    'conversion' AS outcome
  FROM touchpoints t
  INNER JOIN {{ ref('int__conversions') }} c
    ON t.user_id = c.user_id
    AND t.conversion_id = c.conversion_id
  WHERE t.event_timestamp BETWEEN
    TIMESTAMP_SUB(c.conversion_timestamp, INTERVAL 30 DAY)
    AND c.conversion_timestamp
  GROUP BY t.user_id, t.conversion_id
),

non_converting_paths AS (
  SELECT
    t.user_id,
    CAST(NULL AS STRING) AS conversion_id,
    STRING_AGG(t.channel, ' > ' ORDER BY t.event_timestamp) AS journey_path,
    'null' AS outcome
  FROM touchpoints t
  LEFT JOIN {{ ref('int__conversions') }} c
    ON t.user_id = c.user_id
  WHERE c.user_id IS NULL
  GROUP BY t.user_id
)

SELECT
  user_id,
  conversion_id,
  journey_path,
  outcome
FROM converting_paths

UNION ALL

SELECT
  user_id,
  conversion_id,
  journey_path,
  outcome
FROM non_converting_paths

Cela produit des lignes telles que :

Quelques décisions de conception à noter :

• La fenêtre externe de 90 jours définit l’horizon temporel d’extraction des points de contact. Elle doit être plus large que votre fenêtre d’attribution pour capturer le contexte complet du parcours.
• La fenêtre de conversion de 30 jours dans le CTE converting_paths définit quels points de contact sont éligibles pour une conversion spécifique. Ajustez-la selon votre cycle de vente.
• Les parcours sans conversion incluent les utilisateurs qui n’ont jamais converti. Ces données sont essentielles pour obtenir des probabilités de transition précises — sans elles, chaque parcours aboutit à une conversion et le modèle ne peut pas distinguer les canaux à haute probabilité de ceux à faible probabilité.

Comptage des transitions

À partir des parcours extraits, calculez les comptages de transitions — la fréquence à laquelle chaque transition état-à-état se produit. C’est là que la structure du modèle Markov prend forme.

WITH path_transitions AS (
  SELECT
    journey_path,
    outcome,
    SPLIT(journey_path, ' > ') AS channels
  FROM journey_paths
),

transitions AS (
  SELECT
    'START' AS from_state,
    channels[OFFSET(0)] AS to_state,
    COUNT(*) AS transition_count
  FROM path_transitions
  GROUP BY from_state, to_state

  UNION ALL

  SELECT
    channels[OFFSET(i)] AS from_state,
    channels[OFFSET(i + 1)] AS to_state,
    COUNT(*) AS transition_count
  FROM path_transitions,
    UNNEST(GENERATE_ARRAY(0, ARRAY_LENGTH(channels) - 2)) AS i
  GROUP BY from_state, to_state

  UNION ALL

  SELECT
    channels[OFFSET(ARRAY_LENGTH(channels) - 1)] AS from_state,
    CASE outcome
      WHEN 'conversion' THEN 'CONVERSION'
      ELSE 'NULL'
    END AS to_state,
    COUNT(*) AS transition_count
  FROM path_transitions
  GROUP BY from_state, to_state
)

SELECT
  from_state,
  to_state,
  transition_count,
  transition_count / SUM(transition_count) OVER (PARTITION BY from_state) AS transition_probability
FROM transitions

Cette requête gère trois types de transitions :

1. Transitions START — de START vers le premier canal de chaque parcours
2. Transitions canal-à-canal — en utilisant GENERATE_ARRAY et UNNEST pour itérer sur chaque paire consécutive dans le parcours
3. Transitions terminales — du dernier canal vers CONVERSION ou NULL

La colonne transition_probability est calculée en divisant chaque comptage de transitions par le total des transitions depuis cet état, en utilisant une fonction fenêtre. Cela vous donne la matrice de transition Markov sous forme tabulaire.

De SQL à Python

La table de probabilités de transition est là où le travail SQL s’arrête. Les étapes suivantes — calculer la probabilité de conversion globale depuis START, supprimer chaque canal et recalculer, puis normaliser les effets de suppression en parts d’attribution — nécessitent des opérations matricielles que SQL ne peut pas gérer efficacement.

Exportez la table de transition vers Python et utilisez une bibliothèque :

• R : le package ChannelAttribution dispose de backends C++ optimisés pour l’échelle
• Python : marketing-attribution-models ou des implémentations NumPy personnalisées

Le transfert est propre : SQL produit une table avec les colonnes from_state, to_state et transition_probability. Python la lit, construit la matrice et calcule les effets de suppression.

Intégration dans dbt

Dans un projet dbt, cela s’intègre naturellement dans le pattern de comparaison d’attribution :

models/
├── intermediate/
│   ├── int__touchpoints.sql
│   ├── int__conversions.sql
│   └── int__journey_paths.sql        # Requête d'extraction des parcours
└── marts/
    └── attribution/
        ├── mrt__attribution__transitions.sql  # Comptage des transitions
        ├── mrt__attribution__first_touch.sql
        ├── mrt__attribution__last_touch.sql
        └── mrt__attribution__comparison.sql

Le modèle int__journey_paths extrait les parcours. Le modèle mrt__attribution__transitions calcule les probabilités de transition. Un script Python (ou un modèle Python dbt dans dbt Core 1.3+) lit la table de transitions et écrit les résultats de l’attribution Markov dans l’entrepôt.

Les résultats Markov peuvent ensuite être ajoutés à votre modèle de comparaison aux côtés des modèles heuristiques, vous donnant une ligne model_type = 'markov' dans la table de comparaison.

Considérations relatives à la qualité des données

La qualité de l’attribution Markov dépend entièrement de la qualité des données de transition sous-jacentes.

Le groupement des canaux est critique. Trop de canaux produit des matrices de transition creuses où de nombreuses valeurs de cellules sont nulles ou basées sur une poignée d’observations. Commencez avec 5 à 10 groupes de canaux de haut niveau et ajoutez de la granularité à mesure que le volume de données augmente.

Exigences minimales en données : visez environ 10 fois plus de transitions que de types de transitions uniques. Avec 10 canaux (plus START, CONVERSION, NULL), vous avez jusqu’à 13 x 13 = 169 transitions possibles. Cela signifie au moins 1 690 transitions de parcours totales, généralement réalisables avec quelques centaines de conversions.

Les modèles d’ordre supérieur (où l’état suivant dépend de l’état actuel plus les états précédents) nécessitent exponentiellement plus de données. Un modèle de second ordre avec 10 canaux a jusqu’à 169 x 13 = 2 197 transitions possibles. À moins d’avoir des dizaines de milliers de conversions, restez au premier ordre.

Les canaux répétés dans un parcours (Email > Email > Email) sont valides et courants. Ils représentent des utilisateurs revenant plusieurs fois sur le même canal. Ne déduplifiez pas les touches consécutives sauf si vous avez une raison spécifique de le faire — la probabilité de self-transition (Email -> Email) est une information significative sur la fidélité du canal.