Universal Analytics: Grab Offline Data From Excel

ShareShare on Google+44Tweet about this on Twitter18Share on LinkedIn11Share on Facebook18

UPDATE 9 Oct 2013 – This works in Google Spreadsheets now as well! Feel free to make a copy of the sheet and work with the code. It’s still just a prototype, but I’m happy to see data bouncing from spreadsheets to Google Analytics.

View the Google Spreadsheet here

ORIGINAL POST
Inspired by Daniel Waisberg’s excellent post detailing the use of Google Web Forms to send data to your Analytics account, I decided to create something similar.

If you didn’t know already, Universal Analytics is the new version of Google Analytics, and the Measurement Protocol is its heart and soul. The protocol serves as an endpoint for raw visit data, which can be pushed from any service, application, or interface equipped to send HTTP requests. In this post, I’ll introduce you to a prototype of a Microsoft Excel extension I’ve been working on. With the extension, you can push data from Excel back to your account. By using carefully labelled events and by extracting the client ID from the online visit, you can combine offline and online data together in Google Analytics.

Without further ado, I give you: the journey of the coupon.

The premise

Let’s say we have a traditional brick-and-mortar store, which wants to award its regular customers with something special. So they send an e-mail with a link to a page, where the user can download a special coupon, with which they can get a pack of coffee for 5 euros. Incredible deal!

So the user goes to the page and sees this on the phone (of course they used a mobile device!).

Download coupon

Yes, I know it looks like phlegm, but like I said, this is a prototype!

When they press the button, this happens:

Coupon delivered upon button-press

And, as the button is pressed, this happens in Google Analytics:

Coupon download event registered in Google Analytics

So what happened?

When the user clicks the button, they receive the coupon code. In reality, the coupon code is the user’s client ID, which is a variable Universal Analytics uses to identify the client. In essence, all events, transactions, pageviews etc. done by this client ID are always registered to the same unique visitor. Because it’s so easy to get (just a simple function), I chose the variable to anchor the data.

After the button is clicked, an event is sent to Google Analytics with the label get-coupon. The action is always the coupon code (the client ID), which can then be used to categorize the events nicely.

Trip to the store

So now the user has the coupon code on their mobile phone. Next, she heads on down to the store.

She chooses two cans of Coffee Java (a delicious brand), which each cost just 5 euros each, thanks to the coupon.

She goes to the checkout, gives the coupon, thanks, and leaves.

When registering the sale, the cashier types down the coupon code the customer showed.

At the end of the day, the cashier fills the obligatory Microsoft Excel worksheet with the transaction details for the day.

I have no idea what these systems look like in reality, but in my wicked dreams I saw something like this:

Transaction data in Excel

Here you have basic information, which can be exported from any backend system. We have the transaction details for each transaction (just one for the day, how sad!). Under each transaction are the items that were purchased in that one transaction.

Each transaction can have a coupon code attached to it.

There’s also the basic information required for Universal Analytics (namely, the tracking ID).

Upon checking the details and clicking the Big Blue Button, these three message boxes pop up one after another:

Coupon use event sent
Transaction sent
Item sent

I’ve actually bound the button to a custom macro, which sends the HTTP POST request to Google Analytics. First, an event is sent, to link the coupon use to the coupon download that was processed earlier. Next, the transaction details are sent, and finally, the item is pushed through the API to Google Analytics.

The end result

First of all, we can see that the coupon code has both get-coupon and use-coupon events attached to it:

Get coupon and Use coupon in events

Also, we can see that the transaction was registered as well:

Transaction details in Google Analytics

And finally, with a custom report, we can display a number of things. First, each coupon is associated with just one unique visitor, so the client ID really works! Also, we can quickly see whether some coupons were used in transactions by looking at the Revenue column:

Revenue per coupon

Conclusions

This was, and I hope I made it clear, just a prototype. But it shows the power of the Measurement Protocol. You can send visit data (pageviews, events, transactions, you name it) from almost any digital source equipped with some scripting language which supports sending HTTP POST requests!

Using the client ID has its pros and cons. Basically, with the one device, only one coupon can ever be downloaded and used, since the client ID is always the same. But the user only has to switch to another device, and a new client ID is generated. So some other checks need to be in place to make sure that the system is not abused.

Also, and this is a major problem with the Measurement Protocol:

You can’t attach a custom timestamp to hits!

This means that in order to preserve at least some level of accuracy, the events should be sent from Microsoft Excel at the end of the day the transaction occurred. This is the only way to make sure that the transactions are registered on the correct date. I see this as a major shortcoming, and I hope a timestamping feature is introduced in a future version.

(Queue Time will not do, as it can only introduce a time lag which stretches no further than four hour past midnight of the day when the event is sent)

I’m looking forward to expanding the features of the Excel extension. It should be possible to send heaps of transaction data with one button press, as now the macro only supports one item, one transaction, and one coupon event.

ShareShare on Google+44Tweet about this on Twitter18Share on LinkedIn11Share on Facebook18

Comments

  1. says

    Excellent post I must say, thanks for this. I was wondering if you could provide the exercise excel files for test practices.

    • says

      Thanks Abiodun,

      I’m a bit embarrassed by my VBA code, so I won’t be sharing anything until it’s way more generic. It’s actually a very simple HTTP request script that runs in the macro, and there are tona of resources for it online. Sorry I couldn’t be more helpful, I will share the extension as soon as I’ve had time to refine it.

    • says

      Thank you Peter for your comment!

      Actually, we were talking about the same thing but with different terminology. I used “time lag” to mean the lag between occurrence and send time. I now see I should have probably used “latency” or something more descriptive.

      And we also agree about the Queue Time being too short to be useful. I wonder why such a limitation was introduced in the first place? For offline data management, it’s crucial to be able to mass send data weekly or even monthly, but without proper timestamping it’s useless.

      And about the 4 hour limitation, Nick Mihailovski of Google notes that the Developer documentation is actually wrong, and the time limit is “4 hours past midnight of the configured timezone in the profile” (see https://groups.google.com/forum/#!topic/google-analytics-measurement-protocol/yJraRFkTnFE).

      Now, I have no idea what that means, as it seems really ambiguous. Does it mean that if the hit occurred between 00:00 and 23:59 on any given day, you can send it with queue time up to but no later than 04:00 the following day?

      Whatever the interpretation, it’s still useless.

      EDIT: And now I saw my horrible typo on the original post, as I wrote “4 days past midnight” when I meant “4 hours past midnight”.

      • says

        Thanks for the clarification. It’s disappointing but good to know, that dev documentation could be wrong (or misleading) and is not corrected even if the Google Engineers know about it.

  2. Max says

    Some of cons you mention in using clientId are alleviated with google’s addition of “userID” field to Measurement Protocol.

    • says

      Technically yes, but userId is still something that you can apply only in an environment which provides a sign-in experience for visitors. It’s also something that you have to provide for GA (not the other way around, as with clientId), there has to be an opt-out opportunity, etc. I’m interested to see how this will apply to Measurement Protocol hits, because in the example of this post, the user is not signed in, nor is he/she given an opt-out chance (this would have to be something given when the coupon is downloaded) when the hits are sent through MP.

      So for anonymous use of downloaded coupons, for example, userId probably wouldn’t do much good.

      But it is a huge feature, and should definitely be used in lieu of clientId as soon as the requirements are first met.

Leave a Reply

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

Please do not write HTML or other formatted code in your comments!