Automate a daily report with Python, cron and email

Answer three questions in writing:

1. Where does the data come from? (a CSV export, an API, a database query)
2. What three numbers matter every morning? (yesterday's sales, sign-ups, errors…)
3. Who reads it and when? (you, 7:00, on your phone)

Get one real sample of the data into data/sample.csv.

Done when: you can say "the report shows X, Y and Z compared with last week".

python -m venv .venv && source .venv/bin/activate
pip install pandas matplotlib requests python-dotenv

# report.py
import pandas as pd
from datetime import date, timedelta

def load():
    df = pd.read_csv("data/orders.csv", parse_dates=["created_at"])
    return df[df["created_at"] >= pd.Timestamp(date.today() - timedelta(days=14))]

def summarise(df):
    df["day"] = df["created_at"].dt.date
    daily = df.groupby("day")["total"].sum()
    yesterday = date.today() - timedelta(days=1)
    week_ago = yesterday - timedelta(days=7)
    return {
        "revenue": float(daily.get(yesterday, 0)),
        "revenue_last_week": float(daily.get(week_ago, 0)),
        "orders": int((df["day"] == yesterday).sum()),
        "daily": daily,
    }

Done when: python -c "import report; print(report.summarise(report.load()))" prints sensible numbers.

import matplotlib
matplotlib.use("Agg")                      # no screen on a server
import matplotlib.pyplot as plt
import smtplib, os
from email.message import EmailMessage

def chart(daily, path="report.png"):
    fig, ax = plt.subplots(figsize=(8, 3))
    daily.plot(ax=ax, marker="o")
    ax.set_title("Revenue, last 14 days"); ax.grid(alpha=.3)
    fig.tight_layout(); fig.savefig(path, dpi=120); plt.close(fig)
    return path

def send(stats, png):
    change = stats["revenue"] - stats["revenue_last_week"]
    msg = EmailMessage()
    msg["Subject"] = f"Daily report: ${stats['revenue']:,.0f} ({change:+,.0f} vs last week)"
    msg["From"], msg["To"] = os.environ["MAIL_FROM"], os.environ["MAIL_TO"]
    msg.set_content(f"Orders: {stats['orders']}\nRevenue: ${stats['revenue']:,.2f}")
    with open(png, "rb") as fh:
        msg.add_attachment(fh.read(), maintype="image", subtype="png", filename="report.png")
    with smtplib.SMTP_SSL(os.environ["SMTP_HOST"], 465) as smtp:
        smtp.login(os.environ["SMTP_USER"], os.environ["SMTP_PASSWORD"])
        smtp.send_message(msg)

if __name__ == "__main__":
    data = summarise(load())
    send(data, chart(data["daily"]))

Keep secrets in a .env file with chmod 600 .env, never in the script.

Done when: running python report.py puts the email in your inbox.

On a small Linux server (any $5 VPS, or a Raspberry Pi):

crontab -e
# m  h  dom mon dow  command
0  7  *   *   *    cd /home/ada/report && .venv/bin/python report.py >> logs/report.log 2>&1

• Use absolute paths: cron runs with a nearly empty environment.
• Log to a file, and check it the next morning with tail -n 50 logs/report.log.
• Test the exact cron command by hand first.

Done when: the email arrives tomorrow at 7:00 without you touching anything.

• Alert on failure: wrap the main block in try/except and send a short "report failed" email with the error.
• Retry the data fetch 3 times with a short wait before giving up.
• Rotate logs with logrotate or keep only the last 30 days.
• Write a README with the cron line and the required environment variables, for the day you forget how it works.

Done when: you broke the data source on purpose and got a failure email.