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