Guide pratique · E-commerce · SaaS · Lecture : ~22 min Teste ton niveau sur modai-lab.com/test-sql/
Pourquoi ce guide existe
Le marketing est le service qui pose le plus de questions à la data. Et c'est aussi celui où les chiffres sont le plus souvent faux.
Pas parce que les requêtes plantent. Parce qu'elles tournent, renvoient un joli chiffre… et que personne ne vérifie :
Le ROAS de Meta est à 6,2 dans ton dashboard, à 3,1 dans celui de la finance. Qui a raison ?
Le funnel affiche 140 % de passage du panier au paiement. Impossible, et pourtant c'est en prod.
Le CAC du canal SEA est divisé par deux depuis qu'un stagiaire a "corrigé" la jointure.
L'A/B test donne la variante B gagnante… parce qu'on a compté les clics au lieu des utilisateurs.
Le marketing analytics, ce n'est pas du SQL compliqué. C'est du SQL où la granularité et la définition des métriques font tout.
Ce que tu vas trouver :
L'attribution first touch / last touch avec les fonctions de fenêtre
Le funnel de conversion sans taux impossibles
Le CAC et le ROAS par canal sans fan-out ni division par zéro
La LTV par cohorte et par canal d'acquisition
L'analyse d'A/B tests et de campagnes email avec la bonne unité de mesure
Un prompt Claude pour auditer tes requêtes marketing
Une checklist avant d'envoyer un chiffre au CMO
🧪 Calibre ton niveau avant de commencer
Tu sais calculer un taux de conversion. Mais saurais-tu attribuer chaque commande au premier canal qui a amené le client, sans dupliquer une seule ligne ? C'est la question qui sépare le reporting du vrai marketing analytics.
_Résultat en direct · Niveau calibré · Parcours personnalisé_
Le modèle de données utilisé dans ce guide
Toutes les requêtes s'appuient sur ce schéma, très proche de ce que tu trouveras dans un data warehouse e-commerce ou SaaS (BigQuery, Snowflake, Redshift).
Règle d'or du marketing analytics : avant chaque jointure, écris la granularité des deux tables. 80 % des erreurs de ce guide viennent de là.
PARTIE 1 — Attribution : à quel canal revient cette commande ?
Le mécanisme
Un client visite ton site plusieurs fois avant d'acheter : d'abord via une pub Meta, puis via Google, puis en direct. À quel canal attribuer la vente ?
Les deux modèles que tout analyste marketing doit savoir écrire :
First touch : le crédit revient au canal de la première session du client. Il mesure ce qui fait découvrir la marque.
Last touch : le crédit revient au canal de la dernière session avant l'achat. Il mesure ce qui déclenche l'achat.
Techniquement, les deux reposent sur la même brique : ROW_NUMBER() pour isoler une seule session par client (ou par commande), puis une jointure 1-1.
❌ Ce que fait le débutant
-- Objectif : CA par canal d'acquisition
SELECT
s.utm_source,
SUM(o.montant) AS ca
FROM orders o
JOIN sessions s ON s.user_id = o.user_id -- ❌ jointure 1-N : chaque commande est
GROUPBY s.utm_source; -- dupliquée autant de fois que le client a de sessions
-- Résultat : meta 100 · google 100 · direct 100 → 300 € de CA "attribué" pour 100 € réels
sql-marketing-analytics · 01
Le total par canal dépasse le CA réel. Le marketing pense que chaque canal performe trois fois mieux qu'en vrai.
✅ Ce qu'on attend d'un analyste
-- FIRST TOUCH : canal de la première session de chaque client
WITH premiere_session AS (
SELECT
user_id,
COALESCE(utm_source, 'direct') AS canal_acquisition, -- pas d'UTM = trafic direct
ROW_NUMBER() OVER (
PARTITION BY user_id
ORDERBY started_at, session_id -- session_id départage les égalités
) AS rang
FROM sessions
)
SELECT
ps.canal_acquisition,
COUNT(DISTINCT o.user_id) AS nb_clients,
SUM(o.montant) AS ca
FROM orders o
JOIN premiere_session ps
ON ps.user_id = o.user_id
AND ps.rang = -- ✅ 1 seule ligne par client → jointure 1-1
WHERE o.statut = 'livré'
GROUPBY ps.canal_acquisition
ORDERBY ca DESC;
-- Résultat : meta 100 € ✅ (total = CA réel)
sql-marketing-analytics · 02
-- LAST TOUCH : dernière session AVANT chaque commande
WITH sessions_avant_commande AS (
SELECT
o.order_id,
o.montant,
COALESCE(s.utm_source, 'direct') AS canal,
ROW_NUMBER() OVER (
PARTITION BY o.order_id -- 1 classement par commande
ORDERBY s.started_at DESC, s.session_id DESC
) AS rang
FROM orders o
JOIN sessions s
ON s.user_id = o.user_id
AND s.started_at <= o.created_at -- ✅ uniquement les sessions antérieures
WHERE o.statut = 'livré'
)
SELECT canal, COUNT(*) AS nb_commandes, SUM(montant) AS ca
FROM sessions_avant_commande
WHERE rang =
GROUPBY canal
ORDERBY ca DESC;
-- Résultat : direct 100 € (le client est revenu en direct pour acheter)
sql-marketing-analytics · 03
Le contrôle à toujours faire : SUM(ca) de ton résultat doit être égal au CA total de la table orders sur la même période. Si c'est plus, tu as un fan-out. Si c'est moins, tu as perdu des commandes (client sans session, par exemple → utilise un LEFT JOIN et un canal 'inconnu').
Cas business — E-commerce : first touch vs last touch par canal
La directrice marketing veut comparer les deux modèles sur octobre pour arbitrer son budget.
WITH commandes_oct AS (
SELECT order_id, user_id, created_at, montant
FROM orders
WHERE statut = 'livré'
AND created_at >= '2026-10-01'AND created_at < '2026-11-01'
),
first_touch AS (
SELECT user_id, COALESCE(utm_source, 'direct') AS canal
FROM sessions
QUALIFY ROW_NUMBER() OVER (PARTITION BY user_id ORDERBY started_at, session_id) =
),
last_touch AS (
SELECT c.order_id, COALESCE(s.utm_source, 'direct') AS canal
FROM commandes_oct c
JOIN sessions s ON s.user_id = c.user_id AND s.started_at <= c.created_at
QUALIFY ROW_NUMBER() OVER (PARTITION BY c.order_id ORDERBY s.started_at DESC, s.session_id DESC) =
),
ca_first AS (
SELECTCOALESCE(ft.canal, 'inconnu') AS canal, SUM(c.montant) AS ca_first_touch
FROM commandes_oct c
LEFTJOIN first_touch ft ON ft.user_id = c.user_id
GROUPBY
),
ca_last AS (
SELECTCOALESCE(lt.canal, 'inconnu') AS canal, SUM(c.montant) AS ca_last_touch
FROM commandes_oct c
LEFTJOIN last_touch lt ON lt.order_id = c.order_id
GROUPBY
)
SELECT
COALESCE(f.canal, l.canal) AS canal,
COALESCE(f.ca_first_touch, ) AS ca_first_touch,
COALESCE(l.ca_last_touch, ) AS ca_last_touch,
COALESCE(l.ca_last_touch, ) - COALESCE(f.ca_first_touch, ) AS ecart
FROM ca_first f
FULLOUTERJOIN ca_last l ON l.canal = f.canal
ORDERBY ca_first_touch DESC;
-- canal | ca_first_touch | ca_last_touch | ecart
-- meta | 142 300 | 61 800 | -80 500 ← fait découvrir, mais convertit peu
-- google | 118 900 | 131 200 | +12 300
-- direct | 24 600 | 98 400 | +73 800 ← récolte le travail des autres canaux
-- email | 8 200 | 52 100 | +43 900
-- inconnu | 3 000 | 3 000 | 0
-- TOTAL | 347 000 | 346 500 | ← proche du CA réel (346 500 €) ✅
sql-marketing-analytics · 04
Lecture business : couper Meta parce que son last touch est faible serait une erreur. C'est lui qui alimente le haut du funnel. Ce genre de nuance, c'est ce qu'on attend d'un analyste marketing.
🧪 Entraîne-toi sur l'attribution multi-canal
Sur Modai Lab, tu travailles l'attribution sur de vrais parcours multi-sessions, avec un contrôle automatique : ton CA attribué doit retomber sur le CA réel.
Tous les taux sont entre 0 et 100 %, chaque étape est un sous-ensemble de la précédente, et un doublon de tracking ne change plus rien. C'est un funnel robuste.
Cas business — E-commerce : où fuit le funnel mobile ?
Le Head of Product soupçonne un problème sur le checkout mobile. On découpe le funnel par device.
WITH etapes_user AS (
SELECT
e.user_id,
s.device,
MIN(CASEWHEN e.event_name = 'add_to_cart'THEN e.event_at END) AS t_panier,
MIN(CASEWHEN e.event_name = 'begin_checkout'THEN e.event_at END) AS t_checkout,
MIN(CASEWHEN e.event_name = 'purchase'THEN e.event_at END) AS t_achat
FROM events e
JOIN sessions s ON s.session_id = e.session_id -- 1 event → 1 session : pas de fan-out
WHERE e.event_at >= '2026-10-01'AND e.event_at < '2026-11-01'
GROUPBY e.user_id, s.device
)
SELECT
device,
COUNTIF(t_panier ISNOTNULL) AS nb_panier,
COUNTIF(t_checkout >= t_panier) AS nb_checkout,
COUNTIF(t_achat >= t_checkout AND t_checkout >= t_panier) AS nb_achat,
ROUND(COUNTIF(t_checkout >= t_panier) * .
/ NULLIF(COUNTIF(t_panier ISNOTNULL), ), ) AS tx_panier_checkout,
ROUND(COUNTIF(t_achat >= t_checkout AND t_checkout >= t_panier) * .
/ NULLIF(COUNTIF(t_checkout >= t_panier), ), ) AS tx_checkout_achat
Le problème est précis : sur mobile, 27 points de moins entre checkout et achat. Ce n'est pas l'intérêt produit qui manque, c'est l'étape de paiement. Si le mobile convertissait comme le desktop, on gagnerait environ 780 commandes par mois. Une requête, une décision produit.
🧪 Vérifie que tes funnels tiennent la route
Taux supérieurs à 100 %, événements dupliqués, étapes dans le désordre : le test Modai Lab inclut des questions de funnel construites sur ces pièges.
ROAS (retour sur dépense publicitaire) = CA généré ÷ dépenses marketing
La difficulté : les dépenses et les revenus vivent dans deux tables de granularités différentes. ad_spend est à la maille jour × canal × campagne, orders à la maille commande. Les joindre directement, c'est la garantie d'un fan-out.
La bonne méthode : agréger chaque côté à la même maille, puis joindre.
❌ Ce que fait le débutant
-- Objectif : ROAS par canal sur octobre
SELECT
a.canal,
SUM(a.depense) AS depense, -- ❌ multipliée par le nb de commandes du jour
SUM(o.montant) AS ca, -- ❌ multiplié par le nb de campagnes du canal
SUM(o.montant) / SUM(a.depense) AS roas -- ❌ division par zéro si un canal n'a rien dépensé
AND created_at >= '2026-10-01'AND created_at < '2026-11-01'
GROUPBY canal_last_touch
)
SELECT
COALESCE(d.canal, r.canal) AS canal,
COALESCE(d.depense, ) AS depense,
COALESCE(r.ca, ) AS ca,
ROUND(r.ca / NULLIF(d.depense, ), ) AS roas -- ✅ NULL si pas de dépense (organique)
FROM depenses d
FULLOUTERJOIN revenus r ON r.canal = d.canal -- ✅ garde les canaux sans dépense ET sans vente
ORDERBY depense DESC;
-- canal | depense | ca | roas
-- google | 24 500 | 131 200 | 5.36
-- meta | 31 200 | 61 800 | 1.98
-- tiktok | 6 800 | 4 900 | 0.72 ← 🔴 sous l'eau
-- email | 0 | 52 100 | NULL ← pas de dépense média : ROAS non pertinent
-- direct | 0 | 98 400 | NULL
sql-marketing-analytics · 09
Le réflexe NULLIF(denominateur, 0) est indispensable dans toute requête marketing : un canal coupé en cours de mois, une campagne en pause, et ta requête plante ou renvoie l'infini.
Cas business — SaaS : CAC par canal et payback
Le CFO d'un SaaS B2B veut le CAC par canal d'acquisition (first touch) au T3 et le nombre de mois nécessaires pour le rembourser avec le MRR moyen d'un nouveau client.
WITH nouveaux_clients AS (
-- Un client = premier abonnement payant au T3
SELECT
s.user_id,
s.mrr,
COALESCE(ft.utm_source, 'direct') AS canal
FROM subscriptions s
LEFTJOIN (
SELECT user_id, utm_source
FROM sessions
QUALIFY ROW_NUMBER() OVER (PARTITION BY user_id ORDERBY started_at, session_id) =
) ft ON ft.user_id = s.user_id
WHERE s.mrr >
AND s.started_at >= '2026-07-01'AND s.started_at < '2026-10-01'
QUALIFY ROW_NUMBER() OVER (PARTITION BY s.user_id ORDERBY s.started_at) =
),
acquisition AS (
SELECT canal, COUNT(*) AS nb_clients, AVG(mrr) AS mrr_moyen
FROM nouveaux_clients
GROUPBY canal
),
depenses AS (
SELECT canal, SUM(depense) AS depense
FROM ad_spend
WHEREdate >= '2026-07-01'ANDdate < '2026-10-01'
GROUPBY canal
)
SELECT
a.canal,
a.nb_clients,
COALESCE(d.depense, ) AS depense,
ROUND(d.depense / NULLIF(a.nb_clients, ), ) AS cac,
Lecture business : LinkedIn a le CAC le plus élevé, mais amène les clients au MRR le plus fort. Son payback (4,5 mois) reste raisonnable. Comparer les CAC sans la valeur client aurait conduit à le couper.
🧪 Pratique la réconciliation coûts / revenus
Sur Modai Lab, les cas CAC et ROAS te font travailler exactement ce piège : deux tables, deux granularités, un seul chiffre juste.
_Données ad_spend réalistes · Pièges de granularité · Corrigés commentés_
PARTIE 4 — LTV et rétention par cohorte d'acquisition
Le mécanisme
Un ROAS calculé sur le premier achat ne dit pas tout. Un canal peut amener des clients qui achètent peu au départ mais reviennent souvent. Pour le voir, il faut la LTV cumulée par cohorte : combien un client rapporte en moyenne à J+30, J+90, J+180 après son acquisition, selon le canal qui l'a amené.
La construction :
Définir la cohorte : mois du premier achat + canal d'acquisition
Calculer pour chaque commande son ancienneté par rapport au premier achat
Cumuler le CA par tranche d'ancienneté et diviser par la taille de la cohorte (pas par le nombre de clients encore actifs)
❌ Ce que fait le débutant
SELECT
canal_acquisition,
AVG(montant) AS ltv -- ❌ moyenne par COMMANDE, pas par client
FROM orders o
JOIN users u ON u.user_id = o.user_id
GROUPBY canal_acquisition;
-- Problèmes :
-- 1. AVG(montant) = panier moyen, pas une LTV
-- 2. Aucune notion de temps : un client acquis hier et un client acquis il y a 2 ans
-- sont mélangés → les canaux récents sont mécaniquement pénalisés
-- 3. Les clients sans commande (si on part de users) disparaissent du dénominateur
sql-marketing-analytics · 11
✅ Ce qu'on attend d'un analyste
WITH premier_achat AS (
SELECT
o.user_id,
MIN(o.created_at) AS date_premier_achat
FROM orders o
WHERE o.statut = 'livré'
GROUPBY o.user_id
),
cohortes AS (
SELECT
p.user_id,
p.date_premier_achat,
DATE_TRUNC(p.date_premier_achat, MONTH) AS mois_cohorte,
COALESCE(u.canal_acquisition, 'inconnu') AS canal
FROM premier_achat p
JOIN users u ON u.user_id = p.user_id
),
ca_par_anciennete AS (
SELECT
c.mois_cohorte,
c.canal,
SUM(CASEWHEN DATE_DIFF(o.created_at, c.date_premier_achat, DAY) <= THEN o.montant ELSEEND) AS ca_j30,
SUM(CASEWHEN DATE_DIFF(o.created_at, c.date_premier_achat, DAY) <= THEN o.montant ELSEEND) AS ca_j90,
SUM(CASEWHEN DATE_DIFF(o.created_at, c.date_premier_achat, DAY) <= THEN o.montant ELSEEND) AS ca_j180
FROM cohortes c
JOIN orders o ON o.user_id = c.user_id AND o.statut = 'livré'
GROUPBY c.mois_cohorte, c.canal
),
taille AS (
SELECT mois_cohorte, canal, COUNT(*) AS nb_clients -- ✅ dénominateur = taille de cohorte figée
FROM cohortes
GROUPBY mois_cohorte, canal
)
SELECT
t.mois_cohorte,
t.canal,
t.nb_clients,
ROUND(ca.ca_j30 / t.nb_clients, ) AS ltv_j30,
ROUND(ca.ca_j90 / t.nb_clients, ) AS ltv_j90,
ROUND(ca.ca_j180 / t.nb_clients, ) AS ltv_j180
FROM taille t
JOIN ca_par_anciennete ca USING (mois_cohorte, canal)
WHERE t.mois_cohorte <= DATE_SUB(CURRENT_DATE, INTERVAL MONTH) -- ✅ seulement les cohortes assez "mûres" pour J+180
ORDERBY t.mois_cohorte, ltv_j180 DESC;
sql-marketing-analytics · 12
Le filtre final est essentiel : une cohorte de septembre n'a pas encore eu 180 jours pour acheter. L'inclure ferait croire que la LTV s'effondre sur les cohortes récentes.
Cas business — E-commerce : quel canal amène les meilleurs clients ?
Résultat de la requête ci-dessus pour la cohorte d'avril 2026 :
-- 2026-04-01 | tiktok | 640 | 52 | 58 | 61 ← achat unique, peu de retour
sql-marketing-analytics · 13
Croisé avec la partie 3 : TikTok a un ROAS de 0,72 au premier achat et une LTV qui plafonne. Le signal est clair. Meta, à l'inverse, a un ROAS moyen mais ses clients rapportent 50 % de plus à J+180 qu'à J+30 : le canal est plus rentable que ne le montre le ROAS immédiat.
🧪 Mesure ton niveau sur les analyses de cohorte
Fenêtres temporelles, dénominateurs figés, cohortes immatures : le test Modai Lab te dit si tu maîtrises les cohortes ou si tes LTV sont biaisées.
_Cohortes et LTV · Niveau situé · Plan de progression_
PARTIE 5 — A/B tests et campagnes email : la bonne unité de mesure
Le mécanisme
Analyser un A/B test en SQL, c'est d'abord ne pas se tromper d'unité. Le test randomise des utilisateurs : chaque mesure doit donc être calculée par utilisateur assigné, pas par clic, pas par session, pas par commande.
Trois erreurs reviennent en permanence :
Compter les événements au lieu des utilisateurs → un utilisateur très actif pèse 10 fois plus que les autres
Partir des convertis au lieu des assignés → les utilisateurs qui n'ont rien fait disparaissent du dénominateur
Compter les conversions d'avant l'assignation → tu mesures le passé, pas l'effet du test
Même logique pour les campagnes email : le dénominateur est le nombre d'emails délivrés, et une ouverture compte une fois par destinataire.
❌ Ce que fait le débutant
-- Objectif : taux de conversion par variante du test 'checkout_v2'
SELECT
a.variante,
COUNT(o.order_id) * . / COUNT(*) AS tx_conversion -- ❌ dénominateur = lignes après jointure
FROM orders o -- ❌ part des commandes : les non-acheteurs disparaissent
JOIN ab_assignments a ON a.user_id = o.user_id
WHERE a.test_id = 'checkout_v2'
GROUPBY a.variante;
-- Résultat : A = 100 %, B = 100 %. Évidemment : on ne regarde que des acheteurs.
-- Et un utilisateur qui a commandé 4 fois avant le test pèse 4 fois.
sql-marketing-analytics · 14
✅ Ce qu'on attend d'un analyste
WITH assignes AS (
-- Point de départ : TOUS les utilisateurs assignés, 1 ligne chacun
SELECT user_id, variante, assigned_at
FROM ab_assignments
WHERE test_id = 'checkout_v2'
),
resultats_user AS (
SELECT
a.user_id,
a.variante,
COUNT(o.order_id) AS nb_commandes,
COALESCE(SUM(o.montant), ) AS ca
FROM assignes a
LEFTJOIN orders o -- ✅ LEFT JOIN : les non-acheteurs restent (0)
ON o.user_id = a.user_id
AND o.statut = 'livré'
AND o.created_at >= a.assigned_at -- ✅ uniquement APRÈS l'assignation
AND o.created_at < TIMESTAMP_ADD(a.assigned_at, INTERVAL DAY) -- ✅ fenêtre identique pour tous
GROUPBY a.user_id, a.variante
)
SELECT
variante,
COUNT(*) AS nb_assignes,
COUNTIF(nb_commandes > ) AS nb_convertis,
ROUND(COUNTIF(nb_commandes > ) * . / COUNT(*), ) AS tx_conversion,
Bonus : vérifie toujours la répartition (12 480 vs 12 530, soit environ 50/50). Un déséquilibre fort (par exemple 60/40 sur un test configuré en 50/50) signale un bug d'assignation et invalide le test, quel que soit le résultat. La significativité statistique se calcule ensuite (en Python ou avec un outil dédié) à partir de ces agrégats.
Cas business — SaaS : performance d'une séquence email d'onboarding
L'équipe growth a lancé une séquence de 4 emails pour les nouveaux inscrits en essai gratuit. Elle veut savoir quel email contribue le plus à l'activation et à la conversion payante.
WITH envois AS (
SELECT
email_id,
user_id,
etape_sequence, -- 1, 2, 3, 4
sent_at,
delivered_at,
opened_at, -- première ouverture uniquement (NULL si jamais ouvert)
clicked_at
FROM email_sends
WHERE campagne = 'onboarding_trial'
AND sent_at >= '2026-09-01'AND sent_at < '2026-11-01'
),
conversion AS (
SELECT user_id, MIN(started_at) AS date_conversion
FROM subscriptions
WHERE mrr >
GROUPBY user_id
)
SELECT
e.etape_sequence,
COUNT(*) AS nb_envoyes,
COUNTIF(e.delivered_at ISNOTNULL) AS nb_delivres,
ROUND(COUNTIF(e.opened_at ISNOTNULL) * .
/ NULLIF(COUNTIF(e.delivered_at ISNOTNULL), ), ) AS tx_ouverture,
ROUND(COUNTIF(e.clicked_at ISNOTNULL) * .
/ NULLIF(COUNTIF(e.delivered_at ISNOTNULL), ), ) AS tx_clic,
ROUND(COUNTIF(c.date_conversion BETWEEN e.clicked_at
AND TIMESTAMP_ADD(e.clicked_at, INTERVAL DAY)) * .
/ NULLIF(COUNTIF(e.clicked_at ISNOTNULL), ), ) AS tx_conv_7j_apres_clic
Lecture business : l'email 1 est le plus ouvert, mais c'est l'email 3 qui convertit. Mesurer seulement les ouvertures aurait conduit à optimiser le mauvais email.
🧪 Pratique l'analyse d'expérimentations
Sur Modai Lab, les cas A/B tests et CRM te font choisir la bonne unité, le bon dénominateur et la bonne fenêtre, comme en vrai.
ROW_NUMBER() pour isoler 1 session, puis jointure 1-1
COUNT(*) sur les événements du funnel
Mesure l'activité, pas les personnes
1 ligne par user avec MIN(CASE WHEN …)
Étapes du funnel indépendantes
Taux > 100 %, ordre ignoré
Chaque étape exige la précédente et sa chronologie
Joint ad_spend et orders au grain fin
Dépenses et CA multipliés
Agréger chaque côté à la même maille, puis joindre
ca / depense sans protection
Division par zéro sur canal coupé
ca / NULLIF(depense, 0)
Part des acheteurs pour un A/B test
Dénominateur faux, conversion = 100 %
Part des assignés + LEFT JOIN + fenêtre post-assignation
Prompt Claude pour auditer tes requêtes
Copie-colle ce prompt dans Claude, puis ajoute ta requête, la question métier et le schéma de tes tables.
Tu es un Lead Marketing Analyst senior. Tu relis les requêtes SQL de ton équipe
avant que les chiffres partent au CMO. Je vais te donner une requête de
marketing analytics, la question métier qu'elle doit résoudre, et le schéma
des tables utilisées.
Ta mission : trouver tout ce qui pourrait rendre le chiffre faux.
1. GRANULARITÉ ET JOINTURES
- Indique la granularité de chaque table utilisée (1 ligne par quoi ?).
- Pour chaque jointure, dis si elle est 1-1, 1-N ou N-N.
- Signale tout risque de fan-out (CA, dépenses ou commandes dupliqués).
- Vérifie que les LEFT JOIN ne sont pas cassés par un filtre dans le WHERE.
2. DÉFINITION DES MÉTRIQUES
- Attribution : le modèle (first / last touch) est-il explicite et appliqué
avec une seule session par client ou par commande ?
- Funnel : l'unité est-elle l'utilisateur ? Chaque étape exige-t-elle la
précédente et respecte-t-elle l'ordre chronologique ?
- CAC / ROAS : dépenses et revenus sont-ils agrégés à la même maille avant
d'être joints ? Les divisions sont-elles protégées par NULLIF ?
- LTV / cohortes : le dénominateur est-il la taille de cohorte figée ?
Les cohortes trop récentes sont-elles exclues ?
- A/B test : la base est-elle l'ensemble des assignés ? Les conversions
sont-elles limitées à une fenêtre après l'assignation ?
3. FILTRES ET PÉRIODES
- Les bornes de dates sont-elles correctes (BETWEEN sur un timestamp,
borne de fin exclue) ?
- Les statuts (annulé, remboursé, test) sont-ils filtrés de façon cohérente ?
- Les sessions sans UTM sont-elles traitées explicitement (direct / inconnu) ?
4. CONTRÔLE DE RÉCONCILIATION
- Propose une requête de contrôle qui vérifie que le total de mon résultat
retombe sur un chiffre de référence (CA total, nb de commandes, dépense
totale de la plateforme pub).
5. VERSION CORRIGÉE
- Réécris la requête en CTE nommées, avec des commentaires SQL qui
expliquent chaque correction.
- Pour chaque erreur trouvée, montre un mini-exemple de 3-4 lignes de données
où ma version donnait un résultat faux, et l'impact business de l'erreur.
Sois direct et précis. Classe les problèmes du plus grave (chiffre faux) au
moins grave (lisibilité).
Question métier :
[colle ici la question]
Schéma des tables :
[colle ici les tables, colonnes et granularités]
Ma requête :
[colle ici ta requête]
sql-marketing-analytics · 17
📋 Récapitulatif : les requêtes du marketing analytics
Analyse
Question métier
Technique SQL clé
Piège n°1
Contrôle
Attribution first touch
Quel canal fait découvrir la marque ?
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY started_at)
Jointure à toutes les sessions
Σ CA attribué = CA réel
Attribution last touch
Quel canal déclenche l'achat ?
ROW_NUMBER() par commande + started_at <= created_at
Sessions postérieures à l'achat
Σ CA attribué = CA réel
Funnel
Où perd-on les utilisateurs ?
MIN(CASE WHEN …) par user + conditions chaînées
Compter des événements
Taux entre 0 et 100 %
ROAS
Combien rapporte 1 € investi ?
Agréger puis FULL OUTER JOIN + NULLIF
Fan-out dépenses × commandes
Σ dépense = facture plateforme
CAC / payback
Combien coûte un client, en combien de temps est-il rentable ?
Premier abonnement via QUALIFY ROW_NUMBER()
Compter les réabonnements
Nb clients = nouveaux clients CRM
LTV par cohorte
Quel canal amène les meilleurs clients ?
DATE_DIFF + SUM(CASE WHEN …) / taille de cohorte
Cohortes immatures incluses
LTV croissante dans le temps
A/B test
La variante B fait-elle mieux ?
Base = assignés + LEFT JOIN + fenêtre
Partir des convertis
Répartition proche du split prévu
Email
Quel email convertit vraiment ?
Taux / délivrés + conversion post-clic
Optimiser l'ouverture seule
Délivrés ≤ envoyés
✅ Checklist marketing analytics
☐ J'ai écrit la granularité de chaque table avant de joindre
☐ Mon modèle d'attribution (first / last touch) est explicite et documenté
☐ J'isole une seule session par client ou par commande avec ROW_NUMBER()
☐ Le CA attribué total retombe exactement sur le CA réel de la période
☐ Les sessions sans UTM sont classées explicitement (direct / inconnu)
☐ Mon funnel compte des utilisateurs, pas des événements
☐ Chaque étape du funnel exige l'étape précédente et respecte la chronologie
☐ Aucun taux de mon funnel ne dépasse 100 %
☐ J'agrège dépenses et revenus à la même maille avant de les joindre
☐ Toutes mes divisions sont protégées par NULLIF(…, 0)
☐ Ma dépense totale correspond à la facture des plateformes publicitaires
☐ Mes calculs de LTV divisent par la taille de cohorte figée
☐ J'exclus les cohortes trop récentes pour la fenêtre mesurée
☐ Mes A/B tests partent de l'ensemble des utilisateurs assignés
☐ Je ne compte que les conversions postérieures à l'assignation, dans une fenêtre fixe
☐ J'ai vérifié que la répartition A/B correspond au split prévu
☐ Mes taux email sont calculés sur les emails délivrés
☐ Mes bornes de dates excluent la borne de fin (< '2026-11-01')
☐ Les commandes annulées, remboursées ou de test sont filtrées partout de la même façon
☐ J'ai une requête de contrôle pour chaque chiffre envoyé au CMO
🚀 Deviens l'analyste à qui le marketing fait confiance
En marketing analytics, la valeur ne vient pas de requêtes compliquées. Elle vient de chiffres justes, qu'on sait défendre en réunion. Le plus court chemin pour y arriver, c'est de pratiquer l'attribution, les funnels et les cohortes sur des données réalistes, avec un feedback qui te montre où ton chiffre dérape.
_Guide offert suite au post LinkedIn · Entraînez-vous sur modai-lab.com/test-sql/ · Partagez librement ♻️_
ModAi Lab
La théorie c’est bien. La pratique sur des cas concrets, c’est mieux.
Exerce-toi sur les cas SQL qu’on te posera en entretien, avec un coach IA qui review chaque requête comme un senior. Essai gratuit, sans carte bancaire.