Duplicate and stale records

Every CRM export carries the habits of the people who typed into it. Your job is not to make the data look good; it is to make it readable while leaving the mess visible to whoever can fix it.

Three issues that distort a forecast most

1. 1Duplicates — the same account or deal entered twice, usually with a spelling variant or a second owner. Effect: inflated totals.
2. 2Stale records — no activity for weeks, or a close date already in the past. Effect: a forecast built on abandoned deals.
3. 3Missing or default amounts — blanks, zeroes, or an obvious placeholder repeated across rows. Effect: a total that silently undercounts.

A working method

1. 1Copy the raw export to an untouched tab. Never edit the original.
2. 2Add your own columns rather than overwriting theirs: cleaned_amount, duplicate_of, staleness_days, issue_note.
3. 3Sort by account name and scan for near-matches. Record merges in duplicate_of instead of deleting a row.
4. 4Compute staleness from the last-activity date. Do not judge it yet — just measure it.
5. 5Filter for blank or suspicious amounts and note them. Exclude them from the headline only if you say so in the report.
6. 6Keep a short log tab: what you changed, how many rows, and why.

The difference between cleaning and hiding is the log. If a reader can see what you changed and undo it, you cleaned. If they cannot, you hid.

Two judgement calls you will have to make

• Two similar accounts may be genuinely different companies. When you are unsure, flag rather than merge, and say so.
• An old deal may be dormant rather than dead. Report it as 'needs a decision', not as lost.