Building a dynamic dashboard for a 3-day digital flash sale

This time last year I worked with The Write Life during their three-day sale of The Writer’s Bundle, a collection of digital products for writers. I built a dynamic dashboard using Google spreadsheets so the team could visually monitor progress throughout the sale.

This year The Writer’s Bundle 2015 was bigger and better than ever, so I wanted to create an even more useful dashboard for the team. The dashboard was a key tool for the team, to monitor both overall and individual sales channel performance, as well as to be a motivating force by giving everyone a visual sense of progress.

Continue reading Building a dynamic dashboard for a 3-day digital flash sale

Redesigning the Socialexis Site Using the Corsa Theme for WordPress

Recently, I’ve been busy redesigning the website for Socialexis, my wife’s fantastic content marketing firm. I used the Corsa theme from UpSolution. It’s a modern, single-page parallax theme, which is perfect for the Socialexis brand.

Here’s a screenshot of the new website:

New Socialexis website

The screenshot below shows the old website. Whilst there was nothing wrong with it per se, it wasn’t particularly exciting and didn’t represent the creativity and passion at the core of Lexi’s business. Since this original website was built, the business has also focused more on blog management and content creation, so the message needed tweaking. Hence, it was a good opportunity to also redesign the website.

Continue reading Redesigning the Socialexis Site Using the Corsa Theme for WordPress

Survival and retention analysis using MySQL

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

Creating a histogram from MailChimp data 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

Deep dive into Mailchimp email 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

Creating a custom Google Analytics report in a Google spreadsheet

Continuing my recent infatuation with dashboards in Google spreadsheets, I’ve been playing around with the Google Analytics Add-on to build a custom web analytics dashboard in a Google spreadsheet.

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.

Dashboard in action
Dashboard in action

Continue reading Creating a custom Google Analytics report in a Google spreadsheet

Creating a dynamic dashboard in Google Spreadsheets to track sales for a digital product launch

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.

The final dashboard I designed looked and worked like this (view the Google Sheet), and below I’ll show you how I created it:

Dashboard in action
Interactive sales dashboard in a Google spreadsheet

Continue reading Creating a dynamic dashboard in Google Spreadsheets to track sales for a digital product launch