Build a Sales Pipeline Report from a CRM Export

A sales pipeline report answers three questions every week: how much business is in play, how much of it is likely to close this quarter, and which deals need attention. CRMs have dashboards for the first; the second and third usually end up in a spreadsheet, rebuilt by hand from an export, because they need rules the CRM does not know: this quarter's remaining quota, what counts as a stale deal, what moved since last Monday. This page builds that report from two weekly exports with pandas and writes it to Excel. It is part of Building Finance and Operations Reports.

Prerequisites

pip install pandas xlsxwriter

Two CSV exports of the same CRM opportunity report, taken on Monday 28 September and Monday 5 October 2026: one row per opportunity with its account, owner, stage, amount, expected close date, date of last activity and the stage's default probability. Stages run Qualify → Discovery → Proposal → Negotiation, then Closed Won or Closed Lost. The team's quota for Q4 2026 (October to December) is 1,200,000; one week in, a few deals have already closed against it.

Step 1 — Load and keep the open pipeline

Read both snapshots, parse the dates, and separate open opportunities from closed ones. The report date is the snapshot date, not today, so the report can be rerun for any week:

import pandas as pd

AS_OF = pd.Timestamp("2026-10-05")
cols = {"parse_dates": ["CloseDate", "LastActivity"]}
now = pd.read_csv("pipeline_2026-10-05.csv", **cols)
before = pd.read_csv("pipeline_2026-09-28.csv", **cols)

open_now = now[~now["Stage"].str.startswith("Closed")].copy()
open_now["Weighted"] = open_now["Amount"] * open_now["Probability"]
print(f"{len(open_now)} open opportunities, {open_now['Amount'].sum():,.0f} total, "
      f"{open_now['Weighted'].sum():,.0f} weighted")

Step 2 — Pipeline by stage

The core table: how many deals and how much value at each stage, with the weighted value that discounts early-stage deals by their probability:

STAGES = ["Qualify", "Discovery", "Proposal", "Negotiation"]
by_stage = (open_now.groupby("Stage")
            .agg(Deals=("OpportunityID", "size"), Value=("Amount", "sum"), Weighted=("Weighted", "sum"))
            .reindex(STAGES, fill_value=0))
by_stage["Avg deal"] = by_stage["Value"] / by_stage["Deals"]
print(by_stage.round(0).astype(int).to_string())

reindex(STAGES) keeps the stages in sales order rather than alphabetical, and keeps a stage in the table even when it is empty, which is itself worth seeing.

Step 3 — This quarter: forecast and coverage

The question for the quarter is whether the deals expected to close in it are enough to hit quota. Coverage is open pipeline closing in the quarter divided by the quota still to win; sales teams commonly want three times or more, because most early-stage deals will not close:

Q_START, Q_END = pd.Timestamp("2026-10-01"), pd.Timestamp("2026-12-31")
QUOTA = 1_200_000
won_in_q = now[(now["Stage"] == "Closed Won") & now["CloseDate"].between(Q_START, Q_END)]["Amount"].sum()
remaining = QUOTA - won_in_q

in_q = open_now[open_now["CloseDate"].between(Q_START, Q_END)]
by_month = in_q.groupby(in_q["CloseDate"].dt.to_period("M")).agg(
    Deals=("OpportunityID", "size"), Value=("Amount", "sum"), Weighted=("Weighted", "sum"))
print(by_month.round(0).astype(int).to_string())
print(f"won so far {won_in_q:,.0f}; remaining quota {remaining:,.0f}; "
      f"coverage {in_q['Amount'].sum() / remaining:.2f}x; weighted forecast {in_q['Weighted'].sum():,.0f}")

The numbers tell a clear story. There is 2.46 million of open pipeline in total, but only 1.75 million of it is expected to close this quarter, and against 1.18 million of quota still to win that is 1.48 times coverage, half the usual comfort level. The weighted forecast of about 600,000 points the same way. The conversation this report should start is about the deals outside it: 398,000 sits in deals whose close dates have already passed (Step 4), which are counted in no quarter until someone re-dates them, and 314,000 is expected in 2027, some of which may be pulled forward.

Weighted forecast is a sanity check, not a promise: stage probabilities are averages, and a quarter's result is decided by a handful of large deals. Put the big ones in front of the team individually.

Step 4 — Deals that need attention

Two lists do most of the work in a pipeline meeting. Slipped deals have a close date in the past but are still open; their forecast is fiction until someone updates them. Stale deals have had no logged activity for thirty days:

slipped = open_now[open_now["CloseDate"] < AS_OF]
stale = open_now[(AS_OF - open_now["LastActivity"]).dt.days > 30]
attention = pd.concat([slipped.assign(Issue="Close date passed"),
                       stale.assign(Issue="No activity for 30+ days")])
attention = (attention.groupby("OpportunityID", as_index=False)
             .agg({"Account": "first", "Owner": "first", "Stage": "first", "Amount": "first",
                   "CloseDate": "first", "LastActivity": "first", "Issue": " + ".join})
             .sort_values("Amount", ascending=False))
print(f"slipped {len(slipped)}, stale {len(stale)}, needing attention {len(attention)}")
print(attention.groupby("Owner")["Amount"].agg(["size", "sum"]).round(0).to_string())

A deal that is both slipped and stale appears once with both issues joined, so the list is one row per deal. Here 16 deals have slipped and 35 have gone quiet, 44 deals in all, and Chen alone holds 18 of them worth almost 600,000. That is not necessarily a performance problem: often it means one person's CRM hygiene is behind, and the report's first job is to get the data right before anyone forecasts from it.

Step 5 — What changed since last week

Comparing two snapshots turns the pipeline from a picture into a story: new deals, deals that advanced, closed, slipped into a later period, or disappeared. An outer merge on the opportunity ID with indicator=True sorts every deal into one of those groups:

m = before.merge(now, on="OpportunityID", how="outer", suffixes=("_before", "_now"), indicator=True)
rank = {s: i for i, s in enumerate(STAGES)}

def movement(r):
    if r["_merge"] == "right_only":
        return "New"
    if r["_merge"] == "left_only":
        return "Removed"
    if r["Stage_now"] == "Closed Won" and r["Stage_before"] != "Closed Won":
        return "Won"
    if r["Stage_now"] == "Closed Lost" and r["Stage_before"] != "Closed Lost":
        return "Lost"
    if rank.get(r["Stage_now"], -1) > rank.get(r["Stage_before"], -1):
        return "Advanced"
    if r["CloseDate_now"] > r["CloseDate_before"]:
        return "Close date pushed"
    return "No change"

m["Movement"] = m.apply(movement, axis=1)
m["Amount"] = m["Amount_now"].fillna(m["Amount_before"])
moves = m.groupby("Movement").agg(Deals=("OpportunityID", "size"), Value=("Amount", "sum"))
print(moves.round(0).astype(int).to_string())

"Removed" deserves a look every week: opportunities deleted from the CRM instead of being closed as lost distort win rates and hide why business was lost.

Step 6 — Write the report

Four sheets: a summary with the stage table and the quarter coverage, the attention list, the movement detail, and the full open pipeline for filtering:

with pd.ExcelWriter("pipeline_report_2026-10-05.xlsx", engine="xlsxwriter") as xw:
    book = xw.book
    money = book.add_format({"num_format": "#,##0"})
    bold = book.add_format({"bold": True})
    by_stage.round(0).to_excel(xw, sheet_name="Summary", startrow=2)
    ws = xw.sheets["Summary"]
    ws.write(0, 0, f"Pipeline as of {AS_OF:%d %B %Y}", book.add_format({"bold": True, "font_size": 13}))
    r = len(by_stage) + 5
    for label, value in [("Q4 quota", QUOTA), ("Won in Q4 so far", won_in_q), ("Remaining quota", remaining),
                         ("Open pipeline closing in Q4", in_q["Amount"].sum()),
                         ("Weighted forecast for Q4", in_q["Weighted"].sum())]:
        ws.write(r, 0, label, bold)
        ws.write_number(r, 1, value, money)
        r += 1
    ws.write(r, 0, "Coverage", bold)
    ws.write_formula(r, 1, f"=B{r - 1}/B{r - 2}", book.add_format({"num_format": '0.00"x"'}))
    ws.set_column(0, 0, 28)
    ws.set_column(1, 4, 13, money)

    attention.to_excel(xw, sheet_name="Needs attention", index=False)
    m.loc[m["Movement"] != "No change", ["OpportunityID", "Movement", "Stage_before", "Stage_now", "Amount",
                                         "CloseDate_before", "CloseDate_now"]] \
        .sort_values("Movement").to_excel(xw, sheet_name="Movement", index=False)
    open_now.sort_values(["Stage", "CloseDate"]).to_excel(xw, sheet_name="Open pipeline", index=False)
    for name in ["Needs attention", "Movement", "Open pipeline"]:
        sheet = xw.sheets[name]
        sheet.set_column(0, 8, 15)
        sheet.autofilter(0, 0, 0, 8)
        sheet.freeze_panes(1, 0)
print("written")

The coverage cell is a formula over the cells above it, so if someone overrides the quota in the sheet during the meeting, coverage updates.

Common pitfalls

• Alphabetical stages. Reindex to the sales order; "Discovery, Negotiation, Proposal, Qualify" reads nonsensically.
• Treating weighted pipeline as a forecast. It is a sanity check; big deals decide the quarter.
• Ignoring slipped deals. Past close dates inflate the current period until someone updates them.
• Comparing snapshots by row position. Match on the opportunity ID with an outer merge, so new and removed deals are visible.
• Currency. If the CRM holds deals in several currencies, convert before summing.
• Using today's date. Use the snapshot date so the report can be rerun for any week.

Conclusion

Load the weekly CRM export, keep open opportunities, and summarise count, value and weighted value by stage in sales order. For the quarter, forecast by expected close month and compute coverage against the remaining quota. List slipped and stale deals once each with their issues, compare this week's snapshot with last week's using an outer merge with indicator=True to classify every deal's movement, and write summary, attention list, movement and full pipeline sheets to Excel.

Frequently asked questions

What is weighted pipeline? The sum of each open deal's amount multiplied by its stage probability. It discounts early-stage deals and gives a rough expected value, but individual large deals matter more than the average.

How do I calculate pipeline coverage? Divide the value of open opportunities expected to close in the period by the quota still to be won in that period. Three times coverage is a common rule of thumb.

How do I see what changed in the pipeline week over week? Keep each week's export, merge two snapshots on the opportunity ID with an outer join and indicator=True, and compare stage and close date: new, removed, advanced, won, lost and pushed deals each fall out of simple rules.

What makes a deal stale? No logged activity for a set period, commonly 30 days. Use the CRM's last-activity date and compare it with the report date.

Related

• Up one level: Building Finance and Operations Reports — the rest of the finance and operations reports.
• Build a Weekly Sales Report — what has already closed.
• Build a Sales Commission Report — what closed deals pay out.
• Create a Funnel Chart in Excel with Python — a chart for the stage table.
• Find Rows in One Excel File Missing from Another — the snapshot comparison in general.