Overview
You are a data analyst for a Pizza company that is looking to gain insights on the past year’s sales performance. Your task is to analyze the following metrics and create a Dashboard that highlights the requirements below. This project was completed using SQL and Power BI.
KPI Requirement
We will conduct an analysis of vital indicators within our pizza sales data to extract valuable insights into our business performance. Our focus is on computing the following key metrics:
- Total Revenue – The sum of the total price of all pizza orders
- Average Order Value – 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 quantities 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.
Charts Requirement
We would like to visualize various aspects of out 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 the 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 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, and Total Orders. This chart will enable us to identify underperforming or less popular pizza options.
Data Cleansing & Transformation (SQL)
To create the necessary data for doing analysis and fulfilling the business needs defined in the kpi and chart requirements, the following metrics were extracted using SQL in Microsoft SQL Server Management Studio.
One data source (pizza sales) was provided in Excel format.
Below are the SQL statements for cleansing and transforming data.
Total Revenue
SELECT SUM(total_price) AS Total_Revenue
FROM pizza_sales;Average Order Value
SELECT max(order_id) AS Total_Orders, SUM(total_price) AS Total_Revenue, SUM(total_price) / max(order_id) AS Average_Order_Value
FROM pizza_sales;Total Pizzas Sold
SELECT SUM(quantity)
FROM pizza_sales;Total Orders (Two methods shown to confirm)
SELECT COUNT(DISTINCT order_id) AS 'distinct', MAX(order_id) AS max
FROM pizza_sales;Average Pizzas Per Order
SELECT CAST(CAST(SUM(quantity) AS DECIMAL(10,2)) / CAST(MAX(order_id) AS DECIMAL(10,2)) AS DECIMAL(10,2)) AS avg_pizzas_ordered
FROM pizza_sales;Daily Trend for Total Orders
SELECT DATENAME(weekday, order_date) as order_day, COUNT(DISTINCT order_id) AS total_orders
FROM pizza_sales
GROUP BY DATENAME(weekday, order_date)
ORDER BY total_orders DESC;Monthly Trend for Total Orders
SELECT DATENAME(month, order_date) as order_day, COUNT(DISTINCT order_id) AS total_orders
FROM pizza_sales
GROUP BY DATENAME(month, order_date)
ORDER BY total_orders DESC;Percentage of Sales by Pizza Category
SELECT pizza_category, SUM(total_price) AS total_sales, Sum(total_price) * 100 / (SELECT sum(total_price) FROM pizza_sales WHERE MONTH(order_date) = 1) AS 'percent'
FROM pizza_sales
WHERE MONTH(order_date) = 1
GROUP BY pizza_category;Percentage of Sales by Pizza Size
SELECT pizza_size, SUM(total_price) AS total_sales, SUM(total_price) * 100 / (SELECT SUM(total_price) FROM pizza_sales) AS 'percent'
FROM pizza_sales
GROUP BY pizza_size
ORDER BY 'percent' DESC;Total Pizzas Sold by Pizza Category
SELECT pizza_category, COUNT(quantity) AS pizzas_sold
FROM pizza_sales
GROUP BY pizza_category;Top 5 Best Sellers by Revenue
SELECT TOP 5 pizza_name, sum(total_price) AS revenue
FROM pizza_sales
GROUP BY pizza_name
ORDER BY revenue DESC;Bottom 5 Best Sellers by Revenue
SELECT TOP 5 pizza_name, sum(total_price) AS revenue
FROM pizza_sales
GROUP BY pizza_name
ORDER BY revenue ASC;Top 5 Best Sellers by Total Quantity
SELECT TOP 5 pizza_name, sum(quantity) AS total_quantity
FROM pizza_sales
GROUP BY pizza_name
ORDER BY total_quantity DESC;Bottom 5 Best Sellers by Total Quantity
SELECT TOP 5 pizza_name, sum(quantity) AS total_quantity
FROM pizza_sales
GROUP BY pizza_name
ORDER BY total_quantity ASC;Top 5 Best Sellers by Total Orders
SELECT TOP 5 pizza_name, COUNT(DISTINCT order_id) AS total_orders
FROM pizza_sales
GROUP BY pizza_name
ORDER BY total_orders DESC;Bottom 5 Best Sellers by Total Orders
SELECT TOP 5 pizza_name, COUNT(DISTINCT order_id) AS total_orders
FROM pizza_sales
GROUP BY pizza_name
ORDER BY total_orders DESC;Dashboard Creation (Power BI)
The Pizza Sales Dashboard is now fully operational, featuring an overview home page and one supplementary section designed to examine best and worst seller trends by revenue, quantity and total orders.
Joshua Lamothe 