How do you automate the weekly report?

Somewhere in your business there's a report that one person builds every week. They export from the CRM, pull a trial balance from accounting, open the support tool, paste it all into a spreadsheet, fix the formulas that broke since last time, and send it out around lunchtime. If they're off, it's late or it doesn't go. If they leave, nobody knows how it was built.

The report matters. It's how you run the week. But the building of it is a job for a system, and the person doing it has better things to do with Monday morning.

What has to be true before a report can build itself

A report assembles itself when three things are settled. None of them are technical.

Every number has one definition

What counts as an open opportunity? Does revenue mean invoiced, paid or booked? Is a ticket resolved when the agent closes it or when the customer confirms? The person who builds the report today has answered these in their head, and sometimes differently from week to week. Write each definition down in a sentence. This document is the specification, and it's worth more than the automation.

Every number has one source

Pipeline comes from the CRM. Cash comes from accounting. Response times come from the support tool. If a number currently comes from a spreadsheet that someone updates by hand, decide whether that spreadsheet is the real source or a copy of something else. If it's a copy, go to the original. If it's the real source, that's a finding in itself.

The systems agree with each other

If the CRM has forty active customers and accounting has forty-three, the report has to pick one, and the reader will spend the meeting on the difference instead of the decision. A report that pulls from systems directly makes these gaps visible immediately. That's uncomfortable for a fortnight and then it's fixed, usually by connecting the systems so the records stay in step.

How the automated report works

On the agreed morning, before anyone is in, the system queries each source for the numbers on the list, applies the definitions, lays them out on the same page as last week, and sends it. The page shows each number beside last week's and the trend over a few months, because a number on its own tells the reader nothing. Anything that moved more than usual is marked.

Then a person reads it. This is the part that doesn't automate and shouldn't. The owner of the report looks at what moved, asks why, and writes four lines of commentary at the top: what happened, what it means, what we're doing about it. That used to be the ten minutes they had left after three hours of building. Now it's the whole job, and the commentary gets better because the person has time to think.

Keep the layout fixed. The temptation with an automated report is to add a page for every question anyone has ever asked. Resist it. The value of a weekly report is that the reader knows where to look and notices what's different. Questions that come up once get answered once, by someone with access to the data, not by a new permanent section.

Decide where the report lands as carefully as what's in it. One version goes to the people who run the week, by email or into the channel where Monday's conversation already happens. A fuller version with the detail behind each number sits where the owner can open it. Nobody should have to go looking for the report, and nobody should receive a version with more in it than they'll read.

When a number can't be trusted yet

Some numbers won't be ready on the first week. The source is a spreadsheet, or the definition hasn't been agreed, or two systems disagree and nobody has decided which is right. Don't leave them off, and don't quietly include them. Show them on the page with a mark that says this number is still being confirmed, and a line on what's being done about it. The reader sees the whole picture, including the parts that are still soft, and the mark is a weekly reminder to fix the source.

This also handles the awkward period when the business changes how it counts something. The old definition and the new one can appear side by side for a few weeks, so nobody mistakes a change in method for a change in the business.

The numbers worth including

Every business has its own list, but the ones that come up most often across sales, operations and service are these.

• Sales: new enquiries this week, how quickly they were answered, opportunities opened and closed, pipeline by stage, quotes outstanding and their age.
• Operations: orders or jobs received and completed, the backlog, anything past its due date, invoices waiting for approval, exceptions open and their age.
• Service: requests received and resolved, time to resolve, requests escalated, anything open past the agreed time.
• Finance: cash position, invoiced this week, overdue receivables by age, and the handful of costs that move.

Pick fewer than you think. A page the reader finishes is worth more than a pack they skim. If a number has not changed anyone's decision in three months, take it off.

Keeping the report trusted

A report people act on is one they believe. The automated version earns that in three ways.

• Every number can be traced. Click on it, or ask, and you get the query that produced it and the records behind it. Nobody has to take the figure on faith, and the argument about whether it's right ends at the source.
• Failures are loud. If a source can't be reached, the report says so in the place the number would have been. It doesn't silently show last week's value or a zero.
• Definitions change on purpose. When the business decides to count something differently, the definition document changes, the report changes, and the change is noted on the page that week so the trend isn't misread.

How to start

1. Take the current report and, beside each number, write where it comes from and what it means. Mark the ones that come from a hand-kept spreadsheet.
2. Time how long the report takes to build this week, including the fixing. That is your baseline.
3. Automate the numbers with a clean source first. Leave the spreadsheet ones in by hand for now, clearly marked.
4. Run the automated report alongside the hand-built one for a few weeks and reconcile the differences. Every difference is either a definition to fix or a record to fix.
5. Retire the hand-built version. Fix the spreadsheet sources when you can.

What to measure

• Time to produce the weekly report, from the hours it took to the minutes it now takes to read and annotate.
• Whether it arrives on time, every week, including the weeks the owner is away.
• Errors found in the records through the report, which fall over the first months as the sources are cleaned.

Reporting sits under seeing the numbers without building the report on our Operational Efficiency page. If you'd like to talk through what your weekly report contains and where its numbers come from today, bring it to the first call.