- Project Overview
- Business Problem
- Project Objectives
- Tools & Tevhnology Used
- Practical Use of SQL Statement, Clause & Functions
- Practical Use of Power Bi Dax
- End to End Analytics Workflow
- Sales Overview Dashboard, Report & Conclusion
- Customers Analysis Dashboard, Report & Conclusion
- Ads Campaign & Traffic Dashboard, Report & Conclusion
- Summary of Analysis, Action Plan and Key Takeaway
Turning Sales, Customer & Marketing Data into Data-Driven Business Decisions
Analytics Areas
- 📈 Sales Performance
- 👥 Customer Behavior & RFM
- 📣 Advertising & Traffic Performance
- 💰 Revenue & Profitability
- 🎯 Campaign Effectiveness
Technology Stack Excel → MySQL → Power BI → DAX
The objective was to transform this raw data into a reliable analytical dataset and create an executive-level BI solution that helps stakeholders make data-driven decisions.
The analysis was designed to help management answer these critical questions:
- How the business is performing overall
- Which products and categories are driving revenue
- Which customers are the most valuable
- How effectively the company is retaining customers
- Which marketing campaigns and channels generate the best results
- How advertising spend translates into revenue
- Which traffic sources generate high-quality customers
- Where the business has opportunities to improve revenue and profitability
To answer these questions, I built a complete analytics pipeline using:
Excel → MySQL → Power BI
The Business Challenge
The company had large amounts of sales, product, customer, marketing and traffic data but
However, the raw data was not immediately suitable for business analysis due to:
- Inconsistent
- Missing values
- Duplicate records
- Different data formats
- Inconsistent categorical values
- Multiple data sources
And Management lacked a centralized analytical view of business performance to track where is the business generating value, where are we losing value, and where should management focus its next investment?"
The key challenges were:
- Sales performance was difficult to monitor consistently.
- Order failures represented a significant share of total orders.
- Customer behavior was not clearly segmented.
- Marketing channels were generating different levels of traffic and revenue.
- Advertising spend needed stronger performance measurement.
- Management needed actionable insights rather than raw transactional data.
The main objectives of this project were to:
- Prepare and validate raw business data using Excel.
- Clean and transform the data using MySQL.
- Create analysis-ready datasets for reporting.
- Connect MySQL with Power BI.
- Build an interactive Sales Overview Dashboard.
- Perform Customer Analysis and RFM segmentation.
- Analyze advertising campaigns and traffic channels.
- Measure marketing efficiency using ROAS and related KPIs.
- Identify business trends, opportunities, and performance gaps.
- Provide actionable recommendations for business stakeholders.
| Tool | Purpose |
|---|---|
| Microsoft Excel | Initial data preparation, validation and profiling |
| MySQL | Data cleaning, transformation, aggregation and business analysis |
| Power BI | Data modeling, DAX, visualization and dashboard development |
| DAX | Business metrics, KPIs, time intelligence and customer analytics |
| Power Query | Data transformation and preparation |
| GitHub | Project documentation and portfolio |
MySQL was used as the primary data preparation layer between the raw datasets and Power BI. The SQL workflow focused on validating data quality, identifying duplicates and inconsistencies, standardizing categorical fields, handling missing values, correcting date ranges, and transforming the datasets into a reliable structure for business analysis and Power BI reporting.
| SQL Function / Statement | How I Used It in the Project |
|---|---|
| CREATE DATABASE | Created the Estore database to organize all analytical tables in a centralized MySQL environment. |
| USE | Selected the Estore database as the active database for all subsequent SQL operations. |
| CREATE TABLE | Created structured schemas for Orders, Products, Customers, Campaign, and Traffic datasets with appropriate data types. |
| DROP TABLE | Removed existing tables when recreating or restructuring the data schema. |
| TRUNCATE TABLE | Removed all records from a table while retaining its structure for data reloading and preparation. |
| SELECT | Used extensively to inspect datasets, validate records, profile data, and verify cleaning results. |
| SHOW VARIABLES | Checked the MySQL secure_file_priv configuration while preparing for CSV data loading. |
| WITH / CTE | Created temporary result sets to identify duplicate Orders and Customers in a structured and readable way. |
| COUNT() | Counted duplicate records, customers, orders, and other data-quality results. |
| COUNT(DISTINCT) | Measured the number of unique customers, countries, campaigns, and other distinct values. |
| GROUP BY | Grouped records by IDs or categories to identify duplicates and summarize data. |
| HAVING | Filtered grouped results to identify duplicate records where the count was greater than one. |
| COUNT() OVER() | Used a window function with PARTITION BY as an alternative method for identifying duplicate Order IDs. |
| PARTITION BY | Divided records into groups by Order ID so duplicate occurrences could be identified without collapsing the original rows. |
| DELETE | Removed identified duplicate Order records from the Orders table. |
| UPDATE | Modified existing records to clean and standardize values across the datasets. |
| JOIN | Compared Orders with Customers to validate customer IDs and investigate referential data consistency. |
| IN | Used to identify records matching a set of values, including duplicate IDs identified through subqueries. |
| LENGTH() | Checked the length of text fields to identify potential leading or trailing spaces. |
| TRIM() | Removed unwanted spaces from Order Status, Customer Names, IDs, and other text fields. |
| UPPER() | Converted selected characters to uppercase when standardizing text values. |
| LOWER() | Converted characters to lowercase to create consistent text formatting. |
| LEFT() | Extracted the first character of names for proper-case transformation. |
| SUBSTRING() | Extracted the remaining characters of names while standardizing customer names. |
| CONCAT() | Combined multiple text values to create standardized names and corrected Customer IDs. |
| LIKE | Identified patterns such as blank values, country variations, device types, and inconsistent categorical values. |
| IS NULL | Checked for missing values in fields such as Campaign ID and Customer ID. |
| CASE WHEN | Applied conditional transformation rules to standardize countries, device types, and other categorical values. |
| DISTINCT | Retrieved unique values to profile categorical columns and identify inconsistent data entries. |
| YEAR() | Extracted the year from dates to identify and correct invalid signup-date ranges. |
| DATE_ADD() | Adjusted incorrect customer signup dates by adding or subtracting years based on validation rules. |
| INTERVAL | Defined the amount of time to add or subtract when correcting date values. |
| START TRANSACTION | Started a transaction before making potentially sensitive data modifications. |
| COMMIT | Permanently saved validated data changes after reviewing the transformation. |
| ROLLBACK | Reverted changes made within a transaction when the modifications needed to be cancelled. |
| LPAD() | Standardized the formatting of generated Customer IDs by padding numbers with leading zeros. |
| FLOOR() | Converted randomly generated decimal values into whole numbers when creating replacement Customer IDs. |
| RAND() | Generated random numbers for replacing invalid Customer IDs during data correction. |
The project demonstrates practical use of:
| DAX Function | What I Did With It |
|---|---|
| CALCULATE() | Used to calculate business metrics under specific filter conditions, such as completed orders, previous-month revenue, and paid/organic performance. |
| SUM() | Used to aggregate numerical fields such as revenue, profit, COGS, quantity sold, and advertising spend. |
| DISTINCTCOUNT() | Used to count unique entities such as customers, orders, and other distinct business IDs without double-counting. |
| DIVIDE() | Used to safely calculate ratios and percentages such as order success rate, conversion rate, repeat purchase rate, and ROAS. |
| FILTER() | Used to apply custom filtering logic when creating metrics and analytical calculations based on specific business conditions. |
| SWITCH() | Used to create business-rule-based customer segmentation, including RFM customer segments such as Champions, Loyal Customers, and At Risk. |
| IF() | Used to apply conditional business logic, such as classifying customers, handling positive/negative growth, and assigning labels based on conditions. |
| COALESCE() | Used to replace blank values with meaningful defaults, such as classifying traffic with no campaign as Organic. |
| RANKX() | Used to rank customers based on Recency, Frequency, and Monetary values for RFM scoring and customer segmentation. |
| ALL() | Used to remove the current filter context when calculating overall customer rankings, totals, and comparison benchmarks. |
| PREVIOUSMONTH() | Used to retrieve the previous month's values for calculating month-over-month revenue and performance changes. |
| DATEDIFF() | Used to calculate the number of days between a customer's last purchase and the analysis/reference date for the RFM Recency metric. |
| FORMAT() | Used to format numerical results into user-friendly labels, such as RFM scores, percentages, and dynamic KPI labels. |
RAW BUSINESS DATA
│
▼
MICROSOFT EXCEL
│
Data Preparation
Data Validation
│
▼
MySQL
│
┌──────────────┼──────────────┐
│ │ │
Cleaning Transformation Analysis
│ │ │
└──────────────┼──────────────┘
│
▼
POWER BI
│
Data Modeling + DAX
│
┌──────────────┼──────────────┐
▼ ▼ ▼
SALES CUSTOMER MARKETING
ANALYSIS ANALYSIS ANALYSIS
│ │ │
└──────────────┼──────────────┘
▼
BUSINESS INSIGHTS
│
▼
DATA-DRIVEN DECISIONS
| KPI | Result |
|---|---|
| Total Orders | 2.68K |
| Revenue | $10.06M |
| Profit | $3.36M |
| COGS | $6.70M |
| Order Success Rate | 33.58% |
| Successful Orders | 901 |
| Unsuccessful Orders | 66.42% |
| Quantity Sold | 8K |
The company generated approximately $10.06M in revenue and $3.36M in profit.
However, the most important concern is the 33.58% order success rate.
That means approximately two-thirds of orders are currently classified as unsuccessful according to the dashboard's status logic.
This is potentially the largest operational opportunity in the business.
33.1% Completed 33.41% Cancelled 16.45% Refunded ~17% Processing
This creates an important business question:
Why are so many orders failing to reach successful completion?
Potential Causes to Investigate
Payment failures Customer cancellations Inventory availability Delivery delays Pricing issues Customer dissatisfaction Product availability Fraud screening Refund-related issues Business Opportunity
Even a modest improvement in successful order completion could have a meaningful impact on: Revenue, Profit, Customer satisfaction and Marketing ROI
The dashboard identifies the strongest-selling products across the electronics catalog.
The leading products include:
- Nikon Z8
- Sony Bravia XR
- Samsung S90C OLED
- Apple MacBook Pro
- Canon EOS R6
- Razer Blade
- Sony Alpha
- Sony Xperia
- Samsung Galaxy
- LG InstaView
- Business Questions
-
Which products deserve higher inventory allocation?
-
Which products drive the largest share of sales?
-
Are high-volume products also high-margin products?
-
Are there products with strong sales but weak profitability?
Do not evaluate product success using sales volume alone. Combine: Revenue + Quantity + Profit Margin + Return Rate to identify truly valuable products.
| KPI | Result |
|---|---|
| Total Customers / Orders | 927 / 2.68K |
| Net Revenue / Profit | $3.36M / $1.12M |
| Total Order Value | $10.06M |
| Avg Order Value | $3.75 |
| Customer Repeat Rate / Customers | 78.64% / 729 |
| New Customer Rate | 21.36% / 198 |
- 2.68K Orders placed by the 927 Customers.
- Business recieved the orders of order value of $10.06 Million but the net revenue is only $3.36 Million and Profit is $1.12 Million because almost 33.58% orders was successfully delivered
- We Have 78.64% Customers Repeat still we are losing the our most of the revenue and profit of almost 65%-70% in failed orders.
| KPI | Result |
|---|---|
| Total Traffic | 5.00 K / 2.68K |
| Organic Traffic / Orders | 2.54K / 1.38K |
| Paid Traffic / Orders | 2.46K / 1.30K |
| Conversion Rate | 53.66% / 2.68K |
| Bounce Rate / Bounce | 48.78% / 2.49K |
| Ad Spend / ROAS | $250.68K / 19.62x |
| Revenue from Ads / Net Revenue | $4.92M / $1.72M |
| Total Net Revenue | $3.33M |
| Total Order Value / Orders | $10.06M / 2.68K |
| Net Profit | $1.12M |
- The Overall Traffic comes from Organic and paid is 5.00K But the conversion is around 53.66% with 2.68K Orders
- Organic Traffic is 2.54K and the orders we recieved 1.38K Organically and Paid Traffic is 2.46K and the orders we recieved 1.30K. Almost 50% Traffic comes organically and 50% from paid Ads campaign
- Total $250.68K we spent on Ads campaign with 19.62x ROAS we get the overall very ROAS but this ROAS is shows overall ROAS when we look on the filter level for only the completed orders this will around 6x ROAS. Because the most of orders almost 67% of our orders was unsuccessful and only 33% of orders is successfully completed this very big concern
- $4.92 Million Revenue comes from Ads Campaign shows in the dashboard but the Net Revenue is only $1.72 Million when we look this on the order status filter this will show complete picture of all the orders
- $3.33 Million is Net Revenue this is the exact revenue we generated from the business this year
- $10.06 Million is the total order value the customers ordered but with the 33% order success rate we generated only $3.33 Million Net Revenue from the this total order value
- $1.12 Million is the Net profit we generated this year
| Channel | Amount | Percentage |
|---|---|---|
| Instagram Ads | $96,364 | 38.44% |
| Email Marketing | $44,920 | 17.92% |
| Facebook Ads | $43,931 | 17.52% |
| Google Ads | $39,315 | 15.68% |
| Referral | $26,154 | 10.43% |
- We Spent maximum on Instagram Ads $96,364 almost 38.44% of total amount of total Ad spend
- On Email Marketing $44,920 almost 17.92% of total
- $43,931 on Facebook Ad almost 17.52% of total
- $39,315 on Google Ads 15.68% of total
- $26.154 on Referral 10.43% of total
We run Ads campaign based on the multiple discount campaign type on that for that discount we launch Ads campaign for that specific discount campaign like
- Holiday Special
- Flash Sale
- VIP Exclusive
- New Year EVE
- Black Friday
- Back to School
- Clearance Sale
- Summer Sale
| Channel | Traffic % of Total | Conversion % of Total | Revenue % of Total | Order Success Rate |
|---|---|---|---|---|
| Organic | 50.88% | 51.40% | 51.13% | 16.92% |
| Instagram Ads | 14.68% | 13.53% | 13.98% | 4.29% |
| Google Ads | 10.08% | 9.28% | 10.29% | 3.35% |
| Facebook Ads | 9.90% | 10.10% | 8.59% | 3.32% |
| Referral | 9.44% | 10.55% | 10.64% | 3.73% |
| Email Marketing | 5.02% | 5.14% | 5.36% | 1.98% |
-
50% of traffic comes from organically without spending any amount on Ads campaign and almost 50% of traffic come from all the Ads campaign which include Instagram, Facebook, Google, Email Marketing and Referral
-
If we don't spend on the Ads Campaign still we can do well, The biggest concern is that 33% order success rate we are recieving decent amount orders but almost around 60% - 70% is our order unsuccess rate
-
If we do further deep dive analysis to diagnose why our orders has 33% success rate and 60 - 70% unsuccess rate and works on the improvement of order success rate this can become massive win for business
-
Instead of running discount Ads campaign we have to run our campaign based on the customer target who have placed the but cancelled, refunded we have target those customer by giving extra discount with best shopping experience can more valueable than spending on discount Ads campaign
| Channel | Traffic % of Total | Conversion % of Total | Order Success Rate |
|---|---|---|---|
| Tablet | 50.88% | 51.40% | 16.92% |
| Desktop | 14.68% | 13.53% | 4.29% |
| Mobile | 10.08% | 9.28% | 10.29% |
- Overall Ads and Traffic is look perfect but Ads can be make more profitable and gigh revenue generator
- The biggest concern is the low as 50% Conversion rate this can be more better and this can also improve our customers and also helps us to retain customer for long term and increase repeat purchase and the overall business can be improve by which