Python + SQL projects

orders = raw_orders.drop_duplicates("order_id").copy()
orders["channel"] = orders["channel"].fillna("Unknown")
orders = orders.loc[orders["status"].eq("Completed")]

monthly_kpis = pd.read_sql_query("""
  SELECT substr(order_date, 1, 7) AS month,
         COUNT(*) AS completed_orders,
         ROUND(SUM(order_value_gbp), 2) AS revenue_gbp,
         ROUND(AVG(order_value_gbp), 2) AS aov_gbp
  FROM orders
  GROUP BY 1 ORDER BY 1
""", database)

SELECT c.customer_id, c.region, c.segment, c.marketing_source,
       COUNT(o.order_id) AS order_frequency,
       ROUND(COALESCE(SUM(o.order_value_gbp), 0), 2) AS lifetime_value_gbp,
       MAX(o.order_date) AS last_order_date
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.region, c.segment, c.marketing_source;

retention_queue = customer_360[
    (customer_360.order_frequency >= 4)
    & (customer_360.recency_days > 120)
]