Why Your Cohorts Look Clean Until They’re Not
A lot of teams build cohort tables in spreadsheets and call it analysis. They export raw events, pivot them by signup month, and stare at decreasing numbers until something looks wrong. Usually it’s nothing dramatic—just a reporting lag, a misaligned date field, or a single integration that started sending events with tomorrow’s timestamp. The table looks fine from a distance. The insight you pull from it is garbage. It’s a way to track behavior over time for groups of users who share a common starting point, usually the month they first engaged with your product. You’re not measuring overall retention, which mixes new and old users and hides patterns. You’re looking at each group separately so you can see whether retention improves, stalls, or decays as those users age. The confusion usually starts with the word “retention.” People assume it means “still active.” It doesn’t. Retention in a cohort context simply means the user performed a defined action after their first interaction. That action might be logging in, completing a purchase, running a workflow, or using any metric that matches your business model. If you define retention as “opened the app,” your numbers will look very different from a team that defines it as “completed a core task.” Pick the metric that predicts value, not the one that’s easiest to track.
How I Build the Analysis Step by Step
I don’t start with a dashboard. I start with a clean query and a simple table. Dashboards hide problems. Queries expose them. Choose one primary event that represents meaningful engagement for your product. For a subscription tool, that might be “completed setup.” For an e-commerce app, it might be “first purchase.” Don’t use login as your retention metric unless login is your product. Login is a proxy, and proxies accumulate noise. Your cohort date should be a fixed starting point. Most teams use first signup, first purchase, or first campaign attribution. If you have multiple entry points, pick one and stick with it. Mixing first visit with first purchase in the same cohort definition creates overlapping groups that look healthy and mean nothing.
Step 2: Write a base query that returns users, cohort month, and retention status per period
Here’s a straightforward PostgreSQL example: SELECT
DATE_TRUNC('month', first_event.at) AS cohort_month,
u.user_id,
MAX(CASE WHEN EXTRACT(EPOCH FROM (e.at - first_event.at)) / 2592000 BETWEEN 0 AND 1 THEN 1 ELSE 0 END) AS retention_day_30,
MAX(CASE WHEN EXTRACT(EPOCH FROM (e.at - first_event.at)) / 2592000 BETWEEN 30 AND 60 THEN 1 ELSE 0 END) AS retention_day_60,
MAX(CASE WHEN EXTRACT(EPOCH FROM (e.at - first_event.at)) / 2592000 BETWEEN 90 AND 120 THEN 1 ELSE 0 END) AS retention_day_90
FROM users u
JOIN events e ON u.id = e.user_id
JOIN (
SELECT user_id, MIN(at) AS at
FROM events
GROUP BY user_id
LIMIT 100000
) first_event ON u.id = first_event.user_id
GROUP BY 1, 2; This gives you one row per user with flags for retention at 30, 60, and 90 days. Adjust the day windows to match your product cycle. B2B SaaS usually needs 60 and 90. Mobile apps often lean on 7 and 30. E-commerce benefits from 30 and 60 because purchase cycles vary.
Get the Full Details
Step 3: Pivot into a cohort table
Take that result and pivot it so each row is a cohort month and each column is a period. Calculate retention rate as active users divided by cohort size. Keep the raw counts alongside the percentages. Percentages lie quietly. Raw counts tell you whether a dip is real or just a small sample. Aggregate retention across all users hides the people who matter. Break cohorts into segments by acquisition channel, plan type, region, device, or onboarding path. One segment often drives the average. Another segment hides a leak you can actually fix. Pick one cohort, pull the raw events for five random users, and trace their activity against your retention flags. If the math doesn’t match the timeline, your query or date logic is drifting. Fix it before you trust the table.
Cohort analysis is vulnerable to three quiet failures. Lag: If your analytics pipeline is delayed, recent periods look artificially low. A cohort from last week might appear to have zero retention because events haven’t arrived yet. Always hold back the most recent 1 to 3 periods or tag them as incomplete. I learned this the hard way after I told leadership a product update dropped retention by 14 percent. It didn’t. The pipeline just hadn’t caught up for that cohort’s second window. Leakage: This happens when users get counted in the wrong cohort because their first event isn’t your true first interaction. A test user, an internal account, or a re-attributed install can shift a whole month’s baseline. Filter out test accounts and verify first-event attribution before you build cohorts.
Definition drift: Teams change what “retained” means between quarters without updating older data. You’ll end up comparing apples to spreadsheets. Lock the definition. If you must change it, rerun historical cohorts with the new definition or keep both datasets separate and label them clearly.
A Practical Workaround I Use When the Data Misbehaves
Once, our retention table showed a mysterious 8 percent drop in a single cohort. The numbers were internally consistent, but the narrative made no sense. I spent two days chasing dashboards, funnel reports, and support tickets. Nothing aligned. The issue was a mobile SDK version that started sending a duplicate session_start event on app open. The first clean event set the cohort date correctly, but the second pushed the user’s “first activity” forward by a day in our event stream. That shifted a chunk of users into the next cohort bucket for the 7-day window, making the original cohort look worse. The fix wasn’t fancy. I added a deduplication step that kept only the earliest event per user per calendar day for cohort assignment, then ran the retention flags against the raw, undeduplicated events. I also added a small diagnostic column that flagged cohorts with unusually high first-day event counts. That column caught the next SDK bump before ited the table again. The process went from a two-day investigation to a ten-minute check.
Common Mistakes That Make Cohort Analysis Useless
Mistake 1: Using the wrong retention metric. Tracking logins when your product delivers value through completed workflows produces beautiful retention curves that don’t predict revenue. Match the metric to the value event, not the easiest event to measure. Mistake 2: Ignoring cohort size. A 2 percent drop in a cohort of 300 users is noise. A 2 percent drop in a cohort of 12,000 users is a signal. Always show N alongside percentages. Mistake 3: Treating retention as linear. Retention rarely declines in a straight line. It often plateaus, drops after a key feature release, or jumps after an onboarding change. Fit simple curves only if you understand the product milestones that drove the shape.
Mistake 4: Comparing cohorts without controlling for acquisition quality. A paid-search cohort and an organic cohort will have different base retention even if your product experience is identical. Segment by channel, not just by month. Mistake 5: Building a dashboard and never validating it. Dashboards are convenient. They also normalize bad assumptions. Re-run the base query monthly and compare totals. If they drift, something in your pipeline or schema changed.
What This Method Can’t Do
Cohort analysis is descriptive, not predictive. It tells you what happened to groups over time. It does not tell you why a specific user churned, which intervention will work next, or whether a recent product change caused the change you’re seeing. Causality requires experimentation, not observation. It also struggles with sparse data. Early cohorts with few users produce unstable rates. Late periods in recent cohorts are unreliable because retention windows haven’t finished. Treat both ends of the table with caution. If you need predictive signals, combine cohort insights with models like next-best-action recommendations, churn scoring, or survival analysis. Those approaches use the same data but add timing and feature weighting. Cohorts are still useful for grounding those models in real behavior.
Downloadable Resources
I keep a small toolkit in a public GitHub repo. It includes a PostgreSQL query template, a Python script that pivots the output into a cohort table, and a lightweight validation check that compares raw event counts against cohort totals. You can find it at: https://github.com/kevinparr/crm-cohort-analysis-toolkit The repo also contains a spreadsheet template for teams that prefer Excel. It automates the pivot and highlights incomplete periods.
Quick Reference: How to Keep This From Becoming Another Forgotten Report
Define one retention event and lock it. Record cohort size with every percentage. Hold back the most recent 1 to 3 periods or mark them incomplete. Segment by at least one meaningful dimension. Validate with a manual spot-check once a month. When the numbers surprise you, check the pipeline before you blame the product. Do that and the analysis stops being a chart you archive and starts being a tool you use every quarter.
