How to create auto-updated Google Analytics reports in Google Docs - Part 2

Web Analytics

|

10. July 2013

 | 

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.

Google Analytics Google Docs

2) The first field is the account “ids”. To find yours, go to “Google Analytics -> Find Profile / ids” in the main menu. 

Google Analytics Google Docs

3) Select the appropriate Google Analytics account and profile in the displayed list.

Google Analytics Google Docs

4) Copy the ids value and paste it into the related cell in the document.

Analytics ID

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.

Google Docs Google Analytics

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

Google Analytics Google Docs

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.

Google Docs Google Analytics

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.

Google Analytics Google Docs

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.

Google Analytics Google Docs

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.

Google Analytics Google Docs

11) You will also notice a new tab for every query you inserted, containing all the data from Google Analytics.

Google Analytics Google Docs

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:

Google Analytics Google Docs

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.

Google Analytics Google Docs

14) Then select “Resources -> All your triggers”.

Google Analytics Google Docs

15) Select “Add a new trigger”.

Google Analytics Google Docs

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.

Google Analytics Google Docs

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.

Google Analytics Google Docs

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.

Add comment