Pass dynamic data to Google Sheets using Google Tag Manager

Let’s say you want to use Google Sheets as a simple database to store your incoming inquiries with all details people fill in the form submission. You can’t use Google Analytics for it as it doesn’t allow personal identifiable information like email address to be stored. That’s why we’ll pass the data through Google App scripts to a Google Sheet, for free.

You can do the same with Zapier, where the coding part is hidden behind their beautiful interface, but their free version supports up to 100 tasks / month.

Let’s start by creating a simple sheet – my contact form has several fields I want to capture, like name, email, and subject. And in addition I’ll record a timestamp in GMT. So I’ll have these 4 as column titles:

table columns

Now we need the app script to do the magic. We go to Tools – Script Editor… and a new tab will open to write your script. We’ll remove the placeholder with the code below, courtesy of Martin Hawskey. It’s slightly updated, as his original version wasn’t working for me.

// 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();
}
}

We need to specify our Sheet so we copy the part between /d/ and /edit and paste it in the insert-sheet-ID-here quotes on line 5.

Google-sheets-ID

We can name our project however we like and save our script. As a next step, we select Publish – Deploy as web app, change the Who has access to the app setting to Anyone, even anonymous, and hit Deploy button.

deploy-web-app

The first time it will ask for authorization, click Continue and Allow and you’ll be presented with the web app URL:
web-app-URL

Copy it as we’ll need it to complete our setup.

Now, let’s create the variables in GTM that will pass the data we want. In my case I have 3 text fields and I’ll use their class names coming from Contact Form 7 WordPress plugin.

This is what my button looks like:

<input class="wpcf7-form-control wpcf7-text wpcf7-validates-as-required" name="your-name" size="40" type="text" value="" />

Name custom JS variable:

function() {
var inputField = document.getElementsByClassName("wpcf7-form-control wpcf7-text wpcf7-validates-as-required")[0];
return inputField.value || "";
}

Email custom JS variable:

function() {
var inputField = document.getElementsByClassName("wpcf7-form-control wpcf7-text wpcf7-email wpcf7-validates-as-required wpcf7-validates-as-email")[0];
return inputField.value || "";
}

Subject custom JS variable:

function() {
var inputField = document.getElementsByClassName("wpcf7-form-control wpcf7-text")[0];
return inputField.value || "";
}

You can quickly test them in the Console of Chrome Developer tools: custom-JS-variables-test

Timestamp custom JS variable:

function(){
var today = new Date();
var str = today.toUTCString();
return str;
}

Now we have all our variables and we’ll contruct our GET call:

https://script.google.com/macros/s/<replace with your web app URL>/exec?Name={{Name}}&Email={{Email}}&Subject={{Subject}}&Date={{Timestamp}}

Be careful to use exactly the same words as it’s case sensitive.

You can test how this works if you open the link in a new tab – you should get a success result {“result”:”success”,”row”:2} and if you check your sheet, it’s automatically populated:

Google-Sheet-populatedWe can remove the dummy data later.

Now let’s create the tag that will fire this in GTM, I’ll use a Custom Image tag. Give it a name, paste your string in the Image URL field and select your trigger (the form submission in my case):

GTM-tag
Create the tag, enter Preview and Debug mode and let’s see what happens. Go to your form, make a submission and we’ll see our tag being fired:

GTM-debug-mode-test

Also, when we check our sheet, it’s already there: Google-Sheets-final

Like magic! 🙂

Google Analytics sampling work-around with Google Sheets

Do you get a lot of traffic to your website? Maybe more than 500,000 sessions per month, or even per week? If you answered yes and you’re using the free version of GA, you know it’s a pain to run reports with any advanced segment or secondary dimension applied.

Here’s a way to automate this reporting using the Google Sheets GA add-on. If you haven’t used Google Sheets GA add-on before, please refer to this introduction.

In our scenario I’m assuming we’re having over 200,000 sessions per day, so we need to export the data on a daily basis and combine it for the month. Here’s a link to the Google Sheets template I’m using that you can save in your account by clicking File -> Make a copy.

Here’s how to populate the template, you need to fill-in the yellow fields only:

Sampled data template

  1. In the yellow fields in column A put your dates. In my example these are the individual dates of January.
  2. The View ID field should be the ID of your GA view. If you don’t know it, go to Google Analytics -> Admin -> View Settings and get your View ID. Don’t forget to add ga: in front of the number.

View settings

3. Metrics & Dimensions – you should list your metrics and dimensions in these fields, formatted in such a way that the GA API can understand them. You can refer to the list of popular dimensions and metrics in the right section or click the links below the fields to select the proper dimensions and metrics. You can pull up to 7 dimensions in one report.

4. Segment – I’m interested in traffic from mobile devices, so in the Segment field I’m building my segment. For a quick reference what’s possible with segments, check out the documentation using the link below the field.

5. Filters & Sort – if you need to use a filter you can do so here, and also sort the data.

6. Run the report – select Add-ons -> Google Analytics -> Run reports. In order to run the report you need to login to Google Sheets with the same email, that’s added as user in Google Analytics. You’ll get an upgrade notice the first time you run the report.

Now we have all the data we need. If you’re interested in summary metrics, you can see the results in tab Summary. If you need to get a list of the dimensions, check out the Collect tab. You can export it from there, clean up the headers, do a pivot table in Excel or Google Sheets and get the report you need.

Good luck!

Google Analytics Funnel Builder with mandatory first step

As a follow-up to my post about discontinuing PadiTrack and the suggested alternative with Google Sheets, here’s a short update after a few people requested a version with the option to select mandatory first step of the funnel.

So, here’s an updated version of the GA Funnel Builder where steps 2 through 10 require that the visitor has also viewed step 1. To save the document in your account, select File – Make a copy.

As a future version we’ll see how to add events to the pageviews being tracked as funnel steps.

If you have any other questions or ideas, please share them below.

P.S. Got a Google Analytics question? Send it to me and I’ll try to answer it on the blog.

How to create conversion page path report in GA

By default Google Analytics doesn’t provide a page path report. You can see the entrance and second step (Behavior – Site Content – Landing Pages – Entrance Paths) and in the last 3 steps before a conversion (Conversions – Goals – Reverse Goal Path). But if you’re running an ecommerce store, often the last 3 steps are part of your shopping cart and they’re pretty much the same for all conversions.

Understanding what pages customers are viewing and in what sequence could give us valuable insights, so let’s try to get this data using Google Sheets. I explained how to add Google Analytics add-on to Google Sheets back in December.

What we’d like to do now is built a custom report with more than 2 dimensions that are sorted in a particular way.

1. Start a new Sheet document and select Add-ons – Google Analytics – Create new report.

2. Select your GA Account, Property and View ID and the following metrics and dimensions:
Metrics: ga:pageviews,ga:entrances
Dimensions: ga:pagePath,ga:date,ga:hour,ga:minute

3. Hit Create report and add the following for Sort and Segment:
Sort: ga:date,ga:hour,ga:minute
Segment: sessions::condition::ga:transactions>0

Note: If you don’t have ecommerce tracking, you can use a segment sessions::condition::ga:goalXCompletions>0 where X is the number of your goal

4. Update your time range (Start Date and End Date) and Max Results.

You may also want to expand the Max Results to 10000. So your final configuration will look like this:

report-configuration

It will take only the sessions with transactions and show you the pages in these sessions, sorted by date, hour and minute, so basically it shows the path of each customer. The Entrances will show you which is the landing page for each session.

5. Run your report: Add-ons – Google Analytics – Run report.

report-result

Analyzing the data in this scenario will be possible with up to 200-250 conversions for the time range you selected. If you have more conversions, you may wish to add another dimension like City, or implement custom dimension for GA Client ID so multiple sessions happening at the same time are not mixed up.

This custom report is pulling hit-level data, so next week we’ll see how to combine it with session-level data like Transaction ID, Source / Medium and Revenue.

P.S. Got a Google Analytics question? Send it to me and I’ll try to answer it on the blog.

PadiTrack discontinued – here’s a Google Sheets alternative

I received an email today from PadiCode, the creators of PadiTrack, announcing the closing of this awesome free tool. The discontinuation will be effectively starting on March 15th, 2015 and the product will close down effectively a month later, on the 15th of April 2015.

PadiTrack is/was great for showing historical data of your conversion funnels (and many other things included in their Pro package). As you know, if you create or update your goal funnel steps in GA, they will show you data only going forward. One work-around is to build advanced segments and export the data in Excel or another tool. This is where PadiTrack was extremely useful.

Here’s an alternative of PadiTrack (although not that beautiful), using Google Sheets and the GA Add-on. Open the GA Funnel Builder and save a copy in your account.

There are several fields you need to fill in (colored in yellow) on the first sheet called Dashboard.

1. You need to get your GA View ID – go to Admin – View Settings and copy the number under View ID:

view-id

 

 

2. Enter it in the yellow field next to GA profile.

3. Define your start and end dates, by updating the values in the yellow fields.

step1

4. Enter your funnel steps by using the page URI, just like you’d define them when configuring a goal.

5. Select your condition: exact match, contains or matches regular expression by entering “yes” next to the option you want. The example here is using the default Magento shopping cart process.

6. Select Add-ons – Google Analytics – Run reports.

run-reports

7. Google Sheets will get the GA data and you’ll get a status report like the one below. In my example I have 3 successful reports as I defined 3 steps:

status

8. Your funnel is ready.

funnel

 

 

The final result / visualization could be improved a lot, here’s just the data. Also, segmentation could be easily applied or the number of steps expanded.

Note: PadiTrack is using the Users metric to calculate their conversion funnel, while I’m using Sessions here. If you’d like to show Users, you can modify the metrics in the Report Configuration sheet by changing ga:sessions to ga:users.

Hope this will help you in your conversion funnel building 🙂

If you have questions or comments, please share them below.

Update:

Here’s a separate version of the GA Funnel Builder where first step is mandatory.

P.S. Got a Google Analytics question? Send it to me and I’ll try to answer it on the blog.