Pizza Analysis

In this project, I worked with Maven Analytics Pizza dataset using MySQL to answer the following questions:

Business Questions

1. How many customers do we have each day?
2. Are there any peak hours?
3. Are there any crest hours?
4. Are there any peak Days?
5. Are there any crest Days?
6. How many pizzas are typically in an order? Do we have any bestsellers?
7. Which pizza generated the most revenue? The least?

Insights

1. The average number of customers was 59.
2. The best hours are 1:00 PM, 6:00 PM, and 7:00 PM.
3. The worst hours are 9 AM, 10 AM, 11 PM, 10 PM
4. The best day was 2015-11-27
5. The worst day was 2015-12-29
6. The average order contains 2.27 pizzas.
7. More small big meat pizzas were sold than any other pizza.
8. More revenue was generated by the Large Thai chicken than any other pizza.
9. Less revenue was generated by the Greek extra extra large pizza than any other pizza.

Analysis

How many customers do we have each day?

The number of actual customers would be difficult to determine with the given dataset. So I used the metric of the number of orders. A single household could have multiple customers and a single pizza could be for one or more customers. The average number of orders per day was 59.

I calculated this in a SELCT statement that returns the COUNT of order ids divided by the count of unique dates.

2. Are there any peak hours?

Seasonality analysis could potentially be worth considering. SQL might not be the best choice as the primary analysis tool for this. A tool with visualization capacity would be helpful. However, I was able to make some useful aggregations. I was ale to determine the total number of orders during specific hours.

I used a COUNT function on the order ids Grouped by the hour (extracted from the time). I was able to use this query to determine that the peak hours were 1:00 PM, 6:00 PM, and 7:00 PM.

3. Are there any crest hours?

I used the same query to determine that the hours with the least sales are 9 AM, 10 AM, 11 PM, 10 PM.

4. Are there any peak Days?

I used the following query to count the order ids grouped by the date. I was able to determine that the specific day with the most sales was November 27. 2015-11-27

5. Are there any crest Days?

I used the query above to determine the day with the least sales was December 29.

6. How many pizzas are typically in an order?

I was able to determine this by dividing the COUNT of the order detail ids by the DISTINCT COUNT of the order Id. Each order detail represented a unique pizza and each unique order id represents a separate order.

The average number of pizzas per order was 2.27 pizzas.

7. Do we have any bestsellers?

I used the following query to count the pizza ids grouped by the pizza and size combination.

More small big meat pizzas were sold than any other pizza.

8. Which pizza generated the most revenue? The least?

For this question I had to join two tables. The pizza table describes the unique pizza, size and price on the menu, the order details table describes the items (per order). Both tables are required to determine the quantity and price for each pizza. The result is that the Large Thai chicken generated more revenue than any other pizza and the Greek extra extra large pizza generated less revenue than any other pizza.

More small big meat pizzas were sold than any other pizza.

Conclusion

This analysis is more focused on exploration than anything else. Determining which dates and times have the highest number of customers, and which pizzas generate the least and most revenue help the owners ask more specific questions without suggesting answers.