Introduction
Pizza Runner is an “uberized” pizza delivery service launched by Danny offering on call pizza delivery service from their own restaurant. Danny has a simple database on all of the restaurant’s operations. However, raw data alone is not enough to drive operational success and customer satisfaction in this competitive market.
Through a series of data transformations and analyses, this project seeks to:
Optimize the database to ensure its structure is robust and fit to handle the growing volume of data.
Enhance customer satisfaction, through identifying ordering times and improving order fulfilment rates.
Increase sales through targeted recommendations and better resource allocation.
Recommend strategies to improve the lives of delivery runners, focusing on efficiency and workload balance.
1. Database Design and Optimization
Database Schema
Database Design and Optimization
I made use of:
Indexing for faster data retrieval and query run time
Implementing primary keys and foreign keys to enhance referential integrity and cascading updates as well as deletes
2. Data Analysis and Insights
a) Pizza Orders
7/10 orders made were custom while 4/10 were off-the-menu.
Cheese is the most excluded ingredient, being excluded in 80% of total orders made.
Bacon is the most requested extra, being added in 80% of total orders made.
Insights:
70% of customers prefer customized orders over 30% who stick to a standardised menu.
Bacon is the most requested add-on and cheese the most excluded ingredient.
Meat lovers is the most popular pizza type with 10 pizzas made then vegetarian with 4.
Recommendations:
Focus on promoting the ability to customize while placing an order and ensure the process is seamless.
Offer customization suggestions for popular add-ons or exclusive options to cater to customizing base.
Adjust inventory planning to account for the high exclusion rate of cheese and the high inclusion rate of bacon.
Inventory planning to also account for more ingredients for meat-lovers pizzas and balance with vegetarian ingredients.
b) Runner Performance
Insights:
Only 2/3 employees experienced cancellations in their orders. These cancellations accounted for 20% of total orders made.
On average, it takes each runner 20 minutes to deliver an order and an average of 20km.
Recommendations:
- Ensure customers / restaurant staff give reasons for cancellations
c) Revenue Collection
Danny had not yet set up a revenue collection table. Thus, we had to design one and get data from existing tables as well as staff input to derive total revenue. View code used on GitHub
A meat-lovers pizza goes for KSh 1,500 and a vegetarian one for KSh 1,280. There are no charges for customization or delivery.
We analysed multiple pricing scenarios as suggested by marketing to optimize returns while minimizing the impact of sudden price increases on customers.
Current Scenario: A Meat Lovers pizza costs KSh 1,500 and Vegetarian costs KSh 1,280. There are no charges for customization or delivery.
Scenario 1: Retained prices for off the menu orders and an additional KSh 100 for a customized pizza.
A single customised order takes an additional 10 minutes on average to prepare compared to standard menu options. Adding KSh 50 per customized order translates to increased profitability while justifying the extra effort.
This leads to a 28% increase in revenue for Meatlovers pizzas and 50% increase in revenue for Vegetarian pizzas.
d) Profits Analysis
Current Profits: A Meat Lovers pizza costs KSh 1,500 and Vegetarian costs KSh 1,280. There are no charges for customization or delivery. Each runner is paid KSh 40 for every KM travelled during delivery.
The company spends 29% of revenue on paying runners (KSh 5,808)
Upon payment of runners, the company is left with 71% of total revenue (KSh 14,492).
3. Conclusion
In this article, we used SQL to dive into restaurant operations, analysing order trends, revenue collection, and ingredient exclusions to uncover actionable insights. By applying data-driven logic, we highlighted areas for operational improvement and offered tailored recommendations to optimize business performance.
Be sure to follow for more awesome projects.