Consumer Goods - Ad-Hoc-Insight

This project is done as part of Codebasics Data Analyst Bootcamp

Problem Statement

Atliq Hardwares (imaginary company) is one of the leading computer hardware producers in India and well expanded in other countries too.

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 team by adding several junior data analysts. Tony Sharma, their data analytics director wanted to hire someone who is good at both tech and soft skills. Hence, he decided to conduct a SQL challenge which will help him understand both the skills.

Task

Imagine yourself as the applicant for this role and perform the following task

1. Check ‘ad-hoc-requests.pdf’ - there are 6 ad hoc requests for which the business needs insights.
2. You need to run a SQL query to answer these requests.
3. The target audience of this dashboard is top-level management - hence you need to create a powerpoint presentation to show the insights.
4. Be creative with your presentation.

Action

1. Use MySQL database for storing the records
2. Uses Advanced SQL concepts like Joins, Subquery, CTEs, and Window function to write complex queries to find out insight from the records.
3. For automation use advance stuff like Stored Procedures and Views.
4. Learn domain knowledge: fiscal year, quarters, profit & loss statements.
5. Use Google and Chatgpt as mentor whereever find difficulty.

Major Insights

1. Croma operated only in the Indian market.
2. Croma India sales increase by 180 % in the fiscal year 2021.
3. The percentage of unique product increase in 2021 vs. 2020 is 36.33%.
4. Accessories and Networking segments had the highest (34) and lowest (3) increase in unique products, respectively, in 2021 vs 2020.
5. AQ HOME Allin1 Gen 2 and AQ Master wired x1 Ms had the highest and lowest manufacturing cost, respectively, among all products.
6. The Top 5 customers who received an average high pre-invoice discount percentage for the fiscal year 2021 and in India are, in descending order, Flipkart, Viveks, Ezone, Croma, and Amazon.
7. In FY 2020, Q1 (Sep-Nov 2019) and Q3 had the maximum and minimum sold quantity of products, respectively.
8. For FY 2021, the top 3 products in the N&S, P&A, and PC divisions were pen drives, mouse, and personal laptops, respectively.