Customer Lifetime Value Calculator & Cohort Analysis
Analytics & ReportingadvancedClaude 3.5 Sonnet or GPT-4o. Both excel at multi-step financial calculations, creating structured tables, and providing business context. Claude slightly better for complex cohort analysis and strategic recommendations; GPT-4o faster for straightforward calculations.
When to Use This Prompt
Use this prompt when you need to understand the true economic value of your customer base, make data-driven decisions about marketing spend allocation, or identify which customer segments are most profitable. Essential for justifying marketing budgets, setting growth targets, and optimizing channel mix.
The Prompt
You are a data analytics expert specializing in customer lifetime value (LTV) modeling. I need you to calculate and analyze customer lifetime value for my business and provide actionable insights.
## Business Context
- Industry: [YOUR INDUSTRY]
- Average customer acquisition cost (CAC): $[AMOUNT]
- Average order value (AOV): $[AMOUNT]
- Average purchase frequency per year: [NUMBER] times
- Average customer lifespan: [NUMBER] years
- Gross margin percentage: [PERCENTAGE]%
- Monthly churn rate: [PERCENTAGE]%
- Repeat purchase rate: [PERCENTAGE]%
## LTV Calculation Requirements
1. **Calculate three LTV scenarios:**
- Conservative (using lower repeat rates and higher churn)
- Base case (using provided metrics)
- Optimistic (assuming 15% improvement in retention and repeat rates)
2. **Provide cohort analysis:**
- Break down LTV by customer acquisition channel: [LIST YOUR CHANNELS]
- Segment LTV by customer type/segment: [DESCRIBE YOUR SEGMENTS]
- Show LTV trends by acquisition quarter/month if applicable
3. **Calculate key metrics:**
- LTV:CAC ratio and what it means for sustainability
- Payback period (months to recover CAC)
- Customer profitability by segment
- Break-even analysis
4. **Identify optimization opportunities:**
- Which segments have highest/lowest LTV?
- What retention improvements would most impact LTV?
- Which channels deliver highest-value customers?
- Recommended CAC spend adjustments by channel
## Output Format
- Present calculations in clear tables
- Include formulas used for transparency
- Provide a one-page executive summary
- List top 3-5 strategic recommendations
- Highlight any concerning metrics or red flags
- Suggest specific actions to improve LTV by segment
## Additional Context
[PASTE ANY RELEVANT CUSTOMER DATA, TRANSACTION HISTORY, OR ADDITIONAL METRICS]
Tips for Best Results
- 1.Provide actual transaction data or historical cohort data if available—AI will create more accurate projections and identify real patterns in your customer behavior.
- 2.Include seasonal variations or promotional periods that affect purchase frequency, as these significantly impact LTV accuracy and channel comparisons.
- 3.Specify your gross margin carefully; many marketers confuse gross margin with net profit. Use the contribution margin (revenue minus variable costs) for LTV calculations.
- 4.Ask the AI to show formulas and assumptions explicitly so you can audit the math, adjust inputs, and run sensitivity analyses for different business scenarios.
Example Output
# Customer Lifetime Value Analysis
## LTV Calculations
| Scenario | LTV | LTV:CAC Ratio | Payback Period |
|----------|-----|---------------|----------------|
| Conservative | $1,245 | 2.1:1 | 8.2 months |
| Base Case | $1,680 | 2.8:1 | 5.9 months |
| Optimistic | $2,145 | 3.6:1 | 4.2 months |
**Base Case Formula:** ($450 AOV × 3.2 purchases/year × 1.15 gross margin) / (1 - 0.04 monthly churn) - $500 CAC = $1,680
## LTV by Acquisition Channel
- Paid Search: $2,140 (highest value, 3.6:1 ratio)
- Organic: $1,890 (strong retention, 3.2:1 ratio)
- Social Ads: $1,420 (lower repeat rate, 2.4:1 ratio)
- Referral: $2,340 (best performers, 3.9:1 ratio)
## Key Findings
1. Referral customers deliver 65% higher LTV than social ads
2. Payback period of 5.9 months is healthy; can support increased CAC
3. Monthly churn of 4% is the primary LTV constraint
## Top Recommendations
1. Increase referral program investment—shift 15% budget from social ads
2. Implement retention initiatives targeting 2% churn reduction (adds $340 LTV)
3. Develop upsell strategy for organic segment (highest retention, growth opportunity)
4. Audit social ad targeting to improve customer quality and repeat rates