5 Steps to Automate Google Analytics Reports

Google Analytics is a powerful tool for web analysis, but only if used in the most optimized way. Every analytics process has 5 phases:  Data Extraction, Data Manipulation, Analysis, Recommendations, and Testing/Maintenance.

The quicker you get your hands on the data, the more time you’ll have for actual analysis, which can bring out strong recommendations to improve your online performances. Therefore, it's always beneficial to automate (or in other words, eliminate) your Data Extraction and Data Manipulation process.

 

1) Get Google Sheets’ Google Analytics Add-on

Google Sheets has a FREE add-on that connects to Google Analytics (GA) API, and imports all your website data. To download this add-on, go to Google Sheets, click ‘Add-ons’ and then ‘Get add-ons’.

Once you download this add-on, hover over ‘Google Analytics’ under Add-on menu, and then create a new report. 

Here you can fill in your account information, and other information like Dimensions and Metrics. For more information on all fields check out: https://developers.google.com/analytics/solutions/google-analytics-spreadsheet-add-on.

 

2) Schedule Reports

Once you have successfully created a report in the GA Add-on, schedule it so that it gets refreshed as per your need. To do so, click on ‘Add-on’ from the top menu, then ‘Google Analytics’ and select ‘Schedule reports’.

Here you can schedule your GA report to refresh every day, week, month, or quarter. Beware that running a report every day can slow down your report’s server response time, and can quickly accumulate a lot of data. (Note: Google Sheets has a limit of 2 million cells.)

 

3) Google Query Explorer

Google Query Explorer is a useful tool to add filters, segments, and other regular expressions in your ‘conditions’ for custom reporting.

Connect your GA profile to Google Query Explorer; once connected, you will be able to get IDs for all Segments you have created in GA. It will also give you GA syntax for Metrics and Dimensions in your GA profile. For example ‘Medium’ in GA, will be ga:medium. These IDs and GA syntax are important in case you want to manually create reports in the ‘Report Configuration’ tab of your Google Sheets.

 

4) Schedule Email Delivery

Here’s the fun part! not only can you automate your reports, but you can also automate email delivery to key stakeholders. Here’s a ready-made code for you to copy and paste. (Note: A little bit of JavaScript knowledge would be helpful here.)

To place this code, click on ‘Tools’ from the top menu, and then click on ‘Script Editor’. Copy and paste the below code:

        function sendEmails() {

          var emailAddress = "xyz@example.com";  // email addresses for the key stake holders

    var message = "http://www.beaconfire-red.com/";  // Spreadsheets links goes here

    var subject = "Weekly Email Report"; //email subject goes here

    MailApp.sendEmail(emailAddress, subject, message);

  }

Click on ‘File’ and save the project. Next, click on the clock icon from the menu, and set up a trigger for the above sendEmails() function. You can set the trigger to every hour, week, and so on, and accordingly that function (i.e., emails) will fire during those times.

You can also QA the function by clicking on the play button to the right of the trigger button. It will trigger the function, i.e., send out the emails immediately.

 

5) Auto-save Reports in Google Drive

You can also save past reports in your free Google Drive account, acting as an active data repository. To do so, alongside your above email code, add the below code and repeat the same process for trigger setup:

 function AutoSave () {

var ss = SpreadsheetApp.getActiveSpreadsheet();

 var sheet = ss.getSheets()[54];

 

 var range = sheet.getRange("B1:B4");

 // The row and column here are relative to the range

 // getCell(1,1) in this code returns the cell at B2, B2

 var cell = range.getCell(1,1);

 var copy = ss.copy("[New_FOLDER NAME HERE]" + " " + "-" + " " + cell.getValue());

 var folder = DriveApp.getFoldersByName("[EXISTING FOLDER NAME HERE]");

  folder.addFile(copy);

 }

 

These 5 steps will give your Google Analytics reporting a 5x boost, making it fully automatic and recoverable.

 

Feel free to share this blog post with your colleagues and friends. Have questions? Contact Us.

 

At Beaconfire RED, we have a talented analytics team that helps nonprofits in making this world a better place. Don’t hesitate to reach out to us, if you need help to set up your analytics platform.


Are you excited about the epic stuff you just read?

Are you smart, motivated, and eager to solve problems in a collaborative environment? If so, we want you! Join our team!

See Our Current Career Opportunities