青の統計学-DS Playground-

比率と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にする前に、次のような定義表を作ると安全です。

項目 例
指標名 購入率
目的 登録顧客のうち、購入まで進んだ割合を見る
分子 完了注文をした顧客数
分母 登録顧客数
対象期間 2026年5月
除外条件 キャンセル注文、テスト注文を除く
更新頻度 日次
注意点 同一顧客の複数注文を重複カウントしない

この表があると、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) とは意味が少し違います。

指標 意味
AVG(unit_price) 明細行ごとの単価の平均
SUM(quantity * unit_price) / SUM(quantity) 販売数量で重みづけした平均単価

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


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つの数字だけで判断すると誤解することがあります。

KPI 一緒に見るとよい指標 理由
CVR 分母数、購入者数 少数データでは変動が大きい
平均注文金額 注文件数、最大値 高額注文の影響を受ける
構成比 実数の売上、前月比 構成比だけでは成長か縮小かわからない

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

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


9. 悪いKPIと良いKPI

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

悪いKPI例 問題点 改善例
売上 何と比べるかが不明 月別売上、前年比、カテゴリ別売上
ユーザー数 登録、訪問、購入のどれか不明 月間購入者数、月間訪問者数
CVR 分母と分子が不明 商品ページ閲覧者のうち購入した割合
平均単価 外れ値の影響が不明 平均、中央値、件数を一緒に見る

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


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

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

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

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


ミニ演習

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

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

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


まとめ

指標 計算の考え方 例
平均注文金額 売上合計 ÷ 注文件数 SUM(total_amount) / COUNT(*)
平均商品単価 明細売上 ÷ 販売数量 SUM(quantity * unit_price) / SUM(quantity)
CVR 成果数 ÷ 対象数 購入者数 ÷ 登録顧客数
構成比 部分 ÷ 全体 カテゴリ売上 ÷ 全体売上
ゼロ除算対策 分母0を避ける NULLIF(denominator, 0)

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


この章の確認

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

関連演習