--- id: casos/deduplicacao-de-cadastros area: Data usage: widely used effort: medium grain: 1 row per resolved entity, plus 1 row per source record in the map --- # Deduplication and entity resolution One row per person or company, with every source record linked to it and the reason for each merge, without merging people who only share a phone number. ## Executive summary **For:** Companies whose same customer shows up in more than one system, or several times inside the same CRM. **What it solves** - Each customer is counted once, even with records in the CRM, ERP, billing and spreadsheets. - Different people who share a phone or email stop being merged into one. - Every merge says which key it used and can be reviewed. **Why it matters:** Every per-customer metric inherits the identity error: LTV, churn, overdue payments and cohorts are wrong from the root. Merging too much is as bad as merging too little, because mixing two customers moves one person's debt onto another and sends campaigns to the wrong person. That is why the rule has to be explicit, auditable and backed by a human review queue for doubtful cases. **Business questions it answers** - How many unique customers do we really have across all systems? - Which customers are registered more than once in the CRM? - Is this new lead already a customer, an owner or a former customer? - How much has this customer bought, adding up all of their records? - How many records have no valid CPF, CNPJ, email or phone? - Which merges are waiting for review? **What you need** - The systems that hold customer records connected to Nekt - At least one strong key in part of the base: CPF (individual tax ID), CNPJ (company tax ID), email, or one system's id stored in another - An owner to review doubtful cases **What you get** - A table of unique entities with the best value for each field - The map from each source record to its entity, with method and confidence - A queue of doubtful pairs for human review - An AI agent that finds the customer by any document, email or phone ## Detailed ### The problem The same customer shows up with a different name, phone and document in each system, and several times inside the same CRM. In one real base, not a single customer on the accounts receivable list matched a CRM contact by name. Matching by name finds nothing, and matching only by phone or email merges relatives, coworkers and the front desk number into one customer. The result is LTV, overdue payments and campaigns computed on the wrong identities. ### Business rules 1. **Normalize before comparing.** CPF and CNPJ digits only, left-padded with zeros to 11 (CPF) or 14 (CNPJ): a document read as a number loses its leading zero and does not match. Email lowercased, trimmed and validated by format. Phone digits only, dropping the 55 country code when 12 or 13 digits remain, keeping 10 or 11. Name uppercased, without accents and without company suffixes (ltda, me, epp, eireli, s/a). (Avoid: Comparing fields as they arrived: CPF with dots, phone with and without 55, email with capitals.) 2. **Junk becomes null, never a key.** CPF with all identical digits (00000000000, 11111111111), an invalid check digit or scientific notation, empty phones, '0' or '55', and filler emails (naotem@, sememail@) become null. Validate the CPF and CNPJ check digits. A placeholder used as a key merges hundreds of people into one customer. (Avoid: Using the 000.000.000-00 CPF the ERP stores by default as a match key.) 3. **A shared identifier is not an identity.** The front desk phone, the finance email, a broker's or an internal salesperson's email show up on many records. Keep a blocklist of internal addresses and apply a frequency cap: a phone or email present on more than 5 distinct records stops being a key. A name that appears more than 20 times is not used to group candidates either. (Avoid: Merging every record that carries the company's finance@ email.) 4. **Cascade matching, from the strongest key to the weakest.** 1) One system's id stored in another (a custom field with the ERP or old CRM id). 2) A valid CPF or CNPJ. 3) Email. 4) Phone. Collect the matches from every rule and keep, for each record, the highest-priority rule. Store on each link which rule matched. (Avoid: Joining on whichever key matches, with no order of confidence.) 5. **Phone and email suggest, the name validates, the document confirms.** Matching email and phone are used to find candidates and to corroborate. The name decides: if the first name is very different, the pair is not the same person, even with identical email and phone, because relatives and coworkers share contacts. In one real base, phone alone produced dozens of times more pairs than name plus phone. Only an identical CPF or CNPJ confirms without looking at the name. (Avoid: Same phone, same person.) 6. **A key only links when it points to one entity.** When linking an event, ticket, message or sale to a record by phone or email, use only the keys that point to a single record. A key that points to two or more stays unlinked and goes to the queue, instead of picking one at random. (Avoid: Taking the highest id when the phone matches several contacts.) 7. **The most complete record wins, field by field.** Pick the main record by completeness (how many of CPF, email, phone, name and birth date are filled), breaking ties by the most recent modification date and then by id. Empty fields on the main record are filled from the secondary ones. The tie-break must be deterministic, or each run picks a different winner. (Avoid: Keeping any row from the group.) 8. **A role is a link, not an identity.** The same person can be a lead, customer, owner, tenant, guarantor, investor or partner. Create one entity per person and one link row per role, instead of one record per role. When a lead and a customer coexist, the customer prevails. (Avoid: One owner record and another buyer record for the same person.) 9. **Company and economic group are different grains.** For companies, the entity is the full 14-digit CNPJ. The CNPJ root (first 8 digits) groups headquarters and branches: store it as a group column, never as a merge key. An 11-digit document is an individual; markers in the name (ltda, me, s/a) help separate individuals from companies when the document is missing. (Avoid: Merging headquarters and branches by the CNPJ root.) 10. **Merges live in a map, and doubtful ones go to review.** Keep a map from each source id to the entity, updated by upsert, with method, confidence and date. Low-confidence pairs (name plus phone, no document) go to a human review queue, and manual decisions live in a table with who decided and why. Merges already made at the source, such as HubSpot's hs_merged_object_ids, go into the map so old ids still find the entity. (Avoid: Rebuilding the groups from scratch every run and losing manual decisions.) ### What each key can do From strongest to weakest. Store on each link which one was used. - **Recorded manual decision** → Beats all others (`dated table, with who decided and why`) - **One system's id stored in another** → Confirms (`custom field with the source id`) - **Valid CPF or CNPJ** → Confirms (`digits only, lpad 11 or 14, check digit ok`) - **Normalized email** → Suggests, the name validates (`lower, trim, valid format, not on the blocklist`) - **Normalized phone** → Suggests, the name validates (`digits only, no 55, 10 or 11 digits`) - **Name plus birth date, mother's name or postal code** → Candidate for a probabilistic model - **Name alone** → Never confirms - **CNPJ root** → Economic group, not entity (`first 8 digits`) ### Definitions | Metric | Definition | |---|---| | Entity (golden record) | resolved person (CPF) or company (full CNPJ), 1 row, with its own id that does not change when a source record changes | | Source record | each customer, contact, lead or person row in a system, identified by system + id | | Link method | rule that tied the record to the entity: stored id, document, email, phone, model or manual decision | | Duplication rate | (source records - entities) / source records, per system and overall, on the run date | | Strong key coverage | entities with a valid CPF or CNPJ or with an id stored in another system / total entities | | Ambiguous key | normalized email or phone present on 2 or more distinct entities; does not link events or sales | | Review queue | pairs of records with a weak signal (name plus phone, no shared document) waiting for a human decision | | Economic group | company entities sharing the same CNPJ root (8 digits); for individuals, the group is the entity itself | ### Pitfalls - **Contaminated contacts.** An ERP spreadsheet kept a single company record for several people. The first round of contact creation in the CRM wrote one person's phone, email and CPF onto another, and more than a hundred contacts had to be fixed. How to avoid: Separate person from company before creating or merging, and never inherit contact data from a company record. - **Doubled load.** The source load wrote every record twice, with identical content. Every count taken straight from the raw table came out doubled. How to avoid: Remove exact duplicates on the way in, before any match, and compare rows with distinct ids. - **Deleted rows still matching.** ERP tables kept rows flagged as deleted (deleted, or D_E_L_E_T_ in TOTVS). They matched live records and duplicated customers and receivables. How to avoid: Filter out deleted records before matching. - **One person, four ids.** With no CPF or email in common, the same customer was spread across four records, one as a customer in one system and three as leads in another CRM, all with the same phone. How to avoid: Use name plus phone as a signal for human review, not as an automatic merge. - **Transitive chains.** A matched B by phone, B matched C by email, and A and C, who had nothing in common, became the same person. How to avoid: In the probabilistic model, generate pairs with a permissive threshold and form groups with a strict one (for example, 0.85 and 0.95). - **A winner that changes every run.** Two records tied on the selection criteria were resolved with no tie-break, and the main record changed from one run to the next. How to avoid: A fixed tie-break by modification date and, last, by id. Validated on real data: In Nekt's own HubSpot, no email repeats across contacts, because HubSpot already prevents it, but about 1 in 15 phone numbers appears on more than one contact. In about 3 out of 4 of those groups the first name is the same, a sign of a duplicate with a different email. In the rest the names differ, and merging by phone alone would combine different people. ### Acceptance test - [ ] Each source record (system + id) appears in exactly one entity in the map. - [ ] No entity has two distinct CPFs or two distinct full CNPJs. - [ ] No merge used a placeholder (CPF with identical digits, filler email, short phone) or a key above the frequency cap. - [ ] Purchases or revenue summed by entity equal the sum by source record, with no difference. - [ ] Running the process twice on the same data produces the same entities and the same main records. ### Context document ```text Identity of people and companies - Customer = entity in the identity table (1 row per person or company). Count and sum by it, never by the source record. - To find a customer, normalize the search: CPF and CNPJ digits only (11 and 14 positions), email lowercased, phone digits only and without 55. - Link confidence order: manual decision > id stored in another system > CPF or CNPJ > email > phone. A name alone never confirms. - A phone or email shared by several entities is an ambiguous key: do not link events or sales through it. - Companies: the entity is the full CNPJ. The CNPJ root is the economic group, in a separate column. - A person can have several roles (lead, customer, owner, tenant, guarantor, partner). When lead and customer coexist, customer wins. - Internal accounts, test accounts and the company's own emails are not customers. - If an answer relies on a link by phone or email, say so. If the record is not in the map, say it was not resolved instead of guessing. Client exceptions: - : internal emails and phones on the blocklist: . - : CRM field stores the ERP id and is the strongest key. - : frequency cap of records per phone or email. ``` ### Variations - **Probabilistic:** Without documents, a Fellegi-Sunter model (for example, with Splink) groups on clean keys and respects a negative rule: no group can hold two distinct CPFs. - **Properties and products:** The same logic finds properties listed twice across agencies by normalized address: street number without leading zeros and unit number extracted from the address complement. - **Enrich from the tax authority:** For companies, the public CNPJ registry from Receita Federal confirms the document and fills in legal name, activity code (CNAE) and registration status.