1. Map where consent lives and choose one canonical field
Spend 60–120 minutes listing every place a contact’s marketing preference or consent could exist: CRM contact properties, email platform lists/subscriptions, form logs, ad audiences and the spreadsheets teams still keep. Note the owner for each source (marketing, sales, finance) and the most recent timestamp you can find.
Decide one canonical consent field for your operational stack (examples: Explicit Opt‑in, Soft Opt‑in, No Consent, Unknown). Make the values simple, platform‑neutral and human‑readable so HubSpot, Salesforce, Marketo or Pardot can all map to them without ambiguity.
Quick PECR/GDPR check: if an address has no clear, documented marketing consent, treat it as no consent for marketing emails. When in doubt, default to unsubscribed and log the reason; get legal advice for borderline cases.
2. Sample and audit differences, then pick a safe backfill method
Take a stratified sample (e.g. 200–500 contacts covering recent form leads, old customers, and spreadsheet-only rows). Compare the canonical consent value against each system and note disagreements and common failure modes (stale CSVs, missing timestamps, list-only unsubscribes).
Choose one backfill method based on risk and scale:
- Manual CSV import (low‑tech): export from canonical source, normalise in a sheet, import with consent value and provenance (good for <5k contacts).
- Tags/segments in email platform (quick guard): add a readonly segment/tag for ‘consent_source=canonical’ if you can’t write fields everywhere.
- Zapier/Make or native sync (low‑code): use flows to set API flags in CRM/email platform for ongoing fixes (suitable for continuous small updates).
- API backfill (engineering): bulk API calls to set consent_date/source where you can log and retry.
Estimate: sampling + audit 2–4 hours; small backfills 1–3 hours; larger API jobs might take a day including tests. Always keep a rollback snapshot of the fields you overwrite.
3. Add provenance, gate automations and run the backfill
Before you run any bulk writes add three lightweight provenance fields where possible: consent_source (which system decided the value), consent_date (when it was set) and consent_method (import, form, manual, sync). If a platform won’t accept new fields, keep the same columns in your master spreadsheet.
Create gating rules so automations only email when canonical_consent == "Explicit Opt‑in" and consent_date is within your retention policy. Use safety steps: run the backfill in ‘dry run’ to a test tag or a canary list (10–50 records), check logs, then run a staged roll‑out (oldest groups first). Add a one‑hour monitoring window after each stage to catch misfires.
If you’d like a hand testing or implementing these steps in Fareham, across Hampshire or along the South Coast, see our marketing automation support in Hampshire — Optira can help with the practical runbook and low‑code options like Zapier/Make, CSV imports or native platform exports.