How to automate Google Analytics reports in Google Sheets
Recently, we told you about the three types of Google Analytics reports and why it’s important to automate them. We also showed you how to set up a regular report in Google Analytics. In this article, we continue with the topic and tell you how to create reports in Google Sheets with data from Google Analytics, automatically update them on a schedule, and email them.
By the way, with the help of OWOX BI you can automate data collection and marketing reports creation. Sign up for a demo and we'll show you how it works.
How to create a Google Sheets report based on Google Analytics data
To create a Google Sheets report with Google Analytics data, first install the Google Analytics Sheets Add-on in your Chrome browser. This add-on allows you to import data from Google Analytics into Google Sheets based on the Core Reporting API and then work with that data – tabulate it, visualize it, etc.
After installing the add-on, open Google Sheets. In the Add-ons menu, click on Google Analytics and select Create new report:
Come up with a name for the report and choose the account, resource, and Google Analytics representation from which you want to receive data. Next, select the parameters and indicators you want to analyze. If necessary, set up segments. Then click the Create Report button:
After that, the Report Configuration sheet with report settings will appear in your table. In one document, you can create several different reports that will be available in separate sheets.
After creating a report, you can run it to check that everything works correctly. To do this, go to the Add-ons menu, select Google Analytics, and click Run reports:
As a result, you’ll see a separate sheet with the selected report based on data pulled from Google Analytics:
Then you can build pivot tables, graphs, and charts with this data.
How to set up automatic report updates in Google Sheets
So you don’t have to constantly go into this table and manually update the data, you can automate the launching of a report. To do this, go to Add-ons –> Google Analytics –> Schedule reports:
Here, you can choose the frequency with which the information in the report will be updated: every hour, day, week, or month:
How to send reports by email using Google Apps Script
Google Apps Script is a programming language that allows you to add features and process data in Sheets and other Google services. With Apps Script, you can automatically send reports by email. You can send emails either on a schedule or when there are critical changes in your metrics.
Here is a script template that you can use to send emails:
// Send an email with two attachments: a file from Google Drive (as a PDF) and an HTML file.
var file = DriveApp.getFileById(' 1234567890abcdefghijklmnopqrstuvwxyz');
var blob = Utilities.newBlob('Insert any HTML content here', 'text/html', 'my_document.html');
MailApp.sendEmail('mike@example.com', 'Attachment example', 'Two files are attached.', {
name: 'Automatic Emailer Script',
attachments: [file.getAs(MimeType.PDF), blob]
});In your script, you need to specify the rows of the table that you want to check. You also need to specify the conditions under which the report will be sent as well as the recipient’s email address and the body text for the email. You can learn more about how to do this in the developer’s guide. This is a ready-made script that sends an email if the hit limit is exceeded:
After you’ve prepared a script that determines when email will be sent, go to the Extensions menu and select Apps Script:
Paste the code in the window that opens and click on the clock icon to run the script. Then click the +Add trigger button and set the frequency with which the script will send you emails:
Done! Now you will receive emails containing ready-made Google Sheets reports with relevant data.
P.S. Since you’ve decided to optimize your work with reports, why not set up automatic importing of advertising expenses into Google Analytics using OWOX BI so you don’t have to do it manually each time? Moreover, OWOX BI has a free trial period, after which you can still import data from one advertising service for free.
FAQ
-
Can I automate the process of importing Google Analytics data into Google Sheets?
Yes, you can use the Google Analytics add-on for Sheets to schedule automatic imports of your data at regular intervals. You can also use Google Apps Script to create custom functions and automate data manipulation and reporting tasks. -
What types of Google Analytics reports can I create in Google Sheets?
There are many types of reports you can create using Google Analytics data in Google Sheets, including customizable dashboards, detailed traffic and conversion reports, and advanced segmentation reports. -
What is the benefit of using Google Analytics reports in Google Sheets?
Using Google Analytics reports in Google Sheets can provide a more flexible and customizable way to analyze and visualize your data. It can also allow for easy collaboration and automation of reporting.
