Sales insights project using Powerbi and SQL for AtliQ hardware, its and india based hardware company, in this project the insights are derived from the Data using AIMS Grid features.
This learning based project picked from code basics youtube channel. you can find link is as below.
AtliQ Hardware, a company specializing in computer hardware and peripherals, operates multiple branches across India. The sales director is encountering challenges in assessing the company's performance and identifying existing issues. Sales have been below expectations and are steadily declining. When the sales director reaches out to regional managers for updates on sales and market conditions, there is a reluctance to engage with detailed numerical data in Excel files, leading to frustration and hindering effective decision-making.
Sales director of the AltiQ hardware, decided to build a PowerBI Dashboard for converting the data into visual representation to make data driven decisions. So, he hired a team of data people to complete this task.
By using the AIMS grid project management tool, we made sure what are the purpose, stakeholder, end result and success criteria of our project.
- Learned about AIMS grid for project planning.
- Used MySQL for retrieving the data from the database into Power BI.
- Data Cleaning in power query.
- Performed ETL process (Extract Transform and Load)
- Created measure for needs and used them for creating visuals in PowerBi.
- In the currency there were two types of currencies in transactions, performed currency conversion to make all the currency type same
- Data Validation
- Data Modelling and Visualization.
- Solved the ‘(blank)’ problem for the products section by deleting the original products table and adding the self-modified products table (where I have added the Products ranging from Prod280 to Prod339 with their product type (random type- b/w ‘Own Brand’ and ‘Distribution’).
- Merged the original modified ‘sales_transaction’ table with the new ‘sales_transaction’ table having profit margin, cost price, etc.
- In this dashboard, we can see company has generated total revenue in 4 years ₹ 985M, total profit margin ₹24.7M, Profit margin% 2.5%, Sales Qty ₹2M. In 2020 company has generated total revenue of ₹ 142M by selling a total of 350K and earned a profit of ₹2.1M.
- In 4 years Delhi NCR is our largest market in terms of revenue with ₹ 520M and total contribution of 52.8% with total revenue but if you look at the profit margin Delhi NCR is generating only 2.3% profit margin.
- If we check the profit margin then here In 2020 Bhubaneshwar comes into the picture which is generating the highest profit margin of 10.48%. Similarly, if we can check the Profit Contribution % by Market then here Mumbai is the largest player with 23.89% of total contribution in total profit.
- In 4 years Bengaluru generating the lowest profit margin of -20.8%.if we can check the Profit Contribution % by Market then here also Bengaluru is the Lower with -0.3% of total contribution in total profit.
- In our top 5 customers, the Electricalsara Stores is our biggest customer who has generated total ₹413 M revenue generated in 4 years.
- In our top 5 products,the Prod318 is our highest product has generated total ₹69M revenue generated in 4 years.
- In product type Distribution has generated the revenue of ₹494M and ownbrand revenue is ₹494M generated in entire 4 years.
- Revenue Trend is showing that in June 2020 revenue has been decreased drastically compared to the revenue last year and the profit margin was the least in April 2020.
- Learned about what real business data sets look like.
- Learned about how to write some major analysis queries in MySQL.
- how to connect the database’s tables to Power Bi and how to clean & modify the unwanted data in Power Query.
- Learned about some major practical DAX functions and measures.
- Learned about major analytical visuals and reports in PowerBI.
