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 performance. 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, open a sheet, navigate to Extensions/Add-ons/Get add-ons, and search for Google Analytics
Once you download this add-on, hover over ‘Google Analytics’ under the Extensions menu and then click on Create 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: 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 report.
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 can 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 if 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: Some 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:
var emailAddress = “[email protected]”; // email addresses for the key stake holders
var message = “http://www.teamallegiance.com/”; // Spreadsheets links goes here
var subject = “Weekly Email Report”; //email subject goes here
MailApp.sendEmail(emailAddress, subject, message);
}
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);
}
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 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:
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 Allegiance Group + Pursuant, we have a talented analytics team that helps nonprofits make this world a better place. Don’t hesitate to reach out to us if you need help setting up your analytics platform.