# Customer Master Cleanup Worksheet

> Source: https://netray.co/tools/customer-master-cleanup-worksheet
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Track customer records with data defects, score cleanup priority by revenue and issue count, and assign an owner and action.

## Category

erp-operations

## Audience

- ERP administrator
- Sales operations
- Credit controller
- Data steward

## Tool

**Kind:** worksheet

**Mode:** editable

**Columns:**

#### Customer ID and name

**Key:** cust

**Type:** text

#### Main issue

**Key:** issue

**Type:** select

**Options:**

- Duplicate
- Missing tax ID
- Bad address
- Missing payment terms
- Dormant
- Wrong currency
- Other

#### Issue count

**Key:** issues_n

**Type:** number

#### Revenue last 12 months

**Key:** revenue

**Type:** number

**Format:** currency

#### Open AR

**Key:** open_ar

**Type:** number

**Format:** currency

#### Priority score

**Key:** priority

**Type:** number

**Expr:** issues_n * 10 + open_ar / 1000 + revenue / 10000

**Format:** integer

**Help:** Issue count x 10, plus open AR per 1,000, plus revenue per 10,000. Tune to your policy.

#### Action

**Key:** action

**Type:** select

**Options:**

- Merge
- Correct fields
- Block
- Deactivate
- Keep as is

#### Owner

**Key:** owner

**Type:** text

#### Due date

**Key:** due

**Type:** date

**Rows:**

**Cust:** C1001 Orion Aerospace Inc

**Issue:** Duplicate

**Issues n:** 3

**Revenue:** 480000

**Open ar:** 62000

**Action:** Merge

**Owner:** Credit

**Due:** 2026-11-15

**Cust:** C1188 Summit Defense Systems

**Issue:** Missing tax ID

**Issues n:** 1

**Revenue:** 215000

**Open ar:** 18000

**Action:** Correct fields

**Owner:** Sales Ops

**Due:** 2026-11-20

**Cust:** C2040 Baxter Fabrication

**Issue:** Missing payment terms

**Issues n:** 2

**Revenue:** 56000

**Open ar:** 9400

**Action:** Correct fields

**Owner:** Credit

**Due:** 2026-11-22

**Cust:** C0731 Old Mill Supply

**Issue:** Dormant

**Issues n:** 1

**Revenue:** 0

**Open ar:** 0

**Action:** Deactivate

**Owner:** Sales Ops

**Due:** 2026-12-05

**Cust:** C3320 Keystone Components GmbH

**Issue:** Wrong currency

**Issues n:** 1

**Revenue:** 130000

**Open ar:** 21000

**Action:** Correct fields

**Owner:** Finance

**Due:** 2026-11-30

**Totals:**

#### Revenue touched

**Key:** revenue

**Agg:** sum

**Format:** currency

#### Open AR touched

**Key:** open_ar

**Agg:** sum

**Format:** currency

#### Top priority score

**Key:** priority

**Agg:** max

**Format:** integer

#### Records logged

**Key:** cust

**Agg:** count

**Format:** integer

**Currency:** $

**Notes:** 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.

## Content

**Intro:** 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.

**Sections:**

#### How to use the worksheet

**Body:** 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

**Body:** 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.

**Bullets:**

- 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

**Body:** 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

**Body:** 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.

**Cta:** 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.

**Faq:**

#### How do I find duplicate customers in an ERP?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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.

## Search terms

customer master cleanup worksheet, customer master data cleanup, customer data cleansing template, duplicate customer records ERP, customer master audit excel, dormant customer deactivation, customer master data quality, business partner cleanup template

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json