Initial situation
Monday morning. Someone opens five tabs: advertising account, web analytics, unloading from the database, client bank, task tracker. Transfers numbers to the table, colors cells, writes two paragraphs of comment, sends to chat.
It takes half a day. A week later the operation is repeated in its entirety.
Then the discrepancies begin. The number from the advertising account does not match the number from analytics, because the periods are calculated in different time zones. Revenue for the month looks like a failure, because the receipt from the payment aggregator comes at the beginning of the next month. A quarter later, no one can explain where the figure in the old report came from: the tab was closed, and the office recalculated the data retroactively.
The worst option is a report that is read by inertia. The numbers are collected, sent out, and no decision is made on them.
What did you watch?
Measuring assembly time. Not an assessment from memory, but a mark from opening the first tab to sending. Memory scores are systematically underestimated by half.
Inventory of numbers. Each line of the report is marked: whether at least one decision was made on it during the last quarter. Lines without solutions are candidates for deletion rather than automation.
Reconciliation of definitions. For each metric, what it means is written down. It almost always turns out that “active client” in marketing and finance have different sets, and “lead” in the advertising account and in CRM are counted based on different events. There is nothing to automate until there is a written definition.
Time zones and day boundaries. Each source provides data in its own zone: one counts the day according to UTC, another according to local time, the third according to account time. A discrepancy of a few percentage points is usually explained here rather than by an error in the data.
Shift in cash receipts. When the acquiring company lists the fees for a month at the beginning of the next month, the calendar window undercounts the month of revenue, and the year-to-year comparison is inconsistent. The window is shifted once and fixed in the metric definition.
What was changed
1. Dictionary of metrics before code. File where for each metric the following is written: human definition, source, exact formula or SQL, unit of measurement, calculation window. A metric without an entry in the dictionary does not appear in the report.
2. Each source is read via an API. Advertising account, analytics, bank, database, tracker - programmatically and on a schedule. Manual unloading and parsing of the interface by the browser is not used: the first is not reproduced, the second breaks during the next redesign.
3. Snapshot layer. The result of each query is saved as is, with a timestamp and query parameters. The report is compiled from images. Without this layer, yesterday's report cannot be recompiled, because sources recalculate data retroactively.
4. Unified time canon. One report timezone, period boundaries are calculated in one place in the code, all sources are converted to it when reading, and not when assembling.
5. The code counts the numbers. SQL and scripts using formulas from the dictionary. The model is not suitable for arithmetic.
6. The model works at the last step. It describes what has changed relative to the previous period and last year, and notes anomalies. Conclusions and decisions remain with the person.
7. Checks before publication. The sum of the parts converges with the whole. The deviation from the previous period is greater than the threshold - the number is marked as requiring a look, rather than leaving silently. The source did not answer - the report says “no data”, not zero. A zero instead of a pass generates false conclusions.
8. Last year is next to this year. Always, without a separate request. Seasonality in most businesses is stronger than weekly fluctuations.
9. Each number has a passport. Next to the value: source, time of snapshot, link to dictionary definition. Six months later, the question “where does this come from” is closed in seconds.
10. A document, not a message. The script generates a file with the date in the name and puts it in storage. The chat includes a title, several key lines and a link. The long table in the messenger is unreadable.
11. One entry point. Schedule and manual start pull the same command. Different triggers, same code.
What was not touched
- The sources themselves. Data is read locally. Building a separate storage facility for the sake of a weekly report is an order of magnitude more work with the same result.
- Existing dashboards. They solve another problem: to look in the moment. The report records the status as of the date.
- Manual comment from the manager. Remained as a separate section at the end of the document. Automation describes the changes, a person gives the assessment.
- Previous format for the transition period. The table was assembled by hand in parallel until the automatic report matched it several times in a row.
Reversibility
- The schedule is disabled, the manual start command remains the same, the report continues to be collected.
- Pictures are saved regardless of assembly. Even with a full rollback, the data history remains, and the report for past dates is rebuilt.
- Model comment behind the flag. It turns off, numbers and tables remain.
- The metrics dictionary is experiencing a rollback of any automation. It is useful in its own right and remains even if the scripts are thrown away.
- Each version of the report is stored as a file. A comparison of the two versions shows what has changed both in numbers and in code.
Boundaries
- Source without API. Analysis of the interface lives until the next redesign. If the data is needed for a long time, the issue of access is resolved first.
- Metric without agreed upon definition. Automation will record one of the sides of the dispute and give it the appearance of truth. The dispute is resolved before the code.
- Data with closing delay. Accounting and reconciliation indicators become final later than the report is issued. For them, the document requires a “preliminary” mark.
- Regulated reporting. Tax, statistical, audit - they have their own forms, deadlines and responsibilities.
- Report for the sake of report. If decisions are not made based on numbers, automation will produce the same useless document, only faster and more often.
Rule: the code counts the numbers according to the written definition, each source response is saved as a snapshot, and the model only describes the changes.