<\/span><\/h2>\nOps and recruiters cleaning lists before outreach\/enrichment. If you\u2019re about to upload a file, assign recruiter ownership, or start outreach, this SOP prevents double-taps, duplicate submissions, and reporting noise.<\/p>\n
Scope: you have a spreadsheet\/CSV with some mix of NPI<\/strong>, name, specialty, practice location, and sometimes a state license<\/strong>. We\u2019ll treat this as identity resolution<\/strong>: deciding when two rows represent the same provider, then keeping one survivor record with an audit trail.<\/p>\n<\/span>Quick Answer<\/span><\/h2>\n\n- Core Answer<\/dt>\n
- To dedupe provider list by NPI, normalize NPI to 10 digits, group by NPI, select one survivor per group, and log duplicates with source and recency.<\/dd>\n
- Key Insight<\/dt>\n
- NPI is the safest primary key; when it\u2019s missing or invalid, fall back to state license, then name+city with strict normalization and a duplicate log.<\/dd>\n
- Best For<\/dt>\n
- Ops and recruiters cleaning lists before outreach\/enrichment.<\/dd>\n<\/dl>\n
\nCompliance & Safety<\/strong><\/p>\nThis method is for legitimate recruiting outreach only. Always respect candidate privacy, opt-out requests, and local data laws. Heartbeat does not provide medical advice or legal counsel.<\/p>\n<\/blockquote>\n
<\/span>Framework: The \u201cOne Person, One Record\u201d Rule: stop double-tapping candidates<\/span><\/h2>\nDuplicates don\u2019t just waste time. They create candidate annoyance (multiple calls\/emails), internal confusion (two recruiters \u201cown\u201d the same physician), and bad economics (more dials per submittal, lower connectability, and messy attribution).<\/p>\n
Deliverables when you follow this SOP:<\/strong><\/p>\n\n- SURVIVORS<\/strong>: one row per provider identity, ready for outreach or enrichment.<\/li>\n
- DUPLICATES_LOG<\/strong>: a mapped audit trail (duplicate_of, tier, reason, source, timestamp).<\/li>\n
- Before\/after snapshot<\/strong>: duplicate rate and collision rate so ops can prove the cleanup worked.<\/li>\n<\/ul>\n
\n- One identity<\/strong> (the person) can have many attributes (emails, phones, locations, licenses).<\/li>\n
- Your working file should have one survivor row<\/strong> per identity, plus a duplicate log<\/strong> that preserves where the other rows came from.<\/li>\n
- Every dedupe decision should be explainable later (ops, compliance, recruiter handoffs).<\/li>\n<\/ul>\n
In this SOP, recency<\/strong> is the tie-breaker: when two rows disagree, keep the most recently updated or most recently verified attribute, and record what changed.<\/p>\n<\/span>Step-by-step method<\/span><\/h2>\n<\/span>Step 0: Make a safe working copy and add audit columns<\/span><\/h3>\nDuplicate the file and add these columns (even if blank):<\/p>\n
\n- row_id<\/strong> (stable unique ID; if you don\u2019t have one, create it)<\/li>\n
- dedupe_key<\/strong> (the key you used to group)<\/li>\n
- dedupe_tier<\/strong> (NPI \/ state license \/ name+city)<\/li>\n
- survivor_flag<\/strong> (TRUE\/FALSE)<\/li>\n
- duplicate_of<\/strong> (row_id of survivor)<\/li>\n
- duplicate_reason<\/strong> (why you grouped them)<\/li>\n
- source<\/strong> (where the row came from)<\/li>\n
- last_updated<\/strong> (date field if you have it)<\/li>\n<\/ul>\n
This is how you log duplicates<\/strong> without losing traceability.<\/p>\n<\/span>Step 1: Normalize NPI (primary key)<\/span><\/h3>\nNPI definition:<\/strong> The National Provider Identifier (NPI) is a unique 10-digit identifier for health care providers in the U.S.<\/p>\nNormalization rules (apply before grouping):<\/p>\n
\n- Strip spaces, hyphens, and non-numeric characters.<\/li>\n
- After cleaning, accept only values that are exactly 10 digits.<\/li>\n
- Anything else is treated as missing NPI and routed to fallback tiers.<\/li>\n<\/ul>\n
Copy\/paste rules<\/strong> (Google Sheets examples):<\/p>\n\n- NPI_CLEAN (digits only)<\/strong>: =REGEXREPLACE(A2,”[^0-9]”,”” )<\/em><\/li>\n
- NPI_VALID (10 digits)<\/strong>: =IF(LEN(B2)=10,TRUE,FALSE)<\/em><\/li>\n
- NPI_FOR_KEY (blank if invalid)<\/strong>: =IF(C2,B2,””)<\/em><\/li>\n<\/ul>\n
Set dedupe_tier<\/strong> to \u201cNPI\u201d when NPI_VALID is TRUE.<\/p>\n<\/span>Step 1.5: Separate individual vs organization records before dedupe<\/span><\/h3>\nOne common ops failure is mixing individual providers with organization records in the same file. Don\u2019t try to \u201cfix\u201d this during dedupe\u2014split it first.<\/p>\n
\n- If your source labels record type, split into two tabs: INDIVIDUAL<\/strong> and ORGANIZATION<\/strong>, then dedupe INDIVIDUAL only with this SOP.<\/li>\n
- If you don\u2019t have a label, use a practical rule: if the row\u2019s \u201cname\u201d field is clearly a facility\/clinic name (not a person name), route it to ORGANIZATION for separate handling and exclude it from this person-level dedupe run.<\/li>\n<\/ul>\n
This keeps your \u201cone person, one record\u201d logic clean and prevents nonsense groups.<\/p>\n
<\/span>Step 2: Group by NPI and choose a survivor record<\/span><\/h3>\nDedupe definition:<\/strong> Dedupe is the process of identifying multiple rows that represent the same real-world provider and retaining one survivor record while preserving an audit trail of the duplicates.<\/p>\nGroup rows where NPI_FOR_KEY matches. For each NPI group:<\/p>\n
\n- Pick one survivor<\/strong> row (best contactability and freshest data).<\/li>\n
- Mark all other rows as duplicates and set duplicate_of<\/strong> to the survivor\u2019s row_id.<\/li>\n
- Set duplicate_reason<\/strong> to \u201cSame NPI after normalization\u201d.<\/li>\n<\/ul>\n
Survivor selection rules (recruiting order):<\/p>\n
\n- Row with the most recently verified direct contact fields (if you track verification date or last_updated).<\/li>\n
- Row with the most complete contact set (email + phone + location).<\/li>\n
- Row with the newest last_updated<\/strong> date.<\/li>\n
- If still tied, keep the row from your most trusted source<\/strong> and log why.<\/li>\n<\/ol>\n
Multiple locations under one NPI (common):<\/strong><\/p>\n\n- Keep one identity<\/strong> (one survivor row_id) and treat locations as attributes.<\/li>\n
- If your system needs multiple location rows, keep them linked to the same survivor row_id (do not create multiple identities).<\/li>\n
- If two rows disagree on specialty, keep one identity and log the conflict; prefer the most recent source for the primary specialty field.<\/li>\n<\/ul>\n
<\/span>Step 3: Fallback keys when NPI is missing or invalid<\/span><\/h3>\nMatch key definition:<\/strong> A match key is the standardized identifier (or composite of fields) used to decide whether two records refer to the same provider.<\/p>\nNot every row will have a valid NPI. Your fallback tiers should be strict and documented:<\/p>\n
\n- Tier 2: state license<\/strong>. Normalize by removing spaces\/punctuation and uppercasing. Group by (license state + license number). Set dedupe_tier to \u201cstate license\u201d.<\/li>\n
- Tier 3: name + city fallback<\/strong>. Use only when NPI and state license are missing. Normalize name and city\/state. Group by (last name + first initial + city + state). Set dedupe_tier to \u201cname+city\u201d.<\/li>\n<\/ul>\n
The trade-off is\u2026 the deeper you go into fallback tiers, the higher the risk of false positives (two different people who look similar). That\u2019s why you keep the duplicate log and keep the rules strict.<\/p>\n
<\/span>Step 4: Build and keep the duplicate log (non-negotiable)<\/span><\/h3>\nDo not delete duplicates. Move them to a separate tab called DUPLICATES_LOG<\/strong> and keep these fields:<\/p>\n
![\"54144\"](\"http:\/\/hc.heartbeat.ai\/wp-content\/webp-express\/webp-images\/uploads\/2026\/02\/how-to-dedupe-a-provider-list-npi-6ca39603.png.webp\")
<\/p>\n