HIPAA-Conscious Conversion Tracking: The Line I Draw

"Our attorney says we cannot put patient data into Google, and our agency says we cannot measure anything without it." That standoff gets settled in a CREATE TABLE statement rather than in a policy document, because the measurement never needed the patient data to begin with. HIPAA-conscious conversion tracking for a medical practice works when the attribution table has no column that a name, a condition or a clinical foreign key could land in.

What crosses the boundary, and what never does?

Only an anonymous click id, a value, and a timestamp ever leave the system, no patient record is persisted for attribution, and no contact detail is stored in any form, hashed or otherwise.

Why is this a schema question rather than a policy question?

Most builds put the control at the exit: a wide record, then a sanitizer that strips sensitive fields on the way out. If a filter is the boundary, the boundary is code, and code gets edited by somebody with a deadline. Put it at the write instead, where a NOT NULL click id and the absence of a free text column decide whether a row can exist.

Two platform facts make that the only posture I will ship. Google offers no business associate agreement for Google Ads or Analytics, so neither can be a covered arrangement and neither may ever receive an identifier. Google's personalized advertising policy also prohibits building audiences on health status, which makes a condition-named landing page a policy problem as well as a privacy one.

Click-based offline import is therefore the one path needing no identity: a click id, a value, a timestamp. Enhanced conversions for leads carries a hashed email and phone, the exact category this table is built not to hold, so it is not banned here so much as unavailable to a schema with no contact columns. What the upload call actually sends is documented separately, and I have shipped a PHI-free offline loop end to end.

I have run these uploads two ways, out of a warehouse and out of a flat scheduled job, and for a clinic I trust the flat job more, because a warehouse tempts you to widen the table. Warehouse doctrine says keep everything raw against questions you cannot foresee, and a covered entity cannot adopt that, for a reason that is gravitational rather than legal: answering a new question is always cheapest by adding a column, and by month six the boundary is a comment in a migration nobody has read.

What does the table look like when it cannot hold PHI?

This table is already published on this site, so read this section as a diff rather than as a reveal. The full click-to-arrival loop carries an ad_attribution table with an appointment reference that is deliberately not a foreign key, a click id, a status that walks from booked to arrived to uploaded, and two timestamps. That shape is right and I am not restaging it. Three edits turn a table that happens to contain no PHI into one that cannot.

Delete the value column. The hub stores a number a person assigns from a rate sheet, which is fine for exactly as long as a person is the one assigning it. A stored amount carries no provenance, so nothing in the schema can distinguish a list price from a figure read off an invoice.

Constrain status to five words. The hub declares it as free text with a default. Once a CHECK fixes the vocabulary, attended cannot quietly start meaning "attended, hormone consult" the week somebody wants that in a report.

Point service_code at a lookup with a foreign key. A label is a string anybody can type, whereas a foreign key means a code missing from the price list cannot be written at all, which is what makes the value join below deterministic rather than best effort.

CREATE TABLE paid_attribution (
  row_id       BIGSERIAL PRIMARY KEY,
  booking_ref  TEXT        NOT NULL,   -- opaque, no FK into clinical tables
  click_id     TEXT        NOT NULL,   -- no click id, no row. This is the undercount.
  service_code TEXT        NOT NULL REFERENCES service_value(service_code),
  status       TEXT        NOT NULL DEFAULT 'booked'
               CHECK (status IN ('booked','attended','no_show','uploaded','retracted')),
  booked_at    TIMESTAMPTZ NOT NULL,
  uploaded_at  TIMESTAMPTZ
);
-- No value column, on purpose. The amount is joined from service_value at upload time.

booking_ref resolves only inside the practice system, with no foreign key and no grant into the clinical schema, so the join a curious analyst wants cannot be run.

The deliverable is not those seven columns but the absent ones, because absence is what a compliance officer can rule on: patient_name, patient_id, email, phone, phone_sha256, date_of_birth, address, zip, insurance_id, provider_id, appointment_type_label, diagnosis_code, chief_complaint, notes, landing_page, page_title, referrer, call_recording_url, transcript, amount_usd. That list is the first artifact I hand over on a HIPAA-conscious patient conversion tracking build, before a tag exists.

The lookup on the other side of that foreign key is the only place a dollar figure lives, and its columns are what make the amount auditable:

CREATE TABLE service_value (
  service_code TEXT PRIMARY KEY,        -- a billing bucket, never a condition
  value_usd    NUMERIC(10,2) NOT NULL,
  set_by       TEXT          NOT NULL,  -- a person, named
  set_on       DATE          NOT NULL
);

Every row is a price somebody set on a date, so any uploaded amount traces back to a named decision rather than to a chart. One honest caveat: service_code is the one column in paid_attribution that is arguably a clinical fact. It never leaves the practice, only the dollar amount does, and if your service list is granular enough that a code would name a condition, collapse it into price bands until it does not.

The amount is produced at upload time by a join, never stored:

SELECT a.click_id,
       s.value_usd AS conversion_value,
       a.booked_at
FROM paid_attribution a
JOIN service_value s ON s.service_code = a.service_code
WHERE a.status = 'attended'
  AND a.uploaded_at IS NULL;

The number has one source, and the table has nowhere to keep another. Which number to send as the conversion value is a separate argument; this article stops at provenance.

One more place where I contradict the hub on purpose: it says that if a phone number is ever needed as a fallback match key, hash it with SHA-256 before it is stored, and my version does not store it in any form. A SHA-256 hash of a phone number is a hashed HIPAA identifier, and ten-digit US number space is small enough to enumerate exhaustively, so hashing reduces exposure without de-identifying anything. That is why phone_sha256 is not a column: the hash lives inside the call matching job long enough to find a click id, and dies with the process.

What does the constraint stop, and what does it not?

A constraint has an opinion about a value. It has none at all about a column.

Everything on the left outlives whoever set it up, and everything on the right lasts exactly as long as somebody keeps owning it.

That third branch is the whole risk, and it is a human one. A migration moves the boundary by definition, so the question is only whether anyone reads it. Caught by a schema review, you keep it, narrow it or revert it. Missed, the boundary quietly becomes whatever the last migration decided, and the worst version of that is subtle: a value that used to be joined from a price lookup starts being read off a bill instead. It passes every constraint, it passes every scanner, and it is the one change on this page I would actually lose sleep over.

What does this boundary cost you in conversions?

Rows, and how many is arithmetic that starts from paid volume. The arithmetic here is illustrative. Synthetic figures, proportions typical. A practice booking 40 appointments a month from paid clicks, at 95 percent click id coverage, writes 38 rows. If 62 percent reach attended status, the offline action receives about 23 conversions a month, which does not clear the at least 30 in the past 30 days that Google's published Target CPA guidance asks for today. Halve the volume: 22 paid bookings, 21 rows, 13 uploads, which is not a bidding signal at all.

Work that backwards and the entry price for bidding on attended appointments is roughly 50 paid bookings a month. My own rule is stricter, and it is about the pipeline rather than the bidder: I do not point a bidding strategy at an offline action until it has cleared 30 in 30 twice running, because the first month of any new upload is the month you are still finding the booking paths that never captured a click id.

So the rule: under about 30 uploaded conversions a month, keep the offline action as a secondary conversion for reporting and let bidding keep optimizing on the booking action, because a sparse honest signal is worse for the bidder than a dense imperfect one. What makes an account that small is volume, not the boundary. If your paid bookings sit under 50 a month, conversion tracking for a medical practice is a reporting upgrade rather than a bidding one, and still worth having at that size, because deciding which campaign to cut needs the attended count whether or not a bidder ever reads it.

The ledger is illustrative on the same terms.

Four readings, one table. Appointments against rows written is the comparison nobody may run: 292 against 1,503 is 19 percent, and that is the NOT NULL click id working as designed. Paid bookings against rows written is the one that means something, and it fell from 0.977 to 0.816 in March: 66 paid-click bookings with nowhere to be written, which is what a booking page shipped without the click id field looks like from outside. That part is solved work: capturing the click id without breaking the form. Rows uploaded against Google Ads conversions is the upload check, and February's 188 against 186 is a time zone boundary rather than a defect.

The fourth reading is the new column, and the only ratio here that describes the practice rather than the tracking. Attended against rows written holds at 0.62 all three months. When it moves several points, somebody changed what attended means in the scheduling system or the front desk stopped marking arrivals, and none of the other three ratios will show it.

So: healthy coverage is 0.95 to 0.98, a fall of more than 5 points month over month means a booking path shipped without the field, and uploaded rows should hold at 0.97 or better of Google Ads conversions in a settled seven-day window. Never measure against total appointments, because a NOT NULL click id makes this table a deliberate undercount. A runbook that tells you to investigate a 10 to 15 percent gap between Google Ads and the practice system was written for a different design, and it will flag this one as broken every month.

Which changes quietly widen the table after go-live?

None of these is a bad actor. Every one is somebody doing their job.

1. A notes column for the front desk. It fills within a week with "call back Tuesday, wants the hormone consult price". Adding a column is a migration, not a rejected write, so only a schema diff catches it.
2. A landing_page column added for a chart. The booking URL carries ?service=hormone-therapy, nobody intended a condition to land in the table, and one did. The one I watch for.
3. The amount read off the invoice instead of joined from the lookup. It passes every NOT NULL, every CHECK and every PHI scanner, and it is still a patient-specific fact leaving the building.

How do I check, three months later, that the boundary held?

Two artifacts and one counting test, all runnable without me. Artifact one is the schema rather than the payload:

pg_dump --schema-only --table=paid_attribution "$DATABASE_URL" > /tmp/paid_attribution.now.sql
diff schema/paid_attribution.sql /tmp/paid_attribution.now.sql

The question is not whether the payload looked clean the day somebody checked, but whether the structure still refuses what it refused in January. Artifact two is one outbound payload from that day's uploader log, read field by field against the column list: you are hunting a field that should not exist, not a bad value.

The counting test is the part nobody publishes:

SELECT COUNT(DISTINCT amount_usd) AS distinct_amounts
FROM upload_log
WHERE uploaded_at > now() - interval '30 days';
 
SELECT COUNT(*) AS service_codes FROM service_value;

More distinct amounts than service codes means the number stopped coming from the lookup and started coming off a bill, and no scanner will flag it, because a dollar amount is not one of the eighteen identifiers. Run it at month three, and again every time a booking page is redeployed, because a redeploy is when a column gets added. A calendar reminder in June will not sit next to April's migration. That re-read is part of the attribution build I ship for practices.

Where does my job stop and your compliance officer's start?

I build the boundary. I do not rule on it. What follows is engineering context, not legal advice.

The vacated HHS OCR tracking bulletin is the thing every practice asks about, and the operational answer fits in one sentence: American Hospital Association v. Becerra reached the rule about IP addresses on unauthenticated public pages, and it reached nothing about portals, booking flows, intake forms or confirmation pages, which is where every conversion in this design originates. Nothing in this schema moved on either side of that decision, which is what putting the control in the structure buys you.

Two state laws reach past HIPAA, which is why the boundary holds for readers who are not covered entities. Washington's My Health My Data Act came into force for most businesses on March 31, 2024, with a private right of action through the state Consumer Protection Act. Nevada SB 370 came into force the same day without one. Both define consumer health data far more broadly than HIPAA and bind organizations that never see a chart: a med spa that is not a covered entity still cannot build a remarketing audience off a condition-named landing page.

So hand your compliance officer a sentence instead of a vendor deck: the attribution table has no column that can hold protected health information, and a row cannot exist without an ad click id. That is a finite claim about a structure, which is why it can be ruled on in one meeting rather than a review cycle, and it is why I would rather argue about a column list than about a policy PDF.

Two decisions come back out of that meeting, and neither is mine to make. Whether the booking URL may name a service: if the answer is no, landing_page stays out of the schema permanently and the URL convention gets written down beside the column list, because that is the one path by which a condition enters a marketing system without anybody deciding to put it there. And whether a hashed contact detail may ever be persisted here: if that is ever going to be a yes, say so before the build, because enhanced conversions for leads is a different design with a different agreement chain, and bolting it on afterwards is the migration that widens a clean table. Agree on one more thing while everyone is in the room, the sentence the practice says if a patient asks what this table holds: an ad click id, a booking reference, a service code, a status and two timestamps.

If nobody at your practice will still own that column list a year from now, do not start with the tracking. Start by deciding who signs off on a schema change, because a boundary with no owner survives exactly until the first convenient migration.