- In this video, I'm gonna show you
how you can use Google Tag Manager
to send data over to Google Sheets
and use it as a lightweight tracking tool.
All and more coming up right after this.
(upbeat music)
Hi there and welcome to another video of MeasureSchool.com
where we teach you the data-driven way of digital marketing.
My name is Julian and on this channel,
we publish marketing tech reviews, tips and tricks
and tutorials just like this one.
So if you haven't yet, consider subscribing.
Now Google Sheets has been a great companion
over the past few years in analyzing, automating
and discovering data.
For example, you can check out our video on
building dashboards with Google Sheets down below.
Today, we want to bring together Google Tag Manager
and Google Sheets to build a lightweight tracking solution
that you could utilize for example
to log errors or send data in that you don't want to
necessarily store in Google Analytics itself.
This is all based on a blog post
by Margarita from GANotes.com
which I will also link up in the description below.
Now we got lots to cover so let's get started.
All right, today our journey starts here
with a new plain Google Sheet that we wanna fill
dynamically with data through Google Tag Manager.
And this should actually act as our database,
each row representing a single data point.
Now what data do we wanna send in?
Well, let's replicate Google Analytics events.
So let's say we wanna track first of all the timestamp
when the event happened
and then the familiar properties such as
category, action and label.
To demonstrate all this,
I have set us up with a little bit of an example
in Google Tag Manager itself.
I've built up an event tag that registers Add to Cart Clicks
on our demo shop so when we click the Add to Cart button,
an event gets sent over to Google Analytics and
it is registered in Google Analytics.
Now what if we wanted to send that data
over to our Spreadsheet
and keep a record here in Google Spreadsheet itself?
Well, you know that Google Tag Manager
is capable of sending data to multiple tools
at the same time.
We just need to integrate this.
Unfortunately, there is no tag template
or no default way to send data into a Google Spreadsheet
so we need to make use of something called Google Scripts.
That's available here under Tools and the Script Editor.
What do we enter here?
Well, let's head over to the mentioned blog post by GA Notes
and we have here a script that is handedly prepared
which we can simply copy
and then paste it in here.
Let's name this all.
This will be our Event Logger script,
save this and there are a few configurations
that we need to undertake in order for this to work.
First of all, we need to define this sheet name
where the data should be stored.
So in our Google Spreadsheet, we have here our sheet name.
We can use whatever.
It is by default Sheet 1 which is also what is named here
and then we need to insert the sheet ID.
Now the sheet ID is what you see here between the d/
and the slash at the end here, do an edit,
that's the ID.
Let's copy that and enter that into our document as well.
All right, let's save this.
Let's save our sheet.
Let's rename that really quickly into Event Logger
and now we are able to publish this script.
Let's go to Publish and deploy this as a web app.
And then the important part is that you
choose down here who can access the app.
It needs to be open to everybody.
Don't worry, only you know the actual URL
and will be able to use it if you don't deploy openly.
Let's deploy this,
review our permissions.
We just need to Allow this
and we get a URL.
Now what will this URL do?
Let's try it out.
It will give us a response
saying that the script was executed correctly.
What happened?
Nothing really,
but we have a new line here in our Google Spreadsheet
with the timestamp, category undefined,
action undefined, label undefined.
Now why are they undefined?
Well, we didn't actually put in any query parameters
into the URL so let's do this really quickly
just to show you how it's done.
You can add a query string by putting a question mark
and then key value pairs,
whatever you have defined in your spreadsheet.
So first of all, the first property would be timestamp
which is automatically filled so we won't need that.
Let's go on with category.
You put in category and in between,
you put the equal sign
and then the value that we wanna post.
In our case, let's go with test category.
Let's do the same for the other parameters,
separated by an & sign.
And once we have it filled out,
let's execute it
and we have a success message here.
And now our row has been filled
with the actual values that we had filled in to this URL.
Now why is this so awesome?
Well, we can use this URL to fill
our data in dynamically by Google Tag Manager.
So how would we do this if we wanted to
recreate this event call of Google Analytics?
Let's look into the event call itself.
We see here the category is always clicks,
the action is filled dynamically with the click text
and the label is filled with the click URL.
Cool so let's recreate this by going into Tag.
This is a Sheets tag which also fills in event data
for our Add to Cart Click.
As a tag configuration,
we will this time choose the Custom Image tag.
Now the Custom Image tag only takes one parameter
which is an image URL.
It doesn't really have to be an image,
it can also be simply a URL that gets called
so we can input our script URL here.
Now we need to fill again our query string.
We have our category
which is also the same as clicks
and our action
which is dynamically filled by the click text
so we can simply use our variable here,
separate this with an &
and our label
is dynamically filled with our click URL.
We keep cache busting enabled
and simply use the same trigger
that will be used for our Google Analytics event.
Let's save this and try it all out,
refresh our preview and debug mode
go back to our page, refresh that
and I click the Add to Cart button
with the command key press
so it's opened up in a new tab
and we'll be able to see still our
preview and debug console
and we see our event has been sent to Sheets.
Now we should be able to see this in our Spreadsheet itself.
Now you may ask yourself, "Why is the label empty here?"
Well, let's go over to our page
and look at the actual tag itself.
We can see this was the URL that was executed
and in fact, there is no label attached.
We used the click URL variable so that should be upended.
Why wasn't that upended?
Let's click on the event that triggers our tag
and then on the variables,
we should be able to see our click URL, here it is
and it's in fact empty and therefore,
there was no value transferred
and that's because this Add to Cart Click
is not actually a URL but rather a JavaScript call
to the shop platform so
the user is not really redirected in terms of a link.
But let's say we wanted to change this to the page URL
just to make it a bit more useful.
You can do this really quickly,
try this all out
and here we go, we can see there was an Add to Cart Click
on this particular page.
So I hope you can see that this can be really useful.
If you simply want to recreate something
outside of Google Analytics,
I could also see this as useful if you wanna
track data that you're not able to track
within the confinements of Google Analytics
or just because it's not allowed to track that data
within Google Analytics or you wanna structure data
differently and connect it up to another Spreadsheet
or do any kind of calculations within Sheets itself.
The whole script is very flexible
so you can change the properties up here
and still send in the data.
You will need to still use the script
in order to make this work,
but these are the basic steps on sending data in
to Google Sheets via Google Tag Manager.
So there you have it,
this is how you can send data from Google Tag Manager
and store it in a Google Sheet.
Quite practical, don't you think?
Well, what else could be done with it?
I would love to hear from you in the comments below
and please share your ideas
so everybody can learn from them.
If you liked this video, please give us a thumbs up.
And if you haven't yet, consider subscribing
because we'll bring you new videos every Wednesday.
My name is Julian, 'til next time.