---
id: imobiliario/base-unica-clientes
industry: Real estate
usage: common
effort: high
grain: Person (master id) with arrays of source ids, phones and emails; person x role x source for the links
---

# Single customer base

One row per person joining the sales CRM, the old CRM, the rental system and the ERP, with each of their roles (buyer, owner, tenant, guarantor) and the reason for every merge.

## Executive summary

**For:** Brokerages and real estate groups with the same client spread across several CRMs, the rental system and the ERP.

**What it solves**

- The brokerage knows that today's tenant is yesterday's purchase lead and the owner of another property.
- Campaigns go out without sending the same message three times to the same person.
- Nobody merges two people because they share the front desk phone or the broker's email.

**Why it matters:** A full-service brokerage serves the same family as buyer, owner, tenant and guarantor, each role in a different system. Without a single base, cross-sell is guesswork, reactivation fires duplicates and the AI agent does not know who it is talking to. It is the prerequisite for segmentation, repeat business and writing contact data back to the CRM.

**Business questions it answers**

- How many unique clients do we have across all systems?
- Which current tenants were once purchase leads?
- Has this person ever been an owner or investor with us?
- How many contacts are duplicated in the CRM, and between which systems?
- How many clients have a valid phone and email for campaigns?

**What you need**

- Every system with person records connected to Nekt
- The list of internal emails and phones (brokers, front desk, finance) that cannot be used as keys
- Someone on the team to review doubtful matches

**What you get**

- A people table with the best contact details and every source id
- A links table with each role, source and status
- A review queue for doubtful matches
- An AI agent that answers who the person is and their history with the company

## Detailed

### The problem

The same person exists in the sales CRM, the old CRM, the rental system and the ERP, with different ids, phones in four formats and the broker's email in place of theirs. Joining by phone or email without cleaning merges entire families into one person. Not joining counts three clients where there is one and sends the same campaign three times.

### Business rules

1. **Document as digits only, validated.** CPF (individual tax ID) and CNPJ (company tax ID) keep only digits, left-padded with zeros to 11 or 14 (a document read as a number loses the leading zero). Validate the check digit. 11 digits is an individual, 14 is a company; markers in the name (ltda, me, s/a, eireli) confirm a company when the document is missing. An invalid document becomes null, never a key. (Avoid: Matching on the document as it came from the system, with dots and lost zeros.)
2. **Phone in canonical format.** Keep only digits and bring everything to 55 + area code + number, with 13 digits for mobiles. With 12 digits and a number starting with 6 to 9, the ninth digit is missing: insert it. With 10 or 11 digits, the 55 is missing. With no area code, use the area code of the brokerage's market, declared. 14 digits or more becomes null. Each person keeps all valid phones in an array and one main phone, mobile ahead of landline. (Avoid: Comparing phones as they came, with and without 55, with and without the nine.)
3. **Internal and shared emails are not keys.** Email lowercased, trimmed, validated by regular expression. Keep a block list with the brokerage's own emails (brokers, finance, support) and filler emails (naotem@, sememail@). An email used by more than one person in the same system is flagged as shared and cannot be used to merge. As the main email, prefer personal over corporate. (Avoid: Joining by email without a filter and merging all of a broker's clients.)
4. **Cascade matching, strongest key first.** Collect the pairs from every rule at once and keep, for each record, the rule with the lowest number: 1) the id of one system stored in the other (a CRM field holding the client code in Vista or the rental system); 2) valid CPF or CNPJ; 3) email and phone matching together; 4) email only; 5) phone only. Store the rule used and the number of candidates on each row. (Avoid: Joining on whatever key matched, in the order the queries ran.)
5. **The name validates weak matches.** For matches by email or phone, compare the normalized name (no accents, no titles and no 'de', 'da', 'dos') using the higher of sorted-token and token-set similarity. A threshold used in practice: 95 or more is exact, 80 high, 60 medium, 40 low, below that unlikely. Low and unlikely go to review, not to a merge. (Avoid: Merging mother and son who share the family mobile.)
6. **A role is a link, not an identity.** The person is one; buyer, seller, owner, tenant, landlord, guarantor, investor and lead are their links, one per role, source and occurrence. Each role's status summarizes the links by fixed priority: active > open lead > ended > lost lead > unknown. Someone with an active contract and a lost lead in the same role shows as active. (Avoid: One table of owners, another of tenants, the same person in each.)
7. **Individuals and companies never merge on their own.** The same valid CNPJ is the same company, even with different names (a record may carry the partner's name). The same CPF merges only if the name or email also matches, because mistyped CPFs and relatives' CPFs are common. A partner's CPF never merges with the company's CNPJ. (Avoid: Merging on equal CPF without looking at anything else.)
8. **The most complete record supplies the contact.** Pick the main contact by completeness (document, email, phone, name and date of birth filled in), breaking ties by the oldest record and then by id, so the result does not change between runs. In practice, the sales CRM usually has better phone and email than the rental system, so it goes first in the field-by-field choice. Dates of birth before 1930 become null. (Avoid: Keeping the most recently changed record.)
9. **Review decides by pair, and a veto lasts forever.** Doubtful cases go to a queue with the id pair, the reason and the evidence. Approval merges exactly that pair. A pair marked as do-not-merge is never merged again, not even by the automatic rule. Only mark an item as merged after the merge is in the id map. (Avoid: Approving by the item's phone and merging everyone with that phone.)
10. **The merge carries the history.** When two people become one, every event, link and deal stored under the absorbed id is remapped to the surviving id, resolving chains (A became B, B became C). Run the remap right after the merge and check that the row count did not change. (Avoid: Merging the people and leaving the timeline on the old id.)

### Matching order

The lowest-numbered rule wins; matches 3 to 5 go through name validation.

- **Code from one system stored in the other** → 1. Cross id (`regra_match = 1`)
- **Valid CPF or CNPJ, digits only** → 2. Document (`regra_match = 2`)
- **Same email and same phone** → 3. Email and phone (`regra_match = 3`)
- **Unique email, not on the block list** → 4. Email (`regra_match = 4`)
- **Canonical phone, not shared** → 5. Phone (`regra_match = 5`)
- **Name with low or unlikely similarity** → Review queue
- **No rule matched** → New person

### Definitions

| Metric | Definition |
|---|---|
| Person | master id with every source id linked to it; individual by CPF and company by CNPJ when a document exists |
| Link | one row per person x role x source x occurrence (each contract, deal or property), with status and dates |
| Unique clients | distinct count of people with at least one active link, after merging |
| Match rate by rule | records from a source matched by each rule / records in the source, per run |
| Completeness | how many of document, email, phone, name and date of birth are filled and valid on the record, from 0 to 5 |
| Campaign-ready contact | person with a name and a valid email or phone, not blocked and not shared |
| Open review queue | record pairs with a doubtful match still without a decision to merge or keep separate |

### Pitfalls

- **Queue dominated by phones.** Almost the whole review queue was shared phones: front desk, family, a broker who registers their own mobile. How to avoid: Apply a frequency cap to phones and send to review only cases with a conflicting document or name.
- **A phone that looks like a CPF.** 11-digit phones were treated as documents in a merge approval, and one of them matched another person's CPF. How to avoid: An approved merge runs by id pair, never by the key value.
- **A broker's clients merged.** Records with the broker's email in place of the client's became a single person. How to avoid: Block list with internal emails and a shared-email flag.
- **Empty journey after the merge.** The person was unified, but the events stayed under the absorbed id and their history showed up empty. How to avoid: Remap the timeline on every merge.
- **Item closed without a merge.** Approved items were marked as executed even when the merge did not happen, and the queue looked clean. How to avoid: Only mark as executed what entered the id map.
- **Second system with no area code.** Old phones from the rental system came with 8 or 9 digits and matched nothing. How to avoid: Complete with the market's area code, declared, and flag the phone as inferred.

### Acceptance test

- [ ] Every source id points to exactly one person in the id map.
- [ ] No email or phone on the block list appears as a matching key.
- [ ] For a sample of 50 people merged by email or phone, manual review confirms at least 48 as the same person.
- [ ] Running the process twice with no change in the sources produces the same master ids and the same main contact.
- [ ] The count of events and links before and after a merge is the same.

### Context document

```text
Single customer base
- One person = one master id. The links table has one row per person, role, source and occurrence.
- Roles: lead, buyer, seller, owner, landlord, tenant, guarantor, investor. A role is a link, not an identity.
- Status per role by priority: active > open lead > ended > lost lead > unknown.
- To count clients in a role, filter status = active. Never count link rows without filtering status.
- Cascade matching: 1 cross id, 2 CPF/CNPJ, 3 email and phone, 4 email, 5 phone. The regra_match column says which rule linked the record.
- CPF with 11 digits, CNPJ with 14, digits only and validated. Individuals and companies never merge.
- Phone in the format 55 + area code + number. Internal and shared emails are not keys.
- People in the review queue are not merged yet. When answering, say the match is probable, not confirmed.
- Campaign-ready contact: name and a valid email or phone, not blocked and no opt-out.
Client exceptions:
- <client>: default market area code <area code> for phones with no area code.
- <client>: CRM field <field> holds the client code in the rental system and is rule 1.
```

### Variations

- **Group with another line of business:** Builder and car dealership, or developer and property manager: one person per document with quantity, value and date of last purchase in each business.
- **Probabilistic matching:** With no document in common, a record linkage model using first name, last name, email, phone and date of birth suggests pairs, which go through the same review queue.
- **Vacation rentals and fractional ownership:** Guest, owner and investor for the same person, with a guest-specific status (completed booking, cancelled, never booked).