Update 25.6.2015: The process of creating Google Analytics reports in Google Sheets is now much more user-friendly, thanks to the official Google Analytics Add-on. Check out our updated tutorial.
In the first part of this post, we discussed how to configure Google Docs and connect it with a Google Analytics account. In the second part, we are going to insert the Google Analytics queries, create the report and use the auto-update function.
So, let’s start.
1) Go to “Google Analytics -> Create core report” from the main menu, to generate the first query cells. For every new query we are going to create, we have to repeat this step.
2) The first field is the account “ids”. To find yours, go to “Google Analytics -> Find Profile / ids” in the main menu.
3) Select the appropriate Google Analytics account and profile in the displayed list.
4) Copy the ids value and paste it into the related cell in the document.
5) For the date range, you can select a starting and ending date, or define a range of n-days. As we are creating a daily report, we are putting the string “=today()” in the start-date and end-date cells, in order to display the figures only for today.
6) The next two fields, “metrics” and “dimensions”, define the data that we are going to pull from Google Analytics. If you are not familiar with them, check the Core Reporting API reference. (In our example we are pulling the data for visits and visitors in the same query.)
7)The next series of fields define the data selection and the displayed results. “Sort” defines the order, “filters” apply specific filters to the data using the Core Reporting API format, “segment” applies specific segmentation from your Google Analytics account, “start-index” is the starting point of the results and “max results” is the maximum number of results that will be created. The “sheet-name” is the name of the worksheet that will be created. Every query creates a report in a new sheet, so be sure that this field is different for every query. In our example we do not use “sort”, “filters”, “segment” and “start-index” and therefore leave these fields empty.
8) In order to insert a new series of data, select again “Google Analytics -> Create core report” in the main menu. Two new columns will be created, containing the query cells that we described earlier. All we have to do now is to fill in the details for the new query. As we would like to display the data not just for the current period but also the difference compared to the previous period, we are creating two queries, one for the current period and one for the previous one.
9) After the creation of all the queries, we have to make the initial pull of Google Analytics data into Google Docs. In order to achieve this, go to “Google Analytics -> Get Data” in the main menu.
10) A status report will be displayed, when the data import is finished. You will see, if the connection was successful and if there were any errors in your queries.
11) You will also notice a new tab for every query you inserted, containing all the data from Google Analytics.
12) The next step is to create a new spreadsheet, where we combine all the data in a one-page report. You can use all the functionality of Google Docs at this stage, for example doing calculations on the Google Analytics data, or create charts based on them. You can see an example report in the following image:
13) The final step is to make the report auto-updated. In order to enable this functionality, select first “Tools -> Script Editor” in the main menu.
14) Then select “Resources -> All your triggers”.
15) Select “Add a new trigger”.
16) You can define the parameters of your trigger using the drop-down menus. The report in our example will be updated every day just after midnight.
17) If you would like to download this report as PDF, it is possible to do so by selecting “File -> Download as -> PDF document” in the main menu.
That’s it!
The report is now finished and will be updated daily using the latest Google Analytics data. What we showed here is just a basic example. It is also possible to use it for advanced reportings by combining different Google Analytics accounts or creating multi-page reports.
