---
id: financeiros/base-unica-cpf-cnpj
industry: Financial services
usage: widely used
effort: medium
grain: Person or company by normalized tax ID; person x contract x object (license plate, chassis number, account, policy) for the detail
---

# Single base by CPF and CNPJ (tax IDs)

Each person or company appears only once, with what they bought in each system, whether they are active today and who the sales owner was on the date of each event.

## Executive summary

**For:** Vehicle protection cooperatives and associations, insurance brokers, investment advisory firms and fintechs with the same customer in several systems.

**What it solves**

- The active base has a single number, the same for leadership, sales and finance.
- Each customer appears once, with everything they bought across all systems and the source of each contract.
- A lead who was already a customer stops counting as a new sale.

**Why it matters:** In insurance, protection and investments the customer is born in the CRM, signs in the core, gets a policy in another system and is served by an advisor who changes over time. Without a clean key and a written rule for an active customer, each team counts the base its own way and the AI agent answers with the number of whoever asked last. It is the foundation for collections, commissions, retention and any list sent to a partner.

**Business questions it answers**

- How many active customers do we have today, by region and by product?
- Which contracts were activated this month?
- Is this CPF already our customer in any system?
- How many of this month's leads became contracts, and how many were already customers?
- Who was the advisor on this account when the inflow happened?
- Which customers have more than one vehicle or more than one active policy?

**What you need**

- The systems where the customer lives connected to Nekt: core, policies, CRM and partner reports
- A written definition of an active customer, per system and per product
- An owner history per account, if the question is about the past

**What you get**

- A table per person with normalized tax ID, source and active status
- The person x contract x object detail, with activation date
- A lead-to-contract link that states which key matched
- An AI agent that counts the base with the active rule already applied

## Detailed

### The problem

The same cooperative member is in the protection system, the policy system, a CRM pipeline and a spreadsheet, and in each one the tax ID was typed differently: with punctuation, without the leading zero, as a number with ".0" at the end. Adding up the bases counts the same person several times. Each team uses its own definition of active, and the lead who was already a customer becomes a conversion in the funnel.

### Business rules

1. **The normalized tax ID is the key.** Strip everything that is not a digit. CPF has 11 digits and CNPJ has 14: pad with leading zeros when the source read the field as a number. If it comes with more than 14 digits (a system prefix), keep the last 14. Remove the ".0" from fields that went through a float before stripping non-digits, otherwise 8539905.0 becomes 85399050. Keep the original value alongside. (Avoid: Comparing the tax ID as it came, with punctuation, or converted to a number.)
2. **Junk becomes null, never a key.** A tax ID with a length other than 11 or 14, or with all digits equal (000..., 111...), becomes null. Phone: digits only, drop the 55 country code only when it has 12 or 13 digits, accept 10 or 11, the rest is null. License plate: uppercase without dash, valid in the old format and the Mercosur format (3 letters, 1 digit, 1 letter or digit, 2 digits). Chassis number: at least 8 characters and never all zeros. (Avoid: Linking records through a CPF 000.000.000-00 or a phone without area code.)
3. **Active is a written rule, per system.** State what counts as active in each source: in the core, a contract with status ATIVO; in the policy system, a policy with status active; for the product that only exists in the CRM, the deal in the Active stage of that pipeline. Then apply the agreed exclusions: anyone on the canceled list and, if the business decides so, anyone delinquent. Write down the decision on delinquent customers, because it changes the count. (Avoid: Counting any existing contract as active, or using one system's rule for the others.)
4. **One row per source, with origin.** Stack the sources in a common format (name, tax ID, object, contract, activation date) with source and source id columns. Count contracts by source + source id, because the detail may have one row per product. Only then consolidate by tax ID. (Avoid: Joining the bases by name and losing where each contract came from.)
5. **Activation is the first confirmed payment.** A contract's activation date is the earliest payment date among its paid invoices. For a policy, the start of coverage. Contract or proposal creation date is not activation: some contracts are created and never paid. (Avoid: Using the contract creation date as the customer's start.)
6. **The object is part of the grain.** In vehicle protection and insurance, a person has several vehicles, equipment units or policies; in investments, several accounts at different institutions. The detail is person x contract x object, and the object can be a vehicle or a piece of equipment, never both. Count people by distinct tax ID and objects by the object key. (Avoid: Counting customers by the number of rows in the unified base.)
7. **Customer wins over lead.** In the base of people and leads, a lead only enters as type Lead when there is no active customer linked to it. If both exist, the customer stays. A person without a linked account remains in the base with a null account. (Avoid: Adding customers and leads and showing the same person twice.)
8. **Lead to contract in a cascade, with dates.** Match the lead to contracts using the strongest key first: tax ID, then license plate or chassis number, then phone, then the CRM contact. Store which key matched. Compare the contract registration date with the lead creation date: a contract older than the lead is a returning customer, not a conversion. (Avoid: Flagging as converted every lead that matches any contract, from any date.)
9. **The owner is the one valid on the date.** Advisor, consultant or broker changes over time. For inflows, sales or commissions, join with the book history where valid-from <= event date and (valid-to is null or valid-to > event date). The current owner only applies to today's lists. (Avoid: Attributing past inflows to today's advisor.)
10. **Deleted rows and old versions go first.** In the core, filter out records with deleted true in person, contract, region and consultant. In the CRM, keep the latest version of each id (latest modification date and, on ties, the most recent sync). In Open Finance data, check whether the event carries the platform user id instead of the customer id and translate it before joining. (Avoid: Joining core tables without filtering deleted rows, or using the first version of the CRM deal.)

### Matching keys and how to normalize them

From strongest to weakest. An invalid key becomes null and links no records.

- **CPF (individual tax ID)** → Individual (`digits only, 11 positions, not all digits equal`)
- **CNPJ (company tax ID)** → Company (`digits only, 14 positions (last 14 if longer)`)
- **Contract, policy, account number** → Product link (`text without ".0", digits only when numeric`)
- **License plate** → Insured vehicle (`uppercase, no dash, ^[A-Z]{3}[0-9][A-Z0-9][0-9]{2}$`)
- **Chassis number** → Vehicle or equipment (`uppercase alphanumeric, 8+ characters, not all zeros`)
- **Phone** → Weak signal, never alone (`digits only, no 55 when it has 12 or 13, accepts 10 or 11`)
- **CRM contact** → Link stored in the CRM (`contact_id not empty and not 0`)
- **Name** → Validates only, never links (`lowercase, no accents, no suffix such as ltda`)

### Definitions

| Metric | Definition |
|---|---|
| Unique person | valid normalized tax ID (11-digit CPF or 14-digit CNPJ); 1 row per tax ID, with the list of sources where it appears |
| Active customer | person with at least one contract active under their source's rule on the reference date, outside the canceled list and, if stated, the delinquent list |
| Activation date | earliest payment date among the contract's paid invoices; for a policy, coverage start |
| Contracts activated in the month | distinct contracts (source + source id) whose activation date falls in the month |
| Active base by region | distinct active people by contract region on the reference date; a person with contracts in two regions counts once in each and once in the total |
| Lead conversion | lead linked to a contract registered on or after the lead creation date, using the strongest key available |
| Returning customer | lead linked to a contract registered before the lead creation date; not part of conversion |
| Tax ID coverage | rows with a valid tax ID / total rows, per source, on the run date |

### Pitfalls

- **The leading zero vanished.** CNPJ and CPF read as numbers from a spreadsheet or CSV lost their leading zeros, and a CNPJ starting with 00 never matched the core. How to avoid: Read tax IDs as text and pad with zeros to 11 or 14 digits.
- **A policy with one extra digit.** The policy number came as a float, "8539905.0", and stripping non-digits produced 85399050, which matched nothing. How to avoid: Remove the trailing ".0" before stripping non-digits.
- **The same member three times.** Adding the protection core, the policy system and the CRM pipeline counted the same person once in each system. How to avoid: Stack with a source column and consolidate by tax ID before counting.
- **A new sale who was already a customer.** Campaign leads matched by phone to contracts from years earlier showed up as conversions of the month. How to avoid: Compare the contract date with the lead creation date and separate returning customers.
- **Inflow credited to the wrong advisor.** Inflows from previous months were credited to the account's current advisor after a book transfer. How to avoid: Join on the book's validity history on the inflow date.
- **Works in the query, breaks in the pipeline.** The query validated in the editor used 1-based array indexes and cardinality; once it became a Spark transformation, which indexes from 0 and uses size, it started picking the wrong item. How to avoid: Test the rule on the engine where it will run before publishing the table.

### Acceptance test

- [ ] No normalized tax ID appears in more than one row of the people table.
- [ ] Every row has a tax ID with 11 or 14 digits, or a null tax ID with a reason; none has all digits equal.
- [ ] The active customer count per source matches the system's own count on the same date (core by status ATIVO, policies active, Active stage of the pipeline).
- [ ] Contracts activated in the month = contracts with their first paid invoice in the month, checked on 5 randomly chosen contracts.
- [ ] Every lead-to-contract link has the key used and a classification of conversion or returning customer.

### Context document

```text
Single base by CPF and CNPJ
- A person is identified by the normalized tax ID: digits only, CPF with 11 and CNPJ with 14, leading zeros preserved. A tax ID with another length or with all digits equal is invalid and links no records.
- To count people, use distinct tax IDs. Never count rows: the detail table has one row per contract, object (license plate, chassis number, policy, account) and sometimes per product.
- Active customer: ATIVO contract in the core, active policy in the policy system, or deal in the Active stage of pipeline <pipeline> in the CRM; and not on the canceled list. Delinquent: <included | not included> in the active base.
- Activation date = first confirmed payment of the contract. For a policy, coverage start.
- Customer wins over lead. A lead is only a conversion if the contract was registered on or after the lead creation date; an earlier contract is a returning customer.
- Always say which key made the link (tax ID, license plate, chassis number, phone, CRM contact). Phone alone is a weak signal.
- Owner (advisor, consultant, broker) in questions about the past: use the one valid on the event date, from the book history. For today's lists, use the current one.
- If the question does not name the product or system, answer with the consolidated total and break it down by source.
Exceptions per client:
- <client>: product <product> exists only in the CRM; active = stage <stage> of pipeline <id>.
- <client>: customers delinquent for more than <N> days leave the active base.
```

### Variations

- **Investments:** The person is the CPF and the object is the account at each institution. The advisor comes from the brokerage history by event date.
- **B2B pensions and benefits:** Two levels: company (CNPJ) and employee (CPF), with the CRM contact linked to the company by the CNPJ root.
- **Financial planning:** The planner's customers and leads in a single base, with balance per institution and a daily history per customer, account and date.