AtliQ Hardware Sales Insights

AtliQ Hardware

A company that supplies computer hardware and peripherals to many clients across India.
The company has a head office in Delhi and regional offices throughout India.

---

Business Issue

The sales director is facing a lot of challenges. The marketing is growing dynamically, he is struggling to keep track of the sales. He needs more accurate insights about the company sales and then makes the necessary decisions.

---

Solution

• Create a simple and informative dashboard about the company sales.
• I used SQL queries in MySQL Workbench to look into the data and Tableau for ETL and Visualizations to create the insights dashboard.

---

Data Overview

# Tables

1. transactions	2. customers	3. date	4. products	5. markets
Field Name Description Product Code ProdXXX Customer Code CusXXX Market Code MarkXXX Order Date YYYY-MM-DD Sales Qty Quantity Sold Sales Amount Sales Amount Currency INR/USD Profit Margin (Sales Amount - Cost)/(Sales Amount) Profit (Sales Amount-Cost) Cost Total Cost of a Product	Field Name Description Customer Code CusXXX Custmer Name Stores Names Customer Type Brick & Mortar/ E-Commerce	Field Name Description Date YYYY-MM-DD Cy Date YYYY-MM-DD: Starting date of each month of date Year YYYY: Year of the date Month Name MMMM: Month of the date column Date Yy Mmm DD-MMM: Date and Month of the date column	Field Name Description Product Code ProdXXX Product Type Own Brand/Distribution	Field Name Description Markets Code MarkXXX Markets Name City Names Zone South/Central/North

# Data Analysis Using SQL

1. Show all tables and their rows in sales schema

   SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_ROWS  
   FROM INFORMATION_SCHEMA.TABLES 
   WHERE TABLE_SCHEMA = 'sales';
   

   TABLE_SCHEMA	TABLE_NAME	TABLE_ROWS
   sales	customers	38
   sales	date	1126
   sales	markets	17
   sales	products	279
   sales	ransactions	147678

2. Show date range

   SELECT 'First Date', MIn(Order_Date)  
   FROM sales.transactions  
   UNION  
   SELECT 'Last Date', MAX(Order_Date)  
   FROM sales.transactions;
   

   First Date	2017-10-04
   Last Date	2020-06-26

3. Show Revenue in 2020 and 2019.

   SELECT d.year, SUM(Sales_Amount)
   FROM sales.transactions as t
   JOIN sales.date as d
   ON t.Order_Date = d.date
   WHERE d.year = '2019'
   UNION
   SELECT d.year, SUM(Sales_Amount)
   FROM sales.transactions as t
   JOIN sales.date as d
   ON t.Order_Date = d.date
   WHERE d.year = '2020';
   

   2019	336019102
   2020	142224545

4. Show distinct currency and their count

   SELECT currency, COUNT(currency)
   FROM sales.transactions
   GROUP BY currency;
   

   INR	148393
   USD	2

# Insights

After a quick data exploration in MySQL, here are some initial findings:

• The database contains 5 tables: customers, date, markets, products, and transactions.
• There are 17 markets, 279 products, and 38 customers.
• The observation period is from OCT 2017 to JUN 2020.
• The total revenue in 2020 was ₹ 142.22 M, 57.7% less than 2019, which was ₹ 336.02 M.
• Most of the transactions data are in INR(₹) currency, but we have 2 records in US($) currency.
• And we got some garbage values in sales amount and market column. We’re going to deal with it in the ETL process.

---

ETL(Extract, Transform, Load)

Once I knew the basic features of the data I had to work with, I Imported the MySQL database into Tableau to do the necessary transformations and make a simple, reliable, and helpful dashboard.

# Data Modeling Step

We have one main table and four other tables having one shared column with the main table. So we will connect the other tables to the main table using the shared columns.

• Main Table: transactions

Table	Column	Main Table Column
customers	Customer_Code	Customer_Code
date	date	Order_Date
products	Market_Code	Market_Code
markets	Product_Code	Product_Code

# Filtering, Cleaning and Adding New Columns

• The company is serving only in India, So “Paris” and “New York” in the market table are garbage values, so filtering them out.
• The “currency” column (in transactions table) have 2 USD currency values, So created a new column called “Sales”, where all the sales_amount is in INR Currency.

---

Dashboards

The two dashboards shows all the main information about the company sales.

# Dashboard 1: Sales Insights

- Revenue
- Net Profit
- Revenue by Market
- Profit Trend by Market
- Revenue by Top 10 Products
- Profit Trend by Top 10 Products

# Dashboard 2: Loss Analaysis

- Loss Amount
- Markets Which creating Loss
- Top 10 Lossing Products

• It can be filtered by YEAR and it's a interactive Dashboard i.e, each other insights are inter-related and can be seen in any respects. So the sales director can have a deeper and quick view of the sales to support his decision making process.

---

Final Report

Based on the dashbaords insights, I have made some conclusions and recommendation that Sales Marketing team should/can consider making a sales strategy.

# Conclusions

• Sales were rapidly decreasing in 2020 compared to 2019 by around 57.7%.
• Highest revenue generated from Markets such as Delhi NCR, Mumbai, Ahmedabad, Bhopal, Nagpur, and so on.
• Highest quantities sold in the Market such as Delhi NCR, Mumbai, Nagpur, Kochi, Ahmedabad, and so on.
• Majority of the sales were takes place in the month of January followed by November and March.

# Recommendation

• Make a new sales strategy for lucknow since its showing lowest revenue and negative profit margin and if possible so as for Surat and Bhubhaneshwar also.
• try to increase sales quantity in Patna, Surat and Kanpur since they have lowest sales quantity.
• start target campagin for Prod047 and Prod061 since they two are the most profitable and most selling products.
• try to give special benefits to Electronics and Excel stores as they are most profitable customers.
• make campgain strategy for mid year as they are showing high sales among other months.

---

References

• Tableau Dashboard: AtliQ Hardware Sales Insights by Laxman Singh
• Project Data: Google Sheet | MySQL Dump File
• Project Inspiration: codebasics YouTube channel.