This project focuses on preparing, cleaning, and analyzing sales data using SQL and building a real-time, executive-oriented dashboard using Microsoft Power BI. Key business insights are also highlighted.
1. Importing data
The original dataset was reviewed in Excel to understand its structure, identify irrelevant columns, and check for data types.
The data was then imported into **MySQL**, ensuring the correct formats were applied—especially for dates (e.g., `%m/%d/%Y`).
2. Data Cleaning
All cleaning steps were performed using SQL. The full script is available in the project files.
Steps included:
- Removing duplicates
- No duplicates were found. If needed:
DELETE FROM sales_data2_copy WHERE row_num > 1;- Standardizing data
- No spelling issues, extra spaces, symbols, etc. If any, we could proceed as follows:
- Spaces before words:
SET Country = TRIM(Country);- Similar words:
SET Country = ‘Canada' WHERE Country LIKE ‘Canad';- Symbols or dots at the end:
TRIM(TRAILING '.' FROM Country) WHERE Country LIKE 'Canada%';- Handling Null or missing values
- No null values were found in the dataset. If any had been present, they would have been addressed using context-appropriate methods such as imputation or row removal. For example, missing product prices could be filled using the average price of the same item in the same store, or across other stores. Similarly, missing categories could be inferred based on similar products.
- Removing rellevant Columns/Rows
- Example:
ALTER TABLE sales_data2 DROP COLUMN row_num;
DELETE FROM sales_data2 WHERE product IS NULL;3. Analyzing data
Before moving to Power BI, we ran exploratory analysis via SQL. The script calculates key metrics like Total Revenue and Profit:
ALTER TABLE sales_data2 ADD COLUMN Total_Revenue DOUBLE;
UPDATE sales_data2 SET Total_Revenue = (Price_Per_Unit * Quantity_Purchased) - Discount_Applied;
ALTER TABLE sales_data2 ADD COLUMN Profit DOUBLE;
UPDATE sales_data2 SET Profit = Total_Revenue - (Cost_Price * Quantity_Purchased);As seen in the script, The analysis focuses on 4 aspects:
- Total revenue & profit by country.
- Top 5 best-selling products.
- Top 5 representatives.
- Best-performing store locations from the last stored week.
1. Importing Cleaned Data
Cleaned data was imported into Power BI as .csv or directly from the MySQL database.
2. data Transformation with DAXNew measures created:Total Revenue, Total Profit, Total Discounts Given, and Average Order Revenue.
Additional columns: Month Name, Month Number, Day Name, Day Number — useful for chronological charts and trend lines.
3. Dashboard DesignDesign principles:
- Focused on executive-level KPIs.
- Key metrics displayed using value cards: Total Revenue, Total Profit, Total Orders, etc.
Visuals include:
- Bar charts (e.g., daily revenue, revenue by category)
- Line charts (e.g., monthly trends)
- Map charts and scatter plots
- Filters for dynamic analysis (e.g., by country or store)
- December stands out as the month with highest revenue (~0.52M) — likely influenced by seasonal/holiday shopping patterns.
- Tuesdays and Fridays underperform compared to other days — ideal for scheduling non-sales activities like inventory audits or staff training.
- Discounts correlate with profit — suggesting discounts may be used strategically rather than harming margins.
- Payment method usage is balanced — Cash, Credit Card, and Mobile Payments each contribute ~33%, indicating no dominant preference across users.
- Top revenue categories are Home & Kitchen, Clothing, and Electronics — potential areas for promotion and cross-selling.
- Geographic revenue differences are significant — London, Birmingham, New York and Montreal generate higher profits and/or discounts.
- Nigeria, India, and China show cluster patterns with lower discounts but decent profits — possible candidates for margin optimization.
Further insights can be uncovered using Power BI filters to segment by country, store, or product category.