Identify fake/abusive records in CRM

Find out more post on my blog.

If you have a business model where every new user for your organization must have a Contact/Account in your CRM system, than take a look at this article.

Let say that you offer a product for download from your website, for your clients they need to register on your website in order to be able to download the trial of that product. The registration that created a Contact in your CRM, so that way you know who downloaded the trial and you can later on add him to a marketing campaign.

This approach is great as you will always know who is behind that trial downloads, but your CRM will start filling up with more and more records, depending on how popular your product is. Same as many websites, there are spam/abusive registrations where the email address is randomly generated, or in this case it is generated by a script just to download as many trials as possible, without figuring out every time a new email, user name and password. For every CRM administrator this is a headache, as he have to identify and delete this record at some point from CRM, and eventually prevent new records to be created with the same domain.

Example: alabala1@someone.com; alabala2@someone.com; alabala2@someone.com ;alabala3@someone.com ;alabala4@someone.com, or 1q2w3e4r5t6y7u@123.com; 1w2q3r4e5y6t@123.com, you've got the point. These email are generated by a script that changes one or more symbols or the position on the username so that way will be unique for your system and will allow to create user profile.

To identify the email addresses you first must know the amount of records in CRM having the same domain name. To achieve this you need first to create a data export service, and synchronize your data to Azure SQL DB. Once this is done you need to either run the bellow SQL query in Azure DB, or in my case to use SQL Management studio.

SELECT RIGHT(emailaddress1, LEN(emailaddress1) - CHARINDEX('@', emailaddress1)) Domain ,

COUNT(emailaddress1) EmailCount

FROM account

WHERE LEN(emailaddress1) > 0

GROUP BY RIGHT(emailaddress1, LEN(emailaddress1) - CHARINDEX('@', emailaddress1))

ORDER BY EmailCount DESC

This query will return a list of all domain and the count of Accounts (in my case) having the same domain name.

Having this list, now you need to do some manual work and search for an Accounts (again, this is in my case), where the email address end with @<domain name>

Of course that Gmail, Hotmail, and etc., are not abusive records even dough they have the largest number of email addresses in our CRM. There always will be abusive one amongst them, but here the CRM administrator must find the pattern in the username (as shown on the screen bellow) in order not to block all registrations with @gmail.com domain.

If you see any pattern in the records this are your spam/abusive one, in this case the spam/abusive domain. Also you can see that the date when the record is created is the day when the user has registered, and in this case the user has registered quite often of one day. For me this domain is abusive/spam and all records can be deleted from CRM, and the domain can be blocked for future registration, so the records with this email will never reach CRM again.

Now, you will never be able to stop all spam/abusive email, but you can regularly review them and add them to a blacklist or delete them from CRM. It is worth saying that here we are looking for records in CRM having email address with the same domain name, but the username is a combination of letters and numbers which clearly has been auto generated.

After spending some time, I've managed to identify and clear around 500,000 records from our CRM system, something that cannot be automated as there is still manual confirmation of each domain name

Good lick with finding your spam/abusive records and clearing your CRM system.

Let me know if you have any ideas how to improve this or even automate it.