Plato's Pizza Project
This project analyzes Plato's Pizzeria to help find business insights and improve financial success.
Intro: Plato's Pizza Project
​
Of all my projects, the story behind this one is my very favorite! Having completed my Capstone project for the Data Analytics Google certificate using R-programming, I wanted to do a new project using MySQL and Tableau. I also knew I wanted to try to do a collaborative project, to demonstrate my teamwork skills to future employers. As I was reading through LinkedIn posts, I saw that another former teacher transitioning into data analysis had posted her capstone project. After reaching out to ask for a connection, we started corresponding and discovered that we had so much in common and we decided that we wanted to work on a project together. And so the journey began and a wonderful friendship blossomed!
This 2015 open-source dataset is available here, containing a set of four tables. The fields contained information about customer and order ids, order dates and amounts, and pizza types, sizes, and toppings.
You can view our Change Log of the major steps we collaborated on for this project as well as our SQL queries to explore and aggregate the data at our Github.
​
Viewing, exploring and cleaning!
​
To get started, we viewed each table of the four tables (order_details, orders, pizza_types and pizzas) and explored the contents of each. The order_details table, comprised of 5,941 rows, shows different pizza names and quantities ordered. For this table, we changed the DBMS to make "order_details_id" a primary key, "order_id" and "pizza_id" foreign keys and all columns non-null. The orders table shows dates and times of all 21,350 orders from 01/01/15-12/31/15. We changed the DBMS to make "order_id" a primary key & all columns non-null. In the pizza_types table there are 32 different types of pizzas. For this table, "pizza_type_id" was made into a primary key & all columns were changed to non-null in the DBMS. The pizza table shows the size, type and price of each pizza. There are 96 kinds of pizzas (32 different types in 3 sizes each). In this table, "pizza_id" was made into a primary key and "pizza_type_id" was made a foreign key, all columns changed to non-null in the DBMS.
​
All data types were changed to appropriate types, for example some were text but needed to be varchar. Additionally price was set as a float data type, but was changed to a double.
​
Finally we used reverse engineering to create a schema of the database, identifying which keys are primary and which are foreign. This helped us visualize the relationship of the tables and their contents to one another.
Aggregating the Data
​
First of all, we investigated and discovered that Platos’ Pizza sells 32 types of pizzas using this query.
​
​
​
​
​
Then, we used the following query to determine that Plato’s Pizza had made 49,574 pizzas in 2015.
​
​
​
​
​
Next, we determined that 21,350 orders had been placed, using the following query.
​
​
​
​
​
​
To figure out that the average daily customers was 59.64, we used this query. (And then we rounded up to 60 because .64 of a person just doesn't work.) The denominator was 358 because Plato's Pizza was closed for 7 days in 2015.
​
​
​
​
​
​
In order to calculate the average pizzas per order as 2.30, this was the query we utilized. It was rounded down to 2 for the dashboard, because partial pizzas were not being ordered.
​
​
​
​
​
​
Next, we wanted to determine what sizes of pizzas sell best. The following query revealed that of the 49,574 total pizzas sold in 2015, only 28 of them were XXL and 552 were XL. The combined XL and XXL pizzas represent only 2% of the total sales.
​
​
​
​
​
​
​
​
The total annual revenue of $817,860 was calculated with this query.
​
​
​
​
​
​
And then we turned our attention to figuring out the average pizza price to be $16.44 with the following SQL code.
​
​
​
​
​
To calculate the busiest days of the week, we ran this query.
​
​
​
​
​
​
​
​
​
Then, we wanted to determine the times of day when Plato’s Pizza was the busiest.
​
​
​
​
​
​
​
Next, we wanted to determine what were the monthly revenue trends.
​
​
​
​
​
​
And we were also interested in the monthly quantity trends.
​
​
​
​
​
​
​
We then looked into what pizzas sold best by quantity.
​
​
​
​
​
​
​
​
And we determined which pizzas generated the most revenue in sales.
​
​
​
Data Visualization
​
First, we analyzed customer trends by visualizing the patterns in times and days orders were placed. A heat map helps us to visually and quickly pick out busiest and slowest days and times. This heat map shows that weekday lunch hours and weekend dinner hours have the highest number of orders placed. Before 11:00 A.M. and after 9 P.M. orders greatly drop with the exception of Friday and Saturday, where orders continue to be strong in the later hours of the day.
​
​
​
​
​
​
​
​
​
​
​
We then examined monthly trends, looking at the total revenue for each month with a line graph. This shows us that revenue-highs seem to follow bi-monthly peaks, meaning more orders are placed, thus more revenue is earned, in January, March, May, July, and November. The lowest earning months are September, October, December & February. The holidays likely play a role in these trends, as more people tend to order pizza around New Years and Independence Day, but are less likely to order around the December holidays. November is an interesting peak, and may be less attributed to Thanksgiving and more to families being too busy to cook as the prepare for the winter holidays.
​
​
​
​
​
​
​
​
​
​
​
​
​
​
​
​
Next, we looked into sales by pizza size. Of the total 49,574 pizzas sold in 2015, only 552 were XL size. Moreover, only 28 pizzas of XXL size were sold. The total of both these sizes only accounts for 2% of the total quantity sold. We utilized a treemap to represent the pizzas sold by each size.
​
​
​
​
​
​
​
​
​
​
Lastly, we compared pizza products using bar graphs to determine the best and worst selling pizzas based on the amount ordered and the revenue earned. Classic Deluxe Pizza is ordered most frequently while the Thai Chicken Pizza generates the most revenue. However, Brie Carre is consistently ordered the least and generates the least revenue.
​
​
​
Recommendations
Based on the analysis above, we would recommend the following to Plato’s Pizzeria:
-
Ensure the pizzeria is appropriately staffed to handle busiest times of day (lunch on weekdays and dinner on weekends including Fridays).
-
Offer promotions, coupons, and/or discounts for the slower earning months of the year.
-
Advertise discounts in late summer (August) and continue through the fall to drive in more business during the slower months of September and October. And then use promotional tactics again in December and February.
-
-
Promote Pepperoni and BBQ Chicken Pizzas to continue strong streams of revenue from these successful pizza types.
-
But remove Brie Carre from the menu, as it consistently doesn’t perform well with customer orders.
-
Discontinue XL and XXL pizza sizes as they account for only 2% of total revenue.
The dashboard can be explored on Tableau here.​
​
.png)
Action!
​
I thank you for reading and welcome your feedback! Please consider following me or connecting on LinkedIn at Carly Jocson. And keep me in mind for any remote positions as a data analyst!