ファネル分析: 行動ステップの落ち方を見る

Stage 4 — 第3章 | データ分析基礎カリキュラム 推定学習時間：30〜40分 | 難易度：★★★☆☆

この章で学ぶこと

ECサイトでは、顧客が登録してから初回注文し、さらにリピート注文するまでに段階があります。 すべての顧客が最後の段階まで進むわけではありません。

この章では、行動ステップごとの人数や割合を見る ファネル分析 を学びます。

この章を終えると、こんなことができるようになります：

• 登録、初回購入、リピート購入の人数を数えられる
• ステップ間の離脱をSQLで確認できる
• COUNT(DISTINCT ...) の使いどころを理解できる
• ファネル分析の前提条件を説明できる

1. ファネル分析とは

ファネル分析は、ユーザーが進むステップを上から順に並べ、どこで人数が減っているかを見る分析です。

ECデータでは、たとえば次のようなステップを考えられます。

この分析では、単なる注文件数ではなく「顧客数」を数えることが重要です。 同じ顧客が複数回注文しても、顧客数としては1人です。

2. 顧客ごとの注文回数を作る

まず、顧客ごとの完了注文回数を集計します。

WITH customer_order_counts AS (
            SELECT
              customer_id,
              COUNT(*) AS completed_order_count
            FROM orders
            WHERE status = 'completed'
            GROUP BY customer_id
          )
          SELECT
            customer_id,
            completed_order_count
          FROM customer_order_counts;
          

この中間結果があると、初回購入者とリピート購入者を判定できます。

3. ステップごとの人数を数える

登録顧客、購入顧客、リピート顧客を1つのSQLで集計します。

WITH customer_order_counts AS (
            SELECT
              customer_id,
              COUNT(*) AS completed_order_count
            FROM orders
            WHERE status = 'completed'
            GROUP BY customer_id
          )
          SELECT
            COUNT(DISTINCT c.customer_id) AS registered_customers,
            COUNT(DISTINCT CASE
              WHEN o.completed_order_count >= 1 THEN c.customer_id
            END) AS purchased_customers,
            COUNT(DISTINCT CASE
              WHEN o.completed_order_count >= 2 THEN c.customer_id
            END) AS repeat_customers
          FROM customers AS c
          LEFT JOIN customer_order_counts AS o
            ON c.customer_id = o.customer_id;
          

LEFT JOIN を使うことで、注文がない顧客も登録顧客として残します。 ここで JOIN にすると、未購入の顧客が消えてしまいます。

4. CVRを計算する

人数だけでなく、次のステップに進んだ割合を見ると、改善ポイントを考えやすくなります。

WITH customer_order_counts AS (
            SELECT
              customer_id,
              COUNT(*) AS completed_order_count
            FROM orders
            WHERE status = 'completed'
            GROUP BY customer_id
          ),
          funnel AS (
            SELECT
              COUNT(DISTINCT c.customer_id) AS registered_customers,
              COUNT(DISTINCT CASE
                WHEN o.completed_order_count >= 1 THEN c.customer_id
              END) AS purchased_customers,
              COUNT(DISTINCT CASE
                WHEN o.completed_order_count >= 2 THEN c.customer_id
              END) AS repeat_customers
            FROM customers AS c
            LEFT JOIN customer_order_counts AS o
              ON c.customer_id = o.customer_id
          )
          SELECT
            registered_customers,
            purchased_customers,
            repeat_customers,
            purchased_customers * 1.0 / registered_customers AS purchase_rate,
            repeat_customers * 1.0 / purchased_customers AS repeat_rate
          FROM funnel;
          

* 1.0 は、小数で割り算するための書き方です。 データベースによっては整数同士の割り算が整数になることがあるため、実務では注意します。

5. ファネル分析の注意点

ファネル分析では、ステップの定義をそろえることが大切です。

同じ「購入率」でも、定義が違えば数値は変わります。 分析結果を共有するときは、必ず定義も一緒に伝えましょう。

実務での使いどころ: どこで落ちているかを見る

ファネル分析は、ユーザーが次のステップへ進む割合を見る分析です。 ECなら、訪問、商品閲覧、カート投入、購入のような流れを確認します。

ファネルでは、人数だけでなく、前のステップからの遷移率を見ます。 どこかで大きく落ちていれば、そのステップに改善余地があるかもしれません。

ただし、ステップの順番や期間定義が曖昧だと、数値の意味も曖昧になります。 「同じ日に購入した人だけを見るのか」「7日以内の購入まで含めるのか」などを先に決めましょう。

ミニ演習

次のファネルを設計してみましょう。

1. 無料会員登録までのファネルを3ステップで定義する。
2. 模擬試験受験までのファネルを3ステップで定義する。
3. 商品購入ファネルで、分母をどのステップにするか考える。
4. ファネル分析で期間定義が重要な理由を説明する。

まとめ

この章のキーメッセージ： ファネル分析では、行数ではなく顧客数を数え、どのステップで落ちているかを確認します。SQLを書く前に、ステップと対象期間を明確にしましょう。

この章の確認

1. ファネル分析では何を確認しますか？
2. 未購入顧客も含めるために、なぜ LEFT JOIN が必要ですか？
3. 購入顧客数を数えるとき、なぜ COUNT(DISTINCT customer_id) を使いますか？
4. リピート購入率の定義例を説明してください。

関連演習

• SQL集計の基礎
• SQL集計（GROUP BY）
• SQL結合の基礎
• データの整理と読み取り