What this automation does
On Monday morning, leadership gets a one-page report with the numbers that matter, what changed, and what needs a decision, and nobody spent Sunday night building it.
- Extract. On a schedule, the workflow pulls the week’s records from each source: orders from the store or ERP, jobs from field service software, tickets from the help desk, hours from timesheets and cash from QuickBooks Online.
- Load. Records land in Postgres or BigQuery, keyed by business week, with the last seven days re-pulled on every run to catch late entries.
- Compute. SQL views calculate each KPI from a written definitions file, for example on-time shipment rate, backlog, labor hours per unit and first-response time.
- Compare. Each KPI is compared with last week, the trailing four-week average and target. Values outside their normal band are flagged.
- Narrate. A model receives the KPI table, the flags and any notes managers submitted, and writes 150 to 250 words on what moved and what to ask about.
- Verify. Code checks that every number in the narrative appears in the KPI table, and that every source passed its completeness check.
- Review and send. A manager approves the draft in Slack. The report goes out by email and Slack with a link to the live dashboard.
When it is worth building
The signal is simple: someone senior spends half a day or more each week copying numbers from four or five systems into a spreadsheet or slide deck, and the numbers do not quite agree from one week to the next because the steps are done slightly differently each time.
It is not worth building if all your operating data lives in one system with decent built-in reports, or if what people really want is to explore data freely. For that, a dashboard or natural-language SQL reporting is the better tool. Sales-specific reporting is covered in our pipeline reporting guide. The weekly ops report works best as a short, fixed format that complements a dashboard rather than replacing it.
Architecture
| Layer | What we use | Why |
|---|---|---|
| Sources | QuickBooks Online, store or ERP, help desk, timesheets, Google Sheets | Read from the system of record, not from someone’s export |
| Store | Postgres, or BigQuery when data volume is large | One place to join data across systems and keep history |
| Metrics layer | SQL views plus a definitions file with owner per KPI | One agreed definition per number, versioned when it changes |
| Orchestration | n8n on a weekly schedule, with a daily refresh for late data | Handles API pagination, retries and alerts |
| Narrative | Claude Sonnet 5.5 | Clear, short writing from a structured table |
| Number check | Code step | Every figure in the text must match the source table |
| Delivery | Slack, email, and a Looker Studio dashboard link | Summary where people read, detail one click away |
Where to store the data depends on volume and who maintains it. Our Airtable vs Postgres comparison walks through that decision.
How to build it
- Agree on the KPIs and write their definitions. For each metric: formula, source, owner, target and what counts as normal variation. This step takes the longest and matters the most.
- Fix the week. Choose the week boundary (Monday to Sunday is common) and one business time zone. Many mismatches between manual and automated numbers come from this alone.
- Build the extracts. Pull incrementally, re-pull a rolling window to catch late or backdated entries, and record row counts per source per run.
- Backfill and reconcile. Load at least 12 weeks of history and compare against the reports your team built by hand. Investigate every difference. Usually the old report had an error, but not always.
- Write the narrative prompt. Rules: use only supplied figures, name no cause unless the data or a manager note supports it, mention every flagged metric, and keep it short.
- Add the number check and completeness gate. Block delivery if a source is missing or any narrative number fails to match.
- Collect manager notes. A short Friday form asks each department lead for anything notable. These notes give the narrative real context, such as a machine down for two days.
- Deliver and archive. Send the report and save each week’s KPI table so you can compare reports over time.
The downloadable starter template below gives you the scheduled extract, KPI comparison and Slack approval steps as a starting point.
What breaks in production
- Definition drift. Someone changes what “on time” means and the trend line jumps. Version the definitions file and note changes in the report.
- Partial data. One API call fails and revenue appears to halve. The completeness gate is the most important control in this workflow.
- Late and backdated entries. Last week’s numbers change after the report is sent. Show restatements explicitly instead of letting the numbers silently move.
- Invented causes. Models like to explain, for example blaming weather for a dip. Without supporting data or a manager note, the narrative should say the cause is unknown.
- Holidays and short weeks. A four-day week looks like a drop in every volume metric. Store a business-day count per week and normalize where it matters.
- Nobody reads it. Long reports get skimmed. Lead with what changed, keep the narrative short, and ask readers after a month what they actually use.
Timeline and cost
A report drawing from three to five systems typically takes two to four weeks, including the reconciliation period. Lumeor’s price for this is usually $4,000 to $12,000. The main cost drivers are the number of sources, how messy each source’s data is, and whether a dashboard is part of the work. Running costs are hosting plus a small amount of model usage once a week.
How to measure success
- Hours spent preparing the report each week, before and after
- Share of weeks the report goes out on time
- Corrections issued after a report was sent
- Unexplained differences between report figures and source systems
- Replies and follow-up questions from readers, as a sign it is read
- Days between a metric moving out of range and someone acting on it