Customer Master Cleanup Worksheet
A messy customer master causes misapplied cash, wrong tax on invoices, missed credit holds and bad reporting. This worksheet helps you list the problem records, rank them by business impact and track who fixes what by when.
| Customer ID and name | Main issue | Issue count | Revenue last 12 months | Open AR | Priority score | Action | Owner | Due date | |
|---|---|---|---|---|---|---|---|---|---|
140 | |||||||||
50 | |||||||||
35 | |||||||||
10 | |||||||||
44 |
Work the highest priority scores first. Do not delete customers with posted transactions; block or deactivate them using the ERP's standard function. Changes to credit limit, payment terms and tax IDs should follow your approval workflow and leave an audit trail. Check local privacy rules before keeping personal data of dormant contacts.
Want a customer data scan on your ERP?
Optional: share a sanitized export and we will return a ranked list of defects and likely duplicates.
Optional. The tool and every export work without this. No spam.
How to use the worksheet
Export your customer list with revenue and open AR, then run simple checks: blank tax ID, blank payment terms, missing or malformed address, same tax ID or name appearing more than once, and no sales in 24 months. Log each problem customer here with the main issue and the count of issues. The priority score ranks records by how much cleanup matters. Assign an action, owner and due date, and export to Excel to run a weekly status meeting.
Typical defects
Duplicates are the biggest, since one customer's credit exposure is split over two records and cash application fails. Missing tax or VAT numbers block e-invoicing in many countries and break exemption certificates. Bad addresses cause shipping and tax jurisdiction errors. Wrong currency or payment terms cause incorrect invoices and dunning. Dormant records add noise and a data protection burden, so review them against a retention rule.
- Duplicates and near-duplicates
- Missing tax, VAT, GSTIN or similar IDs
- Incomplete or invalid addresses
- Missing payment terms or credit limits
- Dormant accounts with no activity
Reading the priority score
The default formula adds ten points per issue, one point per thousand of open receivables and one point per ten thousand of annual revenue. A customer with several defects and a large open balance rises to the top, while a harmless dormant record falls to the bottom. Adjust the weights so that your own risk, such as credit exposure or e-invoicing compliance, dominates. Re-sort after each batch of fixes.
Common mistakes
Merging customers without checking ship-to and bill-to structures can move invoices to the wrong legal entity. Deleting instead of blocking breaks history. Fixing data in the ERP but not in CRM, e-commerce or EDI systems lets the defect return through the next sync. Skipping prevention, such as mandatory fields and duplicate checks at creation, means the worksheet will be needed again next year.
Frequently Asked Questions
How do I find duplicate customers in an ERP?
Group by tax ID, normalized name, address and bank details, and review groups with more than one record. Fuzzy matching helps with spelling differences. Turn on any duplicate check at customer creation so new duplicates are caught at the source.
Should dormant customers be deleted?
Usually not deleted. Block or deactivate them if they have transactions, and apply your retention policy for personal data. A common rule is to review accounts with no activity for 24 months or more.
Which customer fields matter most?
Legal name, bill-to and ship-to addresses, tax or VAT identifier, currency, payment terms, credit limit, tax class and contact for invoicing. These drive invoicing, tax, credit and collections.
How do I keep the customer master clean afterwards?
Make key fields mandatory, use a creation workflow with duplicate check, assign a data steward, and run a monthly or quarterly scan. Cleanup without prevention tends to degrade again within a year.
If you want customer defects found and ranked automatically from live ERP data, we can build a read-only scan that runs on your own infrastructure.
Related Tools
Duplicate Vendor Detection Worksheet
Record suspected duplicate suppliers, score the matching signals and track the merge decision and owner.
ERP OperationsMaster Data Governance RACI Template
A fill-in RACI matrix and policy outline for item, vendor, customer, BOM and routing master data ownership.
ERP OperationsItem Master Data Quality Checklist
A 33-point checklist for auditing item or material master records before they cause wrong MRP, wrong cost or wrong shipments.
ERP OperationsUnit of Measure Conversion Reference
Searchable table of common UoM conversion factors for ERP item masters, with exact definitions where they exist.