Logistics and distribution
Cleaning customer and item masters before an ERP migration
By SourceX Editorial · Updated
Short answer
Data cleansing before an ERP migration works best in a fixed order: gate new master records, merge duplicate customers and items, standardize units and attributes, then convert with a permanent old-to-new ID map. The mapping matters most, because without it years of order, invoice and support history detach from the cleaned records and lose their meaning.
Key takeaways
- Clean customer and item masters before conversion, not after go-live, when every fix touches live transactions.
- The keep-the-mapping rule: every merged, renumbered or retired ID gets a permanent row linking old to new.
- Inactive customers and items are archived with their history, not deleted.
- Export legacy history in full, with its keys, before the old license or hosting ends.
- A retired system's linked history can be a business asset, not only an archiving cost.
What needs cleaning before an ERP migration?#
Customer and item masters need the most cleaning before an ERP migration, together with the cross-references that connect them, such as customer part numbers and contract pricing. These records are copied into the new system and become the keys every future transaction uses, so their errors outlive the project.
Vendor masters, chart of accounts and pricing tables matter too, but customer and item problems do the most damage to sales history. The table lists the usual problems by master record.
| Master record | Typical problems | Cleanup action |
|---|---|---|
| Customer bill-to | Duplicates from branches and acquisitions, outdated legal names | Merge to one surviving account and log the mapping |
| Ship-to addresses | Same site entered several ways, closed sites still active | Normalize addresses and inactivate closed sites |
| Contacts | Departed buyers, personal emails, shared logins | Inactivate departed contacts and review personal data |
| Item master | Duplicate SKUs, vague descriptions, wrong unit conversions | Merge, standardize descriptions and verify conversions |
| Customer part numbers | Kept in notes fields or spreadsheets | Load as structured cross-references tied to both masters |
| Contract pricing | Expired agreements still loaded | Retire expired records but keep them in the archive |
In what order should the cleanup happen?#
The cleanup should run from controls to customers to items to links, because each step depends on the one before. Starting on items while branches keep creating new customers and SKUs means cleaning a moving target.
Most migration partners use a version of this list. The difference that shows up later is whether the mapping table is treated as a temporary conversion aid or as a permanent record.
- Gate new master records so only named approvers can create customers and items until cutover.
- Agree the target model: required fields, naming rules, unit of measure rules and account hierarchy in the new ERP.
- Merge duplicate bill-to customers, then their ship-tos and contacts.
- Inactivate customers with no recent activity instead of deleting them.
- Merge duplicate items, fix unit conversions and link supersessions.
- Rebuild customer part numbers and contract pricing against the surviving IDs.
- Write every change to the mapping table and get sign-off from the sales, finance and purchasing owners.
- Run a trial conversion and reconcile open orders, open receivables and inventory to the legacy system.
What is the keep-the-mapping rule?#
The keep-the-mapping rule says every customer or item ID that is merged, renumbered or retired gets a permanent row linking the old ID to the new one, with a reason, an effective date and an approver. The table is stored with the legacy archive and is never discarded after go-live.
Without the mapping, history from the old system cannot be joined to the new one. Pre-cutover invoices reference customer numbers that no longer exist, sales history for merged SKUs splits in two, and any later audit, analysis or licensing review has to rebuild links by guesswork.
| Mapping field | What it holds |
|---|---|
| Legacy ID | The customer or item number as it appears on historical documents |
| New ID | The surviving record in the new ERP |
| Change type | Merge, renumber, retire or split |
| Reason | Branch duplicate, acquired company account, superseded part |
| Effective date | When the change took effect at cutover |
| Approver | The data owner who signed off |
How do you find duplicate customer records?#
Duplicate customer records are found by matching on several signals at once, because no single field is reliable. Names vary with abbreviations and legal suffixes, and addresses vary with suite numbers and spelling.
Strong signals include a normalized street address, a shared email domain, the same phone number and the same parent account. Similar names alone are weak and produce false matches between unrelated companies, so score candidate pairs, approve strong matches in bulk and send uncertain ones to the rep who knows the account.
Some duplicates are intentional. Customers may want separate accounts for separate plants, cost centers or tax treatments, and merging them breaks billing; merge only when the accounts describe the same buyer on the same terms, and link the rest through a parent-child hierarchy.
How should inactive customers and items be handled?#
Inactive customers and items should be archived with their history rather than deleted. A customer who stopped ordering long ago is still the counterparty on invoices, credits and support cases that can matter for audits, disputes, warranty claims and later analysis.
A common pattern is to migrate only active records into the new ERP and leave inactive ones in a read-only archive with the mapping. That keeps the new system lean without losing the trail; the risk sits in the shutdown, because if the old license or hosting ends before a full export, the inactive history ends with it.
What should happen to the legacy system's history?#
Legacy history should be exported in full, with the keys that link records, before the old system is retired. Many migrations convert only masters, open transactions, opening balances and summary history, leaving detailed order lines, quotes, credit memos, notes and attachments behind in Dynamics GP, Sage, JobBOSS or an older Epicor or Infor release.
Owners often see that archive as a cost: a server to keep running or a license to keep paying. It can also be an asset, because linked years of quotes, orders, returns and service notes are the kind of operational history AI developers license, and a mapped archive is far easier to assess than one with broken keys.
- Full detail tables for orders, invoices, credits, quotes and returns, not summary reports.
- Notes, comments and attachments with their parent record IDs.
- Master tables as they stood at cutover, including inactive records.
- The mapping table and a short data dictionary for key fields.
- A note of which system version produced the export.
Illustrative: a wholesale distributor leaves Dynamics GP for NetSuite#
Illustrative: a fictional wholesale distributor of janitorial and packaging supplies is moving from Microsoft Dynamics GP to NetSuite after several acquisitions. Its customer master holds multiple accounts for the same property management companies, and its item master carries identical trash liners under supplier and private-label numbers.
The COO gates new records, merges customers to surviving accounts and links duplicate items, writing each change to a mapping table approved by the controller and the purchasing lead. The migration partner converts active records and open transactions only.
Before the GP environment is shut down, IT exports full order, invoice, credit memo and quote history with notes, plus the mapping table and a data dictionary. When the owner later considers a licensing review, the archive can be described by metadata alone, and its history still joins to current NetSuite records.
How SourceX treats migrated and archived history#
SourceX assesses archived and migrated history the same way as live records, starting with metadata: which system produced it, which years it covers, which record families it holds and whether keys still link them. The mapping table is part of the provenance a buyer will ask about.
Within the SourceX five-step transaction, Supply confirms what the archive holds and Rights checks the customer contracts and vendor terms that applied when the records were created. The SourceX Evidence Packet records provenance, including the source system and the mapping used, so cleaned and legacy records can be traced to their origin.
Frequently asked questions
Should we migrate all history into the new ERP?
Usually not. Detailed history from an old ERP rarely maps cleanly into a new data model, and loading it can slow the project. Most companies convert masters, open transactions and limited history, then keep a complete, read-only archive with the mapping table so older records stay accessible and linkable.
Can we merge customers that still have open balances?
Yes, but plan it with finance. Open invoices, credits and unapplied cash must move to the surviving account, and legacy references must stay visible so statements and collections still make sense to the customer. Many teams reconcile small open items before cutover to keep merges simple.
Who should approve merges and retirements?
The owners of each master: sales or customer service for customers, purchasing or product management for items, and finance for anything with open balances or tax settings. One coordinator should keep the mapping table so approvals and changes live in one place.
What format should the legacy archive use?
Use open, documented formats such as CSV files or a database backup your team can still read, plus a data dictionary for key fields and a copy of the mapping table. Avoid depending on the old application's own report viewer, which disappears when the license or server does.
Does duplicate cleanup matter if the data is only used for reporting?
Yes. Duplicates distort customer counts, sales by account, item velocity and margin reporting. They also weaken any later use of the history, from forecasting to an internal assistant to a licensing review, because one customer or product appears as several unrelated records.
Related resources
See if your company qualifies
A short company assessment. No data uploads are needed.