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.
More ERP Operations tools
See allKeep exploring
Free tools
- ERP Master Data AI Cleanup EstimatorTurn record count, error rate, and per-record review time into the hours and dollars saved by using AI to accelerate master data cleanup versus a fully manual review.
- VISUAL to CloudSuite Data Cleanup WorksheetTrack record counts, cleanup effort and owners for each master data object before a VISUAL to CloudSuite Industrial migration.
- Data Cleansing Defect Log WorksheetLog each data quality defect with the affected records, severity, owner, effort and status, and see the total cleanup effort and cost.
Guides
- ERP Master Data Cleanup with AI: A Practical GuideClean up ERP master data with AI: duplicate detection, standardization, and a governance model that keeps your item and customer records clean long term.
- AI Data Cleansing Before ERP Migration: Load Clean or Regret It for a DecadeAI data cleansing before ERP migration: dedupe vendors, fix item masters, and standardize BOMs so your SyteLine or LN migration loads clean the first time.
- Salesforce ERP Master Data Synchronization GuideSynchronize customer, item, and pricing master data between Salesforce CRM and ERP systems with conflict resolution, governance, and real-time sync patterns.
AI on your ERP
- AI for SOLIDWORKS PDM: Ask Your Vault and Your ERP the Same QuestionPrivate AI for SOLIDWORKS PDM Professional: search vault metadata, explain where-used and BOM differences, review ECO workflows and sync with your ERP item master.
- AI for ERP Supplier Onboarding: Document Checks, Duplicates and Approval PacksAI to speed ERP vendor onboarding: document checks, duplicate detection, bank-detail risk flags and approval packs. Human-approved, on-prem if needed.
Integrations
- How to add an AI chatbot to Priority ERPAdd an AI chatbot to Priority ERP for staff or customers: grounded in live Priority data, role-aware, and able to run fully on-prem with open-weight models.
- How to add WhatsApp to Priority ERPHow to connect WhatsApp Business to Priority ERP: OData or Web SDK reads, template messages, opt-in rules and two-way replies that post back into Priority safely.