Automating Google Analytics reports

Direct from the Experts: Digital Marketing Tips and Good Beers for 2016
Digital Marketing Strategies To Consider Before Redesigning Your Company Website

In Digital Marketing we know that running a business without constant analysis of results as traffic, clicks and conversions is like driving a car blindfolded.

And, for this analysis, one of the best tools available on the market is Google Analytics , which offers a free service for you to measure the actions taken by your site’s visitors.

If you do not have much knowledge in this area, we have a post presenting the key metrics from Google Analytics and the importance of each to the Digital Marketing a business.

In addition, we also offer some tips for you to extract even more from Google Analytics , using the dimensions in the results of view, create goals, display campaigns, monitor events and perform AB tests.

So, we have a tool that offers various analyzes of these metrics, allowing studies with combinations views, goals, campaigns, events, and others. And with all this information, we need to conduct studies that support for the decisions of your business.

But the problem is: these data are sufficient to support the decisions of Digital Marketing ?

In many cases, we need to correlate visits with campaigns carried by third-party platforms such as AdRoll or Facebook. Or relate some behavior with a given that there is only inside another platform, how to differentiate visits the stage of the buying journey of visitors Leads.

So the new question is: what do we do if we want to analyze Google Analytics data in conjunction with external data automatically?

In my last post I’ve tried to convince them of the reason , that every marketer should learn to program , and the answer to the above question is one more reason for the issue of the statement of the latter post.

The solution that we use here in the company, which will be presented in this post, is to create an automated report that pulls the necessary information to a spreadsheet using Google Apps Script and Google Spreadsheet.

If you read carefully here, you have a Google Analytics account and an account on the drive, so take this step by step and now create your first report.

Creating the Report

This step will set up the necessary tools to develop the report.

The first step is to create a folder for the project (I created the GA report folder).

In that folder, create a new spreadsheet in Drive, and save this report in that folder.

Now, we must enable Google Apps Scripts in Drive.

For this we must click on the gear in the top right and then Settings (Settings or if the drive is in Portuguese).

automating Analytics reports (1)

In the window that opens, go to Manage Apps and at the top click connect more apps.

In the search window, type “script” and click “connect” to the Google Apps Script.
automating Analytics reports (8)

So in the same way that the database was created, create a new script in the same folder.

Enabling Google Analytics API

With the file created, we must now enable the Google Analytics API to the script.

For this, the created script, go to “Resources” -> “Advanced Google Services”:

automating Analytics reports (2)

And in the open window, activate the Google Analytics API.

automating Analytics reports (5)

As we see in the message, we still need to enable the service on the Google Developers Console. Click the link in this notice and in the search field of the source page type “analytics”.

Then select the Analytics API and click “enable”

With this we have already properly configured everything you need to run the script.

Developing the report

Before we begin to develop, we will understand what are the steps taken by the script to mount the report.

In this step-by-step, we will get the 5 most accessed pages in the past 30 days, list them in a table and get traffic each in the previous 30 days, comparing changes in a third column.

The report will be assembled in a few steps:

  1. Access to the API GA and extract the most accessed pages of blog;
  2. Detect the 5 most accessed pages;
  3. Search accesses these pages in the previous 30 days;
  4. Calculate the change in traffic;
  5. Enter data in the worksheet;
  6. Set a trigger to execute the code periodically.

The next step is to write the code that will mount the report. In the script file, delete the existing code to begin to develop the script.

Then we will start using the GA API. The documentation of this API can be accessed at this link where we can find any reference to the use of this service.

To extract data, use the function:

1
Analytics. Date . Ga . get ( tableId , startDate , endDate , metric , options ) ;

At where:

  • tableId: value for the account access profile
  • startDate: dating from the early period of the data collected
  • endDate: date of end of the period
  • metric: amounts related to metrics you want to analyze
  • options: Object with some search options, such as filter or extra dimensions

To assist in filling these fields, we have the Query Explorer , a tool that allows you to experience and visualize combinations of these parameters.

In the above example, we listed to our account (the value of ids is filled in automatically after selecting the top) to the dates of the period and the pageview metrics (views).

It was also set up as an optional size (pagePath), listing order, and the filter (pagePath contains / blog).

So let’s start entering the first code by setting the period of the search:

The function of the Analytics uses the yyyy-MM-dd format, we will use a native function of AppsCripts to set the date we want for that format.

1
2
3
4
5
6
7
8
9
10
11

function getBlogPageviews ( ) {

var today = new Date ( ) ;
var oneMonthAgo = new Date ( today. getTime ( ) 30 * 24 * 60 * 60 * 1000 ) ;

var startDate = Utilities. formatDate ( oneMonthAgo , “GMT” , “YYYY-MM-DD ‘ ) ;
var endDate = Utilities. formatDate ( today , “GMT” , “YYYY-MM-DD ‘ ) ;

Logger. log ( startDate ) ;

Logger.
log ( endDate ) ;
}

At the end of this code use the Logger, which prints values ​​that can be read on View -> Logs.

After running these codes and make sure that the periods are correct, we will enter the function to collect Analytics data:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

function getBlogPageviews ( ) {

var today = new Date ( ) ;
var oneMonthAgo = new Date ( today. getTime ( ) 30 * 24 * 60 * 60 * 1000 ) ;

var startDate = Utilities. formatDate ( oneMonthAgo , “GMT” , “YYYY-MM-DD ‘ ) ;
var endDate = Utilities. formatDate ( today , “GMT” , “YYYY-MM-DD ‘ ) ;

var ids = “ga: number” ;
var metrics = ‘ga: page views’ ;

var options = {
‘filters’ : ‘ g: @ = pagePath / blog / ‘ ,
‘ dimensions’ : ‘ga: pagePath’ ,
‘sort’ : ‘-ga: page views’ ,
‘ max-results’ : ’50
} ;

var ssTable = [ ] ;
var report = Analytics. Date . Ga . get ( ids , startDate , endDate , metrics , options ) ;

if ( . report rows ) {
for ( var i = 0 ; i < 10 ; i ++ ) {

ssTable.
push ( report. rows [ i ] ) ;

Logger.
log ( report. rows [ i ] ) ;
}
}

Logger. log ( ssTable ) ;
}

In this code, we define variables for parameters that define with Query Explorer and create the object with the options. Then we use the tool that offers Apps Script to access the API. Finally, we pass the result values ​​for a table and print these values. To enter the results in the spreadsheet, insert the following code at the end of the previous function:

1
2
3
4
5

var ss = SpreadsheetApp. openByUrl ( ‘https://docs.google.com/spreadsheets/d/1AOIg7bkFBqYncnXL_6KSijH3bfziXglKuUWWHJXhdDg/’ ) ;

var sheet = ss. getSheetByName ( “Sheet1” ) ;

var tableRange = sheet. GetRange ( 1 , 1 , ssTable. length , ssTable [ 0 ] . length ) ; tableRange. SetValues ( ssTable ) ;

In this code, we access the spreadsheet with the SpreadsheetApp by entering the URL of the spreadsheet created for the project (insert the URL of your spreadsheet). With the spreadsheet saved in a variable, which define flap will be used (in the case Sheet1).

With the selected tab, we create a variable (tableRange) with the range in which the table data will be inserted. This range is defined by the position of the first cell (row and column) and then with the size in rows and columns. These sizes were obtained by the length property of the variable that contains the values.

Now the next step is to collect the values ​​for the previous month.

For this, we will repeat the way we took the data of the last month by changing the start and end dates of the periods using a new date twoMonthsAgo.

1
2
3
4

var twoMonthsAgo = new Date ( today. getTime ( ) 60 * 24 * 60 * 60 * 1000 ) ;

startDate = Utilities. formatDate ( twoMonthsAgo , “GMT” , “YYYY-MM-DD ‘ ) ;

endDate
= Utilities. formatDate ( oneMonthAgo , “GMT” , “YYYY-MM-DD ‘ ) ;

And then we will make a new request to the GA:

1
report = Analytics. Date . Ga . get ( ids , startDate , endDate , metrics , options ) ;

Because these results are not ordered in the same way as earlier, we must conduct a search for each line of the table we want to analyze, as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

if ( . report rows ) {
// iterate through each row of the table we want to ride
for ( var i = 1 ; i <= 10 ; i ++ ) {

// look for relative value, if not find adds 0
var found = false ;
for ( var j = 0 ; j < report. rows . length ; j ++ ) {
if ( report. rows [ j ] [ 0 ] == ssTable [ i ] [ 0 ] ) {

found
= true ;

ssTable
[ i ] . push ( report. rows [ j ] [ 1 ] ) ;

ssTable
[ i ] . push ( j i + 1 ) ;
}
}
If ( found == false ) {

ssTable
[ i ] . push ( 0 ) ;

ssTable
[ i ] . push ( 99 ) ;
}

Logger.
log ( ssTable [ i ] ) ;
}
}

In this code, we create a loop that passes in each row and looking at all the new results if the page is the same. If so, add value to the line and also adds the change in position from the previous month to the current month. Finally, we must change the first line of the table, editing the line where we define, changing of:

1
var ssTable = [ [ ‘Page path’ , ‘Views’ ] ] ;

for:

1
var ssTable = [ [ ‘Page path’ , ‘Views’ , ‘previous Views’ , ‘Change Position’ ] ] ;

And lastly, insert at the end the code to add this new data in the table (move to end).

1
2
3

var tableRange = sheet. GetRange ( 1 , 1 , ssTable. length , ssTable [ 0 ] . length ) ;

tableRange. SetValues ( ssTable ) ;

Now run and see the spreadsheet results.

automating

To set the code for it to run periodically, click the menu Clock Apps Scripts, shown in the following image.

automating Analytics reports (3)

Then, click Add New and select which trigger function and how often to run.

Okay, now you have a personalized and automated report.

You can now experiment with the queries and adapt the code to a specific need that you need to report recurrently.

Conclusion

When we talk about marketing automation , it is common for people to limit their thinking on autoresponders and scheduled campaigns, forgetting that this wheel rotates based and feedback and analysis results.

The automation of reports is an essential component of the operation of this machine. The daily monitoring generates unique insights that would hardly have been noticed in longer periods analysis.

I have helped in the development of this report, and helped demystify programming as a function of who attended college related to the topic.