PIZZA SALES

Problem Statement

Pizza is one of the most popular foods worldwide, with millions of pizzas being sold every day. As a result, understanding the pizza industry and its trends can provide valuable insights for businesses and researchers alike. This dataset on pizza sales offers a comprehensive look at pizza sales trends, including information on sales volume, revenue, and customer preferences. The dataset includes data from various pizza restaurants and chains, both large and small, across different regions and time periods.

Start Now

Dashboard Requirement

KPI’s REQUIREMENT

We need to analyze 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: 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 our pizza sales data to gain insights and understand key trends. We have identified the following requirements for creating charts:

Hourly Trend for Total Pizzas Sold: Create a stacked bar chart that displays the hourly trend of total orders over a specific time period.
Weekly Trend for Total Orders: Create a line chart that illustrates the weekly trend of total orders throughout the year.
Percentage of Sales by Pizza Category: Create a pie chart that shows the distribution of sales across different pizza categories.
Percentage of Sales by Pizza Size: Generate a pie chart that represents the percentage of sales attributed to different pizza sizes.
Total Pizzas Sold by Pizza Category: Create a funnel chart that presents the total number of pizzas sold for each pizza category.
Top 5 Best Sellers by Total Revenue, Total Quantity and Total Orders: Create bar charts highlighting the top 5 best-selling pizzas based on the Revenue, Total Quantity, Total Orders.
Bottom 5 Best Sellers by Total 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.

Data Organized

The data source was downloaded from the Kaggle platform and saved on the computer as pizza_sales.csv in the Pizza Sales Report folder.

Data Cleaning & Transformation

Excel was used to clean and transform raw data.

Duplicates were checked with the Remove Duplicates function.
Gaps were checked with the TRIM function.
Pizza sizes represented as S, M, L, X, XL, XXL in the pizza_size column were changed to Small, Medium, Large, XLarge, XXLarge with the Find & Replace function.
Blank and NULL checked for all columns with filter function.

Data Analyzing

Microsoft SQL was used to analyze data.

KPI’s REQUIREMENT

Total Revenue

SELECT

SUM(total_price) AS total_revenue

FROM

dbo.pizza_sales;

Average Order Value

SELECT

SUM(total_price) / COUNT(DISTINCT order_id) AS avg_order_value

FROM

dbo.pizza_sales;

Total Pizzas Sold

SELECT

SUM(quantity) AS total_pizzas_sold

FROM

dbo.pizza_sales;

Total Orders

SELECT

COUNT(DISTINCT order_id) AS total_orders

FROM

dbo.pizza_sales;

Average Pizzas Per Order

SELECT

CAST(CAST(SUM(quantity) AS DECIMAL(10,2)) / CAST(COUNT(DISTINCT order_id) AS DECIMAL(10,2)) AS DECIMAL(10,2))

AS avg_pizzas_per_order

FROM

dbo.pizza_sales;

CHARTS REQUIREMENT

Hourly Trend for Total Pizzas Sold

SELECT

DATEPART(HOUR, order_time) AS order_hour,

SUM(quantity) AS total_pizzas_sold

FROM

dbo.pizza_sales

GROUP BY

DATEPART(HOUR, order_time)

ORDER BY

order_hour;

Daily Trend for Total Orders

SELECT

DATEPART(WEEKDAY, order_date) AS week_day,

DATENAME(DW, order_date) AS order_day,

SUM(quantity) AS total_pizzas_sold

FROM

dbo.pizza_sales

GROUP BY

DATEPART(WEEKDAY, order_date),

DATENAME(DW, order_date)

ORDER BY

DATEPART(WEEKDAY, order_date);

Percentage of Sales by Pizza Category

SELECT

pizza_category,

CAST(SUM(total_price) AS DECIMAL(10,2)) AS total_revenue,

CAST(SUM(total_price) *100 / (SELECT SUM(total_price) FROM pizza_sales) AS DECIMAL(10,2)) AS percentage_of_total_sales

FROM

dbo.pizza_sales

GROUP BY

pizza_category

ORDER BY

percentage_of_total_sales DESC;

Percentage of Sales by Pizza Size

SELECT

pizza_size,

CAST(SUM(total_price) AS DECIMAL(10,2)) AS total_revenue,

CAST(SUM(total_price) *100 / (SELECT SUM(total_price) FROM pizza_sales) AS DECIMAL(10,2)) AS percentage_of_total_sales

FROM

dbo.pizza_sales

GROUP BY

pizza_size

ORDER BY

percentage_of_total_sales DESC;

Total Pizzas Sold by Pizza Category

SELECT

pizza_category,

SUM(quantity) AS total_pizzas_sold

FROM

dbo.pizza_sales

GROUP BY

pizza_category

ORDER BY

total_pizzas_sold DESC;

Total Orders by Pizza Category

SELECT

pizza_category,

COUNT(DISTINCT order_id) AS total_orders

FROM

dbo.pizza_sales

GROUP BY

pizza_category

ORDER BY

total_orders DESC;

Top 5 Best Sellers by Total Revenue

SELECT TOP 5

pizza_name,

CAST(SUM(total_price) AS DECIMAL(10,2)) AS total_revenue

FROM

dbo.pizza_sales

GROUP BY

pizza_name

ORDER BY

total_revenue DESC;

Bottom 5 Best Sellers by Total Revenue

SELECT TOP 5

pizza_name,

CAST(SUM(total_price) AS DECIMAL(10,2)) AS total_revenue

FROM

dbo.pizza_sales

GROUP BY

pizza_name

ORDER BY

total_revenue;

Top 5 Best Sellers by Total Quantity

SELECT TOP 5

pizza_name,

SUM(quantity) AS total_quantity

FROM

dbo.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

dbo.pizza_sales

GROUP BY

pizza_name

ORDER BY

total_quantity;

Top 5 Best Sellers by Total Orders

SELECT TOP 5

pizza_name,

COUNT(DISTINCT order_id) AS total_orders

FROM

dbo.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

dbo.pizza_sales

GROUP BY

pizza_name

ORDER BY

total_orders;

Data Sharing

1. Visualization

Tableau was used to visualize data.

(Please click on the image to access the Live Dashboard)

2. Insights

During daily working hours, we see that sales peak between 12 and 1 in the afternoon and between 16 and 19 in the evening.
The days when pizza is sold the most are Thursday, Friday and Saturday.
In terms of sales percentage in the pizza category, the classic and supreme categories are only slightly ahead of the chicken and veggie categories.
Pizza size is listed as Large, Medium and Small in terms of sales percentage, with XLarge and XXLarge having very low percentages.
In terms of quantity and sales, classic pizza stands out the most on a category basis.
The best selling pizza by revenue is The Thai Chicken Pizza, the worst selling pizza is The Brie Carre Pizza
The best selling pizza by quantity is The Thai Classic Deluxe Pizza, the worst selling pizza is The Brie Carre Pizza
The best selling pizza by sales is The Thai Classic Deluxe Pizza, the worst selling pizza is The Brie Carre Pizza

3. Results

Customers do not prefer much pizza between 9-11 in the morning and 21-23 at night. For this reason, different breakfast options can be added between breakfast hours, and a campaign menu can be prepared between 21-23 in the evening.
Sales can be increased with a campaign on Sundays and Mondays, when pizza sales are lowest.
Supreme and Veggie categories are the pizza categories with the worst sales, and XLarge and XXLarge sizes are the least sold pizza sizes. These categories and sizes can be evaluated in campaigns.

I'M BURAK KARAKAYA, A DATA ANALYST.

I'M HERE TO SHARE THE EXPERIENCES AND SKILLS I'VE GAINED THROUGHOUT MY CAREER.

YOU CAN DOWNLOAD MY CV TO LEARN MORE ABOUT ME AND GET IN TOUCH.
THANK YOU, AND I APPRECIATE YOUR INTEREST!

DOWNLOAD RESUME

PIZZA SALES

Problem Statement

​

Dashboard Requirement

KPI’s REQUIREMENT

CHARTS REQUIREMENT

​

Data Organized

The data source was downloaded from the Kaggle platform and saved on the computer as pizza_sales.csv in the Pizza Sales Report folder.

Data Cleaning & Transformation

Data Analyzing

Microsoft SQL was used to analyze data.

​

KPI’s REQUIREMENT

​

Total Revenue

SELECT

SUM(total_price) AS total_revenue

FROM

dbo.pizza_sales;

​

​

​

​

​

​

​

Average Order Value

SELECT

SUM(total_price) / COUNT(DISTINCT order_id) AS avg_order_value

FROM

dbo.pizza_sales;

​

​

​

​

​

​

​

Total Pizzas Sold

SELECT

SUM(quantity) AS total_pizzas_sold

FROM

dbo.pizza_sales;

​

​

​

​

​

​

Total Orders

SELECT

COUNT(DISTINCT order_id) AS total_orders

FROM

dbo.pizza_sales;

​

​

​

​

​

​

​

Average Pizzas Per Order

SELECT

CAST(CAST(SUM(quantity) AS DECIMAL(10,2)) / CAST(COUNT(DISTINCT order_id) AS DECIMAL(10,2)) AS DECIMAL(10,2))

AS avg_pizzas_per_order

FROM

dbo.pizza_sales;

​

​

​

​

​

​

​

CHARTS REQUIREMENT

​

Hourly Trend for Total Pizzas Sold

SELECT

DATEPART(HOUR, order_time) AS order_hour,