SQL Analysis & Excel Dashboard: 'Maven Fuzzy Factory' Sales 2012- 2015

Business need:

To help the management at 'Maven Fuzzy Factory' understand the growth of their business over its lifetime.

My goal:

To analyze the sales and website traffic data of 'Maven Fuzzy Factory' using SQL and to present my insights and recommendations to the management in an Excel dashboard.

SQL functions used:

JOINS, AGGREGATE FUNCTIONS, DATE FUNCTIONS & CASE FUNCTION.

Insights:

1. Website session and order volumes have continued to grow steadily with peaks during the holiday season (Nov- Dec). Session to order conversion rates have also increased steadily, with a 60% increase between December 2012 and December 2014.

2. Although ‘The Original Mr. Fuzzy’ had a 5x higher order volume among the two products that have been available for purchase for the whole two years (2013 & 2014), both ‘The Original Mr. Fuzzy’ and ‘The Forever Love Bear’ have had a phenomenal YOY growth rate of 94% and 109%.

‘The Birthday Sugar Panda’ has had an order volume comparable to the ‘The Forever Love Bear’ in the year 2014 after its introduction in December 2013.

‘The Hudson River Mini Bear’ however, is our weakest product, with only 151 units sold after its introduction in February 2014.

3. Among the paid sources of traffic to our website, ‘gsearch’ continues to drive the highest volume and has consistently driven 400% more traffic than ‘bsearch’ to the website since January 2013.

While both ‘gsearch’ and ‘bsearch’ drive higher traffic during the holiday season, ‘socialbook’ has driven 46.5% more traffic during the Valentine season than the Holiday season.

4. The refund rate has dropped sharply and remained steady around 4% following the replacement of our suppliers after fluctuating over the years due to quality issues and peaking in August- September 2014.

Recommendations:

1. Perform root cause analysis of why ‘The Hudson River Mini Bear’ has had weak sales.
2. Bid up on ‘gsearch’ and bid down on the ‘bsearch’ paid traffic source to the website. Increase bids on ‘socialbook’ during the Valentine season.
3. Brainstorm other ideas to increase traffic and orders during the Valentine season.
4. Continue to monitor refund rates to ensure changing suppliers has fully resolved quality issues.

The process:

Database normalization before conducting data analysis:

Check out how I explored and normalized the 'Maven Fuzzy Factory' database here.

SQL code for data analysis:

Note:

Since we don't have data for the full months of March 2012 and 2015, they have been excluded from all visualizations.