Validating Product-Market Fit with Cohort Retention Curves

---
title: "Validating Product-Market Fit with Cohort Retention Curves in PostgreSQL"
published: true
description: "Build cohort retention queries in PostgreSQL, benchmark against Day 1/7/30 thresholds by app category, and use a decision framework to know whether to pivot or double down."
tags: postgresql, architecture, performance, api
canonical_url: https://blog.mvpfactory.co/cohort-retention-curves-pmf-signal
---

## What We Will Build

By the end of this tutorial, you will have a working PostgreSQL cohort retention query, a benchmark table to interpret your numbers, and a decision tree that tells you whether your product has market fit — or what to fix next.

Let me show you a pattern I use in every project that tracks users: cohort-based retention analysis. Not aggregate retention. Cohort retention. The difference is everything.

## Prerequisites

- A PostgreSQL database (12+) with a `users` table (`user_id`, `created_at`) and an `events` table (`user_id`, `event_at`)
- At least four weeks of user activity data
- Basic SQL comfort (CTEs, window functions)

## Step 1: Build the Cohort Retention Query

Here is the minimal setup to get this working. This single query gives you a week-by-week retention matrix per signup cohort:

sql
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;

Plot each cohort as its own line. The shape of those lines is your PMF signal.

## Step 2: Know Your Benchmarks

These ranges are composites from Lenny Rachitsky's data, Mixpanel reports, and direct observation. They are directional, not gospel — but they are 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%             |

Watch the PMF Floor column. If Day 30 retention sits below it and the curve is still declining, you do not have product-market fit.

## Step 3: Detect the Flattening Curve

A healthy curve drops sharply in week one (you are losing tourists), declines more slowly through weeks two to four, then flattens. That flattening — a stable group of users who keep returning — is the signal.

Here is the gotcha that will save you hours: a curve flattening at 8% in e-commerce is a stronger PMF signal than one still declining at 20% in SaaS. Shape beats absolute percentage.

Quantify the flattening with this delta query:

sql
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.

## Step 4: The Decision Framework

With four-plus weeks of data, work through these questions in order:

1. **Is Day 30 retention above your category's PMF floor?** No → investigate which user segments retain best and narrow your ICP. Yes → move to question 2.
2. **Is the curve flattening (delta near zero for 3+ weeks)?** Yes → PMF confirmed, shift focus to acquisition. No → iterate on activation — look at onboarding, re-engagement, and your core loop.
3. **Are newer cohorts retaining better than older ones?** Yes → your product improvements are working. No → something recent hurt retention. Roll back and investigate.

Each branch points to a specific action, not a vague "keep iterating."

## Gotchas

- **Tracking aggregate retention across all users masks the signal.** Always group by signup week. The docs do not mention this, but aggregate numbers will mislead you every time.
- **Benchmark against your category, not your ambition.** A 10% Day 30 in SaaS is below the PMF floor. The same number in e-commerce is solid.
- **Do not eyeball flattening.** Compute the delta. Three consecutive weeks of near-zero delta at or above your category floor is your confirmation threshold.
- **Week 0-to-1 drop is normal.** Do not panic over the initial cliff — focus on the shape after week two.

## Wrapping Up

Run this cohort query weekly. Automate it. Pipe results into a dashboard. Aggregate retention is noise; cohort retention is signal. Three consecutive weeks of near-zero delta at or above your category floor means you stop questioning fit and start scaling acquisition.

The queries are simple. The benchmarks are known. The decision framework is concrete. Now go measure the shape of your curve.