Happy Dashboarding

Use Klipfolio to Track Your Twitter Data

One of the things that is often requested but we've always had trouble with is getting historical data for a Twitter handle/account.

The reason? Twitter does not make historical data available via its API. In order to get this data in any dashboarding service, you would need to use a paid service, like Quintly.

With a bit of a workaround, however, you now have the ability to track this information in a Google Sheet!

The following example details how you can set up a Klipfolio datasource to track the Followers, Friends, Listed, and Favorites count for a Twitter handle daily in a sheet that looks like this:

Use Klipfolio to Track Your Twitter Data |Twitter datasource table

For this to work you will need:

Unfortunately this does not go backwards to get previous account data - you can only start tracking from the time you set it up onwards.

1. Set up the Twitter datasource

First, you will need to set up your Klipfolio datasource. This is the actual datasource that retrieves the information from Twitter.

To get stared, create a new datasource and from our Connector Gallery, select Twitter.

Use Klipfolio to Track Your Twitter Data | Select Twitter

For this section, pick Account Stats (either one).

Use Klipfolio to Track Your Twitter Data | Step 2: Pick a Data Request

Select a valid twitter authentication token, or create a new one.

Use Klipfolio to Track Your Twitter Data | Step 3: Connect an Account

On the next page, enter the Twitter Handle that you wish to track. You can keep the query in basic mode.

Use Klipfolio to Track Your Twitter Data | Step 4: Configure Data Source

If you are using custom mode, this is what the query would look like:

Use Klipfolio to Track Your Twitter Data | Query URL

Click 'Get Resource', then once the data has been loaded, continue to name and save the outsource. The refresh interval here is irrelevant, as the next steps will refresh the datasource for you.

Finally, once you have your datasource, make sure to keep track of the datasource ID, highlighted below, which we will need later.

Use Klipfolio to Track Your Twitter Data | Data Source: Twitter Account Stats

2. Get the Klipfolio API key

You will need a Klipfolio API key to allow you to refresh the Twitter datasource, and to grab the data from it that is pushed to the sheet. You can follow the steps outlined here to get your Klipfolio API key. Keep this API key handy, as we will need it soon.

3. Set up the Google Sheet

To create the actual Google Sheet that you’ll be putting the historical information into, log in to Google Sheets and start a new one:

Use Klipfolio to Track Your Twitter Data | Google Sheets

Set up the sheet with the following headers:

Use Klipfolio to Track Your Twitter Data | Twitter table headers

4. Set up the Script

Now that you have a Google Sheet to push data to, you’ll need a script that actually does the pushing of the data. Never fear, we have already created this script for you - the only thing you need to do is copy and paste.

To get to the script, go to Tools -> Script Editor:

Use Klipfolio to Track Your Twitter Data | Twitter historical data

Here you will copy and paste the code supplied at the bottom of this post. There are two sections where we need to adjust the code. Near the top of the code, replace XXX with your Klipfolio API key (see step 2 above) and replace YYY with ID of the Twitter datasource you created (see step 1 above).

Use Klipfolio to Track Your Twitter Data | function count

5. Set up the Trigger

To set up the trigger that runs the script once a day, first, save your script.

Use Klipfolio to Track Your Twitter Data | Twitter historical data tracking script

Next, set up the trigger:

Use Klipfolio to Track Your Twitter Data | Twitter historical data tracking trigger

At this point you may be asked to enable permissions to run your script.

Use Klipfolio to Track Your Twitter Data | authorization required

You can simply sign in to your Google account, and click 'Allow'.

Once this is done, you can set up the timer to go once per day, at whatever time you wish:

Use Klipfolio to Track Your Twitter Data | Current project's triggers

Now, every day our Google Sheet will be automatically updated with the information from the twitter account we are tracking.

6. Connect Klipfolio to your Google Sheet

The final step is to create a new datasource in Klipfolio, connecting to the Google Sheet you just created. Start off in our Connector Gallery and select Google Drive.

Use Klipfolio to Track Your Twitter Data | Service Connectors

Then, follow these instructions to get your Google Sheet into Klipfolio as a datasource you can use.

If you are having any issues with the datasources not working, check to see if either of the datasources you are using is encountering an error. You may need to periodically re-authorize one or both of the authentication tokens.

Hope this helps out!

Best regards,
Joshua Cohen-Collier
Technical Support Engineer, Klipfolio

Code to paste into Script Editor:

/*
Gets the followersCount,friendsCount,listedCount and favouritesCount from a twitter datasource, and posts it to a linked Google Sheet.
*/
function trackAccount() {

var apiKey="";//Enter your API key here
var DSID="";//Enter your twitter datasource ID here

var sleepTime=4;//Number of minutes to wait before checking datasource

var sheet = SpreadsheetApp.getActiveSheet();//Get sheet this script is linked to

//Refresh datasource first
var URL="https://app.klipfolio.com/api/1.0/datasource-instances/"+DSID+"/@/refresh";
var options = {
"async": true,
"crossDomain": true,
"method" : "POST",
'muteHttpExceptions': true,
"headers" : {
"kf-api-key" : apiKey,
"Content-Type": "application/json"
}
};
var response = UrlFetchApp.fetch(URL, options);

//Get date this was sent
var d = new Date(),
month = '' + (d.getMonth() + 1),
day = '' + d.getDate(),
year = d.getFullYear();
if (month.length < 2) month = '0' + month;
if (day.length < 2) day = '0' + day;
var rowDate=[year, month, day].join('-');

Utilities.sleep(sleepTime*60000);// pause in the loop for specified number of minutes, so as to avoid datasource not being refreshed when we update

//Get Datasource data query settings
URL="https://app.klipfolio.com/api/1.0/datasource-instances/"+DSID+"/data";
options = {
"async": true,
"crossDomain": true,
"method" : "GET",
'muteHttpExceptions': true,
"headers" : {
"kf-api-key" : apiKey,
"Content-Type": "application/json"
}
};

//Run API query
response = UrlFetchApp.fetch(URL, options);

//Check for errors
if (response.getResponseCode()!==200)
{
return;
}

//Get text of response
var responseText=response.getContentText();

//Get followers count
var followersCount=responseText.substring(
responseText.indexOf('followers_count')+17,//start index
//^above number + index of , insto substring starting at above number
responseText.indexOf('followers_count')+17
+
responseText.substring(responseText.indexOf('followers_count')+17).indexOf(',')
);

//Get friends count
var friendsCount=responseText.substring(
responseText.indexOf('friends_count')+15,//start index
//^above number + index of , insto substring starting at above number
responseText.indexOf('friends_count')+15
+
responseText.substring(responseText.indexOf('friends_count')+15).indexOf(',')
);

//Get listed count
var listedCount=responseText.substring(
responseText.indexOf('listed_count')+14,//start index
//^above number + index of , insto substring starting at above number
responseText.indexOf('listed_count')+14
+
responseText.substring(responseText.indexOf('listed_count')+14).indexOf(',')
);

//Get favourites count
var favouritesCount=responseText.substring(
responseText.indexOf('favourites_count')+18,//start index
//^above number + index of , insto substring starting at above number
responseText.indexOf('favourites_count')+18
+
responseText.substring(responseText.indexOf('favourites_count')+18).indexOf(',')
);

//Write to sheet
sheet.appendRow([rowDate, followersCount,friendsCount,listedCount,favouritesCount]);

}

Share

+100 services and pre-built metrics to choose from.

Start building dashboards for your team and your clients.

Free for 14 days ● No credit card