12-advanced-analytics.md

Chapter 12: Advanced Analytics

Advanced analytics goes beyond simple data retrieval to provide deep business insights. In this chapter, we'll explore techniques used by professional data scientists and business analysts to understand customer behavior, retention, and value.

12.1 Cohort Analysis

A cohort is a group of customers who share a common starting point, such as their first purchase month. Cohort analysis helps you understand customer retention over time.

-- Step 1: Find each customer's first purchase month (cohort)
WITH customer_cohort AS (
    SELECT
        customer_id,
        MIN(SUBSTR(CAST(order_purchase_timestamp AS VARCHAR), 1, 7)) AS cohort_month
    FROM orders
    GROUP BY customer_id
)
SELECT cohort_month, COUNT(*) AS cohort_size
FROM customer_cohort
GROUP BY 1
ORDER BY 1;

12.2 RFM Segmentation

RFM stands for Recency, Frequency, and Monetary value. It's a powerful tool for segmenting your customer base and identifying your most valuable users.

• Recency: How recently did the customer buy?
• Frequency: How often do they buy?
• Monetary: How much do they spend?

WITH rfm_base AS (
    SELECT
        c.customer_unique_id,
        MAX(o.order_purchase_timestamp) AS last_purchase,
        COUNT(DISTINCT o.order_id) AS frequency,
        SUM(p.payment_value) AS monetary
    FROM orders o JOIN customers c ON o.customer_id = c.customer_id JOIN order_payments p ON o.order_id = p.order_id
    WHERE o.order_status = 'delivered'
    GROUP BY 1
),
rfm_scores AS (
    SELECT
        *,
        NTILE(5) OVER (ORDER BY last_purchase) AS r,
        NTILE(5) OVER (ORDER BY frequency) AS f,
        NTILE(5) OVER (ORDER BY monetary) AS m
    FROM rfm_base
)
SELECT
    CASE WHEN r >= 4 AND f >= 4 AND m >= 4 THEN 'Champions' WHEN r <= 2 AND f <= 2 THEN 'Lost' ELSE 'Other' END AS segment,
    COUNT(*)
FROM rfm_scores GROUP BY 1;

12.3 Funnel Analysis

Funnel analysis tracks the "journey" of a customer or an order through various stages to identify where drop-offs occur.

-- Order funnel conversion rates
SELECT
    COUNT(*) AS total_orders,
    SUM(CASE WHEN order_status = 'delivered' THEN 1 ELSE 0 END) AS delivered,
    ROUND(100.0 * SUM(CASE WHEN order_status = 'delivered' THEN 1 ELSE 0 END) / COUNT(*), 1) AS delivery_rate
FROM orders;

12.4 Year-over-Year (YoY) Growth

YoY growth compares a metric from one period to the same period in the previous year, which is essential for understanding long-term business trends.

WITH yearly AS (
    SELECT SUBSTR(CAST(order_purchase_timestamp AS VARCHAR), 1, 4) AS yr, SUM(price) AS rev FROM orders o JOIN order_items oi ON o.order_id = oi.order_id GROUP BY 1
)
SELECT
    yr, rev,
    LAG(rev) OVER (ORDER BY yr) AS prev_yr,
    ROUND(100.0 * (rev - LAG(rev) OVER (ORDER BY yr)) / LAG(rev) OVER (ORDER BY yr), 1) AS yoy_growth
FROM yearly;

12.5 Product Cross-Sell Analysis

Cross-sell analysis identifies product categories that are frequently purchased together, which is vital for recommendation engines and marketing campaigns.

WITH order_cats AS (
    SELECT DISTINCT oi.order_id, p.product_category_name AS cat FROM order_items oi JOIN products p ON oi.product_id = p.product_id WHERE cat IS NOT NULL
)
SELECT
    a.cat AS cat_a,
    b.cat AS cat_b,
    COUNT(*) AS co_occurrence
FROM order_cats a JOIN order_cats b ON a.order_id = b.order_id AND a.cat < b.cat
GROUP BY 1, 2
ORDER BY 3 DESC LIMIT 10;

---

Exercises

1. Build a monthly cohort analysis showing the number of first-time buyers each month for 2018.
2. Create a "delivery SLA" analysis: calculate what percentage of orders were delivered within 7, 14, 21, and 30 days for each month.
3. Build a seller churn analysis: find sellers who were active in 2017 but had no orders in 2018.
4. Create an "Average Order Value" (AOV) trend by month with a 3-month moving average.

Solutions

-- Exercise 1
WITH first_buy AS (SELECT customer_id, MIN(order_purchase_timestamp) AS fb FROM orders GROUP BY 1) SELECT SUBSTR(CAST(fb AS VARCHAR), 1, 7) AS ym, COUNT(*) FROM first_buy WHERE fb LIKE '2018%' GROUP BY 1;

-- Exercise 2
SELECT SUBSTR(CAST(order_purchase_timestamp AS VARCHAR), 1, 7) AS ym, ROUND(100.0 * SUM(CASE WHEN CAST(order_delivered_customer_date AS DATE) - CAST(order_purchase_timestamp AS DATE) <= 7 THEN 1 ELSE 0 END) / COUNT(*), 1) AS within_7d FROM orders WHERE order_status = 'delivered' AND order_delivered_customer_date IS NOT NULL GROUP BY 1;

-- Exercise 3
WITH s17 AS (SELECT DISTINCT seller_id FROM order_items oi JOIN orders o ON oi.order_id = o.order_id WHERE o.order_purchase_timestamp LIKE '2017%'), s18 AS (SELECT DISTINCT seller_id FROM order_items oi JOIN orders o ON oi.order_id = o.order_id WHERE o.order_purchase_timestamp LIKE '2018%') SELECT s17.seller_id FROM s17 LEFT JOIN s18 ON s17.seller_id = s18.seller_id WHERE s18.seller_id IS NULL;

-- Exercise 4
WITH m_aov AS (SELECT SUBSTR(CAST(o.order_purchase_timestamp AS VARCHAR), 1, 7) AS ym, AVG(v) AS aov FROM (SELECT order_id, order_purchase_timestamp, SUM(payment_value) AS v FROM orders o JOIN order_payments p ON o.order_id = p.order_id GROUP BY 1, 2) o GROUP BY 1) SELECT ym, aov, AVG(aov) OVER (ORDER BY ym ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) FROM m_aov;