Jisc CETIS MASHe

Like other wordpress.com and self-hosted wordpress blogs using Jetpack I got my annual report. Some of the headlines include:

• blog was viewed about 200,000 times in 2012
• 122 new posts, growing the total archive of this blog to 514 posts
• busiest day of the year was September 7th with1,149 views. The most popular post that day was IFTTT: IF I do THAT on {insert social network/rss feed/other} THEN add row to Google Spreadsheet.

The report then lists the top posts (minus the totals, which are available from the stats panel)

These are the posts that got the most views on JISC CETIS MASHe in 2012.

1. Export Twitter Followers and Friends using a Google Spreadsheet 77 COMMENTS March 2011
2. Twitter Archiving Google Spreadsheet TAGS v3 17 COMMENTS January 2012
3. The best Google Spreadsheet Event Manager (w/h Sites, Contact, Calendar integration) ever, ever, ever 4 COMMENTS November 2010
4. Twitter: How to archive event hashtags and create an interactive visualization of the conversation 13 COMMENTS November 2011
5. Using Google Apps Script for a event booking system (Spreadsheet to Calendar & Site | Form to Spreadsheet, Email and possible Contacts) 23 COMMENTS March 2010

These results are biased towards posts that have had the most time to collect views. What about the posts made later in the year? Or, other posts like IFTTT that attracted a lot of traffic in one day?

Remembering Tony Hirst’s Making Use of WordPress Stats which pulls data from the WordPress Stats API into a Google Sheet (Spreadsheet) I created a modification that pulls in a years worth of stats and lets me explore some of these questions. Below is a summary of WordPress Postviews Stats Analysis for MASHe (columns are sortable by clicking on the column heading).

Notes

• AVERAGE of Views is the average post views per day. Days with no views are not counted
• MAX of Views is the maximum post views in one day
• SUM of Views is the total number of views a post has received
• COUNTA of Views is the total number of days a post received at least one view

This reveals posts like Feeding Google Spreadsheets (from October 2012) and Mozilla Open Badges Issuer Gadget for Google Sites (December 2012) have good average daily views basic projections putting them in the top 10 for 2013.

There’s more digging to be done, but in the meantime if you want to have a look at your own wordpress stats grab a copy of the template below and have a go (and if you make any improvements/useful insights leave a note ;)

*** WordPress Postviews Stats Analysis Template ***
[File > Make a copy for an editable version]

Creation notes

The code comments highlight the main gotchas:

function wordpressStatPostviews(api_key, blog_uri, end, days, limit) {
  // build url for api. Found better results using json rather than csv (csv didn't get  years worth)
  var url = "http://stats.wordpress.com/csv.php?api_key="+api_key+"&blog_uri="+blog_uri+"&end="+Utilities.formatDate(end, "GMT", "yyyy-MM-dd")+"&days="+days+"&limit="+limit+"&table=postviews&format=json";
  var output = [["Link","Title","Title - Short","Date","Views"]]; // initialise return array
  try {
    var options = { "method" : "get" }; // initialise options for UrlFetchApp
    var response = UrlFetchApp.fetch(url, options); // fetch url
    if (response.getResponseCode() == 200) { // if response
      var raw = Utilities.jsonParse(response.getContentText()); // parse text respose into json
      raw.reverse(); // reorder to oldest first - no real need but helped with debugging
      for (i in raw){ // data is returned as object per day so iterate across
        var postdate = raw[i].date; // pull post date (as string) considered converting to date object but not required
        for (j in raw[i].postviews){ // in each date object there is array of views per post, interate across to push into returned 2d array 
          var apost = raw[i].postviews[j]; // abreviating
          // noticed a bug in returned data. Views for homepage have correct post_id but post_title is pulled from last interation
          var title = apost.post_id === 0 ? "Homepage" : apost.post_title; 
          // creating a short title for published table (no cell wrapping)
          var titleShort = title.length > 50 ? title.substring(0,50)+"..." : title;
          // push row to output
          output.push([blog_uri+"/?p="+apost.post_id,title, titleShort, postdate,  apost.views]);
        }
      }
      return output;
    }
  } catch(e) {
    throw e;
  }
}