It would be really interesting to be able to look at how people use things that you post. Google Analytics give a good handle on how your web applications and sites are being used, but once someone downloads a spreadsheet, it's a mystery what happens. How often do people actually run things? What are they running ? How long does it take? A myriad of questions - the answers to which would improve the usefulness of the sample workbooks sites like these provide.
Well you can use Google Universal Analytics to do the same thing for spreadsheets (and other things) as you can do for websites. It was this post by Dan Harrington that got me thinking about how that could be done. In this article I'll show you how I've instrumented some functions to collect usage data, and how you could do the same.
Here's a blog post talking about the first week's instrumentation results, and another a few months later showing how much tracked module usage has grown.
Google provide an html interface into Universal Analytics that can be used to inject whatever data you like. This means that with a very simple http post, you can store data about the usage profile of anything that knows how to POST and use all the Analytics tools to report on it.
With that in mind, I've instrumented a few key functions in cDataSet.xlsm (I'll extend it after I've tried it a bit), to send usage data to Google Analytics that I can later on analyze to see what's popular and what's not. If you just want the instrumentation code, you can get it online with How to update modules automatically in VBA, using this
You can easily opt out of analytic contribution by setting pOptOut to true in the cUAMeasure class as below.
I have this setup to report usage to my Google Analytics account. If you want to set up your own, then create a new Universal analytics profile - The type of profile should be mobile APP since it is for offline use.
Here's an example. I pass the name of the page I want to record an access againt to registerUA, and do something. When complete I use .postAppKill to deregister, followed by a check to see that everything worked.
That's all there is to it. In addition to the data being recorded, I can immediately see, in real time. this happening over on my google Analytics account. Note I can see under versions which version of the workbook is being run, and under active screens, I can see "developing_testua", as I passed to registerUA.
One of the most popular topics on this site is Rest to Excel library. I've instrumented the restQuery function so that each time someone runs it, it will clock up on Google Analytics.
At the beginning of the function I register that it's starting
and at the end, when it's done
Again - we see immediate action on Analytics real time. This time I can see the cDataSet version again, but also I can see the active screen is restQuery_lescourses
This tells me that someone has just done a restQuery, using a particular version of the cDataSet workbook, using the lesCourses library entry. Beautiful.
I can also see that during my testing, I ran only cDataset version 3.00, in 8 sessions, and on average it took 3 seconds to run. So now I not only know what's been run, but how long it usually takes to run.
There are many more options that can be added later for further analysis, but let's run with this for now. You may be interested now in how to get analytics data into Excel. Take a look at Oauth2 and Excel-Rest library
See Universal analytics measurement protocol for your GAS libraries for a Google Apps Script implementation, and Universal analytics for checking co-operating processes for integrating the whole thing.
Here' s the code, which you can find in the downloads section in cDataSet.xlsm.
Services > Desktop Liberation - the definitive resource for Google Apps Script and Microsoft Office automation >