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.

Fais le test en 5 minutes.

👉 Faire le test sur modai-lab.com/test-sql/

_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).

Table

Granularité

Colonnes clés

users

1 ligne par utilisateur

user_id, created_at, pays, canal_acquisition

sessions

1 ligne par visite

session_id, user_id, started_at, device, utm_source, utm_medium, utm_campaign

events

1 ligne par événement

event_id, user_id, session_id, event_name, event_at

orders

1 ligne par commande

order_id, user_id, created_at, montant, statut, canal_last_touch

subscriptions

1 ligne par abonnement (SaaS)

user_id, started_at, mrr, statut

ad_spend

1 ligne par jour × canal × campagne

date, canal, campagne, depense, impressions, clics

ab_assignments

1 ligne par utilisateur × test

user_id, test_id, variante, assigned_at

email_sends

1 ligne par email envoyé

email_id, user_id, campagne, etape_sequence, sent_at, delivered_at, opened_at, clicked_at

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
GROUP BY s.utm_source; -- dupliquée autant de fois que le client a de sessions
 
-- Données d'exemple :
-- sessions orders
-- session_id | user_id | started_at | utm order_id | user_id | montant
-- S1 | U1 | 01/10 | meta O1 | U1 | 100
-- S2 | U1 | 05/10 | google
-- S3 | U1 | 08/10 | direct
--
-- 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
ORDER BY 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é'
GROUP BY ps.canal_acquisition
ORDER BY 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
ORDER BY 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 =
GROUP BY canal
ORDER BY 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 ORDER BY 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 ORDER BY s.started_at DESC, s.session_id DESC) =
),
 
ca_first AS (
SELECT COALESCE(ft.canal, 'inconnu') AS canal, SUM(c.montant) AS ca_first_touch
FROM commandes_oct c
LEFT JOIN first_touch ft ON ft.user_id = c.user_id
GROUP BY
),
 
ca_last AS (
SELECT COALESCE(lt.canal, 'inconnu') AS canal, SUM(c.montant) AS ca_last_touch
FROM commandes_oct c
LEFT JOIN last_touch lt ON lt.order_id = c.order_id
GROUP BY
)
 
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
FULL OUTER JOIN ca_last l ON l.canal = f.canal
ORDER BY 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.

👉 Pratiquer sur modai-lab.com

_Parcours clients réalistes · Contrôle de réconciliation · Feedback immédiat_


PARTIE 2 — Le funnel de conversion sans taux impossibles

Le mécanisme

Un funnel mesure combien d'utilisateurs passent d'une étape à la suivante : visite produit → ajout panier → début checkout → achat.

Trois règles de construction :

  1. L'unité est l'utilisateur (ou la session), jamais l'événement. Un utilisateur qui ajoute 5 produits au panier ne compte qu'une fois.

  2. Un utilisateur ne compte à l'étape N que s'il a franchi l'étape N-1. Sinon tu obtiens des taux supérieurs à 100 %.

  3. L'ordre chronologique compte : un achat qui précède la vue produit n'est pas une conversion du funnel.

❌ Ce que fait le débutant

SELECT
event_name,
COUNT(*) AS nb -- ❌ compte les événements, pas les personnes
FROM events
WHERE event_name IN ('view_product', 'add_to_cart', 'begin_checkout', 'purchase')
AND event_at >= '2026-10-01' AND event_at < '2026-11-01'
GROUP BY event_name;
 
-- event_name | nb
-- view_product | 84 200
-- add_to_cart | 21 400 ← un même user ajoute 3,2 produits en moyenne
-- begin_checkout | 6 100
-- purchase | 4 300
--
-- Taux "panier → checkout" = 28 %… mais en utilisateurs uniques c'est 71 %.
-- Et si l'appli renvoie l'événement begin_checkout deux fois (bug de tracking),
-- le taux checkout → achat passe sous 50 % alors que rien n'a changé côté business.
sql-marketing-analytics · 05

✅ Ce qu'on attend d'un analyste

WITH etapes_user AS (
-- 1 ligne par utilisateur, avec la PREMIÈRE date de chaque étape
SELECT
user_id,
MIN(CASE WHEN event_name = 'view_product' THEN event_at END) AS t_vue,
MIN(CASE WHEN event_name = 'add_to_cart' THEN event_at END) AS t_panier,
MIN(CASE WHEN event_name = 'begin_checkout' THEN event_at END) AS t_checkout,
MIN(CASE WHEN event_name = 'purchase' THEN event_at END) AS t_achat
FROM events
WHERE event_at >= '2026-10-01' AND event_at < '2026-11-01'
GROUP BY user_id
),
 
funnel AS (
-- ✅ chaque étape exige la précédente ET l'ordre chronologique
SELECT
user_id,
t_vue IS NOT NULL AS e1_vue,
t_vue IS NOT NULL AND t_panier >= t_vue AS e2_panier,
t_vue IS NOT NULL AND t_panier >= t_vue
AND t_checkout >= t_panier AS e3_checkout,
t_vue IS NOT NULL AND t_panier >= t_vue
AND t_checkout >= t_panier
AND t_achat >= t_checkout AS e4_achat
FROM etapes_user
)
 
SELECT
COUNTIF(e1_vue) AS nb_vue,
COUNTIF(e2_panier) AS nb_panier,
COUNTIF(e3_checkout) AS nb_checkout,
COUNTIF(e4_achat) AS nb_achat,
ROUND(COUNTIF(e2_panier) * . / NULLIF(COUNTIF(e1_vue), ), ) AS tx_vue_panier,
ROUND(COUNTIF(e3_checkout) * . / NULLIF(COUNTIF(e2_panier), ), ) AS tx_panier_checkout,
ROUND(COUNTIF(e4_achat) * . / NULLIF(COUNTIF(e3_checkout), ), ) AS tx_checkout_achat,
ROUND(COUNTIF(e4_achat) * . / NULLIF(COUNTIF(e1_vue), ), ) AS tx_global
FROM funnel;
 
-- nb_vue | nb_panier | nb_checkout | nb_achat | tx_vue_panier | tx_panier_checkout | tx_checkout_achat | tx_global
-- 31 800 | 6 690 | 4 750 | 3 520 | 21.0 | 71.0 | 74.1 | 11.07
sql-marketing-analytics · 06

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(CASE WHEN e.event_name = 'add_to_cart' THEN e.event_at END) AS t_panier,
MIN(CASE WHEN e.event_name = 'begin_checkout' THEN e.event_at END) AS t_checkout,
MIN(CASE WHEN 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'
GROUP BY e.user_id, s.device
)
SELECT
device,
COUNTIF(t_panier IS NOT NULL) 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 IS NOT NULL), ), ) 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
FROM etapes_user
GROUP BY device
ORDER BY nb_panier DESC;
 
-- device | nb_panier | nb_checkout | nb_achat | tx_panier_checkout | tx_checkout_achat
-- mobile | 4 120 | 2 840 | 1 790 | 68.9 | 63.0 ← 🔴 décrochage
-- desktop | 2 310 | 1 760 | 1 590 | 76.2 | 90.3
-- tablet | 260 | 150 | 140 | 57.7 | 93.3
sql-marketing-analytics · 07

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.

👉 Faire le test sur modai-lab.com/test-sql/

_Pièges de tracking réels · Score détaillé · Points faibles identifiés_


PARTIE 3 — CAC et ROAS par canal : réconcilier dépenses et revenus

Le mécanisme

Les deux KPI que tout CMO regarde chaque lundi :

  • CAC (coût d'acquisition client) = dépenses marketing ÷ nouveaux clients acquis

  • 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é
FROM ad_spend a
JOIN orders o
ON DATE(o.created_at) = a.date
AND o.canal_last_touch = a.canal
WHERE a.date BETWEEN '2026-10-01' AND '2026-10-31'
GROUP BY a.canal;
 
-- Données d'exemple pour google le 12/10 :
-- ad_spend : 2 campagnes (brand 300 €, generic 700 €)
-- orders : 40 commandes, 2 400 € de CA
--
-- Après jointure : 2 × 40 = 80 lignes
-- depense = 1 000 € × 40 = 40 000 € ❌
-- ca = 2 400 € × 2 = 4 800 € ❌
sql-marketing-analytics · 08

✅ Ce qu'on attend d'un analyste

WITH depenses AS (
-- Côté coûts : agrégé à la maille canal
SELECT canal, SUM(depense) AS depense
FROM ad_spend
WHERE date >= '2026-10-01' AND date < '2026-11-01'
GROUP BY canal
),
 
revenus AS (
-- Côté revenus : agrégé à la MÊME maille
SELECT
canal_last_touch AS canal,
COUNT(*) AS nb_commandes,
SUM(montant) AS ca
FROM orders
WHERE statut = 'livré'
AND created_at >= '2026-10-01' AND created_at < '2026-11-01'
GROUP BY 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
FULL OUTER JOIN revenus r ON r.canal = d.canal -- ✅ garde les canaux sans dépense ET sans vente
ORDER BY 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
LEFT JOIN (
SELECT user_id, utm_source
FROM sessions
QUALIFY ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY 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 ORDER BY s.started_at) =
),
 
acquisition AS (
SELECT canal, COUNT(*) AS nb_clients, AVG(mrr) AS mrr_moyen
FROM nouveaux_clients
GROUP BY canal
),
 
depenses AS (
SELECT canal, SUM(depense) AS depense
FROM ad_spend
WHERE date >= '2026-07-01' AND date < '2026-10-01'
GROUP BY canal
)
 
SELECT
a.canal,
a.nb_clients,
COALESCE(d.depense, ) AS depense,
ROUND(d.depense / NULLIF(a.nb_clients, ), ) AS cac,
ROUND(a.mrr_moyen, ) AS mrr_moyen,
ROUND(d.depense / NULLIF(a.nb_clients, )
/ NULLIF(a.mrr_moyen, ), ) AS payback_mois
FROM acquisition a
LEFT JOIN depenses d ON d.canal = a.canal
ORDER BY cac DESC;
 
-- canal | nb_clients | depense | cac | mrr_moyen | payback_mois
-- linkedin | 42 | 58 800 | 1 400 | 310 | 4.5
-- google | 118 | 70 800 | 600 | 190 | 3.2
-- meta | 64 | 22 400 | 350 | 95 | 3.7
-- organique | 203 | 0 | NULL | 160 | NULL
sql-marketing-analytics · 10

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.

👉 Pratiquer sur modai-lab.com

_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 :

  1. Définir la cohorte : mois du premier achat + canal d'acquisition

  2. Calculer pour chaque commande son ancienneté par rapport au premier achat

  3. 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
GROUP BY 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é'
GROUP BY 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(CASE WHEN DATE_DIFF(o.created_at, c.date_premier_achat, DAY) <= THEN o.montant ELSE END) AS ca_j30,
SUM(CASE WHEN DATE_DIFF(o.created_at, c.date_premier_achat, DAY) <= THEN o.montant ELSE END) AS ca_j90,
SUM(CASE WHEN DATE_DIFF(o.created_at, c.date_premier_achat, DAY) <= THEN o.montant ELSE END) AS ca_j180
FROM cohortes c
JOIN orders o ON o.user_id = c.user_id AND o.statut = 'livré'
GROUP BY c.mois_cohorte, c.canal
),
 
taille AS (
SELECT mois_cohorte, canal, COUNT(*) AS nb_clients -- ✅ dénominateur = taille de cohorte figée
FROM cohortes
GROUP BY 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
ORDER BY 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 :

-- mois_cohorte | canal | nb_clients | ltv_j30 | ltv_j90 | ltv_j180
-- 2026-04-01 | email | 410 | 74 | 131 | 188 ← fidélisation forte
-- 2026-04-01 | google | 1 280 | 68 | 102 | 139
-- 2026-04-01 | meta | 1 950 | 61 | 79 | 92
-- 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.

👉 Faire le test sur modai-lab.com/test-sql/

_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 :

  1. Compter les événements au lieu des utilisateurs → un utilisateur très actif pèse 10 fois plus que les autres

  2. Partir des convertis au lieu des assignés → les utilisateurs qui n'ont rien fait disparaissent du dénominateur

  3. 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'
GROUP BY 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
LEFT JOIN 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
GROUP BY 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,
ROUND(AVG(ca), ) AS ca_par_assigne
FROM resultats_user
GROUP BY variante
ORDER BY variante;
 
-- variante | nb_assignes | nb_convertis | tx_conversion | ca_par_assigne
-- A | 12 480 | 524 | 4.20 | 3.15
-- B | 12 530 | 589 | 4.70 | 3.52
sql-marketing-analytics · 15

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 >
GROUP BY user_id
)
 
SELECT
e.etape_sequence,
COUNT(*) AS nb_envoyes,
COUNTIF(e.delivered_at IS NOT NULL) AS nb_delivres,
ROUND(COUNTIF(e.opened_at IS NOT NULL) * .
/ NULLIF(COUNTIF(e.delivered_at IS NOT NULL), ), ) AS tx_ouverture,
ROUND(COUNTIF(e.clicked_at IS NOT NULL) * .
/ NULLIF(COUNTIF(e.delivered_at IS NOT NULL), ), ) 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 IS NOT NULL), ), ) AS tx_conv_7j_apres_clic
FROM envois e
LEFT JOIN conversion c ON c.user_id = e.user_id
GROUP BY e.etape_sequence
ORDER BY e.etape_sequence;
 
-- etape | nb_envoyes | nb_delivres | tx_ouverture | tx_clic | tx_conv_7j_apres_clic
-- 1 | 8 420 | 8 260 | 58.4 | 14.2 | 6.1
-- 2 | 7 980 | 7 830 | 44.1 | 9.8 | 8.4
-- 3 | 7 410 | 7 270 | 36.7 | 11.6 | 17.9 ← email "cas d'usage" : le plus convertissant
-- 4 | 6 900 | 6 760 | 31.2 | 4.3 | 5.2 ← à retravailler
sql-marketing-analytics · 16

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.

👉 Pratiquer sur modai-lab.com

_Datasets d'A/B tests réalistes · Pièges d'unité de mesure · Feedback senior_


Les 6 erreurs classiques en SQL marketing

❌ Ce que fait le junior

Pourquoi c'est faux

✅ Ce que fait le senior

Joint orders à toutes les sessions

Fan-out : CA dupliqué par session

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.

👉 modai-lab.com/test-sql/ — Exercices calibrés · Cas business réels · Feedback senior


_Guide offert suite au post LinkedIn · Entraînez-vous sur modai-lab.com/test-sql/ · Partagez librement ♻️_