Mindful Metrics

How to start tracking historical Twitter data using Klipfolio

Summary - It’s a problem any data-driven marketer has likely run into at one point or another. How to get accurate historical Twitter data. The problem: Twitter doesn’t make historical data available to its users. Thankfully, Klipfolio has a solution. All you need is a Twitter account, Google account and Klipfolio.


It’s a problem any data-driven marketer has likely run into at one point or another.

How to get accurate historical Twitter data.

The problem: Twitter doesn’t make historical data available to its users.

Thankfully, Klipfolio has a solution.

Here’s what you need:

  • Twitter account
  • Google account
  • Klipfolio

That’s it – let’s go!

The problem with Twitter data

Twitter provides a few metrics as part of its in-house analytics tool.

Impressions. Profile visits. Mentions.

But historical follower data isn’t included in the list.

That gives you a few options:

  • Manually check in on your follower account every day: Obviously, this is not appealing. It’s theoretically do-able, but requires you to remember to check in every day (plus weekends).
  • Pay for a service that gives you historical Twitter data: A workable solution but it’s expensive.
  • Glean what you can from Twitter analytics: Twitter analytics does provide some data. The problem is that’s not really in any usable format. For example: You can get historical follower data but only for a fixed period (usually 28 days). You also get a monthly “summary” which tells you how many followers you lost or gained during a month. It might be useful for amateur Twitter users hoping to get a quick glance at how they’re performing. But it’s nothing that could be considered particularly useful from a data analysis perspective.

If these work for you, great – you can stop reading now!

Otherwise, read on for how you can start measuring historical data using Klipfolio.

Getting historical Twitter data using Klipfolio

The following solution will allow you to track the following metrics for Twitter:

  • Followers: The number of accounts that follow your account.
  • Friends: The number of accounts your account follows.
  • Listed count: The number of lists your account appears on.

By doing this workaround, you can record what each metric is for each day. For example: You’ll be able to go back and see how many followers you had on January 3 (assuming you had started tracking before then).

Before we get started, a small but important note:

Data for twitter will only be available from the moment you start collecting it in your Google Sheet

A step-by-step guide to getting Twitter data

Follow these steps to start acquiring historical Twitter data.

Step one: Set up a Twitter datasource

First, you will need to set up your Klipfolio datasource.

This is the actual datasource that retrieves the information from Twitter.

To create a new datasource:

  1. Log in to Klipfolio
  2. Go to the Connector Gallery
  3. Select Twitter
  4. Pick Account Stats (either one)
  5. Select a valid twitter authentication token (if you don’t have one, create it)
  6. On the next page, enter the Twitter Handle that you wish to enter (You can keep the query in basic mode)
  7. Click 'Get Resource'
  8. Once the data has been loaded, continue to naming and saving the outsource (The refresh interval here is irrelevant, as the next steps will refresh the datasource for you)

Important note: Once this is completed, make sure to keep track of the datasource ID, highlighted below. We will need this later.

Step two: Get the Klipfolio API key

The Klipfolio API key is what allows you to refresh the Twitter datasource. This will grab the desired data from Twitter and then push it to the Google Sheet (which we’ll set up in the next step).

To generate a Klipfolio API key:

  1. Sign into your Klipfolio account.
  2. Click on your Account name and select Account.
  3. Beside General Information, click Edit to open the Edit My Profile screen.
  4. Beside API Key, click Generate New API Key. The app automatically generates a new key.
  5. Click Save.

Your API key is now visible and available on your profile page.

Step three: Set up a Google Sheet

A Google Sheet is where we’ll be storing the data we pull from Twitter.

  1. Log into Google Sheets
  2. Create a new Google Sheet
  3. Add the following headers

Step four: Pull data from Twitter into your Google Sheet

A script is what will help us push the data from Twitter to your Google Sheet.

To do that you need a script.

Thankfully, Klipfolio has already created the script for you. So all you need to do is copy and paste.

To get started:

  1. In your Google Sheet, go to Tools < Script editor
  2. Copy and paste the following code:
  3. /*
    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]);
    
    }
    
  4. Replace XXX with your API key
  5. Replace YYY with your Twitter datasource ID

Step five: Set the intervals for when you want to pull in Twitter data

We need to run the script once a day to pull in the data from Twitter.

To set this up:

  1. Save your script
  2. Set up the trigger
  3. Enable permissions to allow Google to run the script (if necessary)
  4. Set up the timer to run once per day (at whatever time you wish)
  5. This means your Google Sheet will be automatically updated every day with your Twitter data.

Step six: Connect Klipfolio to your Google Sheet

Almost there!

The final step in our process is to create a new datasource in Klipfolio.

This will connect Klipfolio to the Google Sheet we just created.

  1. Head over to the Connector Gallery
  2. Select Google Drive
  3. (If you still haven’t authenticated Google Drive with Klipfolio, follow these instructions)
  4. Navigate through your Google Drive folders (choose either My Drive or Shared with me to locate your file).
  5. Select the file to be uploaded. (Important! Make sure your Google Drive spreadsheet name is no longer than 31 characters.)
  6. Click Get File. Verify that the correct data is displayed.
  7. Click Continue.
  8. Type a Name for the data source and set the Queue for Refresh interval.
  9. (Optional) Select the groups with whom you wish to share your file, and assign the required sharing rights.
  10. Click Save.

That’s it! You’re now tracking Twitter followers in Google Sheets

Any issues?

Check to see if either of the datasources you are using is encountering an error.

Aside from that, you may also need to periodically re-authorize one or both of the sources.

Interested in learning more? Check out Klipfolio's Twitter Analytics Reporting Tool

Share

Originally published April 3, 2018, updated Jun, 13 2019

Related Articles

Why customer support should be metrics-driven

By Akshaya SrikanthJuly 2, 2019

PowerMetrics Blog Banner - Bolts of yellow lightning with dark cloudy sky

In Beta no more! PowerMetrics for all.

By Katya Zeisig, Marisha SestoMay 17, 2019

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

Start building dashboards for your team and your clients.