5
Shares
More share buttons
Share on Pinterest

The Super Awesome (and a little stalkerish) Email and Social Media Account Matcher

Mack Web is  now Genuinely. Learn more.

– –

Back in August, there was quite a rush of excitement around the office when our friends over at SEO Gadget wrote a blog post about using the FullContact API to mine user data.

We’re nerds. We admit it.

The one thing that really caught our eye was the ability to find social networks based upon a user’s email address. Say for example you have an email list that you collected at a conference. Now you’d like to follow up with those users via email, but you’d also like to find them on Twitter to see if any of them are professionally active or influential.

Traditionally, you’d have to enter all the email addresses (or names) manually into Google search to see what you can find. If you’re really clever, you may try to use a gmail plug-in like Rapportive to try and match the email addresses. But that’s all a lot of work because you have to look at email addresses one-by-one. If you picked up 200 business cards at a conference, you can easily burn a day or two trying to match all the profiles.

Enter FullContact

They have a great system that allows you to run queries against their massive database of users. Even cooler, they allow you to find up to 250 matches a month for free. Although you couldn’t run an entire marketing email list for a large company with these few queries, it’s great for those conferences or small batch lists you need to try and match up. Searching for social accounts tied to 250 emails manually would take quite a while.

Since this service is set-up as an API, it’s not incredibly user friendly for us non-programmer types. Thus the Microsoft Excel macro that FullContact gives away was great because it suddenly gave non-programmers access to the FullContact database.

But we ran into a couple problems.

1. It’s really slow. It can take 5-10 minutes to run less than a hundred names. That’s lame.

2. It only works on Windows and Excel. I use a Mac. We had one of our interns run it this summer, but he is back at school. And I’m not buying a computer, a copy of Windows, and a copy of Excel just to run this.

3. Macros are a pain. You have to install them to make sure they are operating correctly. Sometimes they don’t work. Sometimes they don’t run with your version of Excel. There are literally hundreds of reasons why one might not work. They are just a major inconvenience.

From Microsoft to Google

So one day, I decided I’d try to build a Google Spreadsheet that would query the API. At first I thought about trying to create a script. But I’m not that great of a programmer plus scripts require you to authorize and install them within your spreadsheet. I really wanted a plug-and-play solution. I wanted something I could give to someone who had zero background in Excel that would allow them to run the queries without any kind of training or help.

Thus after hours of trial and error, endless frustrations, and dozens of Google searches (like 6 hours of work straight…seriously), I was able to create a spreadsheet that pulls in social profiles from FullContact using only the built-in ImportXML function within Google Spreadsheets. I then managed to use a mess of CONCATENATE, Xpath, SEARCH, and other random functions(in other words, I did fancy spreadsheet things) to display each of the main social media profiles: Twitter, Facebook, LinkedIn, and Google Plus.

The Spreadsheet

So enough talking about it, let’s see how it works. First off, let me say it’s really simple. It’s literally copy and paste. I’ll include the link at the end, but here’s quick walk-through:

1. Open the spreadsheet by clicking the link. Make a copy of the spreadsheet for your own use. This document not editable, and I will not share it with you. So don’t ask. Just make your own copy. 🙂 It’s as easy as going to File>Make a Copy

Make a Copy in Google Docs

2. Next, set up a developer account with FullContact. It’s simple and easy. Just give them the info they want, and they’ll get you setup with the account.

3. Get your API key. Once you have your account created, go to the FullContact dashboard. Your API key is big and red and right in the middle of the page. You can see the screen here, but we blacked out our API code so you don’t steal it. Thief.
image01

4. Enter the API key into your new copy of the spreadsheet. It goes in the red box labeled: API Key (clever, right?). As a rule of thumb, in this document you should edit the red cells. You should not edit the blue cells.

FullContact API Google Docs

5. Finally, open the document or spreadsheet where you have your email list saved (you do have the email addresses in some sort of list, right?). Copy only 50 emails on that list and paste them into the spreadsheet in the red cells in Column A, Rows 3-52. Within seconds, you should see social media links begin to appear as they are found by the FullContact API. All 50 ImportXML functions run at once, so you shouldn’t have to wait more than a few seconds for data to appear. Cool, huh?

6. In order to make this data accessible later, you need to copy the results into a new document (or a new sheet within this document). Once this sheet returns the data, select all the cells that you want to save, then go to a new spreadsheet, right click, and select Paste Values. If you just try to do a normal Paste, it will only copy the formulas, and that isn’t going to help you very much. Paste Values will actually paste the results (the links to social profiles) into your new sheet.

You should also know…

There are a few limits and things you should know about this document before you dig in.

FullContact API only counts successful matches. The successful match rate according to FullContact is around 60% – meaning you can probably run close to 400 emails before you run out of queries since many will be returned empty. If you don’t get anything back on a query, it won’t count as an API call. That said, if the person has other information within the FullContact database (such as another social network login, an address, or job title) that data won’t get returned to this spreadsheet, but it will still count as a positive match. You get 250 matches with the free plan and paid plans start at $99/month.

Currently, Google Spreadsheets limits the total number of ImportXML calls to 50 per document. So you can’t run more than 50 emails at a time. That said, you can paste the emails into the document, then copy the social data out into a new spreadsheet, then replace the old emails with 50 new ones. It’s still WAY faster than doing it by hand. If you upgrade to a paid plan and need to run hundreds or thousands of emails, try the Excel macro or have a developer build you a custom solution as running 50 at a time will take a while.

There is no “run” button! The ImportXML function runs automatically, which is usually a good thing. However, if you exit the spreadsheet and then reopen it later with the emails saved inside it, the sheet will immediately make those same 50 API calls. Any successful matches will count against your total. So when you’re finished with the document, you should DELETE all the emails (or your API key) so as to not waste calls every time you open the sheet.

OK, here is the link. Knock yourself out: Spreadsheet

Also, once you make a copy, you’ll be able to see the crazy long formulas I used on the back end. (For what it’s worth, this is probably the most intense spreadsheet I’ve ever made…and I have a graduate business degree). Feel free to customize the formulas to meet your data needs. I only ask that if you find other useful data to call, please share an updated copy of this sheet with me. I’m sure there are hundreds of pieces of data and requests you could pull into it. I’d love to see what y’all come up with. You can find me on Twitter: @tyler_brooks.

Hope this helps!

Author admin

More posts by admin

Join the discussion 10 Comments

  • Tyler,

    Thanks for this thorough walk through on how to do this!

    I’m on a Mac too, but I’m sure I’ll be able to find a work around.

    Thanks again!

  • Thanks for the write-up, Tyler – this is awesome!

    One thing to clarify for your readers – sometimes someone will query our API with an email address we haven’t seen before. When that happens, we have to go out and search the web for information on the person. This search takes a few minutes, so our system returns a “202” response – which means “queued for search – try again later.”

    *So, what does this mean for users of your spreadsheet?*

    If they put in an email address that we haven’t seen before, we might not return any info at first – but if they put in the email address again later, we might return more data.

    This is why we built the Excel macro, incidentally – because it allows us automatically go back and try again whenever it gets a 202 response. Unfortunately, that functionality does lead to the Excel macro being a slower process. We’ve recently released a new version of it that’s much faster – so if anyone’s looking to run a larger batch of email addresses, it might be worth a try.

    Not sure if there’s any way to add that sort of wait-then-try-again functionality to a Google Spreadsheet – but if there is, users would be able to get a better match rate.

    Regardless, speaking as a Mac user myself, thanks again for taking the time to put this together. It’s awesome! 🙂

    • Tyler Brooks says:

      Hey Kipp!

      Thanks for that clarification. I may look and see if there is a way to determine whether or not it returns a 202 using the ImportXML function. I would love to do that, but I’m not sure if there’s a way to do that simply without actually getting into “real” coding – of which I have very little experience.

      If nothing else, I want to see if there’s at least a way to put a “try again later” message on the cells that return that 202 from FullContact. If I get some time, I’ll dive into that.

      Thanks for your feedback! Keep up the great work in Boulder. If you ever make it up to Fort Collins, we’d love to meet some of your team.

      Tyler

  • […] strategy for them all! We introduce FullContact API, the service that allows you to mine user data. Tyler Brooks presents a rundown of the service on his quality […]

  • Tom Church says:

    This is brilliant! Thank you

  • Jon Aston says:

    This is really cool – thanks for sharing it!

    I’m wondering if anyone has taken you up on your ” I’d love to see what y’all come up with” request. If so, do you care to share?

    Thanks again!

    PS – I landed here looking for a tool for finding all of the websites I’ve created accounts on over the years… and no longer use. If you or any of your readers know of something, I would be much obliged.

  • Excellent blog! Do you have any hints for aspiring writers?
    I’m planning to start my own website soon but I’m a little lost on everything.
    Would you propose starting with a free platform like WordPress or
    go for a paid option? There are so many options out there that
    I’m totally overwhelmed .. Any ideas? Appreciate it!

  • There’s definately a lot to know about this subject.
    I love all of the points you’ve made.

Leave a Reply