Cleaning up a brokerage CRM without losing anything
Five problems, what each looks like before and after, and the specific mistake that turns each fix into a bigger mess than it started with.
If you want to know what is wrong with your book first, the eight-count hygiene check is the diagnostic. This page is the treatment.
Three rules before you change a single record
- Export first. A full export before you start is the only undo you are guaranteed to have. Do it even if your system has a trash.
- Fix expiry dates before anything else. Every other problem on this list costs you tidiness. A missing expiry date costs you a renewal, silently, and you find out eleven months later.
- Never merge on a name. Names are not keys. In any book of a few thousand there are two people with the same one, and a wrong merge attaches one client's policies to another — which is worse than a duplicate and much harder to notice.
The five fixes
Duplicate clients
Before
J. Smith (no email) · John Smith (john@acme.ca) · Smith, John (no email)
After
John Smith (john@acme.ca), three policies attached
How: Match on the id from your previous system first, then email. Merge into the record with the most history, not the newest one, and move the policies before deleting anything.
What goes wrong: Never merge on name similarity alone. In a four-thousand-client book there are two J. Smiths, and merging them attaches one person's policies to another.
Invalid or missing expiry dates
Before
Policy POL-100244, expiry blank, status active
After
Policy POL-100244, expiry 2027-03-14, status active
How: Get them from the binder or the carrier, not from a guess. Sort by blank first, then by dates more than 18 months out, which are usually typos in the year.
What goes wrong: A blank expiry is invisible to every renewal view you have. This is the count to fix first, because the others cost you tidiness and this one costs you renewals.
Policy numbers appearing twice
Before
POL-100310 on two records, different clients, different premiums
After
POL-100310 on one record; the other re-keyed as POL-100311
How: Check the carrier document before assuming it is a duplicate. Two genuinely different policies sharing a number is a data-entry error; the same policy entered twice is a merge.
What goes wrong: Deleting the wrong one loses the premium and commission history attached to it. Look at both before choosing.
Past expiry, still marked active
Before
20 policies, expiry in the past, status active
After
Lapsed, renewed, or corrected — each one decided
How: These are three different situations wearing the same badge: genuinely lapsed, renewed but never updated, or a wrong date. Work them by client, not by list.
What goes wrong: Bulk-marking them lapsed is fast and wrong. Some of those clients are still paying you.
Clients with no owner
Before
84 clients, owner blank
After
Every client assigned to one named person
How: Assign by whoever last touched the file, then let the owners correct it. Do not spread them evenly to make the numbers look fair.
What goes wrong: None, and this is the cheapest fix on the list. Do it first if you want a quick win.
The before-and-after examples use illustrative records, not data from any brokerage.
Reconcile at the end, or you have not finished
Cleanup without a reconciliation is a cleanup you cannot defend. Write down four numbers before you start and check them after: client count, policy count, total annual premium, and count of active policies.
Client count should fall, by roughly the number of duplicates you merged. Policy count should not fall at all unless you deliberately deleted duplicates, and you should be able to name each one. Total premium should not move. If it does, a policy went somewhere you did not intend.
That premium figure is the single best check available, because it is sensitive to exactly the errors that are otherwise invisible.
Stopping it coming back
A book does not get messy through neglect so much as through the absence of a rule. Three prevent most of it: decide whether a record is a person or a household and write it down, require an owner and an expiry date at the point of entry, and match on a stable id rather than a name whenever records arrive from elsewhere.
That last one is why an import that matches on the source system's own id is worth more than one that matches on email: re-importing a corrected file adds nothing the second time. It is how CovioIQ's client import works, and it is the question to ask any vendor before a migration.
Start from a clean sheet instead
If the book is beyond repairing in place, the tracking template has the columns these fixes assume and is a reasonable place to rebuild from.
Related: organising a book, all guides.