Same list. Same tools. The order of operations decides whether you get 120 duplicates or none.
On this pageRaw upload: 400 rows were already in HubSpot, 120 missed on formatting and became duplicates, and every row was enriched, including the ones you already own.
Most duplicate problems aren’t a tool problem. They come from running the steps in the wrong order: push, then enrich, then try to clean.
HubSpot automatically dedupes contacts by email and companies by primary domain. A row with no email, or a LinkedIn URL as its only key, creates a new record unless something matches it first.
http vs https, a missing www, a trailing slash, a tracking parameter, a domain with a path. Each is the same identity to a human and a different string to a lookup, so the lookup misses and Create runs.
HubSpot’s own docs say companies created through the API, including sync apps, are not deduplicated by domain. A Create action from Clay will happily make a second copy of a company you already have.
Teams enrich the whole list, then push. You pay to re-enrich records you already own, and fresh vendor values overwrite fields your reps corrected by hand.
And a one-time clean doesn’t last: contact data decays at roughly 22.5% a year on HubSpot’s own decay model, with email addresses decaying faster (ZoomInfo Pipeline, 2026).
Track each one per import batch, not per month, so a bad list shows up on the day it lands.
| Metric | Weak | Acceptable | Strong | Source |
|---|---|---|---|---|
| Hard bounce rate, first send of an imported list | Above 2% | 0.5–2% | Under 0.5% | Apollo, 2026; verified.email, 2026 |
| Total bounce rate | Above 5% | 2–5% | Under 2% | verified.email, 2026 |
| Gmail spam complaint rate | 0.3% or more | 0.1–0.3% | Under 0.1% | Google sender guidelines |
| Email find rate on an outbound list | Under 45% | 45–65% | 75%+ (waterfall + verify) | Compiled enrichment tests, 2026 |
| Duplicates created per import batch | Above 5% of rows | 1–5% | Under 1% | JayOh operating target Estimate |
Sources: Apollo bounce benchmarks (2026); verified.email bounce benchmark (2026); Google email sender guidelines. The find-rate band comes from independent 2026 tests where a single provider found 38–42% of emails on a 2,000-contact list and a waterfall found 78%. For scale: Gartner puts the average cost of poor data quality at $12.9M per organization per year.
Steps 1–5 are a one-time build. Steps 6–10 run on every list after that, mostly without you.
Lists arrive weekly or more, rows often lack an email, LinkedIn URLs are the main key, or the list needs enrichment before anyone can work it.
It’s a one-off of a few hundred rows, every row has an email, and you only need to update records you already own (Update existing only).
A one-page spec of what may enter HubSpot: required keys, allowed values and who owns each field.
Without written rules, every list gets judged by eye and nothing can be automated or rejected with a reason.
The ops owner writes it; sales and marketing leads sign off on field ownership.
lifecyclestage, hubspot_owner_id, legal basis, and anything a rep edits by hand.One Clay table per list type (events, partner, purchased, scraped). Nothing uploads straight to HubSpot.
Stable columns mean stable formulas and run conditions, and the raw file stays intact for audit.
Whoever receives the list drops the CSV into the right table. That’s their whole job.
source_list, batch_id (YYYYMMDD-source), uploaded_by, consent_basis.Canonical versions of every key you match on: email, root domain, LinkedIn URL, phone, country.
Exact-match lookups only work when both sides are formatted the same way. This step is where the protocol and slash duplicates stop.
Built once by a GTM engineer and reused across every staging table.
linkedin_canonical = "https://www.linkedin.com/in/" + lower(slug) slug = text after "/in/", cut at the first "/", "?" or "#" root_domain = lower(host) minus "www." (no path, query or port)
Clay “Lookup object” for every row, in a fixed order, storing the HubSpot record ID you find.
A found ID means update, never create. It also tells you which rows don’t need paid enrichment at all.
Clay does it per row; you only define the match order.
hasUniqueValue: true. HubSpot allows up to 10 unique properties per object (Properties API).IN values must be lowercase, and new records can take a moment to become searchable (CRM Search API).POST /crm/v3/objects/contacts/search
{ "filterGroups": [
{ "filters": [{ "propertyName": "email", "operator": "IN",
"values": ["dana@northwind.io", "omar@kestrel.com"] }] },
{ "filters": [{ "propertyName": "linkedin_canonical", "operator": "EQ",
"value": "https://www.linkedin.com/in/dana-ruiz" }] } ],
"properties": ["email", "linkedin_canonical"], "limit": 200 }
Pull existing contacts into Clay, compute the canonical keys and write them back.
Normalizing incoming lists is only half the match. The stored side has to be canonical too.
One sprint task, then the weekly re-run keeps it current.
Run enrichment only where there’s no record ID or the field is empty.
Rows already in the CRM are often a third or more of an event list. Enriching them is pure waste.
Cap credits per row, and log which provider filled each field.
hs_object_id is empty OR work_email is empty.Email verification status plus suppression checks on every row.
Bounces above 2% hurt sender reputation, and emailing a customer from a cold list costs trust you can’t buy back.
Verification happens in Clay; suppression data is read from HubSpot.
Has a record ID: “Update object”, mapped columns only, “Ignore blank values” on.
No ID and passes the contract: “Create object”, then “Create association” to link contact and company.
Fails the contract: write a reason code and alert the owner. Nothing is pushed.
HubSpot properties that record where each row came from and when it was enriched.
A bad list can be found and rolled back in one filter, and every report can be cut by source.
Written automatically by the Clay push.
original_source_list, import_batch_id, last_enriched_date, enrichment_provider, gate_status.A small gate-health dashboard in HubSpot plus a quarterly pass of the whole database through the gate.
Data decays every month. The gate keeps new lists clean; the re-run keeps old records from rotting.
Ops reviews the dashboard weekly and clears the review queue within 2 business days.
The quickest way to push a list, and the quickest way to split one person across two records.
Cost: duplicate sends, split activity history, broken attribution.Paying to look up people you already have, often a third or more of an event list.
Cost: wasted credits on every list, every time.Protocol, www and trailing-slash differences all miss, so Create runs instead of Update.
Cost: silent duplicates that pass every report.Enrichment replaces a title a rep fixed, or resets an owner.
Cost: misrouted leads, and reps stop trusting the CRM.A purchased list sent unverified on day one.
Cost: bounce rates over 2%, sender reputation damage and possible sending restrictions.Records land with no trace of which upload created them.
Cost: a bad list can’t be found or rolled back.Setting a property to unique while collisions still exist in the database.
Cost: imports error out row by row until someone untangles them.Custom unique properties are enforced on imports and edits, but not on form submissions.
Cost: form traffic recreates the duplicates the gate removed.Model: duplicates = rows × share already in CRM × share with mismatched keys. Hard bounces = rows × unverified bounce rate. Through the gate, matched rows update and are not enriched, and invalid emails are held for review instead of sent. Your actual rates will vary by list source.
IMPORT GATE SPEC (owner: ________ last reviewed: ________)
1. STAGING
- One Clay table per list type: events / partner / purchased / scraped
- Required columns: source_list, batch_id (YYYYMMDD-source), uploaded_by, consent_basis
- Raw columns never edited; cleaning happens in formula columns
2. NORMALIZATION
- email: trim + lowercase
- root_domain: strip protocol, www., path, query, port; lowercase
- linkedin_canonical: https://www.linkedin.com/in/{slug} (lowercase, no slash, no query)
- phone: E.164 country: ISO-2 title: seniority + function bucket
3. MATCH ORDER (stop at first hit, store hs_object_id)
1) hs_object_id 2) email 3) linkedin_canonical (unique property) 4) root_domain (company)
4. ENRICHMENT
- Only run if: hs_object_id is empty OR field is empty
- Company first, then person. Email waterfall stops at first verified hit
- Credit cap per row: ____ Log enrichment_provider per field
5. VERIFY + SUPPRESS
- Email status: valid -> pass | catch-all -> separate stream | invalid/unknown -> review
- Suppress: unsubscribed, bounced, customers, open deals, competitors, personal domains
6. ROUTING
- UPDATE: has hs_object_id -> Update object, mapped columns only, ignore blanks ON
- CREATE: no ID + passes contract -> Create object + Create association
- REVIEW: fails contract -> reason code + alert owner (SLA: 2 business days)
7. FIELD OWNERSHIP (Clay may NOT write)
- lifecyclestage, hubspot_owner_id, legal basis, rep-edited title, ________
8. PROVENANCE PROPERTIES (written on every push)
- original_source_list, import_batch_id, last_enriched_date, enrichment_provider, gate_status
9. REASON CODES
- MISSING_KEY, INVALID_EMAIL, CATCH_ALL, SUPPRESSED_CUSTOMER, SUPPRESSED_UNSUB,
DUP_IN_FILE, COLLISION, PERSONAL_DOMAIN
10. WEEKLY HEALTH CHECK
- [ ] First-send bounce rate per batch under 2%
- [ ] Duplicates flagged per batch under 1%
- [ ] Review queue cleared
- [ ] Fill rate on email / domain / title tracked
- [ ] Quarterly: full database re-run through the gate
The lookup compares strings exactly. If HubSpot stored the profile URL with http and a trailing slash and the list has https without one, it’s a miss and Create runs. The fix: canonicalize the key on both sides (steps 3 and 5) and store it in a unique property.
No. The closest thing is the CRM Search API: JSON filter groups (OR between groups, AND within a group) with operators like EQ, IN, CONTAINS_TOKEN and HAS_PROPERTY. It allows 18 filters per query and returns up to 10,000 results. For per-row checks inside Clay, the Lookup object action is simpler.
Yes. Set a custom property to require unique values (up to 10 per object). HubSpot enforces it on imports and manual edits, but not on form submissions, so keep email as the form key.
If one provider gives you over 70% verified coverage on your ICP, keep it simple. If coverage is thin, international or niche, a waterfall that stops at the first verified hit usually lifts find rates well above any single source. Our Clay vs ZoomInfo vs Apollo comparison breaks this down.
Only if nobody owns it. Every rejected row carries a reason code, so most can be bulk-fixed or dropped by code. Give the queue a named owner and a 2-business-day SLA.
The one-time build (contract, staging tables, normalization, matching, backfill) typically fits in one or two sprints. After that, each new list runs through the gate in minutes.
The gate contract, field ownership and reason codes, agreed with your sales and marketing leads in week one.
Staging tables, normalization formulas, unique properties, the backfill and the three-exit routing, shipped in sprints.
The weekly batch health check, the review queue and the quarterly re-run, so decay never builds back up.
We engineer growth systems. JayOh builds the RevOps architecture, GTM execution and data infrastructure that turn messy revenue operations into predictable pipeline for B2B SaaS and PE-backed teams.