Cohort retention curves: the PMF signal in your data
Meta description: Learn PostgreSQL cohort retention queries, Day 1/7/30 benchmarks by app category, and a structured decision framework to validate product-market fit or pivot.
Tags: backend, startup, saas, productengineering, architecture
TL;DR: Vanity metrics like total signups tell you nothing about product-market fit. Cohort retention curves do. This post covers the PostgreSQL queries to build them, benchmark thresholds by app category, the curve shape that signals PMF, and a decision tree for what to do with the numbers. If your Day 30 retention curve flattens above your category threshold, you have PMF. If it doesn’t, you have data to guide your pivot.
The metric that actually matters
The single most reliable indicator of product-market fit I’ve found is a flattening retention curve. Not downloads. Not signups. Not revenue, at least not initially.
Most teams get this wrong by tracking aggregate retention across all users. That masks the signal. You need cohort-based retention: group users by their signup week and track each group independently over time.
The PostgreSQL queries
Start with a cohort assignment CTE, then calculate retention by period:
WITH cohorts AS (
SELECT
user_id,
DATE_TRUNC('week', created_at) AS cohort_week
FROM users
),
activity AS (
SELECT
c.user_id,
c.cohort_week,
DATE_TRUNC('week', e.event_at) AS active_week
FROM cohorts c
JOIN events e ON e.user_id = c.user_id
GROUP BY 1, 2, 3
),
retention AS (
SELECT
cohort_week,
EXTRACT(DAY FROM active_week - cohort_week)::int / 7 AS week_number,
COUNT(DISTINCT user_id) AS active_users
FROM activity
GROUP BY 1, 2
)
SELECT
r.cohort_week,
r.week_number,
r.active_users,
ROUND(100.0 * r.active_users / c.cohort_size, 2) AS retention_pct
FROM retention r
JOIN (
SELECT cohort_week, COUNT(DISTINCT user_id) AS cohort_size
FROM cohorts GROUP BY 1
) c ON c.cohort_week = r.cohort_week
ORDER BY r.cohort_week, r.week_number;This gives you a week-by-week retention matrix per cohort. Plot each cohort as a line. The shape of those lines is your PMF signal.
Benchmark thresholds by app category
Benchmarks vary by category, and I should be upfront that these ranges are composites from Lenny Rachitsky’s data, Mixpanel reports, and my own observations. They’re directional, not gospel. But they’re useful starting points:
| App Category | Day 1 | Day 7 | Day 30 | PMF Floor (D30) |
|---|---|---|---|---|
| Social / Community | 40-50% | 25-35% | 15-25% | ~15% |
| SaaS / Productivity | 35-45% | 20-30% | 12-20% | ~12% |
| E-commerce | 25-35% | 12-20% | 8-15% | ~8% |
| Gaming (Casual) | 30-40% | 12-18% | 5-10% | ~5% |
| Fintech | 35-45% | 22-32% | 15-22% | ~15% |
The PMF Floor column is the one to watch. If your Day 30 retention sits below it and the curve is still declining, you don’t have product-market fit.
The flattening curve
A healthy retention curve has a pretty recognizable shape. Week 0 to 1 brings a sharp drop, which is normal. You’re losing tourists. Weeks 2 through 4 show continued decline, but the rate slows. Then somewhere around week 4 or later, the curve flattens. That’s the signal.
A flattening curve means a stable group of users keeps coming back. The absolute percentage matters less than the shape. A curve that flattens at 8% in e-commerce is a stronger PMF signal than one still declining at 20% in SaaS. That second one might look better in a slide deck, but it’s the worse position to be in.
You can quantify flattening with week-over-week retention delta:
SELECT
week_number,
retention_pct,
retention_pct - LAG(retention_pct) OVER (ORDER BY week_number) AS delta
FROM cohort_retention_summary
WHERE cohort_week = '2026-03-02';When delta stays between -0.5 and 0 for three or more consecutive weeks, the curve has flattened.
The decision framework
Once you have four or more weeks of cohort data, work through these questions in order:
-
Is Day 30 retention above your category’s PMF floor? If no, investigate which user segments retain best and narrow your ICP. If yes, move to question 2.
-
Is the retention curve flattening (delta near zero for 3+ weeks)? If yes, PMF confirmed. Shift focus to acquisition. Your product retains. If no, iterate on activation. Users find value but lose it. Look at onboarding, re-engagement, and your core loop.
-
Are newer cohorts retaining better than older ones? If yes, your product improvements are working. Keep going. If no, something recent hurt retention. Roll back and investigate.
Each branch points to a specific action rather than a vague “keep iterating.”
What to do with this
Run the cohort query weekly. Automate it. Pipe results into a dashboard. Aggregate retention is noise; cohort retention is signal. Track each signup week as its own line on the chart.
Benchmark against your category, not your ambition. A 10% Day 30 retention in SaaS is below the PMF floor. The same number in e-commerce is solid. Know your threshold before you start interpreting the data.
And measure the delta, not just the level. The flattening of the curve, when week-over-week change approaches zero, is the definitive PMF signal. Three consecutive weeks of near-zero delta at or above your category floor means you stop questioning fit and start scaling acquisition.