Exporting data from Google Analytics to a Google sheet can be done in several ways. Ahmad is going to show us three different methods, beginning with the built-in quick export via the Google Analytics interface.
So what is the best way to export data from Google Analytics to a Google Sheet. Well today Ahmad is going to show us three ways of doing so. All and more coming up. Hey there measuregeeks, Julian here. Back with another video for you today. We have Ahmad from Siavak.com back on a channel to show us three ways to export data from Google analytics into a Google Sheet. Why would you do so? In google sheets you can work with this data even more. Use the built in functionalities of Google Sheets, combine it with other data forms, transform it, do your analysis there or even then connect it to your data visualization tools like Google data studio to show this data to others. So Google Sheets is a really cool tool to do more data analysis on our data. But first we need to get it out from Google analytics to the sheet. How do we do this? Well today Ahmad is gonna show us three ways of doing so and gonna show us what the differences really are. Where we could make it a little bit more convenient. And he is also gonna dive into a data sampling. So as always we’ve got lots to cover. So I’m Ahmad, take it away.
Thanks Julian. Hello and welcome to another measureschool tutorial. This is Ahmad. And in this video I’m going to show you how to extract data from your Google Analytics account into a spreadsheet. You spend a lot of time, money, and resources to track quality data into your Google Analytics account. And there are several reasons you might want to get your data out of Google Analytics and import it into a CSV file spreadsheet like Microsoft Excel or a Google Sheet. You might want to merge your data with other data sources like user data from your CRM, cost data from advertising platforms like Google ads and Facebook ads, campaign data from e-mail marketing tool, or product profitability data from your shopping cart so you can do further analysis on your data. And finally to create better looking charts and visualizations to share with others. Today I’m going to briefly demonstrate and compare three different methods of extracting data from Google analytics into a Google Sheet, manual exports from Google Analytics interface, Google Analytics add on for Google Sheets and supermetrics. OK. So let’s start with the first method manual export. In short you used google analytics interface to adjust their reports the way you wanted to be exported and then you used a built in functionality in G.A. to download a CSV or excel file but directly export your data into a spreadsheet. Let’s see how it’s done. First you log in into your Google Analytics account and navigate to the report that you want to export. Then you need to adjust a report in the exact way that you wanted to be exported.
You can adjust the date, you can apply segment, you can add a secondary dimension or apply a table filter.
Once you’re happy with your reports, just click export and choose the way you want your data to be exported. You can download your report data as a file in a CSV or Microsoft Excel format.
Or you can directly export it into a Google Sheet. That’s it your data is extracted and ready. The benefits of this method is that it’s quick and easy.
There is nothing to learn. There is no software to install and it’s completely free. But there are also some issues. First of all the data set is a static. If you need to create reports on a daily or weekly basis you need to follow the exact same steps manually each time. The other problem is that the format of the data set isn’t the best for working with data. First we have this additional summary rows at the top.
Second ,we have the time series data table at the bottom which corresponds to the data put this time to his chart in Google Analytics. And finally we have total rows at the bottom of the table.
So if we want to work with this dataset we first need to remove these extra rows and data sets. Or we need to copy and paste the main table into another spreadsheet. Another issue with this method is that it only exports visible rows from your Google Analytics reports. So if for example we have our report set to only show 10 rows at a time only the rows that are visible here will be exported to the Google Sheet. If you want more rows to be extracted you need to increase the number of rows shown in this report. And as you can see the maximum is five thousand so you cannot export more than 5000 rows of data at a time. If you have more data you have to export multiple times page by page. So to recap. This manual export feature is quick and free but it’s only good for ad hoc analysis. And when you’re working with a small dataset. Next I’m going to show you how to do this process automatically and with more flexibility.
In the second part of this series, Ahmad will teach us how to export data automatically through Google Analytics reporting API using a Google Sheet add-on. This will also keep the extracted data set updated and refreshed on a regular basis.
Hello and welcome to the second part of this series on extracting Google analytics data into Google sheets. This is Ahmad. And in the first part of this tutorial I covered the manual way of extracting Google analytics data into a CSV or Excel file or directly into a Google sheet. We saw that the process was quick and easy. It was completely free and there was nothing to learn or install. We also saw that the process was good for one time adhoc analysis and when working with the small data sets, there are also some limitations. The process was manual and you needed to do it every time you wanted to extract your data. The resulting data sets was a static and only the visible rows from your Google analytics reports would have been exported to the Google sheets and that was limited to 5,000 rows maximum. Finally, the data sets wasn’t exactly in the best format for working with data because there were additional rows at the top and bottom of the dataset.
Now today, I’m going to cover how to extract the exact same data sets from Google analytics, but this time automatically and through to Google analytics reporting API using a Google sheet add on. In this second method, you can extract your Google analytics data into your Google sheet automatically and keep the extracted data set updated and refreshed on a regular basis. Let’s see how it works. First, create a new Google sheet and click add on under accounts. And then you need to find and install Google analytics add on for sheets. Once you’ve done, you can access it to add ons menu, Google analytics, create report. Then a sidebar will appear that allows you to configure your reports. If you remember from the previous tutorial, we exported to source media reports for two segments or users and direct traffic from 1st of January to the end of March. Now we would like to do the same through the Google sheet.
First of all, let’s give our reports a name then we need to select the account as a property and a view from which we want to extract data. Next we need to select what metrics and dimensions do we want to extract And for the dimensions we only want source medium. Next we can select one or more segments, all users and direct traffic. Now let’s create our reports. A report configuration sheet will be created in which we can review and tweak the report settings before we run the reports. Note that the date range has been set automatically to the last 30 days. It wasn’t possible to define it through the sidebar, but we can adjust it here.
There are also other configurations that we can adjust here. We can define the order of sorting, it can apply your filter and we can also define how many rows of data do we want to extract. If we leave it empty, it will return all the rows. Now let’s apply filter, so the API only returns data for us users. Okay. Once we are happy with the report configuration, we can go to add ons, Google analytics and run reports. Note that this data set seems to be cleaner than the one we used to get from manual export. There is no time series data table at the bottom but it’s still, there are summaries and total rows at the top. Now let’s check row number six. It says that this report contained sample data. It’s a free version of Google analytics where there are more than half a million sessions in the report timeframe.
Data sampling kicks in that means all available data won’t be processed but instead the numbers in the report will be calculated based on a smaller subset of data. In this case we can see that the numbers are being extrapolated based on less than 25% of the actual available data. In the final section of this tutorial, I will show you how we can try to eliminate the effect of data sampling in our reports. Now let’s see how we can automate this process because up to this point it was not so much different from the manual exports method. To automate the process of this extraction. we can go to add ons, Google analytics, scheduled reports. Here we can enable the reports to run automatically every hour, every day, every week or every once. Once we save this, the add on will run at the selected interval even if the sheet is closed and would refresh the data set automatically. So what you see the benefits of using Google analytics add on for sheets is that it’s quick and easy to install. It can help you dynamically pull data from Google analytics API. It can be scheduled to run on an hourly, daily, weekly or monthly basis to extract new data and the data set is cleaner than the one we can get from manual export. And finally it is completely free, but there are limitations as well. First of all, the options in the user interface are kind of limited.
We can only choose metrics, dimensions and segments. Everything else should be manually adjusted by entering data into cells. Off report configuration sheet even the date range. We also saw that the data set isn’t immediately ready for doing analysis because of the extra rows at the top. We also saw that the APR requests is subject to the same data sampling threshold as the Google analytics reporting in the face, but there’s also another limitation which I haven’t covered yet. If you’re a more advanced user of Google analytics, you know that there are two different reporting APIs. The core reporting API which allows you to access metrics and dimensions for the most part of a standard reports like channels, source medium and conversions to the last known direct click attribution model. And then there is MCF or multichannel funnels reporting API which allows you to access the metrics and dimensions like assisted conversions, conversion parts and also access to different attribution models. Say Google analytics add on for sheets only allows us to access core reporting API. So for example, we can have source medium in our dimensions, but we cannot have source medium parts.
It is not available or in metrics we can have conversion but we cannot have assisted conversion. The metric is simply not available. That’s it. This was Google analytics add on for Google sheets. We covered how we can use it to automatically extract data from Google analytics into a Google sheet, with some limitations of course. If you like all of these and also want to have some more flexibility and have access to some nice extra features, then make sure to watch definable part of this tutorial in which I’m going to introduce you to supermetrics for Google sheets. Thank you very much. Bye.
Final part of our GA export series, Ahmad will walk through the easy steps of exporting GA data using a third party add, Supermetrics. This will help us avoid data sampling plus some other nice extra features.
Hello, and welcome to the third and final part of this series on extracting Google entities data into Google Sheets. In the second part of this tutorial, the previous one, I covered how to use Google Analytics add on for sheets to automate the process of data extraction from Google Analytics API into Google sheets. We also saw that this point being free and easy to use, the tool had some limitations. In its final part, I’m going to introduce you to a third party add on named supermetrics. This is going to be exciting. So let’s begin. If you like the ease of use and flexibility of bringing Google Analytics data into Google Sheets through API, but would also like to have access to some nice extra features, such as having a workaround to try and avoid data sampling, you might want to take a look at supermetrics for Google Sheets. You can either install the add on through Google Search marketplace, or you can start with a blank sheet, go to add ons, get add ons and search for Supermetrics and then install it from here.
It’s supposed to mention that this is not a free tool, but it has a free forever plan that lets you access a limited set of features without limits. Or you can trial any of the paid versions for two weeks. I’ve already installed it. So let’s begin and use it. First of all, select the top left cell in the spreadsheet where you want your data to appear. I have selected a1. Then launch the add on sidebar by going to add ons supermetrics launch sidebar. We first need to select our data source. If this is the first time you’re using this add on, then you need to scroll down. Choose Google Analytics and authenticate supermetrics to connect to your account. Note that supermetrics can also connect to an extract data from many other data sources. Once we selected our data source, next we need to select the View from which we want to extract our data from. Next, we need to select the date range for reports. We can either specify the start and the end of our date range, or we can select any of the provided dynamic date ranges from the drop down menu such as today, yesterday, last week, whats to date last year, and many more options. I’m going to set the date manually. We can also select a comparison date range, but I’m going to leave that for now. Next, we need to select the metrics that we’re going to strike.
Next, we need to select the dimensions.I’m going to choose source medium.We can select how many rows do we want to fetch, and how do we want our data to be sorted plus, we can display our dimensions into columns instead of rows, but I’m going to leave that for now. Next, we can select the segments. And then we can apply a filter. If you remember from the previous tutorial, we applied a filter to only extract data from us users. There, we need to type it directly into the report configuration sheet. But here, we can create it within the interface. There are more advanced options available under the options menu. But in this tutorial, I’m only going to cover one of them. Try to avoid Google’s data sampling. If you remember, when we used Google Analytics add on for sheets in the previous tutorial, our report contains sample data. Now with supermetrics, when we check this checkbox, supermetrics tries to avoid Google’s data sampling by using an algorithm to break down a larger API request into a smaller ones to get on sample data from Google Analytics.
Now that we are ready and are happy with our report configuration, let’s click Get Data to table. As you can see, it is running four different softwares. It has broken down the main query into four sub queries. So the resulting data set is not subject to Google sampling. That’s it. The query has been completed. And here is our data set. Let’s take a quick look. First of all, we can see that we have a clean data set here. Unlike the manual exports, and the Google Analytics add on for sheets. There are no summary rows at the top of our data set, plus all the features and options to configure our reports. were accessible through the sidebar, and through the user interface, just like Google antics add on. It dynamically pulls data through the API, and it was able to avoid Google Data sampling. In addition, let me go back and modify the report configuration. In the metrics section, we can see that we have access to metrics from multi channel funnels reporting API, like assisted conversions. Plus assisted conversion value, and assisted per last click conversion ratio. Also, in the dimensions tab, we have access to dimensions that are part of the MCF reporting API, such as source medium part. That’s nice, isn’t it? Okay, so now let’s see how we can schedule this report to be refreshed automatically. Let me exit the query modification interface and go to add ons menu, supermetrics, schedule, refresh and emailing. Here we can select to either refresh or refresh an email the reports, we can choose our leads daily, weekly or monthly, for refresh only, or daily, weekly and monthly for refresh and email. Plus, we can choose to be notified if any of the queries fail when it’s refreshed. That’s it. I hope you’ve enjoyed this three part tutorial on how to extract data from Google Analytics into a Google Sheets. Now it’s your time to go to work and apply what you’ve learned. Thank you very much. This is Ahmad. Bye.
Alright, so there you have it. These are three methods of exporting your data from Google Analytics into a Google Sheet. Thank you very much to Ahmad who has produced this video for us. You can check him out at siavak.com. I have his link down below as well. And I would love to know which method do you like most, which one do you use most often? And maybe there are others that I don’t know about? Let us know in the comments down below. And if you haven’t yet, then subscribe to our channel because we bring you new videos just like this one every week. You can also check out this video, which is also pretty cool. Now my name is Julian til next time