CRM Rebuild: Making Legacy Data Migration Reliable

I rebuilt an agency's CRM three times, working as an independent freelancer hired by the agency. The bigger problem was moving contacts and deals out of Microsoft Access while keeping each deal attached to the right contact.

This is a retrospective account of real work, written from experience. The agency is not named, and its data is not published. It describes the approach and the decisions behind it. It does not include the technology stack, database design, data volumes, test coverage or measured results, and I have not made any of those up.

The agency's contacts and deals were stored in Microsoft Access and needed to move into the rebuilt CRM. Losing the connection between a deal and its contact, or attaching a deal to the wrong contact, would defeat the purpose of moving the data.

The people using the CRM were not technical, so it needed to be usable without explanation.

My first design was shaped by a developer's assumptions rather than by how the people using the CRM behaved. Form validation was insufficient, and some interactions confused non-technical users.

The bigger problem was the migration. Importing contacts and deals from Microsoft Access without a reliable mapping between legacy records and their newly generated IDs could not preserve the relationships between them, and importing records independently was not enough.

• Record identityThe legacy IDs belonged to Access. The new system generated its own, so the old and new identities of each record had to be connected explicitly.
• Relationship mappingRelationships between contacts and deals had to survive the move, which meant expressing them in terms of the new IDs.
• Ambiguous matchesThe data had duplicate names, missing emails and phone numbers written in inconsistent ways, so no single field reliably identified a person.
• ValidationForm validation in the first version was insufficient, which let through input that should have been caught.
• Inconsistent dates and other legacy dataThe legacy data contained inconsistent dates and other messy values that needed cleaning.

Legacy IDs were not copied into the new system. The critical requirement was to map each source record to the new ID it received, so the relationships between contacts and deals stayed intact. These are the elements of the workflow; I am not describing the exact sequence or implementation:

• Source-to-destination ID mappingEach legacy record is linked to the new ID it received, so contacts and deals stay connected.
• Candidate matchingName plus email, or name plus phone when there is no email. These rules produce candidates, not confirmed matches.
• Review before confirmingThe affected records are shown to the responsible person to inspect before the import is confirmed.
• Cleaning legacy dataInconsistent dates and other messy values are cleaned.

Name plus email and name plus phone are not guaranteed to be unique across a dataset, so a match is a candidate rather than proof of identity. Where identity remained ambiguous, the affected records were shown to the person responsible for the data to inspect before the import was confirmed.

The review step is a deliberate point where the rules stop and a person decides.

• Candidates instead of automatic mergesMatching rules suggest links and a person confirms the ambiguous ones. This costs review effort, but it reduces the risk of linking a deal to the wrong contact.
• A mapping instead of copied IDsIt adds bookkeeping, but it preserves the relationships between contacts and deals even though the new system generates its own IDs.
• Review before importing ambiguous recordsIt slows the migration and depends on someone's time, in exchange for a person checking the cases the rules cannot settle.
• Starting from the problemAcross the three builds, my thinking moved from implementation-first to problem-first.

Decide how record identity will work before writing an importer. Look at the real data early. Design around how the people using the system actually behave. Keep a person in the loop for matches the rules cannot settle.

I do not claim the original implementation was perfect, or that every possible data-quality problem was eliminated. This case study records the approach and the reasoning, not a measured outcome.