Cohort Curve Model

SKILL.md

---
name: cohort-curve-model
description: "Fit a retention curve to observed cohort data and project LTV — computed, not estimated. Use when someone has real cohort retention numbers (month 0, 1, 2…) and asks what lifetime value, lifetime periods, or long-run retention they imply, or whether retention is flattening or leaking. Produces a fitted power curve (parameters, R², retention floor), a 24-36 period projection, and a real .xlsx with live formulas where editing ARPU recalculates LTV — via the bundled zero-dependency script."
homepage: https://mohitagw15856.github.io/pm-claude-skills/skill/cohort-curve-model.html
metadata:
  {
    "openclaw": { "emoji": "🧮" }
  }
---

# Cohort Curve Model

Retention data has a shape, and the shape is the business. This skill fits the standard consumer-retention power curve r(t) = a·t^(−b) to observed cohort data by log-log least squares — actual arithmetic run by the bundled script, not model vibes — then projects it forward and prices it.

## Required Inputs

- **Observed retention by period** — from period 0 (100%) through at least period 3-4. Percent or fraction, either works. More periods = a trustworthy fit; 4 is the floor.
- **ARPU per period** (optional) — revenue per *retained* user per period. Without it, LTV is reported in lifetime-period multiples instead of currency.
- **Projection horizon** (optional, default 24 periods).

If the requester has cohort *tables* (rows of cohorts × months), take the average by period-age or fit the most recent complete cohort — say which you did.

## Output Format

1. **The fit** — a (scale), b (decay), R² of the log-log fit, and the observed tail floor. Interpret b plainly: **b < 0.5** = strong flattening, a habit is forming; **0.5–1** = normal decay; **b > 1** = leaky bucket, the curve never accumulates a base.
2. **The projection** — observed vs fitted by period, marked where observation ends and projection begins.
3. **The money** — lifetime periods (Σ fitted retention over the horizon) and LTV = ARPU × lifetime periods.
4. **The caveat that matters most** — if R² < 0.9, say the power family fits poorly and the projection should be distrusted beyond the observed tail.

## Programmatic Helper

This skill ships `scripts/cohort_model.py` — **zero dependencies** (stdlib zip+XML). The math and the workbook both come from the script; run it rather than computing by hand:

```bash
python3 scripts/cohort_model.py fit cohorts.xlsx --observed '[100,62,48,41,37,34,32]' --arpu 40 --horizon 24
```

It prints the fit (`a=0.619 b=0.371 R²=1.000 lifetime≈7.7 periods LTV≈308`) and writes an `.xlsx` with a **Model** sheet (parameters + an editable ARPU cell wired to LTV by a live formula) and a **Curve** sheet (observed vs fitted vs projected). Requires a code-execution environment.

## Quality Checks

- [ ] Period 0 is normalised to 100% and the input had at least 4 periods — otherwise the fit was refused, not fudged
- [ ] R² is reported next to the projection, and a fit below 0.9 carries an explicit "distrust beyond the tail" warning
- [ ] The b-parameter is interpreted in words (flattening / normal / leaky), not left as a naked number
- [ ] LTV states its horizon — "LTV over 24 periods", never an unbounded number
- [ ] The xlsx was actually generated by the script and the ARPU cell recalculates LTV

## Anti-Patterns

- [ ] Do not fit fewer than 4 periods — two points always fit a power law and mean nothing
- [ ] Do not project a poor fit silently — a beautiful curve through bad residuals is how LTV fictions get funded
- [ ] Do not quote LTV without the horizon — "lifetime" hides the assumption that matters
- [ ] Do not average incomplete cohorts into the input (young cohorts drag the tail down mechanically — survivorship in reverse)
- [ ] Do not present the fitted floor as a promise — it is an extrapolation, and the honest phrasing is "if the current shape holds"