Who this is for
Marketing Analysts and growth practitioners who need to understand how user cohorts generate revenue over time, estimate payback, and compare channels or products.
Prerequisites
- Basic spreadsheets (SUMIF, PIVOT) or SQL familiarity
- Comfort with revenue metrics (ARPU, CAC)
- Understanding of acquisition date vs. event date
Why this matters
Real tasks you will do:
- Estimate when paid channels pay back CAC
- Forecast revenue using LTV curves to plan budgets
- Compare cohorts by channel, country, offer, or pricing
- Spot data issues (refund spikes, leakage, misaligned time)
Progress note
The quick test and exercises are available to everyone. Logged-in users will see saved progress and results.
Concept explained simply
A revenue cohort groups users by when they started (for example, sign-up month). For each cohort, track how much revenue those users generate in month 0 (their start month), month 1, month 2, and so on. The LTV curve is the cumulative revenue per user over these months since acquisition.
Key definitions:
- Cohort: Users who started in the same period (e.g., 2025-02).
- Month index: Relative months since a user’s start (0, 1, 2...).
- ARPU per month index: Revenue in that index divided by cohort users.
- LTV(t): Cumulative ARPU from index 0 to t.
- Payback month: The earliest index when LTV(t) ≥ CAC.
Mental model
Imagine each cohort as a snowball rolling downhill. Every month index, it picks up some revenue. The LTV curve shows how big the snowball gets over time. Different channels roll on different slopes: fast upfront (steep early), slow burn (gradual), or plateau (flat tail).
Data you need
- User ID and acquisition date (cohort key)
- Revenue events: event date and amount (net of refunds/taxes if possible)
- Channel/segment labels to compare curves
- CAC per channel (or blended)
Important choices
- Gross vs. net revenue: be consistent.
- Bookings vs. cash collected: match to your business model.
- Deduplicate events and handle refunds/chargebacks.
- Exclude internal/test users.
Step-by-step: Build revenue cohorts and LTV curves
- Define cohorts: Pick acquisition month/week. Assign each user a cohort key such as 2025-02.
- Compute month index: For each revenue event, month_index = months_between(event_date, acquisition_date) truncated to integer.
- Aggregate revenue: Sum revenue by cohort, month_index, and segment (e.g., channel).
- Normalize: Divide each month_index revenue by cohort users to get ARPU by index.
- Accumulate: LTV(t) = cumulative sum of ARPU up to t.
- Compare: Plot curves for segments; look for slope, plateaus, and tails.
- Payback: Find the first t where LTV(t) ≥ CAC.
What if you have partial months?
Use month index based on acquisition date, not calendar months. For recent cohorts, expect truncated tails. Compare like with like (e.g., LTV at month 2 across cohorts that reached month 2).
Discounting (optional)
For long horizons, you may discount future cash flows. Many marketing teams skip this for horizons under 12 months. If you do, apply a monthly discount factor before cumulation.
Worked examples
Example 1: E-commerce cohorts by channel
Suppose five Paid users produce revenue per month index: m0=$80, m1=$60, m2=$20 (total users=5). ARPU by index: m0=16.0, cumulative m1=28.0, cumulative m2=32.0. If CAC(Paid)=$18, payback occurs at month 1 (28 ≥ 18).
Example 2: Subscription monthly plan
100 users; revenue per index: m0=$600, m1=$450, m2=$380. LTV(2)=($600+$450+$380)/100=$14.30. If CAC=$12, payback at month 2; beyond month 2, curve likely continues rising due to retained subscribers.
Example 3: Annual prepay B2B
Annual plan collects most value at m0. Curve: steep jump at m0, then minor upsells later. Interpreting this curve requires aligning CAC to the same cohort and recognizing that early payback can still mask longer-term churn risk at renewal.
Quality checks and interpretation
- Shape: Steep early jump suggests upfront billing or strong early monetization; gentle slope signals delayed monetization or trial-to-paid lag.
- Plateaus: LTV flattening early can indicate product-market fit issues or limited cross-sell.
- Staggered drop-offs: May indicate a lifecycle event (e.g., trial end) or a billing retry policy.
- Segment gaps: Normalize per user before comparing channels. Compare at the same horizon t.
Sanity checks
- Sum of cohort revenue across cohorts equals total revenue from included users and period.
- Month index never negative; missing indexes should be treated as zero revenue.
- Users counted once per cohort; reactivations stay in the original cohort unless you define reactivation cohorts explicitly.
Common mistakes and how to self-check
- Mixing calendar months with months-since-acquisition. Self-check: Pick a user and verify their month 0 aligns with acquisition month.
- Using only payers in the denominator. Self-check: LTV should include all acquired users; use ARPPU separately if needed.
- Ignoring refunds or taxes. Self-check: Compare gross vs. net curves; the net curve should match finance-reconciled numbers.
- Double-counting events. Self-check: Verify unique transaction IDs; totals should match ledger.
- Inconsistent cohort keys across tables. Self-check: Ensure the same acquisition date logic is used everywhere.
- Comparing different horizons. Self-check: Always compare LTV at the same t across cohorts.
Exercises
These mirror the graded exercises below. Do them here first; then submit your answers in the exercise section. Your progress is saved if you are logged in.
Exercise 1: Build monthly revenue cohorts and payback
Use the sample dataset (CSV-like) below. Cohort = signup month. Compute revenue by month index, LTV per user (cumulative ARPU) through month 2, and payback month by channel using CAC: Paid=$18, Social=$12, Organic=$0.
Sample dataset
user_id,signup_date,channel,event_date,revenue u1,2025-01-05,Paid,2025-01-06,20 u1,2025-01-05,Paid,2025-02-02,10 u1,2025-01-05,Paid,2025-03-10,10 u2,2025-01-10,Social,2025-02-15,30 u3,2025-01-12,Paid,2025-01-13,15 u3,2025-01-12,Paid,2025-02-14,15 u4,2025-01-20,Organic,2025-01-21,40 u5,2025-01-25,Social,2025-03-03,20 u6,2025-01-28,Paid,2025-01-29,10 u6,2025-01-28,Paid,2025-03-05,10 u7,2025-02-02,Paid,2025-02-03,20 u7,2025-02-02,Paid,2025-03-04,20 u8,2025-02-05,Organic,2025-03-02,10 u9,2025-02-10,Social,2025-02-11,30 u10,2025-02-18,Paid,2025-02-19,15 u10,2025-02-18,Paid,2025-03-21,15 u11,2025-02-22,Social,2025-03-10,20 u12,2025-02-25,Organic,2025-02-27,5
Expected outputs (summary)
- Paid: LTV(2) ≈ 32.0, payback month = 1
- Social: LTV(2) ≈ 25.0, payback month = 1
- Organic: LTV(2) ≈ 18.33, payback month = 0
Exercise 2: Diagnose curve shapes
Review these LTV curves (cumulative ARPU by month index):
- A: [12, 13, 13.1, 13.1]
- B: [4, 9, 15, 24]
- C: [10, 16, 14, 20]
- D: [0, 0, 6, 12]
Match the likely cause to each:
- 1) Upfront annual billing then flat
- 2) Refunds posted in month 2
- 3) Late conversion after trial
- 4) Strong expansion revenue
Expected mapping
A→1, B→4, C→2, D→3
Practical projects
- Build a cohort dashboard: One pivot for revenue per month index, one for ARPU, and a line chart for LTV curves by channel.
- Payback tracker: Automatically compute the first month index where LTV ≥ CAC for each active channel.
- Retention vs. monetization experiment: Split cohorts by offer (e.g., discount vs. no discount) and compare LTV at month 3 and 6.
Learning path
- Before this: Basic retention and ARPU calculations
- This lesson: Cohort revenue tables, LTV curves, payback
- Next: Segment-level sensitivity, forecasting LTV, and scenario testing
Next steps
- Complete the exercises and check your answers
- Take the quick test to confirm mastery
- Apply to your product data and compare channels at the same horizon
Mini challenge
Your Paid channel LTV curve is [8, 15, 19] with CAC=$14; Social is [6, 11, 18] with CAC=$10. Which channel pays back earlier and which has higher 3-month LTV? Jot down your answer and one action you would take next (e.g., scale, fix leakage, or test pricing).