What it takes to build a unified customer view for a client.

A client's ad platforms know that somebody clicked. Their analytics knows what that person did on the site. Their CRM knows who the person turned out to be. Their store or billing system knows what they paid.

None of those four systems shares an identifier with the others by default. That is the entire problem, and it is why a unified customer view is a data modelling exercise rather than a purchase.

Pick the strongest key available at each join rather than one key for everything.

• An order or invoice identifier

  The most reliable key there is, because it is created once by a system the client controls. Where an order ID can carry through to the CRM, that join is exact.

• A normalised email address

  Strong, and the usual bridge between the store and the CRM. Normalise it first: lower case, trimmed, and with the obvious duplicates resolved, or you will have one person as three records.

• A first-party identifier the site sets itself

  A value the client's own site assigns and passes into forms and orders. More work to set up and the only key that survives someone changing their email address.

• Campaign tags

  The join between spend and behaviour rather than between records. Reliable only to the extent that the tagging is consistent, which is why a naming convention is upstream of all of this.

The failure is almost never a join that returns nothing. That gets noticed. The failure is a join that returns too much.

One customer with two records joins to their orders twice and their revenue doubles. A join at the wrong grain, such as a customer table against a daily events table, multiplies every figure by the number of days matched. Both produce a total that is larger than reality and looks plausible.

The check is a count, not an eyeball. After every join, count rows before and after. If a join increased the row count and you did not expect it to, stop.

A unified customer view sounds like it means every field about every person in one enormous table. In practice the useful version is narrow: a customer, the campaign that first brought them, what they have spent, and when they were last active.

Those four facts answer most of the questions that made the client want this in the first place, including the one no single platform can answer, which is what they spent to acquire the customers who actually paid. Build that, use it for a quarter, then add fields you find you are missing rather than the ones you can imagine wanting.

Agree that narrow version with the client before starting. It is a project with an end, and every field about everybody is not.

Each client's ads, analytics, CRM, e-commerce and payments land in one warehouse you own, scoped to that client, which is the precondition for joining them at all. The modelling runs in SQL inside that account, so a join is something you can read and check rather than a setting inside a product.

Row counts and freshness are monitored on a schedule, which is what catches a join that started returning too much before it reaches a report.