A Deep Dive into Indian Hardware Company's Sales Performance: Profit and Revenue Analysis with SQL and Tableau"

In this project, I tackled a business problem faced by an India-based hardware company by employing a data-driven approach, using SQL and Tableau to provide actionable insights and effective solutions.

Business Problem

AtliQ is a hardware company that operates by supplying computer hardware and peripherals to clients throughout India, with a head office located in Delhi and regional offices situated in various locations across the country.

The sales director is facing numerous challenges due to rapid market changes and strong competition, making sales tracking difficult. The sales director needs more accurate insights about the company's sales to make data-driven decisions.

Business scenario:

The sales director requires comprehensive information about sales performance across all operations, but the current data provided by regional sales managers is insufficient. Merely hearing numbers or receiving large amounts of Excel files is not effective in obtaining a reliable overview of the business. The sales director desires a more effective way to view and understand the data to gain immediate insights into the company's sales performance.

Solution

Non-complex and interactive dashboard uncovering sales insights with latest data available.

Approach - Project Planning & Aims Grid

1. Purpose - To uncover previously unseen sales insights and provide decision support to the sales team, while also automating the process to reduce manual data gathering time.

2. Stakeholders - sales director, marketing team, customer service team, data analytics team, and IT personnel.

3. End Result - An automated dashboard to provide real-time sales insights, facilitating data-driven decision making for the stakeholders.

4. Success Criteria -

• Dynamic dashboards reveal real-time sales order insights.
• Improved decision-making results in 10% reduction in total spend.
• Sales analysts save 20% of their time previously spent on manual data gathering.

Data Analysis -Approach

Setup Process

Step 1: Download file: db_dump.sql or db_dump.xlsx

Step 2: Import it in MySql : Data Exploration

Step 3: Download Tableau Public (Free) or Tableau Desktop (14 days trial) to perform ETL (Extract,tranform and load) and visualization

Step 4: Connect Tableau with MySql database or Excel database

Step 5: Save the file as (.twb or .twbx)

Data Analysis Using SQL

1. Show all customer records

   SELECT * FROM customers;

2. Show total number of customers

   SELECT count(*) FROM customers;

3. Show transactions for Chennai market (market code for chennai is Mark001

   SELECT * FROM transactions where market_code='Mark001';

4. Show distrinct product codes that were sold in chennai

   SELECT distinct product_code FROM transactions where market_code='Mark001';

5. Show transactions where currency is US dollars

   SELECT * from transactions where currency="USD"

6. Show transactions in 2020 join by date table

   SELECT transactions.*, date.* FROM transactions INNER JOIN date ON transactions.order_date=date.date where date.year=2020;

7. Show total revenue in year 2020,

   SELECT SUM(transactions.sales_amount) FROM transactions INNER JOIN date ON transactions.order_date=date.date where date.year=2020 and transactions.currency="INR\r" or transactions.currency="USD\r";

8. Show total revenue in year 2020, January Month,

   SELECT SUM(transactions.sales_amount) FROM transactions INNER JOIN date ON transactions.order_date=date.date where date.year=2020 and and date.month_name="January" and (transactions.currency="INR\r" or transactions.currency="USD\r");

9. Show total revenue in year 2020 in Chennai

   SELECT SUM(transactions.sales_amount) FROM transactions INNER JOIN date ON transactions.order_date=date.date where date.year=2020 and transactions.market_code="Mark001";

Data Analysis Using Tableau

Tableau Public Dashboards: Revenue & Profit Analysis

Create Star Schema in Tableau

Tableau Dashboard - Revenue Analysis

Tableau Dashboard - Profit Analysis

Project References: 🔗

Sr.No. 🔢	References 👨‍💻	Links 🔗
1	Tableau Project Dashboard : Sales Insights	Dashboard
2	MySQL installation	Javatpoint
3	OLTP & OLAP	Geeks for Geeks
4	Star Schema: Fact Table & Dimension Table	Microsoft docs.

Check out my other projects too : 🔍 👨‍💻 🛰️

Data-Driven Marketing Strategies for Rider Conversion : Rider Usage Analysis with R and Tableau 📊

An Analysis of Developer Communities: Insights from the 2022 Stack Overflow global Survey 📊