Who this is for
- Marketing Analysts who need to measure acquisition and early conversion.
- Growth/Performance Marketers validating channel quality beyond top-of-funnel metrics.
- Data-minded PMs and analysts building dashboards for new-user performance.
Prerequisites
- Comfort writing SELECTs with GROUP BY and WHERE.
- Basic knowledge of date functions (DATE_TRUNC, intervals).
- Familiarity with your company’s signup and purchase tables.
Why this matters
Cohort tables let you compare users who joined in the same time window and see how quickly they convert or stay active. Real tasks you will do:
- Track how many signups from a given month purchase within 7/30 days.
- Compare early revenue from different channels.
- Measure weekly retention for each cohort to detect lifecycle trends and campaign quality.
- Report whether new acquisition changes actually improved early outcomes.
Concept explained simply
An acquisition cohort groups users by when they first arrived (e.g., signup month). You then follow that group over time (days/weeks) and summarize outcomes like buyers or revenue.
Mental model
Think of a grid: rows are cohorts (e.g., 2025-03 signups). Columns are time since signup (Day 0, 7, 30; Week 1..8). Each cell shows a metric for that cohort at that time offset, such as % users who made at least one purchase.
Common cohort metrics you’ll compute
- Users in cohort
- Buyers within 7 or 30 days
- Revenue within 7 or 30 days
- Weekly active/buying retention (Week 0–8)
Data you’ll need
You can build acquisition cohorts with two core tables:
- users(user_id, signup_date, channel)
- orders(order_id, user_id, order_date, revenue)
Assumptions about dates and uniqueness
- signup_date is the user’s first qualifying acquisition moment.
- order_date is the timestamp of each purchase; one row per order.
- Use left joins from users so users without orders are kept.
Worked examples
Example 1 — Monthly cohorts with 30‑day outcomes
Goal: For each signup month, show users_in_cohort, d0_orders, d30_buyers, d30_revenue.
-- Postgres-style SQL (adapt date functions to your engine if needed)
WITH base AS (
SELECT
u.user_id,
u.signup_date::date AS signup_date,
date_trunc('month', u.signup_date)::date AS cohort_month
FROM users u
WHERE u.signup_date >= (date_trunc('month', current_date) - interval '12 months')
), joined AS (
SELECT
b.cohort_month,
b.user_id,
b.signup_date,
o.order_date,
o.revenue
FROM base b
LEFT JOIN orders o
ON o.user_id = b.user_id
AND o.order_date::date <= b.signup_date + interval '30 days'
)
SELECT
cohort_month,
COUNT(DISTINCT user_id) AS users_in_cohort,
COUNT(*) FILTER (WHERE order_date::date = signup_date) AS d0_orders,
COUNT(DISTINCT user_id) FILTER (WHERE order_date IS NOT NULL) AS d30_buyers,
COALESCE(SUM(revenue), 0) AS d30_revenue
FROM joined
GROUP BY 1
ORDER BY 1;
Why this works
- LEFT JOIN keeps users who never order.
- d30_buyers uses COUNT(DISTINCT user_id) to avoid double-counting multiple orders from the same buyer.
- Revenue sums over all orders in the window.
Example 2 — Add channel breakdown
Compare acquisition quality by channel for the same 30‑day window.
WITH base AS (
SELECT
u.user_id,
u.channel,
u.signup_date::date AS signup_date,
date_trunc('month', u.signup_date)::date AS cohort_month
FROM users u
WHERE u.signup_date >= (date_trunc('month', current_date) - interval '6 months')
), joined AS (
SELECT
b.cohort_month,
b.channel,
b.user_id,
b.signup_date,
o.order_date,
o.revenue
FROM base b
LEFT JOIN orders o
ON o.user_id = b.user_id
AND o.order_date::date <= b.signup_date + interval '30 days'
)
SELECT
cohort_month,
channel,
COUNT(DISTINCT user_id) AS users_in_cohort,
COUNT(DISTINCT user_id) FILTER (WHERE order_date IS NOT NULL) AS d30_buyers,
ROUND(100.0 * COUNT(DISTINCT user_id) FILTER (WHERE order_date IS NOT NULL)
/ NULLIF(COUNT(DISTINCT user_id),0), 1) AS d30_buyer_rate_pct,
COALESCE(SUM(revenue), 0) AS d30_revenue
FROM joined
GROUP BY 1,2
ORDER BY 1,2;
Tip: Sort channels by cohort quality
Use ORDER BY d30_buyer_rate_pct DESC to see which channels bring higher-quality users fastest.
Example 3 — Weekly retention matrix (orders as activity)
Show, for each signup week, what share of the cohort placed an order in weeks 0–7 since signup.
WITH base AS (
SELECT
u.user_id,
u.signup_date::date AS signup_date,
date_trunc('week', u.signup_date)::date AS cohort_week
FROM users u
WHERE u.signup_date >= current_date - interval '12 weeks'
), cohort_sizes AS (
SELECT cohort_week, COUNT(DISTINCT user_id) AS cohort_users
FROM base
GROUP BY 1
), events AS (
SELECT
b.cohort_week,
b.user_id,
FLOOR( (o.order_date::date - b.signup_date) / 7 )::int AS week_n
FROM base b
JOIN orders o
ON o.user_id = b.user_id
WHERE o.order_date::date >= b.signup_date
AND o.order_date::date < b.signup_date + interval '56 days' -- 8 weeks
), agg AS (
SELECT
cohort_week,
COUNT(DISTINCT CASE WHEN week_n = 0 THEN user_id END) AS wk0_users,
COUNT(DISTINCT CASE WHEN week_n = 1 THEN user_id END) AS wk1_users,
COUNT(DISTINCT CASE WHEN week_n = 2 THEN user_id END) AS wk2_users,
COUNT(DISTINCT CASE WHEN week_n = 3 THEN user_id END) AS wk3_users,
COUNT(DISTINCT CASE WHEN week_n = 4 THEN user_id END) AS wk4_users,
COUNT(DISTINCT CASE WHEN week_n = 5 THEN user_id END) AS wk5_users,
COUNT(DISTINCT CASE WHEN week_n = 6 THEN user_id END) AS wk6_users,
COUNT(DISTINCT CASE WHEN week_n = 7 THEN user_id END) AS wk7_users
FROM events
GROUP BY 1
)
SELECT
a.cohort_week,
s.cohort_users,
a.wk0_users,
ROUND(100.0 * a.wk1_users / NULLIF(s.cohort_users,0), 1) AS wk1_retention_pct,
ROUND(100.0 * a.wk2_users / NULLIF(s.cohort_users,0), 1) AS wk2_retention_pct,
ROUND(100.0 * a.wk3_users / NULLIF(s.cohort_users,0), 1) AS wk3_retention_pct,
ROUND(100.0 * a.wk4_users / NULLIF(s.cohort_users,0), 1) AS wk4_retention_pct,
ROUND(100.0 * a.wk5_users / NULLIF(s.cohort_users,0), 1) AS wk5_retention_pct,
ROUND(100.0 * a.wk6_users / NULLIF(s.cohort_users,0), 1) AS wk6_retention_pct,
ROUND(100.0 * a.wk7_users / NULLIF(s.cohort_users,0), 1) AS wk7_retention_pct
FROM agg a
JOIN cohort_sizes s USING (cohort_week)
ORDER BY 1;
Interpretation
Week 1 retention tells you how many users returned to buy in the first full week after signup. Use the same framing consistently across cohorts.
Exercises
Complete these exercises to practice. The Quick Test is available to everyone; only logged-in users will have their progress saved.
Exercise 1 — Monthly acquisition cohort table with 30‑day outcomes
Using users(user_id, signup_date, channel) and orders(order_id, user_id, order_date, revenue):
- Create a table with one row per cohort_month (last 12 months).
- Columns: cohort_month, users_in_cohort, d0_orders, d30_buyers, d30_revenue.
- Use LEFT JOIN so users without orders are included. Avoid double-counting buyers.
Exercise 2 — Weekly retention matrix (first 8 weeks)
Using the same tables and treating an order as “active”:
- Build a retention matrix by signup week for cohorts in the last 12 weeks.
- Show cohort_week, cohort_users, and wk1_retention_pct through wk8_retention_pct.
- Each week’s percentage denominator is the full cohort size.
Exercise checklist
- Did you use date truncation to define cohorts?
- Did you keep users with zero orders (LEFT JOIN)?
- Did you use COUNT(DISTINCT user_id) for buyer counts?
- Are your windows relative to signup date (e.g., +30 days), not calendar months?
- Did you sanity-check totals against raw signups?
Common mistakes and self-check
- Mixing calendar time with relative time windows. Fix: Always compare order_date to signup_date + interval.
- Double-counting buyers with multiple orders. Fix: DISTINCT user_id for buyer counts; sum revenue separately.
- Dropping users with no activity. Fix: LEFT JOIN from users to orders.
- Inconsistent cohort key (e.g., some rows by month, others by week). Fix: Choose one granularity per table.
- Timezone truncation issues. Fix: Normalize to a single timezone before DATE_TRUNC.
Quick self-audit
- Do cohort sizes match signup counts by period?
- Is d30_buyers ≤ users_in_cohort for every row? (It must be.)
- Do retention percentages non-increase across weeks? Large spikes can indicate logic errors.
Practical projects
- Channel quality tracker: Monthly cohorts by channel with d7/d30 buyer rates and revenue per user.
- Experiments pulse: Split cohorts by a campaign flag to estimate lift in d7 conversion.
- Cohort wallboard: Weekly retention matrix for the last 10 cohorts with conditional formatting in your BI tool.
Learning path
- Now: Acquisition cohort basics (this lesson).
- Next: Retention and reactivation cohorts (weekly matrix with any activity).
- Then: LTV by cohort (cumulative revenue by day/week since signup).
- Advanced: Channel-attributed cohorts and incrementality views.
Next steps
- Run Example 1 on your data. Compare d30 buyer rates by channel.
- Build the weekly retention matrix and share insights with your growth team.
- Take the Quick Test below to confirm understanding.
Mini challenge
Extend Example 2 to include d7_buyer_rate_pct and revenue_per_user_30d. Which channel looks best on early revenue per user, and is it the same as the channel with the best d30 buyer rate?