Scoring de leads basé sur des règles dans dbt

Le scoring de leads basé sur des règles est une somme pondérée : chaque signal reçoit une valeur en points, et le score total détermine la priorité commerciale. Cela peut paraître simpliste, mais bien mis en œuvre dans dbt, c’est versionné, testable et maintenable par des non-ingénieurs. Cette dernière partie est la plus difficile.

Le pattern de base

Le modèle de scoring se situe dans la couche marts. Il prend en entrée une table de features — un ensemble préparé de signaux par lead — et produit un score par lead.

-- models/marts/mrt__sales__lead_scores.sql
SELECT
  lead_id,
  (lead__website_visits * {{ var('weight_website_visits', 5) }})
  + (lead__pricing_page_views * {{ var('weight_pricing_views', 15) }})
  + (lead__email_opens * {{ var('weight_email_opens', 3) }})
  + (lead__form_submissions * {{ var('weight_form_submissions', 20) }})
  + (lead__content_downloads * {{ var('weight_content_downloads', 10) }})
  AS lead__engagement_score,

  CASE
    WHEN lead__job_seniority = 'C-Level' THEN 30
    WHEN lead__job_seniority = 'VP' THEN 25
    WHEN lead__job_seniority = 'Director' THEN 20
    WHEN lead__job_seniority = 'Manager' THEN 10
    ELSE 0
  END AS lead__demographic_score

FROM {{ ref('int__lead__scoring_features') }}

Les appels {{ var() }} sont le détail clé. Ils récupèrent les poids depuis dbt_project.yml plutôt que de coder les nombres en dur dans le SQL. Quand le marketing souhaite augmenter le poids des demandes de démo, il suffit de mettre à jour un fichier YAML — sans déboguer un modèle SQL.

La décroissance temporelle

Les comptages comportementaux bruts sont un point de départ, mais ils ne capturent pas la vélocité d’intention. Ajoutez une décroissance exponentielle pour que l’activité récente compte davantage que l’activité ancienne :

-- Appliquer avant d'agréger les signaux comportementaux
lead__raw_score * EXP(-0.1 * DATE_DIFF(CURRENT_DATE(), event__occurred_at, DAY))

Cette fonction divise la valeur du score par deux tous les ~7 jours. Une soumission de formulaire d’hier compte à environ 90 % de sa valeur nominale. La même soumission d’il y a trois semaines compte à ~13 %. Un lead actif il y a six mois cesse de polluer votre niveau hot.

La constante de décroissance 0.1 est un point de départ. Les cycles de vente plus rapides (essais SaaS, ACV faible) peuvent justifier une décroissance plus rapide — essayez 0.15 ou 0.2. Les cycles de vente plus longs (enterprise, deals multi-parties prenantes) peuvent justifier une décroissance plus lente — essayez 0.05.

Les signaux négatifs

Tout modèle de scoring a besoin de signaux négatifs, et la plupart des premières ébauches les oublient.

Sans scoring négatif, les leads morts s’accumulent dans le niveau hot au fil du temps. Les commerciaux voient un lead avec « score : 85 » qui n’a plus donné signe de vie depuis huit mois, le contactent, s’entendent dire « nous avons choisi un concurrent », et perdent confiance dans le modèle. Ajoutez :

• Désinscription : -20
• Rebond d’e-mail : -15
• Inactivité de 30+ jours : -10
• E-mail de domaine concurrent : -50 (optionnel, mais puissant si vous connaissez votre liste de concurrents)

Ces scores négatifs appartiennent au même modèle de scoring que les signaux positifs. Le score final d’un lead correspond à ses signaux positifs accumulés moins les signaux négatifs qui se sont déclenchés.

Rendre les poids maintenables

Coder les poids en dur dans le SQL fonctionne pour un prototype mais pose problème quand le marketing veut expérimenter avec différentes pondérations. Trois fonctionnalités dbt rendent cela gérable.

Variables dbt

Dans dbt_project.yml :

vars:
  weight_website_visits: 5
  weight_pricing_views: 15
  weight_email_opens: 3
  weight_form_submissions: 20
  weight_content_downloads: 10

Chaque modèle qui référence ces poids récupère les nouvelles valeurs au prochain run quand vous les modifiez ici. Vous pouvez aussi surcharger au moment de l’exécution pour les tests : dbt run --vars 'weight_pricing_views: 25' — sans changement de code, sans commit nécessaire, expérimentez et revenez en arrière librement.

Fichiers seed pour les non-ingénieurs

Pour les équipes où le marketing a besoin d’un contrôle direct, un fichier seed est la meilleure approche. Créez seeds/scoring_rules.csv :

signal,weight,category
pricing_page_view,15,behavioral
demo_request,25,behavioral
whitepaper_download,10,behavioral
vp_or_above,25,demographic
target_industry,20,firmographic
unsubscribe,-20,behavioral

Après modification, dbt seed charge les règles mises à jour. Votre modèle de scoring fait une jointure avec le seed pour récupérer les poids dynamiquement :

-- Dans le modèle de scoring
SELECT
  f.lead_id,
  SUM(
    CASE WHEN f.signal_name = r.signal THEN f.signal_value * r.weight ELSE 0 END
  ) AS lead__total_score
FROM {{ ref('int__lead__signals_long') }} f
JOIN {{ ref('scoring_rules') }} r ON f.signal_name = r.signal
GROUP BY f.lead_id

Le changement passe par Git (puisque les seeds sont versionnés), vous disposez donc d’un historique complet de chaque décision de pondération et pouvez revenir en arrière si une nouvelle pondération envoie les mauvais leads aux commerciaux.

Macros Jinja pour la logique répétée

Si vous appliquez la décroissance temporelle dans plusieurs modèles, une macro vous garde DRY. Plutôt que de copier-coller la formule EXP(-0.1 * ...) partout, écrivez-la une fois :

-- macros/apply_time_decay.sql
{% macro apply_time_decay(score_col, date_col, decay_rate=0.1) %}
  {{ score_col }} * EXP(-{{ decay_rate }} * DATE_DIFF(CURRENT_DATE(), {{ date_col }}, DAY))
{% endmacro %}

Appelez-la ensuite dans n’importe quel modèle : {{ apply_time_decay('lead__raw_score', 'event__occurred_at') }}. Pour en savoir plus sur les cas où les macros valent leur complexité, voir Macros dbt.

Définir les seuils de score

Une fois le score total établi, définissez des niveaux. Un système simple à trois niveaux :

Ces seuils nécessiteront une calibration. Commencez par des chiffres ronds, faites tourner le modèle pendant quelques semaines, et demandez des retours aux commerciaux. Si le niveau hot représente 30 % des leads, le seuil est trop bas. S’il représente 0,5 % des leads et que les commerciaux manquent de prospects, le seuil est trop élevé.

Les seuils doivent vivre dans dbt_project.yml en tant que vars, pas en dur dans le modèle, pour pouvoir les ajuster sans modifier le SQL.

Attentes de précision

Un scoring basé sur des règles bien calibré atteint généralement 60 à 70 % de précision sur la prédiction de conversion. C’est bien au-dessus du hasard (qui se situe à votre taux de conversion de base, souvent 2 à 5 %), mais laisse de la marge pour l’amélioration.

Si vous avez 1 000+ conversions historiques, BigQuery ML peut potentiellement pousser la précision à 80-90 % en trouvant des patterns dans les données que vos règles ont manqués. Le chemin pratique : construisez d’abord le modèle basé sur des règles (un ou deux jours de travail), suivez les conversions pendant quelques mois, puis entraînez et comparez.

L’approche hybride fonctionne souvent le mieux en pratique : le ML génère le score principal (probabilité de conversion), et les règles gèrent les cas limites où vous en savez plus que le modèle. Marquez toujours un lead comme hot s’il demande une démo, quelles que soient les prédictions du modèle. Le modèle gère la nuance ; les règles gèrent la certitude.

Voir Patterns de Reverse ETL pour l’activation CRM pour savoir comment faire remonter ces scores dans Salesforce ou HubSpot où les représentants travaillent réellement.