Almost every business has a report that someone builds by hand, every week, in exactly the same way. It is one of the easiest and most valuable things to automate. This guide walks through a complete pattern you can adapt.
Step 1: write the manual process down
Before any code, list what happens today: which files are downloaded and from where, what is cleaned or combined, which calculations are made, and who receives the result. If you can describe it step by step, it can almost certainly be automated.
Step 2: collect and combine the inputs
from pathlib import Path
import pandas as pd
INPUT = Path("inputs")
frames = []
for file in sorted(INPUT.glob("sales_*.csv")):
df = pd.read_csv(file, parse_dates=["order_date"])
df["source_file"] = file.name
frames.append(df)
sales = pd.concat(frames, ignore_index=True)Step 3: clean and calculate
sales = (
sales.drop_duplicates(subset="order_id")
.assign(
revenue=lambda d: d["quantity"] * d["unit_price"],
week=lambda d: d["order_date"].dt.to_period("W-SUN").dt.start_time,
)
)
weekly = (
sales.groupby(["week", "region"], as_index=False)
.agg(revenue=("revenue", "sum"), orders=("order_id", "nunique"))
)Step 4: check before you send
Automation is only valuable if you can trust it. Stop the run if something looks wrong, rather than sending a misleading report. More patterns are in the data quality guide.
last_week = weekly["week"].max()
latest = weekly[weekly["week"] == last_week]
assert not sales["order_id"].duplicated().any(), "Duplicate orders found"
assert len(latest) > 0, "No data for the latest week"
assert (latest["revenue"] >= 0).all(), "Negative revenue found"Step 5: build the output
output = Path("outputs") / f"weekly_report_{last_week:%Y-%m-%d}.xlsx"
output.parent.mkdir(exist_ok=True)
with pd.ExcelWriter(output) as writer:
latest.to_excel(writer, sheet_name="This week", index=False)
weekly.to_excel(writer, sheet_name="History", index=False)Step 6: send it
import os
import smtplib
from email.message import EmailMessage
msg = EmailMessage()
msg["Subject"] = f"Weekly sales report, week of {last_week:%d %B %Y}"
msg["From"] = os.environ["REPORT_SENDER"]
msg["To"] = os.environ["REPORT_RECIPIENTS"]
msg.set_content(f"Hi all,\n\nRevenue last week: £{latest['revenue'].sum():,.0f}.\n"
"The full report is attached.\n")
msg.add_attachment(output.read_bytes(), maintype="application",
subtype="vnd.openxmlformats-officedocument.spreadsheetml.sheet",
filename=output.name)
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)Keep passwords in environment variables or a secrets manager, never in the script itself.
Step 7: schedule it
On macOS or Linux, one line in crontab -e runs the report every Monday at 07:00:
0 7 * * 1 cd /path/to/weekly-report && /usr/local/bin/uv run report.py >> run.log 2>&1On Windows, Task Scheduler does the same. For data that already lives in the cloud, a scheduled GitHub Action, a Snowflake task or a Google Apps Script trigger are good alternatives.
Deciding what to automate first
- Frequency: daily and weekly reports save the most time.
- Error risk: anything involving a lot of copying and pasting.
- Importance: reports that drive real decisions, such as cash, stock or staffing.
Written by Alessandro Ecclesie Agazzi, freelance analytics engineer in London. Updated 30 September 2026.