Google Ads Offline Conversion: Setup State & Resume Sheet

2026-06-09 · Ben · handoff

Google Ads Offline Conversion: Setup State & Resume Sheet

The operator's resume sheet for wiring the broker funnel's paid leads into Google Ads server-side, ad-block-proof, from D1. This is the implementation half of Pick #1 (conversion-tracking-architecture.md). If you are picking this up cold, this file is everything you need.

Where it stands (updated Jun 19, 2026)

LIVE. Google-side setup cleared (SA approved 06-14, Data Manager API enabled 06-15), the drain worker is built + deployed (vpn-forms 7fa3e9a), the ads_status migration is applied, and GOOGLE_SA_KEY is set — the hourly scheduled() drain uploads stored gclid leads to the Data Manager API, ad-block-proof. As of 2026-06-18, go.vpn.com paid leads also feed this pipeline (they used to POST straight to GHL, bypassing D1 → their gclids never reached Ads; now they route through /api/domain-intake like www, so paid go.vpn.com conversions upload too). One leg still under WATCH: the GHL qualified→5%-of-budget restatement has not been validated against a real qualified lead end-to-end (see memory project_ads_offline_ghl_watch). Original blocker history below.

Was (Jun 14): SA access approval CLEARED 2026-06-14 (the blocker that had this parked — SA ads-conversions@model-factor-487819-h8.iam.gserviceaccount.com). One Google item left to confirm (Data Manager API enabled), then build unblocked.

Done:

Pending (blocks live upload):

⚠️ METHOD CORRECTION (2026-06-15) — upgrade GCLID-only → Enhanced Conversions for Leads

Verified against Google docs on the deprecation date. The transport (Data Manager API) is correct, but the method as first built — GCLID-only (UPLOAD_CLICKS + adIdentifiers.gclid) — is now legacy. Recommended = Enhanced Conversions for Leads (ECL): send hashed first-party data (SHA-256/HEX of normalized email + E.164 phone) in userData.userIdentifiers, alongside the gclid, with top-level encoding: "HEX". Median +10% conversions; matches on email so it captures leads with no gclid too (~117 vs 25).

To do before go-live: rewrite ads-conversions.js to add userData (hashed email + phone) + encoding + a consent block; upload all leads with an email (within 90d), not just gclid rows. The ADS_DRY_RUN=1 validateOnly pass verifies the new payload shape against the live API first.

OPEN DECISION (Ben): consent/compliance — ECL sends hashed customer email/phone to Google. Needs (a) Google customer-data-processing terms accepted in the Ads account, and (b) a consent basis (form terms + privacy policy). Do NOT hardcode consent: granted without this — wire it from a real basis or set conservatively.

Config (non-secret)

The private key is the ONLY secret. It never goes in git or any doc; it goes into a Cloudflare Worker secret (GOOGLE_SA_KEY) at build time.

The build (BUILT 2026-06-14 — not yet committed/deployed)

Lives in the forms worker (vpn-workers/forms), which already owns domain_intake. Status: code written + bundles clean (wrangler deploy --dry-run OK). Files: src/google-auth.js (SA JWT RS256 → token via Web Crypto), src/ads-conversions.js (the drain — no-ops until GOOGLE_SA_KEY is set; ADS_DRY_RUN=1 → validateOnly; expires clicks >90d; status-gated; per-row idempotent transactionId = vpn-broker-{id}), migrations/0002_ads_status.sql, wired into scheduled() in src/index.js, and ads_status exposed in the admin leads query (src/admin.js). Self-alerting via src/askbowtie-alert.js: the drain POSTs a deduped send_alert (key drain_ads_conversions, transition-gated in POPUP_KV) when a run fails (auth down, or every upload in a run fails) and resolve:true on recovery → the domain's email/Slack. Needs secrets ASKBOWTIE_ALERT_URL + ASKBOWTIE_TOKEN (confirm endpoint URL + Bearer auth with bowtie). The cf-admin UI column + the live GOOGLE_SA_KEY secret remain.

  1. D1 migration: add ads_status to domain_intake (default pending). Drain marks each row sent / failed / no_gclid.
  2. scheduled() cron drain: select domain_intake WHERE ads_status = 'pending'; for rows with a gclid, upload to the Data Manager API; mark sent on success, failed on error (retried next run), no_gclid for rows without a click id (terminal). Status-gated so re-runs never double-send. Use the D1 lead UUID as the upload order_id/idempotency key.
  3. SA auth in-worker: build the JWT and token exchange via Web Crypto. Read the key from env.GOOGLE_SA_KEY.
  4. wrangler.toml: add [triggers] crons (hourly is fine at ~9 leads/day) and reference the secret.
  5. Secret: npx wrangler secret put GOOGLE_SA_KEY (paste the JSON). Then delete the local key file.
  6. cf-admin Leads panel: add an ads_status column/tile next to the GHL status, so upload state is visible alongside delivery state.

Resume instructions (cold start)

  1. Confirm the two pending Google items cleared (SA access approved + Data Manager API enabled).
  2. Verify the SA can authenticate: locally, gcloud auth print-access-token style check or a token-exchange test from the worker.
  3. Build steps 1 to 6 above. Verify the request shape against the current Data Manager API docs (the API is new as of 2026; confirm endpoint + payload for an offline conversion event keyed on gclid + conversion action).
  4. Backfill: existing domain_intake rows default to ads_status = 'pending', so the first drain run uploads historical gclid leads (all within the 90-day window). Confirm that is desired before the first run, or pre-mark old rows.
  5. Watch for an MCC: the conversion-action URL showed an ocid differing from the customer ID. If the account sits under a manager account, the API call needs a login-customer-id in addition to the operating customer ID.

Staged values (later)

The conversion value is a flat placeholder today ($25 to $500). Real value comes via staged uploads (expected at lead, probability-weighted at qualified, actual commission at close for in-window deals), because Google caps adjustments at ~55 days and new conversions at ~90 days from click, which a brokerage cycle routinely exceeds. See conversion-tracking-architecture.md.