Wayfair · Data Analytics

E-commerce Sales Analysis

Chethan Reddy BobbaliSee their profile and skills

easy

The work

In [1]:

import pandas as pd

df1 = pd.read_csv("Datasets/customers.csv")
df2 = pd.read_csv("Datasets/order_items.csv")
df3 = pd.read_csv("Datasets/orders.csv")
df4 = pd.read_csv("Datasets/products.csv")

In [2]:

datasets = {
    "customers": df1,
    "order-items":df2,
    "orders":df3,
    "products":df4
}

In [3]:

for name, df in datasets.items():
    print(name)
    print(f"Shape: {df.shape}")
    print()
    print(df.head())
    print()
    print(f"Info: {df.info()}")
    print()
    print(f"Missing Values: {df.isnull().sum()}")
    print()
    print(f"Duplicated Values: {df.duplicated().sum()}")
    print("----------------------------------------")

customers
Shape: (3600, 4)

   customer_id signup_date state acquisition_channel
0            1  2024-09-13    CO      organic_search
1            2  2025-10-21    IL         paid_search
2            3  2025-01-17    AZ            referral
3            4  2024-06-23    FL         paid_social
4            5  2025-04-08    IL         paid_social

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 3600 entries, 0 to 3599
Data columns (total 4 columns):
 #   Column               Non-Null Count  Dtype 
---  ------               --------------  ----- 
 0   customer_id          3600 non-null   int64 
 1   signup_date          3600 non-null   object
 2   state                3600 non-null   object
 3   acquisition_channel  3600 non-null   object
dtypes: int64(1), object(3)
memory usage: 112.6+ KB
Info: None

Missing Values: customer_id            0
signup_date            0
state                  0
acquisition_channel    0
dtype: int64

Duplicated Values: 0
----------------------------------------
order-items
Shape: (15608, 5)

   order_id  product_id  quantity  unit_price  discount_amount
0    100001        1031         1       38.99              0.0
1    100002        1087         1       45.99              0.0
2    100002        1072         1      141.99              0.0
3    100003        1048         1      139.99              0.0
4    100004        1083         1       19.99              2.0

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 15608 entries, 0 to 15607
Data columns (total 5 columns):
 #   Column           Non-Null Count  Dtype  
---  ------           --------------  -----  
 0   order_id         15608 non-null  int64  
 1   product_id       15608 non-null  int64  
 2   quantity         15608 non-null  int64  
 3   unit_price       15409 non-null  float64
 4   discount_amount  15608 non-null  float64
dtypes: float64(2), int64(3)
memory usage: 609.8 KB
Info: None

Missing Values: order_id             0
product_id           0
quantity             0
unit_pri

In [4]:

df1.describe(include='all')

        customer_id signup_date state acquisition_channel
count   3600.000000        3600  3600                3600
unique          NaN         811    35                   6
top             NaN  2025-10-29    CA      organic_search
freq            NaN          17   451                1230
mean    1800.500000         NaN   NaN                 NaN
std     1039.374812         NaN   NaN                 NaN
min        1.000000         NaN   NaN                 NaN
25%      900.750000         NaN   NaN                 NaN
50%     1800.500000         NaN   NaN                 NaN
75%     2700.250000         NaN   NaN                 NaN
max     3600.000000         NaN   NaN                 NaN

In [5]:

df2.describe(include='all')

            order_id    product_id      quantity    unit_price  \
count   15608.000000  15608.000000  15608.000000  15409.000000   
mean   104570.429011   1046.471040      1.244810    113.084088   
std      2659.674531     28.278148      0.577641    108.590935   
min    100001.000000   1001.000000      1.000000      7.990000   
25%    102269.750000   1020.000000      1.000000     49.990000   
50%    104554.500000   1048.000000      1.000000     86.090000   
75%    106867.250000   1073.000000      1.000000    141.740000   
max    109207.000000   1090.000000      4.000000    812.690000   

       discount_amount  
count     15608.000000  
mean          3.201492  
std          11.154638  
min           0.000000  
25%           0.000000  
50%           0.000000  
75%           0.000000  
max         196.560000

In [6]:

df3.describe(include='all')

             order_id  customer_id           order_date     status  \
count     9245.000000  9245.000000                 9245       9245   
unique            NaN          NaN                 9206          3   
top               NaN          NaN  2025-08-05 10:07:36  completed   
freq              NaN          NaN                    2       8490   
mean    104604.530882  1784.054840                  NaN        NaN   
std       2657.471479  1062.469902                  NaN        NaN   
min     100001.000000     2.000000                  NaN        NaN   
25%     102304.000000   863.000000                  NaN        NaN   
50%     104605.000000  1786.000000                  NaN        NaN   
75%     106907.000000  2738.000000                  NaN        NaN   
max     109207.000000  3600.000000                  NaN        NaN   

       payment_method discount_code  shipping_fee  
count            9245          1411   9245.000000  
unique              4             3           NaN  
top       credit_card     HOLIDAY20           NaN  
freq             5332           782           NaN  
mean              NaN           NaN      1.544862  
std               NaN           NaN      2.889826  
min               NaN           NaN      0.000000  
25%               NaN           NaN      0.000000  
50%               NaN           NaN      0.000000  
75%               NaN           NaN      0.000000  
max               NaN           NaN      6.950000

In [7]:

df4.describe(include='all')

         product_id      product_name category  list_price   unit_cost
count     90.000000                90       90   90.000000   90.000000
unique          NaN                90       24         NaN         NaN
top             NaN  Brass Chef Knife  Kitchen         NaN         NaN
freq            NaN                 1       10         NaN         NaN
mean    1045.500000               NaN      NaN  134.378889   69.563222
std       26.124701               NaN      NaN  136.899512   76.820180
min     1001.000000               NaN      NaN    7.990000    4.760000
25%     1023.250000               NaN      NaN   52.490000   24.547500
50%     1045.500000               NaN      NaN   95.490000   49.275000
75%     1067.750000               NaN      NaN  151.490000   74.330000
max     1090.000000               NaN      NaN  773.990000  445.470000

orders has 38 duplicates
In order_items unit_price column has 199 missing values.

In [8]:

df3[df3.duplicated()].head()

      order_id  customer_id           order_date     status payment_method  \
2476    102345           68  2024-08-12 18:24:57  completed    credit_card   
2747    101380         2821  2024-05-15 16:28:12  completed    credit_card   
3222    103209          875  2024-11-03 09:28:56  completed         paypal   
3280    103267         1466  2024-11-07 17:50:28   returned         paypal   
3992    102000          840  2024-07-12 09:50:27  completed    credit_card   

     discount_code  shipping_fee  
2476           NaN          0.00  
2747           NaN          0.00  
3222     WELCOME10          0.00  
3280           NaN          6.95  
3992           NaN          0.00

In [9]:

print("Remaining exact duplicates:", df3.duplicated().sum())
print("Remaining rows:", len(df3))

Remaining exact duplicates: 38
Remaining rows: 9245

In [10]:

#duplicates are dropped
df3.drop_duplicates(inplace=True)

In [11]:

df2[df2.unit_price.isna()]
#the unit price can be done simply on lookup table in excel

       order_id  product_id  quantity  unit_price  discount_amount
55       100033        1086         1         NaN             0.00
110      100062        1020         1         NaN             0.00
197      100116        1087         2         NaN             0.00
366      100210        1014         1         NaN             0.00
626      100361        1077         1         NaN             0.00
...         ...         ...       ...         ...              ...
15205    108954        1088         1         NaN             4.41
15228    108966        1071         2         NaN             4.20
15326    109024        1083         1         NaN             0.00
15354    109043        1074         2         NaN             8.40
15504    109136        1082         1         NaN             0.00

[199 rows x 5 columns]

In [12]:

df3.head()

   order_id  customer_id           order_date     status payment_method  \
0    100001         1530  2024-01-01 09:24:45  completed    credit_card   
1    100002         3531  2024-01-01 10:37:49  completed    credit_card   
2    100003          840  2024-01-01 10:59:09  completed    credit_card   
3    100004          725  2024-01-01 12:48:34  completed      apple_pay   
4    100005         3452  2024-01-01 17:44:03  completed      gift_card   

  discount_code  shipping_fee  
0           NaN          6.95  
1           NaN          0.00  
2           NaN          0.00  
3     WELCOME10          0.00  
4           NaN          0.00

In [13]:

df3.discount_code.unique()

array([nan, 'WELCOME10', 'SPRING15', 'HOLIDAY20'], dtype=object)

In [14]:

df3['discount_code'] = df3["discount_code"].fillna("No Discount")

In [15]:

df3.discount_code.unique()

array(['No Discount', 'WELCOME10', 'SPRING15', 'HOLIDAY20'], dtype=object)

In [16]:

df4.head()

   product_id            product_name  category  list_price  unit_cost
0        1001        Brass Chef Knife   Kitchen       61.99      38.13
1        1002    Walnut Cutting Board   Kitchen      138.99      74.76
2        1003  Matte Black Dutch Oven   Kitchen       22.99      13.50
3        1004         Classic Skillet   Kitchen       75.99      39.86
4        1005   Linen Mixing Bowl Set  Kitchen        79.99      41.11

In [17]:

df4.category.unique()

array(['Kitchen', ' Kitchen', 'Kitchen ', 'kitchen', 'KITCHEN',
       ' Bedding', 'Bedding', 'bedding', 'BEDDING', 'Bath', 'bath',
       'Furniture', 'Furniture ', 'furniture', 'FURNITURE', 'Lighting',
       'LIGHTING', 'Outdoor', 'OUTDOOR', ' Outdoor', 'Decor', 'decor',
       'Storage', 'Storage '], dtype=object)

In [18]:

df4['category'] = df4['category'].str.lower().str.replace(" ", "", regex=True)
df4['category'].unique()

array(['kitchen', 'bedding', 'bath', 'furniture', 'lighting', 'outdoor',
       'decor', 'storage'], dtype=object)

In [19]:

product_lookup = df4.set_index("product_id")["list_price"]
product_lookup

product_id
1001     61.99
1002    138.99
1003     22.99
1004     75.99
1005     79.99
         ...  
1086     97.99
1087     45.99
1088     20.99
1089     53.99
1090     17.99
Name: list_price, Length: 90, dtype: float64

In [20]:

df2["unit_price"] = df2["unit_price"].fillna(
    df2["product_id"].map(product_lookup)
)
df2.isna().sum()

order_id           0
product_id         0
quantity           0
unit_price         0
discount_amount    0
dtype: int64

In [21]:

import pandas as pd
from sqlalchemy import create_engine, text
from sqlalchemy.engine import URL

# MySQL connection details
username = "YOUR-USERNAME"
password = "YOUR-PASSWORD"
host = "localhost"
port = 3306
database = "YOUR-DB"

# Create connection URL safely
connection_url = URL.create(
    drivername="mysql+pymysql",
    username=username,
    password=password,
    host=host,
    port=port,
    database=database
)

# Create SQLAlchemy engine
engine = create_engine(connection_url)

# Test the connection
try:
    with engine.connect() as connection:
        result = connection.execute(text("SELECT VERSION()"))
        print("Connected successfully!")
        print("MySQL version:", result.scalar())

except Exception as e:
    print("Connection failed:", e)

Connected successfully!
MySQL version: 8.0.45

In [22]:

df1.to_sql(
    name="customers",
    con=engine,
    if_exists="replace",
    index=False
)

print("Data uploaded successfully!")

Data uploaded successfully!

In [23]:

df2.to_sql(
    name="order-items",
    con=engine,
    if_exists="replace",
    index=False
)

print("Data uploaded successfully!")

Data uploaded successfully!

In [24]:

df3.to_sql(
    name="orders",
    con=engine,
    if_exists="replace",
    index=False
)

print("Data uploaded successfully!")

Data uploaded successfully!

In [25]:

df4.to_sql(
    name="products",
    con=engine,
    if_exists="replace",
    index=False
)

print("Data uploaded successfully!")

Data uploaded successfully!

Wayfair E-Commerce Sales Analysis

Project Overview
Objectives
Tools & Technologies
Dataset Summary
Project Workflow
- Data Cleaning & Exploratory Data Analysis
- SQL Analysis
- Power BI Dashboard
Key Findings
Business Recommendations
Project Structure
How to Run the Project
Skills Demonstrated
Conclusion

Project Overview

This project analyzes e-commerce sales data from a Wayfair dataset to understand sales performance, customer acquisition, product demand, and order outcomes.

Using Python, MySQL, and Power BI, the project covers data cleaning, exploratory analysis, SQL-based business analysis, and interactive dashboard development to generate actionable business insights.

Objectives

Analyze revenue by product category and customer acquisition channel.
Compare monthly revenue between 2024 and 2025.
Identify best-selling products by units sold and revenue.
Evaluate product category return and cancellation rates.
Calculate Average Order Value (AOV).
Build an interactive Power BI dashboard to communicate key findings.

Tools & Technologies

Python: Data cleaning and exploratory data analysis
Pandas: Data manipulation and missing-value handling
MySQL: Data querying and business analysis
Power BI: Interactive dashboard and data visualization
Jupyter Notebook: Python analysis and documentation

Dataset Summary

The project uses four CSV files containing customer, order, product, and transaction data.

Dataset	Description
`customers.csv`	Customer information, signup dates, states, and acquisition channels
`orders.csv`	Order dates, order status, payment methods, discount codes, and shipping fees
`order_items.csv`	Product IDs, quantities, unit prices, and discount amounts
`products.csv`	Product names, categories, list prices, and unit costs

CSV Files: 4
Total Rows: 28,543 across all files
Total Columns: 21 across all files
Missing Values: 8,033 initially identified across unit_price and discount_code
Duplicates: 38 duplicate rows reported in orders.csv

Project Workflow

1. Data Cleaning & Exploratory Data Analysis

Python and Pandas were used to inspect the datasets and prepare them for analysis.

Key tasks:

Examined dataset structure using info() and describe().
Identified missing values and duplicate records.
Filled missing unit prices using the corresponding product list prices.
Standardized inconsistent product category names.
Replaced blank discount codes with No Discount.
Prepared the cleaned data for MySQL analysis.

2. SQL Analysis

MySQL was used to investigate business questions using joins, aggregations, Common Table Expressions (CTEs), conditional aggregation, and window functions.

The analysis included:

Revenue by Acquisition Channel: Compared customers, orders, orders per customer, and total purchase value across acquisition channels.
Category Performance: Evaluated units sold and revenue by product category.
Order Status Analysis: Examined returned and canceled orders across categories.
Year-over-Year Comparison: Compared monthly revenue between 2024 and 2025.
Best-Selling Products: Ranked products by units sold and revenue.
Return and Cancellation Rates: Calculated category-level return and cancellation percentages.
Average Order Value: Calculated average order value using order-level sales.

3. Power BI Dashboard

An interactive dashboard was developed to present the results visually.

Key Performance Indicators (KPIs):

Total Customers
Total Revenue
Average Order Value
Return Rate
Cancellation Rate

Dashboard Visualizations:

Revenue by state
Revenue by acquisition channel
Revenue by product category
Revenue by payment method
Revenue trends by year and month

Interactive Filters:

Discount Code
Payment Method
Order Status
Acquisition Channel

Key Findings

Category Performance: Furniture generated the highest reported category revenue despite having the lowest unit sales among the eight categories.
Acquisition Channels: Referral recorded 5.29 orders per customer, while Paid Social recorded 2.98 and Affiliate recorded 3.40.
Returns: Furniture showed the highest reported category return rate at 11.77%.
Cancellations: Bath recorded the highest reported category cancellation rate at 4.43%.
Sales Trends: Monthly revenue was compared between 2024 and 2025 to examine changes in sales performance.
Average Order Value: The project report recorded an AOV of 220.21.

Business Recommendations

Reduce Returns: Investigate Furniture first to identify potential product quality, description, or customer expectation issues.
Reduce Cancellations: Examine Bath and other categories with elevated cancellation rates to identify potential operational problems.
Optimize Acquisition Channels: Investigate repeat purchases and customer value for Paid Social and Affiliate before changing marketing budgets.
Improve Category Sales: Investigate low unit sales in Furniture, Lighting, Outdoor, Bath, and Storage.
Promote Best-Selling Products: Use product-level sales and revenue analysis to guide product positioning and marketing campaigns.

Project Structure

Wayfair-Ecommerce-Sales-Analysis/
│
├── customers.csv
├── orders.csv
├── order_items.csv
├── products.csv
│
├── WayFair E-Commerce Sales Analysis.ipynb
├── sales_analysis.sql
├── ecommerce_sales_analysis.pbix
│
└── README.md

How to Run the Project

Prerequisites

Python 3.x
Jupyter Notebook
Pandas
MySQL
Power BI Desktop

Steps

Clone or download this repository.

   git clone https://github.com/bchethan/Wayfair-E-commerce-Sales-Analysis
   cd Wayfair-E-commerce-Sales-Analysis

Install the required Python dependencies:

   pip install pandas jupyter mysql-connector-python

Place the four CSV files in the appropriate project directory.
Open the Jupyter Notebook and run the data inspection and cleaning steps.
Configure your local MySQL connection and load the cleaned tables into the database.
Execute the queries in sales_analysis.sql to reproduce the business analysis.
Open the .pbix file in Power BI Desktop and configure the data source if required.

Important: Keep database credentials outside the notebook and never commit passwords or other secrets to GitHub.

Skills Demonstrated

Data cleaning and preprocessing
Exploratory data analysis
SQL joins and aggregations
CTEs and window functions
Revenue and KPI analysis
Customer acquisition channel analysis
Data visualization and dashboard development
Business insight generation and recommendations

Conclusion

This project demonstrates an end-to-end data analytics workflow, from preparing raw e-commerce data to querying business metrics and presenting findings in an interactive dashboard.

It highlights how Python, SQL, and Power BI can be used together to analyze sales performance, identify operational issues, and support data-driven business decisions.

Author

Chethan Reddy

GitHub: bchethan

The brief this answers

Clean two years of online store orders and turn them into a clear sales review for next year's plan.

Try it yourself

Discussion

Nothing here yet. Ask how they approached it, or tell them what you would have done differently.

Add to the discussion