Maven Fuzzy Factory - Example of SQL & Excel Analysis

Project Brief:

Maven Fuzzy Factory (MFF) is an e-commerce company that sells stuffed animals. The former analyst has just left the company and I was assigned as the new owner and analyst of the MFF database. My goals were to use SQL (1) to get up to speed with the contents of the database with basic exploratory analysis (2) to leverage EER diagramming and implement some improvements to their data structure, and (3) to help them understand their business better, (4) present my initial findings using Excel.

Analysis steps:

(1) Database exploration to examine table contents:

Selected all of the data from each table to view the contents and data fields.

select * from website_sessions ;

Contains the website session id, the date and time of each session, if the session was a repeat session, the utm source, the utm campaign, the utm content, the device type, and the http referrer page.

select * from website_pageviews;

Contains the website page view id, the date and time of each page view, the respective website session id, and the page url.

select * from products;

Contains the product id, the date and time the product launched, and the product name.

select * from orders;

Contains the order id, the date and time of the order, the respective website session, the user id, the primary product id, the number of items purchased, the price, and the cost of goods sold.

select * from order_items;

Contains the order id, order date and time, product id, if the product was the primary product sold, the price, and the cost of goods sold.

select * from order_item_refunds;

Contains refund data, including the date and time of refund, order id, and the refund amount.

(2) Recreate an EER diagram and implement changes to the current MFF schema

Each table was recreated by making a new data model and adding tables with respective data columns, data types, and field properties, duplicating the current state of the MFF database. Notably, table relationships were nonexistent in the current database.

Table relationships were created, connecting primary table keys with foreign keys in related tables.

State of MFF schema before changes:

MFF schema after changes:

(3) Key business questions:

"Pull a monthly trend of a count of website sessions, and a monthly trend of total orders sold, each for the lifetime of the business. "

"Pull a count of orders sliced by primary_product_id for the year 2014. Compare that to a count of orders from 2013, and calculate the YOY increase"

"Use the website_sessions table to pull a monthly trended count of website_sessions for each of the 3 major paid traffic sources."

"Pull a trend of the order_refund rate by month for the life of the business. "

Recommendations:

-Prioritize why The Birthday Sugar Panda has the highest return rate.

-Investigate why The Original Mr. Fuzzy had such a high return rate at the end of 2014.

-Continue to monitor website sessions and orders to see if the order conversion rate continues to increase.

-Consider increasing visibility on other social media platforms, other than social book.

-Determine why products are being returned, specifically if there are product defects

Tools used: SQL, Excel, Photoshop