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.

// Usage
// 1. Enter sheet name where data is to be written below
// 1. Enter sheet name and key where data is to be written below
var SHEET_NAME = "Sheet1";
var SHEET_KEY = "insert-sheet-ID-here";

// 2. Run > setup
//
// 3. Publish > Deploy as web app
// - enter Project Version name and click 'Save New Version'
// - set security level and enable service (most likely execute as 'me' and access 'anyone, even anonymously)
//
// 4. Copy the 'Current web app URL' and post this in your form/script action
//
// 5. Insert column names on your destination sheet matching the parameter names of the data you are passing in (exactly matching case)

var SCRIPT_PROP = PropertiesService.getScriptProperties(); // new property service

// If you don't want to expose either GET or POST methods you can comment out the appropriate function
function doGet(e){
return handleResponse(e);
}

function doPost(e){
return handleResponse(e);
}

function handleResponse(e) {
var lock = LockService.getPublicLock();
lock.waitLock(30000); // wait 30 seconds before conceding defeat.

try {
// next set where we write the data - you could write to multiple/alternate destinations
var doc = SpreadsheetApp.openById(SHEET_KEY);
var sheet = doc.getSheetByName(SHEET_NAME);

// we'll assume header is in row 1 but you can override with header_row in GET/POST data
var headRow = e.parameter.header_row || 1;
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
var nextRow = sheet.getLastRow()+1; // get next row
var row = [];
// loop through the header columns
for (i in headers){
if (headers[i] == "Timestamp"){ // special case if you include a 'Timestamp' column
row.push(new Date());
} else { // else use header name to get data
row.push(e.parameter[headers[i]]);
}
}
// more efficient to set values as [][] array than individually
sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
// return json success results
return ContentService
.createTextOutput(JSON.stringify({"result":"success", "row": nextRow}))
.setMimeType(ContentService.MimeType.JSON);
} catch(e){
// if error return this
return ContentService
.createTextOutput(JSON.stringify({"result":"error", "error": e}))
.setMimeType(ContentService.MimeType.JSON);
} finally { //release lock
lock.releaseLock();
}
}

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.