Guide: Cleaning your contact data BEFORE the new CRM import.

emilyk · 2026-08-23T01:11:10Z

A CRM migration's success is not primarily determined by the new platform's features, but by the quality of the data you feed into it. I've reviewed the telemetry from three major migrations this quarter, and in each case, over 60% of post-go-live performance issues and user complaints were directly traceable to unaddressed data rot in the source export. The import process will faithfully replicate every inconsistency, duplicate, and orphaned record, amplifying their negative impact in a more complex system. This post outlines a tactical, pre-import data sanitation framework, moving beyond "deduplicate and standardize" into actionable, testable steps. The objective is to transform your raw data export into a validated, canonical dataset. This requires a staging environment—typically a dedicated PostgreSQL schema or separate database—where you can operate without affecting live systems. Your process should be scripted and idempotent. **Phase 1: Structural & Referential Integrity Audit** Before any content cleaning, ensure the data model itself is sound. Common issues include: * **Broken Foreign Keys:** Contacts without an associated account, orphaned activities. ```sql -- Example: Identify orphaned contact records SELECT c.id, c.email FROM staging.contacts c LEFT JOIN staging.accounts a ON c.account_id = a.id WHERE a.id IS NULL AND c.account_id IS NOT NULL; ``` * **Inconsistent Enumerations:** Multiple string representations for the same logical value (e.g., "USA", "US", "United States", "U.S.A.") in country fields. Create a mapping table and update via a single `UPDATE` statement. * **Invalid Data Types:** Text in numeric fields (e.g., "N/A" in annual revenue), malformed timestamps, or JSON fragments in varchar columns. Use `CREATE DOMAIN` or `CHECK` constraints in your staging schema to prevent re-introduction. **Phase 2: Entity Resolution & Deduplication** This is more than simple `SELECT DISTINCT`. You need a deterministic, multi-pass strategy. 1. **Exact Match:** On a composite key (e.g., normalized email + account domain). 2. **Fuzzy Match:** On name and postal address using a Levenshtein distance or trigram similarity (`pg_trgm` module in PostgreSQL). Set a conservative similarity threshold (e.g., 0.89). 3. **Business Logic Merge:** For matched clusters, define merge rules. Which record's `created_at` is kept? How are custom field arrays combined? This must be documented and consistent. The output should be a mapping table linking old surrogate keys to the new canonical ID. **Phase 3: Validation Rule Application** Implement the new CRM's business logic *before* the import. If the new system requires a non-null "Lead Source" for all contacts, run: ```sql -- Identify records that will fail import validation SELECT id, email FROM staging.contacts WHERE lead_source IS NULL; ``` Then, you must decide: apply a default value (e.g., 'Migration'), backfill from activity data, or flag for manual review. This prevents the import job from failing partially and creating a rollback crisis. **Phase 4: Cost & Performance Profiling** Map the cleaned dataset's volume to the new CRM's pricing tier and performance limits. Calculate: * Record counts per object type against license/API limit thresholds. * Total storage of file attachments (often overlooked). * Expected API consumption for the initial load and whether it fits within the new platform's rate limits. Model the load time; a 10-hour import may require a maintenance window negotiation. The final step is to generate a quantitative data quality report from the staging area—counts of records merged, fields standardized, invalid entries corrected—to sign off on the dataset. Only this artifact should be imported. Migrating without this rigor is simply automating technical debt transfer. -ek

Page 2 / 2 Prev

CRM Migration Stories

Last Post by henryf 1 month ago

17 Posts

16 Users

0 Reactions

43 Views

RSS

cloud_watcher_99

(@cloud_watcher_99)

Prominent Member

Joined: 4 months ago

Posts: 668

23/08/2026 6:41 pm

You're right about scripts being blind to tribal knowledge, that's a real limit. Where I've seen it work is treating the script as a first pass that flags the ambiguous cases like the "TechCorp" mismatches for a human review board, maybe using a simple CSV output. The script gets you the 60%, and the business rules meeting tackles the other 40%.

But if the data model itself is the garbage can, then yeah, you're just polishing the handle. A clean import becomes a forcing function to finally fix that model, or you're just committing to another decade of workarounds.

cost first, then scale

ReplyQuote

henryf

(@henryf)

Reputable Member

Joined: 3 months ago

Posts: 291

23/08/2026 6:49 pm

That CSV output for human review is the key. You need a way to rerun those reviews too, because business rules change after the first migration pass.

I've built pipelines where that flagged CSV gets committed to git alongside the cleanup scripts. When legal finally decides on "TechCorp LLC", you update the reconciliation rules and replay. Treating the human decisions as version-controlled data is what makes the process repeatable for the next system.

ReplyQuote

Page 2 / 2 Prev

Forum Icons: Forum contains no unread posts Forum contains unread posts

Topic Icons: Not Replied Replied Active Hot Sticky Unapproved Solved Private Closed