User story: the Monday pipeline report

The data

deals = pd.read_csv("crm_pipeline.csv", parse_dates=["close_date","last_modified"])
# id, owner, account, amount, stage, close_date, last_modified

stage_prob = {
    "Discovery":   0.10,
    "Qualified":   0.20,
    "Proposal":    0.40,
    "Negotiation": 0.70,
    "Closing":     0.90,
}
deals["prob"]      = deals["stage"].map(stage_prob).fillna(0)
deals["weighted"]  = deals["amount"] * deals["prob"]

Pipeline by stage

by_stage = (deals.groupby("stage")
                  .agg(deals=("id","count"),
                       value=("amount","sum"),
                       weighted=("weighted","sum"))
                  .reindex(stage_prob.keys()))

Per-rep snapshot

by_rep = (deals.groupby("owner")
                .agg(deals=("id","count"),
                     pipeline=("amount","sum"),
                     forecast=("weighted","sum"))
                .sort_values("forecast", ascending=False))

Stuck deals

stuck = deals[(deals["last_modified"] < pd.Timestamp.now() - pd.Timedelta(days=30)) &
              (deals["stage"] != "Closed")]

One-page output

with pd.ExcelWriter("monday_pipeline.xlsx") as w:
    by_stage.to_excel(w, sheet_name="By Stage")
    by_rep.to_excel(w, sheet_name="By Rep")
    stuck.to_excel(w, sheet_name="Stuck Deals", index=False)
    pd.DataFrame({
        "Metric": ["Total pipeline","Weighted forecast","# stuck deals"],
        "Value":  [by_stage["value"].sum(), by_stage["weighted"].sum(), len(stuck)],
    }).to_excel(w, sheet_name="Cover", index=False)

Schedule it

Schedule the script to run at 6 AM every Monday (Module 12, Lesson 6). Email it to the VP of Sales (Module 12, Lesson 5). Now your "Monday morning pipeline report" job is done before you wake up.