Healthcare Clinic Network Data Model

Ad Spend

What the network paid to be found, at the finest grain it is billed: one row per day, channel, campaign, keyword, creative and country, with cost in the original currency and converted to a single one, alongside impressions and clicks. This is the mart to use when the question is which keyword or creative is consuming budget, rather than which channel — that coarser view is already rolled up with its outcomes in Attribution. Because healthcare acquisition costs vary enormously by channel, keyword-level spend is usually where a blended cost per patient turns out to be hiding one very expensive route and one very cheap one.

Fields

Relationships

Attribution

The funnel on one row: for every day, channel, campaign and country, what was spent and what came back — sessions, enquiries, new patients, completed visits, revenue and the lifetime value now attached to the patients that channel produced. This is the mart that answers what a patient costs to acquire, and it is deliberately built on completed visits rather than on bookings, because a booking that turns into a no-show has not acquired anyone. Having spend and outcome side by side at the same grain is what makes cost per new patient comparable across channels without stitching marts together first.

Fields

Clinic

The network's footprint: one row per clinic location, with where it is, what currency it bills in and which practice-management system its records come from. Locations span the United States and Canada, so a clinic carries both its own billing currency and the state or province it sits in — the cut every regional comparison starts from. is_active separates locations that are open and taking patients from ones that have closed or not yet opened, which matters whenever a per-location average would otherwise be dragged down by a site that was dark for part of the period. This is the dimension that turns any patient, visit or enquiry number into a location-level one.

Fields

Communications

Every attempt to reach a patient, and every time one reached us: one row per interaction, with the channel it used, which direction it went, whether it succeeded, and which agent handled it. Calls dominate, because that is how most people still choose a clinician, and the honest measure of a contact centre lives here rather than in the enquiry record — how many attempts it takes before someone answers, which enquiries are given up on, and whether persistence actually changes the outcome. is_first and is_last bracket each conversation, and is_scheduling separates the interactions that were about getting an appointment in the diary from reminders, follow-ups and automated replies.

Fields

Relationships

Leads

The moment someone asks for an appointment: one row per inbound enquiry, whether it arrived as a tracked phone call, a website form, a chat, a lead form on an ad platform, a referral from another physician or typed in by staff. Each enquiry carries the location it was directed to, the session that produced it where the two could be matched, and the state it has since reached — still new, being worked, converted into a patient, or one of the two dead ends: never reached, or rejected with a stated reason. This is the only place in the model where the enquiries that were never reached at all sit next to the ones that converted, which is what makes the size of that loss visible rather than assumed.

Fields

Relationships

Patient Access Agent

The people who answer the phone. Patient access is the function that handles inbound enquiries and books them into a clinician's diary, and since the overwhelming majority of new patients arrive by phone rather than by form, this small team sits directly on the network's growth. One row per agent, with the role they hold — front-line representative, senior representative or supervisor — and the country they work in. On its own the mart is a short staff list; joined to the interaction history it is what makes contact-centre performance answerable at all, because every call, message and booking attempt is attributed to the agent who handled it.

Fields

Patients

Who the network is treating, and what each patient is worth: one row per patient, combining the identity and contact details the clinics hold with the value metrics built on top of their visit history — lifetime value, average value per visit, how many times they have attended, when they first and last came, and how long it has been since. The distinction between a new and an established patient runs through everything here, because the two behave differently on almost every measure that matters, from how much treatment they accept to how likely they are to attend. has_unused_plan and unused_plan_revenue make the recall opportunity explicit: treatment that was planned and paid attention to but never delivered is revenue already earned and still sitting on the table.

Fields

Provider

The clinicians patients actually see: one row per provider, with the specialty they practise and the location they practise at. "Provider" is the collective the industry uses for the physicians, nurse practitioners and physician assistants who deliver care, and specialty is what makes this mart load-bearing — it is the only place the model says whether a visit was primary care, dermatology, orthopedics or behavioral health, and those service lines behave nothing alike on revenue per visit or on how often patients fail to turn up. is_active distinguishes clinicians currently practising from those who have left, so a provider who worked two months of the year is not compared against one who worked twelve.

Fields

Relationships

Sessions

Every visit to the website, before anyone has identified themselves: one row per session, with the campaign, keyword and ad creative that brought it, the page it landed on, the device it came from and the city it came from. This is the widest mart in the model and the top of the acquisition funnel — most sessions never become an enquiry, which is the point, because the ratio between sessions and enquiries by landing page and channel is where wasted spend shows up. It also records consent_at, so sessions that predate tracking consent can be separated from those that carry it, and patient_id on the small minority of sessions the network could later tie back to a known patient.

Fields

Relationships

Visits

Where the network earns: one row per appointment, from the moment it is put in the diary to whatever became of it — attended, cancelled in advance, or simply not turned up to. Keeping cancellations and no-shows apart is deliberate, because they are different problems with different fixes: one gives the clinic a chance to refill the slot and the other does not. Each visit records the clinician who delivered it and the service they delivered, the location and its currency, what it billed both locally and converted, how the patient paid, and where the wider treatment plan stood at the time. It is the only mart that carries revenue, so every question about money starts here.

Fields

Relationships