Tracking your Excel Workbooks with Google Analytics

Last week, I posted how to instrument Excel workbooks so that usage of them could be tracked using Google Analytics. Now we have a week’s worth of data, let’s see what happened.

Instrumentation Recap

First of all a recap on what instrumenting VBA actually means. Once I’ve set up a Google Analytics Property (treating Excel as if it were a mobile application), I can use Google Measurement Protocol (a product in public Beta for now), to record stuff against that property. In particular, I wanted to see how a couple of key functions Integrating Google Maps and Earth with Excel and Excel Rest Library were being used, how long web queries were taking using them, and which worksheets were most popular. The instrumentation is described in  instrument Excel workbook, but in summary it goes like this, using the classes supplied on Excel Liberation.

 ‘ record something you want to know about
    Dim UA As cUAMeasure
    Set UA = registerUA(“some description to identify this activity”)


   ‘ some code to do something ……


  ‘ record that it’s finished
    UA.postAppKill.tearDown

Anyone who has downloaded cDataSet.xlsm or GoogleMapping.xlsm from Excel Liberation in the past few days will have this instrumentation in those workbooks.Right now that data is fairly basic, but it shows me what is being used.

From this I can deduce that 136 different people downloaded these workbooks, and either ran some of the examples, or created their own applications using the instrumented functions contained in them. I can also tell that the measured code ran for an average of 7 seconds,  and there were 2,249 queries executed.

Application Versions

For the purposes of testing, I’ve released various versions of these workbooks. Now I can also see which versions of the workbooks have generated this usage.

Screens

When we registered the event, some text is stored against it. This is used as the ‘screen’ name in analytics. Like this we can track which event happened, and how long it took. I’ve used this screen name to represent running a particular function on a particular worksheet. So now we can exactly which functions were run, and how long they took.

 Set UA = registerUA(“some description to identify this activity”)

Other metrics

Of course all the other metrics, such as location below are being measured also, and with a little more tweaking we can record Excel version, Operating System version and so on too. Along with Real Time analytics, which also works, this provides a beautiful remote diagnostics tool too. You can of course pull the data into Excel if you want too – for how see oauth2 and Excel Rest Library.

Keep an eye on this blog for more data and more instrumentation once this has been running for a while longer. For more stuff like this see Excel Liberation

(function() {
var po = document.createElement(‘script’); po.type = ‘text/javascript’; po.async = true;
po.src = ‘https://apis.google.com/js/plusone.js’;
var s = document.getElementsByTagName(‘script’)[0]; s.parentNode.insertBefore(po, s);
})();

Author: brucemcp

Leave a Reply

Your email address will not be published. Required fields are marked *