Our customer data lives in five systems. How do we get one reliable customer view?

The short answer

A single customer view is one reliable record of each customer, assembled from every system that holds part of the picture. Most teams treat it as a reporting task. Build a customer 360 dashboard, the thinking goes, and the problem is solved. It is not. A single customer view is an identity problem first, and a reporting problem last.

Each system mints its own customer identifier. Your CRM (the sales system) knows a customer one way. Your billing platform knows the same customer another way. Your product database, your finance ledger and your support desk each hold a third, fourth and fifth identifier. None of them agree, because none of them were built to.

Before any dashboard can be trusted, you have to decide which records across these systems describe the same real customer. Then you map them to one canonical customer ID: the single identifier that everything else points to. This work is called identity resolution. Do it well and the dashboard is straightforward. Skip it and the dashboard is confidently wrong. A 360 view rendered on top of unresolved identities does not fix the identities; it hides them behind a clean chart.

What this usually looks like

On paper you have five systems. In practice you have five different customers who happen to be the same company.

Suppose a company, Northwind Ltd, buys from you. Sales created the account as “Northwind Limited”. Billing set it up as “Northwind Ltd” under a different registered address. The product system knows them only by the email domain of the first user who signed up. Finance carries them under a debtor code inherited from the old accounting system. Support has two tickets filed under two slightly different names. Every one of these is locally correct. Together they are a mess.

When someone asks a reasonable question — how much is Northwind worth, are they growing, are they at risk of leaving — the answer depends on which system you start from. So you try to join the systems together. The obvious keys are email address and company name, so those are what people reach for. The result is worse, not better.

Names are not unique, and they change. Two real companies can share a name. One company can be written five ways. Email addresses belong to people, not companies, and people move on. Joining on either key produces two failures at once. It merges customers who are not the same, and it splits customers who are. This is the same root cause behind why finance and sales numbers disagree: identity, grain and definitions, not the chart itself.

What the result tells you

The exception rate is the headline number. It is the share of customers that did not resolve cleanly to a single identity across your systems.

A low rate means your systems already share enough reliable identifiers. Most records line up on a shared key, and only a handful need a human to look at them. In that case a single customer view is mostly a modelling exercise, and it is well worth doing.

A high rate means the problem is upstream, in the systems themselves. They were never designed to reference each other. No amount of dashboard work will close that gap, because the gap is in the data, not the display.

Match quality matters as much as the count. A clean match on a shared, stable key (a company registration number, or an account ID that one system passes to another) is trustworthy. A match on a name, or on a similarity score, is a guess with a probability attached. Two teams can report a 90% match rate and still be standing on almost entirely guesses. So count the matches, but grade them too. A worksheet that separates “matched on a shared key” from “matched on a name” tells you far more than a single percentage.

What is happening underneath

The worksheet is a manual version of what a real system does continuously. Here are the parts, in plain English first and then precisely.

Deterministic matching is exact, rule-based matching on a shared key. If the CRM account and the billing customer carry the same company registration number, they are the same customer. There is no judgement involved. Deterministic matching is fast, explainable and safe. Its only limit is that it needs a shared key to exist.

Probabilistic matching, also called fuzzy matching, is what you use when no shared key exists. It scores how similar two records are (on name, address, domain and other fields) and treats a high enough score as a match. It is useful, and it is dangerous. A high score is a probability, not a fact. The characteristic failure is the false merge: two genuinely different customers scored as one, silently collapsed into a single record. A false merge is hard to spot and expensive to unpick, because once two customers share a canonical ID, every downstream figure blends them. Probabilistic matching should raise candidates for review, not decide silently.

The canonical customer ID is the one identifier that every source record maps to. It does not belong to any source system. It is issued and owned by the model that resolves identity, so that no single operational tool can redefine who a customer is.

Source precedence is the rule for which system wins for which attribute. It is decided attribute by attribute, not once for the whole record. Finance may be the authority for legal entity name and billing address. The CRM may be the authority for account owner and industry. The product system may be the authority for last active date. Precedence is a business decision written down as configuration, not a default left to whichever system loaded last.

Duplicate handling is what you do when one system holds two records for the same customer. You keep both source records (you never delete operational data) and you point both at the same canonical ID, marking one as the survivor for display.

The exception queue is a monitored list of records that did not resolve: no match, an ambiguous match, or a probabilistic match below your confidence threshold. It is not a dumping ground. It is a work list that a named person triages on a schedule, and its size is a health metric in its own right.

History and grain decide what a row means over time. Grain is the level of detail one row represents: one row per customer, or one row per customer per day. If you need to answer “who was this customer merged with last quarter”, the identity map needs history, so that a resolution decision can be reconstructed for a past date rather than only shown as it stands today.

Quality checks run on every refresh: no canonical ID with conflicting legal names, no source key mapped to two canonical IDs, an exception rate within an agreed band. Lineage is the recorded path from a resolved customer back to the exact source rows that formed it, so any figure can be traced and defended. Ongoing ownership is the person accountable for the rules, the thresholds and the queue after launch, because identity resolution is a system to run, not a project to finish.

In practice, the core of the model is a single mapping table: one row per source key, each pointing at a canonical customer ID with a recorded method and confidence. The query below keeps one best row per source key, then routes the rest to review.

-- 1. Pick one row per source key, keeping the highest-confidence match.
WITH ranked AS (
  SELECT
    source_system,
    source_customer_id,
    canonical_customer_id,
    match_method,       -- 'deterministic' or 'probabilistic'
    match_confidence,   -- 0.00 to 1.00
    ROW_NUMBER() OVER (
      PARTITION BY source_system, source_customer_id
      ORDER BY match_confidence DESC
    ) AS rn
  FROM staging.customer_match_candidates
)
SELECT
  source_system,
  source_customer_id,
  canonical_customer_id,
  match_method,
  match_confidence
FROM ranked
WHERE rn = 1
  AND match_confidence >= 0.90;   -- the resolved identity map

-- 2. Everything that did not clear the bar goes to an exception queue.
SELECT
  source_system,
  source_customer_id,
  match_method,
  match_confidence,
  'review' AS status
FROM ranked
WHERE rn = 1
  AND (canonical_customer_id IS NULL OR match_confidence < 0.90);

The first query does one job. A source key (say a CRM account) can throw up several candidate matches: one deterministic, one or two fuzzy. ROW_NUMBER() OVER (PARTITION BY source_system, source_customer_id ORDER BY match_confidence DESC) numbers those candidates per source key, best confidence first. Keeping only rn = 1 leaves exactly one row per source key. The confidence threshold then admits only matches you actually trust. The result is a clean identity map: every source key that cleared the bar, mapped to one canonical customer.

The second query is the honest half. It takes the same best-per-key rows and selects the ones that did not clear the bar (no canonical ID at all, or a confidence below the threshold) and marks them for review. This is the exception queue. It is a deliberate, visible destination for uncertainty. The threshold of 0.90 is illustrative; you set it against your own tolerance for a false merge, and you monitor how many records fall each side of it.

What good looks like

• Every source record maps to one canonical customer ID, and that ID is owned by the identity model, not by any operational system.
• Source precedence is written down, attribute by attribute, so anyone can say why a given name or address won.
• Deterministic rules do the confident work. Probabilistic matches raise candidates for review rather than merging on their own.
• Exceptions go to a queue that a named person triages on a schedule, and the queue size is tracked as a health metric.
• Quality checks run on every refresh, lineage lets any figure be traced back to its source rows, and the customer view is only ever built on top of resolved identities.

Common ways this goes wrong

• Merging on email address or company name. Both are unstable and non-unique, and they split and merge customers at the same time.
• Forcing a 100% match. Real data never resolves completely. Chasing the last few per cent produces false merges that are worse than the honest gap they replace.
• Treating a customer 360 dashboard as the solution. The dashboard is the last mile. It renders whatever identities you feed it, right or wrong.
• Having no exception process. Unmatched records either vanish from the numbers or get forced into a wrong match. Both are silent, and both erode trust.
• Having no owner. Rules, thresholds and the queue drift the moment nobody is accountable for them, and the view quietly rots.

When this becomes a system

A one-off worksheet is the right first step, and for a small company with two systems and a low exception rate it may be enough for a while. It stops being enough when the same identities have to be resolved again and again, correctly, without a person redoing the matching each time. At that point the manual sheet becomes a liability, because every refresh reopens the same questions. This is the shape of our data reconciliation and identity projects: a maintained identity map, documented precedence, and a monitored exception queue, rather than a spreadsheet rebuilt each month.

A decision guide

Match the approach to the keys you actually have. Most working systems use more than one, in order: deterministic first, probabilistic only for what is left, and human review for the residue.