E-Commerce Website and Product Performance - MySQL, Data Modeling, Excel

THE SITUATION

The project brief called for a high-level KPI summary as well as a database redesign. The KPIs requested were:

• trended website traffic analysis
• trended overall session to order volumes and conversion rates
• the overall refund rate, trended
• year-over-year product performance

What's a plausible scenario for this analysis? I decided to envision myself as an analyst assisting the marketing director to prepare slides for a semiannual all-hands town hall emceed by the company CEO. In that spirit, the insights below are presented as her scripted remarks for the meeting, in a non-technical, pleasantly breezy style.

[Certain story details are inspired by Maven Analytics's Advanced MySQL data analysis course.]

INSIGHTS

Welcome, everyone. We've had a terrific 2014 at Maven Fuzzy Factory any way you look at it. We want to share some success stories.

First, we’ve redesigned our company database to improve our recordkeeping. The changes close some potentially troublesome structural loopholes. They guarantee, for example, that we won't enter an order for a product that we don’t sell or issue a refund for a non-existent order. We didn’t find evidence that that ever happened, but it’s important for us to secure our data, especially as we scale up our business.

Speaking of scaling up—let's start with our website. You all know our best months are November and December because our customers love giving Fuzzy Factory bears as holiday gifts.

• Comparing our 2014 to 2013 holiday surges, we did double the business and welcomed 2x the visitors to our website between last year and the year before.
• A larger share of our customers in 2014 came to us on their own (we call that direct traffic) rather than relying on the two big search engines or Socialbook. That's really good because it means our customers know exactly who we are and are asking for us by name.
• Finally, our targeted website improvements have resulted in more of our visitors buying bears. Between our first holiday season in 2012 and last year our visit/order conversion rate also nearly doubled.

The Original Mr. Fuzzy is still our biggest seller, but our two newer products, The Forever Love Bear and The Birthday Sugar Panda, showed promising growth over 2014.

We’ve also been hard at work improving our product quality. We traced the problem about Mr. Fuzzy’s arms falling off from last fall to a subpar supplier. Replacing that supplier has brought our overall refund rate down to only 3.3%. Our product quality and customer satisfaction showed major improvement the last few months.

By every measure business is booming. We celebrate each of you and the great work you’re doing for the cuddliest, fuzziest teddy bears ever.