How to put a salesforce LeadID on your list of tradeshow leads

Here's the situation. For the hundredth time, you are handed an xls spreadsheet with all the leads from the last tradeshow that your company attended. You need to get them in salesforce, without creating duplicates. Some of them are duplicates, you just know it. Some are existing Leads, and some are existing Contacts. Did I mention that your company is not going to buy you that great dedupe tool from RingLead? Did I mention that already? If I didn't mention it, they're not going to buy it, just get used to it, even though it would save us a million hours a week, and thus save thousands of dollars, and probably would even cure a bad case of the flu or "the consumption", or "the mange", or "scurvy" or whatever......ah, sorry for the rant. As you can tell, I don't have access to the worlds largest CRM budget and I have to prevent duplicates without the help of a dedupe tool like RingLead.

Your first thought is to import the tradeshow xls as Leads and use salesforces' dedupe tool. But, there's a problem. You need to import them into a Campaign. To import them directly into a Campaign using the salesforce campaign import wizard means that as it imports, it can look for duplicates based on email address, right? OK, so the problem rears its ugly head when you realize that as the system imported records into the campaign, it did not import the duplicates it found. So, when you look at the existing Lead in salesforce that was a dupe on your tradeshow xls, you find that it has not been tagged with the proper campaign.



There's a second problem. Some of these tradeshow leads are already in salesforce as Contacts. If you import the list as Leads, no matter where you do it, you're going to end up with duplicates of existing Contacts. Guess what else? If you import them as Leads, and you go to each Lead record individually and use the lovely "Find Duplicates" button, not only will you or your sales reps spend all day doing it, but salesforce, having found the duplicate Contact records, won't let you merge a Lead to a Contact. And if you just delete this duplicate Lead that has a matching Contact record, you've now lost your Campaign information for that person.

Oh, there's one more nicety to remember. Even if you forget the part about Contacts, and choose to use the salesforce dedupe tool when importing Leads, you're going to loose the Campaign information when you use that tool. Why? Because if you choose to import and check for duplicates (using the email address as a match, let's say) salesforce will import all of your Leads except the duplicates. So, your list of 300 tradeshow leads will only show as 249 leads in the Campaign after the import because the rest of them already existed in the system. That means your overall Campaign information is inaccurate. Bummer.

Below is a tutorial to help you take your tradeshow xls list, or any other large list, and match it up with any existing salesforceID that already exists in your organization. Having your tradeshow xls match any existing salesforce Lead or Contact with its' salesforceID will mean you can avoid creating duplicates when you import. I'll be the first to tell you that it takes a few steps. So please, for the love of God and all humanity, if you have some other, ultra-slick way to do this, let me know by posting a comment.

Tutorial Steps:(for the purposes of this tutorial, I'm going to use small sample xls files, but in reality you could use xls files with thousands of records on them as well)

-Export a salesforce report for all Leads in your system. Include LeadID and email address. Save the xls file and call it "All Leads.xls"












-Export a salesforce report for all Contacts w/ ContactID and email address and save that separately (not pictured). You'll need to repeat the below steps using this contact list later.

-Open Microsoft Access. I know, you've never used Access, and never thought you'd have to, but open it anyway.
-Go to the File menu and select "New"
-Select to create a Blank Database. Save it somewhere.
-Right click and choose "Import" (see below). Browse to your tradeshow xls and tell Access you want to import an xls-type file



















-On the Import Spreadsheet Wizard, just leave the defaults on the first screen
-Select the checkbox "First row contains column headings" (see below)



















-Select the "In a new table" bullet (see below).



















-Accept the default column names in the next screen
-Accept the default to "Let Access add primary key"
-Name your table and click "Finish"



















-You need to now import the "All Leads.xls" spreadsheet soepeat the above process for the "All Leads.xls" spreadsheet. That way, you'll have imported both your tradeshow.xls and your "All Leads.xls" as tables in Access. We can now join the two tables together.

-In Access, switch to "Queries" by selecting it in the Access "Objects" list (see below).




















-Then select "Create query in design view"

-Click the "Add" button to add both of your new tables into the Query (see below).




















-After you've added your 2 tables, click the "Close" button
-Notice how there's a little squiggly line that links the two tables together? Click on the little line to highlight it, then right click it, and choose "delete" (this is a join that Access has made that we do not need).

-You now need to draw a little line from one "Email" field to the other "Email" field, thereby linking the tables together. In the "All Leads" table, single click and hold on the "Email" field, then, while holding down the left mouse button, drag your cursor over to the "Tradeshow Leads" table and release the left mouse button once you're hovering over the "email" field in that table.






















You've just joined these two tables together. This join uses the email address to match one table to the other.

-In the "All Leads" table, grab and drag the "LeadID" field down onto the first column in the space below, where it is labeled "Field" (see screenshot- LeadID is identified in yellow)

















-In the "Tradeshow Leads" table, drag the other fields down into their own columns as well (you only need to drag the fields down that you want to see on one final, combined spreadsheet).

-In Access' top menu, look for the icon of an exclamation point, click this icon to run our query.
Access will then display the results of our query. You'll notice that it looked at the 2 spreadsheets, and wherever it made a match on an email address, it listed that item on the new list, and put its salesforce LeadID in place.













-To move this new list into Excel, go to Tools>Office Links>Analyze It with Microsoft Excel.

Now you have your Tradeshow Leads with the proper LeadID associated with them in an xls spreadsheet that you can now use to properly import into your Campaign, without having duplicates created.

Repeat this process using your Contacts xls spreadsheet to do the same thing for any exisiting Contacts that might be on the tradeshow list.