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.
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])))There is a One to Many relationship between;
order_detailsandpizzasusingpizza_idorder_detailsandordersusingorder_idpizzasandpizza_typesusingpizza_type_id--Revenueandordersusingdate
- Are there any peak hours?
- How many pizzas are typically in an order? Do we have any bestsellers?
- How much money did we make this year? Can we indentify any seasonality in the sales?
- Do we have any bestsellers?
- Are there any pizzas we should take of the menu, or any promotions we could leverage?
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.