Enrichment and data-quality pipeline
Fill gaps from enrichment, remove duplicates, normalize titles, then measure what's still broken.
Sample data. Fictional companies. Nothing here is real.
Data health (sample)
Duplicate rate (sample)
25.0%
2 of 8 raw rows share an email
Missing-field rate (sample)
25.0%
6 of 24 email/company/phone cells empty
Sync failures (sample)
2
Sent to a review list, not retried blindly
Enrichment waterfall
Clay: Used in my live setupI use Clay daily or weekly to build Scarlet Risk lead lists. The waterfall tries each source in order and stops at the first verified value. Provider names here are generic.
- Provider 1: primary email and phone source
- Provider 2: fills what provider 1 missed
- Fallback: pattern guess plus verification, or leave blank and flag
- Write back only into empty fields
Before: raw sample contacts
| Name | Title | Company | Phone | |
|---|---|---|---|---|
| Jordan Avery | Director of IT | JORDAN.AVERY@northwind-sample.com | Northwind Logistics | 555-0101 |
| Jordan Avery | Dir. IT | jordan.avery@northwind-sample.com | Northwind Logistics | missing |
| Priya Lennox | CISO | priya.lennox@bluepeak-sample.com | Bluepeak Health | missing |
| Marcus Oyelaran | VP Engineering | marcus.oyelaran@copperline-sample.com | missing | 555-0144 |
| Elena Fairbrook | Head of Compliance | elena.fairbrook@quillstone-sample.com | Quillstone Software | 555-0150 |
| Elena Fairbrook | head of compliance | Elena.Fairbrook@quillstone-sample.com | Quillstone | missing |
| Sam Reyes | IT Intern | missing | Quillstone Software | missing |
| Dana Whitlock | Office Manager | dana.whitlock@harborpine-sample.com | Harbor & Pine Legal | 555-0177 |
After: deduped and normalized
| Name | Title | Seniority | Company | |
|---|---|---|---|---|
| Jordan Avery | Director of IT | Manager | jordan.avery@northwind-sample.com | Northwind Logistics |
| Priya Lennox | CISO | Executive | priya.lennox@bluepeak-sample.com | Bluepeak Health |
| Marcus Oyelaran | VP Engineering | Executive | marcus.oyelaran@copperline-sample.com | missing (flagged) |
| Elena Fairbrook | Head of Compliance | Executive | elena.fairbrook@quillstone-sample.com | Quillstone Software |
| Sam Reyes | IT Intern | Individual | missing (flagged) | Quillstone Software |
| Dana Whitlock | Office Manager | Manager | dana.whitlock@harborpine-sample.com | Harbor & Pine Legal |
Emails are trimmed and lowercased before matching, so "JORDAN.AVERY@…" and "Elena.Fairbrook@…" collapse into one row each. The row with no email is kept but flagged for enrichment.
The logic
Learning / referenceExamples I wrote while building skills, run on fictional CSV files. The regex uses word boundaries (\b) so "cto" doesn't match inside "director".
import re
import pandas as pd
contacts = pd.read_csv("data/contacts_raw.csv")
contacts["email"] = contacts["email"].str.strip().str.lower()
before = len(contacts)
contacts = contacts.drop_duplicates(subset="email", keep="first")
print(f"Removed {before - len(contacts)} duplicate contact(s)")
def seniority(title: str) -> str:
t = title.lower()
# \b is a word boundary, so "cto" does not match inside "director".
if re.search(r"\b(chief|ciso|cio|cto|vp|head)\b", t):
return "Executive"
if re.search(r"\b(director|dir|manager)\b", t):
return "Manager"
return "Individual"
contacts["seniority"] = contacts["title"].apply(seniority)
contacts["target"] = contacts["seniority"].isin(["Executive", "Manager"]) & ~contacts["title"].str.contains("office", case=False)
accounts = pd.read_csv("data/accounts.csv")
out = contacts.merge(accounts[["account_id", "name", "fit"]], on="account_id").rename(columns={"name": "account"})
out = out.sort_values(["fit", "seniority"], ascending=[False, True])
print(out[["account", "first_name", "last_name", "title", "seniority", "target"]].to_string(index=False))-- Goal: rank accounts by fit plus recent buying signals. Works in Postgres and SQLite.
WITH signal_points AS (
SELECT account_id,
CASE priority WHEN 'High' THEN 20 WHEN 'Medium' THEN 10 ELSE 5 END
- CASE WHEN days_ago > 10 THEN 5 ELSE 0 END AS points
FROM signals
),
account_signals AS (
SELECT account_id, SUM(points) AS signal_score, COUNT(*) AS signal_count
FROM signal_points
GROUP BY account_id
),
scored AS (
SELECT a.account_id, a.name, a.fit,
COALESCE(s.signal_score, 0) AS signal_score,
COALESCE(s.signal_count, 0) AS signal_count,
a.fit + COALESCE(s.signal_score, 0) AS total_score
FROM accounts a
LEFT JOIN account_signals s ON s.account_id = a.account_id
)
SELECT RANK() OVER (ORDER BY total_score DESC) AS priority_rank,
name, fit, signal_score, signal_count, total_score
FROM scored
ORDER BY priority_rank;