Test an Offline Conversion Upload Without Wrecking Bids

There are exactly two rehearsals for an offline conversion upload that tell you anything, and neither is what most engineers reach for first. A Google Ads test account cannot serve an ad, so it cannot produce a click, so it holds nothing for your rows to match against, and Google's own test-account documentation says so. The two that work are a replay of settled history you diff instead of send, and a conversion action inside the live account that no bidding strategy is allowed to read.

Why is a bad upload harder to undo than a broken tag?

A broken tag costs you the data you did not collect. A bad upload costs you the data you did collect, plus the bids it bought while nobody was looking.

The synthetic account I use throughout: $22,000 a month in Google Ads spend, about 96 attended appointments a month uploaded, a target CPA of $230, account time zone America/New_York. Illustrative. Synthetic figures, proportions typical. That is $733 a day and 3.2 uploaded conversions, so a week runs about $5,133 and 22 conversions, roughly $233 each. Now take the failure the pre-flight below catches here: the exporter emits UTC instead of the account time zone. Every timestamp is wrong, not some of them, so a corrected re-run matches nothing already in the account and writes a second conversion for every row. That week reports 44 conversions at $117 each, and Smart Bidding reads it as the same campaigns and hours buying appointments at half price.

Recovery is not a rollback. Adjustments are their own upload, they can take up to 24 hours to reflect, and Google's guidance for a new import is to upload daily for one to two conversion cycles before the action is trusted. A cycle here is the real lag, a 4-day median from click to booking plus a 9-day median from booking to visit, about 13 days. The do-over is 26 to 28 days, which at this spend is $20,533 still allocated by a signal you know is wrong. That asymmetry is why every first upload inside offline conversion tracking for a medical practice rehearses before it ships.

Why don't a test account, Preview or validate-only prove anything?

Because none of them answers the only question an upload asks, which is whether a row found a click. Google's test-account documentation is direct about both halves of that (checked 2026-09): test accounts "do not serve ads to users", and "Some features cannot be tested with test accounts. This includes bid simulations, conversion uploads, and billing." An upload is a join. You send a click id, Google looks for the click it belongs to, and a test account never served an ad, so the join has no right-hand side. Use it to prove your OAuth flow, scopes, manager chain, retry and backoff behave when the API says no. That is a rehearsal for your code, not your data.

The file-level Preview in the Google Ads interface is the same category of proof, and it is what most of page one recommends. Preview tells you the columns parse, the dates are dates, and the click ids are shaped like click ids. It does not check ownership. I have watched a Preview pass cleanly on a file whose click ids were captured on a staging host and belonged to a different account, because shape and provenance are different questions and only one is being asked.

Validate-only is the one that surprises people, because it is worse than neutral. Two documents, both checked 2026-09. The Google Ads API reference for UploadClickConversionsRequest describes validate_only as: "If true, the request is validated but not executed. Only errors are returned, not results." The Data Manager API diagnostics guide adds, in full: "You can only retrieve diagnostics for requests that succeed and don't have validate_only set to true."

Join those and the dry run inverts. Turning validate-only on switches off the only report that could have told you whether a single row matched a click, so you trade the signal you wanted for confirmation that your JSON is JSON. I have not seen a per-row match verdict come back from one and the reference does not describe one, so treat the flag as a schema linter in CI. The ingest call itself lives in the Data Manager API upload path.

Replay settled history and diff it against what actually went out

This is the rehearsal that costs nothing and catches the most: rebuild past dates exactly as the production job would build them today, then compare the rebuild against the upload log for those dates. Nothing leaves your infrastructure.

Three thresholds I hold to, and they are mine rather than Google's:

• Go back 10 to 21 days. Old enough that restatements have settled, recent enough that nobody has edited the source rows.
• Widen the window until it holds 20 rows. At three uploads a day that is a week, not a day. A diff over four rows has no content.
• Diff tolerance is zero. A diff is not a tolerance band. The only acceptable delta is one you can name, in a sentence, with a reason.

-- Rebuild a settled window from the same narrow view the production job reads.
-- One day if a day carries 20 rows, a week if it does not.
-- Output is a fixture. Nothing here is sent anywhere.
select
  b.booking_id,
  b.click_id,
  b.click_id_type,                                     -- gclid | gbraid | wbraid
  b.click_host,                                        -- for the ownership check, not for the payload
  date_diff(date(b.attended_at, 'America/New_York'),
           date(b.clicked_at,  'America/New_York'), day) as days_since_click,
  b.conversion_value,
  format_timestamp('%Y-%m-%dT%H:%M:%S%Ez', b.attended_at, 'America/New_York') as conversion_time,
  to_hex(sha256(concat(
    b.booking_id, '|',
    b.click_id, '|',
    format_timestamp('%Y-%m-%dT%H:%M:%S%Ez', b.attended_at, 'America/New_York')
  ))) as dedupe_key
from analytics.v_attended_appointments b
where date(b.attended_at, 'America/New_York')
      between date '2026-03-02' and date '2026-03-08'
  and b.status = 'attended'
order by b.attended_at;

The timestamp is rendered in the account time zone inside the dedupe key rather than beside it. Check C5 below is what that costs when it is not.

A replay fixture is a copy of production appointment data, which makes the rehearsal the quiet way a practice ends up with a second, unmanaged copy of PHI. Three rules keep it HIPAA-conscious. Build it from the same narrow view the production job reads, never a wider one. Keep it inside the system that already holds the data, not on a laptop and not in a shared sheet. Assert the PHI-shaped rule against the outgoing payload rather than its provenance, because provenance checks pass right up until somebody adds a debugging column. Only an anonymous click id, a value and a timestamp leave the practice, the boundary that holds in the offline loop this rehearsal protects.

Then the diff, set arithmetic on dedupe keys, run in both directions:

def diff(replay, uploaded):
    """Compare a rebuilt window against the upload log for those same dates."""
    left = {r["dedupe_key"]: r for r in replay}
    right = {u["dedupe_key"]: u for u in uploaded}
 
    missing = sorted(left.keys() - right.keys())   # built now, never sent then
    extra = sorted(right.keys() - left.keys())     # sent then, not reproducible now
 
    print(f"replay rows: {len(left)}  uploaded rows: {len(right)}")
    print(f"in_replay_not_uploaded: {len(missing)}")
    print(f"in_upload_not_replay:   {len(extra)}")
 
    for key in missing[:20]:
        r = left[key]
        print(f"  MISSING {r['booking_id']} {r['conversion_time']} {r['conversion_value']}")
    for key in extra[:20]:
        u = right[key]
        print(f"  EXTRA   {u['booking_id']} {u['conversion_time']} {u['conversion_value']}")
 
    return missing, extra

Only one direction is scary. Rows in the replay that never went out are a job that skipped, retried and died, or paginated short. Rows in the upload your rebuild cannot reproduce mean your code would write a second conversion for something already live in the account.

Run the pre-flight: 10 checks and what each failure would have cost

Here is the list, run once against that synthetic account: batch replay-2026-03-02-to-03-08, a 7-day window rather than a day, because 3.2 rows a day sits under the 20-row floor above. Illustrative. Synthetic figures, proportions typical.

The arithmetic ties out, and it should tie out on your run: 26 candidates, minus 3 with no click id, minus 1 foreign click id, minus 1 outside the click window, leaves 21. That is the set C5 shifts and the set the diff runs against, and 21 against a weekly average of 22 is the first thing that should look right. After C9 holds the outlier, 20 rows ship.

Three checks passed. Seven did not, on the first run, on code that was already working and had already returned a 200 in staging. That ratio is normal and it is the whole argument for keeping the list.

The ten do not carry the same verdict. Two abort: a PHI-shaped field (C7) and a batch addressed to the wrong conversion action (C8) cannot be undone once the request succeeds. One holds the batch instead of skipping rows, because a UTC exporter (C5) poisons every row and not the 2 that look wrong. One is an investigation (C10). The other six skip a single row with a reason code.

import re
 
ALLOWED_FIELDS = {"click_id", "click_id_type", "conversion_time", "conversion_value", "currency"}
OPAQUE_FIELDS = {"click_id", "conversion_time"}   # identifiers and timestamps: checked by name only
 
PHI_NAME_HINTS = re.compile(
    r"(?i)\b(patient|mrn|chart|record|dob|birth|name|email|phone|address|ssn|insur)\w*"
)
PHI_VALUE_SHAPES = {
    "email": re.compile(r"[^@\s]+@[^@\s]+\.[a-z]{2,}", re.I),
    "phone": re.compile(r"(?<!\d)(\+?1[\s.-]?)?\(?\d{3}\)?[\s.-]?\d{3}[\s.-]?\d{4}(?!\d)"),
    "date_of_birth": re.compile(r"(?<!\d)(19|20)\d{2}[-/](0?[1-9]|1[0-2])[-/](0?[1-9]|[12]\d|3[01])(?!\d)"),
    "person_name": re.compile(r"^[A-Z][a-z]+[ ,-][A-Z][a-z]+$"),
}
 
 
class AbortBatch(Exception):
    """Nothing in this batch ships. Fix the export view, rebuild, run the list again."""
 
 
def assert_payload_shape(rows):
    """C7. Runs against the outgoing payload, never against where it came from."""
    for i, row in enumerate(rows):
        extra = set(row) - ALLOWED_FIELDS
        if extra:
            raise AbortBatch(f"row {i}: field outside the allowlist: {sorted(extra)}")
        for field, value in row.items():
            if PHI_NAME_HINTS.search(field):
                raise AbortBatch(f"row {i}: PHI-shaped field name '{field}'")
            if field in OPAQUE_FIELDS:
                continue
            for label, pattern in PHI_VALUE_SHAPES.items():
                if pattern.search(str(value)):
                    raise AbortBatch(f"row {i}: {label}-shaped value in '{field}'")
    return rows
 
 
def build_batch(rows, production_hosts, click_window_days=90):
    """Every rejection is a skip with a reason code. There is no else branch that fills a blank."""
    batch, skipped = [], []
    for row in rows:
        rid = row["booking_id"]
        if not row.get("click_id"):
            skipped.append((rid, "C2_no_click_id"))
            continue
        if row.get("click_host") not in production_hosts:
            skipped.append((rid, "C3_click_id_not_from_this_account"))
            continue
        if row["days_since_click"] > click_window_days:
            skipped.append((rid, "C4_outside_click_window"))
            continue
        batch.append({
            "click_id": row["click_id"],
            "click_id_type": row["click_id_type"],
            "conversion_time": row["conversion_time"],
            "conversion_value": row["conversion_value"],
            "currency": "USD",
        })
    return assert_payload_shape(batch), skipped

That missing else branch is the point of the function: a job that quietly fills a missing field with a default is indistinguishable from one that worked. C2 and C3 failing is not an upload problem either, it is a signal to go back and capture the click id cleanly at the booking step. C9 redirects the same way, to the decision about what value to send with offline conversions rather than a filter that hides outliers.

Rehearse inside the live account without moving a dollar of budget

The second rehearsal happens in production, which sounds worse than it is. Create a dedicated rehearsal conversion action that is never promoted, point the daily batch at it for the whole observation window, and leave the real action empty until the gate below clears.

One setting decides whether this works. Create the action with Include in Conversions off, then open the settings and confirm it, because an action named "rehearsal" with that toggle at its default is secondary in name and primary in effect. Advice that stops at "start it as Secondary" skips the only step that can fail.

Sometimes there is no second action, because permissions sit with an agency that will not create one. The fallback is smaller than people expect: one row, one booking against one click you can personally identify, into the primary action, with the expected result written down and agreed by whoever owns the budget first. One named row is a test. A thousand rows into a bid-reading action because there was nowhere else to put them is not.

During the window I read three things, and none is the conversion count on its own:

1. The rehearsal action's daily count against the source-of-truth count, day by day rather than as a monthly total.
2. The unmatched share: rows that carried a click id and produced no conversion in that action.
3. The import diagnostics, remembering they are windowed (checked 2026-09) and describe a batch already sent, which is why they cannot rehearse a first upload.

Promotion is one action on a named date: point the job at the real conversion action and let bidding read it. Taking a conversion back afterwards is its own procedure and belongs to the no-show work.

What clears the real conversion action to go primary?

Named gates, published as mine. Google does not set these numbers and neither does the API.

Read the match rate as a diagnosis, not a score. Under 55 percent, stop and fix capture: the clicks were never stored properly and no change to the batch builder moves that number. Between 55 and 70 percent keep uploading while you look, because a stalled rehearsal teaches you nothing and that band is ordinary on accounts mixing gbraid and wbraid traffic. Above 70 percent the gate stops being about level and becomes about stability, which the other three rows measure. Publishing the gates before the run, rather than discovering thresholds after it, is what I mean by an offline conversion tracking setup that is proven before it is trusted.

Once the real action is live the reading changes owner rather than stopping. Daily counts, unmatched share and batch row counts are what the monitoring that watches every scheduled batch after launch is for, and that layer is only worth building because the numbers under it were proven first. Prove the pipeline, then watch it: that sequence separates marketing analytics that ties ad spend to revenue from another dashboard.

When is this whole procedure theater?

For a segment of readers most of this is ceremony. If the account runs manual CPC or maximize clicks, no bidding strategy is reading the conversion action, and the action is already excluded from the Conversions column, then nothing you upload can move a bid. Run three checks and send: row count against the source of truth, timestamps in the account time zone, no PHI-shaped field in the payload. The list earns its keep when a bidding strategy is reading the action, or when the upload runs unattended and nobody is watching the first bad batch go out.

There is a volume floor too. Under about 20 uploaded conversions a month the match-rate gate has no statistical content, and at that volume the gate becomes a plain count check against the source of truth, appointment by appointment.

Before the first live upload on your account, ask whoever runs it for one artifact: the diff between a replayed window and what actually went out over those dates, with the row counts and every unexplained delta named out loud. Not a green Preview screen, not a 200 from the API, not a validate-only run that came back clean. If nobody can produce that diff, the upload is not ready, and that rule applies to me on my own projects.