The weekly report is often the first automation request because everybody can see the person assembling it on Friday. It is rarely the highest-cost workflow, but it is one of the best maps of the company’s information problems. Every export, copied cell and manual adjustment points to a definition or integration the operating systems do not share.
This composite workflow covers a management pack built from an ERP, CRM, service system and one or more spreadsheets. The goal is not to automate presentation while preserving untrusted numbers. It is to make every metric reproducible, surface data-quality exceptions and leave the analyst to explain what changed.
The workflow as it usually runs
- 1An analyst exports sales, orders, backlog, service and inventory data.
- 2They reshape each export to match a reporting workbook.
- 3They fix missing names, duplicate rows and category differences.
- 4They refresh formulas and compare totals with last week.
- 5Unexpected changes are checked against source systems.
- 6Charts are copied into a deck and commentary is written.
- 7A PDF or spreadsheet is emailed and becomes the week’s frozen version.
The seven opportunities
1. Inventory every source and owner
List the report field, source system, query or export, transformation and business owner. This is not busywork. A workbook can hide several definitions of revenue or backlog in different tabs. Automation needs one explicit lineage from source to displayed number.
2. Pull data on a schedule
Replace manual exports with APIs, database reads or controlled file intake. Preserve the extraction time and source version so the report can be reproduced. Where a system only provides files, monitor whether the expected file arrived rather than silently publishing last week’s data.
3. Standardize the shared dimensions
Customer, product, territory, salesperson and status names have to mean the same thing across sources. Build mapping tables with an owner and an exception list. Fuzzy matching can propose a mapping, but an unexplained merge between two customer records should never become permanent automatically.
4. Encode calculations and definitions once
Move workbook formulas into versioned, tested transformations. Define whether cancelled orders count, which date assigns a sale to a week and how returns are treated. The report becomes trustworthy when two people running it from the same source produce the same number.
5. Reconcile before publishing
Automated checks should compare row counts, control totals and key balances with the systems of record and recent history. Missing feeds, large jumps and impossible values stop publication or create a review task. A green pipeline that delivered the wrong number is not a successful run.
6. Direct attention to anomalies, not every number
Calculate week-over-week movement, target variance and agreed thresholds, then show the few metrics that require explanation. This is ordinary analysis. AI can draft a narrative from verified figures, but it should cite those figures and a person should own the business explanation.
7. Publish a live view and a frozen record
Management benefits from current data, while governance benefits from knowing what was presented at the time. Provide a live dashboard for operations and retain the weekly snapshot, definitions and commentary. The email can link to both instead of carrying the only copy.
What we would build first
Pick three decisions the report supports and trace only the metrics needed for them. Automate extraction, transformations and control checks for that slice. Keep the existing pack in parallel until the totals reconcile for several cycles. Expanding a trusted small report is safer than reproducing every inherited tab at once.
The expensive part is not Friday afternoon
The larger cost is steering the business from a picture that is days old or not trusted. Measure report preparation time, but also measure data age and how often a number is challenged after publication.
Return to the seven distributor workflows and choose the next process to inspect.