A análise é de vendas de quase 5mil ordens, realizadas entre 2015 e 2018. O objetivo principal é análisar as principais características das vendas, e segmentar os dados para obter Insights.
Análisar dados de vendas de uma loja online. Os dados são de quase 5mil vendas realizadas entre 2015 e 2018.
Os dados utilizados nesta análise, estavam condensado em uma único Dataset, e foram primeiramente tratados e limpos no Excel.Para conseguir trabalhar com os dados posteriormente no Power BI, foram construidas a partir do Dataset único, 5 Tabelas.
- Excel
- -MySQL/ SQL
- Porwer BI
- Orders(Tabela Fato)
- Clientes
- Localização
- Produtos
- Categorias
- Fretes
A Tabela Fato, contém dados de cada ordem, como ID da Ordem, ID Produto, ID Cliente, ID Frete, Data da compra, Data de Entrega, Valor da Compra, Tempo Entrega.
As 4 Tabelas Dimensões contém: Clientes(ID Cliente; Segmento), Localização( ID Ordem; Cidade; Estado), Produtos(ID Produto; Categoria; Sub-Categoria), Frete(ID Frete; Tipo de Frete).
![](https://private-user-images.githubusercontent.com/77849519/242672211-ea4a2897-a542-475c-9b57-1a9e7d6303e5.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MTkwMTYxNTgsIm5iZiI6MTcxOTAxNTg1OCwicGF0aCI6Ii83Nzg0OTUxOS8yNDI2NzIyMTEtZWE0YTI4OTctYTU0Mi00NzVjLTliNTctMWE5ZTdkNjMwM2U1LnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA2MjIlMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwNjIyVDAwMjQxOFomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPTIzNDhmZDlmMTViM2E2NGYwMzg0YjYyZWFjN2M4MzhiMDdjMWUwZmUwZDA1ZmU5ZGM1MzhiYTcwM2M3NzU4NGUmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.76cxCAgGoRMWsCNSBg19ai5BQFoZOYl-agjV5J1OLdE)
Para chegar ao resultado final foram elaboradas perguntas para guiar a Análise:
1 - Como é a tendência das vendas ao longo do tempo?
2 - Quias as Categorias e Sub-Categorias que mais venderam ?
3 - Quais Estados mais contribuiram para as vendas?
4 - Como está a entrega das mercadorias? A maioria está dentro do prazo médio?
5 - Quais estados tem o maior tempo de Entrega?
6 - Existe sasonalidade nas vendas?
- Upload de todas as tabelas(em formato csv.) para podermos analisar e correlacionar os dados.
load data local infile "C:\\Users\\bruno\\Documents\\Sales_datasets\\Orders.csv"
into table orders_dataset
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
ignore 1 rows;
- Numero de Orders
SELECT
COUNT(distinct OrderID)
FROM orders;
4922
- Valor Médio de Compra
SELECT
ROUND(AVG(Sales),2)
FROM orders;
230.77
- Compra de Maior Valor
SELECT
MAX(Sales)
FROM orders;
22,638.48
- Compra de Menos Valor
SELECT
MIN(Sales)
FROM orders;
0.45
- Desvio Padrão dos valores de compra
SELECT
ROUND(stddev(Sales),2)
FROM orders;
626.62
- Total de Clientes
SELECT
COUNT(distinct CustomerID)
FROM customers;
793
- Total de Clientes
SELECT
COUNT(DISTINCT ProductID)
FROM products;
1861
- Valor Médio de Vendas por Categoria
SELECT
p.Category,
AVG(Sales)
FROM orders o
JOIN products p ON o.ProductID = p.ProductID
GROUP BY p.Category;
Furniture 350.65
Office Supplies 119.38
Technology 456.40
1 - Apesar de ser só 18% das vendas totais,Tecnologia é responsavél por 36%($827mil) da Receita Total, tendo também um Valor Médio de Venda de $456, maior que a Média geral de $230.
2 - O Q4 foi o melhor período de vendas em todos os anos, que pode ser atribuido a ele ter datas fortes para o comércio como Natal e Black Friday.
3 - Os estados que mais trouxeram receitas foram a California e Nova York.
4 - O estado da Flórida se mostrou muito forte nas vendas do segmento de Home Office, principalmente no ano de 2015(Top 1).
Com o Power BI, e linguagem DAX utilizaremos gráficos interativos para melhor analisar e visualizar os dados, e retirar os Insights necessários para responder as perguntas.
![](https://private-user-images.githubusercontent.com/77849519/242670085-59f976da-4d93-4487-8180-70e291bec25c.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MTkwMTYxNTgsIm5iZiI6MTcxOTAxNTg1OCwicGF0aCI6Ii83Nzg0OTUxOS8yNDI2NzAwODUtNTlmOTc2ZGEtNGQ5My00NDg3LTgxODAtNzBlMjkxYmVjMjVjLnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA2MjIlMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwNjIyVDAwMjQxOFomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPWI0ZDg5N2ZkNGRlMTJhM2NjNDI0ZWMyZTQ5NWFjMWJiMzExYTRiZGE4NTQwNGNlZjViOWE0NDgwNTk4NTJjNTEmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.L4VRm2P_00oeBmhwsQZcmwmQLhhTsN5wLZRM3YZ4AnI)
![](https://private-user-images.githubusercontent.com/77849519/242670747-e96643cd-c2ad-4972-a2bf-ebe536ed0d1b.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MTkwMTYxNTgsIm5iZiI6MTcxOTAxNTg1OCwicGF0aCI6Ii83Nzg0OTUxOS8yNDI2NzA3NDctZTk2NjQzY2QtYzJhZC00OTcyLWEyYmYtZWJlNTM2ZWQwZDFiLnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA2MjIlMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwNjIyVDAwMjQxOFomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPTYyMDUyYjY4N2RjYTFjZDhhYmJkODZiYTQ5Yzk5Y2Q2NjFiOWE3YzQ0ODAyYjUyNDliMWU0YzkyNTFkODZkZGUmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.25PNabgLt36jme--e6O5ectgPGmbbf35bQxL4_noJxY)
![](https://private-user-images.githubusercontent.com/77849519/242671025-6f003718-bf5b-4361-92db-3106b6f7f299.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MTkwMTYxNTgsIm5iZiI6MTcxOTAxNTg1OCwicGF0aCI6Ii83Nzg0OTUxOS8yNDI2NzEwMjUtNmYwMDM3MTgtYmY1Yi00MzYxLTkyZGItMzEwNmI2ZjdmMjk5LnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA2MjIlMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwNjIyVDAwMjQxOFomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPTlkOGFjMGRiOWRiYjJlMjEzZTJkODlmOTU0NjRiNTBmN2Q5YjkxYTQ1MzEyZTQ1YTU2NzMwMTdjNDczNTMwZDMmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.WIOB6KszBJVF02_hLTAXKJkgszQn6O0nLkN5ukmJOSU)
![](https://private-user-images.githubusercontent.com/77849519/242671058-0b3c3198-e69e-48c0-98b5-8a29dc05c2df.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MTkwMTYxNTgsIm5iZiI6MTcxOTAxNTg1OCwicGF0aCI6Ii83Nzg0OTUxOS8yNDI2NzEwNTgtMGIzYzMxOTgtZTY5ZS00OGMwLTk4YjUtOGEyOWRjMDVjMmRmLnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA2MjIlMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwNjIyVDAwMjQxOFomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPWIxOGEwZGMwNWRhOWQ0ZWU2YjgwNTU3ZWJjMTAwY2IzNjE2NjZmMTE5OGFlZTc2NGZhZWJjYjlhNDZhMmRhNTkmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.PI3UOB_makAKi5FkxpvnEtyNjTzXDtlOlyZ5j8fyCE4)
Você pode acessar o Dashboard clicando aqui Análise Sales Performance.
![](https://private-user-images.githubusercontent.com/77849519/242674909-4cbdf1a9-e3e7-454d-abaf-4fd3c920c6f0.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MTkwMTYxNTgsIm5iZiI6MTcxOTAxNTg1OCwicGF0aCI6Ii83Nzg0OTUxOS8yNDI2NzQ5MDktNGNiZGYxYTktZTNlNy00NTRkLWFiYWYtNGZkM2M5MjBjNmYwLnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA2MjIlMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwNjIyVDAwMjQxOFomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPTExMjI5ZWI4ZjgxMjZkNTdlYjQ5MjdjMGZjNmM0ZGJhMWI3ODYxYjhkNjQyNTJlODk1MGVhODBmNjM1ODU1MjImWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.UuEaYM2wTnJwTcTsGi1OMQ8talqj6MRqlfAFXd5V_LI)
![](https://private-user-images.githubusercontent.com/77849519/242674926-68f72405-35bd-451f-af22-6c47786732ac.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MTkwMTYxNTgsIm5iZiI6MTcxOTAxNTg1OCwicGF0aCI6Ii83Nzg0OTUxOS8yNDI2NzQ5MjYtNjhmNzI0MDUtMzViZC00NTFmLWFmMjItNmM0Nzc4NjczMmFjLnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA2MjIlMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwNjIyVDAwMjQxOFomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPWMzMWY3NmE4NDVhMjkwZTEyYzM4MWQzOTg5M2ZhNjMyNTVlYzMxZjIxMzQyMWRkMjgyNDNjNDUwY2EyMTYzMjAmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.NC8FRtj679HoaPIgHG9ImcLNPn00j3CYeNKEBsqeelQ)
![](https://private-user-images.githubusercontent.com/77849519/242675525-3cbafedb-0dd7-4112-8dc2-4f8fc058f5f1.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MTkwMTYxNTgsIm5iZiI6MTcxOTAxNTg1OCwicGF0aCI6Ii83Nzg0OTUxOS8yNDI2NzU1MjUtM2NiYWZlZGItMGRkNy00MTEyLThkYzItNGY4ZmMwNThmNWYxLnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA2MjIlMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwNjIyVDAwMjQxOFomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPTA0YTYwYjBhZDFjMzVkYTZmZjkyYmE0YTNkYjBhOGVkM2QwNTVkMTJhNDYzOGI5NDExMGE2ZDM1MzZlMjRiMDkmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.dZ5LgxPo4wp-wcLJpdwWFdliqBgwCXqcUr8C9xGkIvA)
![](https://private-user-images.githubusercontent.com/77849519/242676797-7ec0ff84-fc90-4d77-9847-fcfde483e5d1.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MTkwMTYxNTgsIm5iZiI6MTcxOTAxNTg1OCwicGF0aCI6Ii83Nzg0OTUxOS8yNDI2NzY3OTctN2VjMGZmODQtZmM5MC00ZDc3LTk4NDctZmNmZGU0ODNlNWQxLnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA2MjIlMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwNjIyVDAwMjQxOFomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPTcwOGM0MDgzZWY2OTJlYTUyZGJiNjQ0MjZjYmI0YjVjODQ1ZjQ2OGM4ZDEzZGIyODM5M2U4OTMzMjNhNjdlOTkmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.WBEhTmLpzWtNFSKz9tKEr92s4YY0SFGE4ooRU2KPRWk)
![](https://private-user-images.githubusercontent.com/77849519/242677652-a9d512c6-2c39-4188-8bff-3153d4ac0362.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MTkwMTYxNTgsIm5iZiI6MTcxOTAxNTg1OCwicGF0aCI6Ii83Nzg0OTUxOS8yNDI2Nzc2NTItYTlkNTEyYzYtMmMzOS00MTg4LThiZmYtMzE1M2Q0YWMwMzYyLnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA2MjIlMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwNjIyVDAwMjQxOFomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPWQzMGJhZjJkYWM2Y2ExZjkwZDdlODQwNmQ3ZmUxZjExOThlNDNiYWEyOGU1YjdjMDMyMWY0OGFmNDAzNDY2YzImWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.rfsEVNdR124wYc6OH--2NFvmWUzjxN3WJg5LWAeaRJ8)