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?
The idea originated from discussions with my wife about the tedium of preparing month-end website performance reports for her content management clients. This dashboard was a solution to streamline the monthly web reporting and offer insights that might otherwise be missed. Rather than having to visit Google Analytics for each website in turn and click around gathering the necessary data, this pulls it all together in one place.
Earlier this year I worked with The Write Life team to develop some behind-the-scenes sales data analysis during their 3-day digital bundle sale. The team wanted a simple way of gauging progress and seeing how the different sales channels fared as the sale took place.