Cohort Analysis

Cohort Analysis Skill

Produces a structured cohort analysis covering retention curves, LTV estimation, behavioural segmentation, leading churn indicators, and prioritised interventions. Output is ready to present to product leadership or share with growth and data teams.

Required Inputs

Ask for any of these that are missing before starting. Do not fabricate numbers, benchmarks, or schema details.

If the user cannot supply actual data, produce the framework with clearly marked placeholders ([X%], [£X], [N users]) and note which sections require real data to complete.

Process

Follow these steps in order:

1. Confirm inputs — surface any ambiguities before producing output (e.g. conflicting cohort definitions, missing observation window).
2. Define cohorts — establish entry event, exit criteria, and exclusions so cohorts are mutually exclusive.
3. Build retention table — populate or template the period-by-period matrix; identify the plateau period.
4. Project LTV — use observed retention data only; flag if data is insufficient.
5. Segment by behaviour — create mutually exclusive behavioural segments; identify the activation threshold.
6. Surface leading churn signals — list observable signals with lead time and intervention mapping.
7. Compare cohorts over time — assess whether product changes are showing up in newer cohorts.
8. Prioritise recommendations — tie every recommendation to a specific cohort or segment finding.
9. Provide SQL reference — adapt the template query to the user's actual schema if supplied.
10. Run quality checks — verify all checklist items before delivering output.

Output Template

Produce the following sections in order. Omit a section only if explicitly out of scope; note the omission.

Cohort Analysis: [Product / Feature]

Analysis goal: [Retention / LTV / Behavioural segmentation / Churn prediction] Cohort definition: [e.g. Acquisition month — users grouped by calendar month of first sign-up] Observation window: [X months / weeks] Primary metric: [Metric name and definition] Data source: [Tables or metrics used — do not invent if not supplied] Date prepared: [Date]

1. Cohort Definitions

Cohort logic:

• Entry event: [e.g. First sign-up / First purchase / Feature activation]
• Exit / churn definition: [e.g. No activity matching key retention event for 30 days]
• Exclusions: [e.g. Internal test accounts, trial users with < 3 days of data, cohorts with < [N] users — see anti-patterns]

Note: If any cohort falls below the minimum size threshold for statistical reliability, flag it explicitly and exclude it from trend conclusions.

2. Retention Curve

How to read: Each cell shows the percentage of the cohort that performed the key retention event in period N. Period 0 = 100% by definition.

Retention plateau: [At what period does the curve flatten? What % does it flatten at? If the observation window is too short to show a plateau, state this explicitly.]

Key observations:

• [e.g. The largest single-period drop is P1 → P2, averaging X pp — this is the primary churn moment to address]
• [e.g. Cohorts acquired via [channel] show X pp higher retention at P6 vs the overall average]
• [e.g. Retention at P3 has moved from X% (oldest cohort) to Y% (newest cohort) — a change of Z pp]

Retention chart — render one line per cohort, period on x-axis:

{
  "type": "line",
  "title": "Retention by cohort (%)",
  "labels": ["P0", "P1", "P2", "P3", "P6", "P12"],
  "series": [
    { "name": "[Cohort 1]", "data": [100, "[X]", "[X]", "[X]", "[X]", "[X]"] },
    { "name": "[Cohort 2]", "data": [100, "[X]", "[X]", "[X]", "[X]", "[X]"] }
  ]
}

3. LTV Projection

Skip this section if revenue data is not available. Do not estimate ARPU without a data source — note the gap and ask for it.

ARPU per period: [Currency and amount per active user per period — sourced from: X] Retention curve used: [Which cohort or blended average, and why]

Blended LTV at 12M: [£X — specify which cohorts and weighting method]

LTV by segment:

4. Behavioural Segmentation

Segments are defined by what users did, not when they arrived. Segments must be mutually exclusive and collectively exhaustive within the analysed population.

Activation threshold (the "aha moment"): [What specific action, taken within the first X days, most strongly predicts long-term retention? Source this from the data — do not assume a generic answer.]

5. Leading Indicators of Churn

Signals that appear before users churn, enabling pre-emptive intervention. All signals listed must be observable in production data — flag any that are theoretical only.

Data requirement: Correlation strength must come from observed data. If unavailable, mark as [Hypothesis — not yet validated] and recommend an A/B test or survival analysis to confirm.

6. Cohort Comparison: Trend Over Time

Assess whether product changes are visible in retention outcomes for newer cohorts.

Verdict: [Are more recent cohorts performing better or worse? What shipped during this period that could explain the change? If no causal explanation is available, state that — do not invent one.]