Analytics

At the Association for Learning Technology (ALT) one of the core tools we use for membership management is CiviCRM. CiviCRM has a number of ‘out-of-the-box’ reports you can use and run to summaries and analyse memberships and contributions. Given the flexibility of Civi you can also with a bit of know how create custom extensions and reporting options enabling so very sophisticated analytics. At ALT we are keen to make more use of the data we have on memberships but at the same time have limited resources and time to implement these. In this post I’ll highlight how using Google Sheets we’ve developed a framework that allows us to rapidly develop custom reporting.

Problem

If you are already a CiviCRM user you are probably already aware of CiviReports and the ability create basic custom reports which allow you to view and download data. As part of this you can also schedule reports to land in your inbox. This is great but has it’s limitations. In particular, additional setup is required if you don’t want to just report on a daily basis; you end up with tables of data, with no graphical summaries; and combining current and historic data isn’t possible.

Solution

Scheduling reports at custom intervals

CiviCRM provides an interface to schedule a mail_report. The issue many people discover is this will send reports on set intervals usually hourly or daily. You can schedule individual jobs to run a specific periods but you quickly find yourself in the world of command lines and CRON jobs. Crons are scheduled tasks run by a web server. If you have dedicated admin support this is a fairly standard task and the instructions are easy to follow. At ALT we have the option to open a support ticket with our host but this seems like a waste on time and money.

Our solution is to use a Google Sheet… well a Google Sheet with a bit of ‘juice’. The sheet is shared with our team and anyone can add a CiviReport id to Column A and choose how often it runs in Column B using a data validation list option.

deciding what reports run when

But how does this trigger our civi install to run the job? The juice is Google Apps Script, a cloud based scripting language native to Google Drive. Apps Script is a free service from Google and fortunately for us has the ability to run scripts on configured time intervals. It also has the ability to call specific urls using the build-in UrlFetchApp (similar to CURL). I’ll give you a link to this Sheet so you can setup your own later and when you do you’ll see the entire process is managed with a couple of lines of code included below:

function doTasks() {
  var doc = SpreadsheetApp.getActiveSpreadsheet(); // get spreadsheet
  var sheet = doc.getSheetByName("Tasks"); // get sheet
  var data = sheet.getRange(3, 1, sheet.getLastRow(), COL.total).getValues(); // get values
  var now = new Date(); // time now
  // for each row of the sheet interate accross
  for (var i = 0; i < data.length; i++){
    if (data[i][COL.report_id] != ""){ // if there is instance id do something
      // collect row values
      var report_id = data[i][COL.report_id]
      var type = data[i][COL.type];
      var next_run = data[i][COL.next_run] || 0; 
      // check if it's time to run the report again
      if (next_run < now && type != "never"){
        // if it is ping the report trigger
        var new_next_run = callUrl(report_id, type, {format: data[i][COL.format], ss_id: data[i][COL.ss_id], ss_sht: data[i][COL.ss_sht]} );
        // ..and record when to run again
        sheet.getRange(parseInt(i)+3, 3, 1, 2).setValues([[now, new_next_run]]);
      }
    }
  }
}

What this does is read the sheet data and then iterate across each row. If the report is overdue to be run again it calls a another custom function callUrl which will run the CiviReport and return/write when next to run.

Creating graphical summaries and combining data

By this point you may be sensing that I’m partial to solving problems with Google Sheets. With Sheets it’s fairly straight forward to manually export different reports from Civi and analyse using formula and Charts. The manual export of CiviReports can get tiresome so how can we automate this? Again we return to Google Apps Script. One of the options in CiviReports is to attach the data to the emailed report as a .csv file. From the previous example we can see it is possible to read and write data to a Google Sheet. So if we can get the .csv file from our emailed report we can write it to the Sheet … right?

This is actually more straight forward than you may think as another feature of Google Apps Script is to interact with the script owner’s Gmail.  As part of this we can search for messages and get associated attachments. Using this we can read the latest report from Civi, write the data to a sheet and with a bit of clever formula building automatically get the latest summary or custom chart. As Apps Script runs in a pre authenticated environment, no oAuth handshakes here, the code is relatively straight forward:

function processInbox(){
  var PS = PropertiesService.getScriptProperties();
  var data = PS.getProperties();
  for (var key in data) {
    if (key.substring(0, 10) == "search_str"){
      var param_raw = data[key];
      var param = JSON.parse(param_raw);
      // get last 20 message threads using serach term
      var threads = GmailApp.search(param.search_str, 0, 20); 
      // assume last thread has our latest data
      var last_thread = threads.length-1;
      if (last_thread > -1){
        // get message in the last thread        
        var msg =  threads[last_thread].getMessages()[0];
        // get the attachments
        var attachments = msg.getAttachments();
        for (var k = 0; k < attachments.length; k++) {
          // get the attachment as a string
          var csv_str = attachments[k].getDataAsString();
          // parse string as csv
          var csv = Utilities.parseCsv(csv_str);
          // create destination object
          var doc = SpreadsheetApp.openById(param.ss_id);
          var sheet = doc.getSheetByName(param.ss_sht);
          // clear any old data
          sheet.clear();
          // write new data
          sheet.getRange(1, 1,  csv.length, csv[0].length).setValues(csv);
          // mark message are read and archive (you could also label or delete)
          threads[last_thread].moveToArchive().markRead();
          PS.deleteProperty(key);
        }
      }
    }
  }
}

NB

Data protection

There are a couple of things worth noting here. Google Sheets are a fantastic collaborative environment and with this solution we can still share spreadsheets to selected people in our organisation and beyond. Something to remember though is this script runs as the sheet owner so when configuring the CiviReport it needs to go to the email address of the owner. At ALT we benefit from being a Google for Education user so our email and Drive access comes as part of the Google Apps suite. This solution could also be setup to run on a regular Google account but there are data protection issues to consider sending reports to a non-organisation contact. As such you might only want to re-use this solution as an easy way to schedule reports rather than schedule and process the data.

ARRAYFORMULA, FILTER, QUERY functions are your friends

As our solution dumps .csv data in a sheet, clearing any previous data, any formulas you have in the sheet will also be lost. We get around this by doing all data manipulations in a separate sheet which references the imported data. To save copy and pasting lots of formulas we make extensive use of the ARRAYFORMULA, FILTER, QUERY functions available in Google Sheets.

Comparing CiviEvent registrations

One scenario we have is monitoring the number of registrations to an annual conference. It’s easy for us to export registrations for previous years as static data into a spreadsheet. For the analysis we want to group the number of registrations by week number. To get a week number for the live data we create a second sheet that references it. Using the ARRAYFORMULA you can enter the reference once which is then applied to all the values in the range. For example, we get all the registration dates in column A using =ArrayFormula('2015LiveData'!B:B) in cell A1 and then extract the week numbers in column C by using the formula =ARRAYFORMULA(IF(ISBLANK(A2:A),"",WEEKNUM(A2:A))) in cell C2.

ArrayFormula is your friend

Setting up your own copy of Schedule CiviCRM Reports

If this is a project you’d like to use and/or build upon you can copy a blank version of our template below:

Schedule CiviCRM Reports
[While signed in File > Make a copy]

Once you have a copy open Tools > Script editor to see all of the project code and instructions for setting up.

I’ve hopefully given you enough to go on to setup but feel free to leave a comment if you get stuck or have any questions.

Enjoy!

2 Comments

Update 10/02/2015: A recording (slides/audio) is available here

Today I’ve been invited to ALT’s White Rose Learning Technologists SIG to talk about Learning Analytics and educational mining of Twitter. The Learning Analytics part of this was something I was somewhat reluctant to do as I have recently realised how much I don’t know about this area. Another factor was my fear that learning analytics is being eroded by ‘counts’ rather than actually caring about the learner. This appears to be a shared concern and I was fortunate to recently see a talk given by Dragan Gasevic which addressed this. A recording on his session is currently here and Dragon’s slides are on Slideshare, which I heavily reference in my own talk. I’ve put my own slides online and a recording may be available soon. Here are some notes/reflections:

Absence of theory

Amazon cares not a whit *why* people who buy german chocolate also buy cake pans as long as they get to the checkout buying both - Mike Caulfield - Short Notes on the Absence of Theory

I was fortunate to be in the bar room when ‘absence of theory’ was being discussed at MRI13. The thing that hit me hardest was the reflection that throughout my career there has been an absence of theory. Like many other learning technologists I jumped into this area from another discipline, in my case structural engineering. Consequently I started in this area with more knowledge of the plastic analysis or portal frames than  educational theory. Being a curious person has taken me down numerous avenues and often along the way I’ve been lucky to work and learn with some of the best. For example I was fortunate to work with Professors David Nicol and Jim Boyle at the University of Strathclyde, Jim arguably being responsible for importing Peer Instruction to the UK. So while I have some theory I don’t have enough and whilst I have connections to some of the best people in LA my job isn’t aligned. But enough about me, without theory the danger is you have data but no actual insight to what it means.

Visualizations

Graphs can be a powerful way to represent relationships between data, but they are also a very abstract concept, which means that they run the danger of meaning something only to the creator of the graph … Everything looks like a graph, but almost nothing should ever be drawn as one. - Ben Fry in ‘Visualizing Data’

The consequence is ‘every chart is a lie’, a representation of data defined by it’s creator. One option here is to turn the learner into the creator. With modern web browsers it’s becoming even easier for someone to become the explorer of their own data. Dashboards, which appear to have the same appeal as Marmite, can also be personalised to give more meaning to the learner. Even with personalisation and customisation there is a danger of misinterpretation which Dragon highlighted with Corrin, L., & de Barba, P. (2014). ‘Exploring students’ interpretation of feedback delivered through learning analytics dashboards’.

image

Ethics and privacy

The worlds of privacy and analytics intersect …not always happily – Stephen Downes

I was browsing some slide decks by Doug Clow as part of the LACEProject and he captured the sentiment nicely highlighting that there needs to be transparency when using learning analytics. He contextualised this around guidance and support rather than  surveillance and control. Given the varying degrees of apathy I see around data and privacy this is a conversation always worth having. There is clear outline of ethical considerations in the Analytics for Education chapter penned by my co-authors Sheila MacNeill and Lorna Campbell:

Ethical Issues

As institutional managers, administrators and researchers are well aware, any practice involving data collection and reuse has inherent legal and ethical implications. Most institutions have clear guidelines and policies in place governing the collection and use of research data; however it is less common for institutions to have legal and ethical guidelines on the use of data gathered from internal systems (Prinsloo & Slade, 2013). As is often the case, the development of legal frameworks has not kept pace with the development of new technologies.

The Cetis Analytics Series paper on Legal, Risk and Ethical Aspects of Analytics in Higher Education (Kay, Korn, & Oppenheim, 2012) outlines a set of common principles that have universal application:

  • Clarity - open definition of purpose, scope and boundaries, even if that is broad and in some respects extent open-ended.
  • Comfort and care - consideration for both the interests and the feelings of the data subject and vigilance regarding exceptional cases.
  • Choice and consent - informed individual opportunity to opt-out or opt-in.
  • Consequence and complaint - recognition that there may be unforeseen consequences and therefore provision of mechanisms for redress. (p. 6)

In short, it is fundamental that institutions are aware of the legal and ethical implications of any activity requiring data collection before undertaking any form of data analysis activity.

Opportunity

At best analytics can help start a conversation. People have to be willing to take the conversation on - Roberts, G. Analytics are not relationships

My biggest fear is Learning Analytics just becomes ‘computer says no’. I’m reassured that there are many people working very hard to make sure this doesn’t happen, but in the glitz and glamor of ‘big data’, prediction algorithms and dashboards there is a danger that we start caring about the wrong thing. For me the biggest opportunity is analytics are used as feedback, helping inform the conversation.

4 Comments

There are regular reporting features built in to Google Analytics but what if you want to do customised reporting or other regular data collection and integration techniques? One answer is Google Apps Script. Apps Script is a Google Drive feature that makes custom automation so much easier. The big advantage of Apps Script is you are already in a Google authenticated environment. So while anyone can use the existing Google Analytics APIs using Apps Script you don’t need to worry about the authentication handshake to get your data. This means that you can access Google Analytics data in one line of code. There is a couple of tickboxes to get to that one line of code but in this tutorial I’ll walk you through the process.

Note: You can also get Google Analytics (and other analytics data) in a Google Sheet with zero lines of code using add-ons. The current limitation of these is they cannot currently be automatically run on a scheduled basis.

Setting up our project

Google Apps Script lives in a couple of places but for this example we are going to use a Google Sheet so step one is:

  1. From your Google Drive account create a new Spreadsheet
  2. In the new spreadsheet open Tools > Script editor…
  3. If this is your first script you might see a window popup with options to ‘Create projects for’, if so click ‘Close’
    Close Apps Script project window
  4. You should now see the Script editor
    Script editor
  5. The connection to Google Analytics is a Google Apps Script ‘Advanced Service’ and to enable we need to turn it on. To do this in the Script Editor select Resources > Advanced Google services 
    image
  6. At this point you will be prompted to create a project name. Enter a name for your project and click ‘OK’.
  7. You should now see a list of available advanced services. For this project we want to just turn on Google Analytics API by clicking the on/off toggle. So far we have enabled Analytics in our project but we also need to click on the Google Developers Console link highlighted to enable this at the console end.
    Advanced services control
  8. Similarly to the ‘Advanced Google Services’ we need to enable the Analytics API by toggling the on/off (at this point you may be prompted to review and accept terms of service)
    image
  9. Once enabled in the Developer Console you can close this tab/window and click OK in the ‘Advanced Google Services’ box in your Script Editor.

To recap our project is now configured to use the Google Analytics API. You can use multiple advanced services in the same project as needed. Remember ‘Advanced Services’ need to be enabled for each project you create but this only needs to be done once for each project.

Getting Google Analytics data (and some basic Script Editor tips)

For those familiar with coding environments the Script Editor comes with a code autocomplete. This is useful when starting rather than remembering a long list of classes and methods. To use this:

In the Script Editor click on line 2 between the moustaches (‘{‘ and ‘}’) and for PC press CTRL + SPACE or Mac ⌘ + SPACE, from this you can select Analytics from the list. To continue the autocomplete follow it with a dot ‘.’ which brings the associated options. The line we want to get is Analytics.Data.Ga.get(ids, start-date, end-date, metrics, optionalArgs); 

autocomplete

So you’ll see there are a couple of parameters we need to provide defined in the GA Core Reporting Query Parameters Summary:

  • ids - Unique table ID for retrieving Analytics data. Table ID is of the form ga:XXXX, where XXXX is the Analytics view (profile) ID.
  • startDate - Start date for fetching Analytics data. Requests can specify a start date formatted as YYYY-MM-DD, or as a relative date (e.g., today, yesterday, or 7daysAgo). The default value is 7daysAgo.
  • endDate - End date for fetching Analytics data. Request can should specify an end date formatted as YYYY-MM- DD, or as a relative date (e.g., today, yesterday, or 7daysAgo). The default value is yesterday.
  • metrics - A comma-separated list of Analytics metrics. E.g., 'ga:sessions,ga:pageviews'. At least one metric must be specified.
  • optionalArgs – is an object array of the optional query parameters like dimensions, segment, filters, sort etc. An example from this Google Analytics Apps Script tutorial is:
var optionalArgs = {
 'dimensions': 'ga:keyword',              // Comma separated list of dimensions.
 'sort': '-ga:sessions,ga:keyword',       // Sort by sessions descending, then keyword.
 'segment': 'dynamic::ga:isMobile==Yes',  // Process only mobile traffic.
 'filters': 'ga:source==google',          // Display only google traffic.
 'start-index': '1',
 'max-results': '250'                     // Display the first 250 results.
};

Building a Google Analytics Query with Query Explorer

If you are unfamiliar with the Google Analytics Core Reporting API building a query can be quite daunting. Fortunately the Google Analytics team have made the Google Analytics Query Explorer which gives you an interactive interface to build and test queries. Here’s an example to query your top referring sites, which should give you a page like this:

Google Analytics Query Explorer 2

If you haven’t used the Query Explorer before you’ll need to click on the ‘Authorize Access’ button, which will enable a ‘Get Data’ button. You can test and tweak your query as much as you like using the ‘Get Data’ button to see what is returned from your Google Analytics accounts. The Query Explorer is a great starting point but remember it only contains a few of the optional arguments.

If you are using the Query Explorer then below is a helper script for converting your Query URI into an Apps Script snippet. To use this build your query in the Query Explorer and then press the Query URI button image to get a URI to paste in the textfield and then click ‘Submit’

The little helper script gives us some extra code to store/pass the parameters we need. If you are scheduling this script to run on a regular basis you’ll need to modify the start/end date in the query. The are two main ways you can do this: either building the date by manipulating a Date instance formatted as YYYY-MM-DD; or as a relative date (e.g., today, yesterday, or NdaysAgo where N is a positive integer). Below is an example of a modified query made with the Query Explorer and exported to generate the script we need which gets data from the last 7 days. In this example I’ve wrapped it in a new function name fetchMyQuery. Note: In your example you need your own ids value to return results.

function fetchMyQuery() {
  var query = {
    "optionalArgs": {
      "dimensions": "ga:source",
      "filters": "ga:medium==referral",
      "sort": "-ga:pageviews",
      "max-results": "50"
    },
    "ids": "ga:82426939",
    "metrics": "ga:pageviews,ga:sessionDuration,ga:exits",
    "start-date": "7daysAgo",
    "end-date": "yesterday"
  };
  var results = Analytics.Data.Ga.get(query.ids, query['start-date'], query['end-date'], query.metrics, query.optionalArgs);
  Logger.log(results);  
}

Testing/Debugging Apps Script

With your script saved we can test the code. When developing scripts there is a debug feature demonstrated in the video below (the first time you run a new script you need to authorise it. This only needs to be done the first time the script runs or when new permissions are required.

Insert Data Into A Spreadsheet

The final step is to output the results from our query into Google Sheets. For this example let reuse a modified version of the outputToSpreadsheet method in the Automated Access to Google Analytics Data in Google Sheets tutorial:

function outputToSpreadsheet(results, sheet) {
  // Print the headers.
  var headerNames = [];
  for (var i = 0, header; header = results.getColumnHeaders()[i]; ++i) {
    headerNames.push(header.getName());
  }
  sheet.getRange(1, 1, 1, headerNames.length)
      .setValues([headerNames]);

  // Print the rows of data.
  sheet.getRange(2, 1, results.getRows().length, headerNames.length)
      .setValues(results.getRows());
}

This function inserts all the header and reporting data to the sheet. For more information on how to insert data into Google Sheets with Apps Script there is a  Storing Data in Spreadsheets tutorial.

The outputToSpreadsheet method in our example is expecting two objects to be passed in. To do this your fetchMyQuery method needs to include getting Sheet object and passing it to will need to outputToSpreadsheet. The entire project should look like this:

When you now Run > fetchMyQuery you should see the data written in the sheet you specified.

Automate the Script

This project has been about automated data collection so let look at how we set this up. Google Apps Script makes automation very easy using the triggers feature. To set this up

  1. In the Script Editor click Resources > Current project’s triggers
  2. Click ‘No triggers set up. Click here to add one now’
  3. Lets configure the fetchMyQuery to run once a week by setting:
    • The Run dropdown to: fetchMyQuery
    • The Events dropdown to: Time-driven, and selecting Week timer to run Every Monday between 7:00 a.m. to 8:00 a.m.

Triggers

Once saved this script will run as scheduled with no need for your to have the sheet open. If you would like to be told if the script fails whilst unattended click the notifications link which opens a new dialogue box to allow you to configure to which email you want errors to be sent and when.

Summary

This post has introduced you to using Google Apps Script to automatically collect and write Google Analytics data to a Google Sheet. Once you get started with Apps Script you’ll start discovering many more opportunities to automate tasks. For example, as part of our triggered event we could notify people of an update using the MailApp service or write an entire document using Document Service. For some ideas you might want to read about Analytics reporting with Google Apps Script at the UK Cabinet Office.  If you are just starting to use Google Apps Script the Google Developers has extensive documentation and tutorials and if you get stuck there is a dedicated tag in Stackoverflow and an active community on Google+.

Update 17/07/2014: An issue with this idea is that Google Analytics has a consumer limit of 50 Views (Profiles) per GA Account

At the Google Apps for Education European User Group (GEUG14) I highlighted how Google Analytics could be utilised for Learning Analytics. The type of analytic I have in mind goes beyond pageviews and includes event tracking which through Google Analytics can be explored using segmentations and other built I reporting. This approach is not focused on the individual but for generating course and programme actionable insights. Whilst VLE/LMS vendors and platforms are probably already supporting Google Analytics tracking in their products access to this data often never gets beyond the account administrator. This, in my opinion, is a missed opportunity as the reporting in Google Analytics could easily be applied to a Learning Analytics context.

The solution

Integrate your course creation and management processes with the Google Analytics Management API. With this when a course is created or editing for an instructor a filtered view of the main analytics is also created. With a filtered view instructors would  be able to access their course analytics.

The main advantages of using Google Analytics for Learning Analytics is:

  • the overhead of processing event/click tracking is handled in the cloud by Google
  • scalable and manageable access to analytics

GAforLMS

4 Comments

Update 24/06/2014: Recording of the session currently here

For a while I’ve been interested in the intersection between Google Analytics (and Google’s other analytic reporting APIs like YouTube) applied to the field of Learning Analytics. There are a number of features of Google Analytics such as segmentation, a-b testing, event tracking which I believe could potentially give useful insight to teaching a learning, for example, a look last year at tracking and validating MCQs with GA.

One of the reasons for my interest in this area is the ubiquity of Google Analytics, the majority of institutions already using Google Analytics for their main institutional websites. It should not be forgotten that with power comes responsibility. Whilst Google Analytics usage policies prevent you using it to track personally identifiable information you are still tracking people which should never be forgotten.

The Google Apps for Education European User Group Meeting (GEUG14) at the University of York is another opportunity to roadtest some of these ideas. The process for preparing for an event often not only sees me revisiting prior knowledge but is often turned into an opportunity to create something new. This can be a product, like the Google Analytics data bridge made for IWMW13, or new knowledge.

This time personal exploration has taken me into the land of the Google Tag Manager. Those familiar with the mechanics of Google Analytics tracking will know that this usually requires adding code to every page you want to track. Often this can be achieved modifying page templates. But what if these templates are hard/costly to edit or you want to make changes to what is tracked. This is where Google Tag Manager comes in. Like Google Analytics you need to install some code in your pages. After that what and how you track things becomes completely cloud based. Through Google Tag Manager to can add additional code/markup to your pages even setting up rules to decide when these are used. Whilst Tag Manager is build around Google products like Analytics and Ads you can use it for other purposes. This video gives you an overview of Google Tag Manager.

Below are the slides from my session which will hopefully be streamed via Google Hangout 23rd June at 11:30am BST (see programme).

Back in the good old days when I was a member of the Glasgow based supergroup with my then colleagues Lorna Campbell and Sheila MacNeill we were approached to write a chapter for the soon to be published ‘Reusing Open Resources’.  We were tasked with writing something on ‘Analytics for Education’. Prior to print our chapter along with four others have been published in the Journal of Interactive Media in Education (JiME) under a CC-BY license. You can read the full Analytics in Education chapter here and copied below is the section I had most input on was ‘future developments’.

Given ‘prediction is very hard, especially about the future’ its interesting to look back at what we wrote in the summer 2013. Something we should have perhaps expanded upon was data privacy concerns particularly in light of the news that news that  non-profit inBloom is shutting down. I often find myself with conflicted interests between data collection as part of my personal quantified self and data collection for quantifying others. TAGS is a prime example of where I initially wanted to collect data to understand the shape of the communities I was in, but now is used by myself and others to extract data from communities we have no investment in.

And right now I'm developing the next iteration of ocTEL which thanks to funding  from the MOOC Research Initiative has helped find areas where we can improve data collection, in particular, resolving identities across networks. Achieving this personally feels like progress but I’m sure many others will disagree.

Are we bound by a data dogma? ...continue reading

4 Comments

Google Docs Analytics Tracking - CC-BY-NC Tony Ruscoe
Google Docs Analytics Tracking - CC-BY-NC Tony Ruscoe

There was a time when you could enable Google Analytics tracking in what was Google Docs and is now Google Drive. Sadly the feature was removed and Google now recommend “embed them in your web pages, and then use Analytics to track the pages in which they're embedded”. For someone who has a number of Google Sheet templates this isn’t entirely convenient and workable. I can embed a link to a template in a site and attach an event to track the number of times clicked, but given these templates can easily be copied and recopied there’s no way to monitor use.

Part of the problem is that the Google Analytics predominantly relies on some embedded JavaScript to communicate when a page has been viewed.  Given the increasing range of interactions Google Analytics also provides a Measurement Protocol for developers to send tracking data in other ways using a HTTP POST or GET request. Without going too deep into the technical side this actual opens a way for including tracking in Google Documents, Sheets and Forms by using Google Apps Script.

Apps Script includes both triggers like onOpen and a URL Fetch service which would allow you to send data to the Measurement Protocol (and this little gist gives you the code to do it). Before you go implementing this in all your projects there are two issues to be aware of:

  1. URL Fetch calls are quotaed by fetches per day and overall runtime (current Google Apps Script Quotas).
  2. URLFetch Service requires authorisation before it can run. This means it cannot send data unless the user has given permission. So if you are viewing a Sheet template Google Analytics will only be pinged after you’ve File > Make a copy and authorised it.

Authorization for Google Services

Throwing up a beacon instead

All is not lost. Recently I came across the Google Analytics Beacon:

Sometimes it is impossible to embed the JavaScript tracking code provided by Google Analytics: the host page does not allow arbitrary JavaScript, and there is no Google Analytics integration. However, not all is lost! If you can embed a simple image (pixel tracker), then you can beacon data to Google Analytics.

This project by Googler Ilya Grigorik means if you can embed an image a Google App Engine service has been configured to make a hit against the Measurement Protocol for you. For this to work when you view the page the image needs to be served from the App Engine service. There are also limitations to this approach in that visitor and referral data is lost.

In Google Drive it’s easy for us to Insert > Image in various applications including Documents and Presentations and even specify these as ‘by URL’. Unfortunately these applications also create copies of the inserted image rather than using the image specified by URL. An anomaly to this is Google Sheets. Sheets permits Insert > Image and a cell function IMAGE. In both these cases the image is served from the URL you specify meaning we can track Google Sheets*.

*New Sheets appears to serve Insert > Image in the same way as Documents and Presentations but the IMAGE formula method outlined below still works.

Using Insert > Image

Using the GA Beacon Setup Instructions will walk you through creating a Google Analytics account and making an image URL like

https://ga-beacon.appspot.com/UA-XXXXX-X/sheets/UNIQUE_ID

Remember to use your own tracking ID. The ‘sheets’ and ‘UNIQUE_ID’ can also be whatever you like.

Using Insert > Image and selecting ‘By URL’ you can add a GA Beacon to a sheet (you can check you are collecting data by logging into Google Analytics and looking at Real-Time reporting). Now every time the Sheet is opened and the image is viewable the visit will be counted in Google Analytics. The url for the image is fixed so even if a copy is made of the spreadsheet as long as the image isn’t deleted you will get tracking data. Remember this way won’t work for New Sheets but the next method does and in my opinion is better.

Using IMAGE formula better tracking information

Using the IMAGE formula would work in exactly the same way using the same image url as above. There is something else we can do. Because it’s a formula the image url could have an identifier that is in some way calculated. As Google Apps Script permits creating your own custom formula there is even scope to use this as part of the calculation.  For example, using little Apps Script will include the sheet key and locale in the image url (to include this you your own project open you Sheet and then Tools > Script editor and paste the code in):

function getGABeacon(tid){
  var id = SpreadsheetApp.getActiveSpreadsheet().getId();
  var locale = SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetLocale();
  return 'https://ga-beacon.appspot.com/'+tid+'/sheets/'+id+'/'+locale;
}

In the Sheet we can then use the cell formula like =image(getGABeacon("UA-48225260-1"))

Image with GA Beacon

You can see this better in-situ in this Google Sheet which you are free to File > Make a copy to see how it works. As an added bonus the Apps Script methods used in this example don’t require authorisation so tracking data (limited to view count) is recorded for anyone opening the Sheet.

Note: GA Beacon recently defaulted to using a SVG image which is not compatible with the old version of Google Sheets. The tracking still appears to work but a #N/A error is returned. To get around this I requested an option to add ?gif to the beacon url is added. To use this you can add +'?gif' to the script or use the formula =image(getGABeacon("UA-48225260-1")&"?gif"). Also remember new Sheets has heavy formula caching so if using this technique for tracking templates you should add a reference to a cell you know will be edited to get the actual spreadsheet ID e.g. =image(getGABeacon("UA-48225260-1",A1))

This is what the result looks like in Google Analytics Real-Time.

Google Analytics Real-Time

So there you go I can now track views of my Google Sheets by including an image in a cell!

1 Comment

A recent thought I’ve been pondering is the default closed approach to education. It’s interesting to reflect how the physical structure of the classroom with walls and doors gets replicated online with firewalls and logins. I can appreciate that in part this is needed to create a closed environment where the student feels safe and secure, but it is also has other factors like license to share copyrighted work or terms of license for learning platforms. It’s ironic that the ‘MOOC as a Service’ (MaaS/xMaaS) offering for Coursera, et al., whilst are open to register still default to a closed mode*, studying in their place under their terms. Even FutureLearn which is designed on social learning principles seems to only consider social in the system. ...continue reading

2 Comments

Google recently (1st October 2013) announced improved segmentation in Google Analytics using age, gender and interests

It been interesting to read some of Tony Hirst’s posts on the use of Google Analytics within education. The thread goes back to 2008 with Library Analytics but most recently Tony has writing about this in an open course context such as MOOC Platforms and the A/B Testing of Course Materials and MOOC Busting: Personal Googalytics… which looks at the  idea of collecting and feeding back performance data to users from across platforms.

When Daphne Koller was on the early Coursera push one of the aspects that caught my eye was using student generated data (aka their answers to questions) to in course design, in particular, to identify misconceptions or incorrectly set questions. To see what I mean here’s a cued clip from a presentation Koller gave at the Centre for Distance Education back in 2012.

YouTube: The Online Revolution: Education at Scale

Merging the two lines of thought I wondered if there was a way you could use Google Analytics to create a similar feedback mechanism. My starting point was Google Analytics Event tracking. From the Event Tracking documentation:

Event Tracking is a method available in the ga.js tracking code that you can use to record user interaction with website elements, such as a Flash-driven menu system. This is accomplished by attaching the method call to the particular UI element you want to track. When used this way, all user activity on such elements is calculated and displayed as Events in the Analytics reporting interface. Additionally, pageview calculations are unaffected by user activity tracked using the Event Tracking method. Finally, Event Tracking employs an object-oriented model that you can use to collect and classify different types of interaction with your web page objects.

Examples include:

  • Any Flash-driven element, like a Flash website, or a Flash Movie player
  • Embedded AJAX page elements
  • Page gadgets
  • File downloads
  • Load times for data

Essentially anything you can trigger with a bit of JavaScript is up for grabs. Looking at setting up event tracking each event can include:

  • category (required) - The name you supply for the group of objects you want to track.
  • action (required) - A string that is uniquely paired with each category, and commonly used to define the type of user interaction for the web object.
  • label (optional) - An optional string to provide additional dimensions to the event data.
  • value (optional) - An integer that you can use to provide numerical data about the user event.
  • non-interaction (optional) - A boolean that when set to true, indicates that the event hit will not be used in bounce-rate calculation.

So we can capture events and have some control over how they are described. What might we want to catch? Lets start by looking at was multiple choice questions (MCQs). Looking at the anatomy of an event this is one way we might want to encode it:

  • category: ‘MCQ’
  • action: ‘right’ or ‘wrong’
  • label: a question identifier. This needs to be unique and might be something like coursecode_module_section_question (having a consistently structured label will help filter the data later)
  • value: this is optional but as it needs to be an integer this restricts you a bit. You may want to use time taken to respond, confidence based mark etc.

I should say before you get carried away with tracking that:

The first 10 event hits sent to Google Analytics are tracked immediately, thereafter tracking is rate limited to one event hit per second.

To see how this works I’ve created this example page with a simple MCQ. This is a ‘live’ example with some crude code to push events to my Google Analytics account. You’ll notice on the page a response graph generated from the GA data. I’ll explain how that was made later.

GA Real-time eventsThe fist thing to note is that we can now see responses in real-time via the Google Analytics admin interface. The interface is not really geared for MCQs and there is a complication of who has access to the Analytics dashboard, but given that there is a Real Time Reporting API in beta a custom slice’n’dice should be possible in the future (I’ve got beta access so this might be one I revisit if/when Events get added to the API).

Similarly the Content Events report gives us access to historic data but again it has accessibility issues in terms of who has access to the Google Analytics account. On the plus side tweaking the display from the default ‘data’ view  to ‘performance’ gives a basic bar chart which is more intuitive for this type of data.

Default data view for Content > Events
Default data view for Content > Events
Performance data view for Content > Events
Performance data view for Content > Events

Segmentation and cohort analysis

There are some other built-in Google Analytics features that may also support analysis of the data including filtering:

GA Filtering

or switching from a ‘line chart’ to a ‘motion chart’ (there are limits on what can be used for x-y values so some experimentation is required) and adding event reports to custom dashboards which may pull in other GA data.

GA Motion Chart

This is where is potentially get even more interesting as the new Google Analytics Advanced Segmentation* allows you to do cohort analysis. The built-in segments are perhaps not relevant for this scenario but the custom options have lots of potential. Google provide 6 segment templates for ‘Demographics’, ‘Technology’, ‘Behavior’, ‘Data of First Visit’, ‘Traffic Sources’ and ‘E-commerce’ but it is easy for you to add custom conditions and sequences for segmentation.

GA custom conditions and sequences for segmentation

*I’m not sure if Google are still following this out but noticed the new UI and segmentation options were only available in my Google Apps GA account, my standard @gmail account not having this option.

Examples of conditions/sequences you might want to explore include combining Tony’s suggestion of using Analytics A/B testing with event tracking e.g. identifying any correlation with content to performance or if someone visiting page x did they perform better in the test. It is also worth noting that:

Previously, advanced segments were based on visits. With the new segment, a new option is provided to create user segment. In a user segment, all visits of the users who fit the segment criterias will be selected (such as specific demographics or behaviors). It will be a useful technique when you need to perform user level analysis.

This is particularly useful as “Google Analytics customers are prohibited from sending personal information to Google.” [ref]. So while named individual level analysis isn’t possible you can get down to a user level.

Distributing data

On a practical level whilst these options potentially open some interesting avenues for exploration Google Analytics account administration is still not easy. Whilst this area has been recently improved the granularity of permissions is very course, an all or nothing approach. There is a growing list of tools/add-ins that integrate with Google Analytics which let you create custom workflows for data distribution. This is an area Google appear to be working on recently announcing the Google Analytics superProxy which is a  web application that runs on Google App Engine to allow the distribution of GA data.  This uses the Google Analytics Reporting APIs to define data queries and generate data files. Along similar lines (and announced before Google) I’ve published a similar solution that works in Google Drive (Using Google Spreadsheets as a Google Analytics Data Bridge). Below is an example query I using the the MCQ example at the beginning of the post. It's currently using a very specific filter to exctract the data for all the event labels beginning EMD101_Mod1_1.1_Q1_, but if using a standardise labeling you could include results for the entire module or course. I'm also not using an segment filters. As well as using standard segments you can also use custom segments

Google Analytics Query ExporterAs I outlined in my original post there is a number of ways that these slices of Google Analytics data can be shared or consumed into other tools. In the example above the data is written (and refreshed every hour) to the sheet below, Google Sheets providing a convenient environment for sharing and querying data with the relative familiarity of a spreadsheet interface.

At this point I’d imagine some of you are wondering why go through all of this bother when your VLE is able to do similar, if not better, levels of reporting. My eye is primarily on the open education context where the institutional  VLE is usually not and option. It also potentially provides a more holistic data source where you can experiment with content and resources across your little oasis (like ocTEL).

So what do you think? Will you be event tracking your MCQs?