How to Clean and Deduplicate an HCP Database
Clean before you deduplicate, and never merge before you can prove the match. The order is: profile the damage, standardise the fields that matching depends on, block and score candidate pairs, review the ambiguous band by hand, write the surviving values onto the winning record first, then merge. In both Salesforce and Veeva CRM the merge deletes the losing record and cannot be undone, so every step before it exists to make that irreversible action safe. For 200,000 records, expect six to ten weeks end to end, of which merging is the last five days.
This article is about fixing a database you already have. Blog #43 covers building a doctor 360 from scratch — architecture, identity model, greenfield design. Different job, different reader.
This one assumes the opposite situation: a live CRM with a few hundred thousand records, a field force syncing to it every day, campaigns running from it, and someone senior asking why the same cardiologist appears four times. You cannot re-architect. You have to operate on a patient who is awake.
If you are designing something new, read #43 first and treat this as the maintenance chapter. If you have a mess and a deadline, you are in the right place.
How do I remove duplicates from a doctor database?
Six phases, in this order. The sequence is not a preference — each phase makes the next one cheaper and safer, and running them out of order is the most common way these projects generate new problems.
- Profile before you touch anything. Measure what is actually wrong: how many records, how many suspected duplicates, which fields are empty, which are malformed, which sources contributed what. You cannot report success without a baseline, and you will be asked.
- Freeze the entry points, temporarily. Identify every route by which records are created — field app, web forms, list imports, integrations — and pause or gate the uncontrolled ones for the duration. Cleaning a database that is still taking in dirty records is a treadmill.
- Standardise the matching fields. Phone numbers, names, addresses, specialties and registration numbers into consistent formats. This alone converts a large share of fuzzy comparisons into exact matches.
- Block and score candidate pairs. Group comparable records cheaply, then score within groups. Never compare every record against every other — at 200,000 records that is 20 billion comparisons and it is unnecessary.
- Review the ambiguous band by hand. Everything between the auto-merge and auto-reject thresholds goes to a person. Staff this properly. It is the single highest-value hour anyone spends on the project.
- Write the surviving values, then merge in controlled batches — with a documented pre-flight check, a reversible staging record, and a stop rule.
Phases one to five are recoverable at any point. Phase six is not. That asymmetry should shape how much time you spend on each.
Before you merge anything: it cannot be undone
In Salesforce, a merge deletes the losing records and cannot be undone — it reparents contacts, opportunities and activities onto the survivor and retires the duplicates. In Veeva CRM, the winning account survives and receives the merged child records while the losing account is removed from the system, with no rollback documented. There is no undo button in either. Everything in this runbook before the merge step exists because of this.
Most data-quality guidance treats the merge as the trivial final step. In a pharma CRM it is the step with consequences, because the record you are merging is not just a name and an address — it carries call history, territory assignment, cycle plan targets, sample transactions and affiliations, and each of those moves according to rules you may not have read.
What actually happens to the data — Veeva CRM
From Veeva's own account merge documentation. Configuration varies, so verify against your own instance before relying on any row.
| Object | Documented behaviour on merge | What it means for you |
|---|---|---|
| The losing account | Removed from the system after merge completion | Gone. Confirm your archive exists before you start |
| Addresses | Behaviour is controlled by the `ACCOUNT_ADDRESS_MERGE_BEHAVIOR_vod` setting — copied as-is, marked inactive, or marked active on the winning account | Check this setting before your first merge. It silently determines whether you end up with a clean address or four |
| Calls and call reports | Call reports from the losing account are migrated to the winning account when users sync from offline devices | Field history is preserved — but it depends on sync configuration and permissions being correct first |
| Territory assignments | Account Territory Loader records merge so the winning account inherits the loser's territories; the loser's ATL record is deleted | The winner may end up in more territories than intended. Review territory counts after the first batch |
| Territory-specific fields | The winner retains its own TSF; the loser's TSF is copied only where no matching territory exists on the winner | Territory-specific values on the losing record can be silently discarded |
| Sample limits and transactions | Losing account disbursements are reparented under the master record and remaining quantities update accordingly | Documented caveat: sample limit violations cannot be enforced during a merge. A merged account can end up over limit |
| Hierarchies | When merging accounts with different hierarchies, the losing account's hierarchy is removed | A real and easily missed loss. Capture hierarchy membership before merging |
| Affiliations, child accounts, pricing rules | Merged by Veeva's custom process after the standard Salesforce merge completes | Two-stage process — failures can leave partial state. Verify after each batch |
The gotcha that catches experienced teams Deduplication can create duplicates. Veeva has published support articles documenting duplicate product metrics records appearing after an account merge, and account merges creating duplicated cycle plan target records. The implication for your project plan is specific: your post-merge validation must check the downstream objects, not just the account count. A batch that reduces 400 accounts to 200 and quietly doubles the cycle plan targets has not succeeded — and the account-level metric everyone reports on will say it did. Add cycle plan targets and product metrics to your batch validation query from batch one, not after somebody notices in a quarterly review. |
What "dirty" actually means — the eight defect types
Duplication is one defect among eight, and teams that fix only duplication leave most of the damage in place. Profile all eight before scoping the work, because the mix determines how long this takes.
| # | Defect | How to detect it | Typical cause |
|---|---|---|---|
| 1 | Exact duplicates | Group by normalised name plus mobile, or by registration number. Count groups with more than one record | Repeated imports, or the same doctor added by two reps |
| 2 | Near duplicates | Fuzzy score above threshold on name plus location. The expensive category — needs matching, not a query | Spelling variants, initials expanded differently, changed institution |
| 3 | Malformed values | Regex checks per field — phone length, email shape, PIN digits, registration number pattern | Free-text entry with no validation at the point of capture |
| 4 | Missing critical fields | Null or empty counts on specialty, city, mobile, registration number, consent state | Fields not mandatory in the capture form |
| 5 | Stale records | Last-modified or last-verified date older than your threshold, by field | No refresh process. Usually the largest category by volume and the least visible |
| 6 | Wrong-entity records | Records that are institutions, departments or clinics stored as doctors; test records; staff records | Free-text creation with no type discipline |
| 7 | Inconsistent reference values | Distinct-value counts on specialty, qualification, institution, city. A specialty list with 340 values has about 40 real ones | No controlled vocabulary |
| 8 | Orphaned or unreachable | Bounced emails, dead mobile numbers, addresses that failed a visit, doctors who have retired or died | No feedback loop from the field or from campaign results |
Run all eight counts before you scope. The result is usually uncomfortable and it is the only honest basis for a timeline — a database whose main problem is defect five needs a refresh programme, not a deduplication project, and those are very different pieces of work.
It is also the baseline you will report against. Across our own client CRM audits, roughly 57% of doctor records carry at least one discrepancy across these categories, which is a useful sense of scale but no substitute for measuring your own.
Standardise before you deduplicate — the order that halves the work
The single most effective thing in this runbook, and the one most often skipped because it feels like preparation rather than progress.
A fuzzy matcher comparing "Dr. R.K. Sharma, +91 98765 43210" against "Rajesh Kumar Sharma, 9876543210" has to work hard and will produce a score that needs review. Standardise both first and they become an exact match on mobile number — resolved with a group-by, at zero risk, in seconds.
| Field | Standardise to | Effect on matching |
|---|---|---|
| Mobile number | E.164 — strip spaces, punctuation, country prefixes, leading zeros. Store one canonical form | The highest-yield single transformation. Converts a large share of near-duplicates into exact matches |
| Name | Decompose into title, given, middle, surname. Generate variants: expanded and contracted initials, common transliteration alternates, order-inverted form | Feeds better comparison. Do not collapse to one spelling — keep variants as match evidence |
| Registration number | Strip formatting, uppercase, store the council code separately from the number | Turns your strongest key into a usable one. Watch for transposed digits from manual entry |
| Lowercase, trim, flag shared clinic addresses appearing against multiple names | Strong when institutional, weak when shared | |
| Address | Componentise into line, locality, city, district, state, PIN. Canonicalise city names | Blocking on PIN becomes reliable, which makes everything downstream cheaper |
| Specialty | Map to a fixed taxonomy of 40 to 60 values with a synonym table | Reduces reference chaos and makes blocking on specialty viable |
| Institution | Canonical institution list with alias mapping | Prevents one hospital appearing as nine and inflating affiliation-based mismatches |
Do this in a staging copy, not in production. The standardised values will eventually be written back, but not until matching is complete — you want the raw values available for adjudication when a merge is questioned.
Fastest way to dedupe 200,000 doctor records
Six to ten weeks with one data person and part-time review support — not a weekend, and anyone quoting a weekend is quoting for the matching step alone. Realistic shape: one week to profile and freeze entry points, two weeks to standardise, one week to build and tune matching, two to three weeks to work the review queue, one week to merge in batches, one week to validate and close entry points. The merging itself is about five days. Everything else exists to make those five days safe.
| Phase | Duration at 200k | What is happening | Where it slips |
|---|---|---|---|
| Profile and freeze | Week 1 | Eight defect counts, baseline documented, entry points identified and gated | Discovering more record-creation routes than expected. Usually integrations nobody catalogued |
| Standardise | Weeks 2–3 | Phone, name, registration, address, specialty and institution normalised in staging | Building the specialty taxonomy and institution alias list. Budget more time than feels reasonable |
| Match and tune | Week 4 | Blocking schemes, scoring, threshold calibration against a hand-labelled sample | Calibrating thresholds without a labelled sample. Label 300 pairs by hand first — it saves a fortnight |
| Review queue | Weeks 5–7 | Human adjudication of the ambiguous band | The most under-resourced phase. At 200k expect a few thousand pairs needing eyes |
| Merge | Week 8 | Golden values written, then batched merges with validation between batches | A failed batch mid-run. Have the stop rule agreed in advance |
| Validate and close | Week 9 | Downstream object checks, territory and cycle plan verification, entry points permanently gated | Discovering the merge duplicated cycle plan targets. Which is why it is validated here |
Two ways to genuinely accelerate this, and one way that only appears to. Genuinely faster: start with the highest-confidence deterministic matches and merge those first, which typically clears a meaningful share of duplicates in week four with near-zero risk. And parallelise the review queue across several people with a written adjudication rule so decisions are consistent.
Only appears faster: raising the auto-merge threshold to shrink the review queue. It does compress the timeline, and it converts a visible workload into invisible false merges that will surface in six months as a territory alignment problem nobody can explain.
AI tools for HCP record matching — what actually helps
A fair answer, because the honest version is more useful than the enthusiastic one. AI helps materially with three parts of this job and not at all with two others.
| Task | Does AI help? | Detail |
|---|---|---|
| Name variant generation and transliteration | Yes, substantially | Generating plausible romanisation variants for Indian names is a genuine language problem and models handle it far better than phonetic algorithms designed for English surnames |
| Scoring ambiguous pairs | Yes | A trained matcher outperforms hand-tuned weights, particularly where evidence is mixed — same name, different city, similar registration number |
| Reference data mapping | Yes | Mapping 340 free-text specialty values onto a 45-value taxonomy, or clustering institution name variants, is well suited to a model and tedious by hand |
| Deciding whether to merge | No — and this is the important row | Keep the human in the ambiguous band. The cost of a false merge is asymmetric and irreversible. A model that is 97% right is wrong about 90 pairs in 3,000, and you cannot undo them |
| Verifying a doctor exists and practises where you think | No | That is a verification operation against registers and field confirmation, not a matching problem. No model can confirm a fact absent from your data |
The practical framing for a CRM manager evaluating tools: AI should shrink the review queue, not eliminate it. A vendor promising fully autonomous merging on pharma CRM data is proposing to make irreversible decisions on your behalf, in a system where the losing record is deleted. That is a claim to interrogate rather than a feature to buy.
We treated the broader question of automating this in automating doctor data validation and enrichment with AI.
The merge safety protocol
Because there is no undo, safety has to be procedural. This protocol adds roughly two days to the project and is the reason the project does not become an incident.
Pre-flight — before the first batch
- Take a full export of every affected object, not just accounts. Accounts, addresses, calls, territory assignments, cycle plan targets, product metrics, sample transactions, affiliations. This export is your only rollback. Store it somewhere you can find it under pressure.
- Record the pre-merge counts for every one of those objects. You will compare against them after each batch, and without the baseline you cannot tell whether a downstream object duplicated.
- Check `ACCOUNT_ADDRESS_MERGE_BEHAVIOR_vod` or the equivalent configuration in your instance, so you know what will happen to addresses before it happens.
- Capture hierarchy membership for every losing account, since merging accounts with different hierarchies removes the loser's hierarchy.
- Run the whole protocol in a sandbox first, on a representative sample including the awkward cases — multi-territory doctors, accounts with sample transactions, accounts in different hierarchies.
- Agree the stop rule in writing. What failure rate, what unexpected count change, what field-team report halts the run. Agreeing this under pressure at 4pm on a Thursday produces bad decisions.
Execution — the batch discipline
- Write the surviving field values onto the winning record first, keyed on its record ID, so the golden values are already in place before the merge reparents children and retires the loser.
- Merge in small batches — start at 50 pairs, then 200, then 500 once two clean batches have validated. Never the whole set in one run.
- Validate between every batch. Account count down by the expected number; addresses, calls, territories, cycle plan targets and product metrics all within expected bounds. A downstream count that went up is a stop condition.
- Merge during a sync-quiet window where the field force is not actively syncing, and communicate the window in advance.
- Keep a merge log — winning ID, losing ID, match score, who approved it, timestamp. When someone disputes a merge in four months, this log is the only way to answer them.
On rollback, honestly
There is no rollback. There is only reconstruction. - It is worth being blunt with stakeholders about this at the start rather than discovering it during an incident.
- You cannot un-merge. What you can do is reconstruct: recreate the losing record from your export, restore its child records, and reattach them. That is manual, slow, imperfect — reconstructed records lose their original IDs, which breaks any external integration that referenced them — and it is only possible if you took the export.
- Which is why the export is the first item on the pre-flight list, why batches start at fifty, and why the review queue is staffed properly rather than tuned away. The entire protocol is an admission that the last step is unforgiving.
Keeping it clean — closing the entry points
A deduplication project without this section is a service you will buy again in eighteen months. The record count starts drifting back within weeks of the merge if the routes that created the mess are still open.
| Entry point | What goes wrong | The control |
|---|---|---|
| Field app record creation | A rep cannot find a doctor in three seconds and creates a new one. The single largest source of new duplicates | Search-before-create with fuzzy matching and a forced review of near matches. Make finding easier than creating |
| List imports | A marketing list loaded directly, no matching against existing records | No import reaches production without passing the matcher. Route every list through staging |
| Web and event forms | Doctors register with a personal email and a shortened name, creating an unmatched twin | Match on submission against mobile and registration number, and queue rather than create when uncertain |
| Integrations | A connected system creates records on its own identity logic | Agree the identity contract per integration. Inventory these — teams routinely find integrations nobody remembered |
| Vendor data loads | A purchased file appended rather than matched | Load into staging, match, and merge deliberately. Never append a vendor file into production |
| Free-text reference fields | Specialty and institution typed freely, so the vocabulary re-fragments | Controlled vocabulary with a request process for new values |
Two controls carry most of the benefit. Search-before-create in the field app addresses the biggest single source at the point where it happens. And no list enters production unmatched closes the route that creates duplicates in bulk rather than one at a time.
Then set a standing measure — duplicate rate reported monthly, not annually. A number that is watched stays flat; a number that is measured once a year is measured only after somebody complains. The maintenance cadence is covered in how often pharma teams should refresh their HCP database.
Which record should win the merge?
A question that sounds trivial and is not, and one that most guidance skips entirely because it conflates two different decisions.
Choosing the surviving record and choosing the surviving field values are separate decisions. The surviving record is chosen for its connections — activity history, territory assignment, integration references, external system links. The surviving field values are chosen for their accuracy, field by field. The right pattern is almost always: keep the record with the most downstream dependencies, then overwrite its field values with the best available ones before merging.
Choosing the record with the cleanest data as the winner is the intuitive move and usually the wrong one. A pristine record created last month has no call history, no territory alignment and no integration references, so making it the survivor destroys the connections held by the messier record it replaces — and those connections are far harder to reconstruct than a field value.
| Choose the winner by | Priority | Reasoning |
|---|---|---|
| Volume of activity and call history | Highest | The most expensive thing to lose and the hardest to reconstruct. A record with three years of calls should almost always survive |
| Territory assignment and cycle plan membership | High | Reassignment is manual and visible to the field force on Monday morning |
| External integration references | High | Any system holding this record's ID breaks silently when it is deleted. Inventory these before choosing |
| Record age | Medium | Older records usually accumulated more of the above, though not always |
| Data completeness | Lowest | Counter-intuitive but correct — field values are cheap to copy across, connections are not. Fix the data on the winner instead |
Write this as an explicit, ordered rule before the review queue opens, so reviewers are not making the call individually. Inconsistent winner selection across a few thousand merges produces a database where the logic cannot be explained afterwards — and it will be questioned.
What a reviewer actually does
The review queue is where the project succeeds or fails, and it is usually handed to someone with no written guidance. This is the guidance.
A reviewer is answering one question — is this the same human being? — not judging data quality. Those are different questions and conflating them produces inconsistent decisions.
The evidence hierarchy
| Evidence | Weight | How to read it |
|---|---|---|
| Same registration number, same council | Decisive — merge | Unless one is a transposition of the other's digits. Check the pattern before accepting |
| Same personal mobile | Strong — merge | Unless the number appears against several distinct names, which marks it as a clinic line |
| Same name, same institution, same specialty | Strong | Two doctors with the same name in the same department is possible but rare. Check for a father-and-son or initials difference |
| Same name, same city, different specialty | Weak — usually reject | Common Indian names recur. Different specialty in the same city usually means two people |
| Similar name, different city, no shared identifier | Reject | Unless there is evidence of a move — a dated affiliation change. Absent that, do not merge |
| Same email on a shared clinic domain | Weak | Practice addresses are shared between partners. Not identity evidence on its own |
Three rules that keep decisions consistent
- When genuinely uncertain, do not merge. An unmerged duplicate is a visible problem you can fix next quarter. A false merge is invisible, irreversible and propagates into targeting and incentive compensation. The asymmetry should make reviewers conservative by default, and they should be told so explicitly.
- Record the reason, not just the decision. One field, a few words — 'same reg no', 'different specialty, common name', 'moved, affiliation dated'. When a merge is challenged in six months, this is the answer. It also lets you audit reviewer consistency, which you should.
- Escalate rather than guess on high-value records. Any doctor above a defined potential threshold, or with an active cycle plan, goes to a second reviewer or to the commercial owner. The cost of a false merge is not uniform across the database, and the review process should reflect that.
A calibration step worth the half day - Before the queue opens, have every reviewer independently adjudicate the same 50 pairs, then compare.
- Disagreement rates above about 10% mean the rules are not clear enough, and the fix is to refine the written guidance rather than to trust that people will converge. Convergence does not happen on its own — reviewers develop private heuristics, and by pair 800 you have two different standards applied to the same database with no way to tell which record got which.
- Repeat the calibration once mid-queue. It takes an hour and it is the only way inconsistency gets caught while it is still fixable.
What duplicates actually cost
Included because most CRM managers reading this already know the database is dirty and need the argument that unlocks the time to fix it. Vague appeals to data quality do not win that argument. Specific costs do.
| Cost | How it shows up | How to quantify it for your own case |
|---|---|---|
| Wasted channel spend | The same doctor receives the same WhatsApp message twice, billed twice at the marketing rate | Duplicate rate × campaign volume × per-message cost. The easiest number to produce and the one finance responds to |
| Damaged doctor relationships | Two reps from the same company call the same doctor, unaware of each other | Count multi-territory duplicate clusters. Then ask the field force — they will have examples |
| Distorted territory alignment | Territories sized on inflated doctor counts, so coverage models are wrong | Duplicate rate by territory. A territory with 15% duplicates is carrying a 15% overstated workload |
| Incentive compensation errors | Targets set against an inflated universe, or credit split across duplicate records | The one that gets attention fastest. Check whether any duplicate cluster spans two reps' targets |
| Analytics that cannot be trusted | Reach and frequency reporting counts duplicates as separate doctors, overstating coverage | Compare reported reach against deduplicated reach for one recent campaign |
| Compliance exposure | A doctor withdraws consent on one record and continues receiving messages via its twin | Count duplicate clusters with conflicting consent states. This is the number to lead with |
The last row is the one that changes the conversation. Every other cost on this list is inefficiency, and inefficiency gets scheduled. A duplicate cluster where one record carries a withdrawal and the other does not is a live compliance defect — under a framework where security-safeguard failures carry a ₹250 crore maximum, that reframes deduplication from a data-hygiene project into a risk-remediation one.
Run that single query before you write the business case. It usually returns more than anyone expects, and it is the number that gets the project resourced.
Frequently Asked Questions For How to Clean and Deduplicate an HCP Database
Six phases in order: profile the damage and record a baseline; freeze or gate the routes creating new records; standardise the fields matching depends on — phone, name, registration number, address, specialty; block and score candidate pairs rather than comparing everything against everything; hand-review the ambiguous band between your auto-merge and auto-reject thresholds; then write the surviving values onto the winning record and merge in small validated batches. Phases one to five are recoverable. Phase six is not, which is why the first five deserve most of the time.
Six to ten weeks with one data person and part-time review support. Roughly: one week profiling, two weeks standardising, one week building and tuning the matcher, two to three weeks working the review queue, one week merging in batches, one week validating and closing entry points. The merge itself is about five days. You can genuinely accelerate by merging high-confidence deterministic matches first and by parallelising review with a written adjudication rule. You cannot safely accelerate by raising the auto-merge threshold — that converts a visible workload into invisible false merges.
No. In Salesforce a merge deletes the losing records and cannot be undone; it reparents contacts, opportunities and activities onto the survivor and retires the duplicates. In Veeva CRM the winning account survives and receives merged child records while the losing account is removed from the system, with no rollback documented. What you have instead of rollback is reconstruction from a pre-merge export — manual, imperfect, and impossible if you did not take the export. Take the export, run in a sandbox first, and merge in small batches with validation between them.
Per Veeva's documentation: call reports from the losing account migrate to the winning account when users sync from offline devices, subject to correct sync configuration and permissions. Territory assignments merge so the winning account inherits the loser's territories, and the loser's Account Territory Loader record is deleted — which can leave the survivor in more territories than intended. Territory-specific field values on the winner are retained, and the loser's are copied only where no matching territory exists on the winner. Address behaviour depends on a configuration setting, so check it before your first merge. Verify all of this against your own instance, since configuration varies.
Yes, and it is a documented behaviour rather than a theoretical risk. Veeva has published support articles describing duplicate product metrics records appearing after an account merge, and account merges creating duplicated cycle plan target records. The practical consequence is that post-merge validation must check downstream objects, not just the account count. Add cycle plan targets, product metrics, addresses and territory assignments to your batch validation from the first batch — a run that halves the account count while doubling cycle plan targets has not succeeded, and the headline metric will not tell you.
Before, and the gap is larger than most teams expect. Standardising mobile numbers to a single format, decomposing names, normalising registration numbers and componentising addresses converts a substantial share of near-duplicates into exact matches — resolvable with a group-by at zero risk instead of a fuzzy score needing review. Standardising afterwards means your matcher does hard work on problems that a text transformation would have solved. Do it in a staging copy and keep the raw values, because you will need them when a merge is challenged.
Two thresholds, never one. Above the upper bound, auto-merge. Below the lower, auto-reject. Between them, human review. The correct values depend on your data and should be calibrated against a hand-labelled sample — label around 300 pairs manually before tuning, which takes a day and saves a fortnight of guessing. The instinct to use a single threshold to avoid the review queue is the most common cause of false merges, and false merges are irreversible and invisible.
For three tasks, substantially: generating name variants and handling transliteration, which is a genuine language problem that phonetic algorithms designed for English surnames handle poorly; scoring ambiguous pairs, where a trained matcher beats hand-tuned weights; and mapping messy reference data, such as collapsing 340 free-text specialty values onto a proper taxonomy. For two tasks, no: deciding whether to merge in the ambiguous band, because the cost of error is asymmetric and irreversible, and verifying that a doctor exists and practises where you believe, which is a verification operation rather than a matching one. AI should shrink the review queue, not eliminate it.
Let's Discuss Your Requirements
