Sometimes, unfortunately, we don’t have a CRM to store useful information. Good for us, today we are going to cover a workaround that can help us to store and use this information.
In the previous blog posts, we have covered the basics of GTM, as well as a bit more advanced articles such as tracking forms or drift chat interactions. Today, we will take another step forward and connect GTM with other platforms and services.
Before anything else, the first question we have to ask ourselves is: “why should I want to store GCLIDs?” Storing GCLIDs is useful when the quality of the lead depends on future action. For example, let’s say that you are generating leads through a form. Not all of the leads will have the same value for the business; for example, one lead can deliver a £300 business while another one £10 or directly 0. Therefore, it might seem wrong to treat them all as the same number.
To give another example, it can be that a top-of-the-funnel campaign brings 10 leads through a form, and after the salespeople contact those leads, only 1 converts. On the contrary, another top-of-the-funnel campaign generates 5 leads, but in this case, 3 users convert. It would be interesting to scale up this campaign and re-orientate the former campaign. Taking these decisions is only possible when we have all of the information regarding the user journey.
Offline conversions allow you to upload conversions with a value and it only asks for a GCLID number and its conversion value. The challenge here is to get the GCLID for Google to be able later to match it to a click and give us all of the information about the conversion (campaign name, the hour of the day, keyword, etc.).
These are the steps we are going to follow:
- Collect the GCLID on the website.
- Set up the Google Sheet to collect the values from GTM.
- Set up the tag and triggers in GTM.
Collecting the GCLID
Aitor, Aitor, stop… what is the GCLID? The GCLID is a URL parameter that gets appended to our ad’s final URL (if auto-tagging is active at the account level). It will look this way:
https://www.ilovecakes.co.uk/unicorn-rainbow-sprinklescake.html?gclid=EAIaIQobChMIonV0sLF4QIVzbvtCh0EiwIKEAQYBCABEgJCIfD_BwE
By using this, we can use that unique identifier to map it into a conversion that could potentially happen after the user gets to the landing page. For example, when they finally decide to make a custom order using the form on the landing page. As the GCLID is nothing but a URL parameter, we can store that in cookies or the localStore to send it to our tracking solution (a google sheet in this case).
Google provides us with a piece of code to store the GCLID value in the localStore. This piece of code should be fired before the tag on our page:
<script>
function getParam(p) {
var match = RegExp('[?&]' + p + '=([^&]*)').exec(window.location.search);
return match && decodeURIComponent(match[1].replace(/+/g, ' '));
}
function getExpiryRecord(value) {
var expiryPeriod = 90 * 24 * 60 * 60 * 1000; // 90 day expiry in milliseconds
var expiryDate = new Date().getTime() + expiryPeriod;
return {
value: value,
expiryDate: expiryDate
};
}
function addGclid() {
var gclidParam = getParam('gclid');
var gclidFormFields = ['gclid_field', 'foobar']; // all possible gclid form field ids here
var gclidRecord = null;
var currGclidFormField;
var gclsrcParam = getParam('gclsrc');
var isGclsrcValid = !gclsrcParam || gclsrcParam.indexOf('aw') !== -1;
gclidFormFields.forEach(function (field) {
if (document.getElementById(field)) {
currGclidFormField = document.getElementById(field);
}
});
if (gclidParam && isGclsrcValid) {
gclidRecord = getExpiryRecord(gclidParam);
localStorage.setItem('gclid', JSON.stringify(gclidRecord));
}
var gclid = gclidRecord || JSON.parse(localStorage.getItem('gclid'));
var isGclidValid = gclid && new Date().getTime() < gclid.expiryDate;
if (currGclidFormField && isGclidValid) {
currGclidFormField.value = gclid.value;
}
}
window.addEventListener('load', addGclid);
</script>
To implement it, we will head to GTM > New Tag > Custom HTML. And we will paste the script.
Once we finish creating the Tag, we can fire it on every page, or we can fire it only when the GCLID parameter is on the landing page. I went for the second option, so I created the next trigger:
We preview it on GTM and we go to our webpage. Imagine that one of our users finds our brand new “iphone Carrot” sale on an organic result and clicks it:
As there is no GCLID parameter attached to the URL, our tag won’t fire. Instead, if they click on an ad, the scenario changes. Check out this:
Good! The tag is firing. But… what happens now? What’s its function? What this piece of code does is to store the GCLID value into the localStore, we can also create a cookie out of it, but we will cover that in another article. Let’s double and triple-check that the GCLID has been stored correctly once that the user has gone to check the cart. We can do that easily by going to “Developer tools in Chrome” (F12) and typing “localStorage” on the console.
There you are!
Set up the Google Sheet
Thanks to this awesome article by ganotes.com we can easily set up a web app to begin collecting data. Let’s go ahead, then! We will go to our Google account > Drive > New > Spreadsheet. The new Spreadsheet will contain the columns we want to store the information and the “keys” as the primary values, as easy as that.
Afterward, we will go to “Tools” > “Script Editor”. And we will create a new script that will look this way after pasting the code:
// 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 that, and without leaving the page yet, we will click on “Publish” > “Deploy as web app” with these options:
That’s it! We are almost ready now. We will save the “Current web app URL” somewhere as we will need it for later on.
Sending the information to the Sheet
Now we are collecting the GCLID and storing it in the localStore, but how can we send it to the sheet? Well, it’s time to set the variables!
Variables will collect the actual GCLID values stored depending on the user and after we have them, we will use them to set the final tag.
If you are familiar with JS, you might think that a simple variable JavaScript variable like this is enough.
Unfortunately, it is not enough for us. We would get a not-very-nice “Undefined” as we are pushing an array:
This is the workaround I’ve found to be the most comfortable one for me, but feel free to text me if you find something easier!
We will refresh our GTM preview and our webpage to see if the value appears:
The final step is coming soon, I promise!
We have the saved GCLID, and the variables to send it wherever we want. So, let’s end up this and send the information to our Sheets.
- Set the timestamp variable
In my case, I don’t have a message ID set up that could potentially help me identify and could help me mapping it back to the user. Instead, I’ve thought about a timestamp that will be unique and I will be able to access from my messages to pair it with a certain user. It is as easy as setting a Custom JavaScript Variable with the following value:
function(){
var today = new Date();
var str = today.toUTCString();
return str;
}
Which will return a value like “Wed, 10 May 2021 14:01:40 GMT”.
- Set up the conversion Tag.
In my case, I want to collect those GCLIDs that send a form. A plugin I installed (“GTM For WordPress by Thomas Geiger”, very recommended) makes it easy for me, as the event gtm4wp.contactForm7Submitted triggers every time a form is sent. Therefore, I will use that trigger to set up my Tag, but you can modify this into your own needs!
We go to “Tags” > New Tag > “Custom Image”. In the image URL, we are going to paste the link we saved before in step 1 (where we created the web app). It will look something like:
https://script.google.com/macros/s/AKfycbxNGJ6o8HgE5WCwGS2CJrNgcVn46s3ys-rbavHXdG-jRHE8TQ8y/exec
We will slightly tweak it to send our variables until it looks something like this:
https://script.google.com/macros/s/AKfycbxNGJ6o8HgE5WCwGS2CJrNgcVn46s3ys-rbavHXdG-jRHE8TQ8y/exec?Timestamp={{Timestamp Variable}}&GCLID={{GCLID Field}}
So, after the “exec?” we have to include the following:
- The placeholder of the google sheet={{GTM variable name}}&second placeholder={{GTM variable 2 name}}
Let’s send the form and see if that works.
Finally, it’s working!
Conclusion
Not all leads should be given the same value and attributed equally. This little workaround will allow us to evaluate each lead individually and upload all of them based on the value they have had for our business. There are plenty of ways to improve this implementation but we have to bear in mind that pushing values to a Google Sheet should be only in those few cases where a CRM solution is not possible or is too expensive.
You can even go further now, why not integrate this with Zappier to automate the process? And what about connecting it to your Azure account to enrich your data? There are lots of possibilities that we will explore in the coming articles!
