With the MailChimp Connector, Analytics Edge lets you automate more than just a data download – it lets you create a finished report that you can refresh with just a click. In this article, we’ll show how one click can get you a subscriber activity report for the most recent MailChimp campaign. 
Build your own report using the instructions below, or use the attached example and follow along (MailChimpCampaignActivityReport.xlsx). This requires an installed copy of the Analytics Edge add-in as well as the Connector for MailChimp, both are available free for 30 days from the download page.
Overview
To build the report, we get the most recent campaign Id and use the MailChimp Export API campaignSubscriberActivity function to download the data we need. Then a series of Analytics Edge functions are used to clean up and pivot the data into a report-ready table.
Although there are a number of steps to get this done, the simple function wizards make it quick and easy, and the automatically recorded macro can be run at any time to refresh with the latest data.
MailChimp – Campaign – List
We get the most recent campaign id with this wizard, selecting the optional parameter status with the value ‘sent’. Since the default sorting is by descending created date, the most recent campaign is at the top of the list, in a column named ‘id’.
Table – Table Name
Analytics Edge lets you assign range names to the columns of a table, and the names can be used later in the macros instead of specific values. We will do this with the campaign ‘id’ column.
MailChimp – Other – Export API
Now we make the main query, getting the campaignSubscriberActivity results. Note that we enter the range name in square brackets ‘[id]’ to get the value from the named range. Since this command expects a single value, only the first row is used.
Like most downloaded data, this tabular data is not very useful as is. The dates are actually text strings and include the time component. There is also a lot of duplication, with individual emails recording opens and clicks multiple times over several days.
Our preferred report would show the actions (opens, clicks, etc) daily over the past month, but eliminate the duplicates, reporting only the first time an individual took a specific action.
Column – Convert
With Analytics Edge showing the data we are working with, we see that the timestamp date column contains text, looking like ‘2013-09-17 21:38:33’. Using the Convert wizard, we enter matching format codes to convert it to an Excel date column.
This will allow the pivot function to aggregate by date.
Table – Duplicates
To get rid of the duplication, we select the email, action and url columns; this identifies unique actions taken by individuals, ignoring the timestamp and ip columns.
Where there are duplicates, we select the options so that Analytics Edge will keep the first value to occur.
Table – Pivot
The next step is to pivot the data, putting the days across the columns, the actions down the rows, and a count of the email addresses in the cells.
Simply pick the appropriate options and click Next.
Pivot By Date Options
Since the columns are dates, you get to choose additional options. In this report, we want to report the most recent month (rolling periods, 31 days so there are always 31 data point in the chart). It is a good idea to also check the box to fill in any missing dates, in case you had a quiet day with no actions recorded.
File – WriteToWorksheet
As the final step, write the results to a worksheet for reporting.
The data is perfectly formatted for a simple Excel chart. We are done in minutes, never had to struggle with an Excel formula and can easily refresh the data by running the macro. It just doesn’t get any easier than this.
That’s why we say Analytics Edge delivers simple Excel automation.