Data Analysis with Google Sheets – How to do it?

Spreadsheet software like Excel or Google Sheets are still a very widely used toolset for analyzing data. Sheets has some built-in Quick analysis features that can help you to get a overview on your data and very fast get to insights.

#DataAnalysis
#GoogleSheet
#measure

Links mentioned in the video:
Supermetrics: http://supermetrics.com/?aff=1014
GA Demo account: https://support.google.com/analytics/answer/6367342?hl=en
Learn more from Measureschool: https://measureschool.com/products

Looking to kick-start your data journey? Hire us: https://measureschool.com/services/

Recommended Measure Books: https://kit.com/Measureschool/recommended-measure-books

Gear we used to produce this video: https://kit.com/Measureschool/measureschool-youtube-gear

Our tracking stack:
Google Analytics: https://analytics.google.com/analytics/web/
Google Tag Manager: https://tagmanager.google.com/
Supermetrics: http://supermetrics.com/?aff=1014
ActiveCampaign: https://www.activecampaign.com/?_r=K93ZWF56

FOLLOW US
Facebook: http://www.facebook.com/measureschool
Twitter: http://www.twitter.com/measureschool

In this first part of our data analysis with Google Sheets series, we’re going to take a look at the built in functionalities of Google Sheets to analyze data, all and more coming up. 

Hey there welcome back to another video of measureschool.com, teaching you the data driven way of digital marketing. My name is Julian. And if you’re new to the channel, we do marketing tech reviews, tutorials, and the occasional live stream, all on the data driven way of digital marketing here on this channel, so if you haven’t yet, consider subscribing down below. Now today, we want to kick off our series on data analysis with Google Sheets. Now Google Sheets and Excel for that matter, are still a widely used BI tool for analysis and data analysis. As a whole, you might be able to actually analyze your data in Google Analytics, for example, on AdWords, but it gets so much more powerful once you pull it actually down. Do your own analysis and dig through the data in the raw data and most of the times with Google Sheets. So in this series, we’re gonna take a look at all these different tools that you have available in Google Sheets to slice and dice your data and get meaningful insights out of the data. And we’re gonna start out with the built in functionalities of Google Sheets, and how you can do quick analysis, get a quick overview with some data. Now we got lots to cover. So let’s dive in.

 

Alright, before we start in Google Sheets, we actually need to have some data available. So I’ve logged into the Google Analytics demo account that you can also get access to just check out the link in the description below. And I’m here in the conversion, ecommerce product performance report where we have some nice data in here that we want to analyze. Now, the next step would be copying this out into Google Sheets. We could do this manually, obviously, but this is quite cumbersome. So Google has an export function. Right here that we will use, just click on Export and Google Sheets here. And we can import the data right into Google Sheets. Now if you would like to optimize this whole process, I’d recommend a tool called Super metrics, which you can install as an add on to your account and can pull the data directly from the API. I’m also going to link this up in the description below. Now that we have this sheet available with all our data, let’s look at how we can analyze that data a little bit. First up, let’s clean this data a bit. So up here, this shouldn’t belong to our data set, we can simply market and press the delete button. If we wanted to put this in the first row. Because this I had a row I can actually get rid of the other rows by marking them as well. Right clicking and deleting these rows. Now we have our first row as a header row. 

Now it’s also important to visually make this data a bit more appealing so we know at a quick glance What we’re looking at. So here’s our header row, let’s mark this and actually make this bold, we could also change the background color here, example to blue, change the font color, and so on. There are many different options to actually change data and make it more accessible when you look at it at a quick glance. Now, if you scroll down this data, we actually at the end here, scroll down even further, have some additional data that is pulled. This is actually the data that is available from this graph here. You won’t need this for now. So I’m just going to get rid of this. And also the last row here summarizes some eight all the data above we’ll get rid of this as well because we will do this data analysis the summation in ourselves in the sheet. So now we are left with our raw data that we want to analyze. And this is already one of the first steps that we went through. We actually cleaned our data, we made it a little bit more accessible. So we are ready to analyze a clean data set, as opposed to going back and forth between a data set that we haven’t cleaned yet and need to take care of on the fly later on. So make sure your data is in the correct format and convenient to read in order to go over to the analysis step. All right, next, let’s do a little bit of quick analysis with the built in tools that we have in Google Sheets and can utilize right away when we spot something in a data that we want to analyze. So first up, how can you sort this table now obviously, right now, it’s sorted by the product revenue, but we can sort by any kind of column, all we need to do is hover over the column that we want to sorted by click here on sort from A to Zed. So now we get the zeros most likely, up here. Now what we have just done is also sought our first header column and in order to prevent us Let’s go back, just press your Ctrl Z, we need to actually freeze this column. You can do this by simply dragging down this part of your spreadsheet down here. So now the first column is frozen. And when we scroll down, it actually stays in place. So we’ll be able to still analyze the data again, go back here and press this time on SOT Zed to a. So it will show us our highest quantities first. And we can see this product has been sold most often in terms of the quantity that was put into the basket. So again, you can get quick insights by sorting these columns or finding particular outliers. So if you go back here and say, Okay, these were never sold, but some have been put into the basket quite often, but never bought. Maybe something to look into even further. If you find anything that strikes our analysis Fancy, we can do quick analysis as well with the quick sum functions. And this is done by just simply marking the cells that you want to analyze. So for example, let’s go with the first 10 products here, and we are marking the quantity. And then down here in the right bottom corner, we can see a function that has quickly summed up all the values that we had in our range here of the cells. So our top 10 products that were sold, make up about 9000 units, as opposed to, let’s mark the whole quantity column just by clicking up here, the 17,600 products that were sold overall, so we get a quick ratio of how much the best 10 products or the most sold products are in comparison to the overall pie. In this case, it would be over a half of the product. So again, quick analysis just through the built in features here. have Google Sheets. Once we click on this box, we can actually see other data such as the average, the minimum, the maximum, the count, and the count numbers, which are actually the numbers minus the header row here in this deck. So again, without doing any kind of fancy functional analysis, we have a quick overview of our data. Now, you might also want to filter out data because we have a lot of values in here that maybe didn’t sell anything. So right here, we just want to see the products that actually sold something. How would we achieve that? Well, there is a built in button right here, which is this filter button, we can just click on it with our header row in focus. And it will summarize our data in this table and we get these little buttons here in our header row. And we can click on this and then we get down here all the values that are in here. You can also put in a Filter condition. So for example, our sell should be greater than zero. And now it should take out all the different products that didn’t sell at all. So quick way to actually filter our data, we have other mechanisms. And here, again, we have our sorting options, we have the values. So for example, if we get rid of this, go back to normal. Let’s go here to our product column and say, we only want to have products in here that actually buy the brand. Next, let’s go back. Let’s clear them all and then go to nest and select them all that are in our list. Press OK. And now, we only have our nest products here in this table and can compare that so really a quick, very quick way to use the built in functionality of this filter functions within Google Sheets. Let’s go again to select all. So we go back.

And here we are back in our products. Now the last built in feature that I want to show you is actually something new, a big push that Google is doing in all of its products, which is artificial intelligence. And this is built into the Google Sheets product here with this Explorer. So when you click on that, Google will analyze our data and give us some suggestions here of questions, we might want to ask like average of the order quantity. Let’s click on that. And that would be 1.068. And this is this column that was analyzed. So we don’t even have to fill in any kind of formula. It does it all automatically, we can see the formula so this is what we would put into this formula bar, which we’ll do in another video, but it really gives us quick analysis on the fly. If you’re interested in these questions. we scroll down we get some graphs that were analyzed like the basket to detail ratio, we get Unique purchases, how that is distributed. And we could even ask a question about our data, like how many products and it says here, how many products were sold of the maze pen count here is four. This is not quite correct. But again, the AI engine should be able at some point to pick up that we wanted to have the quantity of this product. So maybe we need to rephrase. And now we get the correct answer back. A bit counterintuitive, but again, pandas is a machine learning algorithm that will hopefully understand in the near future, all our questions that we would put into such a question and answer engine. So again, a quick way to do analysis in Google Sheets.

Alright, so there you have it. This is how you can use the built in functionalities. Google Sheets with a quick inside quick overview on your data. With the built in functionalities of sheets. We talked about sorting your data, but also filtering it. And then we looked at the functionality of explore your data within Google Sheets, and hopefully it will get a little bit better to answer all our questions on the fly. Now in our next video, we’re going to take a look at some more functionalities within Google Sheets to analyze our data. We’ll talk about functions for example. So be sure to subscribe to our channel right over there. And if the videos are already out, you can find it right there as well. And I hope to see you in the next lesson. My name is Julian. See you in the next lesson.

 

"

Google Sheets Formulas Every Marketer Needs (feat. David Krevitt)

Google Sheets Formulas Every Marketer Needs (feat. David Krevitt)

What are the most used formulas for marketers to do data analysis?

Google Sheets can be a great tool when it comes to collecting data from different sources. It gives you the opportunity to connect to a variety of tools like Google Analytics and Google AdWords and allows you to pull in the data from there, then analyze it and build a dashboard of it.
To get your raw data into the right format you need something that is called ‘formulas’.

In this video, we invited David from Coding for Losers to teach you what formulas you really need to analyze data in Google Sheets.

#Formulas
#GoogleSheet
#Measure

? Links mentioned in the video

[SHEET] http://bit.ly/2qaQAYq
[YT CHANNEL] Coding for Losers: http://bit.ly/2qNrjAP
[VIDEO] Google Sheets Formula Hacks for Marketers: http://bit.ly/2rsD2aw
Google Sheets: https://www.google.com/sheets/about/
Blockspring: https://www.blockspring.com/
Supermetrics: https://supermetrics.com/
Google Data Studio: https://www.google.com/analytics/data-studio/

? More from Measureschool

Correct Google Analytics Setup Course: https://measureschool.com/products/google-analytics-course/
GTM Resource Guide: https://measureschool.com/guide
Free GTM Beginner course: https://measureschool.com/emailcourse
Courses: https://measureschool.com/products

?Looking to kick-start your data journey? Hire us: https://measureschool.com/services/

? Recommended Measure Books: https://kit.com/Measureschool/recommended-measure-books

? Gear we used to produce this video: https://kit.com/Measureschool/measureschool-youtube-gear

? FOLLOW US

FACEBOOK: http://www.facebook.com/measureschool
TWITTER: http://www.twitter.com/measureschool

– In this video David is gonna tell you what formulas to use to analyze your data in Google Sheets. All and more coming up right after this. 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 do marketing tech reviews, tutorials, and give you tips on better analysis just like this one. So if you haven’t yet, consider subscribing. Now, you might know I am a big fan of Google Sheets because I can take data from different datasets, such as Google Analytics, Facebook, AdWords, and put them all together in one Google sheet, then analyze my data there and build a dashboard of it as well. Now, once you pull in all that data from these different sources, you will probably have a spreadsheet of raw data and then you need to work with that data in order to get it in the right format, get insights out of it, and maybe also build a dashboard of that. And the tools that you are using to do all this are formulas. Now, I’ve asked my friend David from Coding For Losers, he also has a YouTube channel right over there, to come up and answer the question what are the most used formulas for marketers nowadays to do analysis within Google Sheets, and here’s what he came up with. David, take it away.

– I’m not that good at guitar, just started learning, but to me learning guitar and learning Google Sheets, you know, when I first started learning it five or six years ago, they’re really similar, similar to learning anything. You have to put in time practicing, or else you won’t get any better. And there’s learning curve. You really have to kind of learn things in a specific order, otherwise you really end up confusing yourself, more than you really have to, and having less fun. And that’s the third thing. If you don’t have fun doing it, if you don’t have fun practicing and you don’t have fun moving up that learning curve, you won’t enjoy doing it and it’s, honestly, not even worth doing. So, stick to Excel if you feel like you get it and you don’t want to get into another learning curve and have fun with it. But Google Sheets, over the last couple of years, has really blossomed in terms of what it can do. You can now use all kinds of add-ons, like Blockspring or Supermetrics, or the Google Analytics add-on. It seems like every service now, there’re so many different add-ons that you can use to pull data into sheets and really make use of it there, without having to do CSV exports and all that kind of clunky stuff. And on the other side, in terms of like display of data, Google Data Studio is now this really great free, sexy dashboarding tool and reporting tool. So, you can use Google Sheets as this kind of hub to sit in the middle of the work that we do as digital marketers, pull data in using add-ons, use sheets as your hub to calculate all your metrics and all your changes overtime and all of your stats, all the stuff you want to look at, and then push them up to Data Studio so you have a nice, clean dashboard there to share with your team or other kinds of reports. So, Google Sheets has really become the Swiss Army knife, I would say, for us, digital marketers, anyone working in startups or tech, recently, over the last couple years. But, there is that learning curve. There is a fair amount that you need to learn to be comfortable and be fluent working in Google Sheets. So, let’s talk about Google Sheets formulas specifically and kind of how you can limit your vision in Google Sheets formulas, just learn the ones that you really need to know upfront and potentially never even expand your vision outside of this set of, I would say, about 10 formulas that you really need to know to kick ass in Google Sheets. The first one to narrow your focus, when thinking about sheets formulas, are to think about the jobs they do, because they really only perform, as I’ve seen, three jobs, and those three jobs are: they’re wrangle data in terms of plucking out just the pieces of data that you need removing everything else. They snipe data for you, so something like VLOOKUP would be sniping out just the value that you need. And they count, they help you come up with all of the averages and sums and counts for all the metrics that you need to calculate, the kind of aggregation functions. So those are the three jobs really, and once you think about formulas in that way you can pick a few formulas per job and forget about all the rest of them. So, if you have QUERY you don’t need COUNTIF and examples like that. So the first job of formulas, wrangling data. Those are doing things like processing text and dates, turning a date string into just a year or a month, filtering data that you don’t need, and merging data together so taking data that’s in two tabs and putting it into one tab. And there’re a few formulas that we use to do that. For processing text we’ll use things like, formulas like LEFT and RIGHT, the TEXT function itself for working with dates frequently, date formulas like TODAY. So if you want to calculate the last week you can always do like TODAY minus seven, stuff like that. For filtering data I really frequently use IF and IFERROR, which are kind of logic functions but let you, say you have a referring URL path and you want to boil it down to a channel, you can use IF statements to classify certain texts in URL as a channel in the UTM tags. Just one kind of offhand example. Merging data, I’ll often use formulas like ARRAYFORMULA to pull together large datasets into one tab, and also these curly braces that you see here are really really helpful in Google Sheets, and we’ll get into why later. So the second job of formulas is sniping. That’s you have a specific value you’re looking for from a table of other values and you want to look it up. So, the most common usage of sniping is the VLOOKUP formula and I also, in times where VLOOKUP doesn’t work, say you’re looking, you want to look left in a table versus to the right, you can use INDEX for that instead. So VLOOKUP and INDEX are really kind of the same type of formula. And the last job of formulas is counting. Now, this is where you can get kind of crazy with all the different formulas you want to use, you can use. You can combine things like FILTER with COUNT or SUMIF, but I honestly choose to just use queries for counting. For all averages, counts, sums, max, min, all of this aggregation work I hire query to do that job. So now you’re up to speed on the three jobs that formulas do, wrangling, sniping, and counting, and about 10 formulas that you can use to do those jobs in Google Sheets. So hopefully, this will really help narrow your focus when you’re learning sheets formulas and make it a lot more fun to get started. There is a cheat sheet that Julian will link to in this video in the description and you can use that cheat sheet to really dive into the needy-greedy of learning all these formulas, work through a bunch of examples, and get some of that fun practice that I talked about with guitar. Take care.

– Alright, so there you have it. These are the 10 formulas that you should know as a marketer to analyze your data in Google Sheets. Pretty interesting, right? Now, we’ve got the links that David mentioned in the description below, but he also did a video on how to use these formulas on his YouTube channel right over there, and you can also probably click right here right now to view this video. Now if you haven’t yet, consider subscribing to our channel over there, because we’ll bring you new videos every Wednesday. My name’s Julian. Till next time!

Process Refunds in Google Analytics with Google Sheets (GA Refundr Tool)

GA Refundr - Process Google Analytics Refunds with Google Forms

In this video, we’ll show you how to process refunds in Google Analytics with the help of Google Forms. Processing refunds in GA is not always straightforward because there is usually no page the user visits that serves as a send-off point for the data. That’s why we came up with the GA Refundr tool a google form that lets you send refund data directly into Google Analytics.

Read moreProcess Refunds in Google Analytics with Google Sheets (GA Refundr Tool)

"

Google Sheets tracking with Google Tag Manager

Google Sheets tracking with Google Tag Manager

Google Sheets is an awesome cloud-based Spreadsheet App that you can use to do analyse data. But it can also be use as a light-weight tracking solution. We can send data in and store it in a spreadsheet – In this video we are going to show you how

LINKS

Google Sheet Dashboard for GA https://www.youtube.com/watch?v=EujLlA7MM-c
Pass dynamic data to Google Sheets using Google Tag Manager http://www.ganotes.com/pass-dynamic-data-to-google-sheets-using-google-tag-manager/

GTM Ressource Guide: https://measureschool.com/guide
Free GTM Beginner course: https://measureschool.com/emailcourse
Courses: https://measureschool.com/products

Looking to kick-start your data journey? Hire us: https://measureschool.com/services/

RECOMMENDED MEASURE BOOKS: https://kit.com/Measureschool/recommended-measure-books

GEAR WE USED TO PRODUCE THIS VIDEO: https://kit.com/Measureschool/measureschool-youtube-gear

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.

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.

"

Google Analytics Dashboard with Google Sheets (Using the Reporting API)

Google Analytics Dashboard with Google Sheets (Reporting API)

https://measureschool.com/dashboard
Supermetrics – http://supermetrics.com/?aff=1014

Google Analytics Dashboard and custom Reporting functionality are not always flexible enough. That’s why Digital Analyst prefer to export the data out of GA (via the Reporting API) and manipulate the data in other Tools. In this video, we’ll take a look at how you can accomplish all these with Google Sheets and a Plugin called Supermetrics.

Tools mentioned:
Klipfolio – http://klipfolio.com/
Tableau – http://www.tableau.com/
R – https://www.r-project.org/
Google Sheets – https://www.google.com/sheets/about/
Supermetrics – http://supermetrics.com/?aff=1014

GA Reporting API – https://developers.google.com/analytics/devguides/reporting/

Query Explorer – https://ga-dev-tools.appspot.com/query-explorer/

*******
Free Email course: https://measureschool.com/emailcourse
Free Resource Library: https://measureschool.com/resources
More Measure Courses: https://measureschool.com/products

Hire us: https://measureschool.com/services/

*******
RECOMMENDED MEASURE BOOKS: https://kit.com/Measureschool/recommended-measure-books

GEAR WE USED TO PRODUCE THIS VIDEO: https://kit.com/Measureschool/measureschool-youtube-gear

I’m gonna show you how you can take your data to the next level and export it out of Google Analytics into Google spreadsheets to build dashboards, reports, and more. I will show you how to import the data, analyze it, and build a pretty little report and dashboard. This and more coming up in this special edition of Measureschool, so let’s dive in. Have you ever tried to put together a custom report or dashboard in Google Analytics? Yes, the features exist. But sometimes it’s cumbersome to pull all the data together and you can’t always get it to do what you actually want. In my opinion, Google Analytics is a great tool for quick analysis, a bit of research, and data discovery, but building customer reports or building a simple dashboard that you want to give out to clients is just not flexible enough. That’s why digital analysts actually export the data into different tools. When building a dashboard you can for example use the tool, Klipfolio, to display your data in a comprehensive manner. For data visualization and analysis a lot of analysts use tableau and if you really want to crunch data more advanced statisticians or analysts use a language called R which you can also connect directly to your Google Analytics account to pull the data into this environment. But sometimes you don’t want to go into such detail and just go back to the basics using Excel or Google Spreadsheets to view data. So how does it work? Google Analytics has the functionality to export data directly in their views, but it’s quite annoying because you have to go in to certain reports to pull down the data and then copy them over from spreadsheet to spreadsheet in order to pull all the data together. So there’s a better way. The reporting, API. And they actually showcase this on the demo side which you can find under GA dev tools.appspot.com If you go down to the Query explorer you will be able to try out the core reporting API. For that you need to be logged into your Google account in order for this Query explorer to have access to your Google Analytics and the first thing we see here is the view which is equal to whatever you see in your home tab. Here in the company demoshop.com master and the same is modeled here and then you can select your Query parameters. Let’s say we wanted to recreate a very popular report in Google Analytics which is the source medium report. You can do the same in the Query explorer by choosing the start date and the end date which was here yesterday. Then you can select the metrics. Now there are different metrics that you can select here and you can always click on the little info icon here which will take you to the different metrics that are available with different descriptions. And if you go back here and we just choose the sessions we also need to choose a dimension and in our case that would be the source medium dimension that we want to put in. So source medium, and all we do is just run this Query, and we get our results back in a little table here which has been pulled from the reporting API. Now there are different other parameters that you can fill in like segments and filters, but the really cool thing is also that you can define your sampling level and this is actually in the free version of Google Analytics, one way to get a sample of free data from your Google Analytics account via the API. Now you could write the script in Python or PHP to connect to the API and pull your favorite data but again there are better ways of doing this. In Excel some plug-ins like Analytics Canvas or Axon Analytics let you pull the data right into Microsoft’s Excel to analyze there and further manipulate it and put it into the format that you want. But I actually prefer Google sheets for easier reports and dashboards. Why? Well, Google sheets is a free tool, everybody can access it through a link that you share with them. You can annotate it, collaborate with others, and even leave comments on part of the sheet, and of course they are updateable, so if there’s new data available you can easily incorporate it into your existing sheet. So here is an example of a dashboard report that I’ve built for an e-commerce client from scratch with data directly from Google Analytics, and as a template you can download this at measureschool.com/dashboard and customize it to your business needs. And this is all made possible with a Google sheets add on that you can get for free in the extension gallery of Google sheets. It’s called super metrics. And it’s really a cool tool because it allows you to connect your Google sheet to the Google Analytics reporting API and pull the data directly into the cell that you need it to be in. Then from there, you can clean, analyze, and present your data however you want. So let’s go into a little demo here. So let’s say we wanted to get the visitors from last month into our spreadsheet. I usually open up a raw data sheet here that we can use to first pull the data in from Google Analytics, and then import it over, calculate it, and put it into our dashboard. So how would we do that? We would first of all launch our super metrics and I already connected my account correctly to Google Analytics and you can choose the data source, in our case, Google Analytics is already selected, the right view, and then select the dates, to have some default dates here. For example, this month’s to date and then all you need to do is choose your metrics. In our case it’s sessions but we could also take the users just as an example here. In the next option we’ll choose how we want to split the rows and the columns. In our case, the rows don’t need further splitting up because we already have sessions and users. But the columns should be split up by the actual month. Then as extra options, you can choose any segments that you have defined in Google Analytics, so this will be pulled from your Google Analytics account, if you have any special segments that you want to run this, fetch this data from. And we have filters that you can extra define in order to refine your data. Then under options there’s some really interesting options like avoiding data sampling which can be quite a headache when working with the free version of Google Analytics. And super metrics will then pull that data bit by bit which can take a little bit longer. I’ll keep this turned off. And what we can do now is simply get the data into the table. It will insert it into this cell that I have selected. And here we go, we get the sessions and the users for the month of March up to this date. We have sessions and the users. Now we could do any kind of calculation like you’re probably used to it from Excel or Google spreadsheets to get for example, the sessions per user. And if you choose so you can incorporate this number again into our report. Now to connect this all, you can go into the cell that you want to display this on and simply take the formula to connect it to your raw data. This is how you can easily pull data from Google Analytics, into a Google sheet with the help of super metrics. Now the paid version also has the abilities to schedule updates to these reports automatically and send them out via e-mail. So it’s really a great addition if you’re working with multiple Google Analytics accounts and want to send the data out to your clients. Now super metrics plug-in, at least the basic version is free for Google Analytics. They can connect to many different other tools such as AdWords or YouTube if you want to connect that data as well in your Google spreadsheet. But in a basic version when you only want to get the data out of Google Analytics. It’s free up to 100 rows, after that you need to pay a little price of 50 dollars a month in order to have these integrations available in your sheet. So if you want to check out super metrics, head over to our resource gallery at measureschool.com/supermetrics And don’t forget if you want a template for a dashboard so you can pull the data into the right fields you