CTE를 활용한 코호트 분석

WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC(CAST(o.created_at AS DATE), MONTH) AS month,
        SUM(oi.sale_price) AS revenue,
        COUNT(DISTINCT o.order_id) AS order_count
    FROM `your-project-id.thelook_ecommerce.orders` o
    JOIN `your-project-id.thelook_ecommerce.order_items` oi ON o.order_id = oi.order_id
    GROUP BY month
),
monthly_growth AS (
    SELECT
        month,
        revenue,
        order_count,
        LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
        ROUND((revenue - LAG(revenue) OVER (ORDER BY month)) /
              NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100, 2) AS growth_rate
    FROM monthly_revenue
)
SELECT
    month,
    revenue,
    order_count,
    prev_revenue,
    growth_rate
FROM monthly_growth
WHERE growth_rate > 0
ORDER BY month

WITH user_cohort AS (
    SELECT
        user_id,
        DATE_TRUNC(MIN(CAST(created_at AS DATE)), MONTH) AS cohort_month
    FROM `your-project-id.thelook_ecommerce.orders`
    GROUP BY user_id
),
user_monthly_revenue AS (
    SELECT
        o.user_id,
        uc.cohort_month,
        DATE_TRUNC(CAST(o.created_at AS DATE), MONTH) AS order_month,
        DATE_DIFF(DATE_TRUNC(CAST(o.created_at AS DATE), MONTH), uc.cohort_month, MONTH) AS months_since_first,
        SUM(oi.sale_price) AS revenue
    FROM `your-project-id.thelook_ecommerce.orders` o
    JOIN `your-project-id.thelook_ecommerce.order_items` oi ON o.order_id = oi.order_id
    JOIN user_cohort uc ON o.user_id = uc.user_id
    GROUP BY o.user_id, uc.cohort_month, order_month
),
cohort_revenue AS (
    SELECT
        cohort_month,
        months_since_first,
        SUM(revenue) AS total_revenue,
        COUNT(DISTINCT user_id) AS active_users
    FROM user_monthly_revenue
    GROUP BY cohort_month, months_since_first
)
SELECT
    cohort_month,
    months_since_first,
    total_revenue,
    active_users,
    SUM(total_revenue) OVER (
        PARTITION BY cohort_month
        ORDER BY months_since_first
    ) AS cumulative_revenue
FROM cohort_revenue
WHERE cohort_month >= '2024-01-01'
  AND months_since_first <= 12
ORDER BY cohort_month, months_since_first

WITH customer_rfm AS (
    SELECT
        o.user_id,
        DATE_DIFF(CURRENT_DATE(), MAX(CAST(o.created_at AS DATE)), DAY) AS recency,
        COUNT(DISTINCT o.order_id) AS frequency,
        SUM(oi.sale_price) AS monetary
    FROM `your-project-id.thelook_ecommerce.orders` o
    JOIN `your-project-id.thelook_ecommerce.order_items` oi ON o.order_id = oi.order_id
    GROUP BY o.user_id
),
rfm_scores AS (
    SELECT
        user_id,
        recency,
        frequency,
        monetary,
        NTILE(5) OVER (ORDER BY recency DESC) AS r_score,
        NTILE(5) OVER (ORDER BY frequency ASC) AS f_score,
        NTILE(5) OVER (ORDER BY monetary ASC) AS m_score
    FROM customer_rfm
),
rfm_segments AS (
    SELECT
        user_id,
        monetary,
        CASE
            WHEN r_score >= 4 AND f_score >= 4 AND m_score >= 4 THEN 'Champions'
            WHEN r_score >= 4 AND f_score >= 3 THEN 'Loyal Customers'
            WHEN r_score >= 4 AND f_score <= 2 THEN 'Recent Customers'
            WHEN r_score <= 2 AND f_score >= 4 THEN 'At Risk'
            WHEN r_score <= 2 AND f_score <= 2 THEN 'Lost'
            ELSE 'Others'
        END AS customer_segment
    FROM rfm_scores
)
SELECT
    customer_segment,
    COUNT(*) AS customer_count,
    ROUND(AVG(monetary), 2) AS avg_monetary,
    SUM(monetary) AS total_revenue,
    ROUND(SUM(monetary) * 100.0 / (SELECT SUM(monetary) FROM rfm_segments), 2) AS revenue_share
FROM rfm_segments
GROUP BY customer_segment
ORDER BY total_revenue DESC

WITH category_purchases AS (
    SELECT
        o.user_id,
        p.category,
        o.created_at,
        LAG(o.created_at) OVER (
            PARTITION BY o.user_id, p.category
            ORDER BY o.created_at
        ) AS prev_purchase
    FROM `your-project-id.thelook_ecommerce.orders` o
    JOIN `your-project-id.thelook_ecommerce.order_items` oi ON o.order_id = oi.order_id
    JOIN `your-project-id.thelook_ecommerce.products` p ON oi.product_id = p.id
),
category_gaps AS (
    SELECT
        category,
        user_id,
        DATE_DIFF(CAST(created_at AS DATE), CAST(prev_purchase AS DATE), DAY) AS days_gap
    FROM category_purchases
    WHERE prev_purchase IS NOT NULL
),
category_stats AS (
    SELECT
        category,
        COUNT(DISTINCT user_id) AS repeat_customers,
        AVG(days_gap) AS avg_repurchase_days
    FROM category_gaps
    GROUP BY category
),
category_total AS (
    SELECT
        p.category,
        COUNT(DISTINCT o.user_id) AS total_customers
    FROM `your-project-id.thelook_ecommerce.orders` o
    JOIN `your-project-id.thelook_ecommerce.order_items` oi ON o.order_id = oi.order_id
    JOIN `your-project-id.thelook_ecommerce.products` p ON oi.product_id = p.id
    GROUP BY p.category
)
SELECT
    ct.category,
    ct.total_customers,
    COALESCE(cs.repeat_customers, 0) AS repeat_customers,
    ROUND(COALESCE(cs.repeat_customers, 0) * 100.0 / ct.total_customers, 2) AS repeat_rate,
    ROUND(cs.avg_repurchase_days, 1) AS avg_repurchase_days
FROM category_total ct
LEFT JOIN category_stats cs ON ct.category = cs.category
ORDER BY avg_repurchase_days