I’m working my way through this book, Data Analysis Using SQL and Excel, at the moment and chapter 6 is all about survival and retention modelling. I learn best when I can attack real life problems, so I took some of the lessons from this chapter and applied them to the MailChimp email data I already had in a MySQL database.
Previously I looked at setting up a database and doing some basic MySQL analysis on MailChimp data, as well as some more in-depth analysis such as creating a histogram of email subscribers.
This post takes it a step further by looking at subscriber behaviour within the email campaign data. What can we say about how long people remain active subscribers? For a subscriber who has been active for a given length of time, how likely are they to continue being active?
Continue reading Survival and retention analysis using MySQL
This post digs deeper into the MailChimp data which I analyzed in my previous post. In this analysis, I’ve focused on the distribution of subscribers and what was the most recent date they opened an email.
Specifically, I wanted to answer:
- What is the distribution of subscribers by campaign date that opened an email in 2014?
- And what are their email addresses?
The way I approached this problem was to break it down into its constituent parts, tackle each of those and then build that back up into a single query.
Continue reading Creating a histogram from MailChimp data using MySQL
This post shows how I analyzed a MailChimp email list, including all the data from weekly newsletters for 2014, using MySQL. It was inspired by this excellent tutorial: Performing cohort analysis by Micheal Herman. The email list I’m working with consists of about 18,000 subscribers.
I wanted to answer questions such as:
- How many emails do active subscribers open on average?
- How active are the subset of users who bought a product during the digital flash sale in March of this year?
- Who hasn’t opened an email at all in 2014?
Continue reading Deep dive into Mailchimp email data using MySQL