SQL : Créer un groupe de contrôle dans BigQuery

Par Nicolas le

Objectif

L'objectif est de segmenter une table entre un groupe de traitement (lignes sur lesquelles nous allons réaliser une action), et un groupe de contrôle (lignes sur lesquelles nous n'allons rien faire).
Pourquoi faire cela me direz vous ? Dans ce cas précis, voici ce que je souhaitais faire :
  • Importer les données CRM dans BigQuery (c'est à dire tous les contacts de l'entreprise)
  • A partir de ces données, créer des segments d'utilisateurs
  • Pousser ces segments vers différentes plate-formes, afin de diffuser des publicités ou emails personnalisés (par exemple Facebook Ads, Google Ads, ou un outil d'emailing).
Dans le cadre de ce projet, je souhaite créer un groupe de contrôle, afin de mesurer l'impact de mes publicités : ce groupe de contrôle est constitué d'un pourcentage des utilisateurs (15% ici), que je ne vais pas inclure dans mes listes d'audience.
💡
💡 Je pourrai ainsi mesurer l'impact de ma publicité (remarketing) via ce groupe de contrôle, en comparant par exemple le taux de réachat entre les deux groupes, ou le taux de transformation.

Problématique

L'enjeu est d'avoir quelque chose qui soit :
  • Le plus aléatoire possible
  • Le plus reproductible possible
Pour cela, nous allons utiliser la fonction farm_fingerprint, qui génère un chiffre aléatoire à partir d'une chaîne de caractère, qui sera ici l'adresse email (que l'on conseille préalablement de "hasher" pour des raisons de protection des données).
Ce chiffre étant aléatoire, il y a donc statistiquement 15% de chance que le reste de sa division par 100 soit... inférieure à 100. C'est donc ce que nous allons utiliser :
sql
SELECT //Je sélectionneles champs que je vais utiliser par la suite id, property_company as company, property_createdate as createdate, property_email as email, property_lifecyclestage as lifecyclestage, // J'ajoute ma colonne "control group" CASE WHEN MOD(ABS(farm_fingerprint(property_email)), 100) < 15 THEN 'control group' ELSE 'treatment group' END AS categ FROM `reporting-digital.hubspot_unnest.contact`
Ce qui est intéressant ici, c'est que pour une adresse email donnée, j'aurai toujours le même résultat.

Résultat

  • En entrée, une table avec mes emails :
Image without caption
  • En sortie, une table avec la colonne prenant une valeur aléatoire : "control group" dans 15% des cas :
Image without caption