Monetization Cohorts

Why this matters

Product Analysts use monetization cohorts to understand how different user groups generate revenue over time. This guides pricing, paywall changes, promotions, and product prioritization.

• Real tasks you will face: quantify payback period after pricing change; compare LTV across acquisition channels; separate GMV from revenue in marketplaces; identify monetization lift from a new feature.
• Outcome: a clear, trustworthy cohort table that shows revenue per cohort by months since start, plus ARPU, LTV, revenue retention, and payback.

Concept explained simply

A monetization cohort groups users by a shared start event (commonly acquisition or first purchase month) and tracks their revenue over subsequent months.

• Cohort key: Acquisition month or First Purchase month.
• Time axis: Months since cohort start (0, 1, 2, ...).
• Metrics per cell: Revenue generated in that month by that cohort.
• Rollups: ARPU, ARPPU, LTV (cumulative ARPU), revenue retention, payback.

Key metrics and quick formulas

• Revenue (per cohort, per month): sum of recognized revenue for that cohort in that month.
• ARPU: Revenue in period / Cohort users.
• ARPPU: Revenue in period / Paying users in period.
• Payer rate: Paying users in period / Cohort users.
• LTV (to month N): Sum of ARPU from month 0 to N.
• Revenue retention (month t): Revenue at month t / Revenue at month 0.
• AOV: Revenue in period / Number of orders.
• Purchase frequency: Orders in period / Active users in period.
• CAC payback: First month t when cumulative ARPU ≥ CAC.
• Marketplace note: GMV × Take rate = Revenue. Do not mix GMV and Revenue in one metric.

Worked examples

How to build a monetization cohort table

1. Choose cohort definition: acquisition month or first purchase month (be consistent).
2. Define revenue: gross vs net (refunds, taxes, discounts). Document the choice.
3. Set time buckets: months since cohort start (0,1,2,...). Avoid calendar months for the columns.
4. Create the matrix: rows = cohorts, columns = months since start, values = revenue.
5. Add normalizations: ARPU per month, cumulative LTV.
6. Add diagnostics: revenue retention vs month 0, payer rate, ARPPU.
7. Add payback: compare cumulative ARPU to CAC.
8. QA: check totals match your source facts, verify no double counting across cohorts.

-- Inputs: events (user_id, event_date, revenue, is_refund), users (user_id, acquisition_date), calendar of months
WITH user_cohort AS (
  SELECT
    u.user_id,
    DATE_TRUNC('month', u.acquisition_date) AS cohort_month
  FROM users u
), revenue_clean AS (
  SELECT
    e.user_id,
    DATE_TRUNC('month', e.event_date) AS rev_month,
    SUM(CASE WHEN e.is_refund THEN -ABS(e.revenue) ELSE e.revenue END) AS revenue
  FROM events e
  GROUP BY 1,2
), joined AS (
  SELECT
    c.cohort_month,
    r.rev_month,
    DATE_PART('month', AGE(r.rev_month, c.cohort_month))::int AS month_number,
    r.revenue
  FROM user_cohort c
  JOIN revenue_clean r USING (user_id)
  WHERE r.rev_month >= c.cohort_month
)
SELECT
  cohort_month,
  month_number,
  SUM(revenue) AS revenue
FROM joined
GROUP BY 1,2
ORDER BY 1,2;

Common mistakes and self-checks

• Mixing cohort keys: using acquisition for some users and first purchase for others. Self-check: confirm a single cohort rule in code or sheet.
• Using calendar months as columns. Fix: use months since cohort start (0,1,2...).
• Mixing GMV and revenue. Fix: choose one; for marketplaces, compute revenue = GMV × take rate after returns.
• Ignoring refunds and taxes. Fix: define gross vs net and stick to it.
• No normalization by cohort size. Fix: compute ARPU and LTV.
• Comparing cohorts at different maturity. Fix: compare up to the minimum common month (e.g., LTV to Month 3).
• Currency mix in multi-country data. Fix: convert to a single currency and state the rate/date if needed.
• Small cohorts overinterpreted. Fix: add minimum cohort size threshold and show confidence bands if available.

Exercises

These exercises mirror the tasks below. Do them in a spreadsheet or notebook.

Exercise 1 (ex1): Two cohorts, ARPU, retention, payback

Data:

Cohort Feb: users=800; CAC per user=$5
Month 0: payers=120, revenue=$3,600
Month 1: payers=70,  revenue=$1,400
Month 2: payers=50,  revenue=$900

Cohort Mar: users=1,200; CAC per user=$3.5
Month 0: payers=150, revenue=$3,000
Month 1: payers=110, revenue=$2,400
Month 2: payers=80,  revenue=$1,200

• Tasks: For each cohort, compute ARPPU by month, ARPU by month, LTV to Month 2, revenue retention (M1 and M2 vs M0), and the earliest payback month.

Practical projects

• Spreadsheet cohort dashboard: Build a cohort matrix with revenue, ARPU, LTV, retention, and conditional formatting highlighting payback month per cohort.
• Pricing change analysis: Compare LTV to Month 3 for cohorts before vs after a price update; hold marketing levels steady during the comparison window.
• Marketplace lens: Start from GMV, subtract returns, apply take rate, and produce a net-revenue cohort table. Show both GMV and revenue on separate tabs to avoid mixing.

Who this is for

• Product Analysts and Data Analysts who need to quantify monetization over time.
• PMs looking for clear LTV and payback signals.
• Growth/Monetization teams evaluating pricing, paywalls, or promos.

Prerequisites

• Basic cohort analysis (grouping by start event, months since start).
• Comfort with spreadsheets or SQL for aggregations.
• Clarity on your revenue definition (gross vs net).

Learning path

1. Refresh cohort fundamentals (cohort key, time axis).
2. Define monetization metrics (ARPU, ARPPU, payer rate, LTV, retention).
3. Build a first monetization cohort table and validate totals.
4. Add CAC and mark payback month.
5. Segment by channel, country, or pricing to find drivers.

Mini challenge

Cohort: 2,000 users; CAC=$4. Month 0 revenue $3,000; Month 1 revenue $1,200; Month 2 revenue $1,000.

• Question: What is LTV to Month 2, revenue retention at Month 2, and payback month?

Next steps

• Implement the monetization cohort table using your data.
• Add CAC by channel and compute channel-specific payback.
• Share a 1-page readout with one insight per cohort (what to scale, what to fix).

Save progress and test yourself

The quick test is available to everyone. If you log in, your progress and test results will be saved.