Lesson 3.3: Cohorts, funnels and segmentation for product and credit teams
Learn the three analyses behind most product and credit decisions: funnels that show drop-off, cohorts that show retention, and RFM segments.
By the end of this lesson you will be able to measure where people drop out of a process with a funnel, track how groups behave over time with a cohort table, group customers into useful segments with RFM scoring, and turn retention into a lifetime value. You will build each analysis by hand from small numbers, see why an overall average can hide an improving or worsening trend, and learn the SQL pattern behind each. You should have read Lesson 3.2, since the funnel and cohort queries use the grouping and window ideas from it, and Lesson 3.1, for the habit of tying every analysis to a decision. The mathematics is multiplication and division.
Dashboards fill up with totals and averages: users, revenue, loans disbursed. They answer "how much?" and rarely answer the questions a business needs to act on. Why did growth slow? Which step of the sign-up is losing people? Are customers acquired this year better than last year's? Are there groups that deserve different treatment? Three analyses, each simple, answer most of these. They share one idea, which is to compare like with like: steps with steps, groups born in the same month with each other, and similar customers with similar customers.
Funnels: where do people drop out?
A funnel follows a process that has ordered steps and counts how many people reach each one. Most digital and lending journeys are funnels: visit, sign up, verify, apply, get approved, receive money. The funnel shows the conversion rate between steps, the share who continue, and so points at the step losing the most.
Here is an onboarding journey for a lending app, with illustrative numbers. Of 10,000 visitors, 3,200 sign up, which is 32 per cent. Of those, 2,240 start identity verification, 70 per cent. Of those, 1,568 complete it, 70 per cent. Of those, 784 submit a loan application, 50 per cent. Of those, 392 are approved and receive the loan, another 50 per cent.
The overall conversion is the product of the step conversions: 0.32 × 0.70 × 0.70 × 0.50 × 0.50 = 0.0392, or 3.92 per cent. That multiplication is why a funnel is so sensitive. Improve any one step by a given proportion and the end result improves by exactly the same proportion, because the steps are multiplied together. This gives a clean way to compare ideas: what would a ten-point gain at each step be worth?
Predict first: if you could raise only one step by 10 percentage points, which would be worth more, the verification completion step from 70 to 80 per cent or the application step from 50 to 60 per cent? Decide before reading on.
Raising verification completion from 70 to 80 per cent is a relative gain of 80 ÷ 70 = 14.3 per cent, so the final number rises from 392 to 448. Raising the application step from 50 to 60 per cent is a relative gain of 60 ÷ 50 = 20 per cent, taking the final number to 470. A ten-point gain is worth more at the step where the base rate is lower. The calculation is not the whole story, since it ignores how hard each improvement is, but it tells you where effort has the most leverage.
Reading a funnel carefully
Funnels mislead in a few ways. First, steps must be in a true order and measured on the same people. If some users skip a step, you need to define the funnel for the path that matters. Second, a funnel is a snapshot over a period, so people in the middle may still complete later. The time between steps matters: a verification that is completed three days later counts as a drop-off if you stop counting after one day. Third, segment the funnel. An overall conversion of 3.92 per cent might be 6 per cent for salaried applicants and 1.5 per cent for the self-employed, and the problem and the fix would differ.
In SQL a funnel is a count of distinct users at each step, usually built from an events table.
sql
SELECT
COUNT(DISTINCT CASE WHEN event = 'visit' THEN userid END) AS visited,
COUNT(DISTINCT CASE WHEN event = 'signup' THEN userid END) AS signedup,
COUNT(DISTINCT CASE WHEN event = 'kycdone' THEN userid END) AS kycdone,
COUNT(DISTINCT CASE WHEN event = 'disbursed' THEN userid END) AS disbursed
FROM events
WHERE eventdate BETWEEN '2026-09-01' AND '2026-09-30';
Counting distinct users, not events, matters, because a person may trigger the same event many times.
Cohorts: do customers stick?
A cohort is a group of people who share a starting point, such as the month they signed up or took their first loan. A cohort analysis follows each group over the same number of months since it started and compares them. It answers the question an overall average cannot: are recently acquired customers better or worse than earlier ones?
Take three monthly cohorts of app users, and count how many are still active in each month after joining. Month 0 is the joining month, so retention starts at 100 per cent.
Cohort · Users · Month 0 · Month 1 · Month 2 · Month 3January · 1,000 · 100% (1,000) · 52% (520) · 38% (380) · 31% (310)
February · 1,200 · 100% (1,200) · 55% (660) · 41% (492) · 34% (408)
March · 1,100 · 100% (1,100) · 58% (638) · 43% (473) · 36% (396)
Reading across a row shows how one cohort decays. Reading down a column compares cohorts at the same age, and that is where the story appears: at every age, newer cohorts retain better. Month-1 retention rose from 52 to 55 to 58 per cent. Something improved, perhaps a better onboarding or a better acquisition channel.
Now see what an overall average would show. If you simply took all active users and divided by all users ever signed up, you would mix cohorts of different ages. A fast-growing business has many young cohorts, which have not yet had time to leave, and the blended figure looks better or worse depending on how fast you are growing, not on how good the product is. Cohorts remove that distortion by comparing at equal age. This is the central point of the method.
The same idea applies in credit. A vintage analysis follows loans disbursed in the same month and tracks the share that go 30, 60 or 90 days past due at each month on book. If the 2026 vintages default faster than the 2025 ones at the same age, underwriting has weakened, even if the portfolio's overall default rate looks fine because most loans are still young. Lenders watch vintage curves for exactly this reason.
A vintage example from lending
A lender disburses personal loans each month and wants to know whether its newer loans are riskier. For three vintages, the share of loans that were 90 or more days past due six months after disbursal was 2.1 per cent for the January vintage, 2.6 per cent for February and 3.4 per cent for March. Each vintage is measured at the same age, six months on book, so the comparison is fair.
Now imagine the lender looks only at the whole portfolio, which is mostly made of loans disbursed in earlier years that have survived to maturity and been repaid. Its overall 90-day delinquency might read a calm 1.9 per cent, and nothing would look wrong. The deterioration is visible only when loans are grouped by vintage and compared at equal age. By the time it showed up in the portfolio average, the lender would already have written many more weak loans. This is why credit teams review vintage curves every month, and why the cohort habit from this lesson is more valuable in risk than in almost any other field.
The cohort query below is written for PostgreSQL. Date functions differ between databases, so in MySQL, SQL Server or SQLite the date arithmetic needs a different spelling, while the structure stays the same.
sql
WITH firstmonth AS (
SELECT userid, DATETRUNC('month', MIN(eventdate)) AS cohort
FROM events
GROUP BY userid
)
SELECT f.cohort,
(DATEPART('year', e.eventdate) - DATEPART('year', f.cohort)) 12
+ DATEPART('month', e.eventdate) - DATEPART('month', f.cohort) AS monthssince,
COUNT(DISTINCT e.userid) AS activeusers
FROM events e
JOIN firstmonth f ON f.userid = e.userid
GROUP BY f.cohort, monthssince
ORDER BY f.cohort, monthssince;
The first CTE finds each user's cohort, and the main query counts distinct active users by cohort and age. Divide each count by the cohort's month-0 size to get the retention table.
From retention to lifetime value
Retention connects directly to money. If a customer stays each month with a probability r, the expected number of months they remain is 1 ÷ (1 − r). With monthly retention of 80 per cent, the expected lifetime is 1 ÷ 0.2 = 5 months. If each active customer brings ₹120 of revenue a month with a 40 per cent margin, the monthly profit is ₹48, and the lifetime value is 48 × 5 = ₹240. A small gain in retention has a large effect: at 90 per cent monthly retention the lifetime doubles to 10 months and the lifetime value to ₹480.
The curve rises steeply at the top. Going from 90 to 95 per cent monthly retention doubles the value again. This is why subscription and lending businesses invest in keeping customers, and why a customer acquisition cost is only meaningful when set against a lifetime value.
Segmentation: not all customers are alike
A segment is a group of customers who are similar in some way that matters, and that you would treat differently. Treating everyone the same wastes money on people who would act anyway, and misses people who need something else. Segments can be built by simple rules, by business knowledge, or by statistical clustering, which Lesson 5.4 covers. The best known rule-based method for customers who buy is RFM.
RFM scores each customer on three things.
Recency: how recently did they last buy or transact?
Frequency: how often did they transact in the period?
Monetary value: how much did they spend?
Each is scored from 1 to 5, using thresholds or quintiles, where 5 is best. For recency, a lower number of days is better, so 30 days or fewer scores 5, up to 60 scores 4, up to 90 scores 3, up to 180 scores 2 and beyond that scores 1. For frequency, 12 or more transactions in a year scores 5, 8 to 11 scores 4, 4 to 7 scores 3, 2 or 3 scores 2 and 1 scores 1. For spend, ₹50,000 or more scores 5, ₹30,000 to ₹49,000 scores 4, ₹15,000 to ₹29,000 scores 3, ₹5,000 to ₹14,000 scores 2 and below that scores 1.
Customer · Days since last · Transactions in a year · Spend (Rs thousand) · R · F · M · SegmentA · 12 · 15 · 62 · 5 · 5 · 5 · Champion
B · 45 · 9 · 38 · 4 · 4 · 4 · Loyal
C · 200 · 14 · 55 · 1 · 5 · 5 · Cannot lose: big spender gone quiet
D · 20 · 1 · 3 · 5 · 1 · 1 · New customer
E · 75 · 5 · 18 · 3 · 3 · 3 · Average
F · 300 · 2 · 4 · 1 · 2 · 1 · Lost
The value of the scores lies in the actions they suggest. Champion A deserves recognition, not discounts, which would cost money to persuade someone who is already buying. Customer C is the urgent one: a high spender who has gone silent, the group where a personal call or an offer has the highest payoff. Customer D is new and may become loyal, so the second purchase is the target. Customer F may not be worth much effort. The same logic maps to credit: a borrower's recency of payment, how often they borrow and the amount borrowed suggest who to offer a top-up, who to watch and who to leave alone.
· Funnel · Cohort · SegmentationQuestion · Where do people drop out of a process? · Do groups that started together keep behaving well over time? · Which customers are alike and need different treatment?
Compares · Step with step · Same age across start dates · Group with group
Output · Conversion by step · Retention or default by age · Labelled groups with actions
Typical decision · Fix the weakest step · Judge whether acquisition or underwriting improved · Target offers and service
In pandas, RFM scoring takes a few lines.
python
import pandas as pd
snapshot = df["date"].max()
rfm = df.groupby("customerid").agg(
recency=("date", lambda d: (snapshot - d.max()).days),
frequency=("txnid", "count"),
monetary=("amount", "sum"),
)
rfm["R"] = pd.qcut(rfm["recency"], 5, labels=[5, 4, 3, 2, 1]) # recent = 5
rfm["F"] = pd.qcut(rfm["frequency"].rank(method="first"), 5, labels=[1, 2, 3, 4, 5])
rfm["M"] = pd.qcut(rfm["monetary"], 5, labels=[1, 2, 3, 4, 5])
The qcut function splits customers into five equal-sized groups, called quintiles, so each score contains a fifth of the customers. Rank before cutting when many customers tie on a value, as they often do on frequency.
Using the three together
The analyses combine. A funnel tells you that verification is the weak step. A segmented funnel shows it is weak for self-employed applicants. A cohort view shows whether a change you made last month helped the cohort that came after it. An RFM view tells you which of the customers who completed the funnel are worth a second offer. Many business questions fit in this chain: find the leak, find who it affects, check whether a fix worked, then decide who to treat differently.
Keep one discipline throughout, which is to look at the numbers behind the rates. A cohort of 40 people gives a month-1 retention of 52 per cent that could easily be 40 or 65. Use the margin of error from Lesson 2.3 when cohorts are small, and avoid ranking segments on noise.
Common mistakes
Mixing cohorts of different ages. Compare at equal age, or a fast-growing business will look healthier than it is.
Counting events instead of people. A funnel counts distinct users, not clicks.
Ignoring time between steps. Cut the window at a sensible point and say which one.
Reading an overall funnel when groups differ. Segment it by channel, product and customer type.
Treating retention as constant. Early months usually lose most people. A lifetime value from a flat rate is a rough guide.
Segments no one acts on. If a segment does not suggest a different action, it adds nothing.
Overreading small cohorts or segments. Check the counts, and add an interval.
Scoring recency in the wrong direction. Fewer days since the last purchase is better, so it earns the higher score.
Key takeaways
A funnel counts people at each ordered step. The overall conversion is the product of the step conversions, so a relative gain at any step lifts the result equally.
A cohort follows people who started together and compares them at equal age, which prevents growth from masking trends. Vintage analysis does the same for loans.
Expected lifetime from a constant retention rate r is 1 ÷ (1 − r), and lifetime value is margin per period times that. Retention gains near the top are very valuable.
RFM scores recency, frequency and monetary value from 1 to 5, and each pattern suggests an action.
Always segment funnels and cohorts, count distinct people, and show the numbers behind each rate.
Choose the analysis by the question: funnels for drop-off, cohorts for behaviour over time, segments for different treatment.
Check yourself
1. A funnel has step conversions of 40, 50 and 20 per cent from 5,000 visitors. How many reach the end?
5,000 × 0.40 × 0.50 × 0.20 = 200.
2. Why compare cohorts at the same age and not simply report overall retention?
Different cohorts have different ages, and young cohorts have had less time to leave, so an overall figure depends on how fast you are growing.
3. Monthly retention is 75 per cent, monthly profit per customer is ₹60. What is the expected lifetime value?
The lifetime is 1 ÷ 0.25 = 4 months, so the value is 60 × 4 = ₹240.
4. A customer last bought 250 days ago, but bought 15 times in the year and spent ₹70,000. Which RFM pattern is that, and what action suits it?
Low recency, high frequency and monetary: a valuable customer gone quiet. A personal reach-out or tailored offer fits.
5. A cohort has 30 users and 50 per cent are retained at month 1. Why be careful?
The margin of error is about ±18 points, so the true rate could plausibly be 32 to 68 per cent.
Try this
Use any dataset of user or customer events, such as an e-commerce log or a loan history, or build a small table by hand. Set aside forty-five minutes.
1. Define a funnel of four or five steps, count the distinct people at each, and compute the step and overall conversions. Identify the biggest drop.
2. Split the funnel by one attribute, such as channel or customer type, and see whether the weak step is the same for each group.
3. Use the funnel simulator to test which single-step improvement is worth the most, and write one sentence on how hard each would be.
4. Build a cohort table by month of first activity with at least three cohorts and three months of age. Compare cohorts at the same age. Plot the curves.
5. Compute RFM scores for the customers. Name the segments you find and write one action for each.
6. Use the retention simulator to compute a lifetime value, and say what retention improvement would justify a new feature.
7. Write a half-page note: where the leak is, who it affects, whether things are improving and who deserves different treatment.
A good result is a note that names one step, one group and one action, with counts behind every rate. The next lesson adds time as the main character: how to read a series of numbers over time and make a sensible forecast from it.
Tags: Data Science Cohort analysis Funnel analysis Segmentation RFM