Core, policies, CRM, brokerage reports and spreadsheets
Normalized tax ID, active status per system and owner valid on the date
One person, their contracts and objects, active or not
AI agent, lists, base count and files for partners
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.
Business questions it answers
Ask your agent these questions once the case is live.
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
Where to activate
Answers how many active customers exist and whether a tax ID is already a customer, with the active rule and the source of each contract.
Active customers with license plate, product and due day for collections, roadside assistance and partner files, one row per person and object.
Active base by region, consultant and product, with activations of the month and tax ID coverage per source.
Normalization is mechanical. The work is agreeing with the business on what an active customer is in each system and keeping the owner history. The detailed view has the business rules, the pitfalls and the acceptance test. Or send everything straight to your agent with the Nekt MCP.
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.
How it is usually done, and the rule
Each row is a mistake that already happened in practice.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Matching keys and how to normalize them
From strongest to weakest. An invalid key becomes null and links no records.
Definitions
Pitfalls that already happened
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.
Read tax IDs as text and pad with zeros to 11 or 14 digits.
The policy number came as a float, "8539905.0", and stripping non-digits produced 85399050, which matched nothing.
Remove the trailing ".0" before stripping non-digits.
Adding the protection core, the policy system and the CRM pipeline counted the same person once in each system.
Stack with a source column and consolidate by tax ID before counting.
Campaign leads matched by phone to contracts from years earlier showed up as conversions of the month.
Compare the contract date with the lead creation date and separate returning customers.
Inflows from previous months were credited to the account's current advisor after a book transfer.
Join on the book's validity history on the inflow date.
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.
Test the rule on the engine where it will run before publishing the table.
Acceptance test
The case is done when every item passes.
- 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.
Agent package
Paste into Claude, ChatGPT or any client with the Nekt MCP connected.
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.
Build the "Single base by CPF and CNPJ (tax IDs)" case in my Nekt workspace following the rules at https://academy.nekt.com/en/industrias/financeiros/base-unica-cpf-cnpj.md. 1) Find the source tables and confirm the columns the rules use. 2) Create a transformation at the stated grain applying every rule. 3) Run the acceptance test and show me the result of each item. 4) Create the case's context document in the workspace, leaving my clients' exceptions open for me to fill in.
Variations
The person is the CPF and the object is the account at each institution. The advisor comes from the brokerage history by event date.
Two levels: company (CNPJ) and employee (CPF), with the CRM contact linked to the company by the CNPJ root.
The planner's customers and leads in a single base, with balance per institution and a daily history per customer, account and date.