cohort-analysis

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)