1. Capture failures into a quarantine sheet and add provenance
When an import fails or partially completes, export the CRM's failure CSV or download the import job report. Put every failed row into a single Google Sheet tab called `quarantine` and keep the original CSV as a read‑only attachment (or a second tab) so you never lose the raw input.
Add a few provenance columns you can create in an afternoon: `import_job_id`, `source_filename`, `original_row_index`, `attempted_timestamp`, `raw_error`. These let you trace a cleaned row back to the exact import attempt — HubSpot and Salesforce both surface import job IDs you can paste into the sheet.
If you use HubSpot, Salesforce, Marketo or Pardot, capture the platform's import/external ID in the sheet. For HubSpot specifics on cleaning contact imports, see hubspot CRM data cleanup.
2. Clean, validate and run no‑op previews
Work in isolated columns. Add validation columns (yes/no) for key fields: email format, required lookup (company ID), UK postcode sanity. Add a `fix_reason` and `fixed_by` column so every edit has a short note and owner.
Detect duplicates before reimporting: create formula checks using email + external ID + company name and a COUNTIFS column to flag duplicates within the quarantine and against a recent export of live CRM IDs. For Salesforce use External ID upserts; for HubSpot use email+ID matches. Marketo/Pardot often need suppression flags — add a `suppress_from_marketing` column you can map on import.
Preview changes with a no‑op routine: import a tiny sample (10–20 rows) into a sandbox or run an update-only import with a test flag field (e.g. `do_not_automate = true`) so records update but automations ignore them. Many CRMs support update-only imports or an ‘upsert’ action — that’s your no‑op preview to confirm mapping, field formats and whether the import will create unexpected duplicates.
3. Replay safely in gated batches, audit and rollback
Replay in small batches (start 50, then 200). For each batch, set or map a suppression field (`do_not_automate`, `suppress_emails`, or a temporary tag) so workflows and email sends ignore imported records for 24–48 hours. Only clear that flag after manual spot checks and successful sync counts.
Keep an import audit tab: one row per batch with `import_job_id`, `rows_sent`, `rows_failed`, `sample_success_ids` and a link to the pre‑import export you took. If something goes wrong, you can use those pre‑import exports as a rollback dataset (importing the previous values to restore fields) or to run a targeted undo via the CRM’s API/import tools.
Simple checks to avoid retriggers and duplicates: always upsert on a canonical key (email or external ID), use the CRM’s update‑only option where available, and never remove `do_not_automate` until you've validated. Keep one column in your sheet called `replayed` and mark rows with `yes` when the batch finishes — this prevents accidental double‑replays.
If you need a hand, Optira can help with practical CRM data cleanup and workflow automation support for small teams in Fareham, Hampshire and across the South Coast.