cohort-analysis
GitHub用于构建结构化群体分析框架，涵盖留存曲线、LTV估算、行为细分及流失预警。通过确认输入定义群体，生成保留矩阵与SQL参考，输出可直接向产品团队展示的分析报告及优先干预建议。
Trigger Scenarios
Install
npx skills add mohitagw15856/pm-claude-skills --skill cohort-analysis -g -y
SKILL.md
Frontmatter
{
"name": "cohort-analysis",
"description": "Structure a cohort analysis for retention, LTV, or behavioural patterns. Use when asked to run a cohort analysis, analyse retention by cohort, segment users by behaviour over time, or calculate lifetime value by acquisition period. Produces a complete cohort analysis framework with methodology, cohort definitions, retention curves, and prioritised interventions."
}
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.
| Input | What to ask if missing |
|---|---|
| Analysis goal | Retention improvement / LTV modelling / behavioural segmentation / churn prediction — pick one primary goal |
| Product or feature | What is being analysed? |
| Cohort definition | What groups users into a cohort? (acquisition month, signup channel, plan tier, feature adoption date) |
| Observation window | How many periods to track? (e.g. 12 months, 8 weeks) |
| Key metric | What is measured per cohort? (retention rate, revenue, engagement score, feature usage) |
| Available data | Paste schema, table names, or describe what metrics exist — do not assume |
| Baseline or goal | Any existing retention benchmarks or targets to compare against? |
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:
- Confirm inputs — surface any ambiguities before producing output (e.g. conflicting cohort definitions, missing observation window).
- Define cohorts — establish entry event, exit criteria, and exclusions so cohorts are mutually exclusive.
- Build retention table — populate or template the period-by-period matrix; identify the plateau period.
- Project LTV — use observed retention data only; flag if data is insufficient.
- Segment by behaviour — create mutually exclusive behavioural segments; identify the activation threshold.
- Surface leading churn signals — list observable signals with lead time and intervention mapping.
- Compare cohorts over time — assess whether product changes are showing up in newer cohorts.
- Prioritise recommendations — tie every recommendation to a specific cohort or segment finding.
- Provide SQL reference — adapt the template query to the user's actual schema if supplied.
- 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 | Period | Size | Description |
|---|---|---|---|
| [Cohort 1] | [Jan 2025] | [N users] | [e.g. Users who signed up in Jan 2025 via organic search] |
| [Cohort 2] | [Feb 2025] | [N users] | [Description] |
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.
| Cohort | P0 | P1 | P2 | P3 | P6 | P12 |
|---|---|---|---|---|---|---|
| [Jan 2025] | 100% | [X%] | [X%] | [X%] | [X%] | [X%] |
| [Feb 2025] | 100% | [X%] | [X%] | [X%] | [X%] | [X%] |
| [Trend vs prior cohort] | — | [↑/↓ X pp] | [↑/↓ X pp] | [↑/↓ X pp] | [↑/↓ X pp] | [↑/↓ X pp] |
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]
| Period | Retained % | Revenue per retained user | Cumulative LTV |
|---|---|---|---|
| Month 1 | [X%] | [£X] | [£X] |
| Month 3 | [X%] | [£X] | [£X] |
| Month 6 | [X%] | [£X] | [£X] |
| Month 12 | [X%] | [£X] | [£X] |
Blended LTV at 12M: [£X — specify which cohorts and weighting method]
LTV by segment:
| Segment | LTV (12M) | vs Blended baseline | Key driver of difference |
|---|---|---|---|
| [Organic] | [£X] | [+X%] | [e.g. Higher P6 retention] |
| [Paid] | [£X] | [-X%] | [e.g. Lower activation rate] |
| [Enterprise] | [£X] | [+X%] | [e.g. Higher ARPU per period] |
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.
| Segment | Definition | % of cohort | Retention (P6) | LTV (12M) |
|---|---|---|---|---|
| Power users | [e.g. Completed core action ≥ 3×/week in first 30 days] | [X%] | [X%] | [£X] |
| Casual users | [e.g. Completed core action 1–2×/week in first 30 days] | [X%] | [X%] | [£X] |
| Dormant | [e.g. Logged in but never completed core action] | [X%] | [X%] | [£X] |
| Never activated | [e.g. Signed up but never completed onboarding step 1] | [X%] | [X%] | [£X] |
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.
| Signal | Lead time before churn | Correlation strength | Recommended intervention |
|---|---|---|---|
| [e.g. No login for 7 consecutive days] | [7 days] | [Strong / Moderate / Weak] | [e.g. Automated re-engagement email at day 7] |
| [e.g. Support ticket with unresolved escalation] | [~14 days] | [Moderate] | [e.g. CSM outreach within 48 hours of escalation] |
| [e.g. Core feature usage dropped >50% week-on-week] | [~10 days] | [Strong] | [e.g. In-app prompt linking to use-case tutorial] |
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.
| Metric | [Oldest cohort] | [Newest cohort] | Change | Notes |
|---|---|---|---|---|
| P1 retention | [X%] | [X%] | [↑/↓ X pp] | |
| P3 retention | [X%] | [X%] | [↑/↓ X pp] | |
| Activation rate | [X%] | [X%] | [↑/↓ X pp] | |
| Avg. sessions, first 30 days | [X] | [X] | [↑/↓] |
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.
| # | Recommendation | Anchored to finding | Target segment | Expected impact | Effort | Priority |
|---|---|---|---|---|---|---|
| 1 | [Specific action] | [Section X, finding Y] | [Segment] | [e.g. +X pp P1 retention — basis for estimate] | [Low / Med / High] | P1 |
| 2 | [Specific action] | [Section X, finding Y] | [Segment] | [e.g. +X pp P3 retention] | [Low / Med / High] | P1 |
| 3 | [Specific action] | [Section X, finding Y] | [Segment] | [e.g. +£X LTV at 12M] | [Low / Med / High] | P2 |
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.
| Dimension | 0 | 5 | 10 |
|---|---|---|---|
| Cohort definition rigor | Boundaries ambiguous; a user could sit in two cohorts | Mutually exclusive but entry event weakly justified | Unambiguous entry event and date boundaries, with the definition's tradeoffs stated |
| Plateau & window honesty | Retention read off a window too short to support it | Plateau claimed without showing where | Plateau visible and located, or the window explicitly declared too short to confirm one |
| LTV grounding | LTV from assumed retention or assumed ARPU | Observed data used but projection method unstated | LTV built from observed retention × observed ARPU with the projection method and decay assumption shown |
| Trend & leading-indicator payoff | Cohorts described, nothing compared | Cohort-over-cohort trend shown without indicators | Trend across acquisition periods read correctly, plus behavioural leading indicators of churn tied to detection |
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.
| Anti-pattern | Why it fails | Correct approach |
|---|---|---|
| Overlapping cohort membership | Retention numbers across cohorts cannot be compared | Define a single, unambiguous entry event; one user, one cohort |
| Assumed ARPU in LTV projections | Hides segment differences; produces a number that sounds precise but isn't | Use observed revenue per retained user per period, broken out by segment |
| Drawing conclusions from undersized cohorts | Random variation masquerades as signal | Flag minimum cohort size; exclude or caveat cohorts below threshold |
| Conflating login with retention | A user who logs in but does not complete the key event is not retained by definition | Retention = completion of the defined key retention event, not a session |
| Fabricating lift estimates | Projected impact numbers without a data basis mislead prioritisation decisions | If impact cannot be estimated from data, say so and recommend a test |
| Generic recommendations | Advice that could apply to any SaaS product adds no analytical value | Every recommendation must reference a specific cohort, segment, or signal finding |
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
Version History
-
54fad50
Current 2026-07-19 13:21
新增工作流程步骤（如确认输入、SQL参考）、输出模板增加数据源和日期字段；从列表式输入改为表格展示，并强化了对缺失数据的占位符处理要求。
- a38bc30 2026-07-05 11:31
