Let's face it, there are a lot of really cool data science projects out there that look super cool, but don't have much business value. I saw a LinkedIn post one time about project managers at a company like Salesforce who dealt with business programs, made nearly double as a project manager at a company that produces video games. The point was while something like video game design often looks really, really good and passionate people create it, there's not the same demand and thus reward for a 'unsexy' position or company that solves real business problems.
Recently I had the opportunity to participate in one of these real business projects that people will pay money to solve. As part of the data science community I am in, we partnered with The Column, a chemical engineering newsletter, to see if we can help grow their business and model. You can check out and subscribe to The Column here.
There were over 70 people that participated in this Hackathon and many of them formed teams to come up with something that founder of The Column would find useful. I was pleasantly surprised when I found I was awarded third place!!
Like many newsletters, the business objective of The Column is to maximize traffic to the site to make it more appealing for other company's to pay for advertising space on the site. The Column's founder had manually gathered data on each email he sent out to his subscriber base since the company had started a few years ago. He kept that raw data in an Excel spreadsheet that looked like this:
This is only just a preview as there are several more rows that cover up until this point October 2021. Several companies have lots of raw data lying around often being stored in an excel spreadsheet similar to this one. Yet, all it is at this point is a bunch of numbers on a page. What are the data driven insights that can actually help The Column achieve its goals?
One of the first things I did was find out how The Column's open rate had performed over time. We all have emails that we receive from things we subscribed sometime ago that we quickly delete without opening because there is a lack of interest. Thus open rate is a very crucial statistic as if people aren't even opening the email, company's won't waste money in paid advertising without traffic.
As you can see, the open rate stabilizes at around 50% as the number of emails sent to subscribers continually increases over time. There appeared as well to be some bad data when the founder first started collected as you can have an open rate of over 100%. Still, a 50% open rate is remarkable for this industry as 15-20% is considered good, and one of the industry's leaders The Morning Brew, who generates hundreds of thousands of paid advertising dollars, averages around 45%.
The next step I looked at was click rate. This is also a telling data point because it shows how well those who are opening the emails are actually engaged.
The Column's click rate appears to stick around the summer of 2021, reaching over 60% in May before returning to around 20%. Even with the drop, this is still a promising trend as the Morning Brew's click rate is only about 10%
While these open and click rates normalize the respective data well, I wanted to also see if I could demonstrate the scale of these numbers on a cumulative scale. The way I determined to do that was through a tree map where each rectangle represented a month.
I was able to make this tree map in Tableau where the rectangle length represented total clicks, the rectangle width as total opens and the rectangle color as average sends. This gave a grander picture and some interesting insights as well. May 2021 had 7,699 clicks but only 12,719 opens while June 2021 had only 5,577 clicks but 16,089 opens.
Finally, I wanted to determine if day of the week had any affect on open and click rate. The Column sends an email to subscribers every Monday, Wednesday and Friday to I created a strip plot and ran statistical ANOVA tests in Python to see if there was a significant difference in the average click and open rate depending on the day.
For open rate I found that they are significantly higher(p value = 0.001) on Mondays compared to Fridays.
For click rates, while Mondays are not quite significantly better than Friday(p value=0.8) it is significantly higher than Wednesdays(p value=0.3).
When I presented these findings to The Column's founder were to not miss a MONDAY SEND! Since the start of 2021 with the switch to three days a week model, Mondays are the best day for opens and clicks. Also I found that SUMMER months tend to have a more opens and clicks as well as fewer unsubscribes compared to the other months.
As time goes on and The Column implements this and other suggestions by other participants in the Hackathon, they will need a visualization to track their progress. I made a dashboard to accomplish this that shows the tree map mentioned previously of opens and clicks, a line graph of emails sent to subscribers and scorecards that show the previous month's open rate, click rate and unsubscribes. That way the founder can see how well The Column is doing visually.
As mentioned at the start, I was awarded 3rd place for my submission to The Column. The founder liked how I was able to provide real business value in an easy way for to digest and implement. I learned a lot in completing this business analysis case but my biggest takeaway is that it doesn't matter how complex your code is, what matters is that you determine simple insights that are easy to implement and that you present them well.
Check out my submission here and let me know of your thoughts!
Comments