How do I automate my reporting and dashboards?

Question

VX-N · Accepted Answer

The obstacle is almost never the dashboard, it is that the underlying numbers live in four systems and disagree. Fix the source of truth first; a dashboard built over inconsistent data just distributes the disagreement faster. Start from the three decisions you actually make each week and build only the numbers that inform them. Dashboards showing everything get checked once and then ignored. The failure mode of reporting projects is almost never technical — it is a wall of charts nobody's Monday depends on. Work backwards from the decision: if the number moved, what would you do differently? A metric with no answer to that question is decoration, and decoration is what turns dashboards into wallpaper. Before any chart exists, write down the definition of each number — one definition, owned by one person. Most reporting arguments are definition arguments wearing a data costume: 'revenue' that means booked in one system and collected in another, an 'active customer' with three competing meanings, two teams counting the same week differently because one uses calendar weeks and the other uses a rolling seven days. When two reports disagree, it is almost always because they answer subtly different questions — and the fix is a written definition, not a better chart. The plumbing has its own traps. Timezone and date boundaries quietly corrupt comparisons — a 'day' cut at UTC midnight versus local midnight moves deals across days and makes month-ends disagree with the accounting system. Some numbers must be snapshotted, not recomputed: pipeline value 'as of March 1' has to be captured on March 1, because recomputing it later from live records silently rewrites history as deals change. And test data has to be excluded at the source, or the demo accounts inflate every count downstream. Fix the sources before decorating them. If the same customer exists three ways across systems, or a field is filled inconsistently, the dashboard inherits every defect with a professional-looking chart on top — which is worse than the spreadsheet it replaced, because it looks authoritative. This is why reporting projects are mostly integration and cleanup projects: designate which system owns each number, wire the pipeline that moves the data without human hands, and only then draw the charts. Once the pipeline is automated, the reporting is close to whatever it costs to design a page — which is why it is worth doing in that order. Finally, put the numbers where people already look rather than expecting a new daily habit: a weekly summary that arrives by email, a screen in the room, an alert when a number crosses a threshold. A dashboard that must be remembered gets forgotten; a report that shows up gets read. The maintenance rule is the same as for any system — when a metric stops informing a decision, remove it, or the wall of charts rebuilds itself within a year.