Maven Pizza Sales Challenge
About this project
In this project, I decided to take the role as the lead analyst for a pizza joint and fulfill the requested analysis.
Requested Analysis (From Maven Analytics)
- How many customers do we have each day? Are there any peak hours?
- How many pizzas are typically in an order? Do we have any bestsellers?
- How much money did we make this year? Can we identify any seasonality in the sales?
- Are there any promotions we could leverage?
- Are there any pizzas we should add or take off of the menu?
Below you will find a dashboard that answers each of these questions.
Take Aways
After analysis of the Pizza dataset, I believe that the business should consider taking recommendation on the last dashboard. (Selling more pizzas in Size XL and removing the Green Garden and Brie Carre from the menu) As well as Considering slightly raising the prices of pizzas that we sell a high volume of that have a low cost compared to others, for example, pepperoni. In an average month we sell 202 pepperoni pizzas, if we raise the prices by 5% it would generate an additional$1,500 a year.
Sample Scripts
All of the data used in this project was queried using MySQL. Below are a few sample queries used.
-- This returns the number of pizza's ordered based on size
SELECT
DISTINCT size,
CASE WHEN size = 'S' THEN SUM(quantity)
WHEN size = 'M' THEN SUM(quantity)
WHEN size = 'L' THEN SUM(quantity)
WHEN size = 'XL' THEN SUM(quantity)
WHEN size = 'XXL' THEN SUM(quantity) END AS total
FROM order_details ord
JOIN pizzas piz
ON ord.pizza_id = piz.pizza_id
GROUP BY 1;
-- This returns the average pizzas sold by pizza type for every month except November
SELECT
t1.*,
november_sales
FROM(
SELECT
pizza_type_id,
ROUND(SUM(quantity)/11, 0) rest_of_year_sold
FROM order_details od
LEFT JOIN pizzas p
ON od.pizza_id = p.pizza_id
WHERE order_id NOT IN(
SELECT order_id FROM orders
WHERE month(date) = 11)
GROUP BY 1) t1
LEFT JOIN(
SELECT -- This returns the average pizzas sold by pizza type for November
pizza_type_id,
SUM(quantity) november_sales
FROM order_details od
LEFT JOIN pizzas p
ON od.pizza_id = p.pizza_id
WHERE order_id IN(
SELECT order_id FROM orders
WHERE month(date) = 5)
GROUP BY 1) t2
ON t1.pizza_type_id = t2.pizza_type_id;
/* This returns the sales by month based on pizza type.
The script is repetitive as you cannot reference a temp table in multiple joins in a single Query.
It could be set up as a stored procedure to be more readable in a business setting,
but without a need to reuse this script again it would not be efficient. */
SELECT
pizza_type,
Jan,
Feb,
Mar,
Apr,
May,
Jun,
Jul,
Aug,
Sep,
Oct,
Nov,
December.Dec
FROM pizza_types pizza
LEFT JOIN(
SELECT * FROM(
SELECT pizza_type_id,
month(date) date,
ROUND(SUM(price*quantity),2) Jan
FROM order_details od -- This returns total sales based on pizza type
LEFT JOIN pizzas p
ON od.pizza_id = p.pizza_id
LEFT JOIN orders o
ON o.order_id = od.order_id
GROUP BY 1,2) Jan
WHERE date = 1) Jan
ON Jan.pizza_type_id = pizza.pizza_type
LEFT JOIN(
SELECT * FROM(
SELECT pizza_type_id,
month(date) date,
ROUND(SUM(price*quantity),2) Feb
FROM order_details od -- This returns total sales based on pizza type
LEFT JOIN pizzas p
ON od.pizza_id = p.pizza_id
LEFT JOIN orders o
ON o.order_id = od.order_id
GROUP BY 1,2) Feb
WHERE date = 2) Feb
ON Feb.pizza_type_id = pizza.pizza_type
...
** I limited this script to only show through February as it is roughly 200 lines of code.
Additional project images
