Google Sheets and the Google Analytics Add-on are powerful tools to automate the process of custom reporting in Google Analytics and to even convert your dashboards into PDFs and sent them to a defined list of recipients.
About
For monthly performance reports, it makes sense to collect all data from Google Analytics separately in a Google sheet rather than manually gathering all the data by hand to create tables, pie graphs and diagrams from. For those who use Google Analytics there are powerful tools like Google Sheets and the Google Analytics Add-on to automate this process. This tutorial not just helps you to automate the data-gathering monkey work but also shows you how to convert a dashboard into a PDF and sent it via email to a defined list of recipients on a regular basis. Before you start with this tutorial, we recommend you to read our previous post on „Professionally Setting Up Google Analytics in Three Steps“.
Before starting something new you need the free Google Analytics Add-on.
1. Install the Google Analytics Add-on: Before starting something new you need the free Google Analytics Add-on. Therefore, you have to log into Google Drive with the account you use for Google Analytics. Head on to the Add-ons tab, click on »Get add-ons« and search for »Google Analytics« in the add-ons overlay. Allow the usual permissions and the add-on will be installed. Done. You can now access the Google Analytics Add-on from the »Add-ons« label in the menu.
2. Collect & save Google Analytics data automatically in Google Sheets:
Okay, let’s get started. Within the Google Analytics Add-on click »Create new report« to open
the configuration panel of the add-on. You see different parameters to automate your report with. Simply
name your report, choose your Analytics profile, and set the metrics and dimensions you want to monitor.
In our example we want to report on metrics such as »ga:sessions«
, »ga:pageViewsPerSession«,
»ga:bounceRate«
, »ga:goal1Completions«
, »ga:goal1ConversionRate«
and
»ga:goal1AbandonRate«
.
For dimensions choose »ga:dateHourMinute«
, »ga:pageTitle«
,
»ga:sourceMedium«
, »ga:country«
and »ga:referralPath«
.
After clicking »Create Report« the add-on creates a Google spreadsheets with the name »Report Configuration«
containing all our selected parameters.
From here you can first start a test run for creating a report and to check against your
parameter settings. Just go to »Add-ons« > »Google Analytics« > »Run reports«.
After a few seconds you receive a new sheet showing your overall metrics from the last seven days. You can change
your settings from here if needed. Just click the »Report Configuration« tab at the bottom and edit you
settings on the fly. Within the configuration options you can change the reporting period from 7 to 31 days,
re-arrange your metrics and dimensions as well as your sorting options. If you want to sort your data by dateHourMinute
you have to set the expression »ga:dateHourMinute«
. To reverse your sorting just put a minus in front of
your expression »-ga:dateHourMinute«
or »-ga:users«
.
3. Filter your data: Using filters helps you to filter your data requests to your personal needs, i.e. the expression
»ga:users > 5«
only outputs the data if the user number is higher than 5. Segments help you to segment
your data. The expression »sessions::condition::ga:country==India«
for example only displays sessions from
India. For our report we leave »Filters«, »Segments«, »Sampling Level« and
»Start Index« empty and set our »Max Results« to 1,500. After changing your configuration options
run »Add-ons« > »Google Analytics« > »Run reports« again to overwrite our test
report with the new settings.
To keep your report data up-to-date, it must be regularly updated.
Finally, to keep your report data up-to-date, it must be regularly updated. This can be automated with the Google Analytics add-on as well. In the »Add-ons« menu look after the »Schedule reports« command and click on it. In the upcoming overlay check the box for »Enable reports to run automatically« and set your preferred options your report should run from automatically, i.e. every month.
4. Create a custom report dashboard: After creating your goal completions report you can now start creating your goal completions
dashboard. Dashboards are used to collect all important metrics and, if necessary, display them in graphics.
Therefore, create a new spreadsheet and insert your selected data from the goal completions report, i.e.
»Sessions (Sum)«
and »Goal1: Contact Form Submissions (Sum)«
in the
top empty rows. After this, select the entire table (including the header) and click »Insert chart …«
in the editing options. The chart editor opens, in which you can change the graph for your needs. When you are done
just click »Insert« and the graph is now displayed in the sheet. Just place it where it fits best next to
the figures and numbers and take care to remain within the printable area of your document. If you don’t want to show the
data table in your document just right click and select the »Hide rows [m]–[n]« option from the
context menu. Additionally, you can report the other metrics below your chart, i.e. »Sessions«, »Pages per Session«,
»Bounce Rate« and specific goal KPIs like »Goal 1 Completion«, »Goal 1 Completion
Conversion Rate« and »Goal 1 Completion Exit Rate«.
With our custom dashboard done let's convert it to a PDF and XLS file.
5. Implement a Google script to email Google Sheets and PDF: With our custom dashboard done let's convert it to a PDF and XLS file and email the spreadsheet and PDF to a designated email address. The Google script function will convert the dashboard of a Google spreadsheet into a PDF and XLS file and send it to a designated email address. You can send to multiple email addresses too – just separate each of them with a comma.
First, we write the recipient email address in an empty field of the dashboard and also hide this row. To copy and paste the following Google script go to »Tools > Script editor…« and paste the following code:
The above code snippet converts the spreadsheet dashboard into separate PDF and XLS files.
Now change the code lines for the subject, the email body and the name of the sheet in which we have just
created the dashboard. After this, run your code while clicking the »Run Code« play button in the script
editor. If everything went well, the snippet runs without exceptions and an email with the dashboard as a
converted PDF and the entire XLS file are sent to the specified email address. If you experience problems with
your Gmail user rights or Gmail settings consider to replace the sendEmail
-function of GmailApp
to MailApp
at the end of the snippet:
Other code snippets are available online such as from Digital Inspiration or from BitWiser.
6. Sent the reports automatically and on a recurring schedule: Last but not least, we don’t just want the dashboard to update itself automatically but also that the email is sent automatically and on a recurring schedule. Therefore, just go to the »Resources« tab in the menu and click on »Current project’s triggers« to set a new trigger with your preferred intervals such as daily or weekly etc. With your automated basic custom reports done you can now focus on monitoring your core online marketing KPIs within your overall measurement plan.
Summary:
- Install the Google Analytics Add-on for Google Sheets
- Collect & save analytics data automatically in Google Sheets
- Filter your data
- Create your custom report dashboard
- Implement a Google script to email Google Sheets and PDF
- Sent the reports automatically and on a recurring schedule