BUSINESS ANALYSIS USING SQL

Pizza Runner Case Study

INTRODUCTION

Danny is sold on the idea of starting a pizza business, but he knows that pizza alone will not help him get seed funding to expand his Empire — so he comes up with one more genius idea to combine with it — Uberize it — and so Pizza Runner was launched!

Danny started by recruiting “runners” to deliver fresh pizza from Pizza Runner Headquarters (otherwise known as Danny’s house) and also maxed out his credit card to pay freelance developers to build a mobile app to accept orders from customers. Although his business is flourishing, Danny is hungry for business growth and he is very aware that data collection will be critical for that. He prepared an entity relationship diagram of his database design but requires further assistance to clean his data and apply some basic calculations so he can better direct his runners and optimise Pizza Runner’s operations. That’s where I come in!

The database contains six tables namely:

• Runners

• Customer_orders

• Runner_orders

• Pizza_names

• Pizza_recipes

• Pizza_toppings

Note: MySQL was used to work on this project.

DATA CLEANING

After properly checking through the datasets given, three of them needed preprocessing; customer_orders, runner_orders and pizza_recipes.

• For customer_orders: The “null” values were properly formatted to NULL. Also, the rows containing comma separated values were separated into individual rows.
• For runner_orders: The “null” values were properly formatted to NULL. Also, the data type of the distance and duration columns were changed after removing the units.
• For pizza_recipes: The rows containing comma separated values in the “toppings” column were separated into individual rows.

Data Cleaning Code Snippet

The rest of the data clening code can be found in the github file.

Cleaned Runner_id table

Cleaned customer_id table

CASE STUDY QUESTIONS : PIZZA METRICS

1. How many pizzas were ordered?

Code

Result

This means so far, pizza_runner has had a total of 19 orders.

2. How many unique customer orders were made?

Code

Result

10 unique customers have ordered from Pizza runner.

3. How many successful orders were delivered by each runner?

Code

Result

This shows that Runner 1 has had the highest number of orders (4).

4. How many of each type of pizza was delivered?

Code

Result

There were 15 meat lovers pizza orders and 4 vegetarian pizza orders. This means most customers loved meat lovers piza.

5. How many Vegetarian and Meatlovers were ordered by each customer?

Code

Result

6. What was the maximum number of pizzas delivered in a single order?

Code

Result

The maximum number of pizzas delivered in a single order is five (5).

Get Chelsea Adetan’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

7. For each customer, how many delivered pizzas had at least 1 change and how many had no changes?

Code

Result

8. What was the total volume of pizzas ordered for each hour of the day?

Code

Result

This shows that the most pizzas were ordered between the hours of 6pm — 7pm (18:00–18:59).

9. What was the volume of orders for each day of the week?

Code

Result

The most number of pizzas were ordered on Saturdays.

CASE STUDY QUESTIONS : RUNNER AND CUSTOMER EXPERIENCE

10. How many runners signed up for each 1 week period? (week starts from 2021–01–01)

Code

Result

11. What was the average time in minutes it took for each runner to arrive at the Pizza Runner HQ to pickup the order?

Code

Result

Runner 3 is the fastest runner to arrive at the pizza runner headquarters.

12. Is there any relationship between the number of pizzas and how long the order takes to prepare?

Code

Result

There is an anomaly with a 9 pizza-order having an average preparation time of 15 minutes compared to an 8-pizza order with 23 minutes.

13. What was the average distance travelled for each customer?

Code

Result

14. What was the difference between the longest and shortest delivery times for all orders?

Code

Result

15. What was the average speed for each runner for each delivery and do you notice any trend for these values?

Code

Result

There is an outlier with Runner 2 having an average speed of 93.60 km/s.

16. What is the successful delivery percentage for each runner?

Code

Result

No runner has an 100% succesful delivery percentage. Runner 2 has the highest with 75% success.

KEY INSIGHTS GENERATED

• There have been 19 pizza orders in total.
• The customers’ favourite pizza is the Meatlovers pizza.
• The most pizzas were ordered between the hours of 6pm — 7pm.
• The most orders for pizzas were made on Saturdays.
• Pizza runner 1 has the highest pizza orders but Pizza runner 2 has the highest successful pizza order delivery.

RECOMMENDATION

For Danny to expand his pizza business based on this insights, he’ll need to make meatlovers available more often. There should also be optimal service and high availability on Saturdays since the most orders were made on that day.

I’m really grateful to Danny Ma for providing the platform for me to practice their SQL skills. To check out my code, click on this github link.

If you’re also interested in working on the case study, you can do that using this link.

Thank you for reading through.