This article explains how to build a cohort analysis report in Excel using the Analytics Edge Core Add-in with the Constant Contact connector. Cohort Analysis lets you see how one group of subscribers behaves differently from another. A “cohort” is simply a group of people that share a common characteristic, such as the date they subscribed to your list, whether they clicked on a specific campaign, or those people that selected a particular interest in your signup form.
The first step is to assign each of your subscribers to a cohort — to create a look-up list of their email address and the cohort they belong to. One of the easiest cohorts to obtain is based on the date they were added to your list. If you download your Contacts list, the created_date is one of the columns. Use the Arrange wizard to keep the email address and date column — those are all we need.
To create a monthly cohort, use the Analytics Edge Convert wizard to change the created_date into a text string representing the year and month (format codes “yyyy-MM”). Save the results to a worksheet for later reference.
Collect Your Tracking Data
The next step is to get a list of all the emails that opened each of your campaigns over a period of time. Analytics Edge can automate this process. It starts with the Constant Contact connector downloading all the sent campaigns for the time period of interest.
Then, using a Repeat Macro, I get all of the Opens for each campaign and append them to a worksheet. Note that detailed open data for each campaign may include multiple opens by individual subscribers, so I use the Duplicates wizard to get rid of those extra ones. I am only interested if they opened the campaign, not how many times they opened it.
Combine the Worksheets
The Opens data gives us campaign ids and emails, and I can use the Combine wizard to look up the cohort for each email from the Contacts worksheet.
Note that I used the Arrange wizard to rename the email address columns to match so that the Combine wizard could link the two on a common column name.
I now have a list of cohorts that opened each of my campaigns. Using the Duplicates wizard, I can collapse it all and get a Count column. While that is a nice summary, no one recognizes a campaign by its id number, so I use the Combine wizard again to get the campaign name and send (modified) date from the Campaigns worksheet.
Prepare for Presentation
The last step is to perform a Pivot operation, producing a table showing the number of opens for each cohort by campaign. It is best to Sort the data before the pivot operation so the rows and columns will appear in the desired order.
In my report, I wanted to show the date of the newsletters instead of their name or subject line. Here it what it looks like.
What Can You Do With A Cohort Analysis?
The final Cohort Analysis report is useful in a few ways: first, it lets you quickly see whether you have specific groups of subscribers (cohorts) that are more engaged with your emails. They are the ones that hold the number of readers over time. How did you attract those subscribers, and can you repeat the process?
Second, you could learn from the variation between campaigns. A great campaign will have better opens across all the cohorts, but you already knew it was a great campaign from the summary reports.
Finally, if you see one cohort more responsive to a particular campaign, there is an opportunity to learn what makes that cohort different from the rest, and can you leverage that knowledge in some way.
This is segmentation — the Holy Grail of email marketing. Cohort analysis makes it possible.
Everyone has slightly different reporting needs, and Analytics Edge is designed to adapt easily. If you need some guidance for your own analysis, just ask. Expert advice is free.