A live, trustworthy utilisation dashboard for a publicly funded health scheme

A state-level government health agency needed to show its leadership and its funding partner how far a publicly funded primary healthcare scheme was actually reaching enrolled beneficiaries, month by month, to a standard fit for presentation to the state's leadership. The reporting came in as a monthly facility spreadsheet fed by a mobile form, and turning it into a figure anyone could stand behind meant solving several real data problems at once: two different funding streams recorded in the same table, so a simple total overstated activity several times over; some facility months reported more than once; records with impossible dates or inconsistent area spellings; and, because different areas reported in different months, a full-target denominator that understated performance.

The agency wanted one refreshable dashboard that reported only what the data supported, applied transparent and documented rules to each of these problems, and was clear enough for a non-technical executive to read without explanation.

We built a live, three-page dashboard connected directly to the agency's own cloud spreadsheet, so it refreshes as new monthly reports arrive, with no manual export step. It shows beneficiaries enrolled, monthly visits and services, month-on-month change, performance against target and a map of every programme area, split cleanly by funding stream and by programme phase.

The value is in the governance. Every cleaning rule is written in code and documented in plain language, and none of it edits the source: stray characters are stripped, the many spellings of each area are mapped to one key, and a report's month is derived from month and year alone so an impossible day still resolves correctly. The two funding streams were given an explicit marker and the dashboard locked to the partner-funded one, which removed an inflation that had made a single month look several times larger than it was. Where a facility month appears twice, a validated record wins if one exists, otherwise the row is flagged for follow-up rather than dropped. Monthly performance is measured against the target of the areas that actually reported, so alternating reporting no longer understates it, and every figure shows how many areas reported.

Before handover, every headline figure on every page was recomputed from the raw data in a separate tool and reconciled to the dashboard, and conflicting reference figures in the source were documented for the agency to resolve rather than quietly overwritten. The dashboard keeps no separate copy of the data, the whole pipeline is scripted and reproducible from a documented guide, and the map is generated from open boundary data with no dependency on any consultant's machine.

The analysis is descriptive monitoring, chosen because incomplete months and alternating reporting would not support inference, and we said so. Missing data shows as not reported, never as zero.

The agency moved from a spreadsheet that needed manual filtering to produce any figure, to one live, defensible view of whether funded services were reaching enrolled beneficiaries, with the funding streams correctly separated and every area's performance visible against a fair target. Data-quality problems that had been distorting the totals were surfaced and documented for correction at source.

For a publicly funded programme answerable to political leadership and a development partner, this is the difference between a dashboard and a dashboard you can trust: cleaning rules you can show, funding streams kept honestly apart, a denominator that does not flatter, and every headline number independently recomputed before anyone presents it.