The goal
Ad campaigns deliver leads via Gmail and get qualified in Worldfilia CRM. Without automation, invalid-lead rates per UTM were invisible, duplicates were processed multiple times, and ad spend decisions were guesswork. The goal was a scheduled job that merges Gmail leads with CRM state and publishes a clean per-campaign scorecard.
The solution
A Python CLI with 5 operating modes (INIT bootstrap, date-range, --watch N continuous, --dry-run, --from-cache). Each run fetches new lead emails via Gmail API, pulls matching Worldfilia transactions, runs a multi-tier matcher (exact/phone/email/fuzzy), aggregates per utm_campaign, writes summary + raw leads to Google Sheets, and marks emails as read only after a successful Sheets write so failures never lose leads.
Highlights
- Mark-as-read only after a successful Sheets write — pipeline is fully re-runnable
- 4-tier matcher with visible match_tier lets clients audit confidence
- 5 operating modes (INIT, date-range, watch, dry-run, from-cache) from a single CLI
The challenge
The same phone number appeared in multiple UTM campaigns on the same day, inflating counts and hiding real invalid-rate signal.
Tiered matcher (exact, phone, email, fuzzy) with a visible match_tier tag on every row, so the client can audit confidence; unmatched leads are surfaced separately rather than silently dropped.
Worldfilia session tokens expire unpredictably and the scheduled job would fail silently.
A typed WorldfiliaAuthExpiredError with an operator-actionable message ('re-export WorldFilia_credentials.json or use --from-cache'), and non-zero exit so schedulers flag the failure.
A naive mark-read-then-write order can lose leads if the Sheets write fails afterward.
Strict commit ordering — fetch, match, aggregate, write Sheets, then mark as read. Combined with per-range local JSON cache, the pipeline is fully re-runnable with no data loss.
Bootstrapping at go-live meant thousands of unread historical emails — had to process once without double-processing future ones.
--init mode widens the window to ~2 years, processes everything once, marks as read, and prints a transition message pointing at --unread-only / --watch N for subsequent runs.
What was delivered
- main.py CLI with 5 operating modes
- gmail_client.py, worldfilia_client.py, sheets_writer.py, auth_google.py, lead_matcher.py
- WorldfiliaAuthExpiredError with operator-actionable messaging
- Per-date-range JSON cache in cache/data_YYYYMMDD_YYYYMMDD.json
- Persistent Google OAuth token flow
- requirements.txt pinned dependencies
Results
Gmail + Worldfilia
Data sources
4
Match tiers
5
Operating modes
Zero
Data loss on failure
What it taught me
- Commit ordering (write-then-mark) turns a fragile sync into a fully re-runnable one
- Tiered matching with a visible match_tier earns client trust — auditability beats black-boxing
- Typed errors with operator-actionable messages save support time when token APIs expire