Plato's Pizza Marketing Analysis

This project analyzed customer trends, revenue, and order details to refine marketing strategies and consider store and staffing needs.

Tableau Dashboard

GitHub

Introduction

I love a good pizza! Most pizza-lovers would say the same. But have you ever wondered what types of pizzas sell the most and which aren't so popular? My role as the marketing analyst was to analyze the trends in sales and customer habits as well as the pizza products for Plato's Pizza.

This was a collaboration project that a colleague and I took on to assist the fictional pizza company with their marketing strategies.

About the Data

This 2015 open-source dataset is available on the Maven Analytics DataPlayground. There were 4 tables (order_details, orders, pizza_types and pizzas) with records ranging from 33 to 48,000. 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 my Github.

Viewing and Exploring each Table in the Database

To get started, I viewed each of the four tables and explored the contents of the records. The 'order_details' table, comprised of 48,621 rows, showed different pizza names and quantities ordered. For this table, I changed the DBM to make "order_details_id" a primary key, "order_id" and "pizza_id" foreign keys and all columns non-null. The 'orders' table showed dates and times of all 21,350 orders from 01/01/15-12/31/15. I changed the DBM to make "order_id" a primary key and all columns non-null. In the 'pizza_types' table there were 32 different types of pizzas. For this table, "pizza_type_id" was made into a primary key and all columns were changed to non-null in the DBM. The 'pizza' table showed the size, type and price of each pizza. There were 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 DBM.

Cleaning the Data

All data types were changed to the 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 while the date and time were set the the date and time data types.

I also verified no duplicate records and checked for null values.

Aggregating the Data in MySQL

First, an exploratory analysis was necessary to get an idea of the big picture for the pizza inventory and sales for 2015. I discovered that Plato's Pizza sells 32 types of pizzas.

Then, I used the following query to determine that Plato’s Pizza had made 49,574 pizzas in 2015.

Next, I determined that 21,350 orders had been placed, using the following query.

The total annual revenue of $817,860 was calculated with this query.

Moving into some statistical analyses, I calculated the average daily customers to be 59.64, or rounded to approximately 60 customer per day, found using this query. Due to the fact that there were 7 days the pizzeria was closed, dividing by the 358 days of operation showed an accurate picture of daily customers.

And the average pizzas per order as 2.30, or could be rounded to 2 pizzas per order placed.

The average pizza price was $16.44.

Moving into analyzing customer trends, I calculated the busiest days and times of the week, using these queries.

Zooming out to look at the entire year by analyzing monthly trends, I ran the following query to show monthly revenue trends, and comparing this to monthly quantity trends.

Taking a look into the pizza products themselves, I investigated which pizzas sold best by quantity and determined which pizzas generated the most revenue in sales.

Finally, looking at the sizes of pizzas, I determined which ones sold the best.

- It is important to note that there were only 28 XXL and 552 XL pizzas sold all year. That accounts for only 2% of the total 49,574 pizzas sold.

Visualizing the Data

After developing a strong understanding of the pizzeria's figures for 2015 using MySQL, I then visualized these results and insights in an interactive dashboard in Tableau.

I started with 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.

I then examined monthly and quarterly trends, looking at the number of orders placed and total revenue for each month with line graphs. These show us that order- and 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, and December. 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 a peak to pay attention to, as one would not expect pizza to be ordered as often around the Thanksgiving and other holidays around this time. It would be of interest to investigate any promotions, deals/discounts, and other patterns noticed during this month.

Lastly, I compared pizza products using bar graphs to determine the best and worst selling pizzas based on the amount ordered and the revenue earned. Pepperoni Pizza is ordered most frequently while the BBQ Chicken Pizza generates the most revenue. However, the Brie Carre is consistently ordered the least and doesn’t bring in much in terms of earnings.

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, October, and November.
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.

Please feel free to follow me on LinkedIn here!