This repository contains an end-to-end data analysis project using Superstore Sales Data. The project focuses on setting up a MySQL database, cleaning and transforming raw sales data, performing exploratory analysis (EDA), and implementing RFM (Recency, Frequency, Monetary) segmentation to categorize customers based on their purchasing behavior.

❓ Why This Project?
This project demonstrates skills in database management, data wrangling, and customer behavior analysis. It provides a template for handling real-world sales data and actionable insights through RFM segmentation.

The goal of this project is to:

• Clean and format raw sales data.
• Convert the cleaned CSV to SQL.
• Create a database and table under that database.
• Import the SQL file into MySQL.
• Clean Formatting and Handling of Imported Data.
• Perform exploratory Data analysis.(EDA)
• Segment customers using RFM (Recency, Frequency, Monetary) analysis.

• Data Cleaning: Handled blank cells, renamed columns, and fixed date formats in Google Sheets or Microsoft Excel.
• CSV-to-SQL Conversion: Automated SQL schema creation and bulk insertion using CSV to SQL Converter Tools.
• RFM Segmentation: SQL queries to classify customers into actionable groups (e.g., "Loyal Customers", "At-Risk").
• Exploratory Analysis: Analyzed sales trends, regional performance, and product profitability.

• MySQL Workbench
• Google Sheets (or Excel) for data cleaning
• Rebasedata (free CSV-to-SQL converter)

• Open superstore_raw.xlsx in Google Sheets.
• Perform cleaning:
  • Fill blank cells (e.g., using CTRL + Click or Go To > Special > Blanks).
  • Update column names for consistency (e.g., Order ID → order_id).
  • Fix date formats (e.g., MM/DD/YYYY → MySQL-friendly YYYY-MM-DD).
• Export cleaned data as superstore_clean.csv .

• Upload superstore_clean.csv to Rebasedata.
• Download the generated .sql file (includes table schema and data).

• Open your server in MySQL Workbench.
• If the superstore database doesn’t exist, create it first:

CREATE DATABASE superstore;

• Steps:

  1. Connect to Server:
     Open MySQL Workbench and connect to your MySQL server.

  2. Open Data Import:
     Go to Server > Data Import in the top menu.

  3. Select Import Options:

     • Choose Import from Self-Contained File.
     • Browse and select your superstore_clean.sql file.
     • Under Default Target Schema, select the superstore database.

  4. Start Import:
     Click Start Import at the bottom-right.

  **Why Use This?**  
  - **Beginner-Friendly**: No command-line commands required.  
  - **Progress Tracking**: Visual progress bar for large files.  
  - **Error Logging**: Detailed error messages if the import fails.  

• Steps:

  1. Rename the Table

     RENAME TABLE Superstore_clean TO Sales_data;

  

  1. Get the Row Count of the Table

     SELECT COUNT(*) AS row_count FROM Sales_data;

  

  1. Describe the structure

     DESCRIBE sales_data;

  

  1. Modify Date column types

   ALTER TABLE sales_data
   MODIFY order_date DATE,
   MODIFY ship_date DATE;

  

  1. Fix Date Columns & Handle numeric fields
     • When you update Table , You need to disable safe update mode.

      -- Disable safe update mode
      SET SQL_SAFE_UPDATES = 0;
      
      -- Fix Date Columns
        UPDATE sales_data
        SET Order_Date = STR_TO_DATE(Order_Date, '%Y-%m-%d'),
            Ship_Date = STR_TO_DATE(Ship_Date, '%Y-%m-%d');
      
      -- Handle numeric fields
      UPDATE sales_data 
      SET Product_Base_Margin = NULL 
      WHERE Product_Base_Margin = 'null' 
         OR Product_Base_Margin = '';
         
      -- Re-enable safe update mode
      SET SQL_SAFE_UPDATES = 1;

  

• Steps:

  1. Total Sales & Profit

   SELECT 
       ROUND(SUM(Sales)) AS Total_Sales,
       ROUND(SUM(Profit)) AS Total_Profit
   FROM sales_data;

  

  1. Sales by City

   SELECT 
       City,
       ROUND(SUM(Sales)) AS City_Sales
   FROM sales_data
   GROUP BY City
   ORDER BY City_Sales DESC;

  

  1. Top 10 Products

   SELECT 
       Product_Name,
       ROUND(SUM(Sales)) AS Total_Sales
   FROM sales_data
   GROUP BY Product_Name
   ORDER BY Total_Sales DESC
   LIMIT 10;

  

  1. Top 10 Most Profitable Products

   SELECT 
       Product_Name,
       ROUND(SUM(Profit)) AS Total_Profit
   FROM sales_data
   GROUP BY Product_Name
   ORDER BY Total_Profit DESC
   LIMIT 10; 

  

  1. Monthly Sales Trends

   SELECT 
       DATE_FORMAT(Order_Date, '%Y-%m') AS Month,
       ROUND(SUM(Sales)) AS Monthly_Sales
   FROM sales_data
   GROUP BY Month
   ORDER BY Month;

  

  1. Return Rate Analysis

   SELECT 
       Return_Status,
       COUNT(*) AS Return_Count,
       ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM sales_data), 2) AS Return_Percentage
   FROM sales_data
   GROUP BY Return_Status;

  

• Steps:

  1. Create/Replace View for RFM Raw Metrics

   CREATE OR REPLACE VIEW RFM_RAW_DATA AS
   SELECT
       Customer_ID,
       DATEDIFF('2014-01-01', MAX(Order_Date)) AS Recency,
       COUNT(DISTINCT Order_ID) AS Frequency,
       SUM(Sales) AS Monetary
   FROM sales_data
   GROUP BY Customer_ID;

  

  1. RFM Segmentation Using the View

   CREATE OR REPLACE VIEW RFM_SCORES AS
   WITH rfm_scores AS (
       SELECT
           Customer_ID,
           Recency,
           Frequency,
           Monetary,
           NTILE(3) OVER (ORDER BY Recency DESC) AS R_Score,
           NTILE(3) OVER (ORDER BY Frequency) AS F_Score,
           NTILE(3) OVER (ORDER BY Monetary) AS M_Score
       FROM RFM_RAW_DATA
   )
   SELECT
       Customer_ID,
       Recency,
       Frequency,
       Monetary,
       R_Score,
       F_Score,
       M_Score,
       CONCAT(R_Score, F_Score, M_Score) AS RFM_Cell,
       CASE
           WHEN CONCAT(R_Score, F_Score, M_Score) IN ('333','323','332') THEN 'Champions'
           WHEN CONCAT(R_Score, F_Score, M_Score) LIKE '2__' THEN 'Potential Loyalists'
           WHEN CONCAT(R_Score, F_Score, M_Score) LIKE '1__' THEN 'At-Risk Customers'
           ELSE 'Needs Attention'
       END AS RFM_Segment
   FROM rfm_scores;

  

1. Database Setup: Created a MySQL database and table schema tailored for the Superstore dataset. ```sql -- Create database CREATE DATABASE IF NOT EXISTS Superstore; USE Superstore;

     -- Create table with corrected schema
     DROP TABLE IF EXISTS sales_data;
     CREATE TABLE sales_data (
         Row_ID INT,
         Order_Priority VARCHAR(13),
         Discount DECIMAL(3,2),
         Unit_Price DECIMAL(6,2),
         Shipping_Cost DECIMAL(5,2),
         Customer_ID INT,
         Customer_Name VARCHAR(28),
         Ship_Mode VARCHAR(14),
         Customer_Segment VARCHAR(14),
         Product_Category VARCHAR(15),
         Product_Sub_Category VARCHAR(30),
         Product_Container VARCHAR(10),
         Product_Name VARCHAR(98),
         Product_Base_Margin DECIMAL(4,2), -- Changed from VARCHAR
         Region VARCHAR(7),
         Manager VARCHAR(7),
         State_or_Province VARCHAR(20),
         City VARCHAR(19),
         Postal_Code INT,
         Order_Date DATE, -- Changed from VARCHAR
         Ship_Date DATE, -- Changed from VARCHAR
         Profit DECIMAL(7,2),
         Quantity_ordered_new INT,
         Sales DECIMAL(8,2),
         Order_ID INT,
         Return_Status VARCHAR(12)
     );
    ```
   

2. Bulk Data Insertion: Efficiently imported the dataset into MySQL using Table Data Import Wizard or LOAD DATA INFILE.

3. Data Cleaning & Validation:

   • Standardized date formats.
   • Handled missing/duplicate values.
   • Ensured consistent data types (e.g., sales, profit as DECIMAL, dates as DATE).
   • Updated table schema for optimization (e.g., indexing, constraints).

4. Exploratory Data Analysis (EDA):

   • Analyzed sales trends, regional performance, and product category insights.
   • Identified top customers and profitable products.

5. RFM Customer Segmentation:

   • Calculated Recency, Frequency, and Monetary Value metrics.
   • Segmented customers into groups (e.g., "High-Value", "At-Risk", "Champions") for targeted strategies.

I chose Google Sheets and Rebasedata for data cleaning and SQL conversion due to challenges faced with direct CSV imports in MySQL:

1. Formatting Errors:
   MySQL often failed to parse dates, strings with special characters (e.g., ' in names), or numeric fields with inconsistencies (e.g., $1,000 as text).
2. Data Loss:
   Columns with mixed data types (e.g., postal codes stored as numbers) caused incomplete imports or truncated values.
3. Time-Consuming Fixes:
   Manually editing schemas, handling missing values, and debugging import errors took hours.
4. Bulk Insertion Failures:
   Large datasets (e.g., 10,000+ rows) frequently caused timeouts or incomplete imports.

• Google Sheets:

  • ✅ Visual Data Cleaning: Easily fill blank cells, rename columns, and fix date formats without coding.
  • ✅ Prevent Data Loss: Ensure consistency (e.g., postal codes as text, sales as numbers) before conversion.
  • ✅ User-Friendly: No SQL expertise needed for initial cleanup.

• Rebasedata:

  • ✅ Automated Schema Generation: Converts CSV columns to correct MySQL data types (e.g., DATE, DECIMAL).
  • ✅ Bulk Insertion Made Easy: Generates optimized .sql files for seamless imports, avoiding timeouts.
  • ✅ Error-Free: Handles special characters, quotes, and formatting issues automatically.

A streamlined workflow that saved time, reduced errors, and ensured 100% data integrity during migration.

• Data Cleaning: Google Sheets
• CSV-to-SQL Conversion: Rebasedata
• Database: MySQL
• Analysis: Pure SQL queries

MIT License. See LICENSE for details.

• Tool: MySQL for Data analysis.
• Tool: Google Sheets for data cleaning.
• Tool: Rebasedata for CSV-to-SQL conversion.