This article describes some of the more advanced techniques used to create the Excel workbook Google Analytics Monthly PDF Report. The workbook can automatically send PDF reports for multiple websites as email attachments, and it uses a number of automation features of the Analytics Edge Core Add-in.
The workbook has four Analytics Edge macros in it; one master repeating macro, and one macro for each of the three pages in the report.
The Repeat Macro
The magic in this report is the Repeat Macro. This macro reads a table from the Setup worksheet and repeats the rest of the macro for each row in the table. Each row describes a website to be processed — the Login and View Id number (for the Analytics Edge queries), plus a descriptive name for the site and an email address to send the report to.
As it processes each row, it writes the values from that row to a different location in the Setup worksheet where the other macros can find them. Then the repeat macro uses the Run Macro Function to call each of the 3 report macros in turn. Each report macro reads the values for that specific row and uses them to generate a report page.
The macros were written in this way to make it easier to make changes to individual report pages, or to add or remove individual pages at any time with minimal effort.
Those 3 macros were given names that start with an underscore (e.g. “_Refresh Audience”) so they will not be triggered by a Refresh All operation. The Run Macro Function that is in the Repeat Macro allows it to force those macros to run as part of the repeat macro, and only after the values at the top of the Setup worksheet (in red) have been changed.
After the report macros have been run, the report is ready to be saved and sent as an email attachment. A Save/Email PDF File function is used to save the workbook as a PDF file and send it via email to its intended recipients.
Note: the function in the macro has selected the SMTP email option — change it to a Gmail account if you prefer. Email accounts are configured in the Options wizard.
Two columns in the Repeat Macro table are used to customize the file name and email message. The [Name] range is used in the file name and message to give meaning to which website the report covers, and the [EmailTo] range is used to set the email address for the recipient(s). Read more about Range Names.
The 3 macros for the pages in the report start by reading the range of cells on the Setup worksheet (in red) to get the Google Analytics login account and website view id needed for the queries. The information is passed this way because macros cannot pass parameters between them – they all run independently. It also makes it easy to develop and test independent macros for each report page.
The macros write results to both the report worksheet as well as a hidden data worksheet used to feed the charts on that specific report.
Most of the reports are a simple display of the numbers coming from Google Analytics. The key metric section includes a percentage comparison to the previous period, and this is done by downloading data for both periods and using the Compare function, with a growth calculation.
The time metrics (avg session duration, time on page) are output in Days using an option in the connector. This is done so the resulting number can be formatted in Excel using the mm:ss time format, displaying minutes:seconds.
One feature of the workbook is the ability to change the report worksheet names to suit the client; maybe a difference in language, or simply a local spelling variant (like Behaviour vs Behavior). This is accomplished by putting an Excel formula on the hidden data worksheet that references a cell on the report sheet and returns the name of that sheet:
=MID(CELL("filename", Audience!A1),FIND("]",CELL("filename", Audience!A1))+1,32)
The macro reads this cell and assigns it to an Analytics Edge range name so it can be used in the WriteToWorksheet functions. If you change the worksheet name, the macro automatically detects the new name. Note: do NOT change the name of the hidden data worksheets.
These new features combine to make it possible to send a series of emailed reports from a single, automated macro. Using the Analytics Edge Scheduled Refresh, it can run on your computer unattended, reducing hours of report updates to literally nothing.
Two changes recommended for production use:
1. Move the Setup worksheet to a separate workbook. The Refresh Macro can reference the other workbook, and should you decide to distribute the report workbook or forget to hide the Setup worksheet, you will not send out the names and email addresses of other clients.
2. Some email systems will automatically keep a copy of sent messages, but if yours doesn’t, in the Save/Email PDF File wizard, check the box to Bcc me. It is always a good idea to have a copy of what is sent to a client.