How to Create Automated Pipeline Health Reports

An automated pipeline health report gives you a snapshot of pipeline reality every morning. I run this across multiple client accounts. It catches dying pipelines before anyone notices the revenue gap.

What You Need Before Starting

• Python 3.8+
• CRM API access
• SMTP credentials for email delivery
• pandas and jinja2 installed

Step 1: Define Your Health Metrics

Pipeline health comes down to five numbers: total pipeline value, deal count, average deal age, stage conversion rates, and velocity.

import pandas as pd
from datetime import datetime

def calculate_pipeline_health(deals_df):
    active = deals_df[deals_df["status"] == "open"]
    return {
        "total_value": active["amount"].sum(),
        "deal_count": len(active),
        "avg_deal_age": (
            datetime.now() - pd.to_datetime(active["created_at"])
        ).dt.days.mean(),
        "avg_deal_size": active["amount"].mean(),
        "stalled_count": len(active[active["days_in_stage"] > 14]),
    }

Step 2: Calculate Stage Conversion Rates

Track how many deals move from one stage to the next. A drop at any stage signals a problem.

def stage_conversion_rates(deals_df, stages):
    rates = {}
    for i in range(len(stages) - 1):
        entered = len(deals_df[deals_df["highest_stage"] >= i])
        advanced = len(deals_df[deals_df["highest_stage"] >= i + 1])
        rates[f"{stages[i]} -> {stages[i+1]}"] = advanced / max(entered, 1)
    return rates

stages = ["discovery", "qualification", "proposal", "negotiation", "closed"]
conversions = stage_conversion_rates(deal_data, stages)

Step 3: Build the Report Template

Use Jinja2 to format a clean HTML report. Keep it scannable. Numbers first, details second.

from jinja2 import Template

report_template = Template("""
<h2>Pipeline Health Report - {{ date }}</h2>
<table>
  <tr><td>Total Pipeline</td><td>${{ "{:,.0f}".format(total_value) }}</td></tr>
  <tr><td>Active Deals</td><td>{{ deal_count }}</td></tr>
  <tr><td>Avg Deal Age</td><td>{{ "%.0f"|format(avg_deal_age) }} days</td></tr>
  <tr><td>Stalled Deals</td><td>{{ stalled_count }}</td></tr>
</table>
""")

Step 4: Compare Against Benchmarks

Flag metrics that fall below your baseline. If average deal age jumps 20% above normal, that is a red flag.

def flag_anomalies(current_health, benchmarks):
    flags = []
    if current_health["avg_deal_age"] > benchmarks["avg_deal_age"] * 1.2:
        flags.append("Deal age is 20%+ above benchmark")
    if current_health["stalled_count"] > benchmarks["stalled_count"] * 1.5:
        flags.append(f"{current_health['stalled_count']} stalled deals")
    if current_health["total_value"] < benchmarks["total_value"] * 0.8:
        flags.append("Pipeline value dropped 20%+ below benchmark")
    return flags

Step 5: Schedule Daily Delivery

Run this every morning at 7am. The report lands in your inbox before your first coffee.

# crontab entry
0 7 * * * cd /root/pipeline-health && python generate_report.py

No manual data pulling. No stale reports in a forgotten spreadsheet.

What to Build Next

Add week-over-week trend lines to your report. A single snapshot is useful, but seeing direction is what drives action.

Related Reading

• Setting Up Automated Client Reporting Pipelines - automated client reporting pipeline
• Creating an Automated Client Health Score - automated client health score guide
• AI for Sales Pipeline Management - ai sales pipeline management