A customer portfolio analysis that rebuilds and checks itself every month

The problem

Management wanted answers that a monthly sales export can hold but rarely gives up easily. Is growth coming from new customers or existing ones? How much is price and how much is volume? How dependent is the business on a few large accounts, and which services are growing or declining? The data sat in dozens of monthly files with changing layouts, and one change of accounting system in the middle of the period.

What we built

A pipeline that answers eleven management questions and three costed proposals from 32 monthly files. One command runs about 26 steps, each in its own process, and publishes nothing if any step fails. Every run starts from the raw files, so nothing carries over by accident from the previous version.

It produces four things from the same numbers: a workbook for analysts, a two-page summary for management, a 16-slide deck, and an offline interactive view with a search for any customer.

Why the numbers can be trusted

Every figure is checked by something that shares no code with the thing that produced it. The raw files are read three separate ways: by the main reader, by a SQL path that exists only to check it, and by a third re-read of the service mix that stops the run if any month, customer and product sum differs. 23 checks compare about 1,800 summary cells against an independent calculation, and the 563 numbers quoted in the written texts are recalculated from the source rows.

Before the first presentation the pipeline was also tested against itself. A backtest on the earlier 11-sheet version of the workbook compared 806,179 filled cells across two runs: 806,173 matched, and all six differences were explained. Two were a tie in a most-common-value calculation being broken at random, now fixed, and four were sentences now generated from the data. It also caught empty text turning into a missing value in the SQL path, which dropped a line. Two forward tests followed, one removing the latest month and one adding a fake month, and they caught hard-coded dates, file names and lists that would have broken the next monthly update.

The last guard is about words, not numbers. If a new month contradicts a sentence in the deck, the run flags it and refuses to save the deck until the sentence is rewritten.

A monthly routine, not a one-off

Updating it takes under half an hour; the two logged monthly runs took 17 and 22 minutes. Add the new file, run it, read the run report and settle any flagged sentences. A follow-on monthly churn report reuses the same building blocks, with two calculation paths that must report zero mismatches, 14 tests on synthetic data and a verifier that checks the workbook.

The system map shows every part of the run and eight journeys through it, from a new month to a failed check, the backtest and where AI was used. It uses generic names, and the client stays anonymous.

Inside a monthly run

Pick a management question to see its answer, add a month and watch the run rebuild and recheck everything, or see how the pipeline was tested to break.

What it shows

AI made building this fast: Claude helped write and review the code, including the independent re-reads, and the monthly run happens in a Claude session. No model runs inside the pipeline itself; every figure comes from deterministic scripts and checks. The trust comes from the design around it: independent checks, tests that try to break it, and a rule that the story cannot get ahead of the data. The same approach fits any recurring analysis that ends up in front of a board.