Sales Analysis is an end-to-end data analytics project focused on transforming raw sales data into actionable business insights. The project follows a complete data analysis lifecycle:
🔵 Understanding the Problem Statement – Defining business challenges and analytical goals.
🔵 Project Planning using the AIIMS Grid – Structuring objectives, metrics, and deliverables.
🔵 Data Collection – Extracting source data from a MySQL database.
🔵 Exploratory Data Analysis (EDA) – Using SQL to understand the dataset, identify data types, check for nulls, duplicates, and data consistency.
🔵 Data Cleaning and Transformation (ETL) – Preparing the data by handling missing values, standardizing formats, and building a structured data model.
🔵 Dashboard Development – Creating an interactive and visually compelling Power BI dashboard for real-time sales insights.
This project demonstrates practical skills in SQL, ETL processes, data modeling, and Power BI visualization, and is designed to support data-driven decision-making in a business context.
📑 Table of Contents
1.Problem statement
Atliq Hardware Systems is a company that supplies computer hardware and peripherals to wide range of clients across India. They supply products to clients in various regins of India. The company is headquatered in Delhi, with several regional offices spread scross the nation. The sales manager faces several challenges in tracking and analysing sales in rapidly growing and dynamic market.
Challanges
-
Lack of real time insights Sales manager in each region provides individual reports, making it difficult for director to access overall performance.
-
Insufficient Reporting Process The current process involves receiving numerous Excel files each quarter, which makes tracking sales performance time-consuming and inefficient.
2.Steps Involved
a. Project planning using AIIMS grid
-> Purpose - interactive dashboard that enables real time sales insights and support data driven decison making
-> Stakeholder - Market team, Sales director, IT team, Data Analytics team.
-> End result - interactive dashboard providing latest sales insight
-> Success criteria - Dashboard is user friendly, reflects sales insights and helps stakeholders identify trends
b. Data discovery and data analysis
Once AIMS grid is defined, next step is data discovery. In this step, data analyst team approaches IT team within an organization who owns software system that keep track of sales records. These records are stored in MySQL database. Power BI can be plugged to this database to pull necessary information required for data analysis.
-> IT team provides the Analytics team with access to MySQL database
-> The analytics team:
- Inspects tables (Customers, Products, Transactions, Date, Markets)
- Verifies data integrity by checking for for proper formatting of data, missing or null values.
c Data Loading
-> Established a connection to the MySQL database using Power BI's built-in connector and authenticated using server credentials.
-> Loaded data into Power BI's data model for building visualizations and performing further analysis.
c. Data Cleaning
-> Removed non Indian Markets : Filtered out rows with New York and Paris from the Markets table since the business is focused in India.
= Table.SelectRows(sales_markets, each ([markets_name] <> "New York" and [markets_name] <> "Paris"))**
-> Filtered empty rows : Removed rows with missing values to maintain data quality.
= Table.SelectRows(sales_markets, each ([zone] <> ""))
-> Filtered invalid transactions : Excluded records where Transaction Amount <= 0.
= Table.SelectRows(sales_transactions, each ([sales_amount] <> -1 and [sales_amount] <> 0))
-> Currency conversion : Added a conditional column to convert USD to INR using a defined exchange rate formula.
= Table.SelectRows(#"Removed -1/0", each ([currency] = "INR#(cr)" or [currency] = "USD#(cr)"))
-> Add new column normalised_sales_amount: To normalize all sales amounts to INR for consistent analysis, especially when the dataset includes mixed currencies.
= Table.AddColumn(#"Cleanup currency", "normalised_sales_amount", each if [currency] = "USD" or [currency] = "USD#(cr)" then [sales_amount]*75 else [sales_amount])
d. Data Modelling
-> Established relationship between tables.
-> Model established star schema
2.Performing primary Analysis on Data
1. To display total records are there in transaction table
SELECT count(*) FROM sales.transactions;2. show transactions only from Chennai
SELECT * FROM sales.transactions where market_code = "Mark001";3. To know how many currency has USD cureency
SELECT * FROM sales.transactions where currency = "USD";4.To show transactions in 2020
SELECT sales.transactions.*, sales.date.*
from sales.transactions
inner join sales.date
on sales.transactions.order_date = sales.date.date
where year = 2020;5.To calculate the sum of revenue in the year 2020
SELECT SUM(sales.transactions.sales_amount)as sum_revenue
from sales.transactions
inner join sales.date
on sales.transactions.order_date = sales.date.date
where year = 2020;6.To calculate sum of revenue in chennai
SELECT SUM(sales.transactions.sales_amount)as sum_revenue
from sales.transactions
inner join sales.date
on sales.transactions.order_date = sales.date.date
where year = 2020 and market_code = "Mark001";6.To calculate sum of revenue in 2020
SELECT SUM(sales.transactions.sales_amount)as sum_revenue
from sales.transactions
inner join sales.date
on sales.transactions.order_date = sales.date.date
where year = 2020;3.Building Reports
a. Displaying revenue by customer
b. Sales quantity by markrt
c. Revenue Trend
d.Top 5 Customers
e.Top 5 products
