Cohort analysis is one of the most useful tools in a product team's arsenal. It tells you whether the improvements you shipped last month actually stuck or whether users who signed up in February are churning at the same rate as users from six months ago. The problem is that cohort analysis has traditionally lived behind a wall of SQL, accessible only to whoever controls the data warehouse.
This guide walks through what cohort analysis actually is, why it matters, and how product managers and analysts can run it without writing a single query.
What Is Cohort Analysis (and Why Teams Get It Wrong)
A cohort is a group of users who share a common characteristic at a specific point in time. The most common cohort is "users who signed up during week X." Cohort analysis then tracks what those users did over the following weeks or months.
The classic mistake is treating cohort analysis as a one-time report. You run it once, see that week-2 retention is 42%, nod sagely, and move on. That number means nothing in isolation.
What actually matters:
To answer those questions, you need to run cohort analysis continuously which means you need easy, repeatable access to your database.
The Standard SQL Approach (and Why It Breaks Down)
Here's what a typical cohort retention query looks like in PostgreSQL:
WITH cohorts AS (
SELECT
user_id,
DATE_TRUNC('week', created_at) AS cohort_week
FROM users
),
activity AS (
SELECT
user_id,
DATE_TRUNC('week', activity_date) AS activity_week
FROM user_events
WHERE event_type = 'session_start'
),
cohort_activity AS (
SELECT
c.cohort_week,
c.user_id,
EXTRACT(EPOCH FROM (a.activity_week - c.cohort_week)) / 604800 AS week_number
FROM cohorts c
JOIN activity a ON c.user_id = a.user_id
)
SELECT
cohort_week,
week_number,
COUNT(DISTINCT user_id) AS active_users
FROM cohort_activity
WHERE week_number >= 0 AND week_number <= 12
GROUP BY cohort_week, week_number
ORDER BY cohort_week, week_number;This works if you know SQL, understand your schema, and have time to write it. Then modify it when your schema changes. Then re-run it every week. Then explain it to your PM who wants to slice by pricing plan.
Most product teams have one person who owns this query. When that person is on vacation, cohort analysis stops.
Running Cohort Analysis in Plain English
With a tool like AI for Database, you describe what you want in plain English and the AI writes and executes the query against your actual database.
Instead of the query above, you type:
"Show me weekly retention cohorts for users who signed up in Q1 2026. Group by signup week and show retention through week 8."
The system translates that into SQL, runs it against your database, and returns a cohort table no intermediate step where you copy-paste a query into a SQL client.
More importantly, you can then ask follow-up questions:
"Now break that down by pricing plan."
"Which cohort had the highest week-4 retention?"
"Filter to users who completed onboarding within 24 hours of signup."
Each follow-up refines the analysis without rewriting a query. For product teams doing weekly retention reviews, this removes the bottleneck entirely.
Setting Up Your First Cohort Analysis
Whether you're using SQL directly or a natural language interface, you need three things:
1. A signup/creation date for each user
Usually this is users.created_at. Make sure it's a proper timestamp, not a string. If your users table doesn't have this, check for an account_activations or onboarding_completions event table.
2. An activity signal
This is the event that defines "active." Common choices:
The right signal depends on what "retained" means for your product. For a project management tool, it's probably "created or updated a task." For a messaging app, it's probably "sent a message." Avoid using page views they're too noisy.
3. A time window
Decide on your cohort period (weekly is usually right for SaaS products) and how many periods forward you want to track.
Once you have these three elements, your cohort query whether typed in SQL or described in plain English will be consistent and repeatable.
Reading Cohort Tables: What Good Looks Like
A standard cohort retention table looks like this (simplified):
Cohort Week | Week 0 | Week 1 | Week 2 | Week 4 | Week 8
2026-01-06 | 100% | 52% | 41% | 35% | 28%
2026-01-13 | 100% | 54% | 43% | 37% | 30%
2026-01-20 | 100% | 56% | 46% | 39%
2026-01-27 | 100% | 61% | 49%
A few things to notice:
The diagonal tells you recent cohorts are improving. Each cohort's week-1 retention is trending up (52% → 61%). That's a signal that something you changed in January is working.
Recent cohorts have incomplete later weeks. The January 27 cohort is too recent to have week-4 data. That's expected don't read absence of data as a bad signal.
Week-0 is always 100%. It's your baseline. If you see anything other than 100% in week 0, your cohort definition has a problem.
When cohort retention flattens rather than continuing to drop, that's healthy. It means some portion of each cohort has found enough value to keep coming back indefinitely. Products with strong cohort flattening build durable growth.
Segmenting Cohorts: Where the Real Insights Live
Basic week-by-week cohort analysis is a starting point. The insights usually come from segmentation.
By acquisition channel:
SELECT
u.cohort_week,
u.acquisition_source,
u.week_number,
COUNT(DISTINCT u.user_id) AS active_users
FROM cohort_activity u
GROUP BY u.cohort_week, u.acquisition_source, u.week_number;Or in plain English: "Show me cohort retention by acquisition source for users who signed up in Q1."
If organic search users retain at 40% week-4 but paid social users retain at 18%, you have a channel mix problem not just a retention problem.
By onboarding completion:
Users who complete your onboarding flow almost always retain better. Quantifying that gap gives you a business case for investing in onboarding improvements.
By plan or tier:
Pro users retaining at 60% while free users retain at 20% isn't surprising but the question is whether your free-to-paid conversion is happening before or after week 4. That determines where to put your upgrade prompts.
Making Cohort Analysis a Team Habit
The reason cohort analysis stays siloed in most companies isn't that people don't care it's that access is too hard. If you have to file a Jira ticket to get a query run, you'll only look at cohorts when something is obviously wrong.
The alternative: put cohort analysis in a shared dashboard that refreshes automatically.
With AI for Database, you can build a dashboard that runs your cohort query on a schedule and shows the updated table every Monday morning before your product review meeting. No manual steps. No dependency on whoever owns the SQL.
When any team member can ask follow-up questions in plain English, cohort analysis stops being a weekly report and starts being an ongoing conversation about user behavior.