Analytics

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

Share this post on:
| | |
Posted in Analytics, Half baked on by .

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.

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

1 Comment

Later today I’ll be presenting some thoughts on the opportunities and challenges of trying to gain actionable insight from MOOCs. My slides are below you can tune in at 15:15GMT via the ALT YouTube channel. The whole day is being streamed and recorded, the programme is here. ...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?

2 Comments

Livros de Redes Sociais, SEO e Web 2.0Perhaps the worst SEO post title I could possibly use. If you are still wondering what SEO is ask Nitin Parmar ;)

Still lost? SEO is Search Engine Optimisation. I’ve had a long interest in SEO primarily in selfish terms to try and get this blog more read, but also in a wider ‘educational resource discovery’ context. I was the author of the ‘SEO and discoverability’ chapter in Into the wild – Technology for open educational resources, which highlights the importance and UKOER programme experiments with SEO.

So it’s perhaps no surprise that I agree with Tony:

Something I’m increasingly become aware of is SEO is not just about having the right metadata on your webpage, in fact arguably this is the least important aspect. The area I’m particularly interested in is the tools/techniques the SEO community use to gain ‘actionable insight’.

Ironically this is an area I’ve been inadvertently contributing to without really knowing it. Someone who spotted this early was Wil Reynolds founder of SEER Interactive:

SEER Interactive offer services in Paid Search Marketing and Search Engine Optimization but what’s particularly interesting is their commitment to being “an Analytics first company, and we will not take on projects where we can’t analyze our impact on your business”.

So what do I do that’s of interest to the SEO community? Well it seems like me SEOers like a good old-fashioned spreadsheet. They also like a good old-fashioned spreadsheet that they can hook into social network channels. A recent example of this is the work Richard Baxter (CEO and founder of SEOgadget) presented at MOZCon which extends TAGS (my Twitter Archiving Google Spreadsheet solution) demonstrating How To Use Twitter Data for Really Targeted Outreach. The general synopsis is:

an alternative method to find sites that our target audiences may be sharing on Twitter. With that data, you can build content strategy, understand your market a little better, and construct an alternative outreach plan based on what real people are sharing and engaging with, rather than starting with sites that just rank for guest post queries.

It was really interesting to read how Richard had used the output from TAGS, which was ingested into Excel where additional free Excel based SEO tools could be used to gain that all important ‘actionable insight’.

So ‘learning tech and library folk’ if you are planning your next phase of CPD maybe you should be looking at some SEO training and perhaps I’ll see you at MOZCon next year ;)

2 Comments

This is my first ever attempt at 'live blogging' and haven't done any tidying other than cropping images. You might want to explore the LASI-UK a twitter summary by @sheilmcn  (complete #lasiuk twitter archive here)

Giles Carden (University of Warwick)
Good data visualisations solve real business problems

[Slides here http://www.solaresearch.org/wp-content/uploads/2013/05/G-Carden-LASI-UK-Data-Visualisation.pptx]

Covering key components for effective data visualisation. Informed by Stephen Few's work using the following principles

 wp-1373029139554
Clarity important (forget the 3d charts). Using colour to indicate type of document.

Using different chart types for different audiences (senior management and individual academics seem to get simpler charts)

Warwick design framework

wp-1373029382793

 

Some examples of charts used at Warwick (not commonly used elsewhere)

wp-1373029489465

Using 'tadpole' chart to add time dimension (tails = older results)

 wp-1373029584234

Bullet chart

wp-1373029655284

Lollypop chart used to show student loses/gains

wp-1373029816397

Visualizing estates usage (red = underutilized) limited to teaching and learning. Became very apparent lack of estate usage in term 3 in part a consequence of final exams scheduling.

"good data visualization removes the need for extensive narrative"

Q: how do you get your data?
A: national resources and data warehouse. Culturally hasn't been much resistance. Also has a known face people seem more willing to share their data.

Better to have 80% of the data now than 100% in a years time

Q: visual literacy of senior management
A: thirst for data, variations as some academic backgrounds already have these literacies. Most people seem to get it. Mantra is to make it simple

Chris Ballard (Tribal Labs)
Data visualisation with predictive learning analytics

[Slides here http://www.slideshare.net/ChrisBallard/data-visualisation-with-predictive-learning-analytics]

Background: work has come out of a R&D with University of Wolverhampton. Looking at student success and retention going beyond the traffic light system. Interested in how staff interpret predictive visualisations. Using historical and current data combined with an understanding of student learning to provide insight. Different ways of using predictive analytics in learning analytics including student success, recommendation systems.

Understanding the student factors that influence student success. Focus is to help staff support students (maintaining a human interface). Requirement to make actionable insights.

Issues with predicting
wp-1373031306351
Telling you what might happen and not will happen. Highlighting fallibility and need careful interpretation

Focusing on presenting data to staff. Delivering to a wide range of staff, want to present data appropriate to viewers need. Using logging to filter eg if tutor your students prediction, course leader students + module level

wp-1373031580037

Staff can drill down into courses. Ability at a glance to see summary of module. Drilling down to student view

 wp-1373031726704

Giving an indication of the areas where student might be struggling

wp-1373032025438

Using metrics that have shown to have a strong influence in student success: Preparation for HE, engagement (engagement including vle and library use) and academic integration (formative assessment results)

Design guidelines

wp-1373032161630

Technology guidelines - highlight cross platform and touch friendly interfaces

Design considerations - 'traffic lights' are very emotive and have distinct colour banding (granularity)

Q: about prediction drilldown
A: very difficult to go beyond a certain level. Need to explain what '36% VLE engagement' actually means.

Em Bailey (Heriot-Watt University)
An overlooked tool? Using Excel for advanced data visualisation

All these shiny toys and here to defend the Excel. Benefits, very powerful and flexible and already on most people's desktops.

NSS survey dashboard presented in Excel developed by Em. No macros or VBA and using built in charts. Mix of subject and department information. Users can pick different subjects

wp-1373032820110

Option to drilldown for response range and opinion strength and make sector comparisons

wp-1373033060925

Looking at when things not going so well

wp-1373033204008

Used Excel to mock up what Key Information Set data might look like. Used to all staff to validate their returns.

wp-1373033397433

2 Comments

This is my first ever attempt at 'live blogging' and haven't done any tidying other than cropping images. You might want to explore the LASI-UK a twitter summary by @sheilmcn  (complete #lasiuk twitter archive here)

Sheila MacNeill (JISC CETIS)
Setting the scene: learning scenarios

[Slides are here http://www.slideshare.net/sheilamac/lasiuk-learningscenarios-smn]

Overview of learning scenarios, broad-brush, what data do we have access to. Where does it take place? Learning taking place in various places in institution but not forgetting informal setting.

wp-1373015570667[1]

Various places where we communicate and learn (synchronous and asynchronous), which is creating a data footprint both formal and informal. Opportunities to merge data

wp-1373015755728[1]

University of Derby learner engagement dartboard. Uses primary, secondary and tertiary indicators. Used to build fuller picture of the student and engagement with institution.

wp-1373015881650[1]

Using SNA to help understand learning. Example of using SNAPP for discussion board analysis.

Example from me of dashboarding Canvas Discussion forums in Google Spreadsheet and TAGSExplorer to 'see' conversations.

 wp-1373015982307[1]

wp-1373016067499[1]

Diagram developed by Mark Stubbs for tackling the LA problem space 'analytics to develop understanding'

What actionable insights can we gain? Examples include patterns of behaviour, targeted intervention, benchmarking, retention, activity design, data driven design decisions, greater understanding of the who, why what and where of learning

Clare Llewellyn (University of Edinburgh)
Argumentation on the web - always vulgar and often convincing?

[Slides are here http://www.slideshare.net/ClareLlewellyn/clare-llewellyn-lasiuk]

Using argumentation to analysis the social web. Session title is from Oscar Wild quote. Example, discussion around an typical Guardian post includes threading quoting etc.

wp-1373016387338[1]

Breaking down the discussion into conversations with specific topics, main points of the discussion. Issue is data deluge, how do we find the points of most interest. Solutions: BBC tried using editors picks but issue creates snapshots and not whole conversation.

Argumentation

wp-1373016571361

Result

wp-1373016606197

Various levels of argumentation micro and macro level.

Methodology

wp-1373016727899

Data - using twitter data from the London riots (7729)

Identification - Unsupervised clustering machine learning. Select an appropriate algorithm. Strategy is try and see. Tried Unigram/bigram, incremental clustering, k-means, topic modelling. Topic modelling proved best in this example.

Guardian comments - using api to extract claim (quoted text) counter claim (comment). Using LTT - TTT2

Supervised machine learning - data from London riots which has already been marked up. Tools: TagHelper Lightside? Using claim (text) evidence (link)

Duygu Simsek (Open University)
Towards visual analytics to improve scientific reading & writing

[Slides are here http://www.slideshare.net/dsimsek/lasiuk-talk030713-2]

[wordpress app decided not to save my notes :(]

Naomi Jeffery (Open University in Scotland)

Learning analytics: a whistle-stop tour

wp-1373018881933

Overview of papers Naomi has recently found interesting. Marshal Lovetts (lak13) - no conception of what the data we are looking at actually means in the real world, remembering the person and learning design. Tutor v student requirements.

wp-1373019043881

Overlap between needs.

5 types of LA. Social network analytics - visualizing social learning ties from Network awareness tool. Platform used to explore support learner communities.

Discourse analytics - bit.ly/16ByD1l - meaningful traces from speaking and listening within online discussion forums. Using 9 metrics and then supporting students in interpretation. Discovered that presenting numbers (rather than visualisations) had better results. As helping students developed desired behaviours

Context? Analytics Understanding promotions (how can student 'liking' posts can be used to promote content)

Disposition analytics - mooc disengagement identifying learner subpopulations. developed categorization of completing, auditing, disengaging and sampling

wp-1373019650733

Learning pathways - http://stanford.io/123tufg Five learning methods minimal, careful, strategic explorers, explorers and haphazard. Prior knowledge an influence the model

Context analytics
Whether online, classroom, blended. Framework paper has several examples.

6 Comments

The concepts used in this post are very similar to the Google Analytics superProxy (announced after my solution ;) the main difference is this working in Google Drive meaning data access authentication is built in.

Access control to Google Analytics data is very coarse. Via the web interface you can have full access or none at all. Given the growing interest in data driven decision making at all levels of the institution I thought it would be beneficial to demonstrate how Google Spreadsheets and Google Apps Script could be used to selectively distribute Google Analytics data. This solution was developed for my session at the Institutional Web Managers Workshop 2013 (IWMW13) which I talk about in more detail at the end of this post.

Google Analytics Query ExporterSo here is the general flow. Google Apps Script, which is part of Google Drive, is used by Google Analytics admins to create custom queries which pull back slices of data. The admins can then control how these slices are distributed either within the security of Google Drive or published to the web. Also because Google App Script feature ‘script triggers’, which can be time based, admins can preconfigure mini scripts to decide when and how the data is distributed. Examples include:

  • Add data slice to a Google Spreadsheet (Spreadsheets feature options for publishing to the web)
  • Turn data slice into a .csv file and attached to an email for the recipient
  • Turn data slice into a .csv file and copy/share via Google Drive
  • Turn data slice into a .csv file and publish on Google Drive
  • All the above with different file formats including json

Here is the template I’ve developed which you can copy and use followed by some setup instructions (also there’s a setup video):

*** Google Spreadsheet GA Query Exporter ***

  1. File > Make a copy of this spreadsheet (need to be logged
  2. Open Tools > Script editor and then:
    File > Upgrade authorisation experience;
    Once it's upgraded still in the Script editor click Resources > Use Google's APIs; and
    Click the 'Google API Console' link at the bottom of the dialog window;
  3. In the Google Apis Console switch the Analytics API 'on' and read/accept the terms (you may have to accept two seperate terms windows)
  4. Close the Google Apis Console window and OK the Google API Services window in the Script editor
  5. Before closing the Script editor Run > authoriseGA to authenticate script

Once authenticated there are a number of ways to run the script. You can enter a query and click 'Get data' for one off fetches or open Tools > Script editor and modify the example cron jobs to automate collection and distribution of data (the cron jobs allow functionality that includes emailing/sharing links to csv files)

To create multiple queries in the same spreadsheet duplicate this sheet and modify the query

Tip: You can use formula to build the start and end dates e.g. yesterday is =(TODAY())-1

To help admins construct these data slices the tool is designed to work in collaboration with the official Google Analytics Query Explorer 2. With this users can experiment with queries like this one that returns the top blog posts based on visits. Within the Query Explorer I can export the Query URI and import to a sheet in the template. The general workflow is demonstrated in the video below which shows how to setup an example query/export:

Using the example query from above I’ve created this template sheet. Similar to the example in the video in cells E9 and E10 I use a formula to dynamically create a date range. Assuming I want to run the query on the 1st of each month for last months stats we start with the end-date generated using =TODAY()-1. To get the start-date we use the value of this field to get the 1st of last month using =DATE(YEAR(E10),MONTH(E10),1)

I could open the spreadsheet every month and manually click the ‘Get data’ button but instead I can setup a script trigger to run as a Month timer of the 1st between 1am and 2am.

Setup script trigger/cron job

The code I run is a little scriptlet like:

function exampleCronJobWriteCSVtoParticularFolder(){
  // copy new data as csv and then email as attachment (copy also kept in Google Drive)
  var sheetO = getData("input"); // name of sheet with query to execute e.g. 'input'
  var csv = saveAsCSV(sheetO.getName(), sheetO.getName()+" "+Utilities.formatDate(new Date(), "GMT", "yyyyMMdd"));
  var folder = DriveApp.getFolderById("0B6GkLMU9sHmLbThITlNvb2dzREE"); // folder id from url (navigate to folder and extract for your own folders)
  folder.addFile(csv);
  MailApp.sendEmail([email protected], "New data", "A new csv for '"+sheetO.getName()+"' has been created and is in the folder "+folder.getUrl() );
  // MailApp.sendEmail(recipient, subject, body, options) - More info https://developers.google.com/apps-script/reference/mail/mail-app
}

… which gets the data and then publishes it as a csv to this folder emailing a link to listed recipients. As the folder has been shared we can get access via Google Drive Host with the link https://googledrive.com/host/0B6GkLMU9sHmLbThITlNvb2dzREE/top-posts%2020130701.csv

public folder

The template includes several example scriplet examples for you to play with and modify.  For those who are interested the slides for my IWMW13 session are on slideshare. The session was designed to introduce Google Apps Script followed by the query exporter template. I only made this yesterday so it might be a bit alpha. Feel free to leave issue and suggestions in the comments. I’m sure there are a number of clarifications required.

lasi-ukPart of the Learning Analytics Summer Institute Global Network

Jisc CETIS in partnership with the Open University in Scotland and the University of Edinburgh School of Informatics will be hosting a one day event on the 5th July in the Informatics Forum (Edinburgh) focusing on Learning Analytics.

LASI-UK is part of a global network of events in the first week of July, which are linked to the Learning Analytics Summer Institute (LASI) at Stanford University. LASI-UK will explore ways of working with data to draw out and share insights that can make a real difference to learning and teaching. This free, interactive event has a dual focus on data visualisation and working with dialogue data. You can check out the draft programme here.

Delegates are also invited to join us for an optional evening session, where we will be broadcasting the final morning plenary session live from Stanford.

Who should attend?

Anyone with an interest in using data to support students and inform curriculum design and teaching, including:

  • Data analysts, planners and management information providers
  • Curriculum designers / education developers
  • Those involved in the construction, delivery and analysis of MOOCs

Registration

Click here for more details and registration

This is a free event; however, places are limited, so interested parties are encouraged to register early.  We look forward to welcoming you to our event.

Share this post on:
| | |
Posted in Analytics, Event, Featured on by .