Cohort and Funnel Queries
Answer retention questions.
Cohort and Funnel Queries is a free Digital Marketing Academy lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the Digital Marketing Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
What Is a Cohort?
A cohort is a group of users who share a starting event, usually their signup month. Tracking each cohort over time reveals true retention.
Aggregate metrics hide churn; cohorts expose whether new users actually stick around.
SELECT user_id,
DATE_TRUNC('month', signup_date) AS cohort_month
FROM users;Defining the Cohort
The first step is labeling each user with their cohort. DATE_TRUNC rounds signup dates down to the month so users group cleanly.
Store this as a reusable building block for everything that follows.
SELECT DATE_TRUNC('month', signup_date) AS cohort_month,
COUNT(*) AS cohort_size
FROM users
GROUP BY 1
ORDER BY 1;Activity Periods
Retention measures activity in months after signup. Compute the period as the gap between an order's month and the cohort month.
Period 0 is the signup month, period 1 is the next month, and so on.
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;Building a Retention Grid
Combine cohort month and period, then count distinct active users per cell. The result is the classic retention triangle.
Each row is a cohort; each column is months since signup.
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;Retention Rate
Raw counts are hard to compare across cohorts of different sizes. Divide active users in each period by the cohort's original size.
A window function pulls period-0 size onto every row for the ratio.
SELECT cohort, period,
active * 1.0 / FIRST_VALUE(active) OVER (
PARTITION BY cohort ORDER BY period
) AS retention
FROM cohort_activity;What Is a Funnel?
A funnel tracks users through ordered steps: visit, sign up, add to cart, purchase. The drop between steps shows where you lose people.
Funnel analysis turns vague "low conversion" complaints into a specific leaky stage.
SELECT step, COUNT(DISTINCT user_id) AS users
FROM events
WHERE step IN ('visit', 'signup', 'cart', 'purchase')
GROUP BY step;Counting Each Step
Conditional aggregation counts users at each stage in a single pass. FILTER tallies distinct users who reached each step.
One query, one row, the whole funnel at a glance.
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;Step Conversion Rates
The interesting number is the ratio between consecutive steps. Dividing each step by the previous one exposes the biggest drop-off.
Cast to a decimal so integer division does not zero out your rate.
SELECT signups * 1.0 / NULLIF(visits, 0) AS visit_to_signup,
buyers * 1.0 / NULLIF(signups, 0) AS signup_to_buy
FROM funnel_counts;Ordered Funnels with Timestamps
A strict funnel requires steps to happen in order. Join each step to the next only when its timestamp is later.
This prevents counting a purchase that happened before the matching signup.
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 by Channel
Splitting the funnel by acquisition channel shows which sources send users that actually convert, not just click.
Group the conditional counts by channel to compare quality across 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;From Insight to Action
Cohorts tell you if retention is improving release over release; funnels tell you which step to fix first.
Together they shift marketing from reporting what happened to diagnosing why, and where to invest next.
Quick Check
Your funnel counts users at visit, signup, and purchase. What do consecutive step ratios reveal?
Recap
Cohorts group users by start month and track retention over periods using DATE_TRUNC and AGE. Funnels count distinct users per ordered step and compare consecutive ratios.
You now have the advanced toolkit to diagnose retention and conversion entirely in SQL.
Frequently asked questions
Is the “Cohort and Funnel Queries” lesson free?
Yes — the full text of “Cohort and Funnel Queries” is free to read here on the web, and the Digital Marketing Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the Digital Marketing Academy course, upgrade to CoddyKit PRO.
What will I learn in “Cohort and Funnel Queries”?
Answer retention questions. You practise Digital Marketing Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start Digital Marketing Academy?
No prior experience is required. Digital Marketing Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Cohort and Funnel Queries” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this Digital Marketing Academy lesson?
Yes. Every Digital Marketing Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.
All lessons in this course
- Why Marketers Learn SQL
- SELECT, WHERE, GROUP BY
- Joining Marketing Tables
- Cohort and Funnel Queries