Tutorial : Watch your Competitors’ Facebook Pages with Google Docs

Following the lessons of an SEO post explaining how to parse a batch of information with Google Docs, I got a sudden inspiration… why not do this with the information from the Facebook API? Yes, my geek side has gained the upper hand again.

Anyway, the result is here and you can use it as you wish. This tool has been built on Google Docs and it allows you to watch the development of the number of fans of your competitors’ Facebook pages . Even better, this tool comes with a tutorial to re-build it and adapt it to your needs.

See the Spreadsheet

Before we start

Marketers and community managers are justifiably debating about  social media objectives and KPIs all the time. I would like to say that yes, I totally agree that quantity is not a goal for itself, and I am still convinced that engagement has much more value. However, this reporting tool is useful because:

• The more fans we have, the higher the probability to come across the people that are going to build our community;
• To have a big number of fans is a trust indicator and can reassure potential customers (and could play a role in Google’s Panda Algorithm);
• Identifying when competitors’ fan bases are growing exceptionally fast can be an easy way to find new good ideas.

So after this short point about social media, let’s start the realisation of the spreadsheet.

Facebook Graph API, a frightening name for something that’s within everyone’s reach!

The first step is to understand how to use the Facebook Graph API. For that, you need your internet browser and the unique ID of a Facebook page.

For Non-Customized Facebook URLs:

• http://www.facebook.com/pages/globalholidays/280670926824
• http://www.facebook.com/pages/Flight-Centre/54103434078

For pages with a Vanity URL:

• http://www.facebook.com/laterooms
• https://www.facebook.com/travelodgeuk

If you add the ID to this URL you can access the info of the Facebook Social Graph. For example:

https://graph.facebook.com/ID >> https://graph.facebook.com/Travelzoo.UK

{
   "id": "141835936104",
   "name": "Travelzoo UK",
   "picture": "http://profile.ak.fbcdn.net/hprofile-ak-snc4/188075_141835936104_2962801
_s.jpg",
   "link": "https://www.facebook.com/Travelzoo.UK",
   "likes": 16187,
   "category": "Media/news/publishing",
   "username": "Travelzoo.UK"
}

Now that you understand how to retrieve information from the Social Graph, we can start building the spreadsheet.

How to create a spreadsheet to monitor the evolution of fans for the Facebook pages of the main players of the travel industry

To build this spreadsheet you need 2 scripts (one script in Google Docs and one macro in Excel). The first one parses the information from the Facebook API. The second one enables the automatic update of the report.

1/ Create a new spreadsheet in Google Docs

You need a Google account to do that. If you need help, view the Google Docs Tour.

2/ How to create a script to find the number of « like » from a Facebook Page

Go to Tools > Script Editor… and a pop up appears

Copy-Paste this code into it:

function Fblikes(id) {

  var jsondata = UrlFetchApp.fetch("https://graph.facebook.com/"+id);
  var object = Utilities.jsonParse(jsondata.getContentText());
  return object.likes;

It creates a function to parse the batch from the Facebook Graph API. I called this function “Fblikes” but you can give it any name you like.

Now (after you saved the script), you can use a new formula in the spreadsheet. Try to type “=Fblikes(virginholidays)” (without the quotes) into a cell and it is going to return the number of likes for the Virgin Holidays Facebook Page (note: virginholidays is the ID of the page).

Column E in my document has been generated using this formula and the results are updated every time someone opens the file.

3/ Save results from =Fblikes()to keep a historical record

To have a historical view of several pages, you need to save the results regularly. The simplest way to do that is to copy-paste the results into a new column each time.

You can do it manually once a week, but it can become tiring quite quickly. That is why I am using a second script.

Go back into the Script Editor and paste this code (please don’t show it to a real developer, it has been thrown together)

function UpdateFblikes() {
  var sheet =SpreadsheetApp.getActiveSheet(); //define spreadsheet name
  var column = sheet.getRange("O1").getValue();
  var row = 4;
   Utilities.sleep(4000);  // Slowdown the script (4 second)
   var nblike = sheet.getRange("E4:E60"); // Select and copy the column
   nblike.copyValuesToRange(sheet, column+5, column+5, row, row+56); // Paste the data
  sheet.getRange(3, column+5).setValue(new Date()); // add date in column title
   sheet.getRange("O1").setValue(column+1);  // Incremente variable
}

The comments in green should help you to understand what I did, in case you want to improve it. The counter in cell O1 tells it in which column to paste the data (normally it should be the next empty column).

You need to click the floppy disk icon to save, then (still in the script window), you have to go to Triggers and set it up similar to the one bellow. Thanks to this you don’t need to open the file to update it, it will do automatically every week.

In case you have issues with the time, go to Files > Properties and check your time zone.

Now you know everything you need to re-build this file and maybe create one that is even better than mine. You can benchmark your competitors, but you can also use it to monitor all the pages of your website that have a like button.  And if you have some other good ideas, please share it!