Every clinic's numbers on one screen with predictive analytics in healthcare

The numbers a franchise head office can’t compare

An allied health franchise looks like one business and runs like twenty. Each physiotherapy or podiatry clinic books its own appointments, sees its own patients, bills its own consults and lodges its own Medicare and health-fund rebates. Each is a small business doing the same work under a shared brand. Head office is meant to see across all of them and decide where to put effort, but the view it needs rarely arrives in a usable shape.

The reason is the spreadsheet. Every clinic keeps its numbers in a workbook of its own design, built by whoever ran the front desk, counting things the way that person found sensible. One clinic calls a practitioner “fully booked” at 75 per cent of available hours, another at 90. One counts a cancelled-and-rebooked appointment as two, another as none. So when head office asks the obvious question, which clinics are busy and which are quiet, the answer is a fortnight of someone reconciling workbooks by hand, and a comparison nobody fully trusts at the end of it.

This is operational reporting, not clinical analytics. It measures how the practices run as businesses, practitioner utilisation, occupancy, cancellations and no-shows, new versus returning patients, and billing and rebate throughput. It does not touch treatment outcomes or clinical decisions. Even so, the data describes real patients, so it sits under the Privacy Act 1988 and the Australian Privacy Principles for health information, and franchise reporting has to work from aggregated, de-identified figures rather than patient records.

Why Power BI, and the modelled layer beneath it

The aim is one comparable view of every clinic that head office and each practice can both read from. We headline these builds on Power BI for three practical reasons. A single semantic model defines each measure once, so utilisation means the same thing at every site. Row-level security lets a clinic see only its own numbers while head office sees the whole network. And a scheduled refresh puts current figures in front of people without anyone rebuilding a workbook.

Power BI is only as good as the data beneath it, so the architecture shapes that data first. A modelled data layer, built on Microsoft Fabric or SQL Server depending on the franchise’s existing stack, ingests each clinic’s practice-management export and reconciles it. Power BI reports from that single source rather than from each site’s spreadsheet. The semantic model defines utilisation, occupancy, billings and rebate throughput once, so a clinic manager and head office open a report and see the same number worked out the same way.

The point worth dwelling on is the mapping layer. Because each clinic coded its services slightly differently, the modelled layer maps every site’s service and billing codes to one common definition before any measure is calculated. That is what lets the franchise compare a clinic in one suburb with a clinic in another and know the comparison is fair. We kept the modelled layer separate from Power BI on purpose, so it can feed other reporting later, not just today’s dashboard.

Building it, and where it got hard

The friction in franchise analytics is rarely the chart. It is that the same word means different things at different clinics, and one example stands in for the rest.

Early in the build, “utilisation” would not reconcile across sites. Each clinic ran a slightly different practice-management setup and coded its services inconsistently, so a billed consult at one clinic and the same consult at another landed under different codes. When the first reports went up, head office could see that two clinics looked very different, but could not tell whether that was a real gap in performance or just two front desks counting differently. A comparison you cannot trust is worse than no comparison, because people act on it anyway.

The fix was definition, not a cleverer visual. We built one semantic model that defined each measure once, utilisation, occupancy, billings, rebate throughput, with a single agreed formula behind each. Underneath it sat a mapping layer that reconciled every clinic’s local service and billing codes to a common set, so a consult was counted the same way wherever it happened. Then row-level security made sure each clinic saw only its own numbers while head office saw the reconciled comparison across the network. Getting those definitions agreed took more meetings than building the model did. That is normal, and it is the part that makes the numbers trustworthy.

Two constraints shaped the rest. Because the data describes patients, franchise reporting works from aggregated, de-identified figures, so no patient-level record sits in a head-office dashboard, in line with the Privacy Act 1988 and the Australian Privacy Principles. And the Medicare and rebate figures are throughput counts, how many claims flowed and what they were worth, not advice on how to bill. The reporting tells the franchise what happened, not what to code.

What changed

In a representative build, the franchise got one definition of utilisation across every clinic, so head office could finally compare sites that a pile of bespoke spreadsheets had kept stubbornly out of line. The monthly close got shorter too. Pulling practice-management exports into the modelled layer cut the manual consolidation that had taken the better part of a week each month, because the reconciliation happened once, in the model, rather than by hand in a workbook.

The franchise also saw quiet clinics sooner. Occupancy and rebate-throughput trends surfaced under-booked sites and rebate leakage weeks earlier than the old month-end roll-up, which gave the network room to shift a practitioner’s hours or chase a stalled claim before it became a quarter’s problem.

These figures are illustrative. They describe the pattern we see rather than a published result for a named franchise. The shape is the point. The numbers each clinic was already producing start arriving in one comparable view, defined once, so head office can see the network clearly and each clinic still sees its own slice. That is what turns twenty spreadsheets into one screen.

Where this fits

Practice analytics is one application of our Data Insights and Analysis service, built on Power BI, for an allied health franchise. It is a contained, high-return starting point, because the data already exists at every clinic and the value comes from defining it consistently and getting it in front of the right people. It is operational reporting, deliberately kept clear of clinical analytics and clinical advice. If your clinics each report their own way and head office cannot compare them, the place to start is to map your practice-management data and agree what each measure should mean across the network.