Analise das vendas de uma pizzaria

Feita no Excel e SQL

Sumário

• Análise com o objetivo de saber os sabores, tamanhos e dias que mais vendem.

Metas

• Aumentar o faturamento e conseguir vender em dias de baixa procura.

Dataset

• O dataset contém as seguintes informações:
  • 11 colunas e 49.621 linhas

Metodologia

• Fiz algumas queries no SQL para obter informações relevantes e precisas, depois utilizei o Excel para a criação de KPIs e dashboard.

SQL

• Visão geral das queries

Queries detalhadas

Valor total das vendas

• select SUM(total_price) AS Total_Revenue FROM pizza_sales

Média do valor dos pedidos

• select SUM(total_price) / COUNT(distinct order_id) as Avg_Order_Value from pizza_sales

Quantidade de total pizzas vendidas

• select SUM(quantity) as Total_pizza_sold from pizza_sales

Quantidade de pedidos

• select COUNT(DISTINCT order_id) as Total_Orders from pizza_sales

Média de pizzas por pedido

• select cast(CAST(sum(quantity) as decimal(10,2)) / CAST(count(distinct order_id) as decimal(10,2)) as decimal(10,2)) AS Avg_Pizza_Per_Order from pizza_sales

Pedidos diários

• select DATENAME(DW, order_date) as order_day, COUNT(distinct order_id) as total_orders from pizza_sales group by datename(DW, order_date)

Horários dos pedidos

• select DATEPART(hour, order_time) as order_hours, COUNT(distinct order_id) as Total_Orders from pizza_sales group by DATEPART(hour, order_time) order by datepart(hour, order_time)

% de vendas das categorias

• select pizza_category, cast(sum(total_price) AS DECIMAL(10,2)) as Total_Sales, CAST(sum(total_price) * 100 / (select sum(total_price) from pizza_sales) AS decimal(10,2)) as PCT from pizza_sales group by pizza_category

% de vendas dos tamanhos das pizzas

• select pizza_size, CAST(SUM(total_price) AS DECIMAL(10,2)) as Total_Sales, CAST(SUM(total_price) * 100 / (SELECT SUM(total_price) from pizza_sales) AS DECIMAL(10,2)) AS PCT from pizza_sales group by pizza_size order by pizza_size

Quantidade de pizza vendidas por categoria

• select pizza_category, SUM(quantity) as Total_Quantity_Sold from pizza_sales where MONTH(order_date) = 2 group by pizza_category order by Total_Quantity_Sold DESC

As 5 pizzas mais vendidas

• select top 5 pizza_name, SUM(quantity) as Total_Pizza_Sold from pizza_sales group by pizza_name order by Total_Pizza_Sold DESC

As 5 pizzas menos vendidas

-select top 5 pizza_name, Sum(quantity) as Total_Pizza_Sold from pizza_sales group by pizza_name order by Total_Pizza_Sold ASC

Excel

No Excel eu criei as KPI's e tabelas dinâmicas para maior fluidez na vizualição

E assim ficou o dashboard!