Learn how to transform a raw CRM stage history export into a complete funnel analysis in pandas — calculating stage conversion rates, identifying the worst drop-off points, measuring time-in-stage velocity, and segmenting results by rep, region, or deal size. This is a production-ready pipeline you can plug into any Salesforce or HubSpot export.

Sales funnel analysis is one of the most high-stakes reports a data analyst can own. When you walk into a sales review and tell the VP that 68% of deals stall at the "Proposal Sent" stage, or that deals closed by the West region move through the funnel twice as fast as the East region, you've moved from reporting history to driving decisions. The problem is that most CRM exports — whether from Salesforce, HubSpot, or Pipedrive — arrive as messy, one-row-per-stage logs full of null dates, inconsistent stage names, and duplicate entries that will silently wreck your conversion math if you don't handle them correctly.
By the end of this lesson, you'll have a complete, production-ready funnel analysis pipeline in pandas. We'll go from a raw CRM export all the way to stage-by-stage conversion rates, drop-off percentages, average time-in-stage, and pipeline velocity metrics — the kind of output that answers "where are deals getting stuck and why?"
What you'll learn:
This lesson assumes you're comfortable with pandas fundamentals — loading CSVs, filtering with boolean masks, and running groupby aggregations. If you need a refresher on any of those, the articles on loading and exploring DataFrames and grouping and aggregating in pandas cover the foundations. You should also be comfortable with pandas date parsing; if that's shaky, Working with Dates and Time Series in pandas is worth a quick read first.
Before writing a single line of pandas, you need to understand the shape of the raw data you're working with. CRM systems typically give you one of two export formats.
Format A — Stage snapshot per deal (wide format): One row per deal, with separate columns for the date each stage was entered. This is clean and easy to read, but most CRM tools don't export it this way.
Format B — Stage event log (long format): One row per stage transition, with columns for opportunity_id, stage_name, entered_at date, and sometimes exited_at. This is what Salesforce's opportunity history object produces, and it's what we'll work with here — it's more realistic and more demanding to analyze correctly.
Here's what a typical export looks like:
opportunity_id | rep_name | region | deal_value | stage_name | entered_at | exited_at
OPP-001 | Sarah Chen | West | 45000 | Prospecting | 2024-01-03 | 2024-01-10
OPP-001 | Sarah Chen | West | 45000 | Qualification | 2024-01-10 | 2024-01-18
OPP-001 | Sarah Chen | West | 45000 | Needs Analysis | 2024-01-18 | 2024-01-25
OPP-001 | Sarah Chen | West | 45000 | Proposal Sent | 2024-01-25 | 2024-02-08
OPP-001 | Sarah Chen | West | 45000 | Negotiation | 2024-02-08 | 2024-02-15
OPP-001 | Sarah Chen | West | 45000 | Closed Won | 2024-02-15 | NaN
OPP-002 | Marcus Webb | East | 120000 | Prospecting | 2024-01-05 | 2024-01-15
OPP-002 | Marcus Webb | East | 120000 | Qualification | 2024-01-15 | NaN
...
Notice the design immediately: exited_at is null for the most recent stage any deal is currently in (or for lost deals that just stopped progressing). OPP-002 has only two rows — it stalled in Qualification. This is your drop-off data, and reading it correctly is the whole challenge.
Let's build our sample dataset programmatically so you can run this locally without needing a CRM login. This dataset is realistic enough to produce meaningful funnel insights.
import pandas as pd
import numpy as np
from datetime import datetime, timedelta
import random
random.seed(42)
np.random.seed(42)
# Define funnel stages in order
FUNNEL_STAGES = [
"Prospecting",
"Qualification",
"Needs Analysis",
"Proposal Sent",
"Negotiation",
"Closed Won"
]
# Terminal stages — deals ending here are "lost" or "won"
TERMINAL_STAGES = {"Closed Won", "Closed Lost"}
reps = [
("Sarah Chen", "West"), ("Marcus Webb", "East"),
("Priya Kapoor", "West"), ("James Okafor", "Central"),
("Leila Russo", "East"), ("Devon Park", "Central")
]
rows = []
opp_id = 1
for _ in range(300): # 300 opportunities
rep_name, region = random.choice(reps)
deal_value = random.choice([
random.randint(5000, 20000),
random.randint(20001, 75000),
random.randint(75001, 250000)
])
# Random start date within 2024
start_date = datetime(2024, 1, 1) + timedelta(days=random.randint(0, 300))
# Deals progress through stages; each stage takes 3-20 days
# Deals drop off with increasing probability at later stages
drop_probs = [0.0, 0.15, 0.20, 0.30, 0.20, 0.0] # prob of dropping AFTER entering this stage
current_date = start_date
last_row_index = None
for i, stage in enumerate(FUNNEL_STAGES):
stage_duration = random.randint(3, 20)
exit_date = current_date + timedelta(days=stage_duration)
# Last stage (Closed Won) has no exit date
if stage == "Closed Won":
rows.append({
"opportunity_id": f"OPP-{opp_id:04d}",
"rep_name": rep_name,
"region": region,
"deal_value": deal_value,
"stage_name": stage,
"entered_at": current_date,
"exited_at": pd.NaT
})
break
# Decide if this deal drops off after this stage
will_drop = random.random() < drop_probs[i]
if will_drop:
# Deal entered this stage but never exited — it stalled here
rows.append({
"opportunity_id": f"OPP-{opp_id:04d}",
"rep_name": rep_name,
"region": region,
"deal_value": deal_value,
"stage_name": stage,
"entered_at": current_date,
"exited_at": pd.NaT
})
break
else:
rows.append({
"opportunity_id": f"OPP-{opp_id:04d}",
"rep_name": rep_name,
"region": region,
"deal_value": deal_value,
"stage_name": stage,
"entered_at": current_date,
"exited_at": exit_date
})
current_date = exit_date
opp_id += 1
df = pd.DataFrame(rows)
df["entered_at"] = pd.to_datetime(df["entered_at"])
df["exited_at"] = pd.to_datetime(df["exited_at"])
print(df.shape)
print(df.head(10).to_string())
print("\nNull exited_at count:", df["exited_at"].isna().sum())
Note
In a real project, you'd load this from a CSV or database query instead. The loading patterns from your first pandas DataFrame apply directly — just replace the DataFrame construction with pd.read_csv("crm_export.csv", parse_dates=["entered_at", "exited_at"]).
Before calculating a single conversion rate, run a data quality pass. CRM data is notoriously dirty — reps manually move deals backward, admins rename stages mid-year, and duplicate records sneak in during migrations.
# ── 1. Basic shape and types ──────────────────────────────────────────────
print("Shape:", df.shape)
print("\nDtypes:\n", df.dtypes)
print("\nNull counts:\n", df.isnull().sum())
# ── 2. Check for duplicate stage entries per opportunity ──────────────────
dupes = df.groupby(["opportunity_id", "stage_name"]).size()
multi_stage_entries = dupes[dupes > 1]
if not multi_stage_entries.empty:
print(f"\nWARNING: {len(multi_stage_entries)} opportunity-stage combos appear more than once")
print(multi_stage_entries.head(10))
else:
print("\nNo duplicate stage entries — good.")
# ── 3. Check for unrecognized stage names ─────────────────────────────────
known_stages = set(FUNNEL_STAGES) | {"Closed Lost"}
unknown_stages = set(df["stage_name"].unique()) - known_stages
if unknown_stages:
print(f"\nUnknown stages found: {unknown_stages}")
print("These rows will be excluded from funnel analysis.")
# ── 4. Date sanity: exited_at should never be before entered_at ───────────
invalid_dates = df[
df["exited_at"].notna() & (df["exited_at"] < df["entered_at"])
]
print(f"\nRows where exited_at < entered_at: {len(invalid_dates)}")
# ── 5. Stage distribution ─────────────────────────────────────────────────
print("\nDeals per stage (entry count):")
print(df["stage_name"].value_counts())
Warning
In real Salesforce exports, you'll frequently encounter deals that were moved backward through the funnel (e.g., from "Negotiation" back to "Proposal Sent"). These show up as duplicate stage entries with different dates. Decide upfront whether to keep the most recent entry, the earliest entry, or flag them for exclusion. Getting this wrong silently inflates your early-stage counts.
For our purposes, if duplicates exist, we'll keep the first time a deal entered each stage — which is the most defensible approach for conversion analysis:
# Keep first entry per opportunity-stage combination
df_clean = (
df.sort_values("entered_at")
.drop_duplicates(subset=["opportunity_id", "stage_name"], keep="first")
.copy()
)
print(f"Rows before dedup: {len(df)}, after: {len(df_clean)}")
The core question of funnel analysis is: "Of the deals that entered Stage N, how many made it to Stage N+1?" To answer this, you need to know the furthest stage each deal ever reached.
# Assign a numeric order to each stage
stage_order = {stage: i for i, stage in enumerate(FUNNEL_STAGES)}
df_clean["stage_order"] = df_clean["stage_name"].map(stage_order)
# For each opportunity, find the highest stage reached
max_stage_per_opp = (
df_clean.groupby("opportunity_id")["stage_order"]
.max()
.reset_index()
.rename(columns={"stage_order": "max_stage_order"})
)
# Map back to stage name
max_stage_per_opp["max_stage_name"] = max_stage_per_opp["max_stage_order"].map(
{v: k for k, v in stage_order.items()}
)
print("Deal distribution by highest stage reached:")
print(
max_stage_per_opp["max_stage_name"]
.value_counts()
.reindex(FUNNEL_STAGES) # enforce funnel order
)
This gives you your raw funnel counts — the number of unique opportunities that ever touched each stage. This is different from the event log row count, which could be inflated by deals that move backward.
Key insight
"Deals that entered this stage" is not the same as "deals that successfully exited this stage." The former includes deals that are currently in-progress at that stage. When you calculate conversion rates, be explicit about whether you're including active deals or only completed ones. For historical analysis, it's usually cleaner to work only with deals that have a final outcome (Won or Lost).
Now we build the funnel table. For each stage, we want:
# Count deals that entered each stage
stage_entry_counts = (
df_clean[df_clean["stage_name"].isin(FUNNEL_STAGES)]
.groupby("stage_name")["opportunity_id"]
.nunique()
.reindex(FUNNEL_STAGES) # enforce order
.reset_index()
.rename(columns={"opportunity_id": "entries"})
)
# Count deals that exited each stage (appeared in the NEXT stage)
# A deal "exited" Stage N if it has a row for Stage N+1
stage_exit_counts = []
for i, stage in enumerate(FUNNEL_STAGES[:-1]): # all stages except last
next_stage = FUNNEL_STAGES[i + 1]
# Deals that entered this stage
entered_this = set(
df_clean[df_clean["stage_name"] == stage]["opportunity_id"]
)
# Of those, which also entered the next stage?
entered_next = set(
df_clean[df_clean["stage_name"] == next_stage]["opportunity_id"]
)
# Conversion = deals that entered BOTH
converted = entered_this & entered_next
stage_exit_counts.append({
"stage_name": stage,
"exits": len(converted)
})
# Last stage (Closed Won) has no "exit" in the same sense
stage_exit_counts.append({
"stage_name": FUNNEL_STAGES[-1],
"exits": np.nan
})
exits_df = pd.DataFrame(stage_exit_counts)
# Merge into one funnel table
funnel = stage_entry_counts.merge(exits_df, on="stage_name")
funnel["conversion_rate"] = funnel["exits"] / funnel["entries"]
funnel["dropoff_rate"] = 1 - funnel["conversion_rate"]
funnel["dropoff_count"] = funnel["entries"] - funnel["exits"]
funnel["pct_of_top"] = funnel["entries"] / funnel["entries"].iloc[0]
print(funnel.to_string(index=False))
The output should look something like this:
stage_name entries exits conversion_rate dropoff_rate dropoff_count pct_of_top
Prospecting 300 255 0.85 0.15 45 1.00
Qualification 255 204 0.80 0.20 51 0.85
Needs Analysis 204 143 0.70 0.30 61 0.68
Proposal Sent 143 114 0.80 0.20 29 0.48
Negotiation 114 95 0.83 0.17 19 0.38
Closed Won 95 NaN NaN NaN NaN 0.32
The pct_of_top column is your classic funnel visualization number — it shows how many deals from the top of the funnel make it to each subsequent stage. Here, only 32% of Prospecting deals eventually close, and your biggest drop-off in absolute terms is at Needs Analysis (61 deals lost).
Conversion rates tell you where deals are dying. Stage duration tells you why — a stage with a healthy conversion rate but a long average time is a process bottleneck, not a qualification problem.
# Calculate duration in days for each stage event that has both dates
df_clean["days_in_stage"] = (
df_clean["exited_at"] - df_clean["entered_at"]
).dt.days
# Only use rows where the deal actually *exited* the stage
# (null exited_at means the deal stalled or is still active there)
completed_stages = df_clean[df_clean["exited_at"].notna()].copy()
# Average, median, and std of time per stage
stage_velocity = (
completed_stages
.groupby("stage_name")["days_in_stage"]
.agg(
avg_days="mean",
median_days="median",
std_days="std",
min_days="min",
max_days="max",
sample_size="count"
)
.reindex(FUNNEL_STAGES)
.round(1)
.reset_index()
)
print(stage_velocity.to_string(index=False))
Tip
Use median instead of mean for time-in-stage whenever deal cycle times are even slightly skewed — and they almost always are. A single enterprise deal that sat in "Proposal Sent" for 180 days will drag your mean up and make your process look slower than it really is for 90% of your deals. Report both, but lead with median.
Now merge the velocity data into your main funnel table:
funnel_full = funnel.merge(
stage_velocity[["stage_name", "avg_days", "median_days", "std_days"]],
on="stage_name",
how="left"
)
print(funnel_full.to_string(index=False))
Beyond per-stage velocity, your stakeholders will want to know: how long does it take from first touch to close?
# Get the entered_at for Prospecting and Closed Won per opportunity
prospecting_dates = (
df_clean[df_clean["stage_name"] == "Prospecting"]
[["opportunity_id", "entered_at"]]
.rename(columns={"entered_at": "prospecting_date"})
)
closed_won_dates = (
df_clean[df_clean["stage_name"] == "Closed Won"]
[["opportunity_id", "entered_at"]]
.rename(columns={"entered_at": "close_date"})
)
cycle_times = prospecting_dates.merge(closed_won_dates, on="opportunity_id")
cycle_times["total_cycle_days"] = (
cycle_times["close_date"] - cycle_times["prospecting_date"]
).dt.days
print("Total deal cycle time (Closed Won deals only):")
print(cycle_times["total_cycle_days"].describe().round(1))
print(f"\nMedian cycle time: {cycle_times['total_cycle_days'].median():.0f} days")
print(f"Mean cycle time: {cycle_times['total_cycle_days'].mean():.0f} days")
A single funnel table is a good start, but the real value comes from segmentation. Let's build a function that computes funnel metrics for any grouping dimension — rep, region, deal size bucket, or time period.
First, let's create a deal size bucket using pd.cut to categorize deal values:
# Get one row per opportunity with deal metadata
opp_meta = (
df_clean[["opportunity_id", "rep_name", "region", "deal_value"]]
.drop_duplicates("opportunity_id")
.copy()
)
# Bucket deal values
opp_meta["deal_size"] = pd.cut(
opp_meta["deal_value"],
bins=[0, 20000, 75000, float("inf")],
labels=["Small (<$20K)", "Mid ($20K-$75K)", "Enterprise (>$75K)"]
)
print(opp_meta["deal_size"].value_counts())
Now build a reusable segmented funnel function:
def build_funnel(df_events: pd.DataFrame, opp_meta: pd.DataFrame,
group_col: str = None) -> pd.DataFrame:
"""
Build stage conversion table, optionally segmented by a grouping column.
Parameters
----------
df_events : stage event log (one row per opportunity-stage)
opp_meta : one row per opportunity with deal metadata
group_col : column in opp_meta to segment by (e.g., 'region', 'deal_size')
Returns
-------
DataFrame with stage-level funnel metrics
"""
# Join metadata onto events
merged = df_events.merge(opp_meta[["opportunity_id", group_col or "region"]],
on="opportunity_id", how="left") if group_col else df_events.copy()
groups = merged[group_col].unique() if group_col else [None]
results = []
for grp in groups:
subset = merged[merged[group_col] == grp] if group_col else merged
for i, stage in enumerate(FUNNEL_STAGES):
entries = subset[subset["stage_name"] == stage]["opportunity_id"].nunique()
if i < len(FUNNEL_STAGES) - 1:
next_stage = FUNNEL_STAGES[i + 1]
entered_this = set(subset[subset["stage_name"] == stage]["opportunity_id"])
entered_next = set(subset[subset["stage_name"] == next_stage]["opportunity_id"])
exits = len(entered_this & entered_next)
conversion_rate = exits / entries if entries > 0 else np.nan
else:
exits = np.nan
conversion_rate = np.nan
row = {
"stage_name": stage,
"entries": entries,
"exits": exits,
"conversion_rate": conversion_rate,
"dropoff_rate": 1 - conversion_rate if not np.isnan(conversion_rate) else np.nan,
}
if group_col:
row[group_col] = grp
results.append(row)
return pd.DataFrame(results)
# Funnel segmented by region
funnel_by_region = build_funnel(df_clean, opp_meta, group_col="region")
print(funnel_by_region.pivot_table(
index="stage_name",
columns="region",
values="conversion_rate"
).reindex(FUNNEL_STAGES).round(2))
Key insight
When you segment by region or rep and see dramatically different conversion rates at the same stage, that's the finding that justifies the whole analysis. If the West region converts "Proposal Sent" → "Negotiation" at 85% while the East region converts it at 55%, something specific is happening in that stage for that team — their proposal templates, their follow-up cadence, their pricing authority. That's the conversation your analysis should start.
Rather than eyeballing the table, let's programmatically rank stages by both their absolute drop-off count and their conversion rate impact, then surface the top problem areas:
# Work from the full funnel table (excluding Closed Won which has no conversion)
funnel_ranked = funnel_full.dropna(subset=["conversion_rate"]).copy()
# Rank by: worst conversion rate (most drops proportionally)
funnel_ranked["conversion_rank"] = funnel_ranked["conversion_rate"].rank(ascending=True)
# Rank by: most absolute deals lost
funnel_ranked["volume_rank"] = funnel_ranked["dropoff_count"].rank(ascending=False)
# Combined priority score (lower = more urgent)
funnel_ranked["priority_score"] = (
funnel_ranked["conversion_rank"] + funnel_ranked["volume_rank"]
) / 2
funnel_ranked_sorted = funnel_ranked.sort_values("priority_score")
print("Stages ranked by urgency (worst first):")
print(
funnel_ranked_sorted[
["stage_name", "entries", "conversion_rate", "dropoff_count", "median_days", "priority_score"]
].to_string(index=False)
)
This surfaces stages where both the percentage lost and the number of deals lost are high — which is exactly where intervention will have the most impact.
Beyond individual deal cycle time, sales leadership often wants to know pipeline throughput: how many deals are moving through the funnel per unit of time, and is that rate accelerating or decelerating?
# Add a quarter column based on when the deal entered Prospecting
prospecting_df = df_clean[df_clean["stage_name"] == "Prospecting"].copy()
prospecting_df["quarter"] = prospecting_df["entered_at"].dt.to_period("Q")
# Count new deals entering the pipeline per quarter
pipeline_inflow = (
prospecting_df.groupby("quarter")["opportunity_id"]
.nunique()
.reset_index()
.rename(columns={"opportunity_id": "new_deals"})
)
# Count deals closed per quarter
closed_df = df_clean[df_clean["stage_name"] == "Closed Won"].copy()
closed_df["quarter"] = closed_df["entered_at"].dt.to_period("Q")
pipeline_outflow = (
closed_df.groupby("quarter")["opportunity_id"]
.nunique()
.reset_index()
.rename(columns={"opportunity_id": "won_deals"})
)
# Combine
pipeline_velocity = pipeline_inflow.merge(pipeline_outflow, on="quarter", how="left")
pipeline_velocity["win_rate"] = (
pipeline_velocity["won_deals"] / pipeline_velocity["new_deals"]
).round(3)
# Quarter-over-quarter change in win rate
pipeline_velocity["win_rate_qoq"] = pipeline_velocity["win_rate"].pct_change().round(3)
print(pipeline_velocity.to_string(index=False))
Note
This quarterly velocity view is intentionally simple — it tracks inflow vs. outflow, not individual deal progression. For a more sophisticated cohort approach (tracking deals that entered in Q1 and seeing how many eventually closed), see the lesson on cohort analysis in pandas, where the same retention logic applies to deal cohorts.
A polished, multi-sheet Excel workbook is the standard deliverable for stakeholder funnel reviews. We'll export three sheets: the full funnel summary, the segmented region breakdowns, and the pipeline velocity table.
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.utils import get_column_letter
output_path = "sales_funnel_report.xlsx"
with pd.ExcelWriter(output_path, engine="openpyxl") as writer:
# Sheet 1: Full funnel summary
funnel_full.to_excel(writer, sheet_name="Funnel Summary", index=False)
# Sheet 2: Region segmentation (pivot)
region_pivot = funnel_by_region.pivot_table(
index="stage_name",
columns="region",
values="conversion_rate"
).reindex(FUNNEL_STAGES).round(3)
region_pivot.to_excel(writer, sheet_name="By Region")
# Sheet 3: Pipeline velocity
pipeline_velocity.to_excel(writer, sheet_name="Pipeline Velocity", index=False)
# Basic formatting on the Summary sheet
ws = writer.sheets["Funnel Summary"]
header_fill = PatternFill(start_color="1F4E79", end_color="1F4E79", fill_type="solid")
header_font = Font(color="FFFFFF", bold=True)
for cell in ws[1]:
cell.fill = header_fill
cell.font = header_font
cell.alignment = Alignment(horizontal="center")
# Auto-width columns
for col_idx, col in enumerate(ws.columns, 1):
max_len = max(
len(str(cell.value)) if cell.value is not None else 0
for cell in col
)
ws.column_dimensions[get_column_letter(col_idx)].width = max_len + 4
print(f"Report saved to {output_path}")
For more advanced Excel formatting options — conditional formatting, data bars on conversion rates, frozen headers — see the guide on automating Excel reports with pandas and openpyxl.
Using the pipeline we built together, complete these three extensions:
Exercise 1: Rep-level funnel comparison
Build a funnel table segmented by rep_name. Identify which rep has the lowest conversion rate at "Proposal Sent" and which has the longest median time in "Needs Analysis." Use the build_funnel() function we wrote — you'll need to modify it slightly to merge the rep name column.
Exercise 2: Closed Won pipeline value analysis
Join the cycle_times DataFrame (which has total cycle days) back to opp_meta (which has deal values). Calculate the average deal value for deals that closed in under 30 days vs. over 60 days. Are faster deals smaller deals, or does speed correlate with size?
Exercise 3: Monthly funnel trend
Adapt the pipeline velocity calculation to run monthly instead of quarterly. Then calculate the 3-month rolling average win rate using .rolling(3).mean(). Are win rates trending up or down over the year?
Mistake: Counting event rows instead of unique deals
The event log has multiple rows per deal. If you do df.groupby("stage_name").size() you're counting rows, not deals. Always use .nunique() on opportunity_id when counting deals per stage.
# Wrong — counts rows, not deals
df.groupby("stage_name").size()
# Right — counts unique deals
df.groupby("stage_name")["opportunity_id"].nunique()
Mistake: Including active (in-progress) deals in conversion math
If a deal is currently sitting in "Proposal Sent" with no exit date, it hasn't converted yet — but it also hasn't dropped off yet. Including it in your denominator artificially deflates your conversion rate. For historical analysis, filter to deals that have reached a terminal state.
# Identify closed opportunities (Won or Lost)
opp_final_stage = df_clean.groupby("opportunity_id")["stage_order"].max().reset_index()
# Deals where max stage is Closed Won (stage_order = 5) are closed
closed_opps = opp_final_stage[opp_final_stage["stage_order"] >= 5]["opportunity_id"]
# Use only closed deals for historical conversion analysis
df_historical = df_clean[df_clean["opportunity_id"].isin(closed_opps)]
Mistake: Forgetting to enforce stage order when reindexing
When you do .value_counts() on stage names, pandas returns them sorted by count, not by funnel position. Always .reindex(FUNNEL_STAGES) after any groupby on stage names to enforce the correct funnel order. A funnel table where "Closed Won" appears above "Prospecting" will confuse everyone.
Mistake: Stage names with inconsistent formatting
CRM admins rename stages, typos creep in, and spaces/capitalization vary. Before any analysis, standardize stage names:
df["stage_name"] = df["stage_name"].str.strip().str.title()
If you have more complex cleanup — like "proposal sent" and "Proposal_Sent" and "PROPOSAL SENT" all appearing — the text cleanup with pandas string methods article covers the full toolkit.
Mistake: Null entered_at dates
If entered_at is null for any row, your stage duration calculations will silently produce NaT results, your groupby aggregations will drop those rows, and your counts will be subtly wrong. Always check upfront:
assert df["entered_at"].notna().all(), \
f"{df['entered_at'].isna().sum()} rows have null entered_at — investigate before proceeding"
You now have a complete funnel analysis pipeline that handles the full journey from raw CRM event log to executive-ready insights: data validation, deduplication, stage conversion rates, drop-off ranking, time-in-stage velocity, deal cycle time, pipeline throughput, and segmented comparisons.
The key patterns to internalize:
entered_this & entered_next) is the correct way to calculate stage-to-stage conversion — it handles deals that skip stages and deals that move backward gracefully.For logical next steps, the cohort analysis in pandas article shows you how to extend this into time-based cohort tracking — grouping deals by the quarter they entered Prospecting and following each cohort through the funnel, which reveals whether your process is improving over time. And if you want to automate this report to run weekly and email to your sales leadership, building and automating recurring reports with pandas walks through scheduling and distribution end-to-end.
The data is in the CRM. The story is in the pipeline you just built.