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.
Measurement protocol
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
'uameasure gtDoit "7471153", True
Collected Data
As per the UA terms of use, no identifiable data is stored in the Google Analytics system – its exactly the same as measuring web site data. Aside from info on which function is being run, a unique client ID is calculated using one way encryption. This is a hash of a windows ID, and filename and a salt key encoded using SHA1. This is so that unique visitors can be calculated, and cannot be decrypted back to any personally identifiable data.
Opting out
You can easily opt out of analytic contribution by setting pOptOut to true in the cUAMeasure class as below.
Private Sub Class_Initialize()
Set pBrowser = New cBrowser
' change this to true to opt out of analytic reporting
pOptOut = False
Setting up Universal analytics
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.
Measuring
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.
Public Sub testua()
With registerUA("developing_testua")
' do something
sleep 5
' kill session
.postAppKill
If Not .browser.isOk Then
Debug.Print .browser.status
End If
End With
End Sub
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.
A real example
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
Dim UA As cUAMeasure
Set UA = registerUA("restQuery_" & sEntry)
and at the end, when it’s done
UA.postAppKill
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.
The class cUAMeasure
Examples and parameters
You would replace these with your own data if you are planning to use this as a model for your workbooks.
Generating Unique ID
Public Function encryptSha1(ByVal keyString As String, ByVal str As String) As String
Dim encode As Object, encrypt As Object, s As String, _
t() As Byte, b() As Byte, privateKeyBytes() As Byte
Set encode = createObject("System.Text.asciiEncoding")
Set encrypt = createObject("System.Security.Cryptography.HMACSHA1")
s = Replace(keyString, "-", "+")
s = Replace(s, "_", "/")
privateKeyBytes = decodeBase64(s)
encrypt.key = privateKeyBytes
t = encode.Getbytes_4(str)
b = encrypt.ComputeHash_2((t))
s = tob64(b)
s = Replace(s, "+", "-")
encryptSha1 = Replace(s, "/", "_")
Set encode = Nothing
Set encrypt = Nothing
End Function
Public Function tob64(ByRef arrData() As Byte) As String
Dim objXML As Object, objNode
'Dim objNode As MSXML2.IXMLDOMElement
Set objXML = createObject("MSXML2.DOMDocument")
' byte array to base64
Set objNode = objXML.createElement("b64")
objNode.DataType = "bin.base64"
objNode.nodeTypedValue = arrData
tob64 = objNode.Text
Set objNode = Nothing
Set objXML = Nothing
End Function
Public Function decodeBase64(ByVal strData As String) As Byte()
Dim objXML As Object, objNode As Object
'Dim objNode As MSXML2.IXMLDOMElement
Set objXML = createObject("MSXML2.DOMDocument")
Set objNode = objXML.createElement("b64")
objNode.DataType = "bin.base64"
objNode.Text = strData
decodeBase64 = objNode.nodeTypedValue
Set objNode = Nothing
Set objXML = Nothing
End Function
For help and more information join our forum, follow the blog or follow me on twitter or Google Plus