Clay + HubSpot list hygiene

Put a gate between the list and HubSpot.

Normalize, match, enrich and verify every uploaded list in Clay first. Only clean, matched rows reach the CRM; everything else waits in a review queue.

Same list. Same tools. The order of operations decides whether you get 120 duplicates or none.

On this page
Example: a 1,000-row event list
Toggle to change how each row lands in HubSpot
Dana Ruizlinkedin.com/in/dana-ruiz · stored as http://www…/dana-ruiz/
Kestrel Labskestrel.com/about · company already in CRM
Omar HaddadNew to CRM · no email on the list
J. Pateljpatel@northwind.co · mailbox doesn’t exist
Lena OstCustomer · title fixed by her rep last week
Duplicates created120
Hard bounce rate6.0%
Enrichment spend$120

Raw 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.

Why this breaks

HubSpot only dedupes on a few keys.Everything else is an exact string match, and uploaded lists are never formatted the way your CRM is.

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.

01

Auto-dedupe covers email and domain only

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.

02

Formatting drift breaks exact matches

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.

03

API-created companies skip the domain check

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.

04

Enrichment runs before matching

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).

What good looks like

Measure the gate by what it keeps out.Five numbers tell you whether list imports are helping the CRM or quietly rotting it.

Track each one per import batch, not per month, so a bad list shows up on the day it lands.

MetricWeakAcceptableStrongSource
Hard bounce rate, first send of an imported listAbove 2%0.5–2%Under 0.5%Apollo, 2026; verified.email, 2026
Total bounce rateAbove 5%2–5%Under 2%verified.email, 2026
Gmail spam complaint rate0.3% or more0.1–0.3%Under 0.1%Google sender guidelines
Email find rate on an outbound listUnder 45%45–65%75%+ (waterfall + verify)Compiled enrichment tests, 2026
Duplicates created per import batchAbove 5% of rows1–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.

The playbook

Ten steps from messy CSV to clean CRM.Built in Clay and HubSpot, in the order that keeps duplicates and wasted credits out.

Steps 1–5 are a one-time build. Steps 6–10 run on every list after that, mostly without you.

Use the gate when

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.

Native import is fine when

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).

1
Build once

Write the gate contract

What

A one-page spec of what may enter HubSpot: required keys, allowed values and who owns each field.

Why

Without written rules, every list gets judged by eye and nothing can be automated or rejected with a reason.

Who

The ops owner writes it; sales and marketing leads sign off on field ownership.

  • Contact passes if it has an email or a canonical LinkedIn URL, plus a company domain. Company passes with a root domain and a name.
  • HubSpot’s own import rules set the floor: email to create a contact; name or domain to create a company (HubSpot KB).
  • List the fields Clay may never write: lifecyclestage, hubspot_owner_id, legal basis, and anything a rep edits by hand.
2
Build once

Land every list in a Clay staging table

What

One Clay table per list type (events, partner, purchased, scraped). Nothing uploads straight to HubSpot.

Why

Stable columns mean stable formulas and run conditions, and the raw file stays intact for audit.

Who

Whoever receives the list drops the CSV into the right table. That’s their whole job.

  • Fixed columns on every table: source_list, batch_id (YYYYMMDD-source), uploaded_by, consent_basis.
  • Keep raw columns untouched and do all cleaning in new formula columns next to them.
  • Keep auto-update off while you build, then dry-run 50 rows before you switch it on.
3
Build once

Normalize the keys with formula columns

What

Canonical versions of every key you match on: email, root domain, LinkedIn URL, phone, country.

Why

Exact-match lookups only work when both sides are formatted the same way. This step is where the protocol and slash duplicates stop.

Who

Built once by a GTM engineer and reused across every staging table.

  • Email: trim and lowercase. Domain: strip protocol, www., path, query and port; lowercase.
  • LinkedIn: rebuild as https://www.linkedin.com/in/{slug}: lowercase, no trailing slash, no query string or locale path. Keep the slug in its own column too.
  • Phone: E.164. Country: ISO-2. Title: map to seniority and function buckets for routing and scoring.
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)
4
Build once

Match against HubSpot before you enrich anything

What

Clay “Lookup object” for every row, in a fixed order, storing the HubSpot record ID you find.

Why

A found ID means update, never create. It also tells you which rows don’t need paid enrichment at all.

Who

Clay does it per row; you only define the match order.

  • Order: record ID → email → canonical LinkedIn (a HubSpot custom property set to unique) → company root domain.
  • Create the unique property in Settings → Properties with “require unique values”, or via the API with hasUniqueValue: true. HubSpot allows up to 10 unique properties per object (Properties API).
  • No SOQL in HubSpot. For bulk checks, use the CRM Search API: up to 5 filter groups, 6 filters per group, 18 filters total, 200 results a page, 10,000 per query, about 5 requests a second. 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 }
5
Build once

Backfill the canonical key on records you already have

What

Pull existing contacts into Clay, compute the canonical keys and write them back.

Why

Normalizing incoming lists is only half the match. The stored side has to be canonical too.

Who

One sprint task, then the weekly re-run keeps it current.

  • Use Clay’s “Import objects from HubSpot” source (optionally scoped to a list), then “Update object” keyed on the HubSpot record ID, which edits in place and creates nothing.
  • Resolve collisions (two records with the same canonical value) in HubSpot’s Manage duplicates tool before switching on the unique constraint, or imports will fail row by row.
6
Every list

Enrich only the misses and the gaps

What

Run enrichment only where there’s no record ID or the field is empty.

Why

Rows already in the CRM are often a third or more of an event list. Enriching them is pure waste.

How much

Cap credits per row, and log which provider filled each field.

  • Clay “Only run if”: hs_object_id is empty OR work_email is empty.
  • Enrich the company first (domain to firmographics), then the person. Person lookups get more accurate once the company is known.
  • Email waterfall: stop at the first verified hit rather than querying every provider.
7
Every list

Verify and suppress before anything is written

What

Email verification status plus suppression checks on every row.

Why

Bounces above 2% hurt sender reputation, and emailing a customer from a cold list costs trust you can’t buy back.

Where

Verification happens in Clay; suppression data is read from HubSpot.

  • Statuses: valid, catch-all, invalid, unknown. Catch-all goes to a separate list or a warmed stream, never a main send.
  • Suppress: unsubscribed or bounced in HubSpot, current customers, open deals, competitors, and personal domains if you sell B2B only.
8
Every list

Route every row to one of three exits

Update

Has a record ID: “Update object”, mapped columns only, “Ignore blank values” on.

Create

No ID and passes the contract: “Create object”, then “Create association” to link contact and company.

Review

Fails the contract: write a reason code and alert the owner. Nothing is pushed.

  • Never let Clay write lifecycle stage or owner. Your HubSpot workflows and routing own those, so routing logic stays in one place.
  • For companies, always look up before creating. Domain dedupe doesn’t protect API writes (HubSpot KB).
9
Every list

Stamp provenance on every record

What

HubSpot properties that record where each row came from and when it was enriched.

Why

A bad list can be found and rolled back in one filter, and every report can be cut by source.

Who

Written automatically by the Clay push.

  • Properties: original_source_list, import_batch_id, last_enriched_date, enrichment_provider, gate_status.
  • Add newly created records to a static list named after the batch so campaigns start from exactly what passed.
10
Every week

Monitor per batch and re-run quarterly

What

A small gate-health dashboard in HubSpot plus a quarterly pass of the whole database through the gate.

Why

Data decays every month. The gate keeps new lists clean; the re-run keeps old records from rotting.

Who

Ops reviews the dashboard weekly and clears the review queue within 2 business days.

  • Reports: new contacts by batch ID, duplicates flagged per batch, first-send bounce rate per batch, fill rate on email, domain, title and phone.
  • Set alerts when a batch crosses the weak line in the benchmark table: over 2% bounces or over 5% duplicates.
Best practices

Twelve rules that keep the gate honest.Each one is a small setting or habit. Together they decide whether the gate holds.

  1. Match on the most stable key first: record ID, then email, then canonical LinkedIn, then domain plus name.
  2. Store the canonical key in its own unique property. Never overwrite the raw value a rep typed.
  3. Keep the original upload columns untouched in Clay for audit and rollback.
  4. Run one staging table per list type so formulas and run conditions never drift.
  5. Enrich the company before the person; it raises person-match accuracy and saves credits.
  6. Every rejected row gets a reason code. No reason code, no rejection.
  7. Use “Update existing only” when re-importing data you already own.
  8. Blank cells don’t clear values on HubSpot import. Use a workflow clear-property action when you truly need to wipe a field.
  9. Cap enrichment credits per row and log the provider for each filled field.
  10. Dry-run 50 rows before turning on auto-update for any table.
  11. Give the review queue an SLA: cleared within 2 business days, owned by a named person.
  12. Use the HubSpot unique constraint, not only Clay-side matching, so direct imports by other users are protected too.
Common mistakes

Eight shortcuts that cost more than they save.Every one of these is common, and every one shows up later as a cleanup project.

“Create” on every row

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.

Enriching before matching

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.

Exact-match on raw URLs

Protocol, www and trailing-slash differences all miss, so Create runs instead of Update.

Cost: silent duplicates that pass every report.

Letting vendors overwrite owned fields

Enrichment replaces a title a rep fixed, or resets an owner.

Cost: misrouted leads, and reps stop trusting the CRM.

Pushing unverified emails

A purchased list sent unverified on day one.

Cost: bounce rates over 2%, sender reputation damage and possible sending restrictions.

No batch ID

Records land with no trace of which upload created them.

Cost: a bad list can’t be found or rolled back.

Turning on uniqueness before cleaning

Setting a property to unique while collisions still exist in the database.

Cost: imports error out row by row until someone untangles them.

Trusting forms to dedupe

Custom unique properties are enforced on imports and edits, but not on form submissions.

Cost: form traffic recreates the duplicates the gate removed.
Calculator

What does one ungated list cost you?Enter your own numbers. Results update as you move each slider.

1,000
A typical event or purchased list
4
40%
Event lists from existing audiences often run 30–60%
30%
Protocol, www, slash, case or path differences that miss an exact match
6%
$0.12
Duplicates created per list120Rows in CRM × share that miss on format
Wasted enrichment per year$2,304Rows already in CRM × cost × lists × 12
Hard bounces per list60Gate verifies first and holds invalid rows
Duplicates per year, ungated5,760Per-list duplicates × lists × 12
Where each list goes through the gate
UpdateCreateReview
Matched · update 400New · create 564Held for review 36

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.

Copyable template

The import gate spec, ready to paste.Drop it into your ops wiki and fill in the blanks. It covers keys, match order, routing, field ownership and reason codes.

import-gate-spec.txt
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
FAQ

Questions teams ask before building this.

Why did a Clay lookup miss a contact that was already in HubSpot?

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.

Does HubSpot have an equivalent to SOQL?

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.

Can I make HubSpot reject duplicates on a custom field?

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.

Is a single data provider good enough, or do I need a waterfall?

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.

Won’t a review queue just become another backlog?

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.

How long does it take to build?

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.

How JayOh does it

We build the gate, then we run it.A JayOh pod owns the contract, the Clay tables and the HubSpot properties as one system, not a one-off cleanup.

90%Reduction in data errors
$2.2MPipeline in 90 days
35%Shorter sales cycles
28%Lift in forecast accuracy

Consultant owns the rules

The gate contract, field ownership and reason codes, agreed with your sales and marketing leads in week one.

Devs build the system

Staging tables, normalization formulas, unique properties, the backfill and the three-exit routing, shipped in sprints.

We keep it clean

The weekly batch health check, the review queue and the quarterly re-run, so decay never builds back up.