Data-Driven Decisions: How Analytics Shapes the Pizza Menu.
Introduction
In the dynamic landscape of the culinary industry, understanding and harnessing key performance indicators (KPIs) is pivotal for the sustained success of any restaurant. For the owner of a thriving pizza establishment, gaining insights into the intricacies of business performance is a mission-critical endeavor.
In pursuit of this objective, our project aims to delve into the rich tapestry of pizza sales data, uncovering essential metrics that serve as the compass guiding strategic decisions.
Business task: Ask
Our focus revolves around four fundamental metrics that stand as pillars in evaluating the financial and operational health of the pizza business: total revenue, average sale price, total orders, and the average number of pizzas per order.
Through this exploration, we seek to empower the owner with a data-driven understanding of their business, enabling them to make informed decisions, adapt to market trends, and carve a niche in the competitive world of pizza gastronomy.
Preparing the Data
The dataset at our disposal is a fabricated CSV file comprising comprehensive information on pizza sales throughout the year 2015. It encompasses 11 attributes, namely: pizza_Id, order_id, pizza_name_id, quantity, order_date, order_time, unit_price, total_price, pizza_category, and pizza_ingredients. This dataset serves as the foundation for our analysis, offering a detailed snapshot of various dimensions related to pizza sales during the specified timeframe.
We will employ Microsoft Excel for data manipulation, Microsoft SQL for efficient database management, and Tableau for insightful data visualization in our analysis of pizza sales metrics.
Processing the Data
Initially, the process commenced with importing the dataset into a Microsoft SQL Server, followed by the creation of a new database named PizzaDB. Subsequently, the CSV file was imported into the PizzaDB database as a flat file. The dataset comprises 48,620 rows, and a validation step involved executing a query to ensure the successful import of all rows into the SQL database.
Select *
From pizza_sales 
All rows were successfully imported into SQL.
Analyzing the Data
The initial inquiry from the business owner centered on determining total revenue, which involves summing up the combined prices of all pizza orders.
I wrote the query below to calculate the total revenue:
Select SUM(total_price) as Total_revenue
From pizza_salesThe total revenue returned was $817,860.05.
The business owner’s second query pertained to the calculation of the average order price. This involved dividing the total revenue by the count of unique orders placed.
Select SUM(total_price)/COUNT(distinct order_id) as Average_Order_Price
From pizza_sales
From the result above the average order price is $38.31.
The business owner’s third question revolved around determining the total quantity of pizzas sold in the year 2015. This task involves aggregating the quantity from all pizza orders placed during that period.
Select SUM(quantity) as Total_pizza_sold
From pizza_sales
The total number of pizzas sold in the year 2015 was 49,574 pies.
The fourth inquiry from the business owner focused on the total volume of pizzas sold in the year 2015. This could be achieved by summing up the number of unique pizza orders.
Select COUNT(distinct order_id) as Total_orders
From pizza_sales
The restaurant received a total of 21,350 orders in the fiscal year of 2015.
Finally, the owner inquired about the average number of pizzas per order. This calculation involves dividing the total number of pizzas by the overall count of pizza orders.
Select CAST(Sum(quantity) as decimal(10,2)) /
CAST(Count(distinct order_id)as decimal(10,2)) as Averge_pizza_per_order
from pizza_sales 
The average number of pizzas per order is 2.32.
Analyzing trends
The first trend the business owner inquired about was the number of pizzas sold each hour the restaurant was open.
--- Hourly trend for total pizza sold
Select DATEPART(HOUR, order_time) as order_hour, SUM(quantity) as Total_pizzas_sold
From pizza_sales
Group By DATEPART(HOUR, order_time)
Order by DATEPART(HOUR, order_time)
The results also show the restaurant is open from 9 am to 11 pm and the restaurant receives the most orders between 12–1 pm weekly
The second trend the business owner inquired about was the number of pizzas sold each week during the year 2015
---Weekly trend for total orders
Select DATEPART(ISO_WEEK, order_date) as week_number, YEAR(order_date) as Order_year,
COUNT(distinct order_ID) as Total_orders
From pizza_sales
Group By DATEPART(ISO_WEEK, order_date), YEAR(order_date)
Order By DATEPART(ISO_WEEK, order_date), YEAR(order_date)The query returned 53 rows matching 53 weeks with total orders for each week.
The second trend the business owner inquired about was the percentage of pizza sales by category.
SELECT
pizza_category,
SUM(total_price) as Total_Sales,
SUM(total_price) * 100 / (SELECT SUM(total_price) FROM pizza_sales WHERE MONTH(order_date) = 1) as PCT_of_total_sales
FROM
pizza_sales
WHERE
MONTH(order_date) = 1
GROUP BY
pizza_category;
Subsequently, the owner sought information on the distribution of sales by pizza size, wanting to understand the percentage each size contributes to the overall sales.
SELECT
pizza_size,
SUM(total_price) as Total_Sales,
CAST((SUM(total_price) * 100) / (SELECT SUM(total_price) FROM pizza_sales) AS DECIMAL(10,2)) as PCT_of_total_sales
FROM
pizza_sales
GROUP BY
pizza_size
ORDER BY
PCT_of_total_sales DESC;
The owner subsequently asked the following questions and I employed SQL to analyze and respond to each.
- What are the top 5 best-selling pizzas by revenue?
Select TOP 5 pizza_name, SUM(total_price) as Total_pizza_revenue
From pizza_sales
Group by pizza_name
Order by Total_pizza_revenue DESC
Answer: The top 5 best-selling pizzas by revenue are Thai Chicken, Barbecue chicken, California chicken, classic deluxe, and spicy Italian pizza respectively.
2. What are the lowest 5 selling pizzas by Revenue?
Select TOP 5 pizza_name, SUM(total_price) as Total_pizza_revenue
From pizza_sales
Group by pizza_name
Order by Total_pizza_revenue ASC 
Answer: The 5 lowest-selling pizzas by revenue are the Brie Carre, Green Garden, Spinach Supreme, Mediterranean, and Spinach pesto pizzas respectively.
3. What are the top 5 selling pizzas by quantity?
Select TOP 5 pizza_name, SUM(quantity) as Total_pizza_quntity
From pizza_sales
Group by pizza_name
Order by Total_pizza_quntity DESC
Answer: The Top 5 selling pizzas by quantity are the Classic deluxe, barbecue chicken, Hawaiian, pepperoni, and Thai Chicken pizzas respectively.
4. What are the bottom 5 selling pizzas by quantity?
Select TOP 5 pizza_name, SUM(quantity) as Total_pizza_quntity
From pizza_sales
Group by pizza_name
Order by Total_pizza_quntity ASC
Answer: The bottom 5 selling pizzas by quantity are The Brie carre, Mediterranean, calabrese, spinach supreme, and Soppressata pizzas respectively.
What are the top 5 selling pizzas by total orders?
Select TOP 5 pizza_name, COUNT(distinct order_id) as total_orders
From pizza_sales
Group by pizza_name
Order by Total_orders desc
Answer: The top 5 selling pizzas by total orders are the classic deluxe, Hawaiian, pepperoni, barbecue chicken, and Thai chicken pizza respectively.
What are the bottom 5 selling pizzas by total orders?
Select TOP 5 pizza_name, COUNT(distinct order_id) as total_orders
From pizza_sales
Group by pizza_name
Order by Total_orders ASC
Answer: The bottom 5 selling pizzas by total orders are the brie carre, Mediterranean spinach supreme, calabrese, and chicken pesto pizzas respectively.
Visualizing the Data
In this phase, I translated all the aforementioned findings into visual representations, employing Tableau for the visualization process.
I used the calculated field function in Tableau to find the following for the year 2015
- Total revenue — $817.9k
- Average order price — $38.31
- Total Pizzas Sold — 49.6k
- Total orders — 21.35K
- Average Pizza Order — 2.32
All values calculated also match the results of the SQL calculations during the analysis phase of the project
The next visualization created was the hourly trend for pizzas sold. The owner wanted to know what hours sold the most pizzas.
The restaurant receives the most orders between 12–1 pm weekly
The next visualization created was the weekly trend for total orders.
The owner wanted to know what pizza was selling the most so, The next visualization created was the percentage of sales by pizza category.
The next visualization was the percentage of Sales by pizza Size.
The next visualization was the total orders and pizzas sold by pizza category.
All visualizations were combined into an interactive dashboard.
The next visualization created was the Top and Botton 5 pizzas by revenue.
The next visualization created was the Top and Botton 5 pizzas by quantity.
The next visualization created was the Top and Botton 5 pizzas by total orders.
Lastly, I added all visualizations into a best and worst sellers dashboard
A link to the full dashboard can be found here
Findings/Recommendations
- Upon analyzing the data, it’s evident that four out of the top five best-selling pizzas, based on revenue, share a common ingredient — chicken. Therefore, I recommend maintaining an ample inventory of chicken to consistently meet the demand for these popular pizza variations.
- The data also reveals that the restaurant experiences peak hours daily from 12 to 1 PM. In light of this, my recommendation is for the manager to optimize staff schedules accordingly during these hours to ensure a seamless and efficient operation.
- The Brie Carre pizza ranks lowest in terms of revenue, total orders, and quantity. From a data perspective, I recommend considering the removal of the Spanish pizza from the menu and introducing a new pizza variant that aligns better with customer preferences.
Conclusion
Our project has successfully navigated the intricate landscape of pizza sales data, providing valuable insights to inform strategic decisions. The identification of a common ingredient, chicken, in four of the top five best-selling pizzas highlights a clear opportunity for inventory management optimization.
Additionally, recognizing the daily peak hours from 12 to 1 PM underscores the importance of strategic staffing.
Moreover, our data analysis reveals that the Brie Carre pizza lags in revenue, total orders, and quantity.
In essence, leveraging these data-driven recommendations can contribute to enhancing overall operational efficiency, inventory management, and customer satisfaction, ultimately fostering a more successful and adaptive approach to the restaurant’s offerings.
