Retail Sales Analysis · Step 2 of 5

Data Cleaning & Preparation

Dataset Overview

The Superstore dataset contains 9,994 records with 21 columns including order information (Order ID, Order Date, Ship Date, Ship Mode), customer data (Customer ID, Name, Segment, Location), product details (Product ID, Category, Sub-Category, Name), and financial metrics (Sales, Quantity, Discount, Profit). The data spans from January 2014 to December 2017, covering sales across all 50 US states and four distinct regions (West, East, Central, and South). This comprehensive dataset was obtained from a national retail chain's enterprise resource planning (ERP) system, providing a robust foundation for trend analysis and business intelligence.

Sample Data Preview (First 10 of 9,994 records)
Row ID Order ID Order Date Ship Date Ship Mode Customer ID Customer Name Segment Country City State Postal Code Region Product ID Category Sub-Category Product Name Sales Quantity Discount Profit
1 CA-2016-152156 11/8/2016 11/11/2016 Second Class CG-12520 Claire Gute Consumer United States Henderson Kentucky 42420 South FUR-BO-10001798 Furniture Bookcases Bush Somerset Collection Bookcase 261.96 2 0 41.91
2 CA-2016-152156 11/8/2016 11/11/2016 Second Class CG-12520 Claire Gute Consumer United States Henderson Kentucky 42420 South FUR-CH-10000454 Furniture Chairs Hon Deluxe Fabric Upholstered Stacking Chairs, Rounded Back 731.94 3 0 219.58
3 CA-2016-138688 6/12/2016 6/16/2016 Second Class DV-13045 Darrin Van Huff Corporate United States Los Angeles California 90036 West OFF-LA-10000240 Office Supplies Labels Self-Adhesive Address Labels for Typewriters by Universal 14.62 2 0 6.87
4 US-2015-108966 10/11/2015 10/18/2015 Standard Class SO-20335 Sean O'Donnell Consumer United States Fort Lauderdale Florida 33311 South FUR-TA-10000577 Furniture Tables Bretford CR4500 Series Slim Rectangular Table 957.58 5 0.45 -383.03
5 US-2015-108966 10/11/2015 10/18/2015 Standard Class SO-20335 Sean O'Donnell Consumer United States Fort Lauderdale Florida 33311 South OFF-ST-10000760 Office Supplies Storage Eldon Fold 'N Roll Cart System 22.37 2 0.2 2.52
6 CA-2014-115812 6/9/2014 6/14/2014 Standard Class BH-11710 Brosina Hoffman Consumer United States Los Angeles California 90032 West FUR-FU-10001487 Furniture Furnishings Eldon Expressions Wood and Plastic Desk Accessories, Cherry Wood 48.86 7 0 14.17
7 CA-2014-115812 6/9/2014 6/14/2014 Standard Class BH-11710 Brosina Hoffman Consumer United States Los Angeles California 90032 West OFF-AR-10002833 Office Supplies Art Newell 322 7.28 4 0 1.97
8 CA-2014-115812 6/9/2014 6/14/2014 Standard Class BH-11710 Brosina Hoffman Consumer United States Los Angeles California 90032 West TEC-PH-10002275 Technology Phones Mitel 5320 IP Phone VoIP phone 907.15 6 0.2 90.72
9 CA-2014-115812 6/9/2014 6/14/2014 Standard Class BH-11710 Brosina Hoffman Consumer United States Los Angeles California 90032 West OFF-BI-10003910 Office Supplies Binders DXL Angle-View Binders with Locking Rings by Samsill 18.50 3 0.2 5.78
10 CA-2014-115812 6/9/2014 6/14/2014 Standard Class BH-11710 Brosina Hoffman Consumer United States Los Angeles California 90032 West OFF-AP-10002892 Office Supplies Appliances Belkin F5C206VTEL 6 Outlet Surge 114.90 5 0 34.47

Data Quality Assessment

Methodology

I used a systematic approach to evaluate data quality across multiple dimensions: completeness, consistency, accuracy, and validity. This process included both automated checks using Excel's data validation tools and manual review of critical data points.

  • Missing values: 100% of records were complete with no null values found across all fields
    Used Excel's COUNTBLANK function on each column to verify completeness
  • Data type consistency: Ensured dates were properly formatted (MM/DD/YYYY), numeric fields contained only numbers, and categorical variables maintained consistent formatting
    Standardized date formats, fixed inconsistent capitalization in categorical variables
  • Logical relationships: Verified that all Ship Dates occurred on or after Order Dates (average shipping time: 3.2 days)
    Created an Excel formula to calculate days between dates and identify negative values
  • Outliers in sales and profit figures: Used Interquartile Range (IQR) method to identify potential outliers; confirmed that extremely high-value transactions ($10,000+) were legitimate corporate bulk purchases
    Applied the formula Q3 + 1.5 × IQR to identify upper bounds for outlier detection

All identified issues were documented and addressed before proceeding with analysis, ensuring data integrity throughout the project.

Data Transformation Steps

1. Temporal Enrichment

I created several time-based fields to facilitate different levels of trend analysis:

  • Year: Extracted from Order Date
    Excel Formula
    = YEAR([Order Date])
    Purpose: To enable year-over-year comparisons and annual growth analysis
  • Month: Extracted month name from Order Date
    Excel Formula
    = TEXT([Order Date], "mmmm")
    Purpose: To identify monthly patterns and seasonality using readable month names
  • Month Number: Extracted numeric month (1-12) from Order Date
    Excel Formula
    = MONTH([Order Date])
    Purpose: To enable chronological sorting of months and seasonal analysis
  • Quarter: Calculated from Month using ceiling division
    Excel Formula
    = CEILING(MONTH([Order Date])/3,1)
    Purpose: To facilitate quarterly business reporting and seasonal trend analysis
  • Year-Quarter: Combined Year and Quarter fields
    Excel Formula
    = [Year] & "-Q" & [Quarter]
    Purpose: To track performance across discrete quarters and enable quarter-over-quarter comparisons
  • Month-Year: Formatted for time series visualization
    Excel Formula
    = TEXT([Order Date], "mmm-yy")
    Purpose: To create chronologically sorted x-axis labels in trend visualizations

This temporal structure enabled multi-level time series analysis from granular monthly patterns to long-term annual trends.

2. Financial Metrics

I engineered several financial metrics to provide deeper insights into profitability and pricing:

  • Profit Margin: Calculated for each transaction
    Excel Formula
    = [Profit]/[Sales]
    Purpose: To standardize profit measurement across transactions of different sizes and identify negative-margin sales
  • Discount Range: Categorized discount levels for analysis
    Excel Formula
    = IF([Discount]=0,"None",IF([Discount]<=0.1,"Low (1-10%)",IF([Discount]<=0.3,"Medium (11-30%)","High (>30%)")))
    Purpose: To group similar discount levels and evaluate their impact on margins; breakpoints were chosen based on the company's pricing strategy guidelines
  • Average Order Value: Computed at customer level
    Excel Formula
    = AVERAGEIF([Customer ID], [@Customer ID], [Sales])
    Purpose: To identify high-value customers and understand purchasing patterns across segments
  • Days to Ship: Delivery efficiency metric
    Excel Formula
    = [Ship Date] - [Order Date]
    Purpose: To evaluate operational efficiency and correlate with customer satisfaction metrics

These derived financial metrics provided a framework for profitability analysis beyond simple revenue tracking.

3. Categorical Grouping

I developed several approaches to standardize and group categorical variables:

  • Product Type Classification: Created price-based product groupings
    Excel Formula
    = IF([Sales]/[Quantity]>500, "Premium", IF([Sales]/[Quantity]>100, "Standard", "Economy"))
    Purpose: To evaluate performance across different price points independent of product category
  • Customer Segmentation: Enhanced existing segments with purchasing behavior
    • High Value (>$1000)
    • Medium Value ($500-$1000)
    • Low Value (<$500)
    Excel Formula
    = IF([Average Order Value]>1000, "High Value " & [Segment], IF([Average Order Value]>=500, "Medium Value " & [Segment], "Low Value " & [Segment]))
    Purpose: To identify premium customers within each segment for targeted marketing

These categorical groupings provided additional analytical dimensions beyond the raw data structure.

Data Structure

The "Final Dataset.xlsm" file contains both the original data and the enhanced dataset created through my data preparation process. This Excel workbook is organized into multiple worksheets to facilitate both analysis and documentation:

  • Raw Data: The original 9,994 records with 21 columns, preserved for reference and verification
  • Cleaned Data: The standardized dataset with consistent formatting and validated relationships
  • Enhanced Data: The complete dataset with all derived fields added for analysis
  • Analysis Worksheets: Various pivot tables and summary visualizations used for exploratory analysis
  • Documentation: Detailed notes on data transformation steps and validation procedures

The enhanced dataset includes the original 21 fields plus the following derived columns that enable deeper analysis:

Category Derived Fields Purpose
Temporal Year, Month, Month Number, Quarter, Year-Quarter, Month-Year Time-based analysis across multiple granularities
Financial Profit Margin (%), Discount Range, Average Order Value Standardized profitability metrics and pricing analysis
Operational Order Count (for aggregations), Days to Ship Efficiency metrics and order fulfillment analysis
Categorical Product Type, Customer Value Segment Enhanced grouping for targeted analysis

Each derived field is implemented using Excel formulas with consistent naming conventions and documentation. The workbook includes data validation rules to maintain data integrity and conditional formatting to highlight important patterns and exceptions. Named ranges are used extensively to make formulas more readable and maintainable.

Validation Method: To ensure data integrity throughout the transformation process, I created a dedicated "Validation" worksheet that contains pivot tables comparing key metrics before and after transformation. This includes verification of record counts, sum totals, and distribution checks for categorical variables. Any discrepancies were investigated and resolved before finalizing the dataset.