What to do with dirty lead data from events

One of the things that always drove me crazy in sales was having to wade through dirty data in the CRM to figure out who I should be contacting. This is a challenge for most organizations and ranges from a minor inconvenience to an absolute nightmare. Commonly it leans towards the latter. Tackling sales data has several different aspects.

This is a sample process for cleaning lead data from an event before importing it into Salesforce.

Here is some data that you may get from working a booth at an event:

We want to make it more readable and get an idea of what we are working with:

Just in case we want to remove any duplicates that may be in our data:

One of the big steps is standardizing the data to ensure that it all has the same format. We want to make sure that all of the text case is the same using a formula such as =proper(). We also want to make sure that the dates and currencies are the same.

Next, we can look for extra spaces in the data, spelling errors, account names, phone numbers, columns that can be removed, rows with blank account names, etc. Here we remove the column “Favorite Color” and the row with a blank Account Name.

If the list is larger, but manageable, we could use conditional formatting to point out the rows with blank account names:

Then we want to check the emails. It is extremely common to see wrong emails, junk emails, @yahoo.com emails in a b2b setting. We have a couple of options to fix the emails. We could filter the emails that we do not want. We could also delete the @yahoo.com emails and then guess the emails using a formula =A1 & "." & B1 & "@" & C1. Where A1 is the first name, B1 is the last name, and C1 is the company domain. Finally, we could create a VBA macro to do any of this, but in this case, I created a VBA macro to just delete any rows with @yahoo.com, @gmail.com, and @hotmail.com. Obviously, you could build this to be much more robust and include many other domains.

Finally, we probably want to add a column for the event that these leads were gathered from and label it a campaign. Now we are ready to fire up the data import wizard and import these leads into the salesforce. We can double-check the import to make sure that all of the fields mapped and that the accounts are accurate.

From here we want to create a salesforce list view so that the AEs can easily see who to follow up with. Another thing to note is that some form of enablement should be done for the AEs so that they understand where these leads came from, the context of the event, and who were the team members from the company that spoke to them at the event. It is also a great idea to have the team members who attended the event to speak about their experience to give the AEs a better understanding of the focus of many of the leads.