Measuring offline store activity with Google Universal Analytics

Hi folks,

Here is another post about Google Universal Analytics, seeing how you seem to like them 😉

Today we’re going to measure the performance of an offline store by testing 2 concepts:

• measuring customers as they walk in/out of the store
• measuring cash register transactions

Again, this is a proof of concept but feel free to expand upon it in your own store.

Ready? (Who am I kidding, you’re probably already giddy as a schoolgirl just reading this :D)

The method – how it’s done

Measuring in-/out-bound visitors

In this example, we’ll use visual recognition with basic webcams / IP cameras piloted by Raspberry Pi mini-computers (use model B for more RAM and an additional USB port, among other things). They’re inexpensive ($30 a piece) and are getting to be pretty powerful for something as big as your standard credit card 😉

We’re going to use 2 camera-equipped Raspberry “Pies”:

• one to detect in-bound customers
• one for outbound customers.

Motion sensor triggers whenever a customer passes in front of a camera.

To recap, whenever the picture changes for a given camera, it triggers a Google Universal Analytics endpoint request containing Google Analytics event code.

Measuring transactions

Before you start screaming your heads off because your cash register cannot connect to the Internet, again this is a proof of concept project. Chances are that you cannot set that up in your store, I’m just showing you a concept. Say it again with me: concept. Although it’s very doable.

Here, we’re going to use open source cash register software, meaning that we can modify (details later) its code and its features.

Our goal here is to send a Google Universal Analytics e-commerce transaction (and items, Allah willing) endpoint. Depending on whether we want to see a nice stream of transactions flow into the real-time reports, we can send e-commerce calls either as they register on the … cash register or once the daily sales report is built

Shopping list

Here is what you’ll need:

• Decent programming skills 🙂
• At least 2 Raspberry Pi mini-computers, each with their own power supply/AC adapter.
  Each Raspberry Pi model B costs about $30 per unit and power adapters can be bought for less than $10 each. As far as i’m concerned, you can use a third Raspberry Pi and a monitor + USB keyboard as the cash register computer.
• 2 USB webcams
• USB WiFi dongles or Ethernet cables
• Cans of Red Bull. Buy them in bulk for a discount.

All put together, this setup should not cost you more than $200. So borrow your spousal unit’s credit card and start raiding eBay 🙂

The set-up

Your set-up will most definitely vary (unless you use a spanking new Rasperry Pi).

You’re going to need some degree of connectivity to communicate with the Google Analytics mothership. Depending on your network setup, you will need to connect your Pis with either a trusty Ethernet cable or a WiFi USB dongle for Rasperry Pi.

Motion detection

For motion detection, I took most of my instructions from this tutorial on motion sensing with Raspberry Pi, which leverages the Motion package.

PRO TIP: for better detection results, make sure you have a solid-color partition between inbound/outbound customer passage points if they’re close by.

Note that you don’t necessarily need to save the webcam snapshots. What matters is that Motion allow for running scripts or instructions after the image capture takes place. Those are called external commands and you can see the reference for them in the documentation.

Raspberry Pi resources on webcams and motion detection:

• http://pingbin.com/2012/12/raspberry-pi-web-cam-server-motion/
• http://www.raspberrypi.org/phpBB3/viewtopic.php?t=18314

Assuming we defined our GA endpoint, the external command triggered upon motion detection could look like so in the Motion configuration file:

[code lang=”shell”]#External command to be executed when detecting motion (default: none)
execute wget %ENDPOINT%[/code]

Because I haven’t figured out how to customize the config file to handle different cases, the Google Universal Analytics endpoint URL pretty much has to be hard-coded:

[code]http://www.google-analytics.com/collect
?v=1
&t=event // Hit type
&tid=UA-7634164-5 // my profil ID
&cid=555 // anonymous customer ID
&dh=myofflinestore.com // my "hostname" =)
&ec=Motion%20Detector // Event category
&ea=In // Customer direction: going in or out?
&ev=1 // Event value
&cm5=1 // Custom metric (+1 increment)[/code]

The above endpoint was for the Motion instance running on the Raspberry Pi watching inbound customers. Just change the event label from ‘In’ to ‘Out’ on the other Raspberry Pi that’s watching outbound customers. Note that I’m using a Google Universal Analytics custom metric (#5) to count the total number of customers. I know, I could also be using a custom dimension to mirror Customer type ;-P

And that’s it really. Just run the Motion server on both Raspberry Pis and you’re good to go: they will trigger the GA server call every time the camera picks something up. To be fair, if two customers walk inside but abreast, it won’t count two customers.

EXTRA CREDIT FOR NERDS: if your store has rotating gates, forget about the webcam altogether and slap an accelerometer onto your Raspberry Pi and strap the whole thing to the hinge. Count one customer for every complete spin. And yes, I can hear you go “wheeee, visitors!” 😀

Don’t worry, as I like to say, it’s not rocket science 😉

Transaction tracking

Now we get to the business part: purchases.

In this example, I’m going to use LemonPOS, an open-source cash register package written in C++. Yes, I know, it’s not very visually appealing. I’m just trying to prove a concept can work 😉
In a way, you will notice this is not that different from regular e-commerce tracking in Google Analytics.

With transaction tracking, we have a choice of triggering either:

• the transaction endpoint and item information (default GA behavior)
• just the transaction endpoint (with a total currency amount)

Of course now you have to modifiy your code to trigger the e-commerce transaction endpoints. In LemonPOS, the file that needs modified is dataAccess/azahar.cpp and the 2 functions we need to hack are Azahar::insertTransaction and Azahar::insertTransactionItem. You can change those two functions to insert cURL calls to the endpoints, much like we did in the previous post.

Remember, here is what your endpoints should end up looking like:

Transaction

[code]http://www.google-analytics.com/collect
?v=1
&t=transaction // Hit type
&tid=UA-7634164-5 // my profile ID
&cid=555 // anonymous customer ID, pulled from LemonPOS in info.clientid
&dh=myofflinestore.com // my "hostname" =)
&ta=My%20Offline%20Store
&ti=123456789 // transaction ID, generated upon insert, see post
&tr=2000 // transaction amount, from LemonPOS field info.amount
&tt=100 // Sales tax, from LemonPOS field info.totalTax
&ts=0 // Free shipping, no field in LemonPOS 🙁
&cm2= // Custom metric for transaction amount, from LemonPOS field info.amount[/code]

Note that the transaction ID is only generated upon the database INSERT query so trigger this endpoint after query2.exec() executes and save that transaction ID in a global variable: you’ll need it for items!

Items

[code]http://www.google-analytics.com/collect
?v=1
&t=item // Hit type
&tid=UA-7634164-5 // my profile ID
&cid=555 // anonymous customer ID, pulled from LemonPOS in info.clientid
&dh=myofflinestore.com // my "hostname" =)
&ti=123456789 // transaction ID, taken from C++ global variable upon transaction creation
&ic=W001234 // SKU /product ID
&in=Widget%20%2D%20XL%20White // Product label/name
&ip=100 // Unit price
&iq=20 // Item quantity
&iv=Widgets // Item category

[/code]

With LemonPOS, it gets trickier because you have to recompile your C++ code everytime you make changes. Again, not highly complicated.

If all goes according to plan, you should now see data flowing into your Google Analytics account. Congratulations!

In conclusion

We have reached another level of Nerdvana and we can now manage the activity of our brick and mortar store with Google Analytics. With the right use of custom dimensions and metrics, this proof of concept shows that store owners could soon have access to online sales reporting tools that apply to their physical store.

Google Analytics may never replace regular commerce back-office management solutions. Indeed, those will keep getting uglier and ever more cumbersome, but they’ll get the job done. This doesn’t mean that the tools can’t be made sexier and more user-friendly. This shows that what people like Euclid are doing is right.

As with the other posts in this series, this is part of the R&D work i do at Hub’Sales, especially with Hub’Scan, our Google Analytics quality assurance solution. Note that while I am an employee of Hub’Sales, I paid for my own Raspberry Pis and webcams myself 🙂

As always, constructive criticism and remarks are welcome so use the box below to say hi