Cohort And LTV Analysis

Why this skill matters for a Marketing Analyst

Cohort and LTV analysis helps you answer: Which acquisition sources bring customers who stick around? How long until marketing spend pays back? Where should we scale? You will group users by when or how they were acquired, measure their retention, revenue, and unit economics over time, and guide budget allocation and product/marketing changes.

Who this is for

• Marketing Analysts who need to evaluate channels, campaigns, and budget allocation.
• Growth, CRM, and Performance Marketers validating retention and payback.
• Early-stage founders and product managers building unit economics clarity.

Prerequisites

• Comfort with spreadsheets or SQL joins, aggregations, and date functions.
• Basic understanding of marketing channels, conversions, and revenue.
• Clear definitions for events (sign-up, first purchase) and attribution rules.

Learning path

1. Define cohorts: Choose acquisition month and channel. Confirm event sources (sign-ups, first orders).
2. Retention matrices: Build cohort-period tables for orders or activity.
3. LTV curves: Compute cumulative revenue (or margin) per user over months.
4. Blend vs channel LTV: Compare overall vs by-channel unit economics.
5. CAC:LTV & payback: Combine spend, CAC, and LTV to find payback period.
6. Repeat/repurchase: Measure reorder rates and time between orders.
7. Campaign change comparisons: Compare cohorts before/after major changes.

Worked examples

Example 1: Acquisition cohort and retention matrix (SQL)

Goal: Month 0 = month of first purchase. Show percent of users who purchase again in months 1, 2, 3...

-- Assumptions: PostgreSQL-like syntax
-- tables: orders(user_id, order_date, revenue), users(user_id, acquisition_channel)
WITH first_order AS (
  SELECT user_id,
         DATE_TRUNC('month', MIN(order_date)) AS cohort_month
  FROM orders
  GROUP BY 1
), orders_labeled AS (
  SELECT o.user_id,
         DATE_TRUNC('month', o.order_date) AS order_month,
         fo.cohort_month,
         EXTRACT(month FROM AGE(DATE_TRUNC('month', o.order_date), fo.cohort_month))::int AS month_number
  FROM orders o
  JOIN first_order fo USING (user_id)
), cohort_sizes AS (
  SELECT cohort_month, COUNT(DISTINCT user_id) AS cohort_users
  FROM first_order
  GROUP BY 1
), repeaters AS (
  SELECT cohort_month, month_number,
         COUNT(DISTINCT user_id) AS buyers
  FROM orders_labeled
  GROUP BY 1,2
)
SELECT r.cohort_month, r.month_number,
       ROUND(100.0 * r.buyers / NULLIF(c.cohort_users,0), 2) AS pct_active
FROM repeaters r
JOIN cohort_sizes c USING (cohort_month)
ORDER BY 1,2;

Interpretation: Month 0 often equals ~100% because it includes first purchase. Focus on Month 1-3 retention to judge stickiness.

Example 2: LTV curve by cohort (SQL)

Goal: Cumulative revenue per user over months since first purchase.

WITH first_order AS (
  SELECT user_id,
         DATE_TRUNC('month', MIN(order_date)) AS cohort_month
  FROM orders
  GROUP BY 1
), labeled AS (
  SELECT o.user_id,
         fo.cohort_month,
         EXTRACT(month FROM AGE(DATE_TRUNC('month', o.order_date), fo.cohort_month))::int AS month_number,
         o.revenue
  FROM orders o
  JOIN first_order fo USING (user_id)
), cohort_base AS (
  SELECT cohort_month, COUNT(DISTINCT user_id) AS cohort_users
  FROM first_order
  GROUP BY 1
), revenue_by_period AS (
  SELECT cohort_month, month_number, SUM(revenue) AS rev
  FROM labeled
  GROUP BY 1,2
), cumulative AS (
  SELECT r.cohort_month, r.month_number,
         SUM(r.rev) OVER (PARTITION BY r.cohort_month ORDER BY r.month_number) AS cum_rev
  FROM revenue_by_period r
)
SELECT c.cohort_month, c.month_number,
       ROUND(c.cum_rev / NULLIF(b.cohort_users,0), 2) AS ltv
FROM cumulative c
JOIN cohort_base b USING (cohort_month)
ORDER BY 1,2;

Interpretation: Plot ltv vs month_number. Steeper early growth indicates fast payback potential.

Example 3: Blended vs channel-specific LTV

Goal: Compare blended LTV to channel LTV to find high-quality sources.

-- users(user_id, acquisition_channel), orders(user_id, order_date, revenue)
WITH first_order AS (
  SELECT user_id, DATE_TRUNC('month', MIN(order_date)) AS cohort_month
  FROM orders GROUP BY 1
), labeled AS (
  SELECT u.acquisition_channel,
         fo.cohort_month,
         EXTRACT(month FROM AGE(DATE_TRUNC('month', o.order_date), fo.cohort_month))::int AS m,
         o.revenue
  FROM orders o
  JOIN first_order fo USING (user_id)
  JOIN users u USING (user_id)
), cohort_counts AS (
  SELECT acquisition_channel, cohort_month, COUNT(DISTINCT user_id) AS users
  FROM users u
  JOIN first_order fo USING (user_id)
  GROUP BY 1,2
), rev_by AS (
  SELECT acquisition_channel, cohort_month, m, SUM(revenue) AS rev
  FROM labeled GROUP BY 1,2,3
), cum AS (
  SELECT acquisition_channel, cohort_month, m,
         SUM(rev) OVER (PARTITION BY acquisition_channel, cohort_month ORDER BY m) AS cum_rev
  FROM rev_by
)
SELECT acquisition_channel, m,
       ROUND(AVG(cum_rev / NULLIF(users,0)), 2) AS avg_ltv
FROM cum c
JOIN cohort_counts cc USING (acquisition_channel, cohort_month)
GROUP BY 1,2
ORDER BY 1,2;

Interpretation: Channels with higher LTV at equal or lower CAC are prime to scale.

Example 4: CAC:LTV and payback period

Goal: Combine channel spend and LTV to see if CAC:LTV >= 1:3 and months to payback.

-- spend(channel, month, spend, new_users)
-- From Example 3 you have channel LTV by m (months since cohort start).
-- Compute CAC and payback month for each channel.
WITH cac AS (
  SELECT s.channel,
         DATE_TRUNC('month', s.month) AS cohort_month,
         SUM(s.spend) AS spend,
         SUM(s.new_users) AS users,
         CASE WHEN SUM(s.new_users)=0 THEN NULL ELSE SUM(s.spend)::numeric / SUM(s.new_users) END AS cac
  FROM spend s
  GROUP BY 1,2
), ltv AS (
  -- Pretend we computed channel_cohort LTV by m and wrote it to ltv_curves(channel, cohort_month, m, ltv)
  SELECT channel, cohort_month, m, ltv FROM ltv_curves
)
SELECT l.channel, l.cohort_month, l.m,
       c.cac,
       l.ltv AS ltv_revenue,
       -- If you have gross margin %, adjust: margin_ltv = ltv_revenue * margin
       CASE WHEN l.ltv >= c.cac THEN 1 ELSE 0 END AS paid_back_flag
FROM ltv l
JOIN cac c USING (channel, cohort_month)
ORDER BY 1,2,3;

Interpretation: The smallest m where paid_back_flag flips to 1 is your payback month. Many teams use margin LTV for a stricter view.

Example 5: Repeat purchase and reorder rate

Goal: Measure percent of users with 2+ orders within 30 days of first purchase.

WITH first_order AS (
  SELECT user_id, MIN(order_date) AS first_dt FROM orders GROUP BY 1
), second_order AS (
  SELECT o.user_id, MIN(o.order_date) AS second_dt
  FROM orders o
  JOIN first_order f ON f.user_id = o.user_id AND o.order_date > f.first_dt
  GROUP BY 1
)
SELECT ROUND(100.0 * SUM(CASE WHEN second_dt <= first_dt + INTERVAL '30 days' THEN 1 ELSE 0 END)
                   / COUNT(*) , 2) AS reorder_30_rate_pct
FROM first_order f
LEFT JOIN second_order s USING (user_id);

Interpretation: Track reorder_30_rate_pct by cohort and channel to understand early habit formation.

Drills and exercises

• Create a cohort_month column for first purchase in your dataset.
• Build a 0–6 month retention matrix for purchasers.
• Compute 0–6 month LTV per user by channel; visualize as line charts.
• Join spend to compute CAC and CAC:LTV by channel; flag channels under 2:1.
• Calculate payback month by cohort; highlight those > 6 months.
• Measure reorder within 30, 60, 90 days and compare across channels.
• Run a before/after cohort comparison for a recent pricing or onboarding change.

Common mistakes and debugging tips

• Mixing acquisition and activity cohorts: Ensure Month 0 aligns with first purchase (or your chosen start event).
• Using revenue instead of margin for unit economics: If possible, adjust LTV by gross margin to reflect contribution.
• Attribution drift: Confirm the acquisition channel is fixed at the user level for cohort analyses.
• Time bucketing off-by-one: Use DATE_TRUNC and explicit month differences to avoid partial-month issues.
• Denominator errors: Always divide by cohort user count, not total users across cohorts.
• Survivorship bias: Include inactive users (zero orders) in LTV denominators.

Mini project: Cohort and LTV dashboard

Build a compact dashboard that finance and marketing can use weekly.

1. Data prep: Create tables for cohorts, retention by month, and LTV by month (blended and by channel).
2. Unit economics: Add CAC per channel and compute CAC:LTV and payback month.
3. Visuals: Line chart of LTV curves; heatmap of retention; bar chart of payback by channel.
4. Insights: Add a text box summarizing which channels to scale, pause, or test.

Subskills

• Acquisition Cohort Definition — Choose the start event and grouping logic (e.g., first purchase month and channel).
• Retention By Acquisition Channel — Build month-over-month retention tables segmented by channel.
• Revenue Cohorts And LTV Curves — Compute cumulative revenue/margin per user by cohort.
• Blended Versus Channel LTV — Compare overall LTV to each channel’s LTV to spot quality differences.
• CAC To LTV Ratio — Combine spend and LTV to assess unit economics and scaling readiness.
• Payback Cohorts — Determine how many months each cohort takes to repay CAC.
• Repeat Purchase And Reorder Rates — Track 2nd+ purchase timing and rates by cohort.
• Cohort Comparison After Campaign Changes — Evaluate effect of pricing, onboarding, or creatives on new cohorts.

Next steps

• Automate monthly cohort refresh and sanity checks.
• Add margin and refund adjustments to tighten LTV accuracy.
• A/B test new channels or creatives and compare new cohorts to historical medians.

Note: The exam for this skill is available to everyone. If you log in, your progress and results will be saved automatically.