Mack Web is now Genuinely. Learn more.
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.
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.
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
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.
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.
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!