通販CRMにおける顧客分析の考え方｜パランティア的に購買行動を読む

-- 「購買前72時間の行動パターン」を分析する
-- 購買に至った顧客が、購買の何日前にどんな行動をしていたかを把握する

WITH purchase_events AS (
  -- 購買イベントを取得
  SELECT
    customer_id,
    TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'JST')   AS purchase_date,
    time                                         AS purchase_time
  FROM orders
  WHERE
    status = 'completed'
    AND TD_TIME_RANGE(time, '2024-10-01 JST', '2024-12-31 JST')
),
pre_purchase_behavior AS (
  -- 購買前72時間（3日）の行動ログを取得
  SELECT
    b.customer_id,
    p.purchase_date,
    TD_TIME_FORMAT(b.time, 'yyyy-MM-dd', 'JST') AS behavior_date,
    b.event_type,                               -- 'view' / 'cart_add' / 'cart_remove' / 'search' など
    b.product_category,
    b.page_url,
    -- 購買からの経過時間（時間）
    (p.purchase_time - b.time) / 3600           AS hours_before_purchase
  FROM purchase_events p
  JOIN site_behavior_log b
    ON p.customer_id = b.customer_id
    AND b.time BETWEEN p.purchase_time - 259200  -- 72時間前（秒換算）
                   AND p.purchase_time
)
-- 購買前の行動タイプ別・時間帯別の頻度を集計
SELECT
  event_type,
  CASE
    WHEN hours_before_purchase <= 6  THEN '購買6時間前以内'
    WHEN hours_before_purchase <= 24 THEN '購買24時間前以内'
    WHEN hours_before_purchase <= 48 THEN '購買48時間前以内'
    ELSE                                  '購買72時間前以内'
  END                                              AS time_bucket,
  COUNT(*)                                         AS event_count,
  COUNT(DISTINCT customer_id)                      AS customer_count,
  ROUND(AVG(hours_before_purchase), 1)             AS avg_hours_before
FROM pre_purchase_behavior
GROUP BY event_type, time_bucket
ORDER BY time_bucket, event_count DESC

-- 「離脱前兆スコア」を計算する
-- 過去購買パターンと比較して「いつもと違う」を検知する

WITH customer_baseline AS (
  -- 各顧客の「通常の購買パターン」を過去1年から算出する
  SELECT
    customer_id,
    COUNT(order_id)                        AS annual_order_count,
    AVG(amount)                            AS avg_order_amount,
    -- 平均購買間隔（日数）
    AVG(
      DATE_DIFF('day', prev_order_date, order_date)
    )                                      AS avg_purchase_interval_days,
    MAX(order_date)                        AS last_order_date
  FROM (
    SELECT
      customer_id,
      order_id,
      order_date,
      amount,
      LAG(order_date) OVER (
        PARTITION BY customer_id ORDER BY order_date
      )                                    AS prev_order_date
    FROM orders
    WHERE
      status = 'completed'
      AND order_date BETWEEN DATE_ADD('day', -365, CURRENT_DATE)
                         AND CURRENT_DATE
  ) t
  GROUP BY customer_id
),
churn_signals AS (
  SELECT
    b.customer_id,
    b.avg_purchase_interval_days,
    b.last_order_date,
    -- 最終購買からの経過日数
    DATE_DIFF('day', b.last_order_date, CURRENT_DATE) AS days_since_last_order,
    -- 「平均購買間隔の何倍が経過しているか」を計算
    ROUND(
      DATE_DIFF('day', b.last_order_date, CURRENT_DATE)
        / NULLIF(b.avg_purchase_interval_days, 0)
    , 2)                                   AS interval_multiplier,
    b.annual_order_count,
    b.avg_order_amount
  FROM customer_baseline b
  WHERE b.annual_order_count >= 2  -- 購買が2回以上ある顧客のみ対象
)
SELECT
  customer_id,
  avg_purchase_interval_days,
  days_since_last_order,
  interval_multiplier,
  -- 離脱リスクスコア（0〜100）
  CASE
    WHEN interval_multiplier >= 3.0 THEN 90   -- 通常の3倍以上 → 高リスク
    WHEN interval_multiplier >= 2.0 THEN 70   -- 通常の2倍以上 → 中リスク
    WHEN interval_multiplier >= 1.5 THEN 40   -- 通常の1.5倍以上 → 低リスク
    ELSE                                   10  -- 正常範囲
  END                                      AS churn_risk_score,
  -- ウィンバック施策のタイミング判定
  CASE
    WHEN interval_multiplier >= 3.0 THEN '緊急ウィンバック（SMS推奨）'
    WHEN interval_multiplier >= 2.0 THEN 'ウィンバックメール（クーポン付き）'
    WHEN interval_multiplier >= 1.5 THEN 'リマインドメール（商品レコメンド）'
    ELSE                                  '正常モニタリング継続'
  END                                      AS recommended_action
FROM churn_signals
ORDER BY churn_risk_score DESC, interval_multiplier DESC

-- 顧客ごとの「購買動機パターン」を推定するSQL

WITH purchase_patterns AS (
  SELECT
    o.customer_id,
    -- 補充動機：同一カテゴリの定期購買パターン
    STDDEV(DATE_DIFF('day', LAG(o.order_date) OVER (
      PARTITION BY o.customer_id, o.product_category_id
      ORDER BY o.order_date
    ), o.order_date))                            AS purchase_interval_stddev,
    -- トレンド動機：初回購買後の継続率
    COUNT(DISTINCT o.order_id)                   AS total_orders,
    COUNT(DISTINCT o.product_category_id)        AS category_diversity,
    -- 信頼動機：クーポン未使用購買の割合
    AVG(CASE WHEN o.coupon_used = FALSE THEN 1.0 ELSE 0.0 END) AS no_coupon_ratio
  FROM orders o
  WHERE
    o.status = 'completed'
    AND o.order_date >= DATE_ADD('day', -180, CURRENT_DATE)
  GROUP BY o.customer_id
)
SELECT
  customer_id,
  -- 動機スコアから主動機を推定
  CASE
    WHEN purchase_interval_stddev < 10
     AND total_orders >= 3
    THEN '補充動機型（定期購買顧客）'
    WHEN category_diversity = 1
     AND total_orders = 1
    THEN 'トレンド動機型（単発購買者）'
    WHEN no_coupon_ratio >= 0.8
     AND total_orders >= 3
    THEN '信頼動機型（ロイヤル顧客）'
    WHEN category_diversity >= 4
    THEN '問題解決動機型（多様なニーズを持つ顧客）'
    ELSE 'ミックス型（複合動機）'
  END                                            AS motivation_type,
  purchase_interval_stddev,
  total_orders,
  category_diversity,
  ROUND(no_coupon_ratio * 100, 1)               AS no_coupon_pct
FROM purchase_patterns
ORDER BY total_orders DESC