CRM database cleansePre-migration CRM database cleanse, formatting and validation
The client wanted to migrate contact records between two CRM systems that required different data formats.
Before completing the data upload into the new system they first needed help to clean, verify and reformat the entries however with around 80,000 inconsistent contact records, it was too large a task to complete manually.
After discussion by telephone we agreed a clear set of rules, logic and principles to apply to cleaning up the data including:
- Removing unwanted characters and symbols from phone numbers;
- Replacing +44 UK international dialling codes and preserve leading 0’s;
- Removing non-numeric entries;
- Removing duplicate numbers within each records;
- Verify phone numbers against UK geographic, non-geographic, mobile and premium dialling codes;
- Match clients by name and copy HQ phone numbers across where departmental entries were missing.
Our VBA developers used a mixture of our existing modules in our code library and custom code to complete the operation. By automating data cleaning processes and UK phone number verification in Excel (using VBA) we were able to provide a clean set of data to the client in a short turnaround of only two days allowing them to quickly migrate the system.