Single customer across billing, CRM, product and support

Source

Billing customers, CRM companies, product accounts and tickets

Rule

Key cascade, from strongest to weakest

Outcome

One row per customer with each system's id

Activation

MRR, health score, AI agent and CRM

What it solves

Revenue, usage, tickets and CRM deals all join on the same key.
Every link records how it was made, so you can trust it or audit it.
The same customer stops being counted twice, and two customers stop collapsing into one.

Business questions it answers

Ask your agent these questions once the case is live.

What are customer X's MRR, usage and open tickets?

Which paying customer did this won deal become?

Which customers pay in billing but don't exist in the CRM?

Which companies have more than one account paying separately?

How many of this month's tickets can't we tie to any customer?

What you need

Billing, CRM and product database connected to Nekt
A field that stores one system's id inside another, or a filled-in CNPJ (company tax ID)
An owner for manual link corrections

What you get

A table of canonical customers with each system's id
The method and quality column for each link
Quality queues: payers with no CRM record, CRM records with no payer, CNPJ conflicts
An AI agent that finds the customer by any id

Where to activate

Foundation for every case

MRR, health score, commissions and support by customer all read the same identity table.

AI agent via MCP

Finds the customer by any id, CNPJ or email and answers with revenue, usage and tickets together.

Quality queues

A weekly list of payers with no CRM record, won deals with no payer and document conflicts for the owner to fix at the source.

Ready to build?

The rule is simple. The work is finding the stable key between systems and keeping manual corrections up to date. The detailed view has the business rules, the pitfalls and the acceptance test. Or send everything straight to your agent with the Nekt MCP.

See detailed view

The problem

Each system identifies the customer its own way: billing by customer, the CRM by company and deal, the product by workspace and support by email. Joining by name or email domain matches different companies that use gmail and splits the same company that has two CNPJs. When the map is overwritten instead of updated, different customers end up merged and one's revenue shows up under the other.

How it is usually done, and the rule

Each row is a mistake that already happened in practice.

The common wayThe rule

Joining tables directly on company name in every query.

One canonical customer, one column per system

Create your own customer key and one bridge per system (billing customer, CRM company, product workspace, support organization). The canonical customer is the paying economic account, not the user or the subscription. Store on each row how the link was made (linked_via or match_method) and a confidence level.

Using name or domain even when a stored id exists.

Key cascade, from strongest to weakest

1) One system's id stored in another (subscription id on the deal, customer id on the product account, product id in a support custom attribute). 2) CNPJ, digits only. 3) Corporate email domain. 4) Normalized name, only as a last resort and flagged as weak. The first rule that matches wins.

Comparing the CNPJ as it arrived, with dots, slash or missing the leading zero.

CNPJ normalized to 14 digits

Strip everything that isn't a digit and left-pad with zeros to 14 digits. A CNPJ stored as a number loses its leading zero and won't match. A document with 11 digits or fewer is a CPF (individual tax ID).

Grouping every customer with @gmail.com as one company.

A personal email never becomes a company key

Discard gmail, hotmail, outlook, yahoo, icloud, uol and similar before matching by domain. For those, the key is the full email. Normalize the email to lowercase with no spaces; for Gmail, ignore dots and the +alias suffix.

Matching on a similar name and treating it as certain.

A name never confirms a link

A name is useful to suggest a link, not to confirm it. For fuzzy matching, normalize (no accents, no ltda, me, epp, eireli, sa) and require at least 5 characters and 2 words. When a stable identifier exists, it beats the name.

Revenue by sub-account and qualification by company, without saying which is which.

Declare account and sub-account

One company can have several paying sub-accounts (several subscriptions, several CNPJs in the group). State in each metric which grain it uses. Summing sub-accounts can change the customer's tier and who in CS serves them. In a group with several CNPJs, the primary one is the one with the highest MRR, so the identity doesn't change without reason.

Reading customer status from the CRM.

When systems disagree, one wins

Define status precedence. In general, billing or the product wins over the CRM, which goes stale: in one real base, there were three times more companies marked as churned in the CRM than inactive workspaces in the product.

Appending rows to the map on every run.

The map is updated, never overwritten

The bridge must have one canonical id per CNPJ and per external id, keeping the oldest link. A bridge written by append instead of upsert created conflicting rows and merged different customers, moving one's revenue onto the other. Store the bridge with valid from and valid to when the link changes.

Special cases hand-written inside the SQL.

Manual corrections in a table, with a reason

Links that no rule resolves go into an overrides table with the id, the date, who confirmed it and the reason, applied before the cascade. Whenever possible, also fix it at the source (the CNPJ or the id in the CRM).

Leaving the integration user linked to several accounts.

Remove internal, test and service users

Test accounts, demo accounts and the company's own emails leave the map. In the user-to-account map, service users and integrations must not link one account to another. In product events, resolve the account in cascade: account id on the event, then workspace to account, then user to account.

Link keys in order of confidence

The first one that matches wins. Record which one in the method column.

CRM deal with the subscription idStrong: stored iddeal.id_assinatura = subscription.id

Product account with the customer idStrong: stored idconta.billing_customer_id = customer.id

Support with the product id in an attributeStrong: stored idcustom_attributes.<product_id>

CNPJ in two systemsStrong: documentlpad(digits only, 14)

Corporate email domainMedium: domainnot in the personal email list

Personal emailMedium: full emaillower, trim, Gmail without dots and +alias

Normalized nameWeak: suggests onlyno accents or corporate suffix

Manual overrideBeats alldated table with reason

Definitions

Canonical customer

paying economic account, 1 row per customer, with its own key that doesn't change when an external id changes

Sub-account

each billing customer or subscription that belongs to the canonical customer

Link method

the cascade rule that tied the external id to the customer: stored id, CNPJ, domain, email, name or override

Link coverage

MRR of paying customers with a strong CRM link / total MRR, on the last day of the month

Payer with no CRM record

customer with MRR > 0 in billing and no linked company or deal

CRM record with no payer

company or won deal with no linked paying customer, excluding deals still in implementation

Document conflict

canonical customer with 2 or more distinct CNPJs or CPFs that don't belong to the same declared economic group

Pitfalls that already happened

False merge from append

The identity map was written by append for weeks, picked up two ids for the same CNPJ and started merging different customers in the revenue report.

Write the bridge by upsert, with one canonical id per CNPJ and the oldest link winning.

Two truths about the same company

Lead qualification was measured by company and revenue by sub-account. Companies with several sub-accounts changed tier when their siblings were summed.

Create the company account as an additional truth and state in each metric which grain it uses.

The CRM said churned, the product said active

The CRM had far more companies marked as lost than workspaces that were actually inactive, and churn reports diverged.

Decide that billing or the product wins, and correct the CRM from them.

Deals count the company several times

A customer with three products had three won deals and showed up as three customers in the sales report.

Link the deal to the subscription and count customers by customer id, not by deal.

Ticket with no company

Tickets arrived with only an email or phone number and were left out of the health score.

Link by the contact's email, then by corporate domain, and measure the share of tickets with no customer.

Validated on real data. At Nekt itself, about 1 in 10 Stripe customers uses a personal email (gmail, hotmail), which rules out matching by domain, and about 1 in 8 shares its email with another customer. In HubSpot, half the companies have no domain filled in and some domains repeat across companies. The bridge that works is the customer id stored on the product's organization, not the name or the domain.

Acceptance test

The case is done when every item passes.

Each external id (customer, company, workspace) appears in at most one canonical customer.
No canonical customer has two CNPJs that aren't declared as the same economic group.
MRR summed across canonical customers equals total billing MRR on the same day.
At least 95% of MRR has a strong link (stored id or CNPJ) to the CRM, and a list of the rest exists with the reason.
No canonical customer is formed by a personal email domain alone.

Agent package

Paste into Claude, ChatGPT or any client with the Nekt MCP connected.

Full Markdown

Customer identity
- Customer = the paying economic account. Use the canonical key from the identity table, never the company name.
- To link systems, use the identity table. The order of confidence is: id stored in another system > CNPJ (company tax ID, digits only, 14 positions) > corporate email domain > normalized name.
- A personal email (gmail, hotmail, outlook, yahoo, icloud, uol) is never a company key.
- A name-based link is weak: say so when the answer depends on one.
- Account = the company. Sub-account = each paying customer or subscription. Say which grain you are using.
- Customer status comes from billing or the product, not from the CRM.
- Internal, test and demo accounts and service users are not customers.
- If an id isn't in the identity table, say the customer hasn't been linked instead of guessing.
Per-client exceptions:
- <client>: economic group with CNPJs <list>, consolidated under CNPJ <primary>.
- <client>: manual link between account <product id> and subscription <billing id>, confirmed on <date>.

Build the "Single customer across billing, CRM, product and support" case in my Nekt workspace following the rules at https://academy.nekt.com/en/industrias/saas/cliente-unico-billing-crm-produto.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

Write back to the CRM

Store the canonical customer id and MRR on the CRM company so sales and CS work with the same key.

Probabilistic matching

For contacts with no id, a deduplication model with a negative rule: no group can have two distinct CPFs.

B2C and self-service

With no CNPJ, the key is the canonical email. For several customers with the same email, sum revenue per person.