Build an E-commerce Repurchase Prediction Dashboard with DuckDB — Pure SQL, No ML Required

import duckdb
from datetime import datetime, timedelta
import random

# Connect to DuckDB file (use .db file in production)
con = duckdb.connect("repurchase_predict.db")

# Create tables: customers + orders
con.execute("""
CREATE TABLE IF NOT EXISTS customers (
 customer_id BIGINT,
 name VARCHAR,
 signup_date DATE,
 tier VARCHAR -- 'New','Regular','VIP'
)
""")

con.execute("""
CREATE TABLE IF NOT EXISTS orders (
 order_id BIGINT,
 customer_id BIGINT,
 order_date DATE,
 amount DECIMAL(10,2),
 category VARCHAR,
 channel VARCHAR -- 'MiniProgram','App','Offline'
)
""")

# Simulate data: 300 customers, 6 months of orders
random.seed(42)
customers = [
 (i, f'Customer_{i}',
 datetime(2026, 3, 1) - timedelta(days=random.randint(0, 180)),
 random.choice(['New', 'Regular', 'VIP']))
 for i in range(1, 301)
]
con.execute("INSERT INTO customers VALUES ?", customers)

orders = []
order_id = 1
for cid in range(1, 301):
 num_orders = random.choices([1,2,3,4,5,6,8,12,20],
 weights=[10,15,20,18,12,10,8,5,2])[0]
 for _ in range(num_orders):
 order_date = datetime(2026, 3, 1) - timedelta(days=random.randint(0, 180))
 orders.append((
 order_id, cid, order_date.date(),
 round(random.uniform(29, 599), 2),
 random.choice(['Coffee', 'Dessert', 'Merch', 'Gift Box']),
 random.choice(['MiniProgram', 'App', 'Offline'])
 ))
 order_id += 1

con.execute("INSERT INTO orders VALUES ?", orders)
print(f"✅ Data ready: {len(customers)} customers, {len(orders)} orders")

CREATE OR REPLACE VIEW v_customer_recency AS
SELECT
 c.customer_id, c.name, c.tier,
 DATEDIFF('day', MAX(o.order_date), CURRENT_DATE) AS days_since_last_purchase,
 COUNT(*) AS total_orders,
 DATEDIFF('day', MIN(o.order_date), MAX(o.order_date)) AS active_span_days,
 ROUND(AVG(o.amount), 2) AS avg_order_value,
 ROUND(SUM(o.amount), 2) AS total_spend,
 COUNT(DISTINCT o.category) AS category_count,
 COUNT(CASE WHEN o.order_date >= CURRENT_DATE - INTERVAL '30' DAY THEN 1 END) AS orders_last_30d,
 COUNT(CASE WHEN o.order_date >= CURRENT_DATE - INTERVAL '60' DAY
 AND o.order_date < CURRENT_DATE - INTERVAL '30' DAY THEN 1 END) AS orders_prev_30d
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name, c.tier

CREATE OR REPLACE VIEW v_customer_gap AS
SELECT
 customer_id,
 ROUND(AVG(day_gap), 1) AS avg_repurchase_gap,
 ROUND(STDDEV(day_gap), 1) AS gap_stddev
FROM (
 SELECT
 customer_id,
 DATEDIFF('day',
 LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date),
 order_date
 ) AS day_gap
 FROM orders
) sub
WHERE day_gap IS NOT NULL
GROUP BY customer_id

CREATE OR REPLACE VIEW v_repurchase_prediction AS
SELECT
 r.customer_id, r.name, r.tier,
 r.days_since_last_purchase,
 r.total_orders, r.avg_order_value, r.total_spend,
 r.orders_last_30d, r.orders_prev_30d,
 -- Predicted next purchase date
 DATEADD('day',
 COALESCE(g.avg_repurchase_gap, 30),
 r.days_since_last_purchase
 ) AS predicted_next_purchase_date,
 -- Repurchase status classification
 CASE
 WHEN r.days_since_last_purchase > COALESCE(g.avg_repurchase_gap * 2, 60)
 THEN '⚠️ Churn Risk'
 WHEN r.days_since_last_purchase > g.avg_repurchase_gap
 THEN '🟡 Approaching Repurchase'
 ELSE '🟢 In Repurchase Cycle'
 END AS repurchase_status,
 -- Chase priority score (0-100)
 GREATEST(0, LEAST(100,
 ROUND(
 (1 - r.days_since_last_purchase / GREATEST(g.avg_repurchase_gap * 2, 1)) * 50
 + (r.total_spend / 1000) * 30
 + r.orders_last_30d * 5
 + CASE WHEN r.days_since_last_purchase > COALESCE(g.avg_repurchase_gap * 1.5, 45)
 THEN 20 ELSE 0 END
 , 0))
 ) AS chase_priority_score
FROM v_customer_recency r
LEFT JOIN v_customer_gap g ON r.customer_id = g.customer_id

result = con.execute("""
 SELECT * FROM v_repurchase_prediction
 ORDER BY chase_priority_score DESC
 LIMIT 15
""").fetchdf()
print(result.to_string(index=False))