Pizza Place Analysis.

A year's worth of sales from a fictitious pizza place, including the date and time of each order and the pizzas served, with additional details on the type, size, quantity, price, and ingredients.

This challenge can be found on Maven Analytics.

Table of Content.

• Dashboard View
• Dataset
• Questions
• Solutions

Dashboard View

Click to Download pbix file

Dataset

Click the Table Name to view the tables.

Table Name: Orders

order_details_id	order_id	pizza_id	quantity
1	1	hawaiian_m	1
2	2	classic_dlx_m	1
3	2	five_cheese_l	1
4	2	ital_supr_l	1
5	2	mexicana_m	1
6	2	thai_ckn_l	1
7	3	ital_supr_m	1
8	3	prsc_argla_l	1
9	4	ital_supr_m	1
10	5	ital_supr_m	1

Table Name: Order Details

order_id	date	time
1	1/1/2015	11:38:36
2	1/1/2015	11:57:40
3	1/1/2015	12:12:28
4	1/1/2015	12:16:31
5	1/1/2015	12:21:30
6	1/1/2015	12:29:36
7	1/1/2015	12:50:37
8	1/1/2015	12:51:37
9	1/1/2015	12:52:01
10	1/1/2015	13:00:15
11	1/1/2015	13:02:59

Table Name: Pizzas

pizza_id	pizza_type_id	size	price
bbq_ckn_s	bbq_ckn	S	12.75
bbq_ckn_m	bbq_ckn	M	16.75
bbq_ckn_l	bbq_ckn	L	20.75
cali_ckn_s	cali_ckn	S	12.75
cali_ckn_m	cali_ckn	M	16.75
cali_ckn_l	cali_ckn	L	20.75
ckn_alfredo_s	ckn_alfredo	S	12.75
ckn_alfredo_m	ckn_alfredo	M	16.75
ckn_alfredo_l	ckn_alfredo	L	20.75
ckn_pesto_s	ckn_pesto	S	12.75

Table Name: Pizza Types

pizza_type_id	name	category
bbq_ckn	The Barbecue Chicken Pizza	Chicken
cali_ckn	The California Chicken Pizza	Chicken
ckn_alfredo	The Chicken Alfredo Pizza	Chicken
ckn_pesto	The Chicken Pesto Pizza	Chicken
southw_ckn	The Southwest Chicken Pizza	Chicken
thai_ckn	The Thai Chicken Pizza	Chicken
big_meat	The Big Meat Pizza	Classic
classic_dlx	The Classic Deluxe Pizza	Classic
hawaiian	The Hawaiian Pizza	Classic
ital_cpcllo	The Italian Capocollo Pizza	Classic

Table Name: Revenue

This is a claculate Table;

Revenue = GROUPBY(orders,orders[date],"MonthSales",SUMX(CURRENTGROUP(),SUM(pizzas[price])))

Relationships

There is a One to Many relationship between;

• order_details and pizzas using pizza_id
• order_details and orders using order_id
• pizzas and pizza_types using pizza_type_id -- Revenue and orders using date

Questions

1. Are there any peak hours?
2. How many pizzas are typically in an order? Do we have any bestsellers?
3. How much money did we make this year? Can we indentify any seasonality in the sales?
4. Do we have any bestsellers?
5. Are there any pizzas we should take of the menu, or any promotions we could leverage?

Solution.

Q1) Are there any peak hours?

There is a spike of orders from around mid day (12noon to 1pm and also in the evening)

Visual: Line Graph

Q2) How many pizzas are typically in an order?

There is average of One pizza per order. This is calculated by getting the sun of all pizza ordered / Number of orders.

calculation with DAX:

averageNumberPizzas = COUNT(order_details[pizza_id])/COUNT(order_details[order_id])

*Visual: Card

Q3) Do we have any bestsellers?

The best selling pizza is The Classic Deluxe Pizza.

visual: Table

Q4) How much money did we make this year? Can we indentify any seasonality in the sales?

The higher the number of orders the high the revenue

Visual: Staked Column Chart

-Number of Orders per Month

Q5) Are there any pizzas we should take of the menu, or any promotions we could leverage?

I would suggest taking off Pizzas with less that 500 orders through the year.

For the ones the shop received less than 1000 orders, there should be a promotion strategy.