CONSUMER GOODS
AD-HOC INSIGHTS
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.
Dashboard Requirement
Task:
Imagine yourself as the applicant for this role and perform the following task
1. Check ‘ad-hoc-requests.pdf’ - there are 10 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 presentation to show the insights.
4. Be creative with your presentation, audio/video presentation will have more weightage.
​
Query Requests:
1. Provide the list of markets in which customer "Atliq Exclusive" operates its business in the APAC region.
2. What is the percentage of unique product increase in 2021 vs. 2020?
3. Provide a report with all the unique product counts for each segment and sort them in descending order of product counts.
4. Which segment had the most increase in unique products in 2021 vs 2020?
5. Get the products that have the highest and lowest manufacturing costs.
6. Generate a report which contains the top 5 customers who received an average high pre_invoice_discount_pct for the fiscal year 2021 and in the Indian market.
7. Get the complete report of the Gross sales amount for the customer “Atliq Exclusive” for each month.
8. In which quarter of 2020, got the maximum total_sold_quantity?
9. Which channel helped to bring more gross sales in the fiscal year 2021 and the percentage of contribution?
10. Get the Top 3 products in each division that have a high total_sold_quantity in the fiscal_year 2021?
Data Organized
This dataset was downloaded from codebasics.io website and stored in Consumer Goods Domain folder on PC.
It contains 1 data file: atliq_hardware_db.sql.
This file provides a comprehensive overview of the tables found in the 'gdb023' (atliq_hardware_db) database. It includes information for six main tables:
1. dim_customer: contains customer-related data
2. dim_product: contains product-related data
3. fact_gross_price: contains gross price information for each product
4. fact_manufacturing_cost: contains the cost incurred in the production of each product
5. fact_pre_invoice_deductions: contains pre-invoice deductions information for each product
6. fact_sales_monthly: contains monthly sales data for each product.
Data Cleaning & Transformation
Microsoft Power Query and Power BI will be used
​
-
Duplicates were checked with remove duplicates function
-
Gaps were checked with TRIM function
-
Blank and NULL checked with filter function
Data Analyzing
Measures
MySQL was used for analysis
​
-- 1. Provide the list of markets in which customer "Atliq Exclusive" operates its business in the APAC region.
SELECT DISTINCT market
FROM gdb023.dim_customer
WHERE customer = "Atliq Exclusive" AND region = "APAC" ;
​
​
​
​
​
​
​
​
​
​
​
​
​
​
-- 2. What is the percentage of unique product increase in 2021 vs. 2020?
WITH cte AS
(SELECT COUNT(DISTINCT product_code) AS unique_product_2020
FROM gdb023.fact_sales_monthly
WHERE fiscal_year = "2020"),
cte1 AS
(SELECT COUNT(DISTINCT product_code) AS unique_product_2021
FROM gdb023.fact_sales_monthly
WHERE fiscal_year = "2021")
SELECT *, ROUND(((unique_product_2021 - unique_product_2020)/unique_product_2020)*100,2)AS percentage_chg
FROM cte CROSS JOIN cte1;
​
​
​
​
​
​
​
-- 3. Provide a report with all the unique product counts
-- for each segment and sort them in descending order of product counts.
SELECT segment, COUNT(DISTINCT product_code) AS product_counts
FROM gdb023.dim_product
GROUP BY segment
ORDER BY product_counts DESC;
​
​
​
​
​
​
​
​
​
​
-- 4. Which segment had the most increase in unique products in 2021 vs 2020?
WITH cte AS
(SELECT dp.segment, COUNT(DISTINCT fsm.product_code) AS unique_product_2020
FROM gdb023.fact_sales_monthly AS fsm
JOIN gdb023.dim_product AS dp
ON fsm.product_code = dp.product_code
WHERE fiscal_year = "2020"
GROUP BY dp.segment
ORDER BY unique_product_2020 DESC),
cte1 AS
(SELECT dp.segment, COUNT(DISTINCT fsm.product_code) AS unique_product_2021
FROM gdb023.fact_sales_monthly AS fsm
JOIN gdb023.dim_product AS dp
ON fsm.product_code = dp.product_code
WHERE fiscal_year = "2021"
GROUP BY dp.segment
ORDER BY unique_product_2021 DESC)
SELECT cte.segment, unique_product_2020, unique_product_2021,
(unique_product_2021 - unique_product_2020) AS difference
FROM cte JOIN cte1 ON cte.segment = cte1.segment
ORDER BY difference DESC;
​
​
​
​
​
​
​
​
​
​
-- 5. Get the products that have the highest and lowest manufacturing costs.
SELECT dp.product_code, dp.product, fmc.manufacturing_cost
FROM gdb023.dim_product AS dp
JOIN gdb023.fact_manufacturing_cost AS fmc
ON dp.product_code = fmc.product_code
WHERE fmc.manufacturing_cost = (SELECT MAX(manufacturing_cost) FROM gdb023.fact_manufacturing_cost)
OR fmc.manufacturing_cost = (SELECT MIN(manufacturing_cost) FROM gdb023.fact_manufacturing_cost)
ORDER BY manufacturing_cost DESC;
​
​
​
​
​
​
​
-- 6. Generate a report which contains the top 5 customers who received an average high pre_invoice_discount_pct for the fiscal year 2021 and in the Indian market.
SELECT dc.customer_code, dc.customer,
ROUND((AVG(fpid.pre_invoice_discount_pct)),2) AS avg_discount_percentage
FROM gdb023.dim_customer AS dc
JOIN gdb023.fact_pre_invoice_deductions AS fpid
ON dc.customer_code = fpid.customer_code
WHERE dc.market = "India" AND fpid.fiscal_year = "2021"
GROUP BY dc.customer_code, dc.customer
ORDER BY avg_discount_percentage DESC
LIMIT 5;
​
​
​
​
​
​
​
​
​
​
-- 7. Get the complete report of the Gross sales amount for the customer “Atliq Exclusive” for each month.
SELECT fsm.date AS date, MONTHNAME(fsm.date) AS month, YEAR(fsm.date) AS year,
ROUND(SUM(fgp.gross_price * fsm.sold_quantity),2) AS gross_sales_amount
FROM gdb023.fact_sales_monthly AS fsm
JOIN gdb023.fact_gross_price AS fgp ON fsm.product_code = fgp.product_code
JOIN gdb023.dim_customer AS dc ON fsm.customer_code = dc.customer_code
WHERE dc.customer = "Atliq Exclusive"
GROUP BY date, month, year
ORDER BY year;
​
​
​
​
​
​
​
​
​
​
​
​
​
​
​
​
​
​
​
​
​
​
-- 8. In which quarter of 2020, got the maximum total_sold_quantity?
SELECT CASE WHEN month(date) between 9 and 11 THEN "1st quarter"
WHEN month(date) between 12 and 2 THEN "2nd quarter"
WHEN month(date) between 3 and 5 THEN "3rd quarter"
WHEN month(date) between 6 and 8 THEN "4th quarter"
ELSE NULL END AS "quarter",
SUM(sold_quantity) AS total_sold_quantity
FROM gdb023.fact_sales_monthly
WHERE fiscal_year = "2020"
GROUP BY quarter
ORDER BY total_sold_quantity DESC;
​
​
​
​
​
​
​
​
-- 9. Which channel helped to bring more gross sales in the fiscal year 2021
-- and the percentage of contribution?
WITH cte AS
(SELECT channel, ROUND(SUM(fgp.gross_price*fsm.sold_quantity),2) AS gross_sales_min
FROM gdb023.fact_sales_monthly AS fsm
JOIN gdb023.fact_gross_price AS fgp ON fsm.product_code = fgp.product_code
JOIN gdb023.dim_customer AS dc ON fsm.customer_code = dc.customer_code
WHERE fsm.fiscal_year = "2021"
GROUP BY dc.channel
ORDER BY gross_sales_min DESC),
cte1 AS (SELECT SUM(gross_sales_min) AS total_gross_sales_min
FROM cte)
SELECT cte.channel, cte.gross_sales_min,
ROUND((gross_sales_min*100/total_gross_sales_min),2) AS percentage
FROM cte JOIN cte1;
​
​
​
​
​
​
​
-- 10. Get the Top 3 products in each division that have a high total_sold_quantity
-- in the fiscal_year 2021?
WITH cte AS
(SELECT dp.division, dp.product_code, dp.product, dp.variant,
SUM(fsm.sold_quantity) AS total_sold_quantity
FROM gdb023.dim_product AS dp
JOIN gdb023.fact_sales_monthly AS fsm ON dp.product_code = fsm.product_code
WHERE fsm.fiscal_year = 2021
GROUP BY dp.division, dp.product_code, dp.product, dp.variant
ORDER BY total_sold_quantity DESC),
cte1 AS (SELECT *,
DENSE_RANK() OVER (PARTITION BY division ORDER BY total_sold_quantity DESC) AS rank_rate
FROM cte)
SELECT * FROM cte1
WHERE rank_rate <=3;
​
​
​
​
​
​
​
​
​
​
​
Data Modelling
Data Sharing
1. Visualization
​
Microsoft Power BI was used to visualize data.
(Click the image to see the powerpoint pdf)
​
​
​
​
​
​
​
​
​
​
​
​
​
​
​
​
​
​
​
​
​
​ 2. Insights
-
There was a 36.33% increase in products in 2021 compared to 2020
-
The majority of the product segment consists of Notebook, Accessories and Peripherals.
-
There are a small number of products in the Desktop, Storage, and Networking segments.
-
Accessories is the segment with the highest increase in 2021, followed by Notebook and Peripherals segments, and the Desktop category has also increased significantly compared to last year.
-
The product with the highest manufacturing cost price is the AQ HOME Allin1 Gen 2 product with product code A6120110206.
-
The product with the lowest manufacturing cost price is the AQ Master wired x1 Ms product with product code A2118150101.
-
Even though the rates are close to each other, the customer who is given the most pre-invoice discount is Flipkart.
-
Atliq Exclusive’s lowest Gross Sales Amount in the fiscal year is March 2020.
-
Atliq Exclusive’s highest Gross Sales Amount in the fiscal year is November 2020.
-
According to fiscal year 2020, the highest total sold quantity of products was in the 1st quarter.
-
According to fiscal year 2020, the lowest total sold quantity of products was in the 3rd quarter.
-
Retail channel helped to bring more gross sales to the Company with %73.22 as the contribution percentage in the fiscal year 2021.
-
Every division has a product with different variants that appears twice in the top three products by division list.
​
3. Results
​
-
Atliq Hardware is a successful Company operating in 4 Regions and 27 Countries around the world with its wide product range.
-
They increased their product range by 36.33% in one fiscal year, which means they want to improve their product range according to each region.
-
They can increase rare segments in their product ranges such as Storage, and Networking by implementing various campaigns with best-selling products.
-
Desktop and Notebook segments have higher manufacturing costs than other segments, so a campaign can be created by increasing the prices of products in these two segments and adding products with lower manufacturing costs.
-
Various moves should be made to increase sales in quarters with low sales during the fiscal year.
-
Distributor and direct sales are much lower than retail sales. Therefore, a good market research can be done and products can be offered to the customer according to their expectations, and more customers' attention can be attracted with a correct pricing strategy and campaigns for these sales methods.
