Wayfair · Data Analytics
E-commerce Sales Analysis
The work
Importing the Required Libraries
- In [1]:
import pandas as pd import numpy as np import matplotlib.pyplot as plt import seaborn as sns 1. Data Audit & Cleaning Blueprint
1.1 Load the data
- In [2]:
customers = pd.read_csv('data/customers.csv', parse_dates=["signup_date"]) order_items = pd.read_csv('data/order_items.csv') orders = pd.read_csv('data/orders.csv', parse_dates=["order_date"]) products = pd.read_csv('data/products.csv') - In [3]:
for name, frame in {"orders": orders, "order_items": order_items, "products": products, "customers": customers}.items(): print(name, frame.shape)orders (9245, 7) order_items (15608, 5) products (90, 5) customers (3600, 4)
1.2 Check Duplicates in the dataset and fix them
- In [4]:
print(f"The Duplicates in Orders: {orders.duplicated().sum()}") print(f"The Duplicates in Order Items: {order_items.duplicated().sum()}") print(f"The Duplicates in Products: {products.duplicated().sum()}") print(f"The Duplicates in Customers: {customers.duplicated().sum()}")The Duplicates in Orders: 38 The Duplicates in Order Items: 0 The Duplicates in Products: 0 The Duplicates in Customers: 0
As you can see we have 38 duplicates in orders and we need to see them to identify what procedure we will take
- In [5]:
orders[orders.duplicated(subset=['order_id'],keep=False)].sort_values(by='order_id')order_id customer_id order_date status payment_method \ 47 100048 3320 2024-01-06 16:37:36 completed credit_card 8560 100048 3320 2024-01-06 16:37:36 completed credit_card 7556 100303 853 2024-02-03 08:33:22 completed apple_pay 303 100303 853 2024-02-03 08:33:22 completed apple_pay 5488 100710 526 2024-03-13 13:39:17 completed credit_card ... ... ... ... ... ... 8617 108585 1995 2025-11-28 12:43:37 completed apple_pay 9104 109068 1981 2025-12-20 13:27:25 completed credit_card 3104 109068 1981 2025-12-20 13:27:25 completed credit_card 4213 109087 3358 2025-12-22 08:35:44 completed credit_card 9124 109087 3358 2025-12-22 08:35:44 completed credit_card discount_code shipping_fee 47 NaN 6.95 8560 NaN 6.95 7556 NaN 0.00 303 NaN 0.00 5488 NaN 0.00 ... ... ... 8617 HOLIDAY20 0.00 9104 HOLIDAY20 6.95 3104 HOLIDAY20 6.95 4213 HOLIDAY20 0.00 9124 HOLIDAY20 0.00 [76 rows x 7 columns] now we know that they are duplicated after we checked the duplicated by order_id column
now we will drop them
- In [6]:
orders_clean= orders.drop_duplicates(subset=['order_id'], keep=False).copy() 1.3 Standardize product categories
- In [7]:
products_clean = products.copy() products_clean['category'] = products_clean['category'].str.strip().str.title() 1.4 Impute missing unit prices from products list_price
- In [8]:
order_items_clean = order_items.copy() order_items_clean = order_items_clean.merge( products_clean[['product_id', 'list_price']], on='product_id', how='left' ) order_items_clean['unit_price'] = order_items_clean['unit_price'].fillna(order_items_clean['list_price']) order_items_clean.drop(columns=['list_price'], inplace=True) 1.5 Compute line-item net revenue
- In [9]:
order_items_clean['item_revenue'] = ( order_items_clean['quantity'] * order_items_clean['unit_price'] ) - order_items_clean['discount_amount'] 2. Business Metric Definition: Revenue & Order Status
In e-commerce reporting, explicitly separating gross sales from realized revenue is critical:Net Sales Revenue: Calculated as the sum of discounted item amounts for orders where status == 'completed'. Canceled orders represent zero realized sales, while returned orders are refunded and tracked separately as operational leakage.Line Item Formula:$$\text{Item Revenue} = (\text{Quantity} \times \text{Unit Price}) - \text{Discount Amount}$$Order Revenue:$$\text{Net Revenue} = \sum_{i \in \text{Completed Items}} \text{Item Revenue}_i$$
2.1 Merge cleaned tables into an analytical dataset
- In [10]:
df = order_items_clean.merge(orders_clean, on='order_id', how='inner') df = df.merge(products_clean, on='product_id', how='inner') 2.2 Filter for completed sales
- In [11]:
df_completed = df[df['status'] == 'completed'].copy() df_completed['month_year'] = df_completed['order_date'].dt.to_period('M') df_completed['year'] = df_completed['order_date'].dt.year df_completed['month'] = df_completed['order_date'].dt.month 3. Monthly Trends & Seasonality (2024 vs. 2025)
The analysis shows steady growth across the two-year period:
Overall Growth: Net revenue grew +21.1% YoY, rising from $850,786.45 in 2024 to $1,030,488.63 in 2025.
Holiday Peak: Revenue experiences a massive annual spike in November (reaching $100.6K in 2024 and $129.9K in 2025) driven by Black Friday / Cyber Monday promotions.
Summer Lift: A secondary seasonal bump occurs between June and August, fueled by outdoor furniture and decor demand.
3.1 Monthly pivot table for YoY comparison
- In [12]:
monthly_rev = df_completed.pivot_table( index='month', columns='year', values='item_revenue', aggfunc='sum' ) monthly_rev['YoY_Growth_%'] = ((monthly_rev[2025] - monthly_rev[2024]) / monthly_rev[2024]) * 100 print(monthly_rev)year 2024 2025 YoY_Growth_% month 1 54327.52 71843.19 32.240879 2 63064.65 69538.05 10.264705 3 71371.42 78398.48 9.845762 4 64768.74 79515.73 22.768684 5 73542.85 83113.31 13.013447 6 66985.40 92184.78 37.619213 7 76707.06 83372.59 8.689591 8 62162.60 90405.55 45.433991 9 60054.02 75165.48 25.163111 10 68387.83 81330.47 18.925356 11 100507.25 129426.08 28.772880 12 86497.00 90924.28 5.118420
4. Category Dynamics, Return Rates, & AOV
Analyzing performance by product category reveals stark differences in average order value (AOV) and return risk:
Top Revenue Drivers: Furniture ($322.8K total) and Kitchen ($279.7K total) generate the highest total revenue.
Fastest Growing Category: Storage led growth at +40.8% YoY ($58.0K in 2024 to $81.6K in 2025), followed by Kitchen (+26.2%).
High-Value vs. High-Return:
Furniture yields the highest AOV ($376.19) but suffers from an 11.72% return rate—nearly triple the site average (4–5%).
Lighting ($208.18 AOV) and Outdoor ($199.21 AOV) maintain strong margins with normal return rates (~4.0% and ~4.4%).
Bath recorded the highest cancellation rate (4.56%).
4.1 Category performance summary
- In [13]:
cat_perf = df_completed.groupby('category').agg( total_revenue=('item_revenue', 'sum'), orders=('order_id', 'nunique') ).reset_index() cat_perf['AOV'] = cat_perf['total_revenue'] / cat_perf['orders'] 4.2 Return & Cancellation rate per category across all orders
- In [14]:
cat_status = df.groupby(['category', 'status'])['order_id'].nunique().unstack(fill_value=0) cat_status['total_orders'] = cat_status.sum(axis=1) cat_status['return_rate_%'] = (cat_status['returned'] / cat_status['total_orders']) * 100 cat_status['cancel_rate_%'] = (cat_status['canceled'] / cat_status['total_orders']) * 100 5. Visualizations for Executive Pitch
Chart 1: Monthly Revenue YoY Trend
- In [15]:
plt.figure(figsize=(8, 5)) monthly_rev[[2024, 2025]].plot(kind='line', marker='o', linewidth=2.5, ax=plt.gca()) plt.title('Monthly Sales Revenue (2024 vs 2025)', fontsize=12, fontweight='bold') plt.ylabel('Net Revenue ($)') plt.xlabel('Month') plt.xticks(range(1, 13)) plt.grid(True, linestyle='--', alpha=0.5) plt.tight_layout() plt.savefig('chart1_monthly_revenue.png', dpi=300) plt.show()<Figure size 800x500 with 1 Axes>
Chart 2: Category Revenue Comparison
- In [16]:
cat_rev = df_completed.groupby(['category', 'year'])['item_revenue'].sum().unstack() plt.figure(figsize=(8, 5)) cat_rev.sort_values(by=2025).plot(kind='barh', color=['#a6cee3', '#1f78b4'], ax=plt.gca()) plt.title('Revenue by Category (2024 vs 2025)', fontsize=12, fontweight='bold') plt.xlabel('Net Revenue ($)') plt.ylabel('Category') plt.grid(True, linestyle='--', alpha=0.5) plt.tight_layout() plt.savefig('chart2_category_revenue.png', dpi=300) plt.show()<Figure size 800x500 with 1 Axes>
Chart 3: Return Rate vs AOV Risk Matrix
- In [17]:
cat_matrix = cat_perf.merge(cat_status[['return_rate_%']], on='category') plt.figure(figsize=(8, 5)) sns.scatterplot(data=cat_matrix, x='AOV', y='return_rate_%', hue='category', s=200, ax=plt.gca()) plt.title('Category Risk Matrix: Return Rate vs AOV', fontsize=12, fontweight='bold') plt.xlabel('Average Order Value ($)') plt.ylabel('Return Rate (%)') plt.grid(True, linestyle='--', alpha=0.5) plt.tight_layout() plt.savefig('chart3_risk_matrix.png', dpi=300) plt.show()<Figure size 800x500 with 1 Axes>
Wayfair Sales Analysis (2024 vs. 2025)
An end-to-end analysis of an e-commerce home-goods retailer's sales data, covering data cleaning, revenue definition, seasonality, category performance, and return/cancellation risk. The goal is to turn raw transactional data into clear, actionable recommendations for the business.
Tools: Python · pandas · NumPy · Matplotlib · Seaborn · Jupyter Notebook
Table of Contents
- Business Context
- Business Questions
- Dataset
- Methodology: What I Did and Why
- Key Findings
- Recommendations
- Limitations and Next Steps
- Repository Structure
- How to Run
Business Context
Wayfaie sells home products (furniture, kitchen, lighting, outdoor, storage, bath, decor and more) online. Leadership wants to understand how the business grew between 2024 and 2025, when and where revenue is earned, and which product categories carry hidden risk through returns and cancellations. This analysis answers those questions and is structured to support an executive pitch.
Business Questions
| # | Question | Why it matters |
|---|---|---|
| 1 | How did net revenue change from 2024 to 2025? | Establishes overall growth and the baseline for planning. |
| 2 | Is there seasonality, and when are the peaks? | Drives inventory, staffing, and marketing calendar decisions. |
| 3 | Which categories generate the most revenue and which are growing fastest? | Shows where to invest and where growth is stalling. |
| 4 | Which categories have the highest Average Order Value (AOV), and what is the return risk attached? | High AOV is only valuable if the revenue is kept. |
| 5 | Which categories suffer the most cancellations and returns? | Identifies operational leakage and product/fulfilment problems. |
Dataset
Four relational tables covering orders placed from January 2024 to December 2025.
| Table | Rows | Key columns |
|---|---|---|
orders.csv | 9,245 | order_id, customer_id, order_date, status, payment_method, discount_code, shipping_fee |
order_items.csv | 15,608 | order_id, product_id, quantity, unit_price, discount_amount |
products.csv | 90 | product_id, product_name, category, list_price, unit_cost |
customers.csv | 3,600 | customer_id, signup_date, state, acquisition_channel |
Order status takes three values: completed, returned, canceled.
Methodology: What I Did and Why
1. Data Audit and Cleaning
| Step | What I did | Why |
|---|---|---|
| Load and validate | Loaded all four tables with date parsing and printed their shapes. | Confirms row counts and that dates are real datetimes before any time-based analysis. |
| Duplicate check | Ran .duplicated() on every table. Only orders had issues: 38 duplicated rows. | Duplicate orders would be counted twice and inflate revenue and order counts. |
| Inspect before fixing | Filtered the duplicates by order_id and reviewed them. They were exact copies, not different orders sharing an ID. | Deleting data should be a decision based on evidence, not a blind rule. |
| Standardize categories | Applied .str.strip().str.title() to products.category. | Prevents the same category from splitting into several groups because of casing or stray whitespace. |
| Impute missing unit prices | Left-joined products.list_price and filled missing order_items.unit_price values with it. | Missing prices would silently turn into zero or NaN revenue. List price is the most defensible substitute. |
| Compute line-item revenue | item_revenue = quantity × unit_price − discount_amount | Creates one consistent, discount-aware revenue measure at the lowest grain, so it can be rolled up by any dimension. |
2. Metric Definition
Gross sales and realized revenue are not the same thing, so I defined them explicitly:
- Net Sales Revenue = sum of
item_revenuefor orders withstatus == 'completed'. - Canceled orders represent zero realized revenue.
- Returned orders are refunded, so they are excluded from revenue and tracked separately as operational leakage.
Defining the metric up front keeps every later number consistent and defensible.
3. Building the Analytical Dataset
Joined order_items → orders → products with inner joins to produce a single analysis-ready table. I then created two views:
df: all orders, used for return and cancellation rates (needs every status in the denominator).df_completed: completed orders only, used for revenue and AOV.
Keeping the two separate avoids the common mistake of calculating return rates on a dataset that has already had returns filtered out.
4. Analysis
- Monthly trend and seasonality: pivoted revenue by month and year and calculated month-by-month YoY growth.
- Category performance: aggregated revenue and unique orders per category and derived
AOV = revenue / orders. - Risk analysis: calculated return rate and cancellation rate per category as a share of all orders in that category.
5. Visualization
Three charts were built for an executive audience: a YoY line chart, a category revenue comparison, and a risk matrix of AOV vs. return rate.
Key Findings
1. Strong, consistent growth
Net revenue grew roughly 21% year over year, from about $848K in 2024 to about $1.03M in 2025. Every single month of 2025 beat the same month of 2024.
2. Clear seasonality
- November is the annual peak (about $100.5K in 2024 and $129.4K in 2025), consistent with Black Friday / Cyber Monday promotions.
- December is also strong in both years, which points to holiday-season demand and the
HOLIDAY20discount code. - Mid-year months (June to August) also run higher than spring and autumn in 2025.
3. Furniture and Kitchen lead revenue, Storage leads growth
- Furniture (~$322.8K) and Kitchen (~$279.7K) are the two biggest revenue drivers.
- Storage grew the fastest at +40.8% (about $58.0K to $81.6K), followed by Kitchen at +26.2%.
4. High value does not mean low risk
| Category | AOV | Return / cancel note |
|---|---|---|
| Furniture | $376.19 (highest) | 11.72% return rate, nearly triple the site average of about 4 to 5% |
| Lighting | $208.18 | ~4.0% return rate, healthy |
| Outdoor | $199.21 | ~4.4% return rate, healthy |
| Bath | n/a | Highest cancellation rate (4.56%) |
Furniture earns the most per order but also loses the most revenue to returns, which makes it the biggest hidden profitability risk.
Recommendations
- Plan the calendar around November and December. Build inventory, fulfilment capacity, and ad budget ahead of the Black Friday peak, which is growing year over year.
- Attack Furniture returns. Investigate return reasons by product (damage in transit, size or colour mismatch, assembly issues). Improve packaging, add dimension guides and room-scale imagery, and consider a quality review on the worst-performing SKUs. Even a modest drop in return rate protects the highest-AOV category.
- Double down on Storage. It is the fastest-growing category with normal risk. Increase assortment, run bundle offers, and cross-sell with Furniture and Kitchen.
- Protect margin in Lighting and Outdoor. They combine solid AOV with low return rates. They are good candidates for promotion without the refund drag seen in Furniture.
- Investigate Bath cancellations. The highest cancellation rate suggests stock availability, delivery-time, or checkout issues worth checking.
- Track returns as a standing KPI. Report return and cancellation rate next to revenue so growth is always judged on revenue kept, not revenue booked.
Limitations and Next Steps
- Seasonality drivers are inferred. Attributing the November spike to Black Friday and the summer lift to outdoor demand is a reasonable hypothesis. Confirm it by breaking revenue down by category and discount code per month.
- Profitability is not yet modelled.
unit_costis available, so the next step is gross margin by category, which would show whether Furniture's returns erase its AOV advantage. - Customer-level analysis. Using
customers.csv(acquisition channel, state, signup date) could reveal repeat-purchase behaviour, channel quality, and regional performance. - Discount effectiveness. Compare discounted vs. full-price orders on volume, AOV, and return rate.
Repository Structure
.
├── data/
│ ├── customers.csv
│ ├── order_items.csv
│ ├── orders.csv
│ └── products.csv
├── Wayfaie_Sales_Analysis.ipynb
├── images/
│ ├── chart1_monthly_revenue.png
│ ├── chart2_category_revenue.png
│ └── chart3_risk_matrix.png
└── README.mdHow to Run
# 1. Clone the repository
git clone <your-repo-url>
cd <your-repo-folder>
# 2. Install dependencies
pip install pandas numpy matplotlib seaborn jupyter
# 3. Launch the notebook
jupyter notebook Wayfaie_Sales_Analysis.ipynbRun the cells from top to bottom. The charts are saved automatically as PNG files in the project folder.
Author
Ahmed H. Hewidi, Data Analyst · Linkedin
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 yourselfOther answers to this brief
The same problem, solved by somebody else.
- Wayfair · Data Analytics
E-commerce Sales Analysis
Clean two years of online store orders and turn them into a clear sales review for next year's plan.
- Data cleaning
- pandas
- Time series trends
- easy
22NA1A0554 LIMAT0
- Wayfair · Data Analytics
E-commerce Sales Analysis
Clean two years of online store orders and turn them into a clear sales review for next year's plan.
- Data cleaning
- pandas
- Time series trends
- easy
Harshita Soni0
- Wayfair · Data Analytics
E-commerce Sales Analysis
Clean two years of online store orders and turn them into a clear sales review for next year's plan.
- Data cleaning
- pandas
- Time series trends
- easy
V I N A Y J A G T A P0
Discussion
Nothing here yet. Ask how they approached it, or tell them what you would have done differently.