I’m a big fan of creating your own SEO tools with Google docs, so when I found out about the Klout API, I immediately began finding ways to put that into a practical tool for SEOs. I’ve come up with one potential use which I will show you here, but really, the possibilities are vast for this sort of thing. In this post, I’ll show you how to pull useful data into a spreadsheet and apply the Klout API to get some actionable numbers for your social activities. Specifically, I’ll show you how to pull a list of tweets mentioning your business/organization/name/whatever and grab Klout scores for each Twitter handle automatically with the Klout API. Sound awesome? It sure is! Here we go with the tutorial.
Remember the basic scraping methodology we used in the Google docs rank checker post? We’re going to be using the same function (=importxml) but on a different search engine: Blekko! Blekko is the little SEO secret that’s vastly underutilized. And, since Google killed off Realtime Search recently (and most of us are still mourning its passing, even though it may soon return), we’ve been in dire need of a way to search Twitter without resorting to Twitter’s proprietary search. Enter the /Twitter Blekko slashtag!
Searching Blekko for anything with the /Twitter slashtag amended will yield a list of tweets using the term. This could be applied to brands, subjects, people, phrases, anything! You can even look for usage of specific hashtags if you put the hashtag in quotes (IE: “#seo”). So, we need to get our spreadsheet to form a Blekko search URL. If we run a quick search through Blekko with a basic query and the /Twitter slashtag, we can see that their search URL structure looks like this:
blekko.com/ws/keyword+/slashtag
All we need to do now is set up a concatenate function that will put our desired term and the /Twitter slashtag together. If your keyword was in cell A1 and your /Twitter slashtag was in cell A2, your formula would look like this:
=concatenate(“blekko.com/ws/”&A1&”+”&A2&””)
Take a look at this image for reference:
Alright, now you’ve got your search URL set up, it’s time to scrape that puppy. We can’t just use the importxml function that we used with the Google docs rank checker, since Blekko’s SERP markup is different. If we look closely at the source code for a Blekko /Twitter SERP, we can see that the divs that hold the results are contained within “li” elements. So, our formula for importing that data will look like this:
=importxml(B1,”//li[@class='clear']/div[@class='content']“)
The cell “B1” holds the concatenated search URL that we just constructed. This import will pull the entire tweet in the search results. We don’t need the entire tweet to get Klout scores, so we’ll just split it off from the rest of the tweet by using the =SPLIT function with a space as the delimiter.
=SPLIT(E3, “ “)
This formula will put everything that’s separated by a space into a new cell, so you’re left with the user’s Twitter handle in the first cell and the rest of their tweet scattered among a number of other cells. Go ahead and hide the columns holding the tweet; we aren’t going to need those.If you’re brand conscious, your business Twitter handle probably matches your company’s name pretty closely. Or, your name is your handle, and so on. At any rate, you’re going to have repeat tweeters. Because we don’t want to waste our oh-so-precious Klout API calls, we need to filter those out. Use the
=UNIQUE formula to filter out the duplicates. Put the array that you want to filter in the formula, as shown here:
=UNIQUE(F1:F100)
This will spit out a list of unique Twitter handles in the selected column, giving you a nice, tidy list of everyone who’s been talking about you on Twitter! Now we’re going to find the Tweeps with the most Klout in this bunch. And, while catering to only the group of respondents that have high Klout scores is a good way to tarnish your brand’s image (“Too good to talk to us little guys, huh?”), this can help you identify key influencers that you’ll want to interact with on a more frequent basis. For this step, we’re going to need to integrate some Google Scripts. This is a programming language based off of JavaScript and, while it’s theoretically possible to separate out the necessary data using ONLY spreadsheet functions, adding a script to do it for us is much cleaner. I won’t go into a great deal of detail here on the Google Scriptsplatform (that would take multiple posts by itself!) I will give you the code for pulling Klout scores here:
Here’s the text if you don’t like images:
function kloutScore(sCell) {
var apikey = “XXXX”;
var url = “http://api.klout.com/1/klout.json?key=”+apikey+”&users=”+sCell;
var response = UrlFetchApp.fetch(url);
var doc = Utilities.jsonParse(response.getContentText());
var klout = doc.users[0].kscore;
return klout
}
You can just copy/paste that into a new script by accessing the Script Editor in your spreadsheet under the “Tools” menu, though you will need to format it so it reflects the function (IE: make it look like the image).
To use this formula, you’ll need your own Klout API key. And, as you can probably guess, that goes where the four Xs currently reside. This formula will compile a call to the Klout API using the specified parameters (sCell is the cell you reference with the =kloutScore formula) and return a response in JSON that is then parsed by the script to give you the raw number of the Klout score. In non-technical terms, this gets you the Klout scores for the list of Twitter handles you’ve got.
There you go! Now you’ve integrated the Klout API and Google docs. Want to know the best part about this tool? The Blekko search updates automatically in your spreadsheet, so you’ll always have current data! Whenever someone new tweets about you, the tweet and handle show up in your spreadsheet automatically. Since it auto-updates, you can keep your Klout API calls to a minimum by only grabbing scores for the “X” most recent tweeters by only applying the function on the first “X” cells.
You can use this however you want, (grab a copy of the spreadsheet) and I hope it helps in your social media efforts! What do you think? Are there other useful ways you can think of to apply the Klout API and Google docs?
