Revenue attribution as a reconciliation problem

Ask most companies which first contact led to a paying customer and you get three answers. The web analytics tool counts sessions and form fills. The CRM counts leads and deals. Billing counts accounts and invoices. Each number is correct inside its own system, and none of them can be joined to the others, because the systems were never designed to share a customer key.

That is why we treat revenue attribution as a reconciliation problem before it is a modeling problem. If you cannot say which billing account belongs to which first contact, and by what evidence, no attribution model on top will give you a number you can defend.

Why the systems disagree

The same customer shows up differently in every system:

The web form captures an email address and, if you collect it, a click ID from the URL.
The CRM record is created by a salesperson, who may type a different email or a phone number in a different format.
The billing account is opened under a company name, often with a finance contact who never filled in the form.
One company can have several contacts, and one contact can move between companies.

None of this is a data quality failure in a single system. Each system records what it needs. The gap only appears when you try to join them.

Start with the record, not the model

Write the question down as a join. For every paying account in billing, which first-contact record does it link to, through which records in between, and which identifier made each link? An answer in that form can be checked by someone else. An answer like "paid search drove 34% of revenue" cannot, unless it is built on top of those links.

We keep three kinds of tables: the source records as they arrived, the identifiers extracted from them, and the links between records. A first-contact table looks like this:

CREATE TABLE first_contacts (
  id            UUID PRIMARY KEY,
  captured_at   TIMESTAMPTZ NOT NULL,
  source_system TEXT NOT NULL,       -- web form, call tracking, chat
  click_id      TEXT,                -- from the landing URL, if present
  email_norm    TEXT,                -- lower-cased, trimmed
  phone_e164    TEXT,                -- normalized to E.164
  landing_page  TEXT,
  raw           JSONB NOT NULL       -- the record exactly as received
);

CREATE TABLE record_links (
  left_id   UUID NOT NULL,
  right_id  UUID NOT NULL,
  rule      TEXT NOT NULL,           -- which rule made the link
  evidence  JSONB NOT NULL,          -- the identifier values that matched
  linked_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (left_id, right_id)
);

Keeping the raw record and the evidence for every link means any link can be explained later, and a wrong link can be removed without rebuilding everything else.

Match on hard identifiers first

Identity resolution works best as an ordered list of rules, strongest first. A record that matches on a strong rule is not offered to a weaker one.

Click ID. If the click ID from the landing URL is stored in a hidden form field and carried into the CRM, it links the first contact to the CRM record with almost no ambiguity.
Normalized email. Lower-case, trim, and treat known aliases consistently. This links most CRM records to billing contacts.
Normalized phone. Convert every number to E.164 before comparing. Phone numbers typed by hand are the most common source of missed matches.
Everything else goes to review. Matches on name and company, or on a shared email domain, are suggestions for a person to confirm. They are never merged automatically.

type Rule = { name: string; key: (r: SourceRecord) => string | null };

// Strongest first. A record linked by an earlier rule is not offered to a later one.
const RULES: Rule[] = [
  { name: 'click_id', key: (r) => r.clickId ?? null },
  { name: 'email', key: (r) => normalizeEmail(r.email) },
  { name: 'phone', key: (r) => toE164(r.phone, r.country) },
];

function link(left: SourceRecord[], right: SourceRecord[]): Link[] {
  const links: Link[] = [];
  const done = new Set<string>();
  for (const rule of RULES) {
    const index = new Map<string, SourceRecord[]>();
    for (const r of right) {
      const k = rule.key(r);
      if (k) index.set(k, [...(index.get(k) ?? []), r]);
    }
    for (const l of left) {
      if (done.has(l.id)) continue;
      const k = rule.key(l);
      const hits = k ? index.get(k) ?? [] : [];
      if (hits.length === 1) {
        links.push({ leftId: l.id, rightId: hits[0].id, rule: rule.name, evidence: { [rule.name]: k } });
        done.add(l.id);
      }
      // More than one hit is ambiguous. It goes to review instead of picking one.
    }
  }
  return links;
}

Fuzzy matching on names and addresses raises the match rate and lowers the number of matches you can explain. A match rate that looks good is worth less than a smaller set of links you can defend line by line.

Reconcile in both directions

Once the links exist, count what did not link, in both directions:

Billing accounts with no CRM record.
CRM deals marked won with no billing account.
First contacts with no CRM record after a set number of days.

These lists are the work, not noise to hide inside a model. For example, if billing opens 1,000 new accounts in a month and 820 of them link to a first contact through a hard identifier, the other 180 tell you where the process loses the identifier: a form that drops the click ID, a sales team that creates accounts by hand, or a billing system that stores a different contact.

Run the same checks every day once the platform is in place. A new gap then shows up when it starts, not at month end.

Where attribution models fit

First-touch, last-touch, linear and time-decay models are views on the linked data. They decide how to share credit across the contacts on one customer's path. They cannot add links that are missing. Pick the model after the links exist, and report the share of revenue that has no linked first contact next to whatever the model says. If that share is large, it is the most important number on the page.

Common mistakes

Merging on fuzzy matches. Once two customers are merged, the error spreads into every report built on them.
Overwriting records to deduplicate them. Keep the source records and record the link. Overwriting destroys the evidence.
Treating each platform's own conversion count as the truth. Each platform counts the events it can see. Only your billing system knows who paid.
Building the dashboard before the data model. Another dashboard on three disagreeing systems adds a fourth number.

How we do this

This is the job of our Data platform: source pipelines, an identity layer that matches on hard identifiers, reconciliation checks that run on a schedule, and reporting where every number links to the records behind it. We start with a one-week audit that writes down where your systems disagree and by how much. The platform then runs on your real data within 90 days of the audit.