skip to Main Content
Using Google Spreadsheets As CRM Tool

Google saves people from big troubles thanks to their free products across the market. With the following detailed integration, I’ll show you how you can use Google Spreadsheets as a CRM tool.

We need Google Tag Manager and blank spreadsheet to use Google Spreadsheets as a CRM tool.

We put the dimensions we want to track on a blank Google spreadsheets page. I have created a table as follows for demo purposes.

Here, by pressing the timestamp, I wanted to see the Google Analytics sign-in IDs, the member IDs that on my site, email addresses, page URLs they visit, operating systems, source of the page, name of the page and if they use Adblock. This is fairly simple, and you can take action according to the action you want, aside from the experimental table. For example, I ran it by the Page view event, you can develop actions based on members or by people who come to the basket page. It all depends on your marketing skills.

To do this, we need the script below. I found this script while investigating the topic: http://www.ganotes.com/pass-dynamic-data-to-google-sheets-using-google-tag-manager/

I told you how to run it in script, but it first, we have to access to the Tools > Script Editor in our spreadsheet. We must also add the following codes to the script screen and follow the steps in the code block below.

 

After performing the above operations, you have to run it through GTM. The variables will run our help at this point. In this field, we will decide which variable to send to which colon. What we need to do is create a new “Custom Image” tag on GTM. We will also use the URL of our script to create this tag. A URL will consist of the following:

https://script.google.com/macros/s/AKfycbxd1UW1K2Hd9Wzd2qC98Gtbsa/exec?Timestamp={{Timestamp}}&Email={{Email}}&memberID={{memberID}}&PageURL={{Page URL}}&OS={{OS}}&Referrer={{Referrer}}&PageName={{PageName}}&sessionID={{gaSessionId}}&AdBlock={{adblocker}}

When we interpret this URL, the following situation occurs: I will put my variable {{Timestamp}} to the Timestamp column, {{Gasessionid}} variable on my SessionID column. Of course, you need to create these GTM variables in GTM before.

It’s time for the trigger part, which circumstances should we have to run it under. Like I said, I ran it through Page View Event. You can use it as you like.

In the final case you will have an image like this on your GTM screen:

The image I get at the end of all these procedures is as follows:

I couldn’t show you some data, of course, in secrecy. But if you do the above, you will be greeted with a similar display. You just need to decide what you want to analyze and what conditions you want to read. The rest is unraveling in any way.

Berkan Bagci

I am an online marketer who focused on web analytics for years. I did lots of GTM and GA setups for big websites in Turkey. Also I made a success story with Google Optimize and it has released on Jan 2018.

This Post Has 0 Comments

Leave a Reply

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

Back To Top