Mining User Data in Excel with the FullContact API

Recognized and prominent brands (like Target) are turning to big data to mine user information to find the best ways to reach and retain their customer base. 

Why let the big guys have all the fun?

If you have a list of your users’ email addresses you can access your users’ personally identifiable information and social profiles. All you need is the FullContact API, NetPeak Checker and some Excel skills.

Full Contact Logo

FullContact’s Person API is amazing – If you have someone’s email address, Twitter handle, Facebook ID or phone number, you can use FullContact’s Person API to fetch a ton of personal user data.

If you feed the Person API an email address, it will give you info like:

Last Name
First Name
Location
Twitter profile URL
Twitter Bio
Twitter Followers
Twitter Users Followed
Facebook profile URL
Linkedin Public Profile URL
Google+ profile URL
Google+ User ID
Google+ Username
Klout score
YouTube user profile URL
A whole lot more.

FullContact is going to answer two big questions for us:

1) Who are our client’s most influential users?

If we can find influencers that are actively using our client’s product, they may be a natural fit for a brand evangelist, spokesperson or content contributor role.

2) What websites do our client’s users read and share that we can target for content placement

To find out what our client’s users are reading/sharing, we can use Richard’s methodology that he presented at MozCon on using Twitter data for really targeted outreach. This will allow us to download all of our users’ tweets, isolate the URLs they are sharing and build a list of sites to target for content placement that we know our client’s users read and share.

This whole process is a lot quicker with the help of a developer, but not all of us have access to developers or feel comfortable working with code – so we’re going to run through how to do this whole process in Excel, developer free.

Q1: What Influencers Use Our Client’s Product?

Step 1) Get an export of user emails

This can come from an email marketing list or a CRM.

Step 2) Download the FullContact Person Enrichment Excel Template from GitHub

You’re going to need to sign up for an account to get access to their API. The first 250 calls are free, after that, pricing starts at $99 for 25,000 calls a month.

Step 3) Enter your API key into the excel sheet and follow the instructions in the worksheet

Full_Contact_API_Key_Pic_001

4) Paste a list of emails into the “InputSheet” tab
Anonymous_email_data_for_full_contact

These are obviously real email addresses

 Step 5) Press the “Populate Contacts” button in the first tab of the Excel template

Generate_contacts_pic

 

Step 6) Don’t touch anything!

This workbook is using VB to pull in a ton of information, so go catch up on email for a few minutes, maybe grab a cup of coffee, go look at some cat gifs for a few minutes. I looked up 60 contacts for this example and it took ~5 minutes.

Bask in the glory of all your user’s data:

Excel WOrkbook for FullContact Output

FullContact isn’t going to have info on everyone, their match rate is around 60%. Thankfully, you only have to pay for an API call if it returns data.

Step 7) Copy all Twitter profile URLs for every single user into a new sheet.

twitter_profile_URLs

Step 8) Use =XpathOnURL on a users’ Twitter profile URL to pull their listed website and follower count

If the URL is in cell A1, here’s the formula to scrape a users listed website:

=XPathOnUrl(A1,”//p/span/span/a”)

And here’s the formula to scrape the number of Twitter followers:

=XPathOnUrl(A1,”//li[3]/a/strong”)

twitter_data_galor

Step 9) Pull Domain Authority Metrics of User Websites

There’s a few ways you can do this, but I really like Netpeak Checker, it’s fast and easy to use and plugs right into the Moz API.

Netpeak_Screenshot

Step 10) VLOOKUP Time

Now you need to get your data into one excel sheet, this is going to require a few VLOOKUPs. The constant data point throughout all of this is the Twitter profile URL, so match your VLOOKUP off that data point.

Important Note: NetPeak Checker adds an “http://” to the beginning of a URL, the other data we’ve been working with does not have “http://”, so when you paste the Netpeak data into a new excel spread sheet, make sure to remove all instances of “http://” from your dataset for the VLOOKUP to work properly.

Full_data_set_001

Now you have a list of people who are using your product, their email, their twitter handle, their website and its authority metrics.

From here you can identify influencers just going off Domain Authority and Twitter followers.

Q2: What Websites Are Our Users Reading and Sharing?

 Step 1) Get Your Users Twitter Handles 

The Full Contact Excel Sheet doesn’t give you Twitter handles, but it gives you Twitter Profile URLs, so just copy you users’  Twitter Profile URLs into a new column, and use the following find and replace command:

find and replace for twitter handles

Unfortunately, you can’t use “@” in the replace field, so you will have to add @ to a new cell and concatenate it on to the Twitter user names.

Step 2) Follow Richard’s “How To Use Twitter Data for Really Targeted Outreach” Process using TAGS
Step 3) Analyze what your users are sharing

Here’s a look at domains shared and Domain Authority from Twitter users I have exchanged emails with in the last two weeks.

List_of_sites_my_users_are_sharing

There’s some interesting finds here and a lot of opportunities for potential content placements.

If you have a well segmented email marketing list or CRM, you can break this down into specific user groups and  create content that appeals specifically to their segment and place it on sites that you know they are reading and sharing. It’s a huge opportunity for retention marketing that really harnesses the power of data.

Example Use Cases:

Have a segment of power users? – The sites they share frequently would be perfect for a contest

Have a segment of users who have signed up for your email newsletter, but never purchased your product? – A guest post on a frequently shared site about the basics of your product might be perfect for converting a few readers and pushing email subscribers through your sales funnel.

Trying to place a piece of content? – make sure its a site your customers actually care about and will see

Have any more use cases? Please share them in the comments below!


Stay Updated: Sign Up for Webinar & New Blog Alerts

7 thoughts on “Mining User Data in Excel with the FullContact API

  1. Javier Sanz says:

    Great post, John!

    Just mailed you. We made the process easie with ManyContacts, giving you a nice way of collecting those emails of your audience.

  2. Kevin Wiles says:

    hey

    When I try and run this I am given an error saying “Compile Error – Can’t find project or library”

    Any ideas what could be causing this issue?

  3. John-Henry Scherck says:

    Hi Kevin,

    I think we cleared this up on Twitter, but if you have any more questions, please don’t hesitate to reach out :)

    Please let me know how it works for you as well!

    JH

  4. Spook SEO says:

    Amazing feature! I’m always amazed at what tools like fullcontact and IFTTT can do.

    Kudos to you guys for creating something like this! Cheers!

  5. Xavier says:

    by chance does anyone has the Xpath for retrieving page name, like and talking about this for a facebook page? I tried many time with different combinaisons but couldn’t succeed.

    The twitter ones worked perfectly fine it was very usefull thanks :)

  6. Hey Xavier,

    With Facebook, you actually need to be a little more tricky – you have to scrape the JSON. just go to http://graph.facebook.com/(biz page)
    http://seogadget.com/wp-admin/post.php?post=14172&action=edit#comments-form
    For SEOgadget it’s http://graph.facebook.com/seogadget

    Happy scraping!

  7. Xavier says:

    Hi John-Henry,

    You made my day! I have just tried with Json syntax & FB opengraph and I got the data in a blink of an eye! Thanks a lot for your help.

Comments are closed.