cohort-analysis

name

cohort-analysis

description

Build a full SaaS cohort analysis from a CRM (Attio/Stripe/CSV) joined to revenue (Stripe/Attio/CSV). Outputs a styled Excel workbook with conditional formatting (Customer Churn, MRR Churn, CAC Payback) plus per-section CSVs. Use when the user says "/cohort-analysis", "build a cohort table", "cohort analysis for <client>", "retention by signup month", or "show me NRR by cohort". One-shot, no warehouse, no cron.

Cohort Analysis

Builds the full SaaS cohort suite from a CRM + money source: 11 sub-tables across 3 sections (Customer Churn, MRR Churn, CAC Payback) in a single Excel workbook with green→red conditional formatting, plus per-section CSVs for SQL/raw consumption.

Step 1: Ask the user 3 questions (5 if they want CAC payback)

Ask in order. Don't skip. Don't pick defaults silently.

1. Where do your clients live? (CRM source)

   • attio, Attio Persons or Companies, created_at is the cohort key
   • stripe, Stripe Customers themselves (no separate CRM)
   • csv, paste a path to a CSV with columns customer_id, email, signup_date

2. Where's the money? (revenue source)

   • stripe, Stripe Invoices (paid)
   • attio, currency attribute on a CRM record (requires extra info, see Step 1b)
   • csv, path to CSV with columns customer_id (or email), event_date, amount

3. Want CAC payback analysis? (optional)

   • yes, they paste a path to a cohort,cac_amount CSV. Section 3 of the workbook will show cumulative gross profit vs CAC and flag the lifetime month each cohort breaks even.
   • no / skip, workbook will only include the first two sections.

4. Gross margin? (only if they said yes to #3)

   • Default is 0.8 (80%). Most SaaS companies are 70-85%.

5. Output format?

   • all (default, xlsx workbook + per-section CSVs)
   • xlsx, workbook only
   • csv, per-section CSVs only
   • sql, also dump SQL DDL+inserts
   • evidence, also bootstrap a DuckDB+Evidence project

Step 1b: Attio money source disclaimer (IMPORTANT)

If they picked Attio for money, STOP and show this verbatim before going further:

⚠️ Heads up, Attio doesn't store revenue history natively. It only holds current attribute values, so we have to reconstruct the timeline from date attributes on each customer record. To do that I need three attribute slugs:

1. Amount per period: the recurring amount (e.g. mrr, arr, subscription_amount, monthly_value)
2. Date paid / first invoice: when they started paying (e.g. date_paid, subscription_start, first_invoice_date)
3. Date churned: optional. When they stopped paying. Leave blank and we'll assume they're still active today.

Each customer becomes one event per month between date-paid and date-churned at the amount you give. This works for steady-state subscriptions but won't capture mid-cycle upgrades, downgrades, partial refunds, or one-off charges. For real revenue accuracy, point me at Stripe instead, or export your billing data to CSV.

What are the three Attio attribute slugs? (paste them as amount=mrr date_paid=date_paid date_churned=date_churned)

Wait for them to give you the 3 slugs (or 2, if they're skipping date_churned).

Step 2: Confirm env vars are set

Source	Env var
Attio (CRM or money)	ATTIO_API_KEY
Stripe (CRM or money)	STRIPE_SECRET_KEY
CSV	none, just a path

Run python skills/cohort-analysis/run.py --check-env --crm <X> --money <Y> to validate. If a key is missing, stop and tell the user to add it.

Step 3: Run

python skills/cohort-analysis/run.py \
  --crm <attio|stripe|csv> \
  --money <stripe|attio|csv> \
  --cacs <path-to-cacs.csv> \           # optional, enables CAC payback section
  --gross-margin 0.8 \                  # default 0.8
  --output <all|xlsx|csv|sql|evidence> \
  --out-dir /tmp/cohort-<client>-<date>

Optional flags:

• --attio-object companies|people (default companies)
• --attio-amount-attr <slug> (required if --money attio)
• --attio-date-paid-attr <slug> (required if --money attio)
• --attio-date-churned-attr <slug> (optional, only if --money attio)
• --csv-customers <path> (only if --crm csv)
• --csv-revenue <path> (only if --money csv)
• --cohort-grain month|quarter (default month)

Step 4: KPIs the user gets

Every run produces these metrics for every cohort × lifetime month:

Customer-side (Section 1 of the workbook, 5 sub-tables)

• Retained customers: count of cohort C still paying at lifetime month M
• Churned customers: count who churned that specific period
• % retained customers: customer retention curve (the "GRR shape")
• % churned vs base: monthly churn rate normalized to cohort starting size
• % churned vs previous month: period-over-period churn rate

Revenue-side (Section 2 of the workbook, 5 sub-tables)

• Retained MRR: dollars each cohort is paying every month since signup
• MRR churn: signed: positive = lost dollars, negative = expansion
• % retained MRR: NRR (Net Revenue Retention), the headline VC metric
• % MRR churn vs base: monthly dollar churn normalized to M0 MRR
• % MRR churn vs previous month: period-over-period dollar churn

Unit economics (Section 3 of the workbook, only if --cacs given)

• Cumulative gross profit per cohort, month by month (retained_mrr × gross_margin, accumulated)
• CAC payback period: first lifetime month each cohort's cumulative GP exceeds its CAC
• "Profitable since M_n" flag, or "Not yet profitable"

Per-cohort base stats (in 00_summary.csv and the workbook headers)

• Cohort base size: # of customers signed up that month
• M0 MRR: initial cohort revenue
• CAC (if provided)
• Profitable since: computed
• Max observable lifetime month: per-cohort cutoff inferred from your data

Step 5: Output files

Default output (--output all) writes to /tmp/cohort-<client>-<date>/:

• cohort_workbook.xlsx: the styled Excel file (open in Excel/Numbers/Sheets). Three stacked sections with conditional formatting: Customer Churn, MRR Churn, CAC Payback (only if CACs given).
• cohort_table.csv, headline retained-MRR matrix (familiar shape, opens directly)
• 00_summary.csv, one row per cohort: base counts, base MRR, CAC, profitable-since
• 01_retained_customers.csv through 11_cac_payback_cumulative_gross_profit.csv, every sub-table as its own CSV
• audit_customers.csv + audit_revenue.csv, everything that fed the join, for traceability

Step 6: After running

Show a 4-line summary:

• N customers, N revenue events, N cohorts
• Path to cohort_workbook.xlsx
• One-line read of the matrix (quick_summary output: e.g. "Jan-2026 cohort: 80 customers @ $7,851 MRR → M9: 64 customers, 81% MRR retained")
• If CACs were given: how many cohorts have paid back, how many haven't.

When to use

• User says /cohort-analysis or "cohort analysis for "
• A client asks for retention/NRR by signup month, gross profit per cohort, or CAC payback
• Quarterly review of a SaaS book of business
• A founder is preparing investor materials and needs to show NRR + payback

When NOT to use

• The client wants a continuously refreshing dashboard → recommend ChartMogul or Baremetrics, don't build this.
• The client has <10 customers per cohort → cohorts aren't statistically useful, just show a churn list instead.

Try it without API keys

The examples/ folder ships with a richer fixture (8 cohorts, ~12 months of history, CAC values). Run end-to-end:

python skills/cohort-analysis/run.py \
  --crm csv --money csv \
  --csv-customers skills/cohort-analysis/examples/customers.csv \
  --csv-revenue skills/cohort-analysis/examples/revenue.csv \
  --cacs skills/cohort-analysis/examples/cacs.csv \
  --gross-margin 0.8 \
  --output all \
  --out-dir /tmp/cohort-demo

open /tmp/cohort-demo/cohort_workbook.xlsx

Notes

• Joins are on email (lowercased) by default. Stripe customers usually carry email; Attio Persons too. For Companies → Stripe, falls back to domain match.
• CSV mode is the universal escape hatch, if the user has an "alternative" billing system (Qonto, Chargebee, custom), they export to CSV and we ingest it.
• No data is sent anywhere. Everything stays local in /tmp/cohort-<client>-<date>/.
• The xlsx writer requires openpyxl. If it's not installed, the skill still produces all the CSVs; the .xlsx is skipped with a friendly message.