Daily Ad-Spend Series Delta-Versus-Cumulative Integrity Automation: Caught at 9x Before It Ever Shipped

A metric recorded as "the total so far" is genuinely useful for one question — how much has been spent in total — and genuinely useless, on its own, for a completely different one: how much changed today, or this week, compared to before. Building a report that answers the second question from data shaped for the first requires one deliberate, explicit step — subtracting the previous total from the current one — and skipping that step doesn't produce an error. It produces a normal-looking number that happens to be wildly wrong, because it silently answers a different question than the one being asked.

Writing a query that groups rows by day and sums a spend column is one of the most natural, common patterns in reporting code — it is exactly correct when each row already represents that day's own activity. The mistake only exists at all because the SOURCE rows here represent something different: each one is a cumulative snapshot, a running total as of that day, not that day's own contribution. The two cases look identical in the query itself; the only way to tell them apart is to know, explicitly, what the underlying data actually represents — and a query that's syntactically correct and returns a number gives no signal at all that it answered the wrong question.

The tool this fix concerns was built to close a real, previously-documented gap: Axis's existing campaign-reporting tool could only ever report a LIFETIME total since a campaign's start, with its own text stating plainly that it cannot support a question like "did spend drop in August," and a ROAS figure available elsewhere in the product marked internally as a fixed placeholder never to be cited as real. The new tool reads the same daily snapshot table Centriu Maestro's own ad-metrics pipeline writes — real per-campaign totals recorded once a day — specifically to compute genuine daily change and a genuine period-over-period comparison, along with CTR, cost-per-click and cost-per-acquisition calculated directly from real spend, click and conversion figures rather than from any placeholder column.

Before release, the function was tested against a real, connected account's real data — not synthetic test rows — specifically because the underlying table records a cumulative, lifetime-basis total in every row: a full, running sum since that campaign's first day, not that day's own activity. The first version of the new tool's query summed those cumulative totals directly across every day in the requested window — a natural-looking, syntactically correct aggregation that answers a completely different question than the one intended. Against one real account, an 8-day campaign with a genuine period spend of R$197.78 was reported by that first version as R$15,817 — roughly nine times too high — a discrepancy large enough to be immediately, unmistakably obvious the moment it was checked against what the account's own real spend actually was.

The fix replaces the direct sum with a delta calculation: each day's real figure is computed as that day's recorded cumulative total MINUS the immediately preceding recorded snapshot for that same specific campaign, using a windowed lookback rather than a plain aggregate. A floor is applied to that difference so that a data correction or reset on the advertising platform's own side — which could otherwise produce a negative "delta" — never reports as negative spend; it reports as zero for that day instead. The same delta logic, not a second, separately-written calculation, is reused for the day-by-day series, the per-campaign breakdown, and the current-period-versus-previous-period comparison, so the three views of the same underlying data can never quietly drift out of sync with each other.

An 8-day campaign genuinely spent R$197.78 in real advertising cost over that window. The first version of the new reporting tool, tested against that exact real account before release, reported R$15,817 for the same window — because it summed eight cumulative running totals instead of computing seven real day-to-day deltas. The discrepancy was large enough to be caught immediately by comparing the tool's output against the account's own actual numbers, before the tool was ever released for anyone to rely on.

Centriu Axis can now answer a genuine day-by-day and period-over-period question about a client's paid-media spend — a real gap the platform's own existing tool explicitly could not answer — with figures derived from real, delta-based daily activity rather than a running cumulative total, and calculated cost metrics that never depend on a documented, fixed-placeholder value.

This tool reports real spend, impressions, clicks and conversions — it explicitly does not compute or return a ROAS figure, because doing so honestly would require the client's own revenue data, which the platform does not currently consolidate from this reporting path. A team expecting a return-on-ad-spend number from this specific tool will not find one here.

A calculation that reads cleanly, runs without error, and returns a normal-looking figure can still be answering an entirely different question than the one intended — and nothing about the code itself signals that mismatch. Centriu Axis caught this one specifically because the fix was tested against a real account's real, already-known numbers before release, where a nine-times discrepancy was immediately, unmistakably visible — a standard code review reading the query in isolation would have had no comparably obvious signal that anything was wrong.