This is an end-to-end data analytics project in which a real business problem is solved using SQL (to perform ad hoc requests for which the business needs insights) and Power BI, to develop a dashboard for top-level management for real time monitoring.
Our client is one of the leading computer hardware producers and well expanded in many countries. However, the management noticed that they do not get enough insights to make quick and smart data-informed decisions. They want to expand their data analytics work by adding a data analyst in their team. Their data analytics director wanted to hire someone who can provide insights to ad-hoc requests and business questions and create dashboards and reports for monitoring their business performance.
- SQL
- MySQL Database
- Power BI
- Providing insights for ad-hoc business questions and requests by the client.
- Dashboard for monitoring and tracking sales performance.
- Accessed database and executed SQL queries to get insights for client's ad-hoc requests and questions.
- Connected MySQL database with PowerBI.
- Did data transformation and cleaning with Power Query.
- Loaded the data.
- Created Data Model with Dimensions and fact tables.
- Developed and published dashboard for real-time monitoring and tracking sales performance.
- For SQL queries go to queries folder.
- For Insights Check Linkedin Post
- The total revenue up till now is 2.2 Billion Rupees.
- Average Monthly Sales is around 183 Million Rupees.
- We have shipped approx. 71 Million of product in quantity.
- In fiscal year 2021 we have recorded 210% more sales growth which is 3X as compared to the fiscal year 2020.
- In October 2020, we secured the most gross sales which was around 579M Rupees.
- India and the USA are the biggest markets in terms of sales.
- The retailer Channel is the most successful sales channel with 1.6B Rupees sales recorded.
- Brick & Mortar sales appear to be the dominant channel, accounting for Rs. 1.53 billion, followed by E-Commerce at Rs. 673.8 million. However, E-Commerce shows a portion of (30.62%) compared to Brick & Mortar (72.02%). This could indicate a shift in customer buying habits towards online channels.
- Compared to all quarters in a year, Quarter 4 has gross sales of around Rs. 798M, which indicates that it there is a huge demand of products in this quarter.
- At this moment, we are dealing with 209 customers and have launched 397 of products in market.
- Amazon, Atliq E Store, Atliq Exclusive are out top 3 customers.
- Notebooks are clearly the leader in sales, boasting the highest number of customers, products sold (units), and gross sales.
- Desktop, Networking products and storage products have lower customer acquisition rates, This could indicate a gap in marketing efforts or a need to explore new customer segments for these product categories.
- Mouse, Keyboards and Externel SSDs are the most sold products in terms of quantity. Where has lower gross sales as compared to Keyboards.
- Processors, Business Laptops and Personal Laptops has a decent gross sales all above 270M which indicates that these products are also performing well.
- There is a huge decline in the sales of personal desktops, which is a indicator that world is shifting more towards and laptops due to portability. We may drop this product category in the future.
- We have a average profit margin of 46.73% and have earned Rs. 1.03 Billion till now.
- We are also giving huge amount of discounts to customers which has crossed Rs. 500M in value.
- Notebooks and Accessories are the most profitable product categories.
- In some categories like desktops and networking, we are giving very high discounts which can occur in loss if not reduced.
- There are customers like Notebiling, Circuit City, Nova, Relief etc which are recieving highest discount rates but their gross sales are less.
- From Feb 2021 we are observing a stable sales growth rate and profit.
- Currently monthly sales growth is in a little bit decline of 0.8%.
- Current monthly profit in around Rs. 56 Million.
- The highest growth rate was recorded on April 2020, which was around 270% more sales, this can be due to peak Corona time.
- Most Profitable month was December 2020.