Several Constant Contact Solution Providers use the Analytics Edge Core Add-in for Microsoft Excel to automate their clients’ campaign reports. With the latest release of the add-in, they are taking the leap to fully automated custom reporting. You can, too! This article describes the updated (and completely free) Branded Agency Report workbook that can be used as a template for your own reporting solution.
The Analytics Edge Core Add-in runs on a Windows PC inside Microsoft Excel, and it comes with a number of optional connectors, including one for Constant Contact.
The add-in uses a simple wizard interface to automate the tedious download and transformation steps needed to update or refresh a report workbook with results from the latest email campaign.
New functionality in the Core Add-in and Constant Contact Connector allows you to process multiple campaigns, save the results to separate workbooks or PDF files, send them as email attachments, and even schedule the whole thing to run overnight!
Download the workbook here.
How It’s Done
The report lets you enter a number of Constant Contact accounts in a worksheet. When it runs, it will process all email campaigns sent in the past few days and send the results to a specified email address. You enter the number of days and email address to use for each account.
There are two Analytics Edge automation ‘macros’: one to get the account information needed, and the second to process each campaign for that account.
Account Information
The main macro loads the list of account names, email addresses and number of days from a setup worksheet. In a production deployment, you would move this setup worksheet to a separate workbook so there is no chance the information in it (a list of your clients) would end up as part of a report. The first wizard function in the macro is the only reference to this worksheet, so it can easily be changed.
That first function wizard causes the rest of the macro to repeat for each row in the list of accounts. In the very next step, it writes out the row to a hidden worksheet. This is how the main macro passes information to the other macro.
The rest of the macro gets the general account information, including the full name of the account for display purposes, a list of the email campaigns sent in the past number of days, and all the contact email addresses with the contact list they are associated with. All this information is written to hidden worksheets for use by the second macro.
The very last step runs the second macro. Because the Run Macro function is part of the first macro, and because the first macro repeats for each account, the second macro will read different account information from the hidden worksheets each time it runs.
Processing Email Campaign Results
On one of the hidden worksheets, the first macro had populated the login account to use, the email address to send the report to, and the number of days in the reporting period. It also grabbed the full account (organization) name and the list of campaigns that were sent in the reporting period.
We start the second macro very much like the first; setting it up to repeat for each campaign in the list. The rest of the macro is adapted from the previous branded agency report, which was designed to report on a single campaign.
The last step saves a copy of the workbook and sends it as an email attachment to the address provided. When configuring the wizard for this step, we use Analytics Edge ‘range names’ (similar to Excel range names) so that the file name, message and email address will all use components of the account’s organization name, the name/subject line of the specific email campaign being reported, and the date it was sent.
The result is that each email campaign report is saved to a unique file and attached to an email — all automatically!
Customizing for Production Use
All Analytics Edge free reports are completely open and easily customized to your unique reporting needs. This particular workbook uses a placeholder logo image that is easily replaced.
As mentioned above, it is strongly recommended that you move the _setup worksheet to a separate workbook to avoid sending your customers’ email addresses as part of your report workbook.
If you are looking for different metrics in the reports, the wide array of Analytics Edge wizard functions make it easy to manipulate and transform data from Constant Contact as well as a number of other sources. Just ask for help.