Case Study 36-2: Maya Automates Her Client Status Reports

The Situation

Maya Reyes bills at $175 an hour. At that rate, spending half a day each month producing client status reports is a real cost — not a metaphor, but actual money left on the table. Eight clients at twenty minutes each is less than three hours, which sounds manageable until you add the time it takes to pull the numbers from her tracking spreadsheet, verify that the hours match the invoices, format everything consistently across all eight clients, save and send eight separate PDFs, and respond to the inevitable follow-up email from one client who either didn't receive theirs or wants a different format.

Maya has been tracking her projects in a CSV file since Chapter 9 — date, client, project, hours, billable rate, invoice status. By this point in her practice, that CSV is a genuine operational record. The data is there. It just needs to move from the CSV to a professional-looking PDF in her clients' inboxes without her doing it manually.

Her goal: on the last day of each month, every client receives a status report showing their project progress, hours billed this month, invoices outstanding, and upcoming deliverables — automatically, professionally formatted, personalized to each client.

Data Sources

Maya's report pipeline draws from three sources she already maintains:

projects.csv — Project registry with start dates, target completion dates, current status, and total contracted hours.

time_tracking.csv — The record from Chapter 9, with columns: date, client, project_code, hours, billable, description.

invoices.csv — Invoice history: invoice_id, client, date_issued, amount, due_date, paid_date, status.

The report needs data from all three, joined on the client name and project code.

The Report Structure

Each client receives a personalized PDF with their company name on the cover, covering:

Section 1: Month Summary
  - Hours worked this month
  - Amount billed this month
  - Invoice status (paid, outstanding, overdue)

Section 2: Project Status
  - Each active project with a progress bar
  - Hours used vs. contracted
  - Target completion date and current status

Section 3: Upcoming Deliverables
  - Next milestones across all projects
  - Any items awaiting client review or decision

Section 4: Invoice Detail
  - Itemized hours by project
  - Outstanding invoices with due dates
  - Running total for the year

The cover page includes a personal note — a single sentence about the relationship that Maya writes once per client per year and stores in a client_notes.json file. Everything else is generated from the data.

The Technical Approach

Maya structures her report script around a ClientReport class:

@dataclass
class ClientReport:
    """
    Data container for one client's monthly status report.

    Attributes:
        client_name: Legal name of the client company.
        contact_name: Primary contact's name for the salutation.
        report_month: The reporting period as a date object.
        hours_this_month: Total billable hours for the period.
        amount_billed: Dollar amount billed this period.
        projects: List of active project status dictionaries.
        invoices: List of invoice records for this client.
        upcoming_deliverables: List of upcoming milestone strings.
        personal_note: Optional personal note from client_notes.json.
    """
    client_name: str
    contact_name: str
    report_month: date
    hours_this_month: float
    amount_billed: float
    projects: list[dict]
    invoices: list[dict]
    upcoming_deliverables: list[str]
    personal_note: str = ""

    @property
    def outstanding_balance(self) -> float:
        """Total unpaid invoice amount."""
        return sum(
            inv["amount"] for inv in self.invoices
            if inv["status"] in ("outstanding", "overdue")
        )

    @property
    def has_overdue_invoices(self) -> bool:
        """True if any invoices are past their due date and unpaid."""
        today = date.today()
        return any(
            inv["status"] == "outstanding"
            and date.fromisoformat(inv["due_date"]) < today
            for inv in self.invoices
        )

The build_client_report() function loads the CSVs, filters to the specific client and month, and constructs a ClientReport object:

def build_client_report(
    client_name: str,
    report_month: date,
    data_dir: Path,
) -> ClientReport:
    """
    Construct a ClientReport from the tracking CSV files.

    Args:
        client_name: Name of the client to report on.
        report_month: First day of the month to report.
        data_dir: Directory containing the CSV tracking files.

    Returns:
        Populated ClientReport ready for rendering.

    Raises:
        ValueError: If client_name is not found in the time tracking data.
    """
    import pandas as pd

    # Load all data
    time_df = pd.read_csv(data_dir / "time_tracking.csv", parse_dates=["date"])
    projects_df = pd.read_csv(data_dir / "projects.csv")
    invoices_df = pd.read_csv(data_dir / "invoices.csv", parse_dates=["date_issued", "due_date"])

    # Filter to this client and this month
    month_start = date(report_month.year, report_month.month, 1)
    if report_month.month == 12:
        month_end = date(report_month.year + 1, 1, 1) - timedelta(days=1)
    else:
        month_end = date(report_month.year, report_month.month + 1, 1) - timedelta(days=1)

    client_time = time_df[
        (time_df["client"] == client_name) &
        (time_df["date"].dt.date >= month_start) &
        (time_df["date"].dt.date <= month_end) &
        (time_df["billable"] == True)
    ]

    if client_time.empty and not any(projects_df["client"] == client_name):
        raise ValueError(f"No data found for client: {client_name}")

    hours_this_month = client_time["hours"].sum()
    amount_billed = hours_this_month * 175.0  # Maya's hourly rate

    # Build project status list
    client_projects = projects_df[projects_df["client"] == client_name].to_dict("records")

    # Build invoice list
    client_invoices = invoices_df[
        invoices_df["client"] == client_name
    ].sort_values("date_issued", ascending=False).head(6).to_dict("records")

    # Load personal note
    notes_path = data_dir / "client_notes.json"
    personal_note = ""
    if notes_path.exists():
        import json
        notes = json.loads(notes_path.read_text())
        personal_note = notes.get(client_name, "")

    return ClientReport(
        client_name=client_name,
        contact_name=...,  # From a client registry
        report_month=month_start,
        hours_this_month=round(hours_this_month, 1),
        amount_billed=round(amount_billed, 2),
        projects=client_projects,
        invoices=client_invoices,
        upcoming_deliverables=[],  # Loaded from a separate milestones file
        personal_note=personal_note,
    )

Rendering and Delivery

The rendering step is clean because Maya's data layer and presentation layer are fully separated:

def generate_all_client_reports(report_month: date) -> list[Path]:
    """
    Generate monthly status reports for all active clients.

    Args:
        report_month: First day of the reporting month.

    Returns:
        List of paths to generated PDF files.
    """
    data_dir = Path("data")
    output_dir = Path("reports") / report_month.strftime("%Y-%m")
    output_dir.mkdir(parents=True, exist_ok=True)

    # Get list of active clients
    import pandas as pd
    clients = pd.read_csv(data_dir / "clients.csv")
    active_clients = clients[clients["status"] == "active"]["name"].tolist()

    generated_paths = []
    for client_name in active_clients:
        try:
            report = build_client_report(client_name, report_month, data_dir)
            pdf_path = render_and_save_report(report, output_dir)
            generated_paths.append(pdf_path)
            print(f"  Generated: {client_name} → {pdf_path.name}")
        except Exception as exc:
            print(f"  ERROR generating report for {client_name}: {exc}")
            # Continue generating other clients' reports even if one fails

    return generated_paths

The key design decision is the try/except around each client's generation. If one client's data has a problem, Maya still gets all the other reports. She gets an error message she can address for the one client, rather than losing all reports because of one bad record.

The Outcome

The first month Maya ran the automated reports, she generated eight client PDFs in 23 seconds. She reviewed them — they were accurate, they looked professional, they contained the right data for each client — and sent them with a simple email script that attached each PDF to a personalized email.

One client, a financial services firm that had previously asked for more detail, sent back an unprompted reply: "This is the clearest status report we've received from any vendor. Really helpful." Maya had added a project hours utilization bar — a small visual showing percentage of contracted hours used — specifically because that client had once mentioned being concerned about budget tracking. The automated report was able to include that detail consistently because the logic was written once and applied every month.

The practical upshot: three to four hours of report production time per month converted to twenty-three seconds of computation and fifteen minutes of review. Maya now uses those hours on billable work, which at $175 per hour represents a meaningful monthly gain in either revenue or free time — her choice.

The system she built in Chapter 9 (tracking in a CSV), combined with the email automation from Chapter 19 and the scheduling from Chapter 22, and now Chapter 36 (automated reporting), have together eliminated most of the administrative overhead that used to follow Maya into evenings and weekends.

What Maya Would Add Next

Maya has a list of enhancements she plans to tackle when time permits:

Automated data ingestion: Her time tracking CSV is still updated manually. Toggl (her time tracking tool) has an API — she could write a script that pulls last month's data automatically before running the reports.

Invoice status updates: Her invoice statuses are also updated manually. Connecting to her accounting software's API would close this loop.

Client-specific thresholds: One client always wants a note when budget utilization exceeds 70%. Another wants to know when a deliverable is more than a week late. These could be configured in the client config file and added as conditional blocks in the template.

A BCC to herself: Maya adds herself as a BCC to every outgoing report email. This gives her a simple archive of what each client received, when — useful if a client ever claims they did not receive a report.

None of these enhancements require new concepts beyond what this chapter covers. They are applications of the same pipeline pattern with additional data sources.

The Core Pattern

Maya's case demonstrates the key value proposition of automated reporting at the individual professional level: the automation does not eliminate judgment or expertise. Maya still writes the personal notes. She still assesses project health. She still designs the engagement.

What the automation eliminates is the execution: the repetitive, error-prone, time-consuming work of moving data from spreadsheets into formatted documents. That work has no value for Maya's clients. It is pure overhead.

The code for the complete client report pipeline follows the same six-stage structure from the chapter: load data, calculate metrics, generate charts (in this case, simple progress indicators rather than complex charts), render template, save output, deliver by email. The concepts are identical to Priya's board report pipeline. The template and data structures are different, because the business context is different.

This is the point of the pipeline pattern: it is a general structure that adapts to any recurring report in any business context.