Resolving Domain Lookup Issues for Client List Matching

Jonathan Melhuish-Sprague · 2025-05-29 13:30:13.066539+00

Looking for Clay Lookup Table help - I have a table which over 1000+ companies, I am using a lookup to check our current client list (we don't want to be contacting clients we already have), I've got it to search for a domain match but I've noticed an anomaly. In our 1000+ list, a domain finishes with .com, but our client list the domain is . and the lookup is failing to acknowledge they match. What is the best way to resolve this? I have Domain > Equals > Domain

Resolving Domain Lookup Issues for Client List Matching | Clay

9 comments

· Sorted by Oldest

Prashant Mantri
·
·
Normalize company names in both lists to remove '.com', 'co.uk' either using Javascript or chatgpt nano. Then do a lookup.
Jonathan Melhuish-Sprague
·
·
This still seems to cause an issue as it doesn't seem to allow for capitalisation differences in the name? or should it be 'contains' rather than 'equals' ?
Prashant Mantri
·
·
that right, pls use 'contains' instead of equals
Jonathan Melhuish-Sprague
·
·
Unfortunately that doesn't work as it pulls in other companies we don't have as clients. It also misses some clients due to special characters
Jake Gill
·
·
do you have any other kind of unique id for your customers like a hubspot record id to use for the lookup instead?
Jonathan Melhuish-Sprague
·
·
Unfortunately not, although there are a limited number of customers so I could create manual ID’s?
Jonathan Melhuish-Sprague
·
·
Although unsure if that’d help actually because I’d need to ID the whole 1000+ list
Jake Gill
·
·
There are a bunch of ways of getting similar results. Better regex matching or extracting the domain, removing the TLD (.com, .co etc - you can do this with a formula on a column i.e. "Get the domain from /domain and remove all text after ".") Then try the lookup again? Or, when we work with folks who need to dynamically check if a record already exists in their CRM or client list: the logic would usually be - do a lookup to your crm or to an external source (does this record exist in our CRM/client list?) IF Yes -> Get Record ID -> it returns a record ID as a unique identifier IF no -> it returns nothing Then you can do a lookup row in another table lookup on your prospecting list to make sure none of those records already exist in your master list by unique ID. So, no need to manually ID 1000+ prospects.
👍1
Jonathan Melhuish-Sprague
·
·
Thanks Jake Gill - I'm going to try the removal of anything after the "." for the domain and see if that works - nice idea
🙌1

Prashant Mantri
·
·
Normalize company names in both lists to remove '.com', 'co.uk' either using Javascript or chatgpt nano. Then do a lookup.
Jonathan Melhuish-Sprague
·
·
This still seems to cause an issue as it doesn't seem to allow for capitalisation differences in the name? or should it be 'contains' rather than 'equals' ?
Prashant Mantri
·
·
that right, pls use 'contains' instead of equals
Jonathan Melhuish-Sprague
·
·
Unfortunately that doesn't work as it pulls in other companies we don't have as clients. It also misses some clients due to special characters
Jake Gill
·
·
do you have any other kind of unique id for your customers like a hubspot record id to use for the lookup instead?
Jonathan Melhuish-Sprague
·
·
Unfortunately not, although there are a limited number of customers so I could create manual ID’s?
Jonathan Melhuish-Sprague
·
·
Although unsure if that’d help actually because I’d need to ID the whole 1000+ list
Jake Gill
·
·
There are a bunch of ways of getting similar results. Better regex matching or extracting the domain, removing the TLD (.com, .co etc - you can do this with a formula on a column i.e. "Get the domain from /domain and remove all text after ".") Then try the lookup again? Or, when we work with folks who need to dynamically check if a record already exists in their CRM or client list: the logic would usually be - do a lookup to your crm or to an external source (does this record exist in our CRM/client list?) IF Yes -> Get Record ID -> it returns a record ID as a unique identifier IF no -> it returns nothing Then you can do a lookup row in another table lookup on your prospecting list to make sure none of those records already exist in your master list by unique ID. So, no need to manually ID 1000+ prospects.
👍1
Jonathan Melhuish-Sprague
·
·
Thanks Jake Gill - I'm going to try the removal of anything after the "." for the domain and see if that works - nice idea
🙌1