Case Study 36-1: Priya Builds the Acme Board Report Generator

Characters

Priya Sharma — Senior Data Analyst at Acme Corp. Recently promoted; one of her first projects in the new role is eliminating the manual report process.

Sandra Chen — COO of Acme Corp. Receives the board report each month and relies on the executive summary heavily.

Marcus Webb — Head of Operations. One of eleven regional managers who receives the report.

The Situation

Priya Sharma had been generating Acme Corp's Monthly Business Review report since her analyst days. The process had always worked the same way: pull data from four different Excel exports, paste it into the master template in a specific order (because if you paste regions in the wrong order the chart ranges break), manually update the month label in the header, regenerate each chart by right-clicking and refreshing, check that the conditional formatting hadn't gone haywire, export to PDF, and email to eleven regional managers.

On a good month it took two hours. On a month when a column had shifted in one of the exports, or when someone had left a filter on the source spreadsheet, it took four hours and involved at least one phone call to Marcus Webb in IT asking what happened to the Southeast data.

Priya's recent promotion to Senior Analyst gave her something valuable: the authority to change the process. Her first project in the new role was automating this report completely.

Her goal: the report should generate itself on the first of every month at 6:00 AM, contain all the standard sections plus any month-specific alerts, and land in every regional manager's inbox before they arrive at work.

What Priya Built

Priya structured the solution as three components working in sequence:

Component 1: Data retrieval. A Python script that queries Acme Corp's SQL Server database for the prior month's data — revenue by region, units by product, customer acquisition and retention metrics. The query runs in about four seconds and returns exactly the aggregated data the report needs.

Component 2: Report generation. Using Jinja2 for HTML templating and WeasyPrint for PDF conversion, the script renders the full business review report. Charts are generated with matplotlib and embedded as base64 images. An additional Excel version is produced with openpyxl for the finance team.

Component 3: Distribution. The completed PDF is attached to an HTML email. The email body contains the executive summary — pulled from the same data, not re-typed — so recipients get the key numbers in the email preview without needing to open the attachment.

The Report Structure

Priya modeled the automated report on what had always worked in the manual version, but she added a few things that were too labor-intensive to do manually:

Page 1: Executive Summary
  - Auto-generated bullet points: revenue vs. target, top performer, concern areas
  - KPI dashboard: 5 metrics with traffic-light color coding
  - Generation timestamp and data as-of date

Page 2: Revenue Performance
  - 7-month trend line (current month + 6 trailing months)
  - Regional comparison: current month vs. prior month
  - Target vs. actual gauge for total revenue

Page 3: Regional Detail
  - Full table: revenue, units, average order value, new customers, YoY change
  - Sorted by revenue descending
  - Automatically flags regions with YoY decline > 3% with a caution icon

Page 4: Product Performance
  - Top 10 products by revenue
  - Top 10 products by units sold
  - Month-over-month change for each

Page 5: Customer Metrics
  - New customer acquisition vs. prior month
  - Retention rate with 6-month trend
  - Geographic distribution of new customers

Appendix: Methodology and Data Sources

The alert logic was Priya's addition — something that was impossible to do consistently in the manual process. Any region with a YoY revenue decline greater than 3% automatically generates a "Regions Requiring Attention" callout box on page 3. Any month where new customer acquisition beats target by more than 15% triggers a "Growth Spotlight" section. The logic is rules she and Sandra agreed on; the execution is automatic.

The Auto-Generated Executive Summary

The hardest part was getting the executive summary right. Sandra Chen reads the executive summary and nothing else if she's in a hurry. The summary has to be accurate, use the right language, and not read like it was written by a script.

Priya wrote the summary generator as a function that builds sentences from templates based on the data:

def generate_executive_summary(data: dict) -> str:
    """
    Generate the executive summary as HTML bullet points.

    Each bullet point is constructed from a sentence template
    with values filled in from the report data. The tone
    adapts based on whether targets were met.
    """
    bullets = []

    # Revenue performance bullet
    variance_pct = (
        (data["total_revenue"] - data["revenue_target"])
        / data["revenue_target"] * 100
    )
    if variance_pct >= 5:
        bullets.append(
            f"Revenue of <strong>${data['total_revenue']:,.0f}</strong> exceeded "
            f"target by <strong>{variance_pct:.1f}%</strong>, the strongest month "
            f"in the trailing six-month period."
        )
    elif variance_pct >= 0:
        bullets.append(
            f"Revenue of <strong>${data['total_revenue']:,.0f}</strong> met target "
            f"({variance_pct:+.1f}%). Performance was in line with expectations."
        )
    else:
        bullets.append(
            f"Revenue of <strong>${data['total_revenue']:,.0f}</strong> fell "
            f"<strong>{abs(variance_pct):.1f}% below target</strong>. "
            f"Regional detail on page 3."
        )

    # Add remaining bullets for top region, customer metrics, margin
    # ...

    items_html = "\n".join(f"<li>{b}</li>" for b in bullets)
    return f"<ul class='executive-bullets'>{items_html}</ul>"

After two iterations, Sandra told Priya the summary read "like a real person wrote it." Priya took that as a win.

Distribution and Scheduling

Priya used Windows Task Scheduler (Acme runs Windows servers) to run the script on the first of every month at 6:00 AM. The script:

  1. Checks if today is the first of the month before doing anything
  2. Calculates the prior month's date range
  3. Queries the database
  4. Generates HTML, PDF, and Excel outputs
  5. Sends emails to the distribution list (stored in a config file, not hardcoded)
  6. Writes a completion log entry with timestamp, file paths, and recipient count

If any step fails, the script logs the error with a full traceback and sends an alert email to Priya (not the regional managers). The regional managers never see a failed report; they either get a complete report or silence while Priya investigates.

The Outcome

The first automated report went out on February 1st. Eleven regional managers received it at 6:02 AM. The two minutes of overhead was database query time and PDF rendering.

Marcus Webb's reaction: "I got the report before I got to the office. I didn't even know it was automated until Priya mentioned it."

Sandra Chen's reaction: "The executive summary is better than it used to be. The old one just listed numbers. This one actually tells me what happened."

Priya's own accounting: the two hours of manual work per month reduced to zero. The four-hour months — when something broke — also reduced to zero, because the automated script either runs correctly or alerts Priya before any incorrect output goes out.

Over the course of a year, that's a conservative estimate of 24 to 48 hours of work recovered and redirected to analysis that actually required Priya's judgment.

Technical Note: The SQLite Development Pattern

Priya developed against a SQLite copy of the production database during development, then switched the connection string for production deployment. This pattern — develop against a lightweight local database, deploy against the real one — is standard practice and prevented any risk of running half-built code against live data.

import os

DATABASE_URL = os.environ.get(
    "ACME_DATABASE_URL",
    "sqlite:///dev_acme_data.db"  # Fallback for local development
)

In production, the environment variable is set on the server. In development, the fallback SQLite path is used. The report code itself never changes.

The Template Design Process

Priya spent the most time on the template itself. She interviewed Sandra and three regional managers about what they actually read in the current report. The findings surprised her:

  • Every manager reads the executive summary (page 1).
  • About 60% read the revenue trend chart.
  • Fewer than 30% scroll past page 2 regularly.
  • Nobody reads the appendix.

This shaped her template decisions: page 1 had to carry the full story. Charts on page 2 needed to be visually self-explanatory. Pages 3+ were "I need to investigate" pages, not "I read this every month" pages.

The template uses Jinja2 conditionals to produce different executive summaries based on data patterns:

{# Jinja2 template excerpt — executive summary logic #}
{% if metrics.revenue_change_pct >= 5 %}
    Revenue of <strong>{{ metrics.revenue_total | currency }}</strong>
    exceeded target by <strong>{{ metrics.revenue_change_pct | pct }}</strong>,
    the strongest result in the trailing six-month period.
{% elif metrics.revenue_change_pct >= 0 %}
    Revenue of <strong>{{ metrics.revenue_total | currency }}</strong>
    met target ({{ metrics.revenue_change_pct | pct }}).
    Performance was in line with expectations.
{% else %}
    Revenue of <strong>{{ metrics.revenue_total | currency }}</strong>
    fell <strong>{{ (metrics.revenue_change_pct * -1) | pct }} below target</strong>.
    Regional detail appears on page 3.
{% endif %}

After two rounds of feedback, Sandra told Priya the executive summary "reads like a real person wrote it." Priya took that as a win, with a minor caveat: the sentence templates need to be reviewed when business conditions change significantly. A template written for normal growth conditions sounds wrong during a major downturn. Priya added a comment in the template file reminding whoever edits it next to check the language if market conditions shift.

The Scheduling Configuration

Priya used the Windows Task Scheduler because Acme runs Windows servers. She did not use the schedule Python library because the server needed to restart the job independently if it crashed — something Task Scheduler handles automatically.

The task configuration: - Trigger: Monthly, on day 1 of each month, at 06:00 AM - Action: python C:\acme\reports\run_monthly_report.py - Conditions: Wake the computer to run this task (server is in sleep mode overnight) - Settings: If the task fails, restart every 30 minutes, up to 3 times

The script itself includes a guard check as a safety net:

from datetime import date
import sys

def main():
    today = date.today()
    if today.day != 1:
        print(f"Today is {today}. Not the 1st — exiting.")
        sys.exit(0)

    # Proceed with report generation...

This means if Task Scheduler misfires (which does happen), the script will exit cleanly rather than generate a report for the wrong month.

Error Handling and Alerting

Priya's most important design decision: regional managers should never see a failure. If something goes wrong, they receive nothing; Priya gets an alert.

import traceback
import smtplib
from email.mime.text import MIMEText

def run_with_error_handling():
    try:
        run_monthly_report()
    except Exception as e:
        error_body = (
            f"The monthly report FAILED to generate.\n\n"
            f"Error: {type(e).__name__}: {e}\n\n"
            f"Traceback:\n{traceback.format_exc()}\n\n"
            f"The report was NOT sent to the distribution list.\n"
            f"Please investigate and re-run manually if needed."
        )
        # Alert email goes to Priya only
        send_alert_email(
            to="priya.sharma@acmecorp.com",
            subject=f"REPORT FAILED: Monthly Business Review {date.today()}",
            body=error_body,
        )
        # Log the failure
        with open("report_log.txt", "a") as f:
            f.write(f"{date.today()} FAILED: {type(e).__name__}: {e}\n")
        raise  # Re-raise so the stack trace appears in Task Scheduler logs

if __name__ == "__main__":
    run_with_error_handling()

In the first year of operation, the report failed twice: 1. The database server was offline for scheduled maintenance on February 1st. Priya got the alert at 6:02 AM, rescheduled the task for 8:00 AM, and the report went out two hours late. 2. A schema change in the sales database removed a column the query depended on. Priya got the alert, updated the query in ten minutes, and triggered a manual re-run.

In both cases, no regional manager received an incomplete or incorrect report.

The Numbers After One Year

After twelve months of automated reports:

  • Time saved: 24-48 hours of analyst labor per year (based on pre-automation tracking)
  • Error corrections: Zero. The manual process generated 2-3 correction emails per year.
  • Stakeholder trust: Sandra Chen added the automated report to the standard board meeting prep materials. It had never been there before — the manual process was too unreliable to be a primary source for the board.
  • Unexpected benefit: Because the template was versioned in Git, when Sandra asked "can you show me what the September report said about Northeast revenue?", Priya could pull up the exact HTML file from the output archive and answer in thirty seconds.

What the Code Looks Like End-to-End

The complete script is approximately 350 lines including all functions, error handling, and the email delivery code. The template HTML is another 200 lines. For a project that saves two hours per month indefinitely, that is an extremely favorable ratio of implementation time to ongoing value.

The full working implementation is in code/report_generator.py. The template is in code/templates/monthly_report.html. Together, they implement exactly the pipeline described in this case study.