Case Study 19-1: Priya Automates Sandra's Monday Morning Report
The Situation
Sandra Chen, VP of Operations at Acme Corp, starts every Monday with the same ritual. By 8:00 AM, she needs a consolidated view of last week's performance across all three of Acme's regional divisions: West, East, and Central. The report goes to Sandra, the CFO, the COO, and the three regional directors.
Before automation, the process looked like this:
- Priya logs into the shared drive and downloads three regional CSV files (West_Wk47.csv, East_Wk47.csv, Central_Wk47.csv)
- She opens Excel, creates a new workbook, and pastes in the data
- She manually calculates totals and week-over-week changes
- She formats the table, applies conditional highlighting for metrics that missed targets
- She opens Outlook and composes an email with the Excel file attached
- She adds Sandra, the CFO, COO, and regional directors to the recipient list
- She double-checks everything and hits Send
Total time: approximately 90 minutes. Time of year this needs to happen: every single Monday, fifty-two times per year.
Priya's insight: "The decisions I'm making while doing this are exactly three: which files to open, what the formulas are, and who gets the email. Everything else is mechanical. Python can do all of that."
The Data
Each regional CSV file has the same structure. Here is a representative sample of West_Wk47.csv:
metric,value,target
weekly_revenue,142000.00,135000.00
units_shipped,923,900
new_orders,87,80
returns,28,25
customer_satisfaction,91.2,90.0
on_time_delivery_pct,96.4,95.0
avg_order_value,153.85,150.00
The three files share the same metrics. Priya's job is to aggregate them, compute totals and averages where appropriate, and flag anything that missed its target.
The Credential Setup
Before writing a single line of email code, Priya handles credentials.
She creates a .env file in her project directory:
# .env — NEVER commit this file to version control
EMAIL_SENDER=reports@acmecorp.com
EMAIL_PASSWORD=abcd efgh ijkl mnop
EMAIL_SMTP_HOST=smtp.gmail.com
EMAIL_SMTP_PORT=465
EMAIL_FROM_NAME=Acme Corp Operations
She adds .env to her .gitignore:
.env
*.env
She creates a .env.example file that is safe to commit:
# .env.example — copy to .env and fill in real values
EMAIL_SENDER=your-email@acmecorp.com
EMAIL_PASSWORD=your-app-password-here
EMAIL_SMTP_HOST=smtp.gmail.com
EMAIL_SMTP_PORT=465
EMAIL_FROM_NAME=Acme Corp Operations
The email account reports@acmecorp.com is a service account specifically for automated reports. Priya generates an App Password for it and stores only the App Password in .env. The main account password is never touched.
The Script
"""
monday_report.py
================
Acme Corp — Weekly Operations Report Automation
Reads regional CSV files, computes aggregated metrics, generates an Excel
report, and sends it via email to Sandra Chen and Acme leadership.
Run every Monday at 6:30 AM via cron or Task Scheduler.
"""
import os
import io
import smtplib
from datetime import date, timedelta
from email import encoders
from email.mime.base import MIMEBase
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from pathlib import Path
import pandas as pd
import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
from dotenv import load_dotenv
load_dotenv()
# ---------------------------------------------------------------------------
# Configuration
# ---------------------------------------------------------------------------
REGIONS = ["West", "East", "Central"]
DATA_DIR = Path("data/weekly")
# Metrics that are summed across regions
SUM_METRICS = ["weekly_revenue", "units_shipped", "new_orders", "returns"]
# Metrics that are averaged across regions (not summed)
AVG_METRICS = [
"customer_satisfaction",
"on_time_delivery_pct",
"avg_order_value",
]
# Target overrides for the consolidated total (sum of regional targets)
# The per-metric targets in the CSV are per-region; totals are calculated
RECIPIENTS_TO = ["sandra.chen@acmecorp.com"]
RECIPIENTS_CC = [
"cfo@acmecorp.com",
"coo@acmecorp.com",
"west.director@acmecorp.com",
"east.director@acmecorp.com",
"central.director@acmecorp.com",
]
# ---------------------------------------------------------------------------
# Step 1: Load and aggregate regional data
# ---------------------------------------------------------------------------
def load_regional_data(week_number: int, year: int) -> dict:
"""
Load all three regional CSV files for the given week.
Returns a dict with keys:
'by_region': {region: {metric: {'value': float, 'target': float}}}
'week_number': int
'year': int
"""
by_region = {}
for region in REGIONS:
filename = DATA_DIR / f"{region}_Wk{week_number:02d}.csv"
if not filename.exists():
raise FileNotFoundError(
f"Regional data file not found: {filename}\n"
f"Expected files in {DATA_DIR}/ named "
f"West_Wk{week_number:02d}.csv, etc."
)
df = pd.read_csv(filename)
region_data = {}
for _, row in df.iterrows():
region_data[row["metric"]] = {
"value": float(row["value"]),
"target": float(row["target"]),
}
by_region[region] = region_data
print(f" Loaded {region} data: {len(region_data)} metrics")
return {"by_region": by_region, "week_number": week_number, "year": year}
def aggregate_metrics(regional_data: dict) -> dict:
"""
Compute company-wide totals and averages.
Returns:
{metric: {'value': float, 'target': float, 'type': 'sum' or 'avg'}}
"""
by_region = regional_data["by_region"]
consolidated = {}
all_metrics = set()
for region_metrics in by_region.values():
all_metrics.update(region_metrics.keys())
for metric in all_metrics:
values = [
by_region[r][metric]["value"]
for r in REGIONS
if metric in by_region[r]
]
targets = [
by_region[r][metric]["target"]
for r in REGIONS
if metric in by_region[r]
]
if metric in SUM_METRICS:
consolidated[metric] = {
"value": sum(values),
"target": sum(targets),
"type": "sum",
}
else:
n = len(values)
consolidated[metric] = {
"value": sum(values) / n if n else 0,
"target": sum(targets) / n if n else 0,
"type": "avg",
}
return consolidated
# ---------------------------------------------------------------------------
# Step 2: Generate the Excel report
# ---------------------------------------------------------------------------
METRIC_LABELS = {
"weekly_revenue": "Weekly Revenue",
"units_shipped": "Units Shipped",
"new_orders": "New Orders",
"returns": "Returns",
"customer_satisfaction": "Customer Satisfaction (%)",
"on_time_delivery_pct": "On-Time Delivery (%)",
"avg_order_value": "Avg. Order Value ($)",
}
METRIC_FORMAT = {
"weekly_revenue": '"$"#,##0.00',
"units_shipped": "#,##0",
"new_orders": "#,##0",
"returns": "#,##0",
"customer_satisfaction": '0.0"%"',
"on_time_delivery_pct": '0.0"%"',
"avg_order_value": '"$"#,##0.00',
}
def format_metric_value(metric: str, value: float) -> str:
"""Format a metric value for display in plain text."""
if metric == "weekly_revenue":
return f"${value:,.2f}"
elif metric in ("customer_satisfaction", "on_time_delivery_pct"):
return f"{value:.1f}%"
elif metric == "avg_order_value":
return f"${value:,.2f}"
else:
return f"{value:,.0f}"
def generate_excel_report(
regional_data: dict,
consolidated: dict,
output_path: Path,
) -> Path:
"""
Build a formatted Excel workbook with regional breakdown and company total.
Returns the path to the saved file.
"""
wb = openpyxl.Workbook()
# --- Sheet 1: Company Summary ---
ws_summary = wb.active
ws_summary.title = "Company Summary"
header_font = Font(bold=True, color="FFFFFF", size=12)
header_fill = PatternFill("solid", fgColor="2C3E50")
center = Alignment(horizontal="center")
thin = Side(style="thin", color="CCCCCC")
border = Border(bottom=thin)
# Title row
ws_summary["A1"] = (
f"Acme Corp — Week {regional_data['week_number']} "
f"Operations Summary"
)
ws_summary["A1"].font = Font(bold=True, size=14, color="2C3E50")
ws_summary.merge_cells("A1:F1")
# Date row
ws_summary["A2"] = f"Generated: {date.today().strftime('%A, %B %d, %Y')}"
ws_summary["A2"].font = Font(italic=True, color="666666")
ws_summary.merge_cells("A2:F2")
# Column headers
headers = ["Metric", "West", "East", "Central", "Total / Avg", "vs Target"]
for col, header in enumerate(headers, 1):
cell = ws_summary.cell(row=4, column=col, value=header)
cell.font = header_font
cell.fill = header_fill
cell.alignment = center
# Data rows
by_region = regional_data["by_region"]
row = 5
for metric, label in METRIC_LABELS.items():
if metric not in consolidated:
continue
row_fill = PatternFill("solid", fgColor="F9F9F9") if row % 2 == 0 else None
ws_summary.cell(row=row, column=1, value=label).font = Font(bold=False)
for col, region in enumerate(REGIONS, 2):
val = by_region.get(region, {}).get(metric, {}).get("value", 0)
c = ws_summary.cell(row=row, column=col, value=val)
c.number_format = METRIC_FORMAT.get(metric, "General")
if row_fill:
c.fill = row_fill
total_val = consolidated[metric]["value"]
target_val = consolidated[metric]["target"]
total_cell = ws_summary.cell(row=row, column=5, value=total_val)
total_cell.number_format = METRIC_FORMAT.get(metric, "General")
total_cell.font = Font(bold=True)
if row_fill:
total_cell.fill = row_fill
# vs Target column: show % difference, color red if below
if target_val > 0:
pct_diff = ((total_val - target_val) / target_val) * 100
vs_cell = ws_summary.cell(
row=row, column=6,
value=pct_diff / 100
)
vs_cell.number_format = '+0.0%;-0.0%;0.0%'
# Returns: higher is worse. Everything else: higher is better.
if metric == "returns":
is_good = total_val <= target_val
else:
is_good = total_val >= target_val
vs_cell.font = Font(
bold=True,
color="27AE60" if is_good else "E74C3C"
)
if row_fill:
vs_cell.fill = row_fill
row += 1
# Column widths
ws_summary.column_dimensions["A"].width = 28
for col in ["B", "C", "D", "E", "F"]:
ws_summary.column_dimensions[col].width = 16
# --- Sheet 2: Regional Detail ---
for region in REGIONS:
ws = wb.create_sheet(title=f"{region} Region")
ws["A1"] = f"{region} Region — Week {regional_data['week_number']}"
ws["A1"].font = Font(bold=True, size=13, color="2C3E50")
ws.merge_cells("A1:D1")
ws.cell(row=3, column=1, value="Metric").font = Font(bold=True)
ws.cell(row=3, column=2, value="Actual").font = Font(bold=True)
ws.cell(row=3, column=3, value="Target").font = Font(bold=True)
ws.cell(row=3, column=4, value="Status").font = Font(bold=True)
for col in range(1, 5):
ws.cell(row=3, column=col).fill = header_fill
ws.cell(row=3, column=col).font = Font(
bold=True, color="FFFFFF"
)
row = 4
region_metrics = by_region.get(region, {})
for metric, label in METRIC_LABELS.items():
if metric not in region_metrics:
continue
val = region_metrics[metric]["value"]
tgt = region_metrics[metric]["target"]
ws.cell(row=row, column=1, value=label)
actual_cell = ws.cell(row=row, column=2, value=val)
actual_cell.number_format = METRIC_FORMAT.get(metric, "General")
target_cell = ws.cell(row=row, column=3, value=tgt)
target_cell.number_format = METRIC_FORMAT.get(metric, "General")
if metric == "returns":
on_target = val <= tgt
else:
on_target = val >= tgt
status_cell = ws.cell(
row=row, column=4,
value="On Target" if on_target else "Below Target"
)
status_cell.font = Font(
color="27AE60" if on_target else "E74C3C",
bold=True
)
row += 1
ws.column_dimensions["A"].width = 28
ws.column_dimensions["B"].width = 14
ws.column_dimensions["C"].width = 14
ws.column_dimensions["D"].width = 14
wb.save(output_path)
print(f" Excel report saved: {output_path}")
return output_path
# ---------------------------------------------------------------------------
# Step 3: Build the email
# ---------------------------------------------------------------------------
def build_report_email(
regional_data: dict,
consolidated: dict,
attachment_path: Path,
) -> MIMEMultipart:
"""Build the complete HTML email with Excel attachment."""
week_num = regional_data["week_number"]
today_str = date.today().strftime("%B %d, %Y")
# Inline summary table for the email body
table_rows = ""
for metric, label in METRIC_LABELS.items():
if metric not in consolidated:
continue
data = consolidated[metric]
val_str = format_metric_value(metric, data["value"])
tgt_str = format_metric_value(metric, data["target"])
if metric == "returns":
on_target = data["value"] <= data["target"]
else:
on_target = data["value"] >= data["target"]
status_color = "#27ae60" if on_target else "#e74c3c"
status_text = "On Target" if on_target else "Below Target"
row_bg = "#f9f9f9" if list(METRIC_LABELS.keys()).index(metric) % 2 else "#ffffff"
table_rows += f"""
<tr style="background:{row_bg};">
<td style="padding:8px 12px;">{label}</td>
<td style="padding:8px 12px;text-align:right;font-weight:bold;">{val_str}</td>
<td style="padding:8px 12px;text-align:right;color:#666;">{tgt_str}</td>
<td style="padding:8px 12px;color:{status_color};font-weight:bold;">
{status_text}
</td>
</tr>"""
body_html = f"""
<html>
<body style="font-family: Arial, sans-serif; color: #333; max-width: 680px;">
<div style="background:#2c3e50;padding:20px;">
<h1 style="color:white;margin:0;font-size:20px;">
Acme Corp — Week {week_num} Operations Report
</h1>
<p style="color:#bdc3c7;margin:5px 0 0;">Generated {today_str}</p>
</div>
<div style="padding:20px;">
<p>Hi Sandra (and team),</p>
<p>
Here is the consolidated operations summary for <strong>Week {week_num}</strong>.
The full regional breakdown is in the attached Excel file.
</p>
<h3 style="color:#2c3e50;border-bottom:2px solid #2c3e50;padding-bottom:5px;">
Company-Wide Summary
</h3>
<table border="0" cellpadding="0" cellspacing="0"
style="border-collapse:collapse;width:100%;font-size:14px;
border:1px solid #ddd;">
<thead>
<tr style="background:#2c3e50;color:white;">
<th style="padding:10px 12px;text-align:left;">Metric</th>
<th style="padding:10px 12px;text-align:right;">Actual</th>
<th style="padding:10px 12px;text-align:right;">Target</th>
<th style="padding:10px 12px;text-align:left;">Status</th>
</tr>
</thead>
<tbody>
{table_rows}
</tbody>
</table>
<p style="margin-top:20px;">
Regional breakdowns (West, East, Central) are in the attached
Excel file. Each region has its own tab.
</p>
<p>This report was generated automatically at 6:30 AM.
Please do not reply to this email —
contact <a href="mailto:priya@acmecorp.com">priya@acmecorp.com</a>
with any questions.</p>
<hr style="border:none;border-top:1px solid #eee;margin:20px 0;">
<p style="color:#999;font-size:12px;">
Acme Corp Operations | Automated Weekly Reporting System
</p>
</div>
</body>
</html>
"""
body_plain = f"""Acme Corp — Week {week_num} Operations Report
Generated {today_str}
Hi Sandra (and team),
Here is the consolidated operations summary for Week {week_num}.
The full regional breakdown is in the attached Excel file.
COMPANY-WIDE SUMMARY
"""
for metric, label in METRIC_LABELS.items():
if metric not in consolidated:
continue
data = consolidated[metric]
val_str = format_metric_value(metric, data["value"])
tgt_str = format_metric_value(metric, data["target"])
if metric == "returns":
status = "On Target" if data["value"] <= data["target"] else "Below Target"
else:
status = "On Target" if data["value"] >= data["target"] else "Below Target"
body_plain += f" {label:<30} {val_str:>12} (target: {tgt_str}) — {status}\n"
body_plain += (
"\nRegional breakdowns are in the attached Excel file.\n\n"
"This report was generated automatically. "
"Contact priya@acmecorp.com with questions.\n"
)
# Assemble message
msg = MIMEMultipart("mixed")
msg["Subject"] = (
f"[AUTO] Acme Corp — Week {week_num} Operations Report — {today_str}"
)
msg["From"] = (
f"{os.environ.get('EMAIL_FROM_NAME', 'Acme Reports')} "
f"<{os.environ['EMAIL_SENDER']}>"
)
msg["To"] = ", ".join(RECIPIENTS_TO)
msg["Cc"] = ", ".join(RECIPIENTS_CC)
msg["Reply-To"] = "priya@acmecorp.com"
alt = MIMEMultipart("alternative")
alt.attach(MIMEText(body_plain, "plain", "utf-8"))
alt.attach(MIMEText(body_html, "html", "utf-8"))
msg.attach(alt)
# Attach Excel file
with open(attachment_path, "rb") as f:
part = MIMEBase("application", "octet-stream")
part.set_payload(f.read())
encoders.encode_base64(part)
part.add_header(
"Content-Disposition",
"attachment",
filename=attachment_path.name,
)
msg.attach(part)
return msg
# ---------------------------------------------------------------------------
# Step 4: Send
# ---------------------------------------------------------------------------
def send_report(msg: MIMEMultipart) -> bool:
"""Send the report email. Returns True on success."""
all_recipients = RECIPIENTS_TO + RECIPIENTS_CC
try:
with smtplib.SMTP_SSL(
os.environ.get("EMAIL_SMTP_HOST", "smtp.gmail.com"),
int(os.environ.get("EMAIL_SMTP_PORT", "465"))
) as server:
server.login(
os.environ["EMAIL_SENDER"],
os.environ["EMAIL_PASSWORD"]
)
server.sendmail(
from_addr=os.environ["EMAIL_SENDER"],
to_addrs=all_recipients,
msg=msg.as_string()
)
print(f" Email sent to {len(all_recipients)} recipient(s)")
return True
except smtplib.SMTPAuthenticationError:
print(" ERROR: Authentication failed. Check .env credentials.")
return False
except smtplib.SMTPException as e:
print(f" ERROR: Could not send email: {e}")
return False
# ---------------------------------------------------------------------------
# Main
# ---------------------------------------------------------------------------
def main():
"""Run the full Monday report pipeline."""
today = date.today()
# ISO week number: week 1 is the week containing the first Thursday
week_number = today.isocalendar()[1]
year = today.year
print(f"Acme Corp — Monday Report Generator")
print(f"Date: {today.strftime('%A, %B %d, %Y')}")
print(f"Week: {week_number}")
print()
print("Step 1: Loading regional data...")
regional_data = load_regional_data(week_number - 1, year) # last week
print("Step 2: Aggregating metrics...")
consolidated = aggregate_metrics(regional_data)
print(f" {len(consolidated)} metrics aggregated")
print("Step 3: Generating Excel report...")
output_path = Path(
f"output/Acme_Week{week_number - 1:02d}_Report_{year}.xlsx"
)
output_path.parent.mkdir(parents=True, exist_ok=True)
generate_excel_report(regional_data, consolidated, output_path)
print("Step 4: Building and sending email...")
msg = build_report_email(regional_data, consolidated, output_path)
success = send_report(msg)
print()
print(f"Report pipeline complete. Status: {'SUCCESS' if success else 'FAILED'}")
if __name__ == "__main__":
main()
The Scheduling Setup
Priya creates a cron job on the Acme operations server:
# /etc/cron.d/acme-reports
# Run the Monday report at 6:30 AM every Monday
30 6 * * 1 priya /usr/bin/python3 /home/priya/reports/monday_report.py >> /var/log/acme-reports/monday.log 2>&1
On Windows, she would use Task Scheduler:
- Program: C:\Python310\python.exe
- Arguments: C:\reports\monday_report.py
- Schedule: Weekly, Monday, 6:30 AM
The Outcome
After an afternoon of writing and testing the script (emailing herself first, then using a test distribution list), Priya deploys it. The first Monday it runs automatically, Sandra's report arrives at 6:31 AM — before Sandra has even opened her laptop.
The numbers: - 90 minutes of manual work each Monday, eliminated - Over the course of a year: approximately 78 hours reclaimed - Error rate in the old process: Priya had made a formula error twice in the past year, sending incorrect numbers to the CFO. The script makes the same calculation every time.
Sandra's response after the first automated report: "This looks exactly like what you used to send. How did you get it here so early?" Priya's answer: "I didn't. Python did."
Key Lessons from This Case Study
Separation of concerns. The script has four clearly separated steps: load data, aggregate, generate Excel, send email. Each step can be tested independently. If the Excel generation has a bug, the email-sending code does not need to change.
Credentials never in code. The .env file holds all secrets. The script only refers to os.environ["EMAIL_SENDER"], never to a password string. The script file itself can be shared or committed to version control without exposing any credentials.
Test before scheduling. Priya's first run targeted only her own email. Her second run targeted a test distribution list. Only after confirming everything looked right did she set up the cron job targeting the real recipients.
Logging the output. The cron job redirects output to a log file. If the script fails on a Monday, Priya can check monday.log to see exactly what went wrong. Silent failures are the enemy of automated systems.
Fallback readability. Both the HTML and plain text versions are carefully constructed. Even if a recipient's email client cannot render HTML, the plain text version is formatted cleanly with aligned columns.