--- id: saas/cliente-unico-billing-crm-produto industry: SaaS and technology usage: widely used effort: medium grain: 1 row per canonical account, plus one bridge per system (1 row per external id) --- # Single customer across billing, CRM, product and support One row per customer with their id in every system and the matching method, so revenue, usage and tickets finally line up. ## Executive summary **For:** SaaS companies whose customer is a customer in billing, a company in the CRM, a workspace in the product and an email in support. **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. **Why it matters:** Without this bridge, no per-customer metric adds up: MRR can't find usage, the ticket can't find the account and the won deal can't find the subscription. A wrong link is worse than none, because it mixes one customer's revenue with another customer's risk without anyone noticing. **Business questions it answers** - 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 ## Detailed ### 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. ### Business rules 1. **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. (Avoid: Joining tables directly on company name in every query.) 2. **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. (Avoid: Using name or domain even when a stored id exists.) 3. **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). (Avoid: Comparing the CNPJ as it arrived, with dots, slash or missing the leading zero.) 4. **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. (Avoid: Grouping every customer with @gmail.com as one company.) 5. **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. (Avoid: Matching on a similar name and treating it as certain.) 6. **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. (Avoid: Revenue by sub-account and qualification by company, without saying which is which.) 7. **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. (Avoid: Reading customer status from the CRM.) 8. **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. (Avoid: Appending rows to the map on every run.) 9. **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). (Avoid: Special cases hand-written inside the SQL.) 10. **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. (Avoid: Leaving the integration user linked to several accounts.) ### Link keys in order of confidence The first one that matches wins. Record which one in the method column. - **CRM deal with the subscription id** → Strong: stored id (`deal.id_assinatura = subscription.id`) - **Product account with the customer id** → Strong: stored id (`conta.billing_customer_id = customer.id`) - **Support with the product id in an attribute** → Strong: stored id (`custom_attributes.`) - **CNPJ in two systems** → Strong: document (`lpad(digits only, 14)`) - **Corporate email domain** → Medium: domain (`not in the personal email list`) - **Personal email** → Medium: full email (`lower, trim, Gmail without dots and +alias`) - **Normalized name** → Weak: suggests only (`no accents or corporate suffix`) - **Manual override** → Beats all (`dated table with reason`) ### Definitions | Metric | Definition | |---|---| | 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 - **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. How to avoid: 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. How to avoid: 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. How to avoid: 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. How to avoid: 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. How to avoid: 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 - [ ] 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. ### Context document ```text 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: - : economic group with CNPJs , consolidated under CNPJ . - : manual link between account and subscription , confirmed on . ``` ### 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.