Requêtes par cohortes et entonnoirs
Répondez aux questions sur la rétention.
Requêtes par cohortes et entonnoirs est une leçon Digital Marketing Academy gratuite sur CoddyKit. Ceci est la leçon 4 sur 4. Tu peux lire la leçon complète ci-dessous gratuitement — puis la pratiquer en direct dans le navigateur avec un éditeur de code intégré et un tuteur IA 24/7. Elle fait partie du parcours d'apprentissage Digital Marketing Academy, et ta progression se synchronise sur le web et l'application CoddyKit. Le cours Digital Marketing Academy comprend 4 leçons au total.
Qu’est-ce qu’une cohorte ?
Une cohorte est un groupe d’utilisateurs partageant un événement de départ, généralement leur mois d’inscription. Suivre chaque cohorte au fil du temps révèle la véritable rétention.
Les métriques agrégées masquent l’attrition ; les cohortes montrent si les nouveaux utilisateurs restent réellement actifs.
SELECT user_id,
DATE_TRUNC('month', signup_date) AS cohort_month
FROM users;Définir la cohorte
La première étape consiste à attribuer une cohorte à chaque utilisateur. DATE_TRUNC ramène les dates d’inscription au début du mois afin de regrouper correctement les utilisateurs.
Conservez ce résultat comme une brique réutilisable pour tout ce qui suit.
SELECT DATE_TRUNC('month', signup_date) AS cohort_month,
COUNT(*) AS cohort_size
FROM users
GROUP BY 1
ORDER BY 1;Périodes d’activité
La rétention mesure l’activité au cours des mois suivant l’inscription. Calculez la période comme l’écart entre le mois d’une commande et le mois de la cohorte.
La période 0 correspond au mois d’inscription, la période 1 au mois suivant, et ainsi de suite.
SELECT o.user_id,
(DATE_PART('year', o.order_date) - DATE_PART('year', u.signup_date)) * 12
+ (DATE_PART('month', o.order_date) - DATE_PART('month', u.signup_date)) AS period
FROM orders o
JOIN users u ON u.user_id = o.user_id;Construire une grille de rétention
Combinez le mois de la cohorte et la période, puis comptez les utilisateurs actifs distincts dans chaque cellule. Vous obtenez le triangle classique de rétention.
Chaque ligne représente une cohorte ; chaque colonne représente le nombre de mois écoulés depuis l’inscription.
SELECT DATE_TRUNC('month', u.signup_date) AS cohort,
DATE_PART('month', AGE(o.order_date, u.signup_date)) AS period,
COUNT(DISTINCT o.user_id) AS active
FROM users u
JOIN orders o ON o.user_id = u.user_id
GROUP BY 1, 2;Taux de rétention
Les nombres bruts sont difficiles à comparer entre des cohortes de tailles différentes. Divisez le nombre d’utilisateurs actifs de chaque période par la taille initiale de la cohorte.
Une fonction de fenêtre reporte la taille de la période 0 sur chaque ligne pour calculer le ratio.
SELECT cohort, period,
active * 1.0 / FIRST_VALUE(active) OVER (
PARTITION BY cohort ORDER BY period
) AS retention
FROM cohort_activity;Qu’est-ce qu’un entonnoir ?
Un entonnoir suit les utilisateurs à travers des étapes ordonnées : visite, inscription, ajout au panier, achat. La baisse entre les étapes montre où vous perdez des utilisateurs.
L’analyse de l’entonnoir transforme de vagues plaintes concernant une « faible conversion » en un diagnostic précis de l’étape où les utilisateurs abandonnent.
SELECT step, COUNT(DISTINCT user_id) AS users
FROM events
WHERE step IN ('visit', 'signup', 'cart', 'purchase')
GROUP BY step;Compter chaque étape
L’agrégation conditionnelle compte les utilisateurs à chaque étape en un seul parcours. FILTER dénombre les utilisateurs distincts ayant atteint chaque étape.
Une requête, une ligne, l’ensemble de l’entonnoir en un coup d’œil.
SELECT
COUNT(DISTINCT user_id) FILTER (WHERE step = 'visit') AS visits,
COUNT(DISTINCT user_id) FILTER (WHERE step = 'signup') AS signups,
COUNT(DISTINCT user_id) FILTER (WHERE step = 'purchase') AS buyers
FROM events;Taux de conversion par étape
Le nombre intéressant est le ratio entre deux étapes consécutives. Diviser chaque étape par la précédente révèle la plus forte baisse.
Convertissez le résultat en nombre décimal afin que la division entière ne réduise pas votre taux à zéro.
SELECT signups * 1.0 / NULLIF(visits, 0) AS visit_to_signup,
buyers * 1.0 / NULLIF(signups, 0) AS signup_to_buy
FROM funnel_counts;Entonnoirs ordonnés avec horodatages
Un entonnoir strict exige que les étapes se produisent dans l’ordre. Associez chaque étape à la suivante uniquement lorsque son horodatage est ultérieur.
Cela empêche de compter un achat effectué avant l’inscription correspondante.
SELECT COUNT(DISTINCT v.user_id) AS visited,
COUNT(DISTINCT p.user_id) AS purchased
FROM events v
LEFT JOIN events p
ON p.user_id = v.user_id
AND p.step = 'purchase'
AND p.event_time > v.event_time
WHERE v.step = 'visit';Entonnoir par canal
Répartir l’entonnoir par canal d’acquisition montre quelles sources envoient des utilisateurs qui convertissent réellement, et pas seulement des clics.
Regroupez les dénombrements conditionnels par canal pour comparer la qualité des différentes sources.
SELECT channel,
COUNT(DISTINCT user_id) FILTER (WHERE step = 'visit') AS visits,
COUNT(DISTINCT user_id) FILTER (WHERE step = 'purchase') AS buyers
FROM events
GROUP BY channel;De l’analyse à l’action
Les cohortes vous indiquent si la rétention s’améliore d’une version à l’autre ; les entonnoirs vous indiquent quelle étape corriger en premier.
Ensemble, ils font passer le marketing du simple compte rendu de ce qui s’est produit au diagnostic des causes et des prochains investissements à réaliser.
Vérification rapide
Votre entonnoir compte les utilisateurs lors de la visite, de l’inscription et de l’achat. Que révèlent les ratios entre étapes consécutives ?
Récapitulatif
Les cohortes regroupent les utilisateurs par mois de départ et suivent la rétention au fil des périodes à l’aide de DATE_TRUNC et AGE. Les entonnoirs comptent les utilisateurs distincts à chaque étape ordonnée et comparent les ratios consécutifs.
Vous disposez désormais des outils avancés nécessaires pour diagnostiquer entièrement la rétention et la conversion en SQL.
Questions Fréquemment Posées
La leçon « Requêtes par cohortes et entonnoirs » est-elle gratuite ?
Oui — le texte complet de « Requêtes par cohortes et entonnoirs » est gratuit à lire ici sur le web. Pour la pratiquer de manière interactive (un éditeur de code intégré et un tuteur IA 24/7) et déverrouiller le reste du cours Digital Marketing Academy, passe à CoddyKit PRO. Le cours Digital Marketing Academy comprend 4 leçons au total.
Qu'est-ce que j'apprendrai dans « Requêtes par cohortes et entonnoirs » ?
Répondez aux questions sur la rétention. Tu pratiques Digital Marketing Academy avec du code pratique que tu exécutes directement dans le navigateur, et un tuteur IA 24/7 répond à tes questions au fur et à mesure que tu avances dans la leçon.
Dois-je avoir de l'expérience pour commencer Digital Marketing Academy ?
Aucune expérience préalable n'est requise. Digital Marketing Academy sur CoddyKit est structuré pour les débutants jusqu'aux apprenants avancés, donc tu peux commencer ici ou depuis le début et avancer à ton rythme. Ceci est la leçon 4 sur 4.
Combien de temps prend la leçon « Requêtes par cohortes et entonnoirs » ?
La plupart des leçons CoddyKit prennent environ 5–10 minutes. Chacune est courte et interactive, tu progresses régulièrement et tu repiques exactement où tu t'es arrêté sur le web et l'app.
Peux-tu écrire et exécuter du code dans cette leçon Digital Marketing Academy ?
Oui. Chaque leçon Digital Marketing Academy inclut un éditeur de code intégré, tu écris et exécutes du vrai code directement dans ton navigateur et tu reçois des retours IA instantanés — aucune configuration locale requise.
Toutes les leçons de ce cours
- Pourquoi les spécialistes du marketing apprennent le SQL
- SELECT, WHERE, GROUP BY
- Relier des tables marketing
- Requêtes par cohortes et entonnoirs