Build an Automated E-commerce Analytics Dashboard with DuckDB

import duckdb
import pandas as pd
import numpy as np

# Generate simulated e-commerce data
np.random.seed(42)
n = 50000

orders = pd.DataFrame({
    'order_id': range(1, n+1),
    'customer_id': np.random.randint(1, 5000, n),
    'product_category': np.random.choice(['Electronics', 'Clothing', 'Food', 'Home', 'Beauty'], n),
    'product_name': [f'Product{i%200+1}' for i in range(n)],
    'quantity': np.random.randint(1, 10, n),
    'unit_price': np.round(np.random.uniform(9.9, 999.9, n), 2),
    'order_date': pd.date_range('2025-01-01', periods=n, freq='min'),
    'province': np.random.choice(['Guangdong', 'Jiangsu', 'Zhejiang', 'Beijing', 'Shanghai', 'Sichuan', 'Hubei', 'Fujian'], n),
    'payment_method': np.random.choice(['Alipay', 'WeChat', 'CreditCard', 'BankCard'], n),
    'is_returned': np.random.choice([0, 1], n, p=[0.95, 0.05])
})

orders['total_amount'] = orders['quantity'] * orders['unit_price'] * (1 - orders['is_returned'])

# Save to CSV
orders.to_csv('ecommerce_orders.csv', index=False, encoding='utf-8-sig')
print(f"✅ Generated {len(orders)} order records")

import duckdb
con = duckdb.connect()
df = con.sql("SELECT * FROM read_csv_auto('ecommerce_orders.csv')").df()
print(df.shape)  # (50000, 11)

revenue_sql = """
WITH daily_stats AS (
    SELECT 
        DATE(order_date) AS order_date,
        COUNT(*) AS order_count,
        SUM(total_amount) AS daily_revenue,
        AVG(total_amount) AS avg_order_value,
        COUNT(DISTINCT customer_id) AS unique_customers,
        SUM(is_returned) AS return_count,
        ROUND(SUM(is_returned) * 1.0 / COUNT(*), 4) AS return_rate
    FROM read_csv_auto('ecommerce_orders.csv')
    GROUP BY DATE(order_date)
),
cumulative AS (
    SELECT 
        order_date,
        SUM(daily_revenue) OVER (ORDER BY order_date) AS cumulative_revenue,
        SUM(order_count) OVER (ORDER BY order_date) AS cumulative_orders,
        AVG(daily_revenue) OVER (ORDER BY order_date ROWS UNBOUNDED PRECEDING) AS rolling_avg_revenue
    FROM daily_stats
)
SELECT * FROM cumulative 
ORDER BY order_date;
"""

result = duckdb.sql(revenue_sql).df()
print(result.tail(7))

category_region_sql = """
SELECT 
    product_category AS category,
    province AS region,
    COUNT(*) AS order_count,
    SUM(total_amount) AS revenue,
    AVG(total_amount) AS avg_order_value,
    SUM(is_returned) AS return_count,
    ROUND(SUM(is_returned) * 1.0 / COUNT(*), 4) AS return_rate,
    ROUND(SUM(total_amount) * 1.0 / SUM(SUM(total_amount)) OVER (), 4) AS share
FROM read_csv_auto('ecommerce_orders.csv')
GROUP BY product_category, province
HAVING SUM(total_amount) > 10000
ORDER BY revenue DESC
LIMIT 30;
"""

cat_result = duckdb.sql(category_region_sql).df()
print(cat_result.to_string(index=False))

rfm_sql = """
WITH customer_metrics AS (
    SELECT 
        customer_id,
        MAX(order_date) AS last_order_date,
        MIN(order_date) AS first_order_date,
        COUNT(*) AS frequency,
        SUM(total_amount) AS monetary,
        AVG(total_amount) AS avg_order_value
    FROM read_csv_auto('ecommerce_orders.csv')
    GROUP BY customer_id
),
rfm_scores AS (
    SELECT 
        customer_id,
        frequency,
        monetary,
        avg_order_value,
        NTILE(5) OVER (ORDER BY frequency) AS freq_score,
        NTILE(5) OVER (ORDER BY monetary) AS money_score,
        NTILE(5) OVER (ORDER BY avg_order_value) AS avg_score,
        CASE 
            WHEN frequency >= 10 AND monetary >= 5000 THEN 'High Value'
            WHEN frequency >= 5 AND monetary >= 2000 THEN 'Potential'
            WHEN frequency >= 3 AND monetary >= 500 THEN 'Regular'
            WHEN frequency = 1 AND monetary < 200 THEN 'At Risk'
            ELSE 'Other'
        END AS customer_segment
    FROM customer_metrics
)
SELECT 
    customer_segment,
    COUNT(*) AS customer_count,
    ROUND(AVG(monetary), 2) AS avg_spend,
    ROUND(AVG(frequency), 2) AS avg_orders,
    ROUND(SUM(monetary), 2) AS total_contribution
FROM rfm_scores
GROUP BY customer_segment
ORDER BY total_contribution DESC;
"""

rfm_result = duckdb.sql(rfm_sql).df()
print(rfm_result.to_string(index=False))

import pandas as pd
from datetime import datetime

def generate_monthly_report(data_path='ecommerce_orders.csv'):
    """Generate a complete monthly e-commerce analysis report"""
    
    # 1. Monthly summary
    monthly_sql = """
    WITH base AS (
        SELECT *, 
            DATE_FORMAT(order_date, '%Y-%m') AS month,
            CASE 
                WHEN hour(order_date) < 12 THEN 'Morning'
                WHEN hour(order_date) < 18 THEN 'Afternoon'
                ELSE 'Evening'
            END AS time_period
        FROM read_csv_auto('$data_path')
    ),
    monthly_summary AS (
        SELECT 
            month,
            COUNT(*) AS total_orders,
            SUM(total_amount) AS total_revenue,
            AVG(total_amount) AS avg_order_value,
            COUNT(DISTINCT customer_id) AS unique_customers,
            SUM(is_returned) AS total_returns,
            ROUND(SUM(is_returned)*1.0/COUNT(*), 4) AS return_rate,
            COUNT(DISTINCT product_category) AS category_count
        FROM base
        GROUP BY month
    )
    SELECT * FROM monthly_summary ORDER BY month DESC;
    """
    
    summary = duckdb.sql(monthly_sql.replace('$data_path', data_path)).df()
    
    # 2. Top 10 products
    top_products_sql = """
    SELECT 
        product_name,
        product_category,
        COUNT(*) AS sales_count,
        ROUND(SUM(total_amount), 2) AS revenue
    FROM read_csv_auto('$data_path')
    GROUP BY product_name, product_category
    ORDER BY revenue DESC
    LIMIT 10;
    """
    
    top_products = duckdb.sql(top_products_sql.replace('$data_path', data_path)).df()
    
    # 3. Time-of-day distribution
    traffic_sql = """
    SELECT 
        CASE 
            WHEN hour(order_date) < 12 THEN 'Morning (0-12)'
            WHEN hour(order_date) < 18 THEN 'Afternoon (12-18)'
            ELSE 'Evening (18-24)'
        END AS time_period,
        COUNT(*) AS orders,
        ROUND(SUM(total_amount), 2) AS revenue,
        ROUND(SUM(total_amount)*1.0/SUM(SUM(total_amount))OVER(), 4) AS share
    FROM read_csv_auto('$data_path')
    GROUP BY time_period;
    """
    
    traffic = duckdb.sql(traffic_sql).df()
    
    return {
        'monthly_summary': summary,
        'top_products': top_products,
        'traffic': traffic
    }

# Execute
report = generate_monthly_report()
print("=== Monthly Summary ===")
print(report['monthly_summary'].to_string(index=False))
print("\n=== Top 10 Products ===")
print(report['top_products'].to_string(index=False))
print("\n=== Traffic by Time Period ===")
print(report['traffic'].to_string(index=False))

import schedule
import time
from datetime import datetime

def weekly_report():
    report = generate_monthly_report()
    
    # Generate HTML report
    html = f"""
    <h2>E-commerce Weekly Report - {datetime.now().strftime('%Y-%m-%d')}</h2>
    <h3>Monthly Summary</h3>
    <pre>{report['monthly_summary'].to_string(index=False)}</pre>
    <h3>Top 10 Products</h3>
    <pre>{report['top_products'].to_string(index=False)}</pre>
    """
    
    with open(f'report_{datetime.now().strftime("%Y%m%d")}.html', 'w', encoding='utf-8') as f:
        f.write(html)
    
    print(f"✅ Report generated: report_{datetime.now().strftime('%Y%m%d')}.html")

# Run every Monday at 9 AM
schedule.every().monday.at("09:00").do(weekly_report)

while True:
    schedule.run_pending()
    time.sleep(3600)

# Edit crontab
crontab -e

# Execute every Monday at 9 AM
0 9 * * 1 cd /home/user/report && python3 weekly_report.py