🚀 Alura-Challenge-BI--2nd-Edition 💻
Alura is a coding school from Brazil that offers courses and challenges occasionally. This challenge has 4 weeks of duration and is about Business Inteligence, using mainly Power BI.
🎬 1st Week - Alura films
In this case there is the IMDB dataset which contains more than 1000 movies, the main goal is to explore the data and it visualization to deliver information that helps the decision making of Alura films.
🔗 Dataset
The dataset is available on the following link:
https://drive.google.com/drive/folders/1fBIECPox4nXVeuIfD5y8k7Vvdy1Omzj8
🧹 Data cleaning
- Removal of null values from movie titles
- Change the text format to number format for columns that are numeric, eg: Gross
📊 Dashboard and Data analysis
From the dataset there is some analysis as follow:
Metrics:
- Gross
- IMDB Rating
- Number of votes
Charts:
- Number of titles by main star
- Gross percentage by genre
A table showing Gross and director, and also a search by movie.
This is the dashboard of Alura films, It´s also available in the following link:
And the mobile version were created as well:
✔️ Conclusions
There are relevant conclusions in this dashboard:
- Top 3 gross by genre are:
- Action
- Drama
- Animation
The action gross is bigger than the sum of drama and action.
-
Top 3 Main Stars according to the number of titles:
- Tom Hanks
- Robert De Niro
- Al Pacino
-
Top 3 Directors:
- Steven Spielberg
- Anthony Russo
- Christopher Nolan
🍽️ 2nd Week - Alura food
In this case there is the dataset from Zomato that is an Indian multinational restaurant aggregator and food delivery company. The goal is analyse the Indian food market to deliver information that helps the decision making of Alura food in India.
🔗 Dataset
The dataset is made by json files from Zomato API, available on the following link:
https://drive.google.com/drive/folders/1v_Y7TBObGEEtj4C9ku4GvmlX0x9TTZd-
🧹 Data cleaning
- Removal of restaurant id duplicated
- Removal of restaurant id null values
- Change the text format to number format for columns that are numeric
📊 Dashboard and Data analysis
The dataset is avaliable in json files, so the first step is open the json files and open the sublists. After that, there are the following analysis:
- Replace Goa that is a state of India by Pangim that is its capital - It's used to the map location due to Goa is a city in other country
- Filter only India as country
This is the dashboard of Alura food, It´s also available in the following link:
And the mobile version were created as well:
✔️ Conclusions
There are relevant conclusions in this dashboard:
- Top 3 cusines:
- North Indian
- Chinese
- Fast Food
The number of restaurants with North Indian cusine is bigger than the sum of Chinese an Fast Food.
- Top 3 cities:
- New Delhi
- Gurgaon
- Noida
New Delhi has 5 times more restaurants than the second city Gurgaon
🍦 3rd Week - Alura Skimo
In this case there is the dataset from Alura skimo, that is an ice cream company. The goal is analyse the skimo sales database to deliver information that helps the decision making of Alura skimo.
🔗 Dataset
The dataset is a dump of sql files, available on this link
https://drive.google.com/drive/folders/106j-3sbhHp5CiWNxQWZDydKKoRgPc70z
The first step is create a database with the following code:
CREATE DATABASE iii_semana
After that, using MySQL and restoring a database:
Finally, the database is on schemas:
The last step is load the database on PowerBI.
🧹 Data cleaning
- Change the text format to number format for columns that are numeric
- Removal of id produto (product id) number 239, because there isn't values of preço (price), custo (cost) and categoria (category), probably it is a mistake
📊 Data analysis and Dashboard
There are some columns that had to be imported to make some measures:
The price product is imported from the 'iii_semana produtos'[PREÇO] to 'iii_semana produtos'[Price product] using the following formula:
Price product = LOOKUPVALUE( 'iii_semana produtos'[PREÇO], 'iii_semana produtos'[ID Produto], 'iii_semana itens_pedido'[ID Produto])
Other columns are created in iii_semana itens_pedido table :
Partial revenue = 'iii_semana itens_pedido'[Price product] * 'iii_semana itens_pedido'[Quantidade_Vendida]
Cost product = LOOKUPVALUE('iii_semana produtos'[CUSTO DO PRODUTO],'iii_semana produtos'[ID Produto], 'iii_semana itens_pedido'[ID Produto])
Partial cost = 'iii_semana itens_pedido'[Cost product] * 'iii_semana itens_pedido'[Quantidade_Vendida]
Partial profit = 'iii_semana itens_pedido'[Partial revenue] -'iii_semana itens_pedido'[Partial cost]
Category name = LOOKUPVALUE('iii_semana produtos'[Category name],'iii_semana produtos'[ID Produto], 'iii_semana itens_pedido'[ID Produto])
Measures are created for the following metrics:
- Average Ticket Price
- Amount of sales
- Revenue
- Cost
- Profit
And charts to:
- Revenue by category
- Revenue by sales person
- Partial profit, revenue and cost by trimester
The dashboard is avaiable on the following link:
And the mobile version were created as well:
✔️ Conclusions
- Top 3 best seller categories:
- Milk ice cream
- Fuit ice cream
- Fuit popsicle
Milk ice cream sells more than a half of the total categories.
- Top 3 revenue by salesperson:
- Expedita Joaquina
- Bruno Gadelha
- Lírio Gonçalves
Each one has almost the same sales amount.
- Seasonal component:
- The profit, cost and revenue are bigger during the first trimester, and almost the same in the other 3 trimesters.
📌 4th Week - Improvements and latest revision
- Improvement of all weeks
- Insertion of dashboards mobile version
- Latest revision
📛 Bagde
