RSK World - E-commerce Customer Dataset - Project Files Browser | RSK World

queries.sql

-- ============================================================================
-- E-commerce Customer Dataset - SQL Queries
-- ============================================================================
-- Author: RSK World
-- Website: https://rskworld.in
-- Email: help@rskworld.in
-- Phone: +91 93305 39277
-- ============================================================================

-- Create table structure (Enhanced with 40 features)
CREATE TABLE IF NOT EXISTS ecommerce_customers (
    customer_id INT PRIMARY KEY,
    age INT,
    gender VARCHAR(10),
    annual_income DECIMAL(10, 2),
    spending_score INT,
    purchase_frequency INT,
    avg_order_value DECIMAL(10, 2),
    total_purchases INT,
    browsing_time_minutes INT,
    product_category_preference VARCHAR(50),
    device_type VARCHAR(20),
    last_purchase_days INT,
    segment VARCHAR(20),
    customer_lifetime_value DECIMAL(10, 2),
    return_rate DECIMAL(5, 3),
    payment_method VARCHAR(50),
    newsletter_subscribed VARCHAR(3),
    social_media_engagement INT,
    avg_review_rating DECIMAL(3, 1),
    cart_abandonment_rate DECIMAL(5, 3),
    discount_usage_pct DECIMAL(5, 3),
    referral_source VARCHAR(50),
    customer_satisfaction_score DECIMAL(3, 1),
    preferred_shopping_hour INT,
    mobile_app_user VARCHAR(3),
    wishlist_items INT,
    customer_since_months INT,
    loyalty_tier VARCHAR(20),
    email_open_rate DECIMAL(5, 2),
    click_through_rate DECIMAL(5, 2),
    cross_category_purchases INT,
    repeat_purchase_rate DECIMAL(5, 2),
    avg_session_duration INT,
    pages_per_session INT,
    geographic_region VARCHAR(20),
    preferred_shipping VARCHAR(20),
    support_interactions INT,
    product_reviews_count INT,
    social_shares INT,
    coupon_redemptions INT
);

-- ============================================================================
-- BASIC QUERIES
-- ============================================================================

-- 1. Get all customers
SELECT * FROM ecommerce_customers;

-- 2. Count total customers
SELECT COUNT(*) AS total_customers FROM ecommerce_customers;

-- 3. Get customer statistics
SELECT 
    COUNT(*) AS total_customers,
    AVG(age) AS avg_age,
    AVG(annual_income) AS avg_annual_income,
    AVG(spending_score) AS avg_spending_score,
    AVG(purchase_frequency) AS avg_purchase_frequency,
    AVG(avg_order_value) AS avg_order_value,
    SUM(total_purchases) AS total_purchases_all_customers
FROM ecommerce_customers;

-- ============================================================================
-- SEGMENTATION QUERIES
-- ============================================================================

-- 4. Customer segment distribution
SELECT 
    segment,
    COUNT(*) AS customer_count,
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM ecommerce_customers), 2) AS percentage
FROM ecommerce_customers
GROUP BY segment
ORDER BY customer_count DESC;

-- 5. High value customers
SELECT 
    customer_id,
    age,
    gender,
    annual_income,
    spending_score,
    purchase_frequency,
    avg_order_value,
    product_category_preference
FROM ecommerce_customers
WHERE segment = 'High Value'
ORDER BY spending_score DESC;

-- 6. Segment statistics
SELECT 
    segment,
    COUNT(*) AS count,
    AVG(annual_income) AS avg_income,
    AVG(spending_score) AS avg_spending_score,
    AVG(purchase_frequency) AS avg_purchase_frequency,
    AVG(avg_order_value) AS avg_order_value,
    AVG(browsing_time_minutes) AS avg_browsing_time
FROM ecommerce_customers
GROUP BY segment
ORDER BY avg_spending_score DESC;

-- ============================================================================
-- GENDER ANALYSIS
-- ============================================================================

-- 7. Gender distribution
SELECT 
    gender,
    COUNT(*) AS count,
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM ecommerce_customers), 2) AS percentage,
    AVG(spending_score) AS avg_spending_score,
    AVG(purchase_frequency) AS avg_purchase_frequency,
    AVG(avg_order_value) AS avg_order_value
FROM ecommerce_customers
GROUP BY gender;

-- 8. Gender by segment
SELECT 
    gender,
    segment,
    COUNT(*) AS count,
    AVG(spending_score) AS avg_spending_score
FROM ecommerce_customers
GROUP BY gender, segment
ORDER BY gender, segment;

-- ============================================================================
-- PRODUCT PREFERENCE ANALYSIS
-- ============================================================================

-- 9. Product category preferences
SELECT 
    product_category_preference,
    COUNT(*) AS customer_count,
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM ecommerce_customers), 2) AS percentage,
    AVG(spending_score) AS avg_spending_score,
    AVG(purchase_frequency) AS avg_purchase_frequency,
    AVG(avg_order_value) AS avg_order_value
FROM ecommerce_customers
GROUP BY product_category_preference
ORDER BY customer_count DESC;

-- 10. Product category by segment
SELECT 
    product_category_preference,
    segment,
    COUNT(*) AS count,
    AVG(spending_score) AS avg_spending_score
FROM ecommerce_customers
GROUP BY product_category_preference, segment
ORDER BY product_category_preference, segment;

-- ============================================================================
-- DEVICE ANALYSIS
-- ============================================================================

-- 11. Device type distribution
SELECT 
    device_type,
    COUNT(*) AS customer_count,
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM ecommerce_customers), 2) AS percentage,
    AVG(browsing_time_minutes) AS avg_browsing_time,
    AVG(purchase_frequency) AS avg_purchase_frequency,
    AVG(spending_score) AS avg_spending_score
FROM ecommerce_customers
GROUP BY device_type
ORDER BY customer_count DESC;

-- 12. Device type by product category
SELECT 
    device_type,
    product_category_preference,
    COUNT(*) AS count,
    AVG(spending_score) AS avg_spending_score
FROM ecommerce_customers
GROUP BY device_type, product_category_preference
ORDER BY device_type, product_category_preference;

-- ============================================================================
-- AGE ANALYSIS
-- ============================================================================

-- 13. Age group analysis
SELECT 
    CASE 
        WHEN age < 25 THEN 'Young (0-24)'
        WHEN age < 35 THEN 'Adult (25-34)'
        WHEN age < 50 THEN 'Middle (35-49)'
        ELSE 'Senior (50+)'
    END AS age_group,
    COUNT(*) AS count,
    AVG(spending_score) AS avg_spending_score,
    AVG(purchase_frequency) AS avg_purchase_frequency,
    AVG(avg_order_value) AS avg_order_value
FROM ecommerce_customers
GROUP BY age_group
ORDER BY 
    CASE age_group
        WHEN 'Young (0-24)' THEN 1
        WHEN 'Adult (25-34)' THEN 2
        WHEN 'Middle (35-49)' THEN 3
        ELSE 4
    END;

-- 14. Age and income correlation
SELECT 
    CASE 
        WHEN age < 25 THEN 'Young (0-24)'
        WHEN age < 35 THEN 'Adult (25-34)'
        WHEN age < 50 THEN 'Middle (35-49)'
        ELSE 'Senior (50+)'
    END AS age_group,
    AVG(annual_income) AS avg_income,
    AVG(spending_score) AS avg_spending_score
FROM ecommerce_customers
GROUP BY age_group
ORDER BY avg_income DESC;

-- ============================================================================
-- PURCHASE BEHAVIOR ANALYSIS
-- ============================================================================

-- 15. Top customers by spending score
SELECT 
    customer_id,
    age,
    gender,
    annual_income,
    spending_score,
    purchase_frequency,
    avg_order_value,
    segment
FROM ecommerce_customers
ORDER BY spending_score DESC
LIMIT 10;

-- 16. Customers with high purchase frequency
SELECT 
    customer_id,
    age,
    gender,
    purchase_frequency,
    total_purchases,
    avg_order_value,
    spending_score,
    segment
FROM ecommerce_customers
WHERE purchase_frequency >= 15
ORDER BY purchase_frequency DESC;

-- 17. Recent purchasers (last 7 days)
SELECT 
    customer_id,
    age,
    gender,
    last_purchase_days,
    spending_score,
    purchase_frequency,
    segment
FROM ecommerce_customers
WHERE last_purchase_days <= 7
ORDER BY last_purchase_days ASC;

-- 18. Customers with high average order value
SELECT 
    customer_id,
    age,
    gender,
    avg_order_value,
    purchase_frequency,
    total_purchases,
    annual_income,
    segment
FROM ecommerce_customers
WHERE avg_order_value >= 200
ORDER BY avg_order_value DESC;

-- ============================================================================
-- BROWSING BEHAVIOR ANALYSIS
-- ============================================================================

-- 19. Browsing time analysis
SELECT 
    CASE 
        WHEN browsing_time_minutes < 30 THEN 'Low (< 30 min)'
        WHEN browsing_time_minutes < 100 THEN 'Medium (30-99 min)'
        WHEN browsing_time_minutes < 200 THEN 'High (100-199 min)'
        ELSE 'Very High (200+ min)'
    END AS browsing_category,
    COUNT(*) AS count,
    AVG(spending_score) AS avg_spending_score,
    AVG(purchase_frequency) AS avg_purchase_frequency
FROM ecommerce_customers
GROUP BY browsing_category
ORDER BY 
    CASE browsing_category
        WHEN 'Low (< 30 min)' THEN 1
        WHEN 'Medium (30-99 min)' THEN 2
        WHEN 'High (100-199 min)' THEN 3
        ELSE 4
    END;

-- 20. Browsing time vs purchase frequency correlation
SELECT 
    device_type,
    AVG(browsing_time_minutes) AS avg_browsing_time,
    AVG(purchase_frequency) AS avg_purchase_frequency,
    AVG(spending_score) AS avg_spending_score
FROM ecommerce_customers
GROUP BY device_type
ORDER BY avg_browsing_time DESC;

-- ============================================================================
-- ADVANCED ANALYTICAL QUERIES
-- ============================================================================

-- 21. Customer lifetime value estimation
SELECT 
    segment,
    COUNT(*) AS customer_count,
    AVG(avg_order_value * purchase_frequency) AS estimated_clv,
    SUM(avg_order_value * purchase_frequency) AS total_clv
FROM ecommerce_customers
GROUP BY segment
ORDER BY estimated_clv DESC;

-- 22. Income vs spending analysis
SELECT 
    CASE 
        WHEN annual_income < 20000 THEN 'Low Income (< 20k)'
        WHEN annual_income < 30000 THEN 'Medium Income (20k-29k)'
        WHEN annual_income < 40000 THEN 'High Income (30k-39k)'
        ELSE 'Very High Income (40k+)'
    END AS income_category,
    COUNT(*) AS count,
    AVG(spending_score) AS avg_spending_score,
    AVG(purchase_frequency) AS avg_purchase_frequency,
    AVG(avg_order_value) AS avg_order_value
FROM ecommerce_customers
GROUP BY income_category
ORDER BY 
    CASE income_category
        WHEN 'Low Income (< 20k)' THEN 1
        WHEN 'Medium Income (20k-29k)' THEN 2
        WHEN 'High Income (30k-39k)' THEN 3
        ELSE 4
    END;

-- 23. Comprehensive customer profile
SELECT 
    segment,
    gender,
    product_category_preference,
    device_type,
    COUNT(*) AS count,
    AVG(age) AS avg_age,
    AVG(annual_income) AS avg_income,
    AVG(spending_score) AS avg_spending_score,
    AVG(purchase_frequency) AS avg_purchase_frequency,
    AVG(avg_order_value) AS avg_order_value,
    AVG(browsing_time_minutes) AS avg_browsing_time
FROM ecommerce_customers
GROUP BY segment, gender, product_category_preference, device_type
ORDER BY segment, count DESC;

-- 24. Churn risk analysis (customers with long time since last purchase)
SELECT 
    customer_id,
    age,
    gender,
    last_purchase_days,
    purchase_frequency,
    spending_score,
    segment,
    CASE 
        WHEN last_purchase_days > 45 THEN 'High Risk'
        WHEN last_purchase_days > 30 THEN 'Medium Risk'
        ELSE 'Low Risk'
    END AS churn_risk
FROM ecommerce_customers
ORDER BY last_purchase_days DESC;

-- 25. Product recommendation candidates (high spending, low frequency in category)
SELECT 
    customer_id,
    age,
    gender,
    product_category_preference,
    purchase_frequency,
    spending_score,
    avg_order_value,
    segment
FROM ecommerce_customers
WHERE spending_score > 70 
  AND purchase_frequency < 10
ORDER BY spending_score DESC;

-- ============================================================================
-- ENHANCED FEATURES QUERIES
-- ============================================================================

-- 26. Customer Lifetime Value Analysis
SELECT 
    segment,
    COUNT(*) AS customer_count,
    AVG(customer_lifetime_value) AS avg_clv,
    SUM(customer_lifetime_value) AS total_clv,
    MAX(customer_lifetime_value) AS max_clv,
    MIN(customer_lifetime_value) AS min_clv
FROM ecommerce_customers
GROUP BY segment
ORDER BY avg_clv DESC;

-- 27. Payment Method Preferences
SELECT 
    payment_method,
    COUNT(*) AS customer_count,
    AVG(avg_order_value) AS avg_order_value,
    AVG(spending_score) AS avg_spending_score,
    AVG(customer_lifetime_value) AS avg_clv
FROM ecommerce_customers
GROUP BY payment_method
ORDER BY customer_count DESC;

-- 28. Loyalty Tier Analysis
SELECT 
    loyalty_tier,
    COUNT(*) AS customer_count,
    AVG(customer_lifetime_value) AS avg_clv,
    AVG(repeat_purchase_rate) AS avg_repeat_rate,
    AVG(spending_score) AS avg_spending_score,
    AVG(customer_since_months) AS avg_tenure_months
FROM ecommerce_customers
GROUP BY loyalty_tier
ORDER BY 
    CASE loyalty_tier
        WHEN 'Diamond' THEN 1
        WHEN 'Platinum' THEN 2
        WHEN 'Gold' THEN 3
        WHEN 'Silver' THEN 4
        WHEN 'Bronze' THEN 5
    END;

-- 29. Newsletter Subscription Impact
SELECT 
    newsletter_subscribed,
    COUNT(*) AS customer_count,
    AVG(email_open_rate) AS avg_open_rate,
    AVG(click_through_rate) AS avg_ctr,
    AVG(spending_score) AS avg_spending_score,
    AVG(purchase_frequency) AS avg_purchase_frequency,
    AVG(customer_lifetime_value) AS avg_clv
FROM ecommerce_customers
GROUP BY newsletter_subscribed;

-- 30. Social Media Engagement Analysis
SELECT 
    segment,
    AVG(social_media_engagement) AS avg_engagement,
    AVG(social_shares) AS avg_shares,
    AVG(spending_score) AS avg_spending_score,
    COUNT(*) AS customer_count
FROM ecommerce_customers
GROUP BY segment
ORDER BY avg_engagement DESC;

-- 31. Customer Satisfaction by Segment
SELECT 
    segment,
    AVG(customer_satisfaction_score) AS avg_satisfaction,
    AVG(avg_review_rating) AS avg_review_rating,
    AVG(product_reviews_count) AS avg_reviews_count,
    COUNT(*) AS customer_count
FROM ecommerce_customers
GROUP BY segment
ORDER BY avg_satisfaction DESC;

-- 32. Geographic Region Analysis
SELECT 
    geographic_region,
    COUNT(*) AS customer_count,
    AVG(spending_score) AS avg_spending_score,
    AVG(customer_lifetime_value) AS avg_clv,
    AVG(purchase_frequency) AS avg_purchase_frequency,
    AVG(annual_income) AS avg_income
FROM ecommerce_customers
GROUP BY geographic_region
ORDER BY avg_clv DESC;

-- 33. Referral Source Performance
SELECT 
    referral_source,
    COUNT(*) AS customer_count,
    AVG(spending_score) AS avg_spending_score,
    AVG(customer_lifetime_value) AS avg_clv,
    AVG(customer_satisfaction_score) AS avg_satisfaction,
    AVG(repeat_purchase_rate) AS avg_repeat_rate
FROM ecommerce_customers
GROUP BY referral_source
ORDER BY avg_clv DESC;

-- 34. Mobile App Users Analysis
SELECT 
    mobile_app_user,
    COUNT(*) AS customer_count,
    AVG(spending_score) AS avg_spending_score,
    AVG(purchase_frequency) AS avg_purchase_frequency,
    AVG(browsing_time_minutes) AS avg_browsing_time,
    AVG(customer_lifetime_value) AS avg_clv
FROM ecommerce_customers
GROUP BY mobile_app_user;

-- 35. Cart Abandonment Analysis
SELECT 
    segment,
    AVG(cart_abandonment_rate) AS avg_abandonment_rate,
    AVG(spending_score) AS avg_spending_score,
    AVG(purchase_frequency) AS avg_purchase_frequency,
    COUNT(*) AS customer_count
FROM ecommerce_customers
GROUP BY segment
ORDER BY avg_abandonment_rate DESC;

-- 36. Discount Usage Patterns
SELECT 
    segment,
    AVG(discount_usage_pct) AS avg_discount_usage,
    AVG(coupon_redemptions) AS avg_coupon_redemptions,
    AVG(spending_score) AS avg_spending_score,
    AVG(avg_order_value) AS avg_order_value
FROM ecommerce_customers
GROUP BY segment
ORDER BY avg_discount_usage DESC;

-- 37. Return Rate Analysis
SELECT 
    segment,
    AVG(return_rate) AS avg_return_rate,
    AVG(customer_satisfaction_score) AS avg_satisfaction,
    AVG(avg_review_rating) AS avg_review_rating,
    COUNT(*) AS customer_count
FROM ecommerce_customers
GROUP BY segment
ORDER BY avg_return_rate DESC;

-- 38. Preferred Shopping Hours
SELECT 
    preferred_shopping_hour,
    COUNT(*) AS customer_count,
    AVG(spending_score) AS avg_spending_score,
    AVG(purchase_frequency) AS avg_purchase_frequency
FROM ecommerce_customers
GROUP BY preferred_shopping_hour
ORDER BY preferred_shopping_hour;

-- 39. Wishlist Analysis
SELECT 
    segment,
    AVG(wishlist_items) AS avg_wishlist_items,
    AVG(spending_score) AS avg_spending_score,
    AVG(purchase_frequency) AS avg_purchase_frequency,
    COUNT(*) AS customer_count
FROM ecommerce_customers
GROUP BY segment
ORDER BY avg_wishlist_items DESC;

-- 40. Cross-Category Purchase Analysis
SELECT 
    cross_category_purchases,
    COUNT(*) AS customer_count,
    AVG(spending_score) AS avg_spending_score,
    AVG(customer_lifetime_value) AS avg_clv,
    AVG(purchase_frequency) AS avg_purchase_frequency
FROM ecommerce_customers
GROUP BY cross_category_purchases
ORDER BY cross_category_purchases DESC;

-- 41. Customer Support Interactions
SELECT 
    segment,
    AVG(support_interactions) AS avg_support_interactions,
    AVG(customer_satisfaction_score) AS avg_satisfaction,
    AVG(return_rate) AS avg_return_rate,
    COUNT(*) AS customer_count
FROM ecommerce_customers
GROUP BY segment
ORDER BY avg_support_interactions DESC;

-- 42. Shipping Preference Analysis
SELECT 
    preferred_shipping,
    COUNT(*) AS customer_count,
    AVG(avg_order_value) AS avg_order_value,
    AVG(spending_score) AS avg_spending_score,
    AVG(customer_lifetime_value) AS avg_clv
FROM ecommerce_customers
GROUP BY preferred_shipping
ORDER BY customer_count DESC;

-- 43. Session Behavior Analysis
SELECT 
    segment,
    AVG(avg_session_duration) AS avg_session_duration_seconds,
    AVG(pages_per_session) AS avg_pages_per_session,
    AVG(browsing_time_minutes) AS avg_browsing_time_minutes,
    AVG(spending_score) AS avg_spending_score
FROM ecommerce_customers
GROUP BY segment
ORDER BY avg_session_duration_seconds DESC;

-- 44. Repeat Purchase Rate Analysis
SELECT 
    segment,
    AVG(repeat_purchase_rate) AS avg_repeat_rate,
    AVG(customer_since_months) AS avg_tenure_months,
    AVG(customer_lifetime_value) AS avg_clv,
    COUNT(*) AS customer_count
FROM ecommerce_customers
GROUP BY segment
ORDER BY avg_repeat_rate DESC;

-- 45. Comprehensive Customer Profile (Enhanced)
SELECT 
    segment,
    loyalty_tier,
    payment_method,
    geographic_region,
    COUNT(*) AS customer_count,
    AVG(age) AS avg_age,
    AVG(annual_income) AS avg_income,
    AVG(spending_score) AS avg_spending_score,
    AVG(customer_lifetime_value) AS avg_clv,
    AVG(customer_satisfaction_score) AS avg_satisfaction,
    AVG(repeat_purchase_rate) AS avg_repeat_rate
FROM ecommerce_customers
GROUP BY segment, loyalty_tier, payment_method, geographic_region
ORDER BY segment, avg_clv DESC;

-- 46. High-Value Customer Characteristics
SELECT 
    customer_id,
    age,
    gender,
    loyalty_tier,
    customer_lifetime_value,
    spending_score,
    repeat_purchase_rate,
    customer_satisfaction_score,
    newsletter_subscribed,
    mobile_app_user,
    cross_category_purchases
FROM ecommerce_customers
WHERE segment = 'High Value'
ORDER BY customer_lifetime_value DESC
LIMIT 20;

-- 47. Email Marketing Effectiveness
SELECT 
    newsletter_subscribed,
    AVG(email_open_rate) AS avg_open_rate,
    AVG(click_through_rate) AS avg_ctr,
    AVG(purchase_frequency) AS avg_purchase_frequency,
    AVG(spending_score) AS avg_spending_score,
    COUNT(*) AS customer_count
FROM ecommerce_customers
WHERE newsletter_subscribed = 'Yes'
GROUP BY newsletter_subscribed;

-- 48. Customer Retention Metrics
SELECT 
    segment,
    AVG(customer_since_months) AS avg_tenure_months,
    AVG(repeat_purchase_rate) AS avg_repeat_rate,
    AVG(last_purchase_days) AS avg_days_since_purchase,
    COUNT(*) AS customer_count
FROM ecommerce_customers
GROUP BY segment
ORDER BY avg_tenure_months DESC;

-- 49. Product Review Engagement
SELECT 
    segment,
    AVG(product_reviews_count) AS avg_reviews_count,
    AVG(avg_review_rating) AS avg_review_rating,
    AVG(customer_satisfaction_score) AS avg_satisfaction,
    COUNT(*) AS customer_count
FROM ecommerce_customers
GROUP BY segment
ORDER BY avg_reviews_count DESC;

-- 50. Top Performers by Multiple Metrics
SELECT 
    customer_id,
    segment,
    customer_lifetime_value,
    spending_score,
    repeat_purchase_rate,
    customer_satisfaction_score,
    social_media_engagement,
    product_reviews_count,
    loyalty_tier
FROM ecommerce_customers
WHERE customer_lifetime_value > (SELECT AVG(customer_lifetime_value) FROM ecommerce_customers)
  AND spending_score > 70
  AND repeat_purchase_rate > 0.7
ORDER BY customer_lifetime_value DESC;

721 lines•21.5 KB

sql

visualize_data.py

Raw Download

"""
Data Visualization Script for E-commerce Customer Dataset
==========================================================
This script creates comprehensive visualizations including:
- Customer segment distributions
- Income vs spending analysis
- Product preference charts
- Device usage patterns
- Age group analysis

Author: RSK World
Website: https://rskworld.in
Email: help@rskworld.in
Phone: +91 93305 39277
"""

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
import warnings
warnings.filterwarnings('ignore')

# Set style
sns.set_style("whitegrid")
plt.rcParams['figure.figsize'] = (14, 8)
plt.rcParams['font.size'] = 10

def load_data(file_path='ecommerce_customers.csv'):
    """Load the dataset"""
    try:
        df = pd.read_csv(file_path)
        print(f"Dataset loaded: {len(df)} customers")
        return df
    except FileNotFoundError:
        print(f"Error: File '{file_path}' not found")
        return None

def plot_segment_distribution(df):
    """Plot customer segment distribution"""
    fig, axes = plt.subplots(1, 2, figsize=(14, 6))
    
    # Count plot
    segment_counts = df['segment'].value_counts()
    axes[0].bar(segment_counts.index, segment_counts.values, 
                color=['#28a745', '#ffc107', '#6c757d'])
    axes[0].set_title('Customer Segment Distribution', fontsize=14, fontweight='bold')
    axes[0].set_xlabel('Segment')
    axes[0].set_ylabel('Number of Customers')
    axes[0].grid(axis='y', alpha=0.3)
    
    # Pie chart
    colors = ['#28a745', '#ffc107', '#6c757d']
    axes[1].pie(segment_counts.values, labels=segment_counts.index, autopct='%1.1f%%',
                colors=colors, startangle=90)
    axes[1].set_title('Segment Percentage Distribution', fontsize=14, fontweight='bold')
    
    plt.tight_layout()
    plt.savefig('segment_distribution.png', dpi=300, bbox_inches='tight')
    print("Saved: segment_distribution.png")
    plt.close()

def plot_income_vs_spending(df):
    """Plot income vs spending score"""
    fig, ax = plt.subplots(figsize=(12, 8))
    
    # Scatter plot with segment colors
    segment_colors = {'High Value': '#28a745', 'Medium Value': '#ffc107', 'Low Value': '#6c757d'}
    for segment in df['segment'].unique():
        segment_data = df[df['segment'] == segment]
        ax.scatter(segment_data['annual_income'], segment_data['spending_score'],
                  c=segment_colors[segment], label=segment, alpha=0.6, s=100)
    
    ax.set_xlabel('Annual Income ($)', fontsize=12)
    ax.set_ylabel('Spending Score', fontsize=12)
    ax.set_title('Annual Income vs Spending Score by Segment', fontsize=14, fontweight='bold')
    ax.legend()
    ax.grid(alpha=0.3)
    
    plt.tight_layout()
    plt.savefig('income_vs_spending.png', dpi=300, bbox_inches='tight')
    print("Saved: income_vs_spending.png")
    plt.close()

def plot_product_preferences(df):
    """Plot product category preferences"""
    fig, axes = plt.subplots(1, 2, figsize=(14, 6))
    
    # Category distribution
    category_counts = df['product_category_preference'].value_counts()
    # Use a color palette that works for any number of categories
    colors = plt.cm.Set3(range(len(category_counts)))
    axes[0].bar(category_counts.index, category_counts.values, color=colors)
    axes[0].set_title('Product Category Preferences', fontsize=14, fontweight='bold')
    axes[0].set_xlabel('Category')
    axes[0].set_ylabel('Number of Customers')
    axes[0].grid(axis='y', alpha=0.3)
    
    # Category by segment
    category_segment = pd.crosstab(df['product_category_preference'], df['segment'])
    category_segment.plot(kind='bar', ax=axes[1], color=['#6c757d', '#ffc107', '#28a745'])
    axes[1].set_title('Product Category by Segment', fontsize=14, fontweight='bold')
    axes[1].set_xlabel('Category')
    axes[1].set_ylabel('Number of Customers')
    axes[1].legend(title='Segment')
    axes[1].grid(axis='y', alpha=0.3)
    axes[1].tick_params(axis='x', rotation=45, ha='right')
    
    plt.tight_layout()
    plt.savefig('product_preferences.png', dpi=300, bbox_inches='tight')
    print("Saved: product_preferences.png")
    plt.close()

def plot_device_usage(df):
    """Plot device type usage patterns"""
    fig, axes = plt.subplots(1, 2, figsize=(14, 6))
    
    # Device distribution
    device_counts = df['device_type'].value_counts()
    axes[0].bar(device_counts.index, device_counts.values, 
                color=['#007bff', '#28a745', '#ffc107'])
    axes[0].set_title('Device Type Distribution', fontsize=14, fontweight='bold')
    axes[0].set_xlabel('Device Type')
    axes[0].set_ylabel('Number of Customers')
    axes[0].grid(axis='y', alpha=0.3)
    
    # Average browsing time by device
    device_browsing = df.groupby('device_type')['browsing_time_minutes'].mean().sort_values(ascending=False)
    axes[1].bar(device_browsing.index, device_browsing.values, color=['#007bff', '#28a745', '#ffc107'])
    axes[1].set_title('Average Browsing Time by Device', fontsize=14, fontweight='bold')
    axes[1].set_xlabel('Device Type')
    axes[1].set_ylabel('Average Browsing Time (minutes)')
    axes[1].grid(axis='y', alpha=0.3)
    
    plt.tight_layout()
    plt.savefig('device_usage.png', dpi=300, bbox_inches='tight')
    print("Saved: device_usage.png")
    plt.close()

def plot_age_analysis(df):
    """Plot age group analysis"""
    # Create age groups
    df['age_group'] = pd.cut(df['age'], bins=[0, 25, 35, 50, 100], 
                             labels=['Young (0-25)', 'Adult (26-35)', 'Middle (36-50)', 'Senior (50+)'])
    
    fig, axes = plt.subplots(2, 2, figsize=(14, 12))
    
    # Age distribution
    age_counts = df['age_group'].value_counts().sort_index()
    axes[0, 0].bar(range(len(age_counts)), age_counts.values, color=['#007bff', '#28a745', '#ffc107', '#dc3545'])
    axes[0, 0].set_xticks(range(len(age_counts)))
    axes[0, 0].set_xticklabels(age_counts.index, rotation=15, ha='right')
    axes[0, 0].set_title('Age Group Distribution', fontsize=12, fontweight='bold')
    axes[0, 0].set_ylabel('Number of Customers')
    axes[0, 0].grid(axis='y', alpha=0.3)
    
    # Average spending by age group
    age_spending = df.groupby('age_group')['spending_score'].mean().sort_index()
    axes[0, 1].bar(range(len(age_spending)), age_spending.values, color=['#007bff', '#28a745', '#ffc107', '#dc3545'])
    axes[0, 1].set_xticks(range(len(age_spending)))
    axes[0, 1].set_xticklabels(age_spending.index, rotation=15, ha='right')
    axes[0, 1].set_title('Average Spending Score by Age Group', fontsize=12, fontweight='bold')
    axes[0, 1].set_ylabel('Average Spending Score')
    axes[0, 1].grid(axis='y', alpha=0.3)
    
    # Age vs spending scatter
    axes[1, 0].scatter(df['age'], df['spending_score'], alpha=0.6, c=df['spending_score'], 
                      cmap='viridis', s=100)
    axes[1, 0].set_xlabel('Age')
    axes[1, 0].set_ylabel('Spending Score')
    axes[1, 0].set_title('Age vs Spending Score', fontsize=12, fontweight='bold')
    axes[1, 0].grid(alpha=0.3)
    
    # Age group by segment
    age_segment = pd.crosstab(df['age_group'], df['segment'])
    age_segment.plot(kind='bar', ax=axes[1, 1], color=['#6c757d', '#ffc107', '#28a745'])
    axes[1, 1].set_title('Age Group by Segment', fontsize=12, fontweight='bold')
    axes[1, 1].set_xlabel('Age Group')
    axes[1, 1].set_ylabel('Number of Customers')
    axes[1, 1].legend(title='Segment')
    axes[1, 1].grid(axis='y', alpha=0.3)
    axes[1, 1].tick_params(axis='x', rotation=15)
    
    plt.tight_layout()
    plt.savefig('age_analysis.png', dpi=300, bbox_inches='tight')
    print("Saved: age_analysis.png")
    plt.close()

def plot_correlation_heatmap(df):
    """Plot correlation heatmap"""
    numeric_cols = ['age', 'annual_income', 'spending_score', 'purchase_frequency',
                   'avg_order_value', 'total_purchases', 'browsing_time_minutes']
    correlation_matrix = df[numeric_cols].corr()
    
    plt.figure(figsize=(10, 8))
    sns.heatmap(correlation_matrix, annot=True, cmap='coolwarm', center=0,
                square=True, linewidths=1, cbar_kws={"shrink": 0.8})
    plt.title('Feature Correlation Heatmap', fontsize=14, fontweight='bold', pad=20)
    plt.tight_layout()
    plt.savefig('correlation_heatmap.png', dpi=300, bbox_inches='tight')
    print("Saved: correlation_heatmap.png")
    plt.close()

def plot_purchase_behavior(df):
    """Plot purchase behavior analysis"""
    fig, axes = plt.subplots(2, 2, figsize=(14, 12))
    
    # Purchase frequency distribution
    axes[0, 0].hist(df['purchase_frequency'], bins=20, color='#007bff', edgecolor='black', alpha=0.7)
    axes[0, 0].set_title('Purchase Frequency Distribution', fontsize=12, fontweight='bold')
    axes[0, 0].set_xlabel('Purchase Frequency')
    axes[0, 0].set_ylabel('Number of Customers')
    axes[0, 0].grid(axis='y', alpha=0.3)
    
    # Average order value by segment
    segment_aov = df.groupby('segment')['avg_order_value'].mean().sort_values(ascending=False)
    axes[0, 1].bar(segment_aov.index, segment_aov.values, color=['#28a745', '#ffc107', '#6c757d'])
    axes[0, 1].set_title('Average Order Value by Segment', fontsize=12, fontweight='bold')
    axes[0, 1].set_xlabel('Segment')
    axes[0, 1].set_ylabel('Average Order Value ($)')
    axes[0, 1].grid(axis='y', alpha=0.3)
    
    # Purchase frequency vs spending score
    axes[1, 0].scatter(df['purchase_frequency'], df['spending_score'], 
                      alpha=0.6, c=df['spending_score'], cmap='viridis', s=100)
    axes[1, 0].set_xlabel('Purchase Frequency')
    axes[1, 0].set_ylabel('Spending Score')
    axes[1, 0].set_title('Purchase Frequency vs Spending Score', fontsize=12, fontweight='bold')
    axes[1, 0].grid(alpha=0.3)
    
    # Total purchases by segment
    segment_purchases = df.groupby('segment')['total_purchases'].sum()
    axes[1, 1].bar(segment_purchases.index, segment_purchases.values, 
                   color=['#28a745', '#ffc107', '#6c757d'])
    axes[1, 1].set_title('Total Purchases by Segment', fontsize=12, fontweight='bold')
    axes[1, 1].set_xlabel('Segment')
    axes[1, 1].set_ylabel('Total Purchases')
    axes[1, 1].grid(axis='y', alpha=0.3)
    
    plt.tight_layout()
    plt.savefig('purchase_behavior.png', dpi=300, bbox_inches='tight')
    print("Saved: purchase_behavior.png")
    plt.close()

def main():
    """Main visualization function"""
    print("="*60)
    print("E-COMMERCE CUSTOMER DATASET - DATA VISUALIZATION")
    print("RSK World - https://rskworld.in")
    print("="*60)
    
    # Load data
    df = load_data()
    if df is None:
        return
    
    # Generate all visualizations
    print("\nGenerating visualizations...")
    plot_segment_distribution(df)
    plot_income_vs_spending(df)
    plot_product_preferences(df)
    plot_device_usage(df)
    plot_age_analysis(df)
    plot_correlation_heatmap(df)
    plot_purchase_behavior(df)
    
    print("\n" + "="*60)
    print("ALL VISUALIZATIONS GENERATED SUCCESSFULLY!")
    print("="*60)
    print("\nGenerated files:")
    print("  - segment_distribution.png")
    print("  - income_vs_spending.png")
    print("  - product_preferences.png")
    print("  - device_usage.png")
    print("  - age_analysis.png")
    print("  - correlation_heatmap.png")
    print("  - purchase_behavior.png")

if __name__ == "__main__":
    main()

282 lines•11.4 KB

python

Theme Settings

Color Scheme

Display Options

Font Size