After posting Analysing WordPress post velocity and momentum stats with Google Sheets (Spreadsheet) Tony quickly came back with WordPress Stats in R, which includes a handy custom function for querying the WordPress Stats API. Something I wanted to do in my original post was to generate sparklines for post activity. I wasn’t able to find a way of doing this in Google Sheets  because of the number of data points (n. 366) and didn’t think Google Apps Script would handle the dataset because of timeouts. So given the start Tony had made with R it seemed like the ideal opportunity to refresh my R skills.

So below is the result (here’s the interactive version), which is followed by an explanation of how it was made.

SparkTable of postview 

Processing the data in R

Thanks to the code provided by Tony it’s easy to get postviews data back from WordPress using:

wp.postviews=wordpress.getstats.demo(APIKEY, BLOGURL, 'postviews', days='366', end='2012-12-31',  period='day', limit=-1)

This returns a data frame that looks like this:

Note that 478 different post_titles are returned compared to 178 post_permalinks showing some of the data is missing and in fact wrong. This isn’t important for this particular project. I’ve uploaded a sample of the returned data here. The data returned includes a day stamp, post title and number of views. Posts with no views on that day are not returned.

For the sparklines I initially headed off down a dead end (lines 99-107) making a pivot table in R. This wasn’t entirely wasted effort because the process of casting this and handling null values was used later in the project.  Having got stuck at this point searching for more information on sparklines in R turned up this question on stackoverflow and this set of slides referencing the sparkTable package. Initially I tried dropping and renaming some of the columns from the original data but on line 96 when trying the reshapeExt I got:

Error in `[<`(`*tmp*`, , attr(dat, "reshapeLong")[["timevar"]],  : 
  replacement has 32504 rows, data has 32538

Searching for variations around the error message didn’t turn anything up and if you know what is wrong I’d be very grateful for guidance. Instead I decided to follow a hunch and instead of using partial time series data filled in the blanks by casting and then melting i.e. from this:

data from worpress

to this (filling in missing data with ‘0’)

data from worpress casted

before going back to this

data from worpress then melted

Doing this got me past reshapeExt and outputting a sparkTable as html (the other option is LaTeX) embedding 478 sparkBars as png images. The final step was to wrap the html using jQuery DataTables (the main thing was to add <thead> and <tbody> tags). 

Was it worth it? I’ll let you decide if the product is any good, the process of doing it was definitely useful. So how would you improve it?


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

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


  • 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 = ""+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;

Here's some posts which have caught my attention this month:

Automatically generated from my Diigo Starred Items.


It looks like Twitter are finally rolling out the option to download all your tweets. As well as providing a nice offline search interface it appears that “the archive also includes CSV and JSON files, the latter complete with each tweet’s metadata”. I’m looking forward to see the data visualisations/mashups people come up with around their data. 

The Twitter API has long allowed you to extract a user’s status updates the limitation being you can could only get the last 3,200 tweets. This is something Sheila MacNeill discovered when she tried vizify’s ‘Year on Twitter’ tool.

the archive of meExporting tweets was something I looked at in 2011 in the cryptically titled Google Spreadsheets and floating point errors aka when is 65078736491511804 + 1 = 65078736491511808 (and automatically archiving your Twitter Status Updates). With this I’ve got a record of my tweets going back to April 2010 which is triggered to update itself every week. A reason I do this is often I need to find things I’ve previously said in ‘the archive of me’.

Here’s the template (File > Make a copy) and follow the instructions if you want to try (please be aware of the Twitter Developer Rules of the Road). I’ve updated the code to make it compatibly with version 1.1 of the Twitter API. One of the options I’ve added is a JSON dump which is saved to your Google Drive. It only took two lines of code using Google Apps Script HT +Romain Vialard 

  var blob = Utilities.newBlob(Utilities.jsonStringify(json), "application/json", filename);

[The JSON dump is a bit buggy – some issues with character escaping somewhere]


The 2013 New Year’s greetings, from the French advertising and public relations company Publicis Groupe by their CEO Maurice Lévy is making multiple appearances in my circles

When I saw it my immediate thought was how did they do it? In the official press release they say:

It’s just one of the many very human interpretations of the standard YouTube functionality taken for granted today.

Looking for YouTube player functionality left me scratching my head. Looking at the source gave a big clue:

Publicis Groupe custom tab code (click to enlarge)

… basically the entire video area including title and controls is an embedded iframe. This allows Publicis Groupe to create their own Flash based player with custom functionality for pause, play etc.

To get the iframe into their YouTube channel they used YouTube’s Custom tab settings which are available on Branded Channels.

Simple but very effective.

Alistair Brown has written an interesting post on the LSE Impact of Social Sciences blog - Proving dissemination is only one half of your impact story: Twitter provides proof of real-time engagement with the public. The post highlights the case of how a journal paper was picked up by a university media office, which ended in the author being interviewed on BBC Radio 4’s Today programme. As part of this Alistair highlights that:

REF impact involves an assessment of “significance” as well as “reach,” so the mere fact that research has been disseminated to a wide audience does not constitute an impact by itself; one has also to show the effect it has on those to whom it is disseminated. For this reason, citing the fact that a researcher has appeared on a primetime radio show with several million potential listeners might be one element of an impact statement, but one needs also to evidence that the audience has actively listened to what was being put out, and that it has affected, changed or benefitted them in some way

In the age of the second screen Alistair goes on to highlight how Twitter can be used as evidence of engagement, listeners tweeting personal reflections, feedback or just disseminating the information more widely. But as Alistair points out:

When a piece of academic work receives broadcast media coverage, then, it is useful to have a strategy in place to gather emerging responses, and it is also far easier to do this as it happens rather than retrospectively.

A strategy is required because, as Alistair points, out the Twitter search is limited to the last 7 days. While there are ways to view this activity in realtime how do you capture the evidence.  Here’s my response to the problem:

Here is the spreadsheet template I mention. So have you got a strategy for recording impact evidence from Twitter?


Getting Creative with Big Data and Google Apps I was recently asked to write a guest post for Big Data Week on using Google Apps as an interface for Big Data. For the post I decided to revisit an old recipe which uses Google Sheets (Spreadsheets) and Google Apps Script to interface the Twitter and Google Analytics API. One of the results is the bubble graph shown below which shows who has been tweeting my blog posts, how many visits their tweet generated, the number of retweets and how many followers the person has (click on the image for the interactive version). You can read more about his this was done and get a copy of the template in Getting Creative with Big Data and Google Apps

Visits, rewteets bubble graph


Update 16/06/2014: This idea has been revisited by the Elevate team at University Campus Suffolk. You can read more about and get a copy of the code here

Update 18/02/2015: I've revisited the idea of issuing Open Badges with Google Apps Script and embedding in Google Sites which improves this idea further. You can read more about this here.

 Open Badges Issuer Gadget for Google SitesWant to issue badges in Google Sites? That was the challenge I set myself. My solution is the Open Badges Issuer Gadget for Google Sites. This gadget wraps the Mozilla Issuer API to allow you to issue badges from a Google Site. To use the gadget is insert into a Google Site and prefix (base url) is set for your Assertion JSON. To allow users to collect their badges direct them to the web address of your Site page containing the gadget adding ?claim_code={assertion url post fix}.

For example, if my Site page with the Issuer Gadget is found at and by Assertion files are all found in the directory this would be my base url. If one of my Assertion files in this directory was v1mhaws.json, to claim the badge for that person I’d send them a link to

Get the Open Badges Issuer Gadget for Google Sites

The Open Badges Issuer Gadget for Google Sites is located here:

To add to your own site

  1. Open/create a page on your Google Site
  2. Selecting Insert > More gadgets,
  3. Add gadget by URL inserting the address
  4. Insert a prefix (base url) for your Assertion JSON files (you can leave this blank if the host site varies) and click ‘Ok’
  5. For each or collections of Assertions direct users to visit the page your gadget is hosted on adding ?claim_code= followed by a comma separated list of the remainder of you Assertion file locations

Try it

If you’d like to try the gadget complete this form and you’ll be issued with a Gadget Issuer User Badge. Get the question right and you’ll also get the Gadget Issuer Gold Badge.

How the gadget works

For those of you unfamiliar with gadgets/widgets they are an easy way to embed content in other gadget/widget compatible sites. The majority of gadgets are simply XML wrappers for HTML content. The great thing is that gadgets can include JavaScript that doesn’t get sanitized/stripped out. If you want more information about making gadgets see My First Google Gadget. The source code for the is linked to above but can also be viewed on GitHub. Essentially it’s a wrapper for Mozilla’s Issuer API

The Issuer API is a [java]script that can be dropped-in to any badge issuer's website to provide a way for users to add an issuer's badges to their backpack.

Feel free to modify the gadget code to handle the success and error callbacks.

Yep I’m issuing badges from a Google Form/Spreadsheet, here’s how

If you tried the demo you might be wondering how I went from a Google Form to issuing some badges. Here’s how. Google Spreadsheets includes Google Apps Script, a cloud scripting language with uses the JavaScript Syntax to automate processes across Google products and third party services and deploy/publish custom applications and data. Apps Script includes a Content Service, which amongst other things lets you publish JSON data. As the metadata blobs behind open badges are JSON based we can use Apps Script to process the form responses, email the recipient and create the JSON … well almost.

An issue with JSON files generated by App Script  is security measures put in place by Google prevent the cross-domain use when called by AJAX as used by the Issuer API. So currently I have to proxy the JSON via my webhost (Mozilla could fix this by also permitting JSONP, which can also be produced by Apps Script. I imagine this is however low priority. If you have any thoughts on other ways leave a comment).

Here’s a copy of the code running behind my Google Form (you’ll also need to include a rollup of the CryptoJS sha256 library to hash and salt the recipient’s email address).

[A pointer if you want to extend this work is you might want to use the native NoSQL style ScriptDb Service part of Google Apps Script to prepare and serve assertions. Also I found the Open Badges Validator is very useful for testing your Assertions.]

Related work

Some recent posts/upcoming events that have influenced this post are:

So what do you think? Inspired to issue badges from Google Apps? Have I missed anything? What isn’t clear? I look forward to your thoughts ;)


Amber (@ambrouk/at JISC/of UKOER) is leaving JISC today and Lorna has written a lovely post wishing her goodbye. I’ve known amber since my days in JISC RSC Scotland North & East and she has been a fantastic supporter of my work forever ‘pimping my shit’.

Amber thank you and good luck in the new job. Here are a some flowers for you (made of your Twitter friend network, degree 1.5, nodes scaled by betweenness centrality and clustered with Spin-glass community finding algorithm ;)

Here's some posts which have caught my attention this month:

Automatically generated from my Diigo Starred Items.