---
title: "Clinic network data model: utilisation, no-shows, revenue"
canonical: "https://www.owox.com/blog/articles/clinic-network-data-model"
updated: "2026-10-11"
---

# Clinic network data model: tables, keys and one definition per metric

You have been asked for bookings, utilisation, no-shows, revenue per clinician and per location, patient retention and marketing cost per new patient, across every clinic in the network. This guide gives the analyst at a dental, physio, vet or aesthetics group the model behind them: the tables and their keys, how to keep patient identifiers out of the reporting layer, appointments and their status changes, capacity, treatment lines and the insurer's share, one definition per metric with a worked example you can run, a free model to start from, and a note to forward to whoever asked.

[Data Modeling](/blog/topics/data-modeling) · Updated October 11, 2026 · [Ievgen Krasovytskyi](/team/ievgen-krasovytskyi) · 22 min read

![Clinic network data model: tables, keys and one definition per metric](https://cdn.owox.ai/www/content-marketing-article/clinic-network-data-model/thumbnail/1.png/public)

Someone has asked you for the clinic numbers: bookings, utilisation, no-shows, revenue per clinician and per location, how many patients come back, and what marketing pays for each new one, for every clinic in the network, every month. **The model behind them is fifteen tables, each with one grain.** One row per appointment, and one per change in its status. One row per bookable slot, so utilisation has a denominator. One row per treatment delivered, and one per amount billed to each payer, so the insurer’s share and the patient’s share stay apart. Patients, locations, clinicians and services, enquiries and ad spend, and a calendar.

The tables are the easy part. The numbers diverge on the rules. Revenue has a standard to point at: finance recognises it under IFRS 15 or ASC 606, when the treatment is delivered, whoever pays. **Utilisation, no-show rate and retention have no standard at all.** Whether a cancellation two hours before the appointment is a no-show, whether utilisation counts booked time or treated time, how long a patient can stay away and still count as retained: each choice moves the number, and if nobody writes it down, you become its author.

And unlike most models, **this one holds sensitive personal data**, so it is built for a reporting layer that never needs a patient’s name.

If you are the operations director, the owner or the finance lead who asked, the last section is written for you. The rest is for the analyst who has to build it.

## What the numbers mean, and what they are not

Five words the model has to keep apart:

*   **Bookings** here are appointments booked: a count, not money, and not the finance sense of a signed contract.
*   **Recognised revenue** is what finance reports from its ledger. Under [IFRS 15](https://www.ifrs.org/issued-standards/list-of-standards/ifrs-15-revenue-from-contracts-with-customers/) a treatment is revenue when it is delivered, at the amount the clinic expects to be entitled to, and that includes the insurer’s share before the insurer has paid it.
*   **Cash collected** is what the patient paid at the desk and what the insurer has actually remitted.
*   **Receivables** are what is still owed: the insurer’s share of delivered treatment and any unpaid patient balance. **An insurer receivable is not cash**, and some of it may never arrive.
*   **Prepaid courses, packages and plans** (a block of physio sessions, an aesthetics package, a monthly membership) are cash before revenue. Until delivered, the money is a liability.

## Patient data is sensitive personal data

Treat every row about a patient as health data. Under the EU’s GDPR, [data concerning health](https://gdpr-info.eu/art-9-gdpr/) (Article 9; links go to an unofficial consolidated text) is a special category with stricter conditions for processing. In the US, the HIPAA Privacy Rule protects [individually identifiable health information](https://www.hhs.gov/hipaa/for-professionals/privacy/laws-regulations/index.html) held by a healthcare provider that sends it electronically for transactions such as insurance claims. Other countries have their own rules. An appointment type or a treatment code can reveal a condition without a name beside it.

Four modelling choices follow. None replaces your network’s own rules, and **your data protection officer or compliance lead should sign off before patient-level data is loaded into the warehouse at all**:

*   **Report on a pseudonymised patient key**, a code that stands in for the patient, not the practice-management system’s patient number, and never on a name, a date of birth, a phone number or an email.
*   **Keep the identifiers in one restricted table**, `patient_identity`, outside the reporting layer: the key, each system’s own patient id, and whatever contact details recall work needs. Only the people who already handle patient records read it.
*   **Report ages in bands and places at the level of a location**, not a date of birth or a home address.
*   **Suppress small cells.** A report by location, service, age band and month can come down to one person. Hide or merge any cell below a minimum count; your data protection officer sets the number.

Questions your data protection officer will ask before signing off:

*   Is there a data protection impact assessment, and what is the legal basis, including the condition for health data?
*   How long are patient-level rows kept, and who, by name or role, can read `patient_identity`, with access logged?
*   Which processors are involved (the warehouse, OWOX, any AI provider), with which agreements, in which region, and with what transfers?
*   How does an erasure or objection request reach the warehouse?
*   Are free-text notes and call recordings kept out of the reporting layer? (They should be.)

Pseudonymised is not anonymous: the GDPR still treats it as personal data ([Recital 26](https://gdpr-info.eu/recitals/no-26/)). The point is narrower: the tables your reports read hold no names or contact details. A row that combines location, service, date and age band can still point to one person, so suppression applies to every report, and the row-level tables are restricted too. **By default, an AI assistant reads only aggregated Data Marts, with no patient key at all**, and connecting one is a question for your data protection officer first. What the rules require of your network is for them to say, not this article.

## The Turning Point

OWOX Data Marts

See your first report built in real time. _15 minutes._

1.  Connect your data warehouse
2.  Pick your metrics
3.  Get a live Google Sheets report

In the time it takes to write a ticket. Then imagine never writing that ticket again.

[Book a Demo](/book-a-call)

We'll use your actual use case

## The model on one page

![A clinic network data model: patient, location, practitioner and service as reference tables; schedule slots as capacity; appointments and their status events at the centre; treatment lines, invoice lines and payments for the money; enquiries and ad spend for acquisition; practitioner day and patient first visit built from the others; patient identity kept outside the reporting layer](https://cdn.owox.ai/www/content-marketing-article/clinic-network-data-model/screens/1.png/public)

Fifteen tables in the reporting layer. **Start every table’s description with its grain**, “one row per appointment”, and make the grain its primary key:

| Table | One row per | Primary key |
| --- | --- | --- |
| `patient` | patient, pseudonymised | patient key |
| `location` | clinic location | location id |
| `practitioner` | clinician | practitioner id |
| `service` | service or procedure the network offers | service code |
| `schedule_slot` | bookable time of one clinician at one location | practitioner, location, slot start |
| `appointment` | appointment | appointment id |
| `appointment_event` | change of status on an appointment | event id |
| `treatment_line` | service delivered at an appointment | treatment line id |
| `invoice_line` | amount billed to one payer for one treatment line | invoice line id |
| `payment` | payment, refund or adjustment applied to one invoice line | payment id, invoice line id |
| `enquiry` | booking request: call, form, chat or referral | enquiry id |
| `ad_spend` | day, channel, campaign and location | date, channel, campaign, location |
| `calendar` | day | date |
| `practitioner_day` | clinician per location per day | practitioner, location, date |
| `patient_first_visit` | patient’s first completed appointment in the network | patient key |

`practitioner_day` and `patient_first_visit` are built from the others; nothing is typed into them. `patient_identity` sits outside this list on purpose.

**A clinician works at several locations, and a patient visits several.** So location belongs on the slot and the appointment, not on the clinician or the patient. A clinician’s “home clinic” is an attribute for the staff list, never the location their revenue is counted in.

### Which system each table comes from

| Table | Source of truth |
| --- | --- |
| `appointment`, `appointment_event`, `schedule_slot`, `patient`, `practitioner`, `location` | the practice-management or booking system |
| `treatment_line`, `invoice_line`, `payment`, `service` | the practice-management system’s billing, and the insurers’ remittances |
| `enquiry` | call tracking, web forms, the CRM, referral letters |
| `ad_spend` | the ad platforms |
| `calendar` | yours |

### One patient, one key, across locations

Each practice-management system gives the same person its own patient number. `patient_identity` holds the match: one network `patient_key`, and beside it every system’s patient id. **Count the patients matched on anything weaker than an id**, so the share is a number you can show. Every other table carries only `patient_key`.

## Appointments and their status changes

An appointment moves between states, and **the history of those moves is the data**. If you keep only the current status, last month’s no-show rate changes whenever someone tidies up the diary today.

### One table for the appointment, one for what happened to it

`appointment` holds what the booking was: patient, clinician, location, service, start, length and when it was booked, plus the current status for convenience.

`appointment_event` holds every change, one row each:

*   **what happened**: booked, confirmed, rescheduled, cancelled, arrived, completed, marked no-show;
*   **when it happened** (`occurred_at`) and **when it was recorded** (`recorded_at`). A receptionist marks Monday’s no-shows on Tuesday morning;
*   for a cancellation, **who cancelled** (patient or clinic) and **how much notice was given**, in hours before the start;
*   for a reschedule, the appointment it moved to.

Key it on the system’s own event or audit id where there is one, so a change loaded twice is not counted twice.

### Late cancellation is the rule to write down

A cancellation with three days’ notice frees a slot someone else can take. One two hours before frees a slot nobody can. A no-show frees nothing. **Store all three as different outcomes**, and decide once where the cut-off between an ordinary and a late cancellation sits. If the cancellation policy names one, use it.

Two more rules: **a reschedule is not a cancellation**, or every moved appointment inflates the cancellation rate; and **a cancellation by the clinic is not the patient’s**: a clinician off sick belongs in the capacity figures, not in patient behaviour.

## Capacity: what utilisation divides by

Utilisation needs a denominator, and appointments cannot give one. **`schedule_slot` is the denominator.** One row per bookable stretch of one clinician’s time at one location, with its start, its length in minutes and whether it was open for booking or blocked (training, admin, a break).

Working sessions instead of slots work too: **capacity is time a clinician could treat patients at a location**, measured before anyone books it.

`practitioner_day` brings it together, one row per clinician per location per day: minutes available, minutes booked, minutes attended, and the revenue delivered. Utilisation, revenue per clinician hour and the location roll-ups all read it.

**Snapshot the roster daily**, or keep its change history, so last month’s open minutes cannot be edited after the fact. When booked minutes exceed open minutes (a double booking, an extra squeezed in), report the real figure, say 112%, and flag the day: it shows overtime and squeezed-in work. Decide once whether surgery or emergency time outside the slot grid counts as capacity.

## Treatment lines, invoices and payments

In a shop, a sale and a payment are one event. **In a clinic they come apart**, and three tables keep them apart.

*   **`treatment_line`**, one row per service delivered at an appointment: the service code, quantity, the clinician who delivered it (not always the one who was booked), the delivery date and the price after discount. **The warehouse computes revenue from these lines** and reconciles it to finance’s ledger each month; where they differ, the ledger wins.
*   **`invoice_line`**, one row per amount billed to one payer for one treatment line. A €200 treatment with an insurer paying €150 is two invoice lines: €150 to the insurer and €50 to the patient. Keep finance’s expected amount per payer on the line too, so a short-payment finance has already expected is not taken out twice.
*   **`payment`**, one row per amount applied to one invoice line: the patient’s card payment, the insurer’s remittance, a refund, or an adjustment when the final settlement differs from finance’s expected amount. An insurer pays many invoice lines in one remittance, so the grain is the allocation, not the remittance.

### Recognised revenue, cash and receivables

**Revenue is recognised when the treatment is delivered**, from `treatment_line`, whoever pays and whenever. The insurer’s share is revenue in the month of the treatment and a receivable until the remittance arrives. The remittance is cash: if it arrives in April for a March treatment, it settles the receivable and adds nothing to April’s revenue.

**When an insurer routinely pays less than was billed**, revenue is measured at what the clinic expects to be entitled to, and finance estimates that at month end ([IFRS 15](https://www.ifrs.org/issued-standards/list-of-standards/ifrs-15-revenue-from-contracts-with-customers/) calls it variable consideration). That estimate lives on the invoice line, as above, and the warehouse reconciles to the ledger each month. A rejected direct claim usually goes back to the patient or owner, who stays liable: it becomes a patient receivable, not an insurer one. A write-off is either a reduction of revenue (a short-payment that was expected) or a credit loss (a debt that cannot be collected), and finance classifies which.

Prepaid courses and packages work the other way round. The cash arrives first and sits as a liability. A course of sessions becomes revenue as each session is delivered; a membership or health plan that covers a period becomes revenue over that period.

## Marketing source and the new patient

`enquiry` holds one row per booking request: the channel (phone, form, chat, an ad platform’s lead form, a referral from another clinician), the campaign or tracked number where there is one, the location asked for, and the appointment it led to, if any. Many arrive by phone, so call tracking matters. **Strip phone numbers and emails at load**: keep them in `patient_identity`, or leave them out if recall runs in the practice system, which already holds them. **By default, send ad platforms no patient-level conversion data, and put no tracking pixels on booking pages for health-related services**; only aggregated signals that reveal nothing about health, and only after your data protection officer has reviewed them.

`patient_first_visit` is built from the rest: one row per patient, with the date and location of their **first completed appointment anywhere in the network** and the source of the enquiry behind it. A booking that ended in a no-show is not a new patient yet, and a patient is not new again at a second location.

`ad_spend` keeps one row per day, channel, campaign and location; spend that serves the whole network stays at network level unless someone decides how to spread it.

## Dates: which one each number reads

**Give every metric one date, and join every date to one `calendar` table** in the network’s reporting time zone, so “March” means the same days at every location.

| Number | The date it reads |
| --- | --- |
| Bookings | when the appointment was booked |
| Utilisation, no-shows, cancellations | the appointment’s scheduled date |
| Recognised revenue | the treatment’s delivery date |
| Cash collected | the payment date |
| Receivables | the balance at month end |
| New patients, cost per new patient | the first completed appointment’s date; spend on its own date |
| Retention | the first completed appointment’s month (the cohort) |

## One definition per metric

Defensible defaults. The point is **one of each, written down, used by every report and assistant**.

| Metric | Definition | Reads |
| --- | --- | --- |
| Bookings | Appointments booked in the period, by booking date; reschedules not counted again | `appointment` |
| Utilisation (booked) | Minutes of appointments still booked at the late-cancellation cut-off ÷ minutes of open slots | `practitioner_day` |
| Utilisation (delivered) | Minutes of completed appointments ÷ minutes of open slots | `practitioner_day` |
| No-show rate | No-shows ÷ appointments still booked at the late-cancellation cut-off | `appointment_event` |
| Late cancellation rate | Patient cancellations inside the cut-off ÷ the same base | `appointment_event` |
| Recognised revenue | Value of treatment lines delivered in the period, after discount, all payers, net of VAT where it applies, at finance’s expected amount | `treatment_line` |
| Revenue per clinician hour | Recognised revenue delivered by the clinician ÷ their open slot hours; per location, the same at the location | `practitioner_day` |
| Cash collected | Payments received in the period, patient and insurer, less refunds | `payment` |
| Insurer receivable | Insurer invoice lines billed less insurer payments and adjustments, at month end | `invoice_line`, `payment` |
| New patients | Patients whose first completed appointment in the network falls in the period | `patient_first_visit` |
| Patient retention | Share of a month’s new patients with another completed appointment within the return window (for example 6 months), anywhere in the network | `patient_first_visit`, `appointment` |
| Marketing cost per new patient | Ad spend in the period ÷ new patients in the period, by the source of their first enquiry; referrals and walk-ins shown with no spend | `ad_spend`, `patient_first_visit` |

Three notes on choices that move numbers most:

*   **The no-show base.** Whether early cancellations are in it, and whether a late cancellation counts as missed, can move the rate by more than any change in patient behaviour (see the worked example).
*   **Revenue per hour of what.** Per open hour rewards the clinician whose diary fills; per attended hour rewards the one who bills most per patient. They rank clinicians differently; pick one and show the other beside it.
*   **The return window differs by service line.** A dental recall and a yearly vaccination come back at different intervals. Set the window per service line, or the longest interval always looks worst.

## A worked example you can run

Eight appointments over two days at two locations, small and clean on purpose, typed in as literal rows so the query runs in any BigQuery project as written. Prices exclude VAT, so the cash below is net of VAT too. The late-cancellation cut-off is 24 hours. The query reads each appointment’s final status, a simplification of `appointment_event` that holds for a closed past period.

*   **C1** works at location L1 on Monday morning (240 minutes). **C2** works at L1 on Monday afternoon and at L2 on Tuesday morning (240 minutes each).
*   **A1**, patient P1 with C1, 60 minutes, completed: a €90 consultation, paid by the patient.
*   **A2**, P2 with C1, 60 minutes, no-show.
*   **A3**, P3 with C1, cancelled with 72 hours’ notice; **A4**, P4, booked into the freed slot, completed: €200, of which the insurer pays €150.
*   **A5**, P5, an established patient first seen last year, with C2, 90 minutes, completed: €300, of which the insurer pays €240.
*   **A6**, P6 with C2, cancelled 2 hours before the start.
*   **A7**, P1 again, with C2 at L2, completed: €120, paid by the patient.
*   **A8**, P7 with C2 at L2, no-show.

```
WITH capacity AS (
  SELECT * FROM UNNEST([
    STRUCT('C1' AS clinician,
      'L1' AS location,
      DATE '2026-03-02' AS day,
      240 AS available_min),
    ('C2', 'L1', '2026-03-02', 240),
    ('C2', 'L2', '2026-03-03', 240)
  ])
),
appointment AS (
  SELECT * FROM UNNEST([
    STRUCT('A1' AS appt_id,
      'P1' AS patient_key,
      'C1' AS clinician,
      'L1' AS location,
      DATE '2026-03-02' AS day,
      60 AS minutes,
      'completed' AS status,
      CAST(NULL AS INT64)
        AS notice_hours),
    ('A2', 'P2', 'C1', 'L1',
      '2026-03-02', 60,
      'no_show', NULL),
    ('A3', 'P3', 'C1', 'L1',
      '2026-03-02', 60,
      'cancelled', 72),
    ('A4', 'P4', 'C1', 'L1',
      '2026-03-02', 60,
      'completed', NULL),
    ('A5', 'P5', 'C2', 'L1',
      '2026-03-02', 90,
      'completed', NULL),
    ('A6', 'P6', 'C2', 'L1',
      '2026-03-02', 60,
      'cancelled', 2),
    ('A7', 'P1', 'C2', 'L2',
      '2026-03-03', 60,
      'completed', NULL),
    ('A8', 'P7', 'C2', 'L2',
      '2026-03-03', 60,
      'no_show', NULL)
  ])
),
treatment_line AS (
  SELECT * FROM UNNEST([
    STRUCT('T1' AS line_id,
      'A1' AS appt_id,
      'C1' AS clinician,
      90.00 AS amount,
      0.00 AS insurer_share),
    ('T2', 'A4', 'C1',
      200.00, 150.00),
    ('T3', 'A5', 'C2',
      300.00, 240.00),
    ('T4', 'A7', 'C2',
      120.00, 0.00)
  ])
),
appt AS (
  SELECT *,
    -- still booked 24 hours
    -- before the start
    NOT (status = 'cancelled'
      AND notice_hours >= 24)
      AS due
  FROM appointment
)
SELECT c.clinician,
  c.available_min,
  a.booked_min,
  a.attended_min,
  ROUND(a.booked_min
    / c.available_min, 3)
    AS booked_util,
  ROUND(a.attended_min
    / c.available_min, 3)
    AS delivered_util,
  r.revenue,
  ROUND(r.revenue
    / (c.available_min / 60), 2)
    AS per_available_hour
FROM (
  SELECT clinician,
    SUM(available_min)
      AS available_min
  FROM capacity GROUP BY 1) c
JOIN (
  SELECT clinician,
    SUM(IF(due, minutes, 0))
      AS booked_min,
    SUM(IF(status = 'completed',
      minutes, 0)) AS attended_min
  FROM appt GROUP BY 1) a
  USING (clinician)
JOIN (
  -- credited to the clinician
  -- who delivered the line
  SELECT clinician,
    SUM(amount) AS revenue
  FROM treatment_line
  GROUP BY 1) r
  USING (clinician)
ORDER BY 1
```

What it returns:

| Clinician | Open minutes | Booked | Attended | Booked utilisation | Delivered utilisation | Revenue | Per open hour |
| --- | --- | --- | --- | --- | --- | --- | --- |
| C1 | 240 | 180 | 120 | 75% | 50% | €290 | €72.50 |
| C2 | 480 | 270 | 150 | 56% | 31% | €420 | €52.50 |

The no-show rate, three ways, from the same `WITH` block. Replace the final `SELECT` with:

```
SELECT
  ROUND(COUNTIF(
    status = 'no_show')
    / COUNT(*), 2)
    AS of_all_booked,
  ROUND(COUNTIF(
    status = 'no_show')
    / COUNTIF(due), 2) AS of_due,
  ROUND(COUNTIF(due
    AND status != 'completed')
    / COUNTIF(due), 2)
    AS missed_of_due
FROM appt
```

It returns 0.25, 0.29 and 0.43.

Five things the same eight rows show.

**The no-show rate is 25%, 29% or 43%, from the same diary.** Two no-shows of all eight booked is 25%. Take A3 out of the base, since it was cancelled three days ahead and its slot refilled, and it is 2 of 7, 29%. Then count A6’s two-hour cancellation as missed too, and it is 3 of 7, 43%. Two decisions, the base and the late cancellation, move the rate from 25% to 43%.

**Utilisation is 63% or 38% for the network.** 450 of 720 open minutes were still booked at the cut-off; 270 were treated. Of the 450 minutes not treated, 180 were lost to no-shows and the late cancellation and 270 were never booked.

**C2 earns less per open hour and more per attended hour.** Per open hour, C1 brings in €72.50 and C2 €52.50. Per attended hour, C1 brings in €145 (€290 over 2 hours) and C2 €168 (€420 over 2.5 hours). C2’s diary had 210 open minutes nobody booked and 120 lost to a no-show and a late cancellation. Most of the gap is empty diary, a scheduling or demand question; the rest is missed appointments. Neither is about C2’s work.

**March has €710 of revenue and €320 of cash.** All four treatments were delivered in March, so March revenue is €710. Patients paid their shares at the desk, €320, and the insurer owes €390 at month end. When it pays in April, April’s cash rises by €390 and April’s revenue does not.

**P1 is one new patient, not two.** P1’s first completed appointment was A1 at L1, so P1 is a March new patient at L1. A7 at L2 is P1 coming back, which counts towards retention, not towards L2’s new patients. With a key per location, P1 would be new twice and retained never. Revenue by location is still €590 at L1 and €120 at L2.

Marketing cost per new patient follows from the same rows with spend added. Say paid search spent €300 over those two days and brought P1 and P2, and a paid social campaign spent €100 and brought P6 and P7, while P4 came by referral. **Per new booking, paid search cost €150 and paid social €50. Per new patient, paid search cost €300 (only P1 attended) and paid social produced none**, because P6 cancelled late and P7 did not turn up. The network spent €400 for two new patients, P1 and P4, and P4 came free; P5 is established, not new.

## Start from a free model

OWOX publishes a free [Healthcare Clinic Network data model](/data-models/healthcare-clinic-network): **10 Data Marts** for outpatient clinics in the US and Canada, from ad spend and enquiries to attended visits and their revenue. It opens on the [OWOX canvas](https://model.owox.com/?okf=https://github.com/OWOX/models/tree/main/bundles/healthcare-clinic-network&utm_source=owox-blog&utm_medium=article&utm_campaign=clinic-network-data-model&utm_content=model-section), where you can edit it and export it as an [OKF](/blog/articles/open-knowledge-format-okf) bundle; the definition is [on GitHub](https://github.com/OWOX/models/tree/main/bundles/healthcare-clinic-network). It keeps cancellations and no-shows apart and counts acquisition on attended visits. Adapt four things: it has **no capacity** (add `schedule_slot`), **no insurer and patient shares** (add invoice lines and payments), **revenue on the visit** with only its current status, and **a Patients table with names, contact details and dates of birth** (move them to a restricted table).

For one provider with claims and denials, see the [healthcare data model](/blog/articles/healthcare-data-model); another model in this series is the [forex and CFD broker](/blog/articles/forex-broker-data-model) one. The free models describe a business; they do not connect to your practice-management system.

## Where OWOX Data Marts fits, and what it does not do

The SQL above is BigQuery’s dialect and ports to other warehouses with small changes; choose the warehouse region with your data protection officer. OWOX Data Marts takes on three parts:

*   **Getting the data in:** built-in [sources](/data-sources) for the ad platforms, and a [custom connector](https://docs.owox.com/docs/connectors/custom-connectors/) for a practice-management or call-tracking API.
*   **Owning the definitions:** each table becomes a Data Mart with its primary key in one [data model](/product/data-modeling), each metric a [calculated field](https://docs.owox.com/docs/getting-started/setup-guide/calculated-fields/), and [data quality checks](/product/data-quality) catch an event loaded twice.
*   **Delivery:** scheduled reports to [Google Sheets](/product/spreadsheets); through the [MCP server](https://docs.owox.com/docs/getting-started/setup-guide/mcp/), part of OWOX Data Marts Cloud, Claude or ChatGPT can query published Data Marts, and **the rows returned are sent to that AI provider**. By default, give an assistant only aggregated Data Marts with no patient key.

**What it does not do:** it does not write the SQL that builds `practitioner_day` or matches patients, and it does not set the late-cancellation cut-off. It does not recognise revenue or replace finance’s figures, and it does not make your network compliant with anything.

## For the person who asked

A note from the analyst to whoever asked for the numbers, ready to forward:

*   I can give you bookings, utilisation, no-shows, revenue per clinician and per location, how many new patients come back, and marketing cost per new patient, monthly, for every clinic.
*   **Patient data is designed to stay protected, subject to our data protection officer’s sign-off.** Reports will use a coded patient key, never names or contact details, and the table that links the two will be readable only by the people who already handle patient records. Whether patient-level data may go into the warehouse at all is for \[data protection officer or compliance lead\] to sign off before we start. No AI tool will see patient-level data; at most, totals.
*   **Revenue is not cash.** Revenue is the treatment delivered in the month, including the part the insurer owes us. Cash is what patients and insurers have actually paid. In a made-up two-day example, the clinics delivered €710 of treatment and collected €320; an insurer owed the other €390. I will show all three side by side.
*   Revenue follows accounting rules. **Utilisation, no-shows and retention follow ours**, and they need to be set. In the same example, the no-show rate was 25%, 29% if appointments cancelled days ahead are left out, or 43% if last-minute cancellations also count as missed. Diaries were 63% booked but only 38% used for treatment. One clinician looked weakest per rostered hour, mostly because 210 minutes of their diary were never booked, and strongest per hour spent treating.
*   **Decisions for you:**
    *   How late can a patient cancel before it counts as **missed**, like a no-show?
    *   Is **utilisation** booked time or treated time, and is the bottleneck the clinician or the room?
    *   Who owns **roster accuracy** at each clinic, and do **locums** count as capacity?
    *   Is revenue per clinician measured per **hour rostered** or per **hour spent treating**?
    *   How long can a patient stay away and still count as **retained**, for each service line?
    *   Which **source** gets the credit for a new patient: the first time they contacted us, or the contact that led to their first visit? (Or leave it to marketing.)
*   **Decisions for finance:**
    *   That **revenue** is recognised when treatment is delivered; the warehouse computes it from treatment lines and reconciles to finance’s ledger each month, and the ledger wins.
    *   **Revenue by type** (consultations, procedures, drugs, retail), so a clinician is not credited with dispensary sales.
    *   **VAT** treatment, and **treatments recorded after month-end close**: restate the month or count them in the next.
    *   **Rejected claims**: when they pass back to the patient or owner, and when a balance is written off.
    *   How to estimate what **insurers** will actually pay against what we bill.
    *   How **prepaid** courses, packages and memberships become revenue as they are used.
    *   The **exchange rate**, if our clinics bill in more than one currency.
*   **How the disputes end.** You sign off these definitions, and I publish them in one place that every report reads. A clinic manager who disagrees takes it up with you as a change to the definition, not with me as an argument about the figure. Every change is dated and versioned.
*   **Until you decide**, I will count a cancellation less than 24 hours before the appointment as a late cancellation and report it next to no-shows, not inside them; report utilisation both ways; report revenue per open hour and per treated hour side by side; count a patient as retained if they come back within six months; credit a new patient to the enquiry behind their first visit; and say so at the top of the report.

## Your New Normal

Turn your data into decisions.

Governed data marts give you the clean foundation ML needs to actually work.

*   No AI hallucinations
*   Analyst-governed definitions
*   Every number traces to SQL

[Get started free](https://www.owox.com/app-signup)

## FAQ

## Frequently Asked Questions

Is there a free clinic network data model to start from?

Yes. OWOX publishes a free [Healthcare Clinic Network data model](https://www.owox.com/data-models/healthcare-clinic-network) of 10 Data Marts, defined [on GitHub](https://github.com/OWOX/models/tree/main/bundles/healthcare-clinic-network). It opens on the OWOX canvas in the browser, where you can edit it and export it as an OKF bundle of Markdown files. It has no schedule slots and no insurer and patient shares, and its Patients table holds names and contact details, so a real network adds capacity and invoice lines and moves the identifiers into a restricted table.

How should patient identifiers be handled in a clinic data model?

Report on a pseudonymised patient key and keep names, dates of birth and contact details in one restricted table outside the reporting layer, readable only by the people who already handle patient records. Pseudonymised data is still personal data under the GDPR ([Recital 26](https://gdpr-info.eu/recitals/no-26/)), and health data is a special category ([Article 9](https://gdpr-info.eu/art-9-gdpr/)), so what your network may do with it is for its own compliance lead to decide; the point of the key is that dashboards, spreadsheets and AI assistants never need an identifier.

Should marketing cost per new patient count bookings or attended visits?

Attended visits. A new patient is someone whose first completed appointment anywhere in the network falls in the period; a booking that ends in a no-show or a late cancellation has acquired nobody. Credit each new patient to the source of the enquiry behind that first visit, and show cost per new booking beside it, because a channel can look cheap per booking and produce few patients who attend.

How should a data model handle no-shows and cancellations?

Keep them as different outcomes, with the history of status changes in an appointment events table: a cancellation with enough notice frees a slot someone else can take, a late cancellation frees one nobody can, and a no-show frees nothing. Store how many hours' notice each cancellation gave and who cancelled, patient or clinic. Then write down the late-cancellation cut-off and whether the no-show rate's base includes early cancellations, because those two choices move the rate more than most changes in patient behaviour.

How do you model the insurer's share and the patient's share?

Bill each treatment line to each payer as its own invoice line: a 200 treatment with an insurer paying 150 is a 150 line to the insurer and a 50 line to the patient. Record payments, refunds and adjustments as rows applied to invoice lines, because one insurer remittance pays many. Revenue is still recognised when the treatment is delivered, at the amount the clinic expects to be entitled to ([IFRS 15](https://www.ifrs.org/issued-standards/list-of-standards/ifrs-15-revenue-from-contracts-with-customers/)); the insurer's share is a receivable until it is paid, not cash.

How is a clinic network data model different from a single-practice one?

A network has clinicians who work at several locations and patients who visit several, so location belongs on the slot and the appointment, not on the clinician or the patient, and one patient key has to hold across locations and practice-management systems. Otherwise a patient who moves clinic counts as new twice and as retained never. A network also adds marketing spend and enquiries across locations, and often more than one currency.

What tables are in a clinic network data model?

At least patients (on a pseudonymised key), locations, clinicians and services as reference tables; schedule slots, one row per bookable stretch of a clinician's time at a location, for capacity; appointments, one row each, and appointment events, one row per change of status; treatment lines, one row per service delivered; invoice lines, one row per amount billed to each payer; payments, one row per amount applied to an invoice line; enquiries and ad spend for acquisition; and a calendar. Two more are built from them: clinician per location per day, which utilisation reads, and each patient's first completed visit, which new patients and retention read. Names and contact details sit in a restricted identity table outside the reporting layer.

## Who wrote this

![Ievgen Krasovytskyi](https://cdn.owox.ai/www/webflow/68404586b341508a789a4aa5_.png/public)

[Ievgen Krasovytskyi](/team/ievgen-krasovytskyi) · Head of Marketing

Ievgen Krasovytskyi is the Head of Marketing at OWOX, leading strategy across content, SEO, product marketing, and AI-powered automation. With deep expertise in analytics infrastructure, data warehouses, and marketing technology, he builds systems that connect marketing performance to business outcomes. Ievgen writes about SaaS growth, analytics workflows, and the future of AI in marketing operations.

[LinkedIn](https://www.linkedin.com/in/ievgen-krasovytskyi-a38a1253/) · [All articles](/team/ievgen-krasovytskyi)

[Data Modeling](/blog/topics/data-modeling)

## Related articles

[Data Modeling · Benefits of data modeling for business reporting · March 3, 2025](/blog/articles/benefits-of-data-modeling)

[Data Modeling · Best Data Modeling Tools in 2026 · July 21, 2026](/blog/articles/best-data-modeling-tools)

[Data Modeling · Best Free ERD Tools in 2026 · July 10, 2026](/blog/articles/best-free-erd-tools)

[See all articles →](/blog/articles)

## Book a call

You are booked. The invitation is on its way to , with the calendar invite and the meeting link.

[Reschedule](#)

[Cancel](#)

## Links

Pages this page links to, on this site and on docs.owox.com. Where the page has a Markdown twin, its address follows the link.

- [Data Modeling](https://www.owox.com/blog/topics/data-modeling) — /blog/topics/data-modeling.md
- [Ievgen Krasovytskyi](https://www.owox.com/team/ievgen-krasovytskyi) — /team/ievgen-krasovytskyi.md
- [Book a Demo](https://www.owox.com/book-a-call) — /book-a-call.md
- [Healthcare Clinic Network data model](https://www.owox.com/data-models/healthcare-clinic-network) — /data-models/healthcare-clinic-network.md
- [OKF](https://www.owox.com/blog/articles/open-knowledge-format-okf) — /blog/articles/open-knowledge-format-okf.md
- [healthcare data model](https://www.owox.com/blog/articles/healthcare-data-model) — /blog/articles/healthcare-data-model.md
- [forex and CFD broker](https://www.owox.com/blog/articles/forex-broker-data-model) — /blog/articles/forex-broker-data-model.md
- [sources](https://www.owox.com/data-sources) — /data-sources.md
- [custom connector](https://docs.owox.com/docs/connectors/custom-connectors)
- [data model](https://www.owox.com/product/data-modeling) — /product/data-modeling.md
- [calculated field](https://docs.owox.com/docs/getting-started/setup-guide/calculated-fields)
- [data quality checks](https://www.owox.com/product/data-quality) — /product/data-quality.md
- [Google Sheets](https://www.owox.com/product/spreadsheets) — /product/spreadsheets.md
- [MCP server](https://docs.owox.com/docs/getting-started/setup-guide/mcp)
- [Get started free](https://www.owox.com/app-signup)
- [Data Modeling · Benefits of data modeling for business reporting · March 3, 2025](https://www.owox.com/blog/articles/benefits-of-data-modeling) — /blog/articles/benefits-of-data-modeling.md
- [Data Modeling · Best Data Modeling Tools in 2026 · July 21, 2026](https://www.owox.com/blog/articles/best-data-modeling-tools) — /blog/articles/best-data-modeling-tools.md
- [Data Modeling · Best Free ERD Tools in 2026 · July 10, 2026](https://www.owox.com/blog/articles/best-free-erd-tools) — /blog/articles/best-free-erd-tools.md
- [See all articles →](https://www.owox.com/blog/articles) — /blog/articles.md