Cohort- en funnelquery's
Beantwoord vragen over retentie.
Cohort- en funnelquery's is een gratis Digital Marketing Academy-les op CoddyKit. Dit is les 4 van 4. Je kunt 3 lessen uit dit leerpad gratis volledig lezen — daarna ontgrendelt CoddyKit PRO alle lessen, plus praktische oefeningen met een ingebouwde code-editor en een AI-tutor die 24/7 beschikbaar is. Deze les maakt deel uit van het leertraject Digital Marketing Academy. Je voortgang wordt gesynchroniseerd op het web en in de CoddyKit-app. De cursus Digital Marketing Academy bevat in totaal 4 lessen.
Wat is een cohort?
Een cohort is een groep gebruikers met dezelfde startgebeurtenis, meestal de maand waarin ze zich hebben aangemeld. Als je elk cohort in de loop van de tijd volgt, krijg je inzicht in de werkelijke retentie.
Geaggregeerde statistieken verbergen churn; cohorten laten zien of nieuwe gebruikers echt actief blijven.
SELECT user_id,
DATE_TRUNC('month', signup_date) AS cohort_month
FROM users;Het cohort definiëren
De eerste stap is elke gebruiker aan een cohort toewijzen. DATE_TRUNC rondt aanmeldingsdatums af op maandniveau, zodat gebruikers netjes kunnen worden gegroepeerd.
Sla dit op als een herbruikbaar bouwblok voor alles wat volgt.
SELECT DATE_TRUNC('month', signup_date) AS cohort_month,
COUNT(*) AS cohort_size
FROM users
GROUP BY 1
ORDER BY 1;Activiteitsperioden
Retentie meet activiteit in de maanden na aanmelding. Bereken de periode als het verschil tussen de maand van een bestelling en de cohortmaand.
Periode 0 is de maand van aanmelding, periode 1 is de volgende maand, enzovoort.
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;Een retentierooster opbouwen
Combineer de cohortmaand en de periode en tel vervolgens het aantal unieke actieve gebruikers per vak. Het resultaat is de klassieke retentiedriehoek.
Elke rij is een cohort; elke kolom staat voor het aantal maanden sinds aanmelding.
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;Retentiepercentage
Ruwe aantallen zijn lastig te vergelijken tussen cohorten van verschillende grootte. Deel het aantal actieve gebruikers in elke periode door de oorspronkelijke omvang van het cohort.
Met een windowfunctie haal je de omvang van periode 0 naar elke rij voor deze verhouding.
SELECT cohort, period,
active * 1.0 / FIRST_VALUE(active) OVER (
PARTITION BY cohort ORDER BY period
) AS retention
FROM cohort_activity;Wat is een funnel?
Een funnel volgt gebruikers door opeenvolgende stappen: bezoek, aanmelden, toevoegen aan de winkelwagen en aankoop. De daling tussen stappen laat zien waar je mensen verliest.
Funnelanalyse maakt vage klachten over "lage conversie" concreet door de specifieke stap met uitval zichtbaar te maken.
SELECT step, COUNT(DISTINCT user_id) AS users
FROM events
WHERE step IN ('visit', 'signup', 'cart', 'purchase')
GROUP BY step;Elke stap tellen
Met voorwaardelijke aggregatie tel je gebruikers in elke fase in één doorloop. FILTER telt de unieke gebruikers die elke stap hebben bereikt.
Eén query, één rij en de hele funnel in één oogopslag.
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;Conversiepercentages per stap
Het interessantste getal is de verhouding tussen opeenvolgende stappen. Door elke stap te delen door de vorige, zie je waar de grootste uitval plaatsvindt.
Zet de waarde om naar een decimaaltype, zodat gehele getaldeling je percentage niet naar nul afrondt.
SELECT signups * 1.0 / NULLIF(visits, 0) AS visit_to_signup,
buyers * 1.0 / NULLIF(signups, 0) AS signup_to_buy
FROM funnel_counts;Funnels met tijdstempels in de juiste volgorde
Voor een strikte funnel moeten stappen in de juiste volgorde plaatsvinden. Koppel elke stap alleen aan de volgende als de tijdstempel later is.
Zo voorkom je dat een aankoop wordt meegeteld die vóór de bijbehorende aanmelding plaatsvond.
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';Funnel per kanaal
Als je de funnel opsplitst per acquisitiekanaal, zie je welke bronnen gebruikers opleveren die daadwerkelijk converteren en niet alleen klikken.
Groepeer de voorwaardelijke tellingen per kanaal om de kwaliteit van verschillende bronnen te vergelijken.
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;Van inzicht naar actie
Cohorten laten zien of retentie per release verbetert; funnels laten zien welke stap je als eerste moet aanpakken.
Samen verschuiven ze marketing van rapporteren wat er is gebeurd naar vaststellen waarom het gebeurde en waar je vervolgens moet investeren.
Korte controle
Je funnel telt gebruikers bij een bezoek, aanmelding en aankoop. Wat laten verhoudingen tussen opeenvolgende stappen zien?
Samenvatting
Cohorten groeperen gebruikers op basis van hun startmaand en volgen retentie over perioden met DATE_TRUNC en AGE. Funnels tellen unieke gebruikers per stap in de juiste volgorde en vergelijken verhoudingen tussen opeenvolgende stappen.
Je hebt nu de geavanceerde gereedschappen om retentie en conversie volledig in SQL te analyseren.
Leer Digital Marketing Academy met een AI-tutor — gratis
Schrijf echte code en voer die uit in je browser, krijg direct hulp van een AI-tutor die 24/7 beschikbaar is en ga verder waar je gebleven bent op het web of in de app.
- Cursussen
- 63
- Lessen
- 239
Veelgestelde vragen
Is de les “Cohort- en funnelquery's” gratis?
Ja — je kunt hier op het web alle 3 lessen van het leerpad Digital Marketing Academy, waaronder “Cohort- en funnelquery's”, gratis volledig lezen. Daarna ontgrendelt CoddyKit PRO alle lessen, plus interactieve oefeningen met een ingebouwde code-editor en een AI-tutor die 24/7 beschikbaar is. De cursus Digital Marketing Academy bevat in totaal 4 lessen.
Wat leer ik in “Cohort- en funnelquery's”?
Beantwoord vragen over retentie. Je oefent met Digital Marketing Academy door code rechtstreeks in de browser uit te voeren. Een AI-begeleider die 24/7 beschikbaar is beantwoordt je vragen terwijl je de les doorwerkt.
Heb ik ervaring nodig om met Digital Marketing Academy te beginnen?
Ervaring vooraf is niet nodig. Digital Marketing Academy op CoddyKit is opgebouwd voor beginners tot gevorderden, zodat je hier of bij het begin kunt starten en in je eigen tempo kunt leren. Dit is les 4 van 4.
Hoe lang duurt de les “Cohort- en funnelquery's”?
De meeste lessen van CoddyKit duren ongeveer 5–10 minuten. Elke les is kort en interactief, zodat je gestaag vooruitgaat en op het web en in de app precies verdergaat waar je was gebleven.
Kan ik code schrijven en uitvoeren in deze les over Digital Marketing Academy?
Ja. Elke les over Digital Marketing Academy bevat een ingebouwde code-editor, zodat je rechtstreeks in je browser echte code kunt schrijven en uitvoeren en direct feedback van AI krijgt — lokale installatie is niet nodig.
Alle lessen in deze cursus
- Waarom marketeers SQL leren
- SELECT, WHERE, GROUP BY
- Marketingtabellen samenvoegen
- Cohort- en funnelquery's