Healthcare Clinic Attribution Data Model

Call

Every telephone conversation between the practice and someone enquiring. A phone call and a web form are the same step in the funnel, not two funnels: both are the first engagement, both create the same CRM record, and both carry the same advertising identifiers when the caller was on the website. A report built on web forms alone leaves telephone enquiries out and credits the wrong channels.

Those identifiers are empty for a caller with no web history, and that is the record being accurate rather than a tracking defect. Someone rings a number from a leaflet, a sign or a recommendation, having never opened the site; there is no visit to match them to and no click to credit. Dropping unattributed calls makes the measurable channels look better than they are, so they are kept and reported as demand the practice knows it has and cannot yet trace.

direction separates calls received from calls placed: a first screen can begin with the practice ringing back, and treating a returned call as a fresh enquiry would double-count demand.

Fields

Relationships

Click

Every click on one of the practice's ads, held as its own record rather than as columns on whatever happened next, and keyed the same way on every platform so clicks across Google, Meta, Microsoft and TikTok count in one place. session_id is empty when the click produced no visit — the person tapped and went straight back, the page failed, tracking was blocked — and those clicks were still paid for, so dropping them is how advertising quietly looks better than it was.

platform_click_id holds the raw value the ad platform itself attached: the gclid, fbclid, msclkid or ttclid. It is the harder evidence of a paid arrival, and needs one guard: a value shorter than ten characters should be treated as absent — below that what turns up is undefined, null, empty strings and truncated fragments, which inflate whichever channel they land in. Lesley van de Mortel, who described this model, uses that minimum in her own SQL and pairs it with a check on utm_source and utm_medium, so a claimed channel agrees with itself from two directions.

Fields

Relationships

Consultation

Where an enquiry is judged. A practice screens in two steps, both held here, told apart by type and by who conducted them: they differ in who screens, not in what is recorded. A Pre-Consultation is a telephone conversation held by a receptionist or a practice manager — not a clinician — who decides whether the practice is a plausible fit. A Consultation is the appointment that follows: a clinician examines the person and decides whether treatment can go ahead. The first step exists because the second is expensive: without it every enquiry would go straight to a clinician, and no practice has the hours for that.

Qualification is decided here, and only here. The enquiry record carries a field of the same name, but it is empty: at the moment of a form or a call nobody has yet formed a view. One enquiry can be screened more than once, so it may have several rows here, to be read in order. disqualification_reason carries the practice's own account of a refusal, which turns a low acceptance rate from a count into a diagnosis and shows which campaigns bring people the practice can actually treat.

Fields

Relationships

Customer (Patient)

The patient once money is involved. A person exists from the first anonymous visit; a customer exists once there is an invoice or revenue against them — not when someone enquires, and not when a clinician agrees to treat them — and that is the line between an audience and a patient list. In healthcare the two are the same human, so this is one record rather than two: customer_id is this model's key and patient_id the clinical system's, so a commercial report and a clinical record reconcile without renaming anything. That is not universal — elsewhere an employer or a school pays for the people being served, and payer and patient are separate records.

One customer covers several treatments and several payments, so counting rows here counts patients while counting treatments, invoices or payments counts events, and with returning patients the two are not the same. person_id resolves a paying patient back to the anonymous first visit, so the advertising that started a journey can be credited with the money that ended it, however long the gap.

Fields

Relationships

Employee

Everyone at the practice who takes part in turning an enquiry into a booked treatment — clinicians, receptionists and practice managers alike — kept as one list and told apart by type: Doctor, Receptionist, Manager. They are one mart rather than three because they carry the same properties and differ only in what they do; splitting them would duplicate the same five fields three times and make every question about staff a question asked three times.

Both screening steps are staffed from here: the phone call that decides whether an enquiry is worth an appointment, and the appointment itself, where a clinician decides whether treatment can go ahead. Because both consultations point at this one mart, the same question — who held it, and how did it end — answers for either step, which is what lets a practice see where its enquiries are actually lost, and to whom. Without it every enquiry would appear to go straight to the doctor, and the screen that turns away a poor fit would be invisible.

Fields

Form Submission

Every web form the practice receives, with the advertising that produced it attached. A form submission is one of the two ways an enquiry begins; ringing the practice is the other, and the two are the same step in the funnel. A submission opens a CRM record if the person has not enquired before and attaches to the existing one if they have, so one enquiry can produce several rows: counting rows counts contact attempts, not prospective patients.

It carries two families of identifiers worth keeping apart. The five utm_* fields are what the link itself declared, set by hand when the ad was built; the four click identifiers — gclid, fbclid, msclkid, ttclid — are what the advertising platform attached, and they are the harder evidence of the two. As on the ad click, a platform click identifier shorter than ten characters should be treated as absent.

An empty attribution field is normal rather than broken: someone who arrived through unpaid search or typed the address in carries no campaign and no click identifier, and the identifiers of platforms a person did not come through are empty too.

Fields

Relationships

Invoice

A bill the practice has issued, raised against one treatment and one patient. An invoice is a claim on money, not money: it records what is owed and to whom, and nothing here says any of it has arrived. Money actually received is recorded on Revenue, joined by invoice_id: a report on invoices answers "what have we billed", one on revenue "what have we been paid". Read as income they overstate, carrying the full value of work billed, including the part an insurer has not settled and the part that may never be paid at all.

A treatment is billed in two parts. In the United States, where this pattern was described, the patient's copay can be billed as soon as the treatment is booked, is collected before it starts, and is money straight away, though a small share of the total. The rest is claimed from the insurer after the treatment and becomes money only when the insurer pays, with a lag. payer_type tells the two apart. A written_off invoice is delivered work that earned nothing, so it belongs in any honest reading of what a channel produced.

Fields

Relationships

Lead

The enquiry as the practice's CRM holds it: a named person with contact details, created the moment someone first gets in touch, by web form or by phone. It does not exist beforehand, which is why the form submission and the call point at the lead rather than the other way round. person_id traces the enquiry back to the browsing that preceded it by months or years, and the advertising identifiers copied onto the record keep a campaign attached to that name long afterwards. One lead is one person's enquiry, not one contact attempt: the same lead may submit several forms, make several calls, and be screened more than once.

is_qualified is empty on this mart, and that is by design, not by omission. The CRM carries the field from the start, but at the enquiry stage nobody has yet spoken to the person; the decision is taken later, at a consultation, and stored there. A report that counts unqualified enquiries as is_qualified = false will therefore undercount them: the enquiries that were never accepted are not false, they are empty. To judge an enquiry's outcome, look at the consultations attached to it, not at this field.

Fields

Relationships

Page

Every page of the practice's website that a visitor can land on, held once and reused by every view of it. Keeping pages as their own object rather than as a URL repeated on each view is what lets a clinic ask which kinds of page do the work: whether the enquiries that begin on a treatment page and the ones that begin on a pricing page fare the same once a clinician has looked at them. page_type is what lets that question be asked of a kind of page rather than of one URL — whether the visitor was reading about a treatment, checking prices, or browsing the blog.

Fields

Page View

Every page a visitor actually opened, in the order they opened it. Where a page is described once and for all, a page view is one reading of it by one person at one moment — which is what turns a site map into a record of behaviour.

This mart reaches further back than anything else in the model: the very first page view is where a person's identity is minted — before a name, before an email, before anyone at the practice knows a prospective patient is there. It is also what makes "what did they read before they got in touch" answerable. sequence_in_session makes a journey readable as an order rather than a pile of URLs; is_entrance and is_exit mark its two ends: entrances say which pages bring people in, exits say where attention is lost, and those are two different lists.

time_on_page_seconds is measured from when the next page was opened, so it cannot be known for the last page of a visit. That value is empty rather than zero, and treating it as zero understates how long the site was read for.

Fields

Relationships

Person

The human behind everything else in this model. The identity is minted at the very first page view — before anyone has given a name, an email or a phone number — and is never reissued, so a phone at lunchtime, a laptop weeks later and a call months after that all belong to one person. It keeps the source, medium and campaign of that first arrival frozen, so the ad that started a journey is still attached when the money arrives, whether that takes two weeks or twenty years.

Two levels of identity sit here, and keeping them apart is what makes attribution honest. visitor_id, carried on the session, is browser-level — the Google Analytics client_id — and changes with a new device, a cleared cookie or a different browser. person_id sits above it: several of those resolved to one human. Grouping by visitor_id counts devices; grouping by person_id counts people, and a practice's patient numbers only mean anything at the second level. This way of stitching a person together across devices and years is the one APAS® Cloud uses, described for this model by its co-founder.

Fields

Revenue

Money the practice has actually received. Issuing a bill creates a claim; only a payment creates revenue, and the two are kept apart because they are not the same event and do not happen at the same time. A single invoice can be settled in more than one payment, or in none, so this mart is joined to the invoice rather than folded into it.

A treatment is paid for in two pieces. The patient's copay is collected before the treatment starts and is revenue straight away, though a small share of the total. The rest comes from the insurer, after the claim is submitted and with a lag; days_to_payment records that wait, and read with payment_source it says who the practice is waiting on. That lag is why a total taken over the month a treatment happened is not the same as one taken over the month the money landed.

Revenue reaches advertising through the patient, whose person_id goes back to the first visit and the campaign frozen on it. One patient's payments can spread over years, so a campaign total sums a whole history rather than a single purchase.

Fields

Relationships

Session

One visit to the practice's website, from the moment someone arrives to the moment they go quiet; everything seen or clicked in between belongs to it. A session carries the channel that brought this particular visit, the device it happened on and where in the world it came from, so it answers "what was this trip for", while the person record answers "who was this and what first brought them here".

visitor_id is browser-level — the Google Analytics client_id — lost when someone switches device, clears cookies or changes browser; person_id sits above it. Counting distinct visitors counts browsers, counting distinct people counts prospective patients, and only the second means anything for a practice's own numbers.

The UTM fields describe this visit and are deliberately not the first-touch source frozen on the person: someone who arrived through an ad and returned through a branded search has one record of each, which separates the channel that creates demand from the channel that merely collects it. campaign, term and content are empty for visits that carry no campaign at all, the normal state for direct and organic arrivals.

Fields

Relationships

Treatment

The step between a clinician saying yes and the practice being paid. Once a consultation qualifies someone a date is agreed and the treatment is booked, the patient's copay is collected before it starts, and the claim goes to the insurer after it. Every bill and every payment in this model is raised against one of these rows: before this record the model describes demand, after it money.

Being qualified and being treated are two different things. Someone can be cleared and still decide not to go ahead, a drop-off that stays invisible unless bookings are counted separately from verdicts. booked_at and scheduled_date are deliberately separate too: one is when the appointment was agreed, the other the day it was set for, and the distance between them is the practice's waiting time.

A treatment points back at the consultation that cleared it, forward at the patient receiving it and at the clinician delivering it — the same staff list the screenings point at — so a clinician's verdict and the money that followed sit on one chain, and advertising can be read against treatments delivered, not only enquiries taken.

Fields

Relationships