fb-conversions-api-bq

Server-side Facebook Conversions API events sourced from a BigQuery contacts / purchases table — multi-region, multi-pixel, with a small token-refresh helper.

Problem

The browser-side Pixel is increasingly unreliable: iOS 14+ App Tracking Transparency, GDPR / ePrivacy consent banners, and ad-blockers all eat into the events Facebook actually receives. Without server-side events, Meta Ads optimization runs on incomplete data, CPA bids drift, and lookalike-audiences degrade.

The fix is well-known — Conversions API (CAPI) — but in a multi-region setup it requires more than the official tutorial: per-region pixel mapping, token rotation, and feeding the right events from the right CRM date window.

Solution

Two small Python entry points:

• send_lead_events.py — for each region, reads recent contacts (or purchases) from BigQuery, builds one CAPI Event per row (Lead or Purchase), and POSTs them through the official facebook_business SDK. The same script handles every region; the per-region pixel + token + currency map lives in sites.json.
• tokens.py — exchanges short-lived page access tokens for long-lived (60-day) ones via Graph API's fb_exchange_token flow. Run as a monthly maintenance task; updates sites.json in place.

Hashing of user identifiers (email, phone) is delegated to the SDK so the warehouse never has to worry about leaking PII over the wire.

Stack

• Python 3.10+
• BigQuery — pandas-gbq, google-auth
• Facebook Conversions API — official facebook_business SDK
• Graph API for token refresh — requests

How it works

                    ┌──────────────────────────────────────────┐
                    │ BigQuery: crm.contacts_all (all sites)   │
                    └────────────┬─────────────────────────────┘
                                 │ per-site SELECT (date window)
                                 ▼
                  ┌───────────────────────────────────┐
                  │      send_lead_events.py          │
                  │  for site in sites.json:          │
                  │     read contacts                 │
                  │     build Event(s) (Lead/Purchase)│
                  │     EventRequest.execute()        │
                  └────────────┬──────────────────────┘
                               │
                               ▼
                ┌────────────────────────────────────┐
                │  Facebook Conversions API          │
                │  (per-pixel; PII hashed by SDK)    │
                └────────────────────────────────────┘

                ┌──────────────────────────────────────┐
                │      tokens.py (monthly)             │
                │  for each site: short -> long-lived  │
                │  rewrite sites.json in place         │
                └──────────────────────────────────────┘

• Same script handles many regions in one run; if a site has no pixel_id or page_access_token, it's skipped with a warning rather than failing.
• EVENT_NAME=Lead or Purchase switches the script's behavior (with Purchase an extra contact_type LIKE '%orderFull%' filter is applied to the SQL).
• Token expiry: long-lived page tokens last ~60 days. tokens.py renews them in place. Add a monthly Cloud Scheduler trigger.

How to run

1. Create a Facebook App in Meta for Developers, add the Marketing API product, and grab the App ID + App Secret.
2. For each Page / Pixel pair:
   • Get a short-lived page access token from Graph API Explorer (or your in-house OAuth flow).
   • Get the Pixel ID from Meta Events Manager.
3. Create a GCP service-account with BigQuery read access; download the JSON key.
4. Copy .env.example to .env, fill in GCP_PROJECT_ID, FB_APP_ID, FB_APP_SECRET.
5. Copy sites.example.json to sites.json and replace each placeholder with real per-region values.
6. Install and run:

pip install -r requirements.txt

# Daily: send events
python send_lead_events.py
EVENT_NAME=Purchase python send_lead_events.py

# Monthly: refresh tokens
python tokens.py

In production: schedule send_lead_events.py daily (or hourly), tokens.py monthly, and pipe both into Cloud Functions / Cloud Run via Cloud Scheduler.

Structure

• send_lead_events.py — main: BQ contacts → CAPI events
• tokens.py — short-lived → long-lived page-token exchange (rewrites sites.json)
• sites.example.json — per-site pixel / token / currency mapping template
• .env.example — environment template
• requirements.txt — pinned dependencies

Notes

• PII handling. Don't put raw emails / phones in the event body — let UserData hash them. The SDK does this automatically.
• Event deduplication with the browser Pixel. When both browser and server fire for the same purchase, Meta dedupes by event_id. This pipeline doesn't emit event_id, so it assumes the browser Pixel either doesn't fire or has been disabled. If both fire, add an event_id matching the browser side (e.g., the order number) to avoid double-counting.
• No retry / dead-letter queue. A 5xx from the API just logs. For tighter delivery, wrap EventRequest.execute() in a retry-with-backoff and route persistent failures to a capi_dead_letter BQ table.
• Test events. During development, attach a Test Event Code from Events Manager to the EventRequest so events flow into the test view rather than the real Pixel.
• Page rating monitoring (a separate module from the original codebase, Telegram-alerting page review changes) is not included here — it's a different topic from CAPI and belongs in its own repo.