Retention Curve and PMF: How AI Finds the Signal in the Data

In 2014, Alex Schultz, VP of Growth at Facebook, gave one of the most-cited takes on product-market fit in his Stanford CS183B lecture: if your retention curve flattens out and runs parallel to the x-axis, you have PMF. Not NPS, not revenue growth, not registration numbers. The shape of the curve.

The problem is that real retention curves are noisy. Small cohorts produce statistical outliers, seasonality distorts the trend, and the difference between “the curve is stabilizing” and “the curve is slowly sliding toward zero” can be visually indistinguishable. That’s where AI analysis earns its keep: it finds flattening patterns the eye misses.

This article walks through building retention curves from raw events to a formal PMF verdict — SQL queries for cohort analysis, statistical tests for flattening, and prompts for AI interpretation.

Retention Curve as a PMF Indicator

A retention curve shows what share of users from a cohort returns after N days/weeks/months. Day 0 is always 100%. Day 1 drops sharply. Everything after that tells you more about the product than almost any other single metric.

Three characteristic patterns:

Plateau (PMF signal). The curve levels off. Example: Day 1 — 40%, Day 7 — 25%, Day 14 — 20%, Day 30 — 18%, Day 60 — 17%, Day 90 — 17%. It found a floor and stopped falling. Some users stay. That’s PMF.

Slow decay (weak PMF or no PMF). The curve drops without clear stabilization: Day 30 — 15%, Day 60 — 12%, Day 90 — 9%, Day 120 — 7%. It looks like a plateau at first, but you’re bleeding 2–3 percentage points every month. At that rate, retention hits zero within a year.

Cliff (no PMF). Day 1 — 15%, Day 7 — 3%, Day 30 — 0.5%. Users try the product and walk away. No stabilization at all.

The key question: how do you tell the first pattern from the second? Visually, “a plateau at 17%” and “a slow decline from 18% to 12% over six months” can look identical in a 90-day window. Formal analysis is required.

SQL Queries for Building Retention Curves

You need an events table with user_id, event_timestamp, and event_name. Minimum schema:

CREATE TABLE events (
    user_id       TEXT NOT NULL,
    event_name    TEXT NOT NULL,
    event_timestamp TIMESTAMP NOT NULL
);

Defining Cohorts

A cohort is a group of users who registered — or first appeared — in the same period. Start by calculating each user’s first-seen date:

CREATE MATERIALIZED VIEW user_first_seen AS
SELECT
    user_id,
    DATE_TRUNC('week', MIN(event_timestamp)) AS cohort_week
FROM events
GROUP BY user_id;

DATE_TRUNC('week', ...) groups users by week. For daily-use products like messengers or social apps, use 'day'. For SaaS with a monthly billing cycle, use 'month'.

Calculating Cohort Retention

WITH activity AS (
    SELECT DISTINCT
        e.user_id,
        u.cohort_week,
        DATE_TRUNC('week', e.event_timestamp) AS activity_week
    FROM events e
    JOIN user_first_seen u USING (user_id)
),
retention_raw AS (
    SELECT
        cohort_week,
        EXTRACT(DAYS FROM activity_week - cohort_week)::INT / 7 AS week_number,
        COUNT(DISTINCT user_id) AS active_users
    FROM activity
    GROUP BY cohort_week, week_number
),
cohort_sizes AS (
    SELECT
        cohort_week,
        COUNT(DISTINCT user_id) AS cohort_size
    FROM user_first_seen
    GROUP BY cohort_week
)
SELECT
    r.cohort_week,
    r.week_number,
    r.active_users,
    c.cohort_size,
    ROUND(100.0 * r.active_users / c.cohort_size, 2) AS retention_pct
FROM retention_raw r
JOIN cohort_sizes c USING (cohort_week)
WHERE r.week_number >= 0
ORDER BY r.cohort_week, r.week_number;

The result is a table: cohort, week number, percentage of users who came back. That’s the raw data for the retention curve.

Aggregated Retention Curve

For PMF analysis, you don’t want individual cohort scatter — you need an average curve across mature cohorts:

WITH mature_cohorts AS (
    -- Take cohorts that are 12+ weeks old
    SELECT cohort_week
    FROM user_first_seen
    GROUP BY cohort_week
    HAVING MAX(CURRENT_DATE - cohort_week::DATE) >= 84
),
retention_data AS (
    SELECT
        r.week_number,
        AVG(r.retention_pct) AS avg_retention,
        STDDEV(r.retention_pct) AS std_retention,
        COUNT(*) AS num_cohorts
    FROM retention_raw r
    JOIN mature_cohorts mc USING (cohort_week)
    WHERE r.week_number BETWEEN 1 AND 12
    GROUP BY r.week_number
)
SELECT
    week_number,
    ROUND(avg_retention, 2) AS avg_retention_pct,
    ROUND(std_retention, 2) AS std_dev,
    num_cohorts,
    ROUND(std_retention / SQRT(num_cohorts), 2) AS standard_error
FROM retention_data
ORDER BY week_number;

standard_error shows how reliable the average is. If SE exceeds 3–4 pp, there’s not enough data to draw conclusions.

Detecting Flattening: A Statistical Approach

Eyeballing “the curve has plateaued” is subjective. Formalizing it requires a definition: flattening is when the rate of retention decline is statistically indistinguishable from zero.

Rolling Slope Method

Calculate the slope over the last N data points. If it doesn’t differ from zero at a given significance level, the curve has stabilized.

WITH weekly_retention AS (
    -- Use data from the previous query
    SELECT week_number, avg_retention_pct
    FROM retention_data
    ORDER BY week_number
),
slope_calc AS (
    SELECT
        week_number,
        avg_retention_pct,
        -- Slope over the last 4 weeks (linear regression)
        REGR_SLOPE(avg_retention_pct, week_number)
            OVER (ORDER BY week_number ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
            AS rolling_slope,
        -- R² to assess linearity
        REGR_R2(avg_retention_pct, week_number)
            OVER (ORDER BY week_number ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
            AS rolling_r2,
        -- Number of points in the window
        COUNT(*) OVER (ORDER BY week_number ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
            AS window_size
    FROM weekly_retention
)
SELECT
    week_number,
    avg_retention_pct,
    ROUND(rolling_slope, 3) AS slope_per_week,
    ROUND(rolling_r2, 3) AS r_squared,
    CASE
        WHEN window_size >= 4 AND ABS(rolling_slope) < 0.5 THEN 'PLATEAU'
        WHEN window_size >= 4 AND rolling_slope < -0.5 THEN 'DECLINING'
        ELSE 'INSUFFICIENT_DATA'
    END AS curve_status
FROM slope_calc
WHERE window_size >= 4
ORDER BY week_number;

The 0.5 threshold means: if retention drops by less than 0.5 pp per week, the curve is considered stabilized. For monthly cohorts, use 1–2 pp per month.

Period Comparison Method

Alternative approach: compare retention across two consecutive halves of the observed period using a t-test.

WITH period_comparison AS (
    SELECT
        CASE
            WHEN week_number BETWEEN 5 AND 8 THEN 'period_1'
            WHEN week_number BETWEEN 9 AND 12 THEN 'period_2'
        END AS period,
        avg_retention_pct
    FROM retention_data
    WHERE week_number BETWEEN 5 AND 12
)
SELECT
    period,
    ROUND(AVG(avg_retention_pct), 2) AS mean_retention,
    ROUND(STDDEV(avg_retention_pct), 2) AS std_retention,
    COUNT(*) AS n_weeks
FROM period_comparison
GROUP BY period
ORDER BY period;

If the difference in means between period_1 and period_2 isn’t statistically significant (p > 0.05), the curve has stabilized. Calculate the p-value in Python or pass the data to AI for interpretation.

AI Analysis of Retention Curves

SQL produces numbers. AI turns them into conclusions. Three prompts for sequential analysis.

Prompt 1: Curve Shape Diagnosis

You are a product data analyst. Take the retention curve data
and identify the pattern.

Data (format: week_number | avg_retention_pct | std_dev):
[INSERT SQL QUERY RESULT]

Tasks:
1. Classify the curve shape: plateau / slow_decay / cliff
2. If plateau — identify the week where stabilization begins
   and the level at which it stabilizes
3. Calculate the slope of the curve over the last 4 data points
4. Assess statistical significance: is there enough data
   for a conclusion? What is the confidence interval?
5. Give a clear verdict: is there a PMF signal or not?

Response format: JSON with fields curve_type, plateau_start_week,
plateau_level, final_slope, confidence, pmf_signal (boolean),
reasoning.

Requesting JSON matters. Structured output enables pipeline automation: events → SQL → AI analysis → Slack alert.

Prompt 2: Comparative Cohort Analysis

You are a product data analyst. Compare retention curves
across multiple cohorts and identify the trend.

Retention data by cohort:
[INSERT COHORT TABLE]

Tasks:
1. Compare the plateau level between early and late cohorts
2. Is the plateau level increasing from cohort to cohort?
   (This is a signal of improving PMF)
3. Is the rate of initial decline (week 0 → week 4) changing?
4. Are there anomalous cohorts? If so, hypothesize why.
5. Forecast the plateau level for the next 3 cohorts.

Format: JSON with fields trend_direction (improving/stable/
degrading), plateau_levels_by_cohort, anomalies,
forecast, confidence.

This prompt catches what a static single-curve analysis misses: the trajectory of PMF. A plateau at 15% is one thing. If three months ago it was at 10%, that’s a strong positive signal.

Prompt 3: Segmented Analysis

You are a product data analyst. Analyze retention curves
for different user segments.

Segment A (organic traffic):
[DATA]

Segment B (paid traffic):
[DATA]

Segment C (referral traffic):
[DATA]

Tasks:
1. For each segment: identify the curve shape and plateau level
2. Which segment shows the best PMF signal?
3. Are there segments with no PMF at all?
4. Calculate the difference in LTV potential between segments
   (based on retention levels)
5. Recommendations: which channels to increase spend on,
   which to cut

Format: JSON with fields segments (array with curve_type,
plateau_level for each), best_segment, worst_segment,
ltv_multiplier, recommendations.

Segmentation surfaces what aggregates hide. Combined retention of 18% might be 30% for organic and 8% for paid. PMF exists — just not for every channel.

Full Pipeline: From Events to PMF Verdict

A practical implementation chains SQL and AI into an automated process.

Step 1: Extract Data

-- Full query: weekly retention, mature cohorts,
-- with confidence intervals
WITH user_first_seen AS (
    SELECT
        user_id,
        DATE_TRUNC('week', MIN(event_timestamp)) AS cohort_week
    FROM events
    WHERE event_name = 'session_start'
    GROUP BY user_id
),
weekly_activity AS (
    SELECT DISTINCT
        e.user_id,
        u.cohort_week,
        DATE_TRUNC('week', e.event_timestamp) AS activity_week
    FROM events e
    JOIN user_first_seen u USING (user_id)
    WHERE e.event_name = 'session_start'
),
retention_by_cohort AS (
    SELECT
        w.cohort_week,
        EXTRACT(DAYS FROM w.activity_week - w.cohort_week)::INT / 7
            AS week_number,
        COUNT(DISTINCT w.user_id) AS active_users,
        c.cohort_size,
        ROUND(100.0 * COUNT(DISTINCT w.user_id) / c.cohort_size, 2)
            AS retention_pct
    FROM weekly_activity w
    JOIN (
        SELECT cohort_week, COUNT(*) AS cohort_size
        FROM user_first_seen
        GROUP BY cohort_week
    ) c USING (cohort_week)
    GROUP BY w.cohort_week, week_number, c.cohort_size
),
aggregated AS (
    SELECT
        week_number,
        ROUND(AVG(retention_pct), 2) AS avg_retention,
        ROUND(STDDEV(retention_pct), 2) AS std_dev,
        COUNT(DISTINCT cohort_week) AS n_cohorts,
        ROUND(STDDEV(retention_pct) / SQRT(COUNT(DISTINCT cohort_week)), 2)
            AS se
    FROM retention_by_cohort
    WHERE week_number BETWEEN 0 AND 12
      AND cohort_week <= CURRENT_DATE - INTERVAL '84 days'
    GROUP BY week_number
    HAVING COUNT(DISTINCT cohort_week) >= 4
)
SELECT * FROM aggregated ORDER BY week_number;

Step 2: Compute Slope and Flattening Metrics

WITH slopes AS (
    SELECT
        week_number,
        avg_retention,
        avg_retention - LAG(avg_retention) OVER (ORDER BY week_number)
            AS week_over_week_change,
        REGR_SLOPE(avg_retention, week_number)
            OVER (ORDER BY week_number ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
            AS rolling_slope_4w
    FROM aggregated
)
SELECT
    week_number,
    avg_retention,
    ROUND(week_over_week_change, 2) AS wow_change,
    ROUND(rolling_slope_4w, 3) AS slope_4w,
    CASE
        WHEN week_number >= 4 AND ABS(rolling_slope_4w) < 0.5
            THEN 'FLATTENING'
        WHEN week_number >= 4 AND rolling_slope_4w < -0.5
            THEN 'DECLINING'
        ELSE 'EARLY_STAGE'
    END AS status
FROM slopes
ORDER BY week_number;

Step 3: AI Interpretation

Feed the results of both queries into Prompt 1 (curve shape diagnosis). If the verdict is plateau, run Prompt 2 (cohort dynamics) to confirm.

Step 4: Formal PMF Criteria

When all of these are met simultaneously, PMF is confirmed:

All three required criteria must hold at once. PMF isn’t a single metric — it’s a convergence of signals.

Retention Benchmarks by Product Type

A plateau level means nothing without context. Reference points by product type:

B2C mobile apps. Day 1 retention: 25–40% (good), Day 30: 10–15%. A plateau above 15% at Day 90 is a strong PMF signal. For social apps and messengers the bar is higher: 20–25% at Day 90.

SaaS (B2B). Month 1 retention: 80–90%, Month 12: 60–75%. A plateau above 70% at Month 12 means a healthy product. Below 50% points to a value delivery problem.

E-commerce. Repeat purchase rate at Month 3: 20–30%. A plateau above 25% means users have formed a habit of buying from that store.

Content platforms. Week 4 retention: 15–25%. A plateau above 20% at Week 12 means the content keeps its audience. Below 10% — users got what they needed and left (one-shot usage).

Cross-category comparisons are meaningless. 20% Day 90 retention for a mobile game puts you in the top decile. The same 20% Month 3 retention for a subscription SaaS means 80% of paying customers churned within a quarter.

False PMF Signals

A retention curve can look like a plateau even without real PMF. Four traps to watch for.

Survivorship bias in cohorts. If cohorts only include users who completed a specific action (payment, onboarding), retention looks artificially high. The right approach: include all registered users, no filtering.

Small cohorts. 50 users is noise, not signal. One active user out of 50 is 2% retention. Two is 4%. That’s a 100% swing from a single person. You need 200–500 users per cohort for meaningful conclusions.

Seasonality. December cohorts (pre-holiday) and July cohorts (summer slowdown) behave fundamentally differently. Aggregating them mixes signal with noise. Analyze same-season cohorts separately, or apply a seasonal adjustment.

Push notifications and reactivation. Aggressive push campaigns inflate retention. The user opens the app because of a notification, not because they need the product. That retention doesn’t convert into monetization and collapses the moment push is turned off.

SQL query to check for survivorship bias:

-- Compare retention for "all registered users" vs
-- "users who completed onboarding"
WITH all_users AS (
    SELECT user_id, cohort_week
    FROM user_first_seen
),
onboarded_users AS (
    SELECT DISTINCT e.user_id, u.cohort_week
    FROM events e
    JOIN user_first_seen u USING (user_id)
    WHERE e.event_name = 'onboarding_complete'
),
retention_all AS (
    SELECT
        'all_users' AS segment,
        week_number,
        ROUND(AVG(retention_pct), 2) AS avg_retention
    FROM retention_by_cohort
    GROUP BY week_number
),
retention_onboarded AS (
    -- Analogous calculation for onboarded_users only
    SELECT
        'onboarded' AS segment,
        week_number,
        ROUND(AVG(retention_pct), 2) AS avg_retention
    FROM retention_by_cohort_onboarded
    GROUP BY week_number
)
SELECT * FROM retention_all
UNION ALL
SELECT * FROM retention_onboarded
ORDER BY segment, week_number;

If the gap between the two segments exceeds 10 pp at Week 8+, retention is inflated by filtering. Real PMF has to show up in the unfiltered cohort.

Automation: A Weekly PMF Monitor

Replace manual analysis with an automated pipeline that reports PMF status every week.

Architecture

Cron (Monday, 09:00)
    │
    ▼
SQL queries to the events database
    │
    ├─ Aggregated retention curve
    ├─ Slopes and flattening metrics
    └─ Retention by cohort
    │
    ▼
AI analysis (Prompt 1 + Prompt 2)
    │
    ▼
Structured JSON
    │
    ├─ pmf_signal: true/false
    ├─ plateau_level: X%
    ├─ trend: improving/stable/degrading
    └─ confidence: high/medium/low
    │
    ▼
Slack alert / Dashboard update

Python Orchestration Script

import json
from datetime import datetime

def run_pmf_monitor(db_conn, llm_client):
    # 1. Execute SQL queries
    retention_data = db_conn.execute(RETENTION_QUERY).fetchall()
    slope_data = db_conn.execute(SLOPE_QUERY).fetchall()

    # 2. Format data for the prompt
    data_str = format_retention_table(retention_data)
    slope_str = format_slope_table(slope_data)

    # 3. AI analysis
    prompt = DIAGNOSTIC_PROMPT.format(
        retention_data=data_str,
        slope_data=slope_str
    )
    response = llm_client.chat(
        model="claude-sonnet-4-6",
        messages=[{"role": "user", "content": prompt}],
        response_format={"type": "json_object"}
    )
    result = json.loads(response.content)

    # 4. Check formal criteria
    formal_check = {
        "slope_ok": abs(result["final_slope"]) < 0.5,
        "plateau_ok": result["plateau_level"] > 15,
        "cohorts_ok": len(retention_data) >= 6,
        "ai_signal": result["pmf_signal"]
    }
    pmf_confirmed = all([
        formal_check["slope_ok"],
        formal_check["plateau_ok"],
        formal_check["cohorts_ok"]
    ])

    return {
        "date": datetime.now().isoformat(),
        "pmf_confirmed": pmf_confirmed,
        "details": result,
        "formal_check": formal_check
    }

The choice of claude-sonnet-4-6 is deliberate: for tabular data and numerical analysis, Sonnet is comparable to Opus at a fraction of the cost. For unstructured context — anomaly diagnosis, strategic recommendations — use Opus.

What to Do After Detecting PMF

Three scenarios depending on what you find.

PMF confirmed (plateau > threshold, slope ≈ 0, trend stable or improving). Shift focus from product development to growth. The retention data says the product solves a real problem. Next questions: how to scale acquisition, which channels bring users with the best retention (Prompt 3), how to push the plateau level higher through onboarding work.

PMF not confirmed (curve declining, slope clearly negative). Scaling acquisition here is burning money. Go back to product: user interviews, churn moment analysis (which week sees the biggest drop), finding the magic number — the action that predicts retention.

Uncertainty (slope is borderline, limited data, small cohorts). Extend the observation window. Wait another 4–6 weeks for mature cohorts to form. Grow cohort sizes through targeted acquisition. Run the analysis again.

Limitations of the Method

Retention curve is necessary but not sufficient.

A product can show a plateau with weak willingness to pay. Users return but don’t convert to paid. Retention holds; the business model doesn’t.

Retention also ignores depth of usage. 17% of users return — but if each visit is 30 seconds rather than 10 minutes, engagement is eroding even as the retention number stays flat.

For the full picture, pair retention curves with other metrics: revenue retention (NRR), engagement depth (DAU/MAU), willingness to pay, NPS. Retention is the foundation. Everything else builds on it.

Conclusion

A flattening retention curve is still one of the most reliable quantitative PMF signals you have. SQL queries build the curve. Statistical tests formalize what would otherwise be a gut call. AI prompts automate interpretation and catch patterns that manual review misses.

The minimum setup: an events table with user_id and timestamp, a cohort retention query, and one diagnostic prompt. Everything else — segmentation, cohort comparison, automated monitoring — adds precision. Each layer cuts uncertainty, but the basic answer to “is there PMF?” is available from the first run.

Need help setting up retention analysis and PMF detection for your product? I help startups build AI products and automate processes — belov.works.