(Power BI and SQL Project: creating an interactive dashboard on Pizza Sales and validation using SQL)
Directly see this static dashboard pdf: Pizza Sales Dashboard - NON-interactive PDF
Note: The above link is not INTERACTIVE, so is not a complete and true showcase of the dashboard. For a full understanding, read this README.
Page 1: Home Page ⤵
Page 2: Best/Worst Sellers ⤵
We need to analyse key indicators for our pizza sales data to gain insights into our business performance. Specifically, we want to calculate the following metrics:
- Total Revenue: The sum of the total price of all pizza orders.
- Average Order Value (AOV): The average amount spent per order, calculated by dividing the total revenue by the total number of orders.
- Total Pizzas Sold: The sum of the quanitites of all pizzas sold.
- Total Orders: The total number of orders placed.
- Average Pizzas Per Order: The average number of pizzas sold per order, calculated by dividing the total number of pizzas sold by the total number of orders.
We would like to visualise various aspects of our pizza sales data to gain insights and understand key trends. We have identified the following requirements for creating charts:
- 📊 Daily Trend for Total Orders: Create a bar chart that displays the daily trend of total orders over a specific time period. This chart will help us identify any patterns or fluctuations in order volumes on a daily basis.
- 📈 Monthly Trend for Total Orders: Create a line chart that illustrates the hourly trend of total orders throughout the day. This chart will allow us to identify peak hours or periods of high order activity.
- 🍩 Percentage of Sales by Pizza Category: Create a pie chart that shows the distribution of sales across different pizza categories. This chart will provide insights into the popularity of various pizza categories and their contribution to overall sales.
- 🍩 Percentage of Sales by Pizza Size: Generate a pie chart that represents the percentage of sales attributed to different pizza sizes. This chart will help us understand customer preferences for pizza sizes and their impact on sales.
- 🌪 Total Pizzas Sold by Pizza Category: Create a funnel chart that presents the total number of pizzas sold for each pizza category. This chart will allow us to compare the sales performance of different pizza categories.
- 📊 Top 5 Best Sellers by Revenue, Total Quantity and Total Orders: Create a bar chart highlighting the top 5 best-selling pizzas based on the Revenue, Total Quantity, Total Orders. This chart will help us identify the most popular pizza options.
- 📊 Bottom 5 Worst Sellers by Revenue, Total Quantity and Total Orders: Create a bar chart showcasing the bottom 5 worst-selling pizzas based on the Revenue, Total Quantity, Total Orders. This chart will enable us to identify underperforming or less popular pizza options.
-
MS Office / Excel: Version 2019 (v2311)
-
MS SQL Server: Version 2022
-
SQL Server Management Studio (SSMS): Version 19.2
-
Power BI: Version 2.123.742.0 64-bit (November, 2023)
48621 rows. CSV. Granularity level: Pizza ID.
- pizza_id: (Primary Key) Unique ID of pizza.
- order_id: Unique ID of order. One order may contain multiple pizzas ordered.
- pizza_name_id: A unique SKU name for Pizza type with description and size, separated by underscores (like, bbq_ckn_l is barbecure chicken pizza of large size)
- quantity: No. of pizzas ordered. Range of values is usually 1 to 7 or 8.
- order_date: Date of order. Format: DD-MM-YYYY
- order_time: Time of order. Format: HH:MM:SS
- unit_price: Price of an individual pizza in dollars. Numerical, float value upto 2 decimal places.
- total_price: Total Price of Pizza in dollars. Numerical, float value upto 2 decimal places. (On first observation, both 7: uni_price = 8: total_price, for all rows. So need to find why 2 duplicate features are needed.)
- pizza_size: Size of Pizza among: S, M and L. (Also present in 3: pizza_name_id)
- pizza_category: Category of Pizza among: Classic, Supreme, Veggie and Chicken.
- pizza_ingredients: Comma-separated List of ingredients of the pizza.
- pizza_name: Name of the Pizza (Consumer-Readable)
On SMSS, we run a number of queries to get the outputs which fulfil the KPI requirements. Here is the document with all the queries with screenshotted outputs: SQL Queries Document
To take a look at the final Dashboard report:
- Download the runnable PowerBI file: Pizza Sales Dashboard - Interactive Runnable and open it on your local Power BI Desktop Application, or;
- Download the PDF file: Pizza Sales Dashboard - non-interactive PDF. This will not be interactive and would simply be a collection of all the dashboard pages.
THE END