Clean Your Data Before You Buy a CRM or ERP

Take stock of where your data lives

List every place that holds customer, supplier, product and price information: accounting files, sales spreadsheets, email contacts, phone books, order forms, a storekeeper's register. Note who maintains each one and how often it is updated.

You will probably find the same customer in several places with different details. Decide which source is the most reliable for each kind of record. That source becomes the starting point for the clean list.

Decide what a good record looks like

Write the minimum fields each record needs. For a customer this might be legal name, display name, address, contact person, phone, email, and the tax registration number if relevant. For an item: code, description, unit, category and cost. Mark which fields are mandatory.

Set naming rules too. For example, how do you write company suffixes, abbreviations and city names? A one-page standard prevents the mess from returning after the migration.

• Mandatory fields for each record type
• One format for names, addresses, phone numbers and dates
• A unique code for each customer, vendor and item
• Allowed values for categories and statuses

Remove duplicates and fill gaps

Sort each list by name, phone number or email to bring probable duplicates together. Review them by hand before merging, since two similar names may be different customers. Keep the most complete and recent details and note which old codes map to the new one.

Then go through gaps. Records with no contact or no category are not necessarily worthless, but they need attention. Ask the salesperson, the storekeeper or the accounts team to fill the gaps, and archive records that have been inactive for a long time and have no reason to stay.

Check the history that you will carry across

Decide how much history to migrate. Opening balances, open orders and current prices are usually essential. Years of old transactions may be better kept in an archive, accessible but not loaded into the new system.

Reconcile what you do bring: do the balances in the clean file match your books? Resolving differences before migration is far easier than after. For financial records, involve your accountant.

Run a test load and keep it clean

Ask the implementation team to load a sample into a test environment, and have users check it against reality. Errors found here are cheap to fix. Repeat until the sample is right, then do the full load.

After go-live, assign a data owner and give users simple rules: search before creating a new record, fill the mandatory fields, never use free-text where a list exists. A short monthly review of duplicates and blanks prevents decay.