The Good News Bears

Key Insights:

1. Sales of the Original Mr. Fuzzy grew 93.7% from 2013-2014.
2. Sales of the Forever Love Bear grew 109.6% from 2013-2014.
3. Sales of the Birthday Sugar Panda grew 47.4% from the first 6 months of 2014 to the second 6 months of 2014.
4. Total website sessions and orders followed a similar trend with Gsearch leading more customers to our website followed by Bsearch and Socialbook, respectively.
5. The refund rate dropped 8.5% once supplier issues were resolved in September 2014.

Next Steps:

1. Continue analyzing website traffic and orders data to improve conversion rates and the value of ad spend.
2. Monitor ongoing sales of the Hudson River Mini Bear. The product debuted in February 2014. I compared the first 7 months of orders to the last 7 months of orders, and sales are up 81.9%.
3. Monitor sales of the Birthday Sugar Panda to determine if this product is meeting revenue goals.

After the exploratory analysis, I created an EER Diagram from the database and updated it to improve data infrastructure.

Before including primary key and foreign key mappings:

After:

SQL Code for EER sync:

-- MySQL Workbench Synchronization

-- Generated: 2023-05-03 11:06

-- Model: New Model

-- Version: 1.0

-- Project: Name of the project

-- Author: mcombswright

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

CREATE SCHEMA IF NOT EXISTS mydb DEFAULT CHARACTER SET utf8 ;

CREATE TABLE IF NOT EXISTS mydb.order_item_refunds (

order_item_refund_id INT(11) NOT NULL,

created_at DATETIME NOT NULL,

order_item_id INT(11) NOT NULL,

order_id INT(11) NOT NULL,

refund_amount_usd DECIMAL(12,2) NOT NULL,

PRIMARY KEY (order_item_refund_id),

INDEX orders_order_id_idx (order_id ASC) VISIBLE,

INDEX refund.order_item_idx (order_item_id ASC) VISIBLE,

CONSTRAINT orders_order_id

FOREIGN KEY (order_id)

REFERENCES mydb.orders (order_id)

ON DELETE NO ACTION

ON UPDATE NO ACTION,

CONSTRAINT refund.order_item

FOREIGN KEY (order_item_id)

REFERENCES mydb.order_items (order_item_id)

ON DELETE NO ACTION

ON UPDATE NO ACTION)

ENGINE = InnoDB

DEFAULT CHARACTER SET = utf8;

CREATE TABLE IF NOT EXISTS mydb.order_items (

order_item_id INT(11) NOT NULL,

created_at DATETIME NOT NULL,

order_id INT(11) NOT NULL,

product_id INT(11) NOT NULL,

is_primary_item INT(11) NOT NULL,

price_usd DECIMAL(12,2) NOT NULL,

cogs_usd DECIMAL(12,2) NOT NULL,

PRIMARY KEY (order_item_id),

INDEX orders.order_id_idx (order_id ASC) VISIBLE,

INDEX products.product_id_idx (product_id ASC) VISIBLE,

CONSTRAINT orders.order_id

FOREIGN KEY (order_id)

REFERENCES mydb.orders (order_id)

ON DELETE NO ACTION

ON UPDATE NO ACTION,

CONSTRAINT products.product_id

FOREIGN KEY (product_id)

REFERENCES mydb.products (product_id)

ON DELETE NO ACTION

ON UPDATE NO ACTION)

ENGINE = InnoDB

DEFAULT CHARACTER SET = utf8;

CREATE TABLE IF NOT EXISTS mydb.orders (

order_id INT(11) NOT NULL,

created_at DATETIME NOT NULL,

website_session_id INT(11) NOT NULL,

user_id INT(11) NOT NULL,

primary_product_id INT(11) NOT NULL,

items_purchased INT(11) NOT NULL,

price_usd DECIMAL(12,2) NOT NULL,

cogs_usd DECIMAL(12,2) NOT NULL,

PRIMARY KEY (order_id),

INDEX ws_website_session_id_idx (website_session_id ASC) VISIBLE,

CONSTRAINT ws_website_session_id

FOREIGN KEY (website_session_id)

REFERENCES mydb.website_sessions (website_session_id)

ON DELETE NO ACTION

ON UPDATE NO ACTION)

ENGINE = InnoDB

DEFAULT CHARACTER SET = utf8;

CREATE TABLE IF NOT EXISTS mydb.products (

product_id INT(11) NOT NULL,

created_at DATETIME NOT NULL,

product_name VARCHAR(45) NOT NULL,

products_product_id INT(11) NOT NULL,

PRIMARY KEY (product_id))

ENGINE = InnoDB

DEFAULT CHARACTER SET = utf8;

CREATE TABLE IF NOT EXISTS mydb.website_pageviews (

website_pageview_id INT(11) NOT NULL,

created_at DATETIME NOT NULL,

website_session_id INT(11) NOT NULL,

pageview_url VARCHAR(45) NOT NULL,

PRIMARY KEY (website_pageview_id),

INDEX wp_website_session_id_idx (website_session_id ASC) VISIBLE,

CONSTRAINT wp_website_session_id

FOREIGN KEY (website_session_id)

REFERENCES mydb.website_sessions (website_session_id)

ON DELETE NO ACTION

ON UPDATE NO ACTION)

ENGINE = InnoDB

DEFAULT CHARACTER SET = utf8;

CREATE TABLE IF NOT EXISTS mydb.website_sessions (

website_session_id INT(11) NOT NULL,

created_at DATETIME NOT NULL,

user_id INT(11) NOT NULL,

is_repeat_session INT(11) NOT NULL,

utm_source VARCHAR(45) NOT NULL,

utm_campaign VARCHAR(45) NOT NULL,

utm_content VARCHAR(45) NOT NULL,

device_type VARCHAR(45) NOT NULL,

http_referer VARCHAR(45) NOT NULL,

PRIMARY KEY (website_session_id))

ENGINE = InnoDB

DEFAULT CHARACTER SET = utf8;

CREATE TABLE IF NOT EXISTS mydb.website_pageviews (

website_pageview_id INT(11) NOT NULL,

created_at DATETIME NOT NULL,

website_session_id INT(11) NOT NULL,

pageview_url VARCHAR(45) NOT NULL,

PRIMARY KEY (website_pageview_id))

ENGINE = InnoDB

DEFAULT CHARACTER SET = utf8;

SET SQL_MODE=@OLD_SQL_MODE;

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;