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.