Advanced Techniques: the Monthly PDF Report

[updated 2016-01-11] This article describes some of the more advanced techniques used to create the free 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 the new features of both the Analytics Core Add-in v2.20 and the Analytics Edge Connector for Google Analytics v2.3.

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.

monthlypdf-websites

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.

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 allows you to force those macros to run as part of the repeat macro, and only after the values at the top of the Setup worksheet have been changed.

save-email-pdf-fileAfter the report macros have been run, the report is ready to be saved. 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.

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).

Report Macros

The 3 macros for the pages in the report start by reading the range of cells on the Setup worksheet 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.

The macros write results to both the report worksheet as well as a hidden data worksheet used to feed the charts on the report.

report-worksheetMost 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.

Summary

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.

Recommended Modifications

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.