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:

/* Send Spreadsheet in an email as PDF, automatically */ function emailSpreadsheetAsPDFAndXLSX() { // Define a variable for the email address. var email = ""; // Get the currently active spreadsheet URL (link) // Or use SpreadsheetApp.openByUrl("<<SPREADSHEET URL>>"); var ss = SpreadsheetApp.getActiveSpreadsheet(); var ssID = ss.getId(); // Subject of email message var subject = „Replace this text with your own subject line: Monthly Goal 1 Conversion Reporting"; // Email Body can be HTML too with your logo image - see ctrlq.org/html-mail var body = „Replace this text with your body text: Here is your monthly report regarding Goal 1 Completions.“; // Base URL var url = "https://docs.google.com/spreadsheets/d/SS_ID/export?".replace("SS_ID", ssID); /* Specify PDF export parameters From: https://code.google.com/p/google-apps-script-issues/issues/detail?id=3579 */ var url_ext = 'exportFormat=pdf&format=pdf' // export as pdf + '&size=A4' // paper size A4 + '&portrait=true' // orientation portrait + '&fitw=false&source=labnol' // use actual size + '&sheetnames=false&printtitle=false' // hide headers and footers + '&pagenumbers=false&gridlines=false' // hide page numbers and gridlines + '&fzr=false' // do not repeat row headers (frozen rows) on each page + '&gid='; // the sheet's Id var token = ScriptApp.getOAuthToken(); var bogus = DriveApp.getRootFolder(); // get the sheet with the report var report = ss.getSheetByName(„Replace with your sheet name: Goal-1-Completions-DB"); // Convert individual worksheets to PDF var response_pdf = UrlFetchApp.fetch(url + url_ext + report.getSheetId(), { headers: { 'Authorization': 'Bearer ' + token } }); //convert the response to a blob and store it var blobs_pdf = response_pdf.getBlob().setName(report.getName() + '.pdf'); // Convert XLS var response_xls = UrlFetchApp.fetch(url + 'download/spreadsheets/Export?key='+ ssID +'&exportFormat=xlsx', { headers: { Authorization: 'Bearer ' + token } }); //convert the response to a blob and store it var blobs_xls = response_xls.getBlob().setName(ss.getName() + '.xlsx'); // Get the email list from the sheet email = report.getRange("A3:A3").getValues(); // If allowed to send emails, send the email with the PDF attachment if (MailApp.getRemainingDailyQuota() > 0) GmailApp.sendEmail(email, subject, body, { htmlBody: body, attachments:[blobs_pdf, blobs_xls] }); }

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:

// If allowed to send emails, send the email with the PDF attachment if (MailApp.getRemainingDailyQuota() > 0) MailApp.sendEmail(email, subject, body, { htmlBody: body, attachments:[blobs_pdf, blobs_xls] });

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:

  1. Install the Google Analytics Add-on for Google Sheets
  2. Collect & save analytics data automatically in Google Sheets
  3. Filter your data
  4. Create your custom report dashboard
  5. Implement a Google script to email Google Sheets and PDF
  6. Sent the reports automatically and on a recurring schedule

Share on Facebook | Share on Twitter