An automated report system for a business has four parts: data sources (CRM, invoices, WhatsApp, spreadsheets), a layer that consolidates and cleans them, calculation of metrics defined in advance, and delivery of the result to the right people on a fixed schedule. You build it backwards: first decide which decision each report supports, and only then choose tools.
This guide describes how we approach that build at Automush, what to settle before touching any tool, and where these systems usually break.
How do you build a report system, step by step?
A report system is built in six steps: define the decisions each report serves, map the data sources, consolidate them in one place, define one formula per metric, choose a delivery channel, and add monitoring that alerts you when something breaks. The table shows what happens at each step and what comes out of it.
| Step | What you do | What you end up with |
|---|---|---|
| 1. Define the decisions | For each report, record who reads it, how often, and which decision it supports | A list of reports, each with an owner |
| 2. Map the sources | Identify where each number comes from: CRM, invoicing system, WhatsApp, spreadsheets | A source table with an access method for each |
| 3. Consolidate the data | An n8n workflow pulls data on a schedule and writes it to one place | Raw data in a central location |
| 4. Calculate the metrics | One formula per metric, including what counts as a “lead” or a “closed deal” | A calculation sheet that updates itself |
| 5. Deliver | Send by email or WhatsApp, or show in a dashboard | The report arrives without anyone asking for it |
| 6. Monitor | Alert on failed runs, a “last updated” stamp, and totals checked against the source | Numbers you can rely on |
Most of the real work sits in steps 1, 2 and 4, which are decisions rather than technology. Once it is clear who needs which number and what the number means, the technical connections are the more predictable part of the project.
Which reports should you build first?
Start with one report that someone in the business already assembles by hand every week, usually sales by lead source, open invoices, or incoming WhatsApp inquiries. A report like that already has an owner and familiar definitions, and the person who built it manually can tell at once when a new number looks wrong. Add the next report only after the first one runs reliably.
Before building, write one line for each metric: what it counts, which field in which system it comes from, and who owns the definition. Is a “lead” every inquiry, or only one logged in the CRM? Does “invoice paid” mean the issue date or the date the money arrived? If that argument is not settled up front, it appears after launch, when the automated report shows one number and the manual one shows another.
Financial reports, such as open invoices from Morning (Green Invoice), are a good starting point because they have a single clear source of truth to compare against.
Where should the report system store its data: Google Sheets or a database?
For most small and mid-sized businesses, Google Sheets is enough as the consolidation layer: an n8n workflow pulls data from each source and writes it to a dedicated spreadsheet, and the calculations and display are built from there. A database becomes necessary when data volume grows, when several processes write at the same time, or when you need long history and complex queries.
In both cases one rule saves a lot of trouble: keep raw data separate from calculations. Raw data is written only by the workflow, with a timestamp on every row, and nobody edits it by hand. Calculations and display live in separate tabs built on top of the raw data. When a number looks suspicious, you can go back to the original row and see where it came from and when it was written.
How should reports reach people: email, WhatsApp or a dashboard?
The channel depends on who reads the report and what they do with it. Owners who want a weekly picture get a short email summary; someone who needs an alert on one metric gets a WhatsApp message; and someone who needs to filter and explore the data works in a dashboard. You can combine them: a short WhatsApp summary with a link to the full dashboard.
Sending through the WhatsApp Cloud API has a limit worth knowing: a message the business initiates outside the 24-hour window after the recipient’s last message must be sent as a pre-approved template. A WhatsApp report is therefore built as a template with variable fields, not as free text.
If you need an interactive view, we have a separate guide on setting up a BI dashboard for your business.
What breaks when a data source goes down or the numbers don’t add up?
A report system built properly does not fail silently. In n8n you set up an error workflow that triggers when a run fails and alerts whoever maintains the system, and the report itself states plainly that a given source was not updated, instead of showing an old number as if it were current.
Three simple checks go with that:
- Update stamp: every report shows when data was last pulled from each source.
- Total comparison: the invoice total in the report is checked against the total in the invoicing system, and any gap is flagged.
- Run log: every run is recorded, so you can see what ran, when, and what failed.
The most common cause of failure is not a technical fault but a change: a field renamed in the CRM, an expired permission, or a file whose format changed. That is why the alert matters more than trying to guarantee nothing will ever go wrong.
How long does it take to set up a report system?
There is no fixed number, and the time depends on three things: how many data sources are connected, how clean and consistent the data is, and how settled the metric definitions already are in the business. One report from one tidy source is a small project. Several sources that define the same term differently are what stretch the timeline.
In practice the main wait is not the build but the decisions: who approves the formulas, who grants access to the systems, and who checks the result against what they know. A business that arrives with written definitions and permissions ready shortens the process more than any technology choice does.
When should you build it yourself, and when should someone build it for you?
You can build it yourself when there is one data source, one report, and someone in the business who knows n8n or Google Sheets and can maintain it. Bring in a professional when there are several sources, when the data is sensitive, or when several people rely on the report for decisions and a silent failure would be costly.
At Automush we build these systems in n8n, connect them to the CRM, Google Sheets, invoicing systems and WhatsApp, and add monitoring. One of our partners leads technology and AI at an Israeli bank and builds production systems under security and regulatory requirements, and that approach shapes the build: narrow permissions, credentials stored properly, and an alert on every failure. The system is built around the business’s definitions, not the other way round. More on this area is on our page about automated reporting and BI systems.
Frequently asked questions
Can AI write the report for me?
A language model, for example from OpenAI or Anthropic, can draft a short written summary on top of numbers that have already been calculated. The calculation itself stays in fixed formulas rather than with the model, because a formula always returns the same result and can be checked. It is also better to give the model only the data the summary needs, not the whole table.
What if my data lives in Excel files that arrive by email?
A workflow can read the file from the mailbox, parse it and write the rows to the central data layer. That works as long as the file structure is stable: the same columns in the same order. If whoever sends the file changes it by hand each time, fix the format or the data source first, and automate afterwards.
How do you keep the data secure in a report system?
Each connection gets the minimum permission it needs, and read-only access wherever possible. Credentials are kept in n8n's credential store, not inside the spreadsheet or in code. In WhatsApp messages you send summaries, not raw rows containing customer details.
Do you need a BI tool to get started?
No. You can start with Google Sheets and delivery by email or WhatsApp, which is enough for regular reports such as a weekly sales summary. A dedicated display tool earns its place when people need to filter and explore the data themselves. The data and formulas built beforehand carry over after the move.