Customer Cohort Analysis Builder

Prompt

Build a customer cohort retention analysis in Excel using the uploaded customer data.

Data available: [DESCRIBE: CUSTOMER IDs, SIGN-UP DATES, ACTIVITY/PURCHASE DATES]
Business context: [SAAS / E-COMMERCE / SUBSCRIPTION / OTHER]
Key question: [WHAT RETENTION QUESTION ARE YOU TRYING TO ANSWER]
Cohort definition: [MONTHLY / QUARTERLY COHORTS]

**COHORT ANALYSIS OUTPUT**

1. **Cohort Table** — Classic retention matrix showing:
   - Rows: cohort month (sign-up month)
   - Columns: period 0, 1, 2, 3... (months since sign-up)
   - Values: % of original cohort still active
   - Color gradient: darker = higher retention

2. **Retention Curves Chart** — Overlapping line charts for each cohort showing how retention decays over time.

3. **Cohort Comparison** — Are newer cohorts retaining better or worse than older ones?

4. **Key Metrics**
   - Average retention at 1 month, 3 months, 6 months, 12 months
   - Customer lifetime estimate based on retention curve
   - LTV estimate if [AVERAGE REVENUE PER USER] is [AMOUNT]

5. **Excel Build Instructions**
   - How to structure the raw data for cohort analysis
   - COUNTIFS formula for building the cohort matrix
   - Conditional formatting for the heat map
   - Chart setup for retention curves

Identify which cohort had the best long-term retention and hypothesize why.