比率とKPI: CVR、平均単価、構成比を作る

Stage 2 — 第5章 | データ分析基礎カリキュラム 推定学習時間：35〜45分 | 難易度：★★★☆☆

この章で学ぶこと

集計関数を使うと、件数や売上を出せるようになります。 しかし実務の分析では、単純な件数や合計だけでなく、比率や平均を使って状態を判断することが多いです。

たとえば、ECサイトでは次のようなKPIがよく使われます。

• CVR: 訪問や顧客のうち、購入に至った割合
• 平均単価: 1注文あたり、または1商品あたりの平均金額
• 構成比: 全体売上のうち、各カテゴリが占める割合

この章では、集計結果を組み合わせて比率とKPIを作る考え方を学びます。

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

• 分子と分母を意識して比率を作れる
• 平均注文金額や平均商品単価を計算できる
• カテゴリ別売上構成比を作れる
• KPIを見るときの注意点を説明できる

1. KPIは「判断に使う指標」

KPIは、Key Performance Indicatorの略で、事業や施策の状態を判断するための重要指標です。 SQLで数字を出すだけなら、COUNT や SUM で十分なこともあります。 しかし、意思決定では「その数字が良いのか悪いのか」を比べる必要があります。

たとえば、売上が100万円でも、訪問者が1万人だったのか、500人だったのかで意味は変わります。 そのため、比率や平均を使って、規模の違いをならして見ることがあります。

KPIを作るときは、まず分子と分母を言葉で説明できるようにします。

2. KPI定義書を作る

実務では、SQLを書く前にKPIの定義をそろえます。 同じ「購入率」という名前でも、分母を登録顧客にするのか、訪問者にするのか、商品ページ閲覧者にするのかで数値は変わります。

KPIをSQLにする前に、次のような定義表を作ると安全です。

この表があると、SQLの書き方も自然に決まります。 分子・分母・期間・除外条件が曖昧なままSQLを書くと、数字は出ても判断に使えないKPIになりがちです。

3. 平均注文金額を作る

平均注文金額は、売上合計を注文件数で割った指標です。 AVG(total_amount) でも出せますが、考え方としては SUM / COUNT です。

SELECT
            SUM(total_amount) AS sales,
            COUNT(*) AS order_count,
            SUM(total_amount) / COUNT(*) AS average_order_amount
          FROM orders
          WHERE status = 'completed';
          

このSQLは、完了注文の売上合計、注文件数、平均注文金額を出します。

平均は便利ですが、少数の高額注文に引っ張られることがあります。 そのため、平均を見るときは件数や最大値も一緒に確認すると安全です。

4. 平均商品単価を作る

注文明細から、販売された商品の平均単価を考えます。 ここでは、売上合計を販売数量で割ります。

SELECT
            SUM(quantity * unit_price) AS sales,
            SUM(quantity) AS sold_units,
            SUM(quantity * unit_price) / SUM(quantity) AS average_unit_price
          FROM order_items;
          

このSQLの平均単価は、商品1点あたりの平均販売価格です。 AVG(unit_price) とは意味が少し違います。

数量が多い商品ほど実際の売上への影響が大きいため、実務では分子と分母を確認して使い分けます。

5. CVRを作る

CVRは Conversion Rate の略で、何かが成果に転換した割合を表します。 ECでは、購入率として使われることがあります。

ここでは簡単な例として、登録顧客のうち完了注文をした顧客の割合を計算します。

SELECT
            COUNT(DISTINCT o.customer_id) AS buyer_count,
            COUNT(DISTINCT c.customer_id) AS customer_count,
            COUNT(DISTINCT o.customer_id) * 1.0
              / COUNT(DISTINCT c.customer_id) AS purchase_rate
          FROM customers AS c
          LEFT JOIN orders AS o
            ON c.customer_id = o.customer_id
            AND o.status = 'completed';
          

このSQLでは、分子が「完了注文をした顧客数」、分母が「登録顧客数」です。 * 1.0 は、小数で割り算するための書き方です。

CVRは、分母の定義が特に重要です。 訪問者を分母にするのか、登録顧客を分母にするのか、商品ページ閲覧者を分母にするのかで意味が変わります。

6. カテゴリ別の構成比を作る

構成比は、全体のうち各カテゴリがどれだけを占めるかを見る指標です。 カテゴリ別売上と全体売上を組み合わせます。

SELECT
            p.category,
            SUM(oi.quantity * oi.unit_price) AS category_sales,
            SUM(oi.quantity * oi.unit_price) * 1.0
              / total.total_sales AS sales_share
          FROM order_items AS oi
          JOIN products AS p
            ON oi.product_id = p.product_id
          CROSS JOIN (
            SELECT SUM(quantity * unit_price) AS total_sales
            FROM order_items
          ) AS total
          GROUP BY
            p.category,
            total.total_sales
          ORDER BY sales_share DESC;
          

このSQLでは、カテゴリ別売上を全体売上で割っています。 sales_share が0.35なら、そのカテゴリが売上全体の35%を占めるという意味です。

構成比を見ると、売上の中心カテゴリや依存度がわかります。 ただし、構成比が高いカテゴリは「伸びている」とは限りません。 全体売上が下がっていても、他カテゴリがもっと下がれば構成比は上がるためです。

7. ゼロ除算に注意する

比率を作るときは、分母が0になる可能性に注意します。 分母が0のまま割り算すると、エラーになったり、結果が不正になったりします。

SELECT
            SUM(total_amount) AS sales,
            COUNT(*) AS order_count,
            SUM(total_amount) / NULLIF(COUNT(*), 0) AS average_order_amount
          FROM orders
          WHERE status = 'completed';
          

NULLIF(COUNT(*), 0) は、注文件数が0ならNULLにします。 これにより、0で割ることを避けられます。

実務のダッシュボードでは、分母が0のときにNULL、0、または「対象なし」のどれで表示するかを決めておく必要があります。

8. KPIは単独で判断しない

KPIは便利ですが、1つの数字だけで判断すると誤解することがあります。

たとえばCVRが20%でも、分母が5人なら購入者は1人です。 一方、CVRが5%でも、分母が10万人なら購入者は5,000人です。

比率を見るときは、必ず分母と分子の実数も一緒に確認しましょう。

9. 悪いKPIと良いKPI

KPIは、数字として計算できればよいわけではありません。 改善アクションにつながる形で定義されているかが重要です。

良いKPIは、見た人が次の判断に進めます。 たとえば「購入率が下がった」だけでなく、「分母の訪問者数は増えたが、購入者数が伸びていない」と言えると、商品ページや価格、在庫など次に確認すべき場所が見えます。

10. KPIをSQLにするときの手順

KPIを作るときは、いきなりSQLを書くより、次の順番で考えると安全です。

1. 何を判断したいかを決める
2. 分子を言葉で定義する
3. 分母を言葉で定義する
4. 対象期間と除外条件を決める
5. SQLで集計し、分子・分母・比率を一緒に表示する

たとえば「購入率」を見るなら、分母が登録顧客なのか訪問者なのかを先に決めます。 SQLは、その定義を表にするための手段です。

ミニ演習

次のKPIについて、分子・分母・除外条件を言葉で定義してみましょう。

1. 購入率
2. 平均注文金額
3. カテゴリ別売上構成比
4. リピート購入率

定義できたら、SQLで必ず一緒に出すべき実数も考えてください。 たとえば購入率なら、購入率だけでなく、購入者数と分母人数も一緒に表示します。

まとめ

この章のキーメッセージ： 比率とKPIは、分子と分母の定義がすべてです。SQLで計算する前に、何を何で割っているのかを言葉で説明できるようにしましょう。

この章の確認

1. 平均注文金額の分子と分母を説明してください。
2. 登録顧客のうち購入した顧客の割合を出すとき、分母は何ですか？
3. 構成比を見るとき、実数の売上も一緒に確認したほうがよい理由を説明してください。
4. 比率計算で分母が0になる可能性がある場合、どのような対策ができますか？

関連演習

• SQL入門（集計関数）
• SQL入門（GROUP BY）
• SQL基礎（指標設計と比較分析）