I am supporting CandidateX

CandidateX is a startup that focuses on creating inclusion-focused hiring solutions, designed to increase access to job opportunities for underestimated talent. Check them out if you have a few minutes to spare. They need visibility!

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.

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
 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.


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
        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


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