Every Monday morning, someone on your RevOps team manually pulls pipeline numbers, formats them into a presentable email, and sends it to leadership. That process takes 30 to 45 minutes and it is exactly the kind of repetitive work that a short Python script can eliminate entirely.

How It Works

The script runs three steps in sequence: pull pipeline data from Salesforce, transform it into a formatted HTML report, and send it via email. The entire thing runs on a cron schedule so Monday reports land in inboxes before anyone reaches the office.

Pulling Pipeline Data

Start by querying Salesforce for the current pipeline snapshot:

import os
import pandas as pd
from simple_salesforce import Salesforce

sf = Salesforce(
    username=os.environ["SF_USERNAME"],
    password=os.environ["SF_PASSWORD"],
    security_token=os.environ["SF_TOKEN"],
)

def get_pipeline():
    query = """
        SELECT StageName, COUNT(Id) deal_count,
               SUM(Amount) total_amount
        FROM Opportunity
        WHERE IsClosed = false AND Amount > 0
        GROUP BY StageName
        ORDER BY StageName
    """
    results = sf.query(query)
    return pd.DataFrame(results["records"]).drop(columns=["attributes"])

Generating the HTML Report

Plain text emails get ignored. An HTML-formatted table with clear numbers gets read. Pandas makes this conversion simple:

from datetime import date

def build_report(df):
    total_pipeline = df["total_amount"].sum()
    total_deals = df["deal_count"].sum()

    table_html = df.to_html(index=False, float_format="${:,.0f}".format)

    html = f"""
    <html>
    <body>
    <h2>Weekly Pipeline Report - {date.today().strftime('%B %d, %Y')}</h2>
    <p>
      <strong>Total Pipeline:</strong> ${total_pipeline:,.0f}<br>
      <strong>Open Deals:</strong> {total_deals}
    </p>
    <h3>Pipeline by Stage</h3>
    {table_html}
    <hr>
    <p style="color: #888; font-size: 12px;">
      Auto-generated by RevOps Pipeline Bot. Data pulled at
      {date.today().isoformat()}.
    </p>
    </body>
    </html>
    """
    return html

Sending the Email

The smtplib module handles email delivery without any third-party dependencies:

import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText

def send_report(html_body):
    msg = MIMEMultipart("alternative")
    msg["Subject"] = f"Weekly Pipeline Report - {date.today().strftime('%b %d')}"
    msg["From"] = os.environ["SMTP_FROM"]
    msg["To"] = os.environ["REPORT_RECIPIENTS"]

    msg.attach(MIMEText(html_body, "html"))

    with smtplib.SMTP(os.environ["SMTP_HOST"], 587) as server:
        server.starttls()
        server.login(os.environ["SMTP_USER"], os.environ["SMTP_PASS"])
        server.sendmail(msg["From"], msg["To"].split(","), msg.as_string())

Putting It Together

The main function chains all three steps:

def main():
    df = get_pipeline()
    html = build_report(df)
    send_report(html)
    print(f"Report sent to {os.environ['REPORT_RECIPIENTS']}")

if __name__ == "__main__":
    main()

Scheduling With Cron

Add a cron entry to run the report every Monday at 6 AM before leadership checks email:

0 6 * * 1 cd /opt/revops-scripts && python pipeline_report.py >> /var/log/pipeline_report.log 2>&1

On Windows, use Task Scheduler to achieve the same result. Point it to your Python executable and the script path.

Adding Week-Over-Week Comparison

To make the report more actionable, save each week’s snapshot and include the delta:

import json
from pathlib import Path

SNAPSHOT_FILE = Path("last_week_pipeline.json")

def add_wow_comparison(df):
    if SNAPSHOT_FILE.exists():
        last_week = json.loads(SNAPSHOT_FILE.read_text())
        current_total = df["total_amount"].sum()
        delta = current_total - last_week["total"]
        direction = "up" if delta > 0 else "down"
        df.attrs["wow_note"] = f"Pipeline is {direction} ${abs(delta):,.0f} vs last week"

    SNAPSHOT_FILE.write_text(json.dumps({"total": float(df["total_amount"].sum())}))
    return df

Key Takeaways

  • A Python script can fully replace the manual Monday-morning pipeline report process in under 100 lines of code
  • HTML-formatted emails with summary metrics and tables are far more likely to be read than plain-text reports
  • Saving weekly snapshots enables week-over-week trend analysis that manual reports rarely include
  • Cron scheduling ensures the report arrives before leadership needs it, every single week