Use Google Sheets for Tracking and Analytics 📉📊📈

– 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.

Written by Raul Dinwiddie

11 comments