Wicked Smart Data
LearnInsightsAboutContact
Sign InLet's Build
LearnInsightsAboutContact
Sign InLet's Build
Wicked Smart Data

Intelligence, automation, and expert execution — plus an elite library of free knowledge. We turn complexity into competitive advantage.

Start a conversation

Platform

  • Learning Paths
  • Insights
  • RSS Feed

Company

  • About
  • Contact
  • Work With Us

Legal

  • Privacy Policy
  • Terms of Service

© 2026 Wicked Smart Data. All rights reserved.

Intelligence · Automation · Advantage

All Insights
Python

Building a Sales Funnel Analysis in pandas: Calculating Stage Conversion Rates, Drop-Off Points, and Pipeline Velocity from CRM Export Data

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.

⚡ Practitioner19 min readNov 16, 2026Updated Nov 16, 2026
Building a Sales Funnel Analysis in pandas: Calculating Stage Conversion Rates, Drop-Off Points, and Pipeline Velocity from CRM Export Data
On this page
  • Prerequisites
  • Understanding the Data Model: What a CRM Stage History Export Actually Looks Like
  • Loading and Creating the Dataset
  • Step 1: Validating and Profiling the Data Before You Analyze
  • Step 2: Identifying Each Deal's Highest Stage Reached
  • Step 3: Calculating Stage Conversion Rates and Drop-Off
  • Step 4: Calculating Time-in-Stage (Pipeline Velocity)
  • Calculating Total Deal Cycle Time
  • Step 5: Segmented Funnel Analysis
  • Step 6: Finding the Worst Drop-Off Points Automatically
  • Step 7: Pipeline Velocity — Deals per Day
  • Step 8: Exporting the Funnel Report to Excel
  • Hands-On Exercise
  • Common Mistakes & Troubleshooting
  • Summary & Next Steps
  • Building a Sales Funnel Analysis in pandas: Calculating Stage Conversion Rates, Drop-Off Points, and Pipeline Velocity from CRM Export Data

    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:

    • How to load and validate a real-world CRM stage history export and identify data quality issues before they corrupt your analysis
    • How to calculate stage-entry counts, conversion rates, and drop-off percentages across each funnel stage
    • How to measure average time spent in each stage (stage velocity) and total deal cycle time
    • How to segment funnel metrics by deal owner, region, deal size bucket, or any other categorical dimension
    • How to build a clean, stakeholder-ready funnel summary table and export it to Excel

    Prerequisites

    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.


    Understanding the Data Model: What a CRM Stage History Export Actually Looks Like

    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.


    Loading and Creating the Dataset

    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"]).


    Step 1: Validating and Profiling the Data Before You Analyze

    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)}")
    

    Step 2: Identifying Each Deal's Highest Stage Reached

    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).


    Step 3: Calculating Stage Conversion Rates and Drop-Off

    Now we build the funnel table. For each stage, we want:

    1. Entry count — unique deals that reached this stage
    2. Exit count — unique deals that made it past this stage (i.e., reached the next stage)
    3. Conversion rate — Exit / Entry
    4. Drop-off rate — 1 - Conversion rate
    5. Drop-off count — absolute number of deals lost at this stage
    # 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).


    Step 4: Calculating Time-in-Stage (Pipeline Velocity)

    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))
    

    Calculating Total Deal Cycle Time

    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")
    

    Step 5: Segmented Funnel Analysis

    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.


    Step 6: Finding the Worst Drop-Off Points Automatically

    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.


    Step 7: Pipeline Velocity — Deals per Day

    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.


    Step 8: Exporting the Funnel Report to Excel

    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.


    Hands-On Exercise

    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?


    Common Mistakes & Troubleshooting

    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"
    

    Summary & Next Steps

    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:

    • Stage entry counts come from nunique on opportunity_id, not row counts. This distinction will save you from the most common funnel analysis mistake.
    • Set intersection logic (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.
    • Median beats mean for time-in-stage because outlier deals will always exist and will always mislead.
    • Segmentation is where the value is. An overall 32% close rate is a number. A West region 41% vs. East region 23% is a finding.

    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.

    Work With Us

    From insight to implementation

    Reading is the start. When you're ready to build the data, automation, or AI systems behind it, our team turns strategy into shipped results.

    Let's Build

    Python for Data Analysis

    Previous

    Unpivoting and Stacking Survey or Form Data in pandas: Transforming One-Row-Per-Respondent Wide Exports into Analysis-Ready Long Format

    Related Insights

    PythonFoundation

    Unpivoting and Stacking Survey or Form Data in pandas: Transforming One-Row-Per-Respondent Wide Exports into Analysis-Ready Long Format

    16 min
    PythonExpert

    Building a Customer Churn Prediction Scoring Table in pandas: Calculating Behavioral Flags, Weighted Risk Scores, and Exporting a Ranked Watch List to Excel

    25 min
    PythonPractitioner

    Building an Inventory Aging Report in pandas: Classifying Stock by Days on Hand, Flagging Slow Movers, and Exporting a Tiered Summary to Excel

    22 min

    On this page

    • Prerequisites
    • Understanding the Data Model: What a CRM Stage History Export Actually Looks Like
    • Loading and Creating the Dataset
    • Step 1: Validating and Profiling the Data Before You Analyze
    • Step 2: Identifying Each Deal's Highest Stage Reached
    • Step 3: Calculating Stage Conversion Rates and Drop-Off
    • Step 4: Calculating Time-in-Stage (Pipeline Velocity)
    • Calculating Total Deal Cycle Time
    • Step 5: Segmented Funnel Analysis
    • Step 6: Finding the Worst Drop-Off Points Automatically
    • Step 7: Pipeline Velocity — Deals per Day
    • Step 8: Exporting the Funnel Report to Excel
    • Hands-On Exercise
    • Common Mistakes & Troubleshooting
    • Summary & Next Steps