How URL shorteners can inform your restaurant sales attribution
insights we can gain from looking at our logfiles
I’ve often complained that restaurant online ordering solutions don’t come with the basics of tracking and monitoring tools you’d normally get in any ecommerce solution. URL shorteners offer a workaround this, but how does tracking help us?
For this exercise, we are going to use two data sources:
Transactions export by order source:
I’ve removed sensitive data like order id’s and customer names, but this is an actual export from ChowNow’s transactions. The only variables that we can use in our sales attribution are the time and order source. Neither of those variables are perfect, so we will have to still perform some approximations.
URL weblogs:
I’ve exported this from SOCIALHOSE links url shorterning tool.
Source: SOCIALHOSE links - URL shortener tool
Now that we have our data, we can start analyzing while remaining tool-agnostic. Let’s have a little fun now shall we?
Attribution Rules for Tracking Clicks with Purchases
Attribution is the holy grail of marketing, because it is so impossibly difficult to get right. We have to make a lot of educated guesses, because the data is never complete enough.
We will use two basic rules for our attribution of sales here:
Time lapse rule
For the purpose of this exercise, we will allow for a maximum of 10 minutes from the time a user clicked on the link to when an order is made. If more than 10 minutes passed, that order cannot be attributed to the link.
Device match rule
ChowNow’s exports only tell us the following about the order source:
Direct Mobile Web 4.0: mobile user
Direct Web 4.0: desktop user
Discover iOS: through ChowNow’s iOS app
Yelp: through Yelp’s order (and payment) processing
Unknown/Other
We try to match the order source with devices we know. So if we think an order fits the time lapse rule, we check to see if the device/os match the Order Source.
Play with numbers like a data scientist
We’re not going to do anything advanced here, so don’t let the ‘data scientist’ scare you. Now let’s fire up Jupyter and play with those numbers a little..
As you can see, this is not particularly interesting, but we can see that our users are overwhelmingly on mobile devices. It’s not a surprise, but I was actually surprised at how much PC users were there.
Matching timezones
If you’ve noticed, there are a couple of lines that seem to deal with time. Those lines simply take the string-based time column and converts it into a machine-readable version, so we can have fun with it later.
When combining data from multiple sources, we have to be careful to match the timezones used. For example, ChowNow’s exports times in PST, while we are on CST (that’s a 2 hour difference). If you don’t do that, you may find it difficult to attribute any orders to a clicked link.
Keep it short and sweet
Now we have some data showing the gross sales amounts. Here we created a new DataFrame that only has the columns: bot, device, os, gross. This is not necessary, but it makes it easier for us to know what we’re working with.
Let’s take a closer look at the breakdown to see if any of this makes any sense.
iOS users spend the most
We’ve always known about iOS users being more affluent than Android users. There are many reasons why iOS users spend more money than other platforms, but we’re not getting into this now.
Let’s take a look at our breakdown from August 1’s sales of direct online orders. That is, those are orders made directly through the restaurant menu (powered by ChowNow) and does not include phone-in orders or in-person orders.
I’ve included the bots here just so you’re aware of the noise level they create. As you can see, the iOS users spend is significantly higher than those of Android’s or even desktop.
An assumption the restaurant owner had was that orders that came through offices for lunch are big tickets. That may still be true, but what we can demonstrably tell is that those holding an iPhone make the biggest orders.
We barely scratched the surface here, but I hope that it was enough to have you start imagining all the different bits and pieces of information you have at your disposal, and how collating them can inform your business strategy.