Use Klipfolio to Track Your Twitter Data

Joshua Cohen-Collier
Joshua Cohen-Collier

Published December 19, 2017, updated February 15, 2022

Use Klipfolio to Track Your Twitter Data Blog Banner

Summary - One of the things that has often been requested of Klipfolio is getting historical data for a Twitter handle/account. This blog goes over how you can start tracking it with a paid service, like Quintly. Alternatively, you can try out our brand new Metrics which stores historical data.


* 2019: We now have PowerMetrics! Metrics can store your historical Twitter data, in an easier way than this! But, if you're adamant to stick with Klips, read on...

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 Analytics 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.

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

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:

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.

Select Twitter

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

Pick A Data Request

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

Connect An Account

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

Configure Data Source

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

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.

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:

Google Sheets

Set up the sheet with the following headers:

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:

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).

Function Count

5. Set up the Trigger

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

Twitter Historical Data Tracking Script

Next, set up the trigger:

Twitter Historical Data Tracking Trigger

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

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:

Current Project 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.

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]);

}

Related Articles

Metric Stack   Cover Art (social Media) (1)

Klipfolio's Jon Taylor: Is the MQL right for my business?

By Emily HaywardApril 1, 2022

What Is Hubspot Lifecycle Stages

What are HubSpot lifecycle stages?

By Jonathan TaylorMarch 14, 2022

What is an MQL

What is an MQL?

By Jonathan TaylorMarch 2, 2022

How to Build Content Marketing Dashboard.jpg

How to Build a Content Marketing Dashboard

By Emily HaywardFebruary 16, 2022

Level up your decision makingStart building pixel-perfect dashboards for you and your team.Get Started Free