Project-Sales Insight of AtliQ Hardware
Sales insights Power BI Dashboard
What you will attain from this project?
This project will provide hands-on learning in dealing with real-world data scenarios and making data-driven decisions for business growth. You will gain expertise in data exploration using MySQL, data cleaning, and the ETL process. Additionally, you will build a final dashboard on Power BI to visualize and analyze the insights derived from the data. Overall, this project equips you with valuable skills in data management and analysis to drive informed decision-making.
Introduction
AtliQ Hardware is a company which, that supplies computer hardware and peripherals to many clients like Surge Stores, Nomad Stores, Excel Stores, and Electronicalsara Stores across India. AtliQ Hardware’s head office is situated in Delhi, and they have many regional offices throughout India.
Problem Statement
The market is growing dynamically and the Sales Director is facing issues in terms of tracking the sales in this dynamically growing market and he’s having issues with the growth of his business, as overall sales were declining. He has regional managers for North India, South, and Central India. Whenever he wants to get insight into these regions he would call these people and on the phone regional manager give some insights to him.
Now Problem was that all these things happening is verbally and there was no proof with facts that how his business is affected and which made him frustrated as he can see that overall sales is declining but when he asked the regional manager, he is not getting the complete picture of his business. And when he asks to give detail, they give a lot of Excel files and this AtliQ hardware is big business so to see insight clearly, he needs a dashboard to look at real data. And he will get proper insight and can take data-driven decisions to increase sales of his company.
Solution Approach
To overcome the challenges and provide the Sales Director with actionable insights, the following approach was adopted:
- Data Gathering: The existing sales data from various sources, including Excel files and MySQL databases, was collected and consolidated for analysis.
- Data Exploration: The collected data was thoroughly examined to understand its structure, identify key variables, and uncover any data quality issues or anomalies.
- Data Transformation and Modeling: Data was preprocessed and transformed to create a clean and unified dataset. Relevant measures and dimensions were defined based on the business requirements. The data was then modeled to facilitate analysis and visualization.
- Dashboard Development: Power BI, a powerful data visualization tool, was used to develop an interactive and visually appealing sales analysis dashboard. The dashboard consists of various charts, tables, and key performance indicators (KPIs) to provide a comprehensive view of sales performance across regions.
- Insights and Recommendations: Through the analysis of the sales data and exploration of various measures, valuable insights were gained regarding the factors contributing to declining sales. These insights were used to provide actionable recommendations to the Sales Director, enabling data-driven decision-making for business growth.
Technologies Used
MySQL: Used to store and retrieve sales data from the company’s databases. Power BI: Utilized for data exploration, modeling, and dashboard development to provide interactive visualizations and insights.
Data Exploration using My-SQL:
You can access the data from here- Data
After downloading data into your local system, now import data into MySQL-
Visit MySQL →Servers →Data Import
Let’s explore the Data using the query-
# All Customers Records
select * from customers; # All Markets Regions
select * from markets; # we have paris and Newyork so we need to remove it
Observation- We are doing Sales insights in India and also might have done in New York and Paris 1–2 times, so here we will remove it because right now the company is doing business only in India so these data are not useful.
# All Transactions
select * from transactions; # We can see sale amount in negative
Observation: As we can see the negative values in the sales_amount also it contain zero where the sales made it is not acceptable by the company, as well as some of the transaction, were made in USD not in Indian ₹, so we need to convert it from $ to ₹.
# we have currency in USD and USD\r but this data is about
#Indian Market so Lets convert the currency and negative values including zero
select *,
if(sales_amount <='0',
(select avg(sales_amount) from transactions),
if (currency='USD' or currency = 'USD\r',
sales_amount*65,
sales_amount
)
) as adjusted_amount
from transactions;
/* Method2 */
select *,
case
when sales_amount<=0 then (select avg(sales_amount) from transactions)
when currency = 'USD' or currency ='USD\r'
then sales_amount * 65
else sales_amount
end
from transactions
where sales_amount > 0;
Observation: In this Query, where ever we have zero or less than it, we consider there the average sales amount, and we converted the $ to ₹.
To see more query which can be helpful to understand your data- SQL-Query
Data Cleaning and ETL
Now we will pull data into Power BI and also do Data Cleaning known as ETL(Extract Transform and Load).
We need to transform data as it is messy and need to convert it into different formats so that we can perform Data Analysis in Power BI.
Get data →More Options →Databases →MySQL database
→Now Enter your server and database name and connect it with your database.
→Load all the data
→Now, Transform Data which will launch into Power Query Editor where we will do Data Cleaning .
In sales.market table, we need to remove New York and Paris, and for that we will remove the blank from zone column and that will filter out .
We created new measures that are Revenue and Sales Quantity in the power Bi
Go to Table Tools → New measure → Revenue and Sales Quantity
Creating a dashboard of sales insight:
Finally ,You can access to Dashboard and to check other files click on — Git hub Link .