Google Analytics Offline Transaction Tracking via Google Sheets

Even though tracking your visitor activity online is a great step forward towards improving your customer experience or marketing efforts, sometimes however, that’s not enough. See, though I agree that tracking users online is a good direction to take, but, the real customer action still happens down here in this big bad world. That means the shops, over the phone, at events, this is where the magic is happening.

What does any of that have to do with Google Analytics? Brilliant question! What with the marketing teams becoming more and more technically equipped with each passing day, it’s become only logical to have your entire data collected on a single platform. This is where Google Analytics comes in. With the arrival of universal analytics, Google Analytics provides the flexibility to send custom hits ( pageviews, events, social and transaction) via the measurement protocol.

In this article I’ll be walking you through two super simple ways to import your offline data to Google Analytics. For this, we’ll be using Google Sheets and the Google Analytics measurement protocol mentioned earlier. Got both of those ready? Okay here we go.

With Client ID/User ID.

For this method you will need to store the client id or user-id of the user as a custom dimension in Google Analytics and your r backend/cms as well.

Whats Client ID you ask, By default, Analytics assigns each device a unique ID, and considers each unique ID as a unique user in your reports.which is know as Client ID.

This will allow you to store the individual purchases with the respective users, i.e, the very people who made those purchases. For this to be effective however, you will need to have this set up in advance, i.e before the actual purchase is made, so that the data could be connected to the original user.

Here is how you can retrieve the client id of user from the cookie.

Without Client ID/User ID

Now, this method requires less work and you only need a single client-id (Could be yours as well). The method of acquiring Client ID is mentioned above.

Even though this method is easier, and quicker, it will only store the transaction in Google Analytics and will not attribute it to any marketing source or user unless you pass traffic source data along with it.

In the following, are the steps you’ll need to follow to transform your data and send it to Google Analytics.

Here is a sample worksheet for sending data to a google analytics account.

You can copy our sample sheet with the app script code and work on it directly.

Now, to start off, upload the CSV to Google Sheets and make sure the CSV has the following columns:
- Date with time example 09/06/2018 15:01.
- Transaction Revenue in Dollar but without any currency sign.
- Product Name
- Product Sku
- Quantity Purchased
- Transaction ID

The above mentioned are required parameters for sending a valid transaction hit to GA but, there are of course, others that can be sent. If you want to know about those, feel free to check out this.

2. Select columns that contain the data you want to store in GA and create a new sheet, containing that data only. Set the headers for that sheet, and add header names for each respective column (all this does is make it easy to understand, but no pressure).

Update the tracking id in the configuration sheet.
Be sure to use a test account while doing a test run.

3. Before uploading the data to the live account. Go ahead and do a test run by clicking the Run button in the header, with a minimum amount of data in a test Google Analytics property.
After the upload, Audit the data in google analytics to make sure everything is recorded as expected.

One this is done, send the data to the live account and perform an audit again.
You can look at the real-time reports in google analytics and go to events and look at last 30 mins.

Additional Use Cases

Here are some additional use cases where this implementation can be used.

Importing historical data in Google Analytics ( Google Analytics only allows data that is a couple of days old).
Importing missing data, if for some reason your tracking broke down for a day and you want to store that data in Google Analytics.
Adding additional data to an existing dataset, for example adding product data to transaction IDs.
Adding refund data to Google Analytics.

Conclusion

Having both walk-in and online customers, I assure you, that your life would be much better if you just tie both data sets together on one platform. (brownie points for guessing which one!). With Universal Analytics it’s just a million times easier to view the bigger picture and get better insights for your business by using offline conversions and measurement protocol. And with that, i’m leaving you a couple hundred (no, i’m just kidding, just four) links to articles that you might find useful while working with the measurement protocol.