One person, three records: how duplicates break lead scoring

Propensity models often fail for a reason that has nothing to do with the algorithm: one person shows up in your data as three different records, and the model treats them as three different people.

Picture a prospect who reads a blog post on their phone, downloads a guide from a laptop a week later, and then talks to sales using a work email. Your web analytics sees anonymous visitors, one for each device. Your form tool sees an email address. Your CRM sees a contact. Unless something deliberately connects them, your warehouse holds three records with three partial histories.

‍

Diagram of one prospect whose phone visit, laptop guide download, and sales conversation each create a separate record in the warehouse.

‍

A model trained on that data sees three lukewarm leads where there is one highly engaged person, and the sales conversation that closed the deal is not connected to the content that earned it.

The fix is identity resolution, done once, in the warehouse.

Start with a deterministic person ID

On a recent donor-marketing platform for a nonprofit client, we weighed letting a customer data platform resolve identity, building a custom real-time identity API, or computing a deterministic ID from email and resolving it in Snowflake. We chose the last: the client wanted to own its data layer, and the team inheriting it was small. We still use a customer data platform for collection and activation, and its identity graph fills a few gaps in our client-ID mapping. What we kept out of it was the definition of a person.

The ID is derived, not assigned. Normalize the email (trim whitespace, lowercase) and run it through a UUIDv5, a name-based UUID, with a fixed namespace. The same email always produces the same ID in any system, with no lookup table, so every source resolves to one key and rebuilds never change an ID.

On this project the same function runs in the website, the form handlers, and the warehouse, checked against a pinned test vector. Add messy inputs to it: Snowflake's TRIM removes only plain spaces by default, while JavaScript's trim() also strips tabs and non-breaking spaces, and in our test a trailing tab produced a different ID.

How do you stitch anonymous sessions to a known person?

Most early behavior is anonymous. Key it on the analytics client ID (for example, the GA4 client_id for a browser), then connect that ID to a person when they identify themselves: a form submission carries both the email and the client ID. Every earlier session from that client ID can then be attributed to the person retroactively. That is a join in the model, not a backfill, because the raw events were already stored under the client ID. The condition is that those events are in your warehouse: on our project the GA4 export to BigQuery only captures days after it was turned on.

One rule we hold to: the client ID is a stitching key, never the person ID. A person can collect several client IDs while their own ID never changes.

Where identity lives in the dbt layers

In a medallion-style dbt project (bronze, silver, and gold, roughly staging, intermediate, and marts), compute the person ID once, in silver, and let everything downstream read it:

‍

Diagram of bronze, silver, and gold data layers, with the person ID computed once in the silver layer and read by the gold layer.

‍

A simplified identity map follows. Snowflake's UUID_STRING returns an RFC-compliant version 5 UUID from a namespace and a name; on our project a small dbt macro computes the same value.

‍

-- models/intermediate/int_identity__client_map.sql (simplified)
with submissions as (    
  select        
    lower(trim(email))             as email_norm,        
    nullif(trim(ga_client_id), '') as ga_client_id,        
    submitted_at    
  from {{ ref('stg_forms__submissions') }}
)

select    
  uuid_string('6ba7b810-9dad-11d1-80b4-00c04fd430c8', email_norm) as person_id,    
  ga_client_id,    
  submitted_at as last_identified_at
from submissions
where email_norm like '%_@_%._%'   -- drops blank and placeholder emails
 and ga_client_id is not null
-- one person per browser: the most recent identification wins
qualify row_number() over (    
  partition by ga_client_id    
  order by submitted_at desc, email_norm
) = 1

‍

Two guards matter. Placeholder emails collapse many people into one ID: about 2% of identify calls on our project carried the literal text "undefined", which never reached the session join but still created a phantom person. A not-null check misses that; a pattern check catches it. And a browser shared by two people would duplicate every session, so one identification has to win. Ours takes the most recent, ranks test identifications last, and flags ambiguous browsers instead of hiding them.

Join sessions to this map at read time and an old anonymous session picks up the person on the first run after they fill out a form. If your model is incremental, also reprocess newly mapped client IDs.

What do duplicate records do to a propensity model?

Identity problems don't announce themselves: the model trains, the metrics look reasonable, and no amount of tuning fixes it.

  1. Over-optimistic validation. When one person exists as several records, those records can land on both sides of a train/test split, and the model is graded on people it has already seen. Split by person ID, not by row.
  2. Broken labels. If the conversion sits on the CRM record and the behavior on an anonymous web record, the converter's history looks like it belongs to someone who never bought.
  3. Incomplete features. "Visited pricing three times in 14 days" only works if all of a person's activity is counted together.

The honest limits

  • Consent-denied visits stay anonymous. There is no client ID to stitch, and there shouldn't be. Treat "no web data" as its own state, not as "low engagement."
  • Client IDs expire. Safari deletes JavaScript-created cookies after seven days without interaction, and after 24 hours for some link-decorated landings.
  • Cross-domain journeys need configuration. Otherwise the same person gets a new anonymous identity at each hop. On our project, landing pages on a separate platform need the same analytics tag and settings, or their earlier browsing isn't stitched.
  • Email is the anchor. Where people share inboxes or change addresses, add a second identifier or an alias table. SMS-only contacts have no ID until an email arrives, and linking their earlier records takes its own matching step.
  • A derived ID is still personal data. A UUIDv5 is unkeyed and SHA-1-based, so anyone with the email can recompute it, and the UK ICO advises against unsalted hashes and SHA-1 for pseudonymisation. Treat it like the email, and use a keyed token outside your walls.

Where to start

Do this before downstream tools start minting their own IDs: on our project, shipping one launch early would have meant overwriting email-platform user IDs once the spine was live. 

A first pass:

  1. Pick one normalization rule for email, apply it everywhere, and compute the person ID in silver and nowhere else.
  2. Capture the client ID on every form submission, and join sessions to the identity map at read time.
  3. Track two rates: the stitch rate (newly identified people who arrive with a client ID) daily, and the session resolution rate (sessions with a client ID that resolve to a person) weekly by channel and browser. A slow decline usually means a page shipped without the analytics script. Expect both to start low.

Get this layer right, and every model, audience, and report starts from a truer picture of your audience.

‍