Cohort Analysis & Unit Economics

I'm uploading a CSV of customer data with columns: ${columns}

Build a monthly cohort retention analysis:

1. **COHORT ASSIGNMENT**
Group customers by their signup month (the cohort).

2. **RETENTION TABLE**
Create a triangular retention table:
| Cohort | Month 0 | Month 1 | Month 2 | Month 3 | ... | Month ${months} |
| Jan 2025 | 100% | 88% | 82% | ... | |
| Feb 2025 | 100% | 85% | ... | | |

Month 0 = 100% (by definition)
Each subsequent month = (customers still active) / (customers in cohort)

3. **RETENTION CURVE**
Calculate the average retention rate at each month across all cohorts.
Identify: is retention improving (newer cohorts retain better) or degrading?

4. **STABILIZATION POINT**
At what month does retention stabilize? (Where the MoM drop becomes <1%)

5. **OUTPUT**
- Retention table as a formatted CSV or Excel file
- Summary statistics: Month 1 retention, Month 3 retention, Month 6 retention, Month 12 retention
- Is retention improving over time? Compare first-half cohorts vs second-half cohorts.

A customer is "active" if they have any activity/payment after their signup month. Define churn as: ${churnDefinition}

Using the same customer data, build separate cohort retention tables for each acquisition source.

SOURCES TO SEGMENT: ${sources}

For each source, show:
1. **Cohort retention table** (same format as above, but filtered by source)
2. **Average retention at Month 1, 3, 6, 12**
3. **Cohort size** (how many customers in each cohort from this source)

Then build a **COMPARISON TABLE**:
| Source | Avg Month 1 Ret | Avg Month 3 Ret | Avg Month 6 Ret | Avg Month 12 Ret | Avg Cohort Size |

Sort by Month 12 retention, best to worst.

Finally, write a 2-paragraph analysis:
- Which source produces the most durable customers?
- Which source has volume but poor retention (red flag)?
- Are there any sources where retention is improving/degrading over time?

This directly informs CAC budget allocation — spending more on channels with better retention is almost always the right move.

I'm uploading two data sets:
1. Customer data (same as before): signup dates, revenue, retention, acquisition source
2. Marketing spend data: ${spendColumns}

Calculate unit economics for each acquisition source:

**CAC (Customer Acquisition Cost)**
- Total spend per source per month / new customers acquired from that source
- Show monthly CAC and trailing 3-month average (to smooth noise)

**LTV (Lifetime Value)**
- Method 1: Historical — average revenue per customer × average customer lifespan (in months)
- Method 2: Predictive — ARPU / monthly churn rate (for each source separately)
- Show both methods and note which is more reliable given our data history

**LTV:CAC RATIO**
- For each source
- Flag any source with LTV:CAC below 3.0

**PAYBACK PERIOD**
- CAC / monthly ARPU = months to recover acquisition cost
- For each source

**SUMMARY TABLE**
| Source | CAC | ARPU | LTV (Historical) | LTV (Predictive) | LTV:CAC | Payback (months) | Verdict |

Verdict column:
- "Scale" if LTV:CAC > 4 and payback < 12 months
- "Maintain" if LTV:CAC 3-4 and payback < 18 months
- "Optimize" if LTV:CAC 2-3 (need to improve retention or reduce CAC)
- "Cut" if LTV:CAC < 2

Write a 3-paragraph investment recommendation: where to increase spend, where to hold, and where to cut.

Build a revenue cohort analysis from our customer data:

1. **REVENUE RETENTION TABLE**
Same triangular format as customer retention, but tracking revenue instead of customer count:
| Cohort | Month 0 Revenue | Month 1 | Month 2 | ... |

Month 0 = 100% (starting MRR for that cohort)
Month N = (total MRR from surviving customers in that cohort) / (original cohort MRR)

If Month N > 100%, we have net expansion (surviving customers are paying more).

2. **NET REVENUE RETENTION (NRR)**
Calculate NRR for each cohort and overall:
NRR = (Starting MRR - Churned MRR - Contraction MRR + Expansion MRR) / Starting MRR

3. **EXPANSION VS CONTRACTION**
Break down the components:
| Cohort | Starting MRR | Expansion | Contraction | Churn | Ending MRR | NRR |

4. **ANALYSIS**
- Is NRR above or below 100%?
- Is it improving with newer cohorts?
- Where is expansion coming from — upgrades, seat additions, or usage-based pricing?
- Is contraction (downgrades) a significant factor?

NRR above 120% means the business grows even without new customers. Below 90% means you're on a treadmill.

Build a Python script that processes our customer data and generates a complete cohort analysis:

INPUT:
- Customer CSV: ${customerColumns}
- Marketing spend CSV: month, source, spend_amount

OUTPUT — an HTML dashboard with:

1. **CUSTOMER RETENTION HEATMAP**
Triangular retention table with color coding:
- Dark green: >90% retention
- Light green: 80-90%
- Yellow: 70-80%
- Orange: 60-70%
- Red: <60%

2. **RETENTION BY SOURCE** (one heatmap per source)

3. **UNIT ECONOMICS TABLE**
CAC, LTV, LTV:CAC, Payback by source with traffic-light indicators

4. **TREND CHARTS**
- Average retention curves by source (line chart)
- CAC trend by source over time (line chart)
- LTV:CAC ratio trend (line chart with 3.0x reference line)

5. **REVENUE COHORT TABLE**
Same heatmap format but tracking revenue retention / NRR

Use pandas for data processing and matplotlib for charts (embedded as base64 PNGs in the HTML). Make the HTML self-contained — no external dependencies.

The script should be re-runnable monthly with updated data files.