Skip to content
  • Curation Policy
  • Curation Policy
British Empire History
Use Google Sheets for Tracking and Analytics πŸ“‰πŸ“ŠπŸ“ˆ
Articles . Blog

Use Google Sheets for Tracking and Analytics πŸ“‰πŸ“ŠπŸ“ˆ

On August 29, 2019 by Raul Dinwiddie


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

Tags: analytics, and, data, database, digital analytics, Google App Scripts, google sheets, Google Spreadsheet, Google Tag Manager, Google Tag Manager course, gtmtraining12, internet marketing, JavaScript, jjanalytics, julianjuenemann, marketing, measure, the, this, tracking
Written by Raul Dinwiddie

11 comments

  • Comment by Get Found Madison December 21, 2016 at 3:17 pm - Reply

    Very cool. Why do you want the data in Sheets in addition to Google Analytics?

  • Comment by Christoph Kleine December 21, 2016 at 3:50 pm - Reply

    This is amazingly simple! (Once you have the Google Script) Pretty cool.
    You could think about transferring Product Descriptions, Search Result Lists, Contact Requests or something else into Google Sheets which you wouldn't want to use in Google Analytics. (given you were able to define the variables accordingly in GTM).

  • Comment by John Peter December 21, 2016 at 4:19 pm - Reply

    I am looking forward one of your video that talks about datastudio

  • Comment by Travis Heller December 21, 2016 at 5:13 pm - Reply

    for some reason, it is only recording contact submissions from when I am using my computer? Makes no since. Any idea why this would be happening? I can get the spreadsheet to capture my event form using contact form but it will only record from when I submit the contact form on my computer.

  • Comment by Morey Antonio Alimpangog December 22, 2016 at 12:38 am - Reply

    Very useful. Thanks Julian!

  • Comment by Kel Broven January 27, 2017 at 4:14 pm - Reply

    Is it possible to pull Tag Manger data from GA?

  • Comment by Kosta Jam May 17, 2017 at 4:54 pm - Reply

    Julian you are the man! thanks

  • Comment by Melis Han January 11, 2018 at 9:00 am - Reply

    Thanks Julian to share this useful report! I want to add more cells to my sheet however I reached the limit of cells. I cannot get any new data now:( Should I create new web app in a new sheet which is not practical solution or is there any better solution that you suggest?

  • Comment by Mark Sibert October 20, 2018 at 6:54 pm - Reply

    nice. do you have something that works with a Google calendar? I want to get date & time from and date & time too, downloaded together i.e. (01/25/2018 15:30).

  • Comment by Vaibhav Deshpande January 6, 2019 at 3:17 pm - Reply

    Great stuff, Julian. I had previously created a similar setup with Zapier! I believe, it's way easier for people who don't want to get into coding Google scripts. πŸ™‚

  • Comment by SmolSquishyTaeil June 26, 2019 at 9:22 pm - Reply

    Can this work for social media?

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Recent Posts

  • Business Case for Sustainability
  • Bill would give Florida workers 3 months of family leave
  • Workers Rescued After Cherry Picker Accident
  • Energizers & icebreakers that work anywhere 🌩
  • Crime Trackers: Tucson woman thankful for co workers who helped PCSD find man who robbed her
  • Rehab center helps coworker recover after deadly fire
  • KVUE Defenders: Misclassifying workers leads to unpaid wages | KVUE
  • Company that hired local workers goes silent
  • Better Business for a Better World (Full 3 mins)
  • Korail workers begin indefinite strike from Wednesday

Recent Comments

  • Rusty Nail 5 by 5 on Workers Rescued After Cherry Picker Accident
  • ItsSam on Company that hired local workers goes silent
  • Rohan Sheth on Should You Build a Lifestyle Business or Have an In House Team for your Marketing Agency?
  • Ozelia Borralhos on VÍDEO EXCLUSIVO PARA MEMBROS DA NETOLAB!
  • μ œμž„μŠ€ on 정말 κ³ κΈ‰μŠ€λŸ¬μš΄ 단독주택. λ§ˆλ‹Ήμ— λΆ„μˆ˜λŠ” κΈ°λ³Έ. μ•‘μžκ°™μ€ 전망은 κΈ°λ³Έμ˜΅μ…˜

Tags

and are Business Career comedy Corporate team building activity education entrepreneur for fun funny Fun Team Building Ideas game games Google how to job kids leadership management music news office politics Success team team-building teambuilding team building activities team building for unity team building games teamwork that the they this training tutorial video was work workers you your yt:cc=on

Copyright British Empire History 2019 | Theme by Theme in Progress | Proudly powered by WordPress

Back to top