E-commerce Sales Analysis Project — Complete Python Tutorial

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
from datetime import datetime

# Load dataset
df = pd.read_csv('online_retail.csv', encoding='latin1')

# Basic inspection
print(f"Dataset shape: {df.shape}")
print(f"\nColumn types:\n{df.dtypes}")
print(f"\nMissing values:\n{df.isnull().sum()}")
print(f"\nFirst few rows:\n{df.head()}")

# 1. Remove rows with missing CustomerID (can't do customer analysis without it)
print(f"Before: {len(df)} rows")
df = df[df['CustomerID'].notna()]
print(f"After removing nulls: {len(df)} rows")

# 2. Remove canceled orders (InvoiceNo starts with 'C')
df = df[~df['InvoiceNo'].astype(str).str.startswith('C')]
print(f"After removing cancellations: {len(df)} rows")

# 3. Remove invalid quantities and prices
df = df[(df['Quantity'] > 0) & (df['UnitPrice'] > 0)]
print(f"After removing invalid values: {len(df)} rows")

# 4. Convert InvoiceDate to datetime
df['InvoiceDate'] = pd.to_datetime(df['InvoiceDate'])

# 5. Create calculated columns
df['TotalPrice'] = df['Quantity'] * df['UnitPrice']
df['Year'] = df['InvoiceDate'].dt.year
df['Month'] = df['InvoiceDate'].dt.month
df['YearMonth'] = df['InvoiceDate'].dt.to_period('M')

# 6. Remove outliers (orders > 99th percentile)
upper_limit = df['TotalPrice'].quantile(0.99)
df = df[df['TotalPrice'] <= upper_limit]

print(f"\nFinal dataset: {len(df)} rows")
print(f"Date range: {df['InvoiceDate'].min()} to {df['InvoiceDate'].max()}")
print(f"Unique customers: {df['CustomerID'].nunique()}")
print(f"Unique products: {df['StockCode'].nunique()}")

df.to_csv('cleaned_data.csv', index=False)
print("Cleaned data saved!")

# Set analysis date (day after last transaction)
analysis_date = df['InvoiceDate'].max() + pd.Timedelta(days=1)

# Calculate RFM for each customer
rfm = df.groupby('CustomerID').agg({
    'InvoiceDate': lambda x: (analysis_date - x.max()).days,  # Recency
    'InvoiceNo': 'nunique',  # Frequency
    'TotalPrice': 'sum'  # Monetary
}).reset_index()

rfm.columns = ['CustomerID', 'Recency', 'Frequency', 'Monetary']

print(rfm.head())
print(f"\nRFM Summary:\n{rfm.describe()}")

# Score each metric (5 = best, 1 = worst)
# Recency: lower is better (recent purchase = 5)
# Frequency & Monetary: higher is better

rfm['R_Score'] = pd.qcut(rfm['Recency'], q=5, labels=[5,4,3,2,1])
rfm['F_Score'] = pd.qcut(rfm['Frequency'].rank(method='first'), q=5, labels=[1,2,3,4,5])
rfm['M_Score'] = pd.qcut(rfm['Monetary'], q=5, labels=[1,2,3,4,5])

# Combine into RFM Score
rfm['RFM_Score'] = rfm['R_Score'].astype(str) + rfm['F_Score'].astype(str) + rfm['M_Score'].astype(str)

# Simplified segment (high/medium/low)
rfm['RFM_Total'] = rfm[['R_Score', 'F_Score', 'M_Score']].astype(int).sum(axis=1)

def segment_customer(score):
    if score >= 12:
        return 'Champions'
    elif score >= 9:
        return 'Loyal'
    elif score >= 6:
        return 'Potential'
    else:
        return 'At Risk'

rfm['Segment'] = rfm['RFM_Total'].apply(segment_customer)

print(rfm['Segment'].value_counts())
print(f"\nSegment Revenue:\n{rfm.groupby('Segment')['Monetary'].agg(['count', 'sum', 'mean'])}")

# Segment distribution
plt.figure(figsize=(10, 6))
segment_counts = rfm['Segment'].value_counts()
plt.bar(segment_counts.index, segment_counts.values, color=['#2ecc71', '#3498db', '#f39c12', '#e74c3c'])
plt.title('Customer Segmentation by RFM', fontsize=14, fontweight='bold')
plt.xlabel('Segment')
plt.ylabel('Number of Customers')
plt.xticks(rotation=45)
for i, v in enumerate(segment_counts.values):
    plt.text(i, v + 50, str(v), ha='center', fontweight='bold')
plt.tight_layout()
plt.savefig('rfm_segments.png', dpi=300)
plt.show()

# Revenue by segment
segment_revenue = rfm.groupby('Segment')['Monetary'].sum().sort_values(ascending=False)
plt.figure(figsize=(10, 6))
plt.bar(segment_revenue.index, segment_revenue.values, color=['#2ecc71', '#3498db', '#f39c12', '#e74c3c'])
plt.title('Total Revenue by Customer Segment', fontsize=14, fontweight='bold')
plt.xlabel('Segment')
plt.ylabel('Revenue (£)')
plt.xticks(rotation=45)
plt.ticklabel_format(style='plain', axis='y')
plt.tight_layout()
plt.savefig('segment_revenue.png', dpi=300)
plt.show()

# Identify first purchase date for each customer
df['CohortMonth'] = df.groupby('CustomerID')['InvoiceDate'].transform('min').dt.to_period('M')

# Calculate months since first purchase
df['CohortIndex'] = (df['YearMonth'] - df['CohortMonth']).apply(lambda x: x.n)

# Create cohort table
cohort_data = df.groupby(['CohortMonth', 'CohortIndex'])['CustomerID'].nunique().reset_index()
cohort_pivot = cohort_data.pivot(index='CohortMonth', columns='CohortIndex', values='CustomerID')

print("Cohort Table (Customer Counts):")
print(cohort_pivot)

# Retention rate = customers in month N / customers in month 0
cohort_size = cohort_pivot.iloc[:, 0]
retention = cohort_pivot.divide(cohort_size, axis=0) * 100

print("\nRetention Rates (%):")
print(retention.round(1))

plt.figure(figsize=(12, 8))
sns.heatmap(retention, annot=True, fmt='.0f', cmap='RdYlGn', vmin=0, vmax=100, cbar_kws={'label': 'Retention %'})
plt.title('Cohort Retention Heatmap', fontsize=16, fontweight='bold')
plt.xlabel('Months Since First Purchase')
plt.ylabel('Cohort Month')
plt.tight_layout()
plt.savefig('cohort_retention.png', dpi=300)
plt.show()

-- Top 20 customers by total spend
SELECT
    CustomerID,
    COUNT(DISTINCT InvoiceNo) AS total_orders,
    SUM(Quantity) AS total_items,
    SUM(Quantity * UnitPrice) AS total_revenue
FROM online_retail
WHERE CustomerID IS NOT NULL
GROUP BY CustomerID
ORDER BY total_revenue DESC
LIMIT 20;

-- Revenue and orders by month
SELECT
    DATE_TRUNC('month', InvoiceDate) AS month,
    COUNT(DISTINCT InvoiceNo) AS orders,
    COUNT(DISTINCT CustomerID) AS unique_customers,
    SUM(Quantity * UnitPrice) AS revenue,
    SUM(Quantity * UnitPrice) / COUNT(DISTINCT InvoiceNo) AS avg_order_value
FROM online_retail
WHERE CustomerID IS NOT NULL
    AND Quantity > 0
    AND UnitPrice > 0
GROUP BY month
ORDER BY month;

-- Products frequently bought together
WITH product_pairs AS (
    SELECT
        a.InvoiceNo,
        a.Description AS product_a,
        b.Description AS product_b
    FROM online_retail a
    JOIN online_retail b ON a.InvoiceNo = b.InvoiceNo AND a.StockCode < b.StockCode
    WHERE a.CustomerID IS NOT NULL
)

SELECT
    product_a,
    product_b,
    COUNT(*) AS pair_count
FROM product_pairs
GROUP BY product_a, product_b
HAVING COUNT(*) > 50
ORDER BY pair_count DESC
LIMIT 20;

Total Revenue = SUM(online_retail[TotalPrice])
Total Orders = DISTINCTCOUNT(online_retail[InvoiceNo])
Unique Customers = DISTINCTCOUNT(online_retail[CustomerID])
Avg Order Value = [Total Revenue] / [Total Orders]