Automation

Automated reports: from the manual export to a dashboard that updates itself

delpra™ team5 min read

Every Monday, in thousands of companies, someone opens three systems, exports three files, pastes everything into a spreadsheet, fixes the formulas that broke and emails a report. In the meeting, half the time goes to arguing about whether the number is right. The next Monday, all over again.

The cost of the manual report

The report built by hand has three costs, and only the first is visible.

  • Hours. The time of whoever builds it, every week or every month, forever.
  • Delay. The number arrives days after the fact. The decision is made looking backward.
  • Trust. Every copy is a chance for error. After the second wrong number, nobody believes the report anymore, and decisions go back to intuition.

Building reports is a clear example of what Asana calls work about work: in its 2021 Anatomy of Work Index, the company estimated that about 60% of time in office work goes to activities around the task. We cover the topic in Work about work.

On the third cost, Gartner estimated, in 2020 research, that poor-quality data costs the surveyed organizations an average of US$12.9 million a year. Those are large companies, but the mechanism applies at any size.

The three levels of a report

You can think of the evolution in three steps. There is no need to jump straight to the last one.

Level 1: manual export

Someone downloads files and builds the spreadsheet. It works while the company is small and the questions are few.

Level 2: a spreadsheet that feeds itself

Data reaches the spreadsheet through integration, at a set time. The person stops building and starts checking and interpreting. It is a big leap for little investment.

Level 3: a dashboard connected to the source

The systems feed a single database, and a dashboard shows up-to-date numbers, with filters. There is no "Monday's report": there is an address everyone checks when they need it.

What to fix before building the dashboard

The dashboard tool is the easy part. What sinks data projects comes before it.

  1. Define the questions. A dashboard answers someone's questions. "How much did we sell by channel?", "where are orders stuck?". Start with five to eight questions that generate arguments today.
  2. Choose the source for each number. If two systems have "revenue", decide which one counts. That decision is for management, not for technology.
  3. Write the definitions. What is an active customer? Does a sale count at signing or at payment? One line per metric.
  4. Fix the record at the source. If the team does not fill in the CRM, the dashboard will precisely show wrong data. See How to build a CRM your team actually uses.
A beautiful dashboard on top of bad data only makes the error more convincing.

To choose which numbers deserve a place, it is worth reading Process metrics: what to measure beyond revenue.

How the automation works behind the scenes

Without getting into code, the data's path has four steps:

  • Collection. The systems deliver data through an API, at a set time or on every event. We explain these terms in API and webhook for managers.
  • Organization. The data goes to a single place and is standardized: dates in the same format, the same customer under the same name.
  • Calculation. The definitions you wrote become fixed rules. The number is always calculated the same way.
  • Display and alerts. The dashboard shows it, and alerts fire when something moves out of the expected range.

The alert is what changes the routine. Instead of someone looking at the dashboard every day to discover a problem, the dashboard tells you when the problem appears.

What a good dashboard has

  • A few numbers on the first screen. The ones that answer the main questions. Detail is one click away.
  • Comparison. A number alone says nothing. Show it against the previous period or against the target.
  • A visible last-updated date. So nobody decides with old data thinking it is new.
  • One owner per metric. Whoever explains it when the number changes.
  • Access for the people who do the work. A dashboard only for leadership becomes a tool for chasing people. Open to the team, it becomes a working tool.

And AI in all this?

Language models help at two ends: summarizing what changed in the period in plain text and allowing questions in natural language, such as "which customers reduced their orders this month?". Both uses depend on the same organized base. AI on top of messy data answers with confidence and gets it wrong all the same. More practical uses are in Generative AI in operations.

An example to picture it

Imagine a chain of three stores. Today, each manager sends a spreadsheet at the end of the month and the co-owner merges the three. With the sales systems feeding a single database, she opens the dashboard on Monday morning and sees sales, average ticket and idle stock by store, updated through Sunday. The meeting with the managers starts with "why did store two sell less?", not with "which number is right?".

Where to start

Take the report that causes the most work today. List where each column comes from and how long it takes to build. Then try to bring just that report up to level 2: data arriving on its own in a spreadsheet. It is a small project that shows the way and pays for itself in hours.

delpra™'s Operational Maturity Assessment has an entire dimension on data and metrics. In 4 minutes you see what level your company is at.

Written by the delpra™ teamProcesses, automation and AI so operations scale efficiently.

How mature are your operations?

The Operational Maturity Assessment takes 4 minutes and shows where to start.

Take the free assessment

Read next