Over the course of time, duplicate companies/records and contacts are added by accident that are not entirely identical. For example, typos, alternate spellings, alternate names, etc.
How would you suggest "cleaning" up the records other than inefficiently reading down the list with the hope that we will notice these anomalies?
To register for the forums, please contact your Digital-Clay representative.
Cleaning & Fixing
-
ZevToledano
- Posts: 722
- Joined: Fri Apr 03, 2009 2:29 pm
Re: Cleaning & Fixing
Non-identical names is a challenge. If one name were contained in another, then that could be done with a subquery. But typos, and alternate names etc can mean any kind of mutation and often cannot be found using a query.
For example, one advanced method to look into (assuming you are using SQL Server and are comfortable with T-SQL programming, is using a custom database function. i.e. you would add the function to the SQL Server database, or program the function yourself and add it to SQL Server, then configure Clay to use it as an External Function, then use it in a Clay subquery to compare names to all the other names.
This function could use a built-in function like SOUNDEX/DIFFERENCE to compare names and return the ones with the highest similarities, but there are limitations to this, and it would return many false positives (names that are similar but actually two different companies) as well as miss lots of typos that make the name too dissimilar. Or, you can try to use a more advanced algorithm like Levenshtein but I haven't played with this to see how effective it is. Or you can program your own logic.
The only method I know of that is pretty foolproof is to search for/sort by duplicates using OTHER fields besides the name. For example, company/people IDs, phone numbers, emails, etc, even if they are emails of people linked to the company. Assuming you have such data, that is.
For example, one advanced method to look into (assuming you are using SQL Server and are comfortable with T-SQL programming, is using a custom database function. i.e. you would add the function to the SQL Server database, or program the function yourself and add it to SQL Server, then configure Clay to use it as an External Function, then use it in a Clay subquery to compare names to all the other names.
This function could use a built-in function like SOUNDEX/DIFFERENCE to compare names and return the ones with the highest similarities, but there are limitations to this, and it would return many false positives (names that are similar but actually two different companies) as well as miss lots of typos that make the name too dissimilar. Or, you can try to use a more advanced algorithm like Levenshtein but I haven't played with this to see how effective it is. Or you can program your own logic.
The only method I know of that is pretty foolproof is to search for/sort by duplicates using OTHER fields besides the name. For example, company/people IDs, phone numbers, emails, etc, even if they are emails of people linked to the company. Assuming you have such data, that is.