Who this is for
This lesson is for Marketing Analysts who need to compare retention across acquisition channels (e.g., Paid Social, Search, Email) to improve channel mix, optimize onboarding, and tie cohorts to LTV.
Prerequisites
- Know basic cohort analysis (cohort = users grouped by signup/first purchase date).
- Understand acquisition channels (first-touch vs last-touch).
- Comfort with spreadsheets or SQL pivots.
Why this matters
- Budget allocation: Shift spend to channels that retain better, not just cheaper CAC.
- Onboarding: Diagnose where activation breaks by channel.
- Revenue: Higher retention usually drives higher LTV, improving payback.
- Stakeholder clarity: Clear, comparable metrics reduce debates about which channel is “best.”
Concept explained simply
Retention by acquisition channel compares what percent of users from each channel come back and stay active after signup. You pick a time grain (days, weeks, months), define what “active” means (session, purchase, feature use), and measure the share of the original cohort that returns at time t. You then compare curves or specific periods (e.g., Week 4) across channels.
Mental model
Think of channels as different doors into your product. Some doors attract people who are curious but leave quickly; others bring in motivated users who stick. Retention curves show how steeply users drop off after entering each door. The flattest curve usually wins long-term value.
Data you need
- User ID
- Signup/first purchase date (to form cohorts)
- Acquisition channel (canonical channel grouping)
- Activity events with timestamps (to decide if a user is active at t)
Helpful definitions
- Retention at t = active_users_in_cohort_at_t / users_in_cohort_at_0
- Active user: at least one qualifying event in the period (define this clearly)
- Time grain: weeks or months since signup (not calendar weeks/months)
- Channel: first-touch channel unless specified otherwise
Step-by-step workflow
- Define active. Example: a user is retained in week t if they had at least one session or purchase in that week.
- Choose time grain. Weeks since signup are common for apps; months for subscriptions.
- Map channels. Normalize sources to a canonical list (e.g., Paid Social, Organic Search, Paid Search, Email, Referral, Direct).
- Prepare a tall table. user_id, signup_date, channel, event_date (or aggregated user-week table).
- Create cohorts. cohort_week = week(signup_date); week_since_signup = datediff(week, signup_date, event_date).
- Build the matrix. For each channel and cohort, compute users at t and retained users at each t. Pivot to % rates.
- Compare curves. Plot or scan Week 1, 4, 8 retention by channel.
- Interpret. Investigate steep early drops (onboarding), late drops (habit/fit), channel-message mismatch.
- Decide actions. Reallocate spend, adjust creatives/keywords, tweak onboarding for weak channels.
Worked examples
Example 1 — Weekly retention by channel
Suppose Week 0 cohort sizes: Paid Social = 400, Organic Search = 300, Email = 200. Week 4 active users: Paid Social = 80, Organic Search = 180, Email = 90.
- W4 retention: Paid Social 80/400 = 20%
- Organic Search 180/300 = 60%
- Email 90/200 = 45%
Insight: Organic Search users stick best by W4; increase SEO efforts or direct spend to similar intent keywords.
Example 2 — Comparing early vs late retention
W1 retention: Paid Social 45%, Email 55%. W8 retention: Paid Social 12%, Email 38%.
Interpretation: Paid Social has decent activation but poor long-term fit; Email nurtures better long-term engagement. Consider targeted onboarding sequences for Paid Social, and scale Email lists.
Example 3 — Retention to LTV link
Assume average revenue per retained user at W8 is $15. Channels: Paid Search W8 retention 30% with CAC $12; Organic Search W8 retention 45% with CAC ≈ $0; Paid Social W8 retention 20% with CAC $8.
- Expected value proxy: W8 retention × $15
- Paid Search: $4.50 vs CAC $12 (not good)
- Organic: $6.75 vs CAC $0 (great)
- Paid Social: $3.00 vs CAC $8 (weak)
Action: Reduce Paid Search bids or refine keywords; scale Organic; iterate creatives and targeting for Paid Social.
Practical projects
- Spreadsheet dashboard: Build a cohort-by-channel retention matrix with conditional formatting and 8-week trend spark-lines.
- SQL notebook: Create a query that outputs retention by channel and week_since_signup for the last 6 months of cohorts.
- Channel mapping: Draft a canonical channel mapping and apply it to your UTM/source data.
- Onboarding test: Pick the lowest-retaining channel and design a first-session experiment to improve W1 retention.
Common mistakes and self-check
- Mixing calendar weeks with weeks since signup. Self-check: week_since_signup should start at 0 at signup.
- Switching attribution model mid-analysis (first-touch vs last-touch). Self-check: document and stick to one model for comparability.
- Counting re-acquired or churned-and-reacquired users as continuously retained. Self-check: define retention strictly by activity per cohort.
- Including internal/test users. Self-check: filter them out.
- Undefined “active” event. Self-check: write the exact rule in your chart title/notes.
- Too-small samples per channel. Self-check: flag cohorts with n < meaningful threshold (e.g., 100) before drawing conclusions.
Quick QA checklist
- Clear definition of active and time grain on the chart.
- Cohorts formed by signup date, not calendar week.
- Attribution model stated (first-touch or last-touch).
- Channel mapping applied consistently.
- Sample sizes checked; small cohorts annotated.
- Baseline cohort sizes and retention denominators verified.
Exercises
Complete the exercise below, then open the solution to compare your work.
Task: Using the data below, compute Week 4 retention by channel and write one insight.
Dataset (copy to your sheet)
user_id,channel,signup_date,ws0_active,ws1_active,ws2_active,ws3_active,ws4_active U1,Paid Social,2025-01-03,1,1,0,0,0 U2,Paid Social,2025-01-05,1,1,1,1,1 U3,Paid Social,2025-01-06,1,0,0,0,0 U4,Paid Social,2025-01-07,1,1,0,0,0 U5,Organic Search,2025-01-04,1,1,1,0,1 U6,Organic Search,2025-01-04,1,1,0,0,0 U7,Organic Search,2025-01-05,1,1,1,1,1 U8,Email,2025-01-03,1,1,1,0,1 U9,Email,2025-01-05,1,0,0,0,0
- Step 1: For each channel, count users (denominator).
- Step 2: Count users with ws4_active = 1 (numerator).
- Step 3: Retention_W4 = numerator / denominator.
- Step 4: Write one practical action you’d take.
Ready to check? Open the solution in the exercise card below.
Mini challenge
You have three channels with W1/W4 retention and CAC:
- Paid Social: W1 50%, W4 22%, CAC $9
- Paid Search: W1 40%, W4 28%, CAC $14
- Organic Search: W1 55%, W4 45%, CAC ~$0
If average revenue per retained user at W4 is $12, where do you invest and what do you test next?
See one way to reason it out
- W4 value proxy: Paid Social $2.64; Paid Search $3.36; Organic $5.40
- Scale Organic content/SEO; test better targeting/onboarding for Paid Social; refine Paid Search keywords and landing pages for higher intent.
Learning path
- Before: Cohort basics; Activation metrics.
- Now: Retention by acquisition channel.
- Next: Tie retention to LTV and CAC payback; Segment by campaign/creative and new vs returning buyers.
Next steps
- Build a re-usable retention-by-channel template with inputs for time grain and active definition.
- Schedule monthly reviews to re-balance channel budgets based on retention and LTV.
- Add annotations to charts (major campaign changes, onboarding releases) to explain curve shifts.
Quick test is available to everyone. To save your progress and resume later, log in to your account.