Why Your Offline Conversion Upload Double-Counts

Count the rows, count the keys, count the visits, and refuse to send until all three are the same integer. When Google Ads reports more booked appointments than your practice management system has records for, the duplicates were almost certainly born in your own staging table hours before Google ever saw them. Google does deduplicate what you send, but the conversion timestamp sits inside the key it builds, so a row whose time moved by one second is not a duplicate to Google. It is a new patient.

Quick answer

• Three counts must match before a batch leaves your system: distinct visits you meant to send, rows in the file, distinct keys Google will build from it.
• Google deduplicates on the order id inside a conversion action, or otherwise on click id plus conversion name plus conversion time. The timestamp is part of the key.
• The check most people write, rows over distinct dedupe keys, misses the commonest cause in a practice: a rescheduled visit coming back under a new key.
• Catching that needs a ledger of every visit id already sent, and a job that skips the upload when a check fails.
• The gate below is three checks, called A, B and C throughout: A is duplicates inside tonight's batch, B is a visit already sent on an earlier night, C is two real conversions Google will collapse into one.

Everything below assumes you already have offline conversion tracking running and want to keep it honest.

What does a double-counted conversion look like in the account, and why do Google's own upload diagnostics call the pipeline healthy?

It does not look like an error, which is the whole problem.

Start with the count setting, because it decides whether the defect is visible at all. Count One reports a single conversion per ad interaction and discards the rest, Count Every reports all of them. So the same defect can be silent on your booking action and doubled on your call action, in one account, on one night.

Then look where you would go for reassurance. Google's offline data diagnostics report events received, events successfully processed, pending events and errors by type, refreshed on the most recent full calendar day. Every duplicate clears all of that, because it is well formed, carries a valid click id and points at a click that really happened, so the panel certifies a healthy import for an account that is quietly wrong. Nothing in that view is ever compared against the number of appointments actually on your schedule, which is the only comparison that would catch this.

Here is what that buys, on a synthetic fourteen day window I use for the rest of this piece: six percent inflation across the full window, running at ten percent by the last seven days once the defect is established. Calibrated value $265, target 3.50.

Synthetic figures, proportions typical. Twenty-two conversions no visit backs put $5,830 of invented revenue into the account. Against the 3.50 target, reported blended ROAS clears the bar by 0.38 while the truth clears it by 0.17, so more than half the headroom you would spend into is not there.

The blended number is not the part that costs money. Duplicates do not spread evenly, because reschedules concentrate in the campaign selling the appointment with the longest lead time. Reported, the first campaign beats the second by 25 percent against a real gap of 14 percent. The bidder reads the split rather than the average, so it reallocates about twice as hard toward the reschedule problem as the truth justifies. Inflation spread evenly is something you can mentally discount; inflation that concentrates pushes budget into the exact campaign where the defect lives. It also multiplies the value you attach to each conversion, so a value-based strategy compounds the error rather than averaging it away.

Why does the duplicate check you would write first pass for a week while the account inflates?

The failure signature I reach for first is rows over distinct dedupe keys, and what I learned the hard way is that on its own it is the wrong ratio.

On a build where the nightly export windowed on updated_at, the only reliable change marker the practice system exposed, a rescheduled visit came back a second time carrying a rewritten timestamp. The key contained the appointment time, so the second copy minted a fresh key. Rows over distinct keys sat at a perfectly clean 1.000 through the entire week the account was inflating. The ratio that actually moved was distinct keys over distinct visit ids, and I only computed it because the numbers refused to reconcile after the first check passed.

Synthetic figures, proportions typical. Fourteen day totals: 408 rows staged, 408 distinct keys, 386 distinct visits. The column everyone builds reads 1.000 on all fourteen days. The reschedule flow went live in the booking widget on day 5, and nothing noticed.

The second ratio cannot be computed inside tonight's batch, because tonight's batch holds one row per visit and looks perfect. It needs a ledger: one row per visit id you have actually sent, written after the upload returns success. That is the warehouse side of marketing analytics doing the only job that matters here, remembering what you already did. If your uploads run at thirty rows a night out of a flat scheduled job, a CSV of sent visit ids next to the job is a ledger.

Where do the duplicate rows come from, and what is the tell for each one?

Five origins cover nearly all of it, each with its own fingerprint in the same three counts. Name the cause from the tell rather than guessing.

Rows one and five are the ones to stare at, because both survive every in-batch check you can write, and they are the two that put a ledger on the critical path. The difference is structural rather than a matter of severity: origins two through four leave two rows sitting in the same file, where any count comparison finds them, while origins one and five leave one row tonight and one row on some other night, under two different keys, with neither file looking wrong on its own.

Google's guarantee sits after your gate, not instead of it, and it closes exactly one of the two exits.

How do you design a dedupe key that survives a re-run, a reschedule, and a privacy review?

Two immutable, system-generated ids, hashed together, and nothing else.

-- The key. Two ids the source system is not allowed to rewrite.
SELECT
  visit_id,
  click_id,
  TO_HEX(SHA256(CONCAT(visit_id, '|', click_id))) AS dedupe_key,
  conversion_action,
  conversion_time,
  conversion_value,
  booking_created_at,
  ingested_at
FROM `warehouse.staging_conversions`
WHERE run_date = @run_date;

Every omission is deliberate. The appointment time is out because a reschedule rewrites it. A status field is out because a status is something the source system may change. A row id generated at load is out because it does not survive a re-run, the exact event you are trying to detect.

The tempting shortcut is worse than any of them. Hashing the patient phone number plus the appointment date gives you a key that is stable and always present, and also a stable pseudonymous patient identifier that lands in your warehouse, in job logs and in every alert payload, and re-identifies trivially against the practice system. Visit id and click id are machine-generated, meaningless outside the practice, and neither describes a person.

Two rules follow. Alerts carry counts, an assertion name and a run date, never rows, because a message listing three offending visit ids is an appointment record crossing a boundary. And none of this changes the payload: only an anonymous click id, a value, and a timestamp ever leave the system, and no patient record is persisted for attribution. That is a HIPAA-conscious engineering posture, not legal advice, and I do not call the work HIPAA compliant.

Which row survives the collapse, and which timestamp and value does it carry?

Deduplicating is two decisions and most write-ups make only the first. Detecting a duplicate is easy. Choosing which copy is the truth is what changes what Google learns.

-- Keep the earliest booking, with ingest time only as a tiebreak.
SELECT * EXCEPT(rn)
FROM (
  SELECT
    s.*,
    ROW_NUMBER() OVER (
      PARTITION BY dedupe_key
      ORDER BY booking_created_at ASC, ingested_at ASC
    ) AS rn
  FROM staged s
)
WHERE rn = 1;

Ordering on the booking time first means a re-run cannot change which row wins, which makes the job idempotent. The surviving row keeps the conversion time from the original booking, not from the reschedule, because that is the event the click produced. Re-stamp it and you have not removed a duplicate, you have minted one under a fresh Google key.

The value travels unchanged too, and that is the decision this query quietly makes for you. When a reschedule also moves the visit onto a different service line, the two rows disagree on conversion value, and keeping the earliest row keeps the value that was true at booking. I keep it deliberately, because the value is supposed to describe what the click bought. If the service line genuinely changed later, that is a restatement of a conversion you already sent, filed as an adjustment against the same conversion, with its own window and its own upload path. It is not a row in tonight's batch, and a good share of the double counting I have had to unpick started with somebody sending the new value as a new row.

The modeling choice underneath all of it: a reschedule is the same conversion carrying a new date on the calendar, so the row that survives has to keep the time of the click that caused it.

What runs before every upload, and what happens on the night it fails?

Three assertions, in one file, run before the sender is called. Each returns zero rows when the batch is clean.

-- guard.sql. Any row returned means tonight's upload does not happen.
WITH staged AS (
  SELECT
    visit_id,
    click_id,
    conversion_action,
    conversion_time,
    TO_HEX(SHA256(CONCAT(visit_id, '|', click_id))) AS dedupe_key
  FROM `warehouse.staging_conversions`
  WHERE run_date = @run_date
)
 
-- A. Duplicates inside tonight's batch: retries, overlapping windows, half re-runs.
SELECT 'A rows_per_key' AS assertion,
       CAST(COUNT(*) / COUNT(DISTINCT dedupe_key) AS STRING) AS observed
FROM staged
HAVING COUNT(*) / COUNT(DISTINCT dedupe_key) > 1.0
 
UNION ALL
 
-- B. The same visit under a second key, already sent on an earlier night.
SELECT 'B visit_already_sent',
       CAST(COUNT(*) AS STRING)
FROM staged s
JOIN `warehouse.conversions_sent` t USING (visit_id)
HAVING COUNT(*) > 0
 
UNION ALL
 
-- C. Two real conversions Google will collapse into one on its own key.
SELECT 'C google_key_collision',
       CAST(COUNT(*) - COUNT(DISTINCT FORMAT('%s|%s|%d',
         click_id, conversion_action, UNIX_SECONDS(conversion_time))) AS STRING)
FROM staged
HAVING COUNT(*) > COUNT(DISTINCT FORMAT('%s|%s|%d',
         click_id, conversion_action, UNIX_SECONDS(conversion_time)));

Read them as rules, not as dials. A is exact equality, with no tolerance band, because any tolerance you allow is a decision to ship that many duplicates. B is zero staged visit ids already in the ledger. The one exception is a system that reuses visit ids across appointments, and that is a build problem, not a threshold: you need a different immutable id first. C is zero collisions on Google's own key, and the fix is to nudge the second conversion time by one second and re-run the check, not to send and hope.

Fail-closed lives in the runner.

#!/usr/bin/env bash
set -euo pipefail
 
RUN_DATE="${1:-$(date -u -v-1d +%F)}"
 
fails=$(bq query --nouse_legacy_sql --format=csv \
  --parameter="run_date:DATE:${RUN_DATE}" < guard.sql \
  | tail -n +2 | wc -l | tr -d ' ')
 
if [ "$fails" -ne 0 ]; then
  notify "upload skipped for ${RUN_DATE}, ${fails} assertion(s) failed"
  exit 1
fi
 
python3 upload.py --date "${RUN_DATE}"        # non-zero exit on a failed batch
python3 record_sent.py --date "${RUN_DATE}"   # ledger insert, only after success

The order matters. The ledger insert runs after the upload returns success, because a ledger that records intent rather than delivery will cheerfully tell you a batch that never left was already sent.

A fourth number is worth watching but does not belong in the gate: reported conversions over distinct visits sent, on a rolling seven days rather than one, because a single quiet Sunday puts a daily ratio at 1.07 with no bug behind it. If it sits above 1.000 while all three assertions pass, look for a web tag firing the same conversion action on-site.

On the night the guard trips, the right behavior is to do nothing and say so. A skipped night re-uploaded inside 24 hours is invisible to bidding. Past seven days, treat a late backfill as reporting rather than steering, because the window that moves bids has closed. I have run these uploads out of a warehouse and out of a flat scheduled job, including the offline conversion loop I built for a multi-location practice, and fail-closed is the piece I would not drop from either. It is the part of an offline conversion tracking setup nobody asks for and everybody needs.

When your ratios are clean and Google still drops a conversion you were owed

Now run the same math backward.

A patient books two different appointments in one session, or a batch job stamps two real conversions with the same second. Two rows, two visit ids, two dedupe keys. Both duplicate ratios read a clean 1.000 and both are correct. Then Google builds its own key from click id, conversion action and conversion time, finds them identical, collapses them, and reports one.

You did not double count. You under-counted, and nothing tells you: no error, no rejected row, no line in the diagnostics panel, because from Google's side that second row is exactly what deduplication exists to remove.

Assertion C is the check for it, and the fix is the one Google's own guidance gives: give each real conversion a unique timestamp. Which is why the rule at the top counts three things rather than hunting for duplicates. More rows than dedupe keys and you are about to over-report. More rows than the distinct keys Google will build from them and you are about to under-report. The same three counts, run once, catch both directions.

You already sent duplicates. Do you retract them, or fix forward?

Freeze first. Disable the scheduled job before you touch anything, and do not fix the key and re-upload the same night, because the corrected batch will re-send every visit the broken batch already sent.

Then quantify from the ledger, not from the account: which action, which upload dates, how many rows duplicated, and whether it feeds bidding. With no ledger this is the step you cannot perform, and it is where this argument stops being theoretical.

Then decide against a threshold. Retract if the duplicated rows exceed 2 percent of that action's trailing 30-day volume, or if the action feeds bidding at all. Below that, and observation only, leave them and fix forward, because a retraction batch is itself a shock to the same model. A retraction removes the conversion completely from your reports, runs inside a 54-day adjustment window measured from when the conversion was recorded, and goes out over the Data Manager API upload path. Only an adjustment landing within about 7 days of the conversion still influences Smart Bidding, so a retraction sent later cleans up the report without undoing what the bidder already learned. That is the uncomfortable half of the 2 percent rule: past the first week you are correcting bookkeeping, and the reason to do it anyway is that every period comparison you run for the next year reads those reports. And do not re-enable value-based bidding until fourteen consecutive nights have passed all three assertions. No-shows and cancellations are a different problem with a different rule.

What should run every night so you find this before your client does?

Four things, in this order, with no human in the loop.

1. Build the staging table and compute the key.
2. Run guard.sql. Any returned row means the alert fires and the upload does not.
3. Upload, then write the ledger, and only on success.
4. Recompute the rolling seven day ratio, and flag it the moment it leaves 1.000.

The first three are a job any scheduler can run, while the fourth is where a monitoring agent sitting on top of the pipeline earns its place, because that number moves slowly, never throws an error, and no dashboard you own is pointed at it.

Take the asymmetry with you, because it is the entire argument for a check that blocks rather than warns. A skipped upload costs you one night of conversions, and you can backfill it before lunch once the key is fixed. A duplicated upload that processed cleanly costs you a bidding model that spent a week learning to buy more of a patient who was never on the schedule, and no retraction hands that week back. So put the rule where the job can read it, not where you can remember it: if any assertion is not exactly clean, nothing gets sent and somebody gets told. One of those is a bad morning. The other is a bad quarter that never shows up as an error.