Automated Reporting
Four scheduled reporting streams running on top of one shared Python framework: a monthly board pack, a weekly retailer report, a weekly BDM portfolio, and a weekly commercial SMT dashboard. I designed, built, and deployed the entire framework end to end myself — data layer, scheduling, rendering, AI commentary, deployment and admin tooling.
What I built
Before this existed, every report in the business was either a manual spreadsheet exercise or a one-off script. Definitions drifted between people. Reports landed late, or sometimes not at all. There was no shared way to query Salesforce, no shared formatting layer, and no shared scheduling.
I designed and built the whole framework solo, from scratch: the SOQL extraction layer, the date and period maths, the bucketing rules, the HTML and Word renderers, the AI commentary integration, the cron auto-sync, the EC2 deployment, and the Vercel-hosted admin dashboard for toggling reports and managing recipient lists. Every report below sits on the same primitives, which is why a new one can be authored as a single Python file under /reports and picked up by the scheduler automatically.
Architecture
Each report is a Python class inheriting from BaseReport. The framework provides shared modules: framework/salesforce.py for SOQL with retry and pagination, framework/dates.py for period maths (Last Week, MTD, QTD, YTD and matching previous-period windows), framework/bucketing.py for product classification and lender name normalisation, framework/formatting.py for currency, percentage and traffic-light helpers, and Jinja2 templates with shared macros for KPI cards, period tables, lender splits and the pipeline filter view.
Reports run on a t3.small EC2 instance under cron. The crontab is auto-generated from each report's declared schedule by scripts/sync-cron.py at deploy time, so adding a new report needs zero ops work. Output is delivered through the Microsoft Graph API to recipient inboxes; AI commentary is generated by Claude via the Anthropic API, with three retries on overload errors and a graceful fallback to a generic body so a slow LLM never blocks the report. SOQL volume sits at roughly five percent headroom under Salesforce's 705,000-call daily limit, peaking on Monday mornings when the three weekly reports run back to back.
The admin dashboard is a static HTML + serverless proxy on Vercel that lets the commercial team toggle reports, edit recipient lists, run test sends, and trigger discovery of newly-onboarded retailers — all without my involvement. Credentials never leave EC2; the dashboard authenticates via a per-environment admin key.
The four reports
Each report has its own example below. The samples are fully-rendered versions of the live HTML output but with completely fictional data — invented company, invented BDMs, invented retailers, invented numbers. The structure, styling and behaviour all match what subscribers actually receive in production.
Executive month-end pack covering 25+ metrics across 17 charts, with AI-generated commentary on every section and a full methodology appendix. Lands on the first working day of the month, replacing what used to be a five-to-seven-day manual exercise where three people were calculating the same metric three different ways.
A self-contained HTML report sent to every active retailer's primary contact. KPI bar, period tables (Last Week / MTD / QTD / YTD), lender splits with vs-prev comparisons, and a pipeline filter view for “Approved but Not Signed” and “Signed but Not Paid”. AI commentary nudges retailers on submission volume, approval rate and pipeline progression.
Each BDM gets a portfolio rollup of every retailer they manage. A dropdown switches between consolidated portfolio totals and a single retailer drill-down. AI commentary calls out volume drops, inactive retailers and pipeline opportunities, calibrated for a relationship-management audience rather than the retailer.
A two-tab dashboard for the SMT covering the entire retailer book regardless of toggles or status. The Overview tab carries independent BDM + retailer dropdowns and a pipeline filter view; the Retailer Table tab is a sortable league table of every retailer with a period switch (Last Week / MTD / QTD / YTD). AI commentary contextualises business-wide trends.