Data Migration and Cleanup

Your CRM is only as good as the data inside it. Migrating dirty data into a shiny new system just gives you a shiny new system full of garbage — invest in cleanup before migration, not after.

Why This Matters

• 🏢 Owner: Bad data costs businesses an estimated 15-25% of revenue through missed opportunities, duplicated outreach, and flawed forecasting. Data migration is your one chance to start clean.
• 💻 Dev: You will build the migration scripts, field mappings, and validation rules. Understanding the data landscape upfront prevents weeks of rework when production data behaves nothing like test data.
• 📋 PM: Data migration is the most commonly underestimated workstream in CRM projects. If you do not plan for it explicitly, it will blow your timeline.
• 🎨 Designer: The data model determines what you can display and how. Inconsistent or missing data means empty fields, broken layouts, and user frustration on day one.

The Concept (Simple)

Think of data migration like moving to a new house.

You would not pack every item you own — including the broken furniture, expired food, and boxes of junk from the garage — load it all into a truck, and dump it in your new home. You would sort first. Keep what is valuable, toss what is trash, fix what is worth repairing, and organize everything before it goes into the new space.

Data migration works the same way. Before you move a single record to your new CRM, you audit what you have, throw away duplicates and dead records, standardize formats, and map everything to its new home. The move itself is the easy part. The preparation is where the real work happens.

In one sentence: Clean your data before you migrate it, because a new CRM will not fix old data problems — it will amplify them.

How It Works (Detailed)

The Data Migration Lifecycle

Data migration is not a single event — it is a structured process with distinct phases.

┌──────────┐    ┌──────────┐    ┌──────────┐    ┌──────────┐    ┌──────────┐
│  AUDIT   │───▶│  CLEAN   │───▶│   MAP    │───▶│ MIGRATE  │───▶│ VALIDATE │
│          │    │          │    │          │    │          │    │          │
│ Inventory│    │ Dedupe   │    │ Old ──▶  │    │ Extract  │    │ Spot     │
│ all data │    │ Purge    │    │ New      │    │ Transform│    │ check    │
│ sources  │    │ Normalize│    │ fields   │    │ Load     │    │ Reconcile│
│ Assess   │    │ Enrich   │    │ Define   │    │ (ETL)    │    │ User     │
│ quality  │    │          │    │ rules    │    │          │    │ acceptance│
└──────────┘    └──────────┘    └──────────┘    └──────────┘    └──────────┘
     │                                                               │
     └───────────── Iterate until quality gates pass ────────────────┘

Phase 1: Data Audit and Inventory

Before cleaning anything, you need to know what you have. The audit answers four questions:

1. Where does data live? (Spreadsheets, old CRM, email, ERP, sticky notes)
2. How much data is there? (Record counts by type)
3. How good is it? (Completeness, accuracy, freshness)
4. Who owns it? (Which team or person is the source of truth)

┌────────────────────────────────────────────────────────────────┐
│                    DATA INVENTORY MATRIX                        │
├────────────────┬─────────┬────────────┬───────────┬────────────┤
│  DATA SOURCE   │ RECORDS │ COMPLETE % │ FRESHNESS │ OWNER      │
├────────────────┼─────────┼────────────┼───────────┼────────────┤
│ Old CRM        │ 45,000  │ 62%        │ Mixed     │ Sales ops  │
│ Spreadsheets   │ 12,000  │ 35%        │ Stale     │ Various    │
│ Email contacts │ 28,000  │ 20%        │ Recent    │ Individual │
│ ERP system     │ 8,500   │ 85%        │ Current   │ Finance    │
│ Business cards │ 3,000   │ 40%        │ Stale     │ Sales reps │
├────────────────┼─────────┼────────────┼───────────┼────────────┤
│ TOTAL          │ 96,500  │            │           │            │
│ After dedupe   │ ~38,000 │ (estimate) │           │            │
└────────────────┴─────────┴────────────┴───────────┴────────────┘

Data quality scoring: Rate each data source on four dimensions:

Dimension	Definition	Score 1 (Bad)	Score 5 (Good)
Completeness	Are required fields populated?	< 30% of fields filled	> 90% of fields filled
Accuracy	Is the data correct and current?	Many bounced emails	Verified within 6 months
Consistency	Are formats and values standardized?	Free-text everything	Picklists, standard formats
Uniqueness	How many duplicates exist?	> 30% duplicate rate	< 5% duplicate rate

Phase 2: Deduplication Strategies

Duplicates are the most common and most damaging data quality problem in CRM. They cause reps to call the same prospect twice, reports to overcount pipeline, and automation to fire multiple times.

  DEDUPLICATION DECISION TREE
  ============================

  Two records look similar
         │
         ▼
  ┌─────────────────┐     NO     ┌─────────────────┐
  │ Same email OR   │───────────▶│ Same company +   │
  │ same phone?     │            │ same name (fuzzy │
  └────────┬────────┘            │ match > 85%)?    │
           │ YES                 └────────┬─────────┘
           ▼                              │ YES
  ┌─────────────────┐              ┌──────▼──────────┐
  │ DEFINITE DUPE   │              │ PROBABLE DUPE   │
  │ Auto-merge      │              │ Flag for human   │
  │                 │              │ review           │
  └─────────────────┘              └─────────────────┘

Merge rules — which record wins?

Field	Merge Rule
Name	Most complete version
Email	Keep all; mark primary
Phone	Keep all; mark primary
Address	Most recently updated
Company	Most complete; standardize format
Owner	Most recent activity
Activity history	Merge all activities into surviving record
Custom fields	Non-blank value wins; flag conflicts for review

Phase 3: Field Mapping

Field mapping translates the data model of your old system into the data model of your new CRM. This is where subtle mismatches cause the biggest problems.

┌─────────────────────────────────────────────────────────────────┐
│                     FIELD MAPPING TABLE                          │
├────────────────────┬──────────────────┬─────────────────────────┤
│  OLD SYSTEM FIELD  │  NEW CRM FIELD   │  TRANSFORMATION         │
├────────────────────┼──────────────────┼─────────────────────────┤
│ CustomerName       │ Account Name     │ Trim whitespace, title  │
│                    │                  │ case                    │
├────────────────────┼──────────────────┼─────────────────────────┤
│ ContactPerson      │ First Name +     │ Split on space; last    │
│                    │ Last Name        │ word = Last Name        │
├────────────────────┼──────────────────┼─────────────────────────┤
│ Tel                │ Phone            │ Format: +1 (XXX) XXX-  │
│                    │                  │ XXXX                    │
├────────────────────┼──────────────────┼─────────────────────────┤
│ Status (free text) │ Stage (picklist) │ Map to: Prospect, Lead, │
│                    │                  │ Opportunity, Customer   │
├────────────────────┼──────────────────┼─────────────────────────┤
│ Notes              │ Description      │ Truncate to 32,000 char │
├────────────────────┼──────────────────┼─────────────────────────┤
│ SalesRep (name)    │ Owner (user ID)  │ Lookup against user     │
│                    │                  │ table                   │
├────────────────────┼──────────────────┼─────────────────────────┤
│ (no equivalent)    │ Lead Source      │ Default: "Legacy Import"│
├────────────────────┼──────────────────┼─────────────────────────┤
│ Region code        │ (no equivalent)  │ Archive to custom field │
│                    │                  │ or discard              │
└────────────────────┴──────────────────┴─────────────────────────┘

Common mapping pitfalls:

• One field in the old system maps to multiple fields in the new (e.g., full name to first + last)
• Picklist values do not match (e.g., "Hot Lead" vs "Qualified")
• Data types change (e.g., free text to structured picklist)
• Required fields in the new system have no source data

Phase 4: Normalization and Standardization

Before loading data, apply consistent formatting rules.

Data Type	Standardization Rule
Phone numbers	E.164 format: +1234567890
Email addresses	Lowercase, trim whitespace
Company names	Remove Inc/LLC/Ltd variations; standardize casing
Addresses	USPS or postal service standard format
Dates	ISO 8601: YYYY-MM-DD
Currency	Store as cents/integer; separate currency code
Names	Title case; trim whitespace; remove honorifics
State/Province	Two-letter abbreviation codes

Phase 5: Cutover Planning and Rollback

The actual migration event — the cutover — needs to be planned like a surgical operation.

┌─────────────────────────────────────────────────────────────────┐
│                    CUTOVER TIMELINE                              │
├───────────────────┬─────────────────────────────────────────────┤
│  T-14 days        │ Final test migration on staging             │
│                   │ Validate record counts and field accuracy   │
├───────────────────┼─────────────────────────────────────────────┤
│  T-7 days         │ Freeze changes in old system (or capture    │
│                   │ delta records)                              │
├───────────────────┼─────────────────────────────────────────────┤
│  T-1 day          │ Full backup of old system                   │
│                   │ Full backup of new system (pre-migration)   │
├───────────────────┼─────────────────────────────────────────────┤
│  T-0 (cutover)    │ Run production migration scripts            │
│                   │ Validate record counts match                │
│                   │ Spot-check 50 records across all types      │
├───────────────────┼─────────────────────────────────────────────┤
│  T+1 day          │ Key users validate their data               │
│                   │ Fix critical issues                         │
├───────────────────┼─────────────────────────────────────────────┤
│  T+3 days         │ Go/no-go decision on full rollout           │
│                   │ If no-go: execute rollback plan             │
├───────────────────┼─────────────────────────────────────────────┤
│  T+7 days         │ Decommission old system access (read-only)  │
└───────────────────┴─────────────────────────────────────────────┘

Rollback plan essentials:

• Full backup of the new CRM before migration starts
• Documented steps to restore the pre-migration state
• Clear criteria for when to invoke rollback (e.g., > 5% data loss, critical fields missing)
• The old system remains accessible in read-only mode for at least 30 days

In Practice

A Real-World Data Migration: 96,000 Records to Clean CRM

A B2B services company had 96,000 contact records spread across an old CRM, spreadsheets, and email exports. Here is what they found during audit:

• 40% duplicates — the same contacts appeared in multiple sources with different spellings
• 60% of phone numbers were in non-standard formats or disconnected
• 25% of email addresses bounced when validated
• "Status" field had 47 unique free-text values that needed to map to 6 picklist options

After a three-week cleanup effort, they migrated 38,000 clean, deduplicated records. Sales reps reported that the new CRM felt "like it actually knows our customers" because every record had complete, accurate, standardized data.

Anti-Patterns to Avoid

1. The "Migrate Everything" Approach — Importing all historical records regardless of quality or relevance. If a contact has not been touched in three years and has no email address, it does not belong in your new CRM.

2. Cleaning Data After Migration — This always takes three times longer and means users start with a bad experience. Clean first, migrate second.

3. Skipping Test Migrations — Running the migration script once in production and hoping for the best. Always run at least two full test migrations against realistic data volumes.

4. One-Person Migration — Assigning data cleanup to one analyst working alone. Business users must validate that their data looks correct — the analyst cannot know that "Acme Corp" and "ACME Corporation" are the same company without domain knowledge.

5. Ignoring Delta Records — Records created or modified between the test migration and production cutover get lost. Plan for a delta migration or enforce a data freeze window.

Key Takeaways

• Audit all data sources before cleaning — you cannot fix what you have not measured.
• Deduplication typically reduces record counts by 30-50%. This is a feature, not a bug.
• Field mapping is where most migration bugs originate. Document every transformation rule explicitly.
• Normalize data formats (phone, email, dates, addresses) before migration, not after.
• Run at least two test migrations with production-scale data before the real cutover.
• Always maintain a rollback plan and keep the old system accessible in read-only mode.
• Budget 2-4 weeks for data cleanup — it is the most underestimated phase in every CRM project.
• Business users must validate migrated data. Technical teams alone cannot verify accuracy.

Action Items

┌─────────────────────────────────────────────────────────────────┐
│  ROLE-BASED ACTION ITEMS                                        │
├──────────┬──────────────────────────────────────────────────────┤
│ 🏢 Owner │ ☐ Mandate a data quality audit before any migration  │
│          │   work begins                                        │
│          │ ☐ Approve the data retention policy — what migrates  │
│          │   and what gets archived                             │
│          │ ☐ Allocate budget for data enrichment tools if       │
│          │   needed (email validation, firmographic data)       │
│          │ ☐ Set quality targets: < 5% duplicates, > 85%       │
│          │   field completeness                                 │
├──────────┼──────────────────────────────────────────────────────┤
│ 💻 Dev   │ ☐ Build the field mapping document with              │
│          │   transformation rules for every field               │
│          │ ☐ Write migration scripts with validation checks     │
│          │   and error logging                                  │
│          │ ☐ Run two full test migrations before production     │
│          │ ☐ Create rollback scripts and test them              │
├──────────┼──────────────────────────────────────────────────────┤
│ 📋 PM    │ ☐ Build the data inventory matrix with record        │
│          │   counts and quality scores                          │
│          │ ☐ Schedule the cutover window and communicate it     │
│          │   to all stakeholders                                │
│          │ ☐ Define the go/no-go criteria for post-migration    │
│          │ ☐ Plan the delta migration strategy for late changes │
├──────────┼──────────────────────────────────────────────────────┤
│ 🎨 Design│ ☐ Review migrated data in the new CRM layouts to    │
│          │   verify fields render correctly                     │
│          │ ☐ Design empty-state experiences for records with    │
│          │   incomplete data                                    │
│          │ ☐ Plan how "Legacy Import" records will display      │
│          │   differently from newly created records             │
│          │ ☐ Test dashboards with migrated data volumes         │
└──────────┴──────────────────────────────────────────────────────┘

---

Next: Customization vs Configuration