Send EHR Appointment Data to Google Ads Offline Conversions

A working outcome feed is smaller than people expect: one row per appointment status change, carrying the appointment id, the click id, the new status, and the moment it changed. Getting those rows out of an EHR or practice management system comes down to four paths, and your vendor has already decided which are open to you: an update webhook, an API poll, a scheduled report export, or a status field the front desk maintains by hand. This is the extraction half. Capture, upload mechanics and bidding belong to the end-to-end offline conversion loop.

Quick answer

Build hours and rates in this article are illustrative planning assumptions, not quoted figures.

One question sorts you into a path: does your system tell you when an appointment changes, or only when one is created? Everything after that is detail.

What exactly has to come out of the scheduling system?

Write the contract before you open an API doc, because it is what the four paths compete to satisfy: an append-only table, one row per status change, never an update in place, never a row overwritten when the front desk corrects itself.

Two rules ride along. The conversion timestamp is the moment the outcome became true, normally the arrival, and it carries an offset, because a bare local string is guesswork once two locations exist. And Google Ads files an offline conversion against the click date, not the upload date, so a late upload back-dates a number you already read. Choosing between the four implementations of this contract is the first hour of a patient conversion tracking build.

Why is the booking confirmation screen not a data source?

Because it is a screen. It knows what it rendered, not what the schedule accepted. I have shipped the fix for a booking widget that reported success without reading the API response, where the signal fired for appointments that never reached the schedule, and since then I extract from the schedule record only.

The check is cheap, and it is the one time the ad account is the right place to look: count terminal statuses in the schedule for a completed month, then count the same conversion action in the account. Click-date filing means they will not match exactly, but the account cannot legitimately be the larger number. If it is, the feed is reading the widget.

For the same reason the click id rides on the appointment record and never on the patient identity. I have watched an identity match file a visit onto the wrong chart in a live booking flow, and a wrong chart with a right click id credits a conversion to a click that had nothing to do with that visit. Both records look correct, so nothing alerts.

Which extraction path has your vendor actually left open to you?

Whichever path you land on, three gates decide whether it is safe to connect an ad account to it.

Build time and running cost sit in the Quick answer table. What follows is what each path does to you if you build it the obvious way.

Path 1, the update webhook. The vendor posts to your endpoint when an appointment changes. The receiver is the easy half. The replay endpoint and the dead-letter queue decide whether it works, because a missed delivery is silent and there is no second one.

Path 2, the API poll. You ask a read endpoint for appointments changed since a point in time. Re-read a rolling window instead of chasing a pointer: a poll that asks only for what is new looks correct in testing and fails on its first busy Monday.

Path 3, the scheduled report export. The system drops its own status report and you parse it. Read columns by header name, never by position. The vendor will add a column without telling you, and a positional parser does not crash, it shifts every field one to the left.

Cadence is what disqualifies this path when it is slow. Offline click conversion imports must reach Google within 90 days of the click, enhanced conversions for leads within 63, and on an illustrative lag distribution about 30 of those days are gone before the row exists (11 click to booking, 17 booking to visit, 2 to 3 for the status to settle). Daily, or it does not qualify. Both deadlines are Google's to change, so confirm them against current documentation.

Path 4, the staff-marked field with a weekly CSV. Honest, slow, and the only path whose cost never stops. At an illustrative loaded rate of $28 an hour, 30 minutes of front-desk time a week is about $728 a year, plus a guaranteed hole in the week somebody is on vacation. The automated paths cost 10 to 14 more build hours; price those against a bill that recurs forever, at your loaded rate rather than mine.

Score every path against the same three tests: it carries the appointment id, it re-emits a status edited days after the visit, and its counts reconcile against the schedule in both directions.

Which fields leave the practice, and which ones never do?

Design the field list against the HIPAA minimum necessary standard (45 CFR 164.502(b) and 164.514(d)) and it answers itself: appointment id, canonical status, status timestamp, visit timestamp, a value. Nothing else earns its place.

The Safe Harbor identifier list at 45 CFR 164.514(b)(2) is why the rest stays inside the practice boundary: names, contact details, record numbers and dates more specific than a year all sit on it. Only an anonymous click id, a value and a timestamp ever leave, and no patient record is persisted for attribution. From there the upload itself runs through the Data Manager API, a separate article and a separate build step.

Two points page-one guides skip. The extraction job sits inside the covered boundary, because it reads appointment rows, so whoever hosts it needs a business associate agreement even though the three values it sends outward do not. And a cash-pay practice that assumes it is outside HIPAA still has Washington's My Health My Data Act and Nevada's SB370 reaching consumer health data, so the field discipline is the same either way.

For context and not as legal advice: HHS OCR's December 2022 bulletin on tracking technologies was updated in March 2024, and a June 2024 decision in the Northern District of Texas vacated one narrow part of it. That portion reached unauthenticated public pages, not the schedule, so the engineering posture for a booking flow is unchanged. This area is perishable and I last checked it in September 2026. Your counsel confirms it for your practice, not me.

How do I map "seated", "checked in" and "completed" onto one outcome vocabulary?

Vendors name the same event four ways, so the mapping table is an artifact in the repository, not a line in a wiki.

That last row is load-bearing, and the closing section is about nothing else. The trap, though, is the arrived and completed pair, and it is the first thing I check in any pipeline I inherit. Both are terminal and both look like an outcome, so the obvious implementation uploads on each and sends two conversions for one visit. The rule that fixes it: the first terminal outcome per appointment id is the conversion, and everything after it is an adjustment to that conversion, never a new one. I upload on arrived because it is the earliest point the visit is real, which also buys the most room before the click identifier expires.

Reschedule is the one non-terminal loop, and the case a flat table cannot show. Some systems keep the appointment id through a reschedule, some mint a new one. If yours mints a new one and the click id is not carried forward, the outcome arrives orphaned.

Why does a last-updated watermark lose exactly the appointments you are waiting for?

The classic incremental pattern is a high-water mark: store the last processed timestamp, ask the source for rows at or above it, advance the pointer on success. Correct for a well behaved source. A schedule is not one.

What a four-day extract actually looks like

Illustrative extract. Synthetic data, proportions typical. Click ids are shortened for width.

Extraction runs Thursday 2026-04-09 at 21:00, America/New_York. Three rows are the point. apt_4423 was a Tuesday afternoon visit nobody marked until Thursday morning, 41 hours after the patient left. apt_4444 was marked a no-show 25 hours late. apt_4431 came back from a reschedule as a new appointment id, apt_4462, on the same click id.

Run the windows against it, counting terminal statuses only:

One row. One aligner consult. The pointer worked perfectly, advanced cleanly, logged no error, and dropped the most valuable attended appointment of the week. Not a red alert: a slightly short number nobody can falsify, because the only thing that would contradict it is the schedule, and nobody is looking there.

Sizing the window. A 12-row extract cannot produce a percentile, so these come from the larger illustrative distribution behind it, synthetic, proportions typical of a practice whose providers close charts in batches: median status lag 6 hours, P90 31 hours, P95 58 hours. Size off the tail rather than that median, and size the tail off the case you know exists, a provider clearing a week of charts after a vacation, which is the 9-day assumption. Rounded up, plus 5 days of slack, floor 7 and ceiling 30, because past 30 the 90-day click deadline eats the benefit. That lands on 14 days. P95 has its own job below: the age at which reconciliation has to be exact.

Re-reading is only safe if the write is idempotent, so the event key is a deterministic hash of appointment id plus canonical status plus status timestamp. A generated row id does not survive a re-run, and the re-run is the whole design.

-- Rolling re-read into an append-only outcome feed. Requires pgcrypto.
-- Re-reading an unchanged row regenerates the same event_key and writes nothing.
-- A genuine later edit produces a new key and appends a new row.
INSERT INTO outcome_feed (
  event_key, appointment_id, click_id, click_id_type,
  outcome, status_changed_at, visit_start_at, value_usd
)
SELECT
  encode(
    digest(
      a.appointment_id || '|' || m.outcome || '|' ||
      to_char(a.status_changed_at AT TIME ZONE 'UTC', 'YYYYMMDDHH24MISS'),
      'sha256'
    ),
    'hex'
  ),
  a.appointment_id,
  t.click_id,
  t.click_id_type,
  m.outcome,
  a.status_changed_at,
  a.visit_start_at,
  m.value_usd
FROM staged_appointments a
JOIN status_map  m ON m.vendor_status  = a.vendor_status
JOIN attribution t ON t.appointment_id = a.appointment_id
WHERE m.is_terminal
  AND (
        a.visit_start_at    >= now() - make_interval(days => :window_days)
     OR a.status_changed_at >= now() - make_interval(days => :window_days)
  )
ON CONFLICT (event_key) DO NOTHING;

The poll loop carries the same window and one guard: a page that comes back exactly full with no cursor is silent truncation, so fail the run rather than write what you got.

from datetime import timedelta
 
# Sized off the tail, not the typical case: the chart-closing assumption
# (9 days) rounded up, plus 5 days of slack. Floor 7, ceiling 30.
WINDOW_DAYS = 14
PAGE_SIZE = 500
 
def fetch_window(session, base_url, now):
    since = (now - timedelta(days=WINDOW_DAYS)).isoformat()
    cursor, rows_out = None, []
    while True:
        params = {"updated_after": since, "limit": PAGE_SIZE}
        if cursor:
            params["cursor"] = cursor
        r = session.get(f"{base_url}/appointments", params=params, timeout=30)
        r.raise_for_status()
        body = r.json()
        rows = body.get("data", [])
        cursor = body.get("next_cursor")
        if len(rows) == PAGE_SIZE and not cursor:
            raise RuntimeError("full page, no cursor: refusing to commit a truncated run")
        rows_out.extend(rows)
        if not cursor:
            return rows_out

How do I prove the feed is right before it touches the ad account?

Not with a green check in a staging account. With counts, both directions, against the practice's own report.

1. Freeze a comparison window: the 14 completed days ending 3 days ago, so the status lag has settled.
2. Pull the practice's own status report for that window, counted by visit day in its time zone.
3. Count the feed the same way, distinct on appointment id, terminal statuses only.
4. Compare both directions. Schedule rows missing from the feed are a missed extraction. Feed rows missing from the schedule are ghosts: a front-end event, or a staff test booking.
5. For visit days older than the P95 status lag, the gap is zero on both sides. One unexplained row blocks go-live rather than getting averaged away.
6. Re-run after a full weekend, because Monday front-desk behavior does not look like Wednesday's.
7. Require one late edit to survive the pipeline: a status changed more than 24 hours after the visit, appearing in the next batch under the right appointment id with the changed timestamp on it. Find one in the window or wait for one. Nothing else proves the re-read re-reads.
8. Only then connect the ad account, and run in observation first.

-- Pass condition is an empty result set. Anything returned is a blocker.
WITH sched AS (
  SELECT (visit_start_at AT TIME ZONE 'America/New_York')::date AS visit_day,
         count(DISTINCT appointment_id) AS n
  FROM practice_status_report
  WHERE vendor_status IN ('Arrived', 'Checked Out', 'No Show', 'Cancelled')
  GROUP BY 1
),
feed AS (
  SELECT (visit_start_at AT TIME ZONE 'America/New_York')::date AS visit_day,
         count(DISTINCT appointment_id) AS n
  FROM outcome_feed
  WHERE outcome IN ('arrived', 'completed', 'no_show', 'canceled')
  GROUP BY 1
)
SELECT visit_day,
       coalesce(sched.n, 0) AS in_schedule,
       coalesce(feed.n, 0)  AS in_feed
FROM sched FULL OUTER JOIN feed USING (visit_day)
WHERE coalesce(sched.n, 0) <> coalesce(feed.n, 0)
ORDER BY visit_day;

One trap makes step 5 fail forever: the practice report exports in local time and the pipeline stores UTC, so evening appointments land on the wrong day on one side and the gap never closes. Normalize the day boundary first, then look for real misses. Treating this count reconciliation in a conversion tracking build as part of the build rather than optional QA is what stops it repeating.

Who is better off not automating this at all?

If your practice takes a deposit at booking and effectively everyone shows, booked and attended are nearly the same population. The feed is a reporting nicety, not a bidding input: take the weekly CSV and put the money into click capture instead.

The same verdict holds below roughly 25 attended ad-driven appointments a month: the bidder never accumulates enough terminal rows for the gap between booked and attended to move anything. Above that, or as soon as a second location shares the feed, automate it.

One hard stop has nothing to do with volume. If the only path is a nightly PDF, or your vendor agreement restricts automated access, do not build a scraper around it. Run the staff-marked path openly, and put a name and a calendar reminder on it.

Wire the unmapped-status alert before you connect anything

The mapping table's last row decides whether this feed still tells the truth a year from now. Vendors add statuses and practices turn features on: a waitlist goes live, an office starts using "Left without being seen", and a value nobody mapped lands in the staging table.

Every available default is wrong. Default it to booked and those appointments vanish quietly and permanently, and the loss reconciles to nothing, because both systems agree on the rows they can still see. Default it to a terminal outcome and you upload conversions for visits that may never have happened, which is worse, because the bidder learns from them. So there is no default: an unmapped value writes no row and raises an alert carrying the exact vendor string.

-- Any vendor status the map has never seen. Empty result is the pass condition.
SELECT a.vendor_status,
       count(*)                 AS rows_held,
       max(a.status_changed_at) AS last_seen
FROM staged_appointments a
LEFT JOIN status_map m ON m.vendor_status = a.vendor_status
WHERE m.vendor_status IS NULL
GROUP BY 1
ORDER BY rows_held DESC;

Run it against the staging table on every extraction, not against the feed, so the alert fires while those rows are still recoverable. The watermark is the other quiet failure in this article, and it gets a 14-day window. This one gets a human. Everything else in the pipeline is allowed to fail loudly.