Choosing the right phone number for every supporter

From my résumé Figures from my résumé; examples are made up

A five-stage pipeline in production that reconciles phone records from several systems, decides which numbers may be called or texted, then picks one best number per person, the same way every run.

Status: My job at EDF · no employer data shown
Relevant to: Data engineering, CRM and RevOps

Results · Figures from my résumé; examples are made up

source systems combined into one choice per personFrom my résumé
incorrect vendor-appended numbers caught before outreachFrom my résumé

The problem

Phone numbers came from 7 source systems and vendor appends, and they disagreed: the same number on different people, conflicting line types, missing consent and numbers already flagged invalid. Texting a wrong number or one without consent is a compliance problem, not just a data problem.

My role

I designed the weighted phone-scoring rules and the five-stage framework, and used them to catch incorrect vendor-appended numbers before they reached outreach. The framework is in production.

How it works

1.Resolve
Match each phone record from each source to the person it belongs to.
2.Unify
Normalize every number to E.164 and keep one row per person and number, with every source that reported it.
3.Enrich
Add line type, consent by channel (broadcast and one-to-one texting), suppression and invalid flags, deceased exclusions, and how many people share the number.
4.Select best
Keep only numbers that are eligible for the channel, then score and rank them, with a stable tie-break.
5.Pivot
Return one row per person with the best number for each brand and channel.

What I built

Weighted scoring that combines evidence from 7 source systems: which sources agree, how recent the evidence is and how reliable each source has been.
Eligibility rules applied before scoring: consent by channel, suppression, invalid numbers and deceased exclusions.
Penalties for numbers shared by several people and for conflicting line types.
A deterministic final tie-break on a hash of the E.164 number, so the same inputs always pick the same number.
Selection per brand and channel, since consent to one kind of message isn’t consent to another.

Example

Number	Sources	Texting consent	Score	Result
Mobile A	Sign-up form, texting platform	Yes	High	Selected for texting
Mobile B	Vendor append only	No	Highest	Not eligible, skipped
Landline C	Old CRM record	Not applicable	Low	Kept for calls only

Made-up numbers for one made-up person, showing why eligibility comes before the score.

In code

select person_id, brand, channel, phone_e164
from enriched_phones
where is_eligible            -- consent, suppression, invalid, deceased
qualify row_number() over (
  partition by person_id, brand, channel
  order by quality_score desc,
           sha2(phone_e164)  -- stable tie-break: same input, same pick
) = 1;

A reconstructed sketch of the select-best step, not the production SQL. Table and column names are made up.

Decisions and tradeoffs

Eligibility before quality: If you rank first and filter after, a person whose top number lacks consent ends up with no number or the wrong one. Filtering first means the best number is always one you are allowed to use.
A deterministic tie-break: Ties broken by row order change from run to run. A hash of the number gives the same answer every time, so lists can be reproduced and compared between runs.
Consent tracked per channel: Broadcast texts and one-to-one texts have different consent rules, so a single “can text” flag would be wrong in both directions.
Shared numbers count against a number: A number on many people is usually a household, office or bad append, so it is a weaker signal for any one person.

Validation

Each person ends with at most one selected number per brand and channel.
No selected number is suppressed, invalid or missing consent for its channel.
Running it twice on the same inputs gives the same output.

Limitations

Described without employer data: no numbers, people, weights or production SQL. The example and the code sketch are made up.

Why it matters for the job

The same problem shows up anywhere contact data comes from several systems: picking one trustworthy email or phone per customer, with consent rules, for marketing and sales operations.

Stack

Snowflake, SQL, EveryAction/VAN

Finding duplicate people in a 1.2M-record CRM
My job at EDF · no employer data shown
Finding 44K members missing from the CRM
My job at EDF · no employer data shown