本文へ移動
顧客分析SQL 更新日: 2026年5月2日 約21分で読めます

F2転換をSQLで分析する方法｜初回から2回目購買までのカテゴリ経路を見る

「何から入った顧客が定着するか」を知っているか

新規顧客の最初の購買カテゴリは、その後の定着率に大きく影響することがある。

シャンプーから入った顧客は次にコンディショナーを買う。洗顔フォームから入った顧客は次に化粧水を買う。しかしギフトセットから入った顧客は2回目を買わずに離脱する傾向がある。
こういった「経路の傾向」がデータの中に眠っている。

これを定量化できれば、次のような判断が可能になる。

  • 新規顧客が「定着しやすいカテゴリ」を最初に買ったとき、すぐに関連カテゴリのレコメンドを送る
  • 「離脱しやすいカテゴリ」が初回購買だった顧客には、手厚い2回目購買促進を打つ
  • 広告のランディングページを「定着率の高いカテゴリ」に集中させる

今回は LEAD / LAG を使って購買の順序を追い、「初回購買カテゴリ → 2回目購買カテゴリ」の遷移パターンをSQLで集計する。

使用するテーブル

今回は orders と order_items を結合して使う。

-- orders（注文テーブル）
-- order_id     : 注文ID
-- customer_id  : 顧客ID
-- order_date   : 注文日
-- status       : 'completed' / 'cancelled'

-- order_items（注文明細）
-- order_id     : 注文ID
-- product_id   : 商品ID
-- category     : カテゴリ名
-- quantity     : 数量
-- unit_price   : 単価

STEP 1 ― 各顧客の「購買番号」を付与する

まず注文ごとに「この顧客にとって何回目の購買か」という購買番号（purchase_rank）を振る。

同一注文に複数カテゴリの商品が入っている場合、その注文の「代表カテゴリ」をどう決めるかが最初の設計上の選択だ。今回は「売上金額が最も高いカテゴリ」を代表とするアプローチをとる。

-- STEP1: 注文ごとの代表カテゴリと購買番号を付与する

WITH order_category AS (
    -- 注文ごとに売上金額が最大のカテゴリを代表カテゴリとする
    SELECT
        o.order_id,
        o.customer_id,
        o.order_date,
        oi.category,
        SUM(oi.quantity * oi.unit_price)  AS category_sales,
        -- カテゴリ別売上の降順で1位のみを残す
        ROW_NUMBER() OVER (
            PARTITION BY o.order_id
            ORDER BY SUM(oi.quantity * oi.unit_price) DESC
        )  AS category_rank
    FROM orders       o
    JOIN order_items  oi  ON o.order_id = oi.order_id
    WHERE o.status = 'completed'
    GROUP BY o.order_id, o.customer_id, o.order_date, oi.category
),
representative_category AS (
    -- 各注文の代表カテゴリ（1位のみ）
    SELECT
        order_id,
        customer_id,
        order_date,
        category  AS main_category
    FROM order_category
    WHERE category_rank = 1
),
purchase_ranked AS (
    -- 顧客ごとに購買日順で購買番号を付与
    SELECT
        customer_id,
        order_id,
        order_date,
        main_category,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY order_date ASC
        )  AS purchase_rank  -- 1=初回, 2=2回目, 3=3回目...
    FROM representative_category
)
SELECT *
FROM purchase_ranked
ORDER BY customer_id, purchase_rank;

出力イメージ（顧客 C001 の例）

customer_idorder_datemain_categorypurchase_rank
C0012024-01-08ヘアケア1
C0012024-02-12スキンケア2
C0012024-04-18ヘアケア3
C0012024-08-01ボディケア4

C001 の初回はヘアケア、2回目はスキンケアという経路が分かる。

STEP 2 ― LEAD で「次の購買カテゴリ」を同じ行に並べる

LEAD 関数を使って、「現在の購買カテゴリ」と「次の購買カテゴリ」を同じ行に持ってくる。

-- STEP2: 現在の購買と次の購買を同じ行に並べる

WITH order_category AS (
    SELECT
        o.order_id,
        o.customer_id,
        o.order_date,
        oi.category,
        ROW_NUMBER() OVER (
            PARTITION BY o.order_id
            ORDER BY SUM(oi.quantity * oi.unit_price) DESC
        )  AS category_rank
    FROM orders       o
    JOIN order_items  oi  ON o.order_id = oi.order_id
    WHERE o.status = 'completed'
    GROUP BY o.order_id, o.customer_id, o.order_date, oi.category
),
representative_category AS (
    SELECT order_id, customer_id, order_date,
           category AS main_category
    FROM order_category
    WHERE category_rank = 1
),
purchase_ranked AS (
    SELECT
        customer_id,
        order_date,
        main_category,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY order_date ASC
        )  AS purchase_rank
    FROM representative_category
),
with_next AS (
    SELECT
        customer_id,
        order_date,
        main_category          AS current_category,
        purchase_rank,

        -- 次の購買カテゴリ（1行先）
        LEAD(main_category, 1) OVER (
            PARTITION BY customer_id
            ORDER BY purchase_rank ASC
        )  AS next_category,

        -- 次の購買日
        LEAD(order_date, 1) OVER (
            PARTITION BY customer_id
            ORDER BY purchase_rank ASC
        )  AS next_order_date
    FROM purchase_ranked
)
SELECT *
FROM with_next
ORDER BY customer_id, purchase_rank;

出力イメージ

customer_idorder_datecurrent_categorypurchase_ranknext_categorynext_order_date
C0012024-01-08ヘアケア1スキンケア2024-02-12
C0012024-02-12スキンケア2ヘアケア2024-04-18
C0012024-04-18ヘアケア3ボディケア2024-08-01
C0012024-08-01ボディケア4NULLNULL

最後の購買（purchase_rank = 4）は次の購買がまだないので NULL になる。これは LEAD が参照する「次の行」が存在しないためだ。

STEP 3 ― 初回 → 2回目の遷移パターンを集計する（完成版）

purchase_rank = 1 の行だけを使い、「初回購買カテゴリ → 2回目購買カテゴリ」の組み合わせを集計する。

-- STEP3: 初回→2回目 カテゴリ遷移の集計（完成版）

WITH order_category AS (
    SELECT
        o.order_id,
        o.customer_id,
        o.order_date,
        oi.category,
        ROW_NUMBER() OVER (
            PARTITION BY o.order_id
            ORDER BY SUM(oi.quantity * oi.unit_price) DESC
        )  AS category_rank
    FROM orders       o
    JOIN order_items  oi  ON o.order_id = oi.order_id
    WHERE o.status = 'completed'
    GROUP BY o.order_id, o.customer_id, o.order_date, oi.category
),
representative_category AS (
    SELECT order_id, customer_id, order_date,
           category AS main_category
    FROM order_category
    WHERE category_rank = 1
),
purchase_ranked AS (
    SELECT
        customer_id,
        order_date,
        main_category,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY order_date ASC
        )  AS purchase_rank
    FROM representative_category
),
with_next AS (
    SELECT
        customer_id,
        main_category  AS first_category,
        purchase_rank,
        LEAD(main_category, 1) OVER (
            PARTITION BY customer_id
            ORDER BY purchase_rank ASC
        )  AS second_category,
        LEAD(order_date, 1) OVER (
            PARTITION BY customer_id
            ORDER BY purchase_rank ASC
        )  AS second_order_date,
        order_date     AS first_order_date
    FROM purchase_ranked
),
-- 初回購買のみ抽出
first_to_second AS (
    SELECT
        customer_id,
        first_category,
        first_order_date,
        second_category,
        second_order_date,
        -- 2回目購買までの日数
        DATE_DIFF('day', first_order_date, second_order_date)  AS days_to_second
    FROM with_next
    WHERE purchase_rank = 1  -- 初回購買のみ
)
SELECT
    first_category,
    second_category,

    -- 初回購買者数
    COUNT(customer_id)                                   AS first_buyers,

    -- 2回目購買者数（NULLでない場合）
    COUNT(second_category)                               AS second_buyers,

    -- 2回目購買率（リテンション率）
    ROUND(
        COUNT(second_category) * 100.0
        / NULLIF(COUNT(customer_id), 0)
    , 1)                                                 AS retention_rate_pct,

    -- 2回目購買までの平均日数
    ROUND(AVG(days_to_second), 0)                        AS avg_days_to_second

FROM first_to_second
GROUP BY first_category, second_category
ORDER BY first_category, second_buyers DESC;

完成した出力イメージ

first_categorysecond_categoryfirst_buyerssecond_buyersretention_rate_pctavg_days_to_second
ヘアケアスキンケア1,28464149.938
ヘアケアヘアケア1,28431224.328
ヘアケアNULL（離脱）1,28433125.8—
スキンケアヘアケア89234839.042
スキンケアスキンケア89226729.931
スキンケアNULL（離脱）89227731.1—
ギフトセットNULL（離脱）41228970.1—
ギフトセットスキンケア4126816.552
ギフトセットヘアケア4125513.448

読み取れることが多い。

  • ヘアケアから入った顧客の2回目購買率は74.2%（100% – 25.8%）と高く、中でも「ヘアケア → スキンケア」という経路が最多（49.9%）だ
  • ギフトセットから入った顧客は70.1%が離脱しており、2回目購買率が非常に低い。ギフト購買は贈り物需要なので、自分用リピートにならないのは自然だが、この顧客層への施策設計を変える根拠になる
  • 「スキンケア → スキンケア」（同カテゴリリピート）は平均31日で最も早い。消耗品としてのサイクルが短いことを示している

応用：初回カテゴリ別の「最終的な定着率」を比較する

2回目購買だけでなく「3回以上購買した定着顧客の割合」を初回カテゴリ別に比較すると、どのカテゴリが長期定着の入口として優れているかが分かる。

-- 初回カテゴリ別の長期定着率比較

WITH order_category AS (
    SELECT
        o.order_id, o.customer_id, o.order_date, oi.category,
        ROW_NUMBER() OVER (
            PARTITION BY o.order_id
            ORDER BY SUM(oi.quantity * oi.unit_price) DESC
        )  AS category_rank
    FROM orders o JOIN order_items oi ON o.order_id = oi.order_id
    WHERE o.status = 'completed'
    GROUP BY o.order_id, o.customer_id, o.order_date, oi.category
),
first_category AS (
    -- 初回購買カテゴリ
    SELECT
        customer_id,
        MIN(order_date)  AS first_order_date,
        FIRST_VALUE(category) OVER (
            PARTITION BY o2.customer_id
            ORDER BY o2.order_date ASC
        )  AS first_cat
    FROM order_category o2
    WHERE category_rank = 1
    GROUP BY customer_id, category, order_date
    QUALIFY ROW_NUMBER() OVER (
        PARTITION BY customer_id
        ORDER BY order_date ASC
    ) = 1
),
total_orders_per_customer AS (
    SELECT
        customer_id,
        COUNT(DISTINCT order_id)  AS total_orders
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
)
SELECT
    fc.first_cat                             AS first_category,
    COUNT(fc.customer_id)                    AS total_new_customers,

    -- 2回以上購買（初回リテンション）
    SUM(CASE WHEN tp.total_orders >= 2 THEN 1 ELSE 0 END)  AS repeat_buyers,
    ROUND(SUM(CASE WHEN tp.total_orders >= 2 THEN 1 ELSE 0 END) * 100.0
        / NULLIF(COUNT(fc.customer_id), 0), 1)             AS repeat_rate_pct,

    -- 5回以上購買（ロイヤル顧客化）
    SUM(CASE WHEN tp.total_orders >= 5 THEN 1 ELSE 0 END)  AS loyal_buyers,
    ROUND(SUM(CASE WHEN tp.total_orders >= 5 THEN 1 ELSE 0 END) * 100.0
        / NULLIF(COUNT(fc.customer_id), 0), 1)             AS loyal_rate_pct

FROM first_category              fc
JOIN total_orders_per_customer   tp  ON fc.customer_id = tp.customer_id
GROUP BY fc.first_cat
ORDER BY loyal_rate_pct DESC;

出力イメージ

first_categorytotal_new_customersrepeat_rate_pctloyal_rate_pct
ヘアケア1,28474.238.4
スキンケア89268.931.2
ボディケア64161.424.8
メイク39848.214.1
ギフトセット41229.96.8

ヘアケアから入った顧客の38.4%が5回以上の購買に達している。ギフトセットからの6.8%と比べると約6倍の差だ。新規獲得の広告費をどのカテゴリに向けるかの判断基準として非常に強い。

実務での運用ヒント

① 代表カテゴリの決め方は用途次第で変える

今回は「売上金額が最大のカテゴリ」を代表にしたが、「最も数量が多いカテゴリ」「最初に追加したカテゴリ」など設計の選択肢はある。重要なのは定義を統一してチームで合意しておくことだ。

② 同日に複数注文した顧客への対処

EC では同日に2回注文するケースが稀にある（カートに入れ忘れた商品を追加注文など）。同日複数注文を「1回の購買」としてまとめたい場合は、ROW_NUMBER の前に DATE_TRUNC('day', order_date) でまず日次に集約してから purchase_rank を振るとよい。

③ 「カテゴリ → カテゴリ」遷移をサンキー図で可視化する

STEP3 の集計結果は、BIツールのサンキー図（フロー図）と相性が抜群だ。Tableau なら「初回カテゴリ」を左端、「2回目カテゴリ」を右端、フローの太さを second_buyers にマッピングするだけでそのまま可視化できる。Looker Studioでも同様の表現が可能だ。

まとめ

今回のクエリの骨格を振り返る。

  1. order_items と orders を JOIN し、注文ごとに「売上金額最大のカテゴリ」を代表として選ぶ（ROW_NUMBER で1位を残す）
  2. 顧客ごとに購買日順で purchase_rank を振り、LEAD で「次のカテゴリ」を同じ行に並べる
  3. purchase_rank = 1（初回購買のみ）に絞り、first_category × second_category の組み合わせで集計する
  4. 2回目購買率・平均日数・長期定着率を初回カテゴリ別に比較して施策設計に使う

この分析は「新規顧客をどう育てるか」の戦略に直結する。
入口カテゴリによって定着率がこれだけ違うなら、広告のターゲティング・ランディングページ・初回レコメンドの設計をすべてこの経路データを起点に設計し直す価値がある。

次のアクション

SQLやデータ活用を、手元で試しながら理解する

記事で読んだ考え方を、SQL練習場や関連カテゴリの記事でさらに深掘りできます。相談やご依頼もお問い合わせページから受け付けています。

SQL練習場で試す お問い合わせ

この記事を書いた人：martechfarm

Treasure Data Top Lapidarist Award受賞。

SQL / Digdag / Python / CDP設計 / CRM設計を横断し、企業のデータ活用を支援。

実績・支援内容を見る →
Survival curve analysis chart showing system reliability decreasing from 100% to near 0% over time
顧客分析SQL 約25分で読めます

生存時間分析をSQLで近似する方法｜顧客が離脱するまでの期間を測る

サバイバル分析を用いることで、顧客離脱の時期とパターンを把握し、継続率だけでは見えない顧客像を明確化する。Kaplan-Meier推定量を利用し、打ち切りデータを正しく処理することで、顧客の生存率やハザード率を算出し、リテンション戦略に活用できる。

続きを読む

MarTech Farmをもっと見る

今すぐ購読し、続きを読んで、すべてのアーカイブにアクセスしましょう。

続きを読む