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

7. Recommendations

Every recommendation must reference a specific cohort, segment, or signal from sections above. Generic advice that could apply to any product must be cut.

If expected impact cannot be estimated from available data, say so — do not fabricate a percentage lift.

8. SQL Reference

Adapt this template to the user's actual schema if supplied. Replace placeholder table and column names — do not ship a query the user cannot run.

-- Retention cohort query
-- Replace: users, events, created_at, event_date, user_id, event_type, [start_date], [key_retention_event]
SELECT
  DATE_TRUNC('month', u.created_at)    AS cohort_month,
  DATE_TRUNC('month', e.event_date)    AS activity_month,
  DATEDIFF('month', u.created_at, e.event_date) AS period,
  COUNT(DISTINCT e.user_id)            AS retained_users,
  COUNT(DISTINCT c.user_id)            AS cohort_size,
  ROUND(
    COUNT(DISTINCT e.user_id) * 100.0
    / NULLIF(COUNT(DISTINCT c.user_id), 0), 1
  )                                    AS retention_rate
FROM users u
JOIN (
  SELECT user_id, DATE_TRUNC('month', created_at) AS cohort_month
  FROM users
  WHERE created_at >= '[start_date]'
) c ON u.user_id = c.user_id
   AND DATE_TRUNC('month', u.created_at) = c.cohort_month
JOIN events e
  ON u.user_id = e.user_id
 AND e.event_type = '[key_retention_event]'
GROUP BY 1, 2, 3
ORDER BY 1, 3;

Adapt for your stack: BigQuery uses DATE_DIFF; Redshift uses DATEDIFF; Snowflake uses DATEDIFF or TIMESTAMPDIFF. Confirm dialect before running.

Scoring Rubric (0–40)

Score any output of this skill before handing it over; 32+ is ship-quality.

Quality Checks

Run all checks before delivering output. Do not mark a check as passed unless it is verifiably true given the supplied data.

• Mutual exclusivity: No user can appear in two cohorts — entry event and date boundaries are unambiguous
• Retention plateau: The curve shows a visible plateau, or the analysis explicitly states the window is too short to confirm one
• LTV grounding: LTV projections use observed retention and observed ARPU — not assumed figures
• Behavioural segments: Mutually exclusive and collectively exhaustive — every user in scope falls into exactly one segment
• Minimum cohort size: Any cohort below the reliability threshold is flagged and excluded from trend conclusions
• Churn signals: All leading indicators listed are observable in production data, or explicitly marked as hypotheses
• Recommendations anchored: Every recommendation references a specific finding — no free-floating growth advice
• SQL adapted: Query uses the user's actual table/column names if schema was provided

Anti-Patterns

Avoid these failure modes — they produce analysis that looks rigorous but cannot be trusted or acted upon.

Trigger Phrases

This skill activates on phrases including:

• "Run a cohort analysis for [product]"
• "Analyse retention by acquisition month"
• "What's the LTV of users from [channel] vs [channel]?"
• "Build a cohort retention model from