💳
Real-world scenario
Arjun (Hyderabad, fintech): Finding where the loan funnel leaks

Arjun works nights as a data analyst at SouthBank India, a consumer-lending fintech in Hyderabad. His product team is seeing fewer loan disbursements week-on-week, but they don't know where users are dropping out. Are they abandoning after viewing rates? Failing KYC checks? Being rejected by credit? Or is one channel (App, Web, Branch, Agent) hemorrhaging users while others convert?

He has 3,000 application records flowing through a 5-stage funnel: viewed → started → KYC verified → credit approved → disbursed. Tonight he'll run pandas groupby and value_counts to trace each drop, segment by channel, and spot the biggest leak. One code cell can reveal which stage needs urgent fixing.

3,000
Applications viewed
5
Funnel stages
4
Channels (App/Web/Branch/Agent)
What you'll learn
  • Count users at each funnel stage with .value_counts()
  • Preserve stage order using categorical dtype
  • Calculate stage-to-stage conversion rates (not just percentages)
  • Identify the biggest dropoff in absolute and percentage terms
  • Segment funnel by channel to find bottlenecks per channel
  • Analyze drop reasons to recommend product fixes
  • Use .groupby() for multi-dimensional funnel slicing

What is a funnel? The 5-stage loan path

A funnel tracks users through sequential steps. In Arjun's case, users move through loan application stages: they first view rates and terms (stage 1), then submit an application (stage 2), pass KYC checks (stage 3), get credit-decision approval (stage 4), and finally receive disbursement (stage 5). Most drop out at each step — that's normal. Your job is to measure exactly how many reach each stage, calculate the conversion rate, and find where the biggest leak is.

Loan funnel: absolute counts at each stage (viewed → disbursed)
Bar chart showing the 5-stage loan funnel. Viewed: 3,000. Started: 2,100. KYC Verified: 1,470. Credit Approved: 735. Disbursed: 588. Bars shrink as users drop out.
Load the funnel data
What you're looking at: Each row is one application. The column last_stage_reached tells you the furthest stage the user got to (or where they dropped). No separate status code—just the honest truth: did they make it to viewed, started, KYC, approved, or disbursed? This format is perfect for funnel analysis.

Counting users at each stage with .value_counts()

Your first instinct is to count: how many users reached viewed? How many started? KYC? This is where .value_counts() shines. But watch out: the stage order (viewed first, disbursed last) won't sort alphabetically. You need to tell pandas the correct order using a categorical dtype.

Count users at each stage (preserving order)
Why order matters: If you skip the categorical step, .value_counts() sorts alphabetically (credit_approved first, viewed last) — the opposite of your funnel. By defining categories=stage_order, pandas remembers the real sequence. .sort_index() then respects that order.

Stage-to-stage conversion rates: where do users leave?

Counting is half the battle. The other half is conversion: what % of users who started actually completed KYC? What % of KYC-verified got credit approval? These rates reveal which stage has the worst leakage. A 70% conversion is typical for early stages (viewing to apply); a 50% KYC-to-approval suggests stricter credit checks; and an 80% approval-to-disburse is great.

Conversion rate between consecutive stages
Bar chart showing the 4 conversion rates. Viewed→Started: 70%. Started→KYC: 70%. KYC→Approved: 50%. Approved→Disbursed: 80%.
Calculate stage-to-stage conversion rates
Reading the rates: Viewed → Started is 70% — 30% of users bounce before applying. Started → KYC is also 70% — another dropout bulk. KYC → Approved drops to 50% — this is the strict gate (most likely credit checks). Approved → Disbursed is 80% — near the end, most approved users collect their money. The KYC-to-approval step is the biggest bottleneck percentage-wise; Viewed-to-Started leaks the most users in absolute terms (900 people).

Spotting the biggest leak: absolute vs percentage dropoff

Two ways to measure "biggest leak":

  • Absolute: How many users actually dropped out? (Viewed to Started loses 900 users.)
  • Percentage: What % converted? (KYC to Approved only 50% convert.)

Both matter, but for different reasons. Absolute numbers tell you revenue impact (900 lost applications = lost loan origination volume). Percentages tell you stage severity (50% pass-through is a dangerous gate).

Find both types of dropoffs
Action insight: The Viewed→Started step loses 900 people (most in absolute count). But KYC→Approved is the percentage bottleneck at 50%. If Arjun has to pick one fix: simplifying KYC or speeding up the application form, percentage dropoff suggests KYC is the bigger gate. But 900 abandoned applications is money left on the table. Ideally, improve both.

Slicing by channel: which path converts best?

Not all users come the same way. Some apply via the mobile App, others via the Bank's website, visit a Branch, or work with an Agent. Each channel has different friction. App users might bounce due to UI lag. Branch users might have longer wait times. Analyzing funnel by channel reveals which needs improvement.

Disbursement rate by acquisition channel
Bar chart showing disbursement rate for 4 channels. Web: 7.6%. App: 7.3%. Agent: 7.6%. Branch: 7.3%. Web and Agent slightly higher.
Compare funnel by channel
What this reveals: Web and Agent channels hit 7.6% disbursement rate; App and Branch lag at 7.3%. The gap is small (0.3 percentage points) but meaningful at volume. If each channel has ~2,000 applicants, that 0.3% difference is ~6 extra loans disbursed per month. Web and Agent may be smoother (fewer form fields? more support?). Branch might have longer approval cycles or user dropouts due to in-person friction. App might need UX polish to reduce abandonment between stages.

Understanding drop reasons: why do users leave?

Not all dropoffs are equal. Some users abandon because they're not interested (natural). Others fail because the product rejected them (KYC failed, credit low). Understanding drop reasons tells you what to fix: product friction vs. natural market forces.

Count of users by drop reason (excluding completions with 'none')
Bar chart showing drop reasons. KYC Failed: 594. Abandoned: 530. Credit Low: 330. Loan Too Small: 25.
📁
Loan funnel dataset included
3,000 applications across 5 stages; 4 channels; complete with drop reasons.
Count drop reasons
Actionable takeaway: KYC failed is the top reason (594 users). This suggests SouthBank's KYC process is either too strict, confusing, or slow. Simplifying document collection, adding live support, or auto-filling data could recover 594 loans. Abandoned (530) is next — users bail of their own volition, suggesting friction in the flow (too many form fields? unclear next steps?). Credit Low (330) is a gate SouthBank controls through policy; relaxing it for borderline cases could help. Loan Too Small (25) is rare — set-aside issue, not urgent.
🎯
Check your understanding
Q1: Why do we convert the stage column to categorical dtype before counting?
To preserve the funnel order (viewed → started → ... → disbursed) instead of sorting alphabetically.
To make the code run faster.
Categorical dtype is mandatory for .value_counts() to work.
To reduce memory usage.
Q2: What does a "biggest dropoff" mean in funnel analysis — is it absolute count or percentage rate?
Only absolute count matters; percentage is noise.
Both matter: absolute tells you revenue impact; percentage tells you stage severity. You need both perspectives.
Only percentage rate; absolute numbers don't tell you about gate strictness.
Whichever is larger; they're interchangeable.
Q3: Why segment your funnel by channel (App/Web/Branch/Agent) instead of just analyzing the overall funnel?
Because one channel always converts better; the others are useless.
Different channels have different bottlenecks and user behaviors. A fix for the App (UX) won't help Branch (service); you need channel-specific insights.
Segmentation is optional; it doesn't change the overall funnel story.
Because .groupby() is the only way to count users.
0
/3
Great start!

You're ready to move to RFM segmentation and cohort analysis.