SQLで"データの正規化"をすることでCRMデータを分析しやすくする

概要

CRM（顧客管理）に、SFA（案件進捗管理）的な用途として、
顧客との折衝記録が回数別に残されている場合、
元のデータだけでは、営業担当社員の成績などを分析し難い問題があります。

例えば、下記にサンプルデータとして顧客との接触順により、
フラグステータス1~5（flag）、
記載日時1〜5（flagRecordDate）、
商談者1〜5（flagWrite）などを所持するテーブルが存在します。

| customerId | customerName | status   | finalContactDate | flag01 | flagRecordDate01 | flagWriter01 | flag02 | flagRecordDate02 | flagWriter02 | flag03 | flagRecordDate03 | flagWriter03 | flag04 | flagRecordDate04 | flagWriter04 | flag05 | flagRecordDate05 | flagWriter05 |
|------------|--------------|----------|------------------|--------|------------------|--------------|--------|------------------|--------------|--------|------------------|--------------|--------|------------------|--------------|--------|------------------|--------------|
| 1          | 佐藤 翔太　　　    | 受注     | 2024/8/20        | 見込み | 2024/8/1         | 稲田 武      | 多忙   | 2024/8/5         | 杉山 明王    | 多忙   | 2024/8/7         | 杉山 明王    | 多忙   | 2024/8/9         | 杉山 明王    | 受注   | 2024/8/10        | 小山 淳二    |
| 2          | 鈴木 花子　　　    | 未決着   | 2024/8/15        | 留守   | 2024/8/2         | 杉山 明王    | 留守   | 2024/8/4         | 杉山 明王    | 見込み | 2024/8/10        | 小山 淳二    | 受注   | 2024/8/20        | 小山 淳二    |        |                  |              |
| 3          | 高橋 一郎　     | 受注     | 2024/8/20        | 多忙   | 2024/8/1         | 杉下 茂      | 多忙   | 2024/8/7         | 稲田 武      | 多忙   | 2024/8/9         | 稲田 武      | 多忙   | 2024/8/12        | 稲田 武      | 失注   | 2024/8/15        | 杉下 茂      |
| 4          | 中村 次郎　     | 失注     | 2024/8/12        | 留守   | 2024/8/3         | 小山 淳二    | 多忙   | 2024/8/6         | 杉下 茂      | 多忙   | 2024/8/9         | 杉下 茂      | 見込み | 2024/8/11        | 稲田 武      | 受注   | 2024/8/12        | 稲田 武      |
| 5          | 田中 美咲　     | 未決着   | 2024/8/17        | 見込み | 2024/8/2         | 杉山 明王    | 多忙   | 2024/8/10        | 小山 淳二    | 失注   | 2024/8/15        | 杉下 茂      |        |                  |              |        |                  |              |

しかし、このデータから、
どの商談者が一番受注を獲得しているのかなどを分析することは難しいです。

そこで、データを正規化することで、分析を行いやすくします。

手順

まずはデータがBigQueryなどに入っていない場合、
保存します（SQLが実行できるサンドボックスなどでも大丈夫です）。

flagごとにレコードを作るのを目的に、SQLクエリを記述します。
この時、欲しい情報が他にもあれば一緒に記述します。

-- フラグがNULLではないデータを選択し、結果を表示

-- flag01がNULLでないレコードを選択
SELECT
  customerId,
  customerName,
  status AS status,
  flag01 AS flag,
  flagRecordDate01 AS flagDate,
  flagWriter01 AS writer
FROM
  `hogehoge.hoge.hoge1`
WHERE flag01 IS NOT NULL

UNION ALL

-- flag02がNULLでないレコードを選択
SELECT
  customerId,
  customerName,
  status AS status,
  flag02 AS flag,
  flagRecordDate02 AS flagDate,
  flagWriter02 AS writer
FROM
  `hogehoge.hoge.hoge1`
WHERE flag02 IS NOT NULL

UNION ALL

-- flag03がNULLでないレコードを選択
SELECT
  customerId,
  customerName,
  status AS status,
  flag03 AS flag,
  flagRecordDate03 AS flagDate,
  flagWriter03 AS writer
FROM
  `hogehoge.hoge.hoge1`
WHERE flag03 IS NOT NULL

UNION ALL

-- flag04がNULLでないレコードを選択
SELECT
  customerId,
  customerName,
  status AS status,
  flag04 AS flag,
  flagRecordDate04 AS flagDate,
  flagWriter04 AS writer
FROM
  `hogehoge.hoge.hoge1`
WHERE flag04 IS NOT NULL

UNION ALL

-- flag05がNULLでないレコードを選択
SELECT
  customerId,
  customerName,
  status AS status,
  flag05 AS flag,
  flagRecordDate05 AS flagDate,
  flagWriter05 AS writer
FROM
  `hogehoge.hoge.hoge1`
WHERE flag05 IS NOT NULL

すると結果はこうなります。
flag列がある場合、新しいレコードになりましたね。

なお、上記はクエリ結果を表示するクエリですが、
下記のようにやれば新しくテーブルを作り、結果を入れることも可能です。

-- 新しいテーブルを作成
CREATE OR REPLACE TABLE `hogehoge.hoge.hoge2` (
  customerId INT,
  customerName STRING,
  status STRING,
  flag STRING,
  flagDate DATE,
  writer STRING
);

-- hogehoge.hoge.hoge1からデータを選択し、新しいテーブルに挿入
INSERT INTO `hogehoge.hoge.hoge2` (
  customerId,
  customerName,
  status,
  flag,
  flagDate,
  writer
)
-- flag01がNULLでないレコードを選択し、挿入
SELECT
  customerId,
  customerName,
  status AS status,
  flag01 AS flag,
  flagRecordDate01 AS flagDate,
  flagWriter01 AS writer
FROM
  `hogehoge.hoge.hoge1`
WHERE flag01 IS NOT NULL

UNION ALL

-- flag02がNULLでないレコードを選択し、挿入
SELECT
  customerId,
  customerName,
  status AS status,
  flag02 AS flag,
  flagRecordDate02 AS flagDate,
  flagWriter02 AS writer
FROM
  `hogehoge.hoge.hoge1`
WHERE flag02 IS NOT NULL

UNION ALL

-- flag03がNULLでないレコードを選択し、挿入
SELECT
  customerId,
  customerName,
  status AS status,
  flag03 AS flag,
  flagRecordDate03 AS flagDate,
  flagWriter03 AS writer
FROM
  `hogehoge.hoge.hoge1`
WHERE flag03 IS NOT NULL

UNION ALL

-- flag04がNULLでないレコードを選択し、挿入
SELECT
  customerId,
  customerName,
  status AS status,
  flag04 AS flag,
  flagRecordDate04 AS flagDate,
  flagWriter04 AS writer
FROM
  `hogehoge.hoge.hoge1`
WHERE flag04 IS NOT NULL

UNION ALL

-- flag05がNULLでないレコードを選択し、挿入
SELECT
  customerId,
  customerName,
  status AS status,
  flag05 AS flag,
  flagRecordDate05 AS flagDate,
  flagWriter05 AS writer
FROM
  `hogehoge.hoge.hoge1`
WHERE flag05 IS NOT NULL;

test2というテーブルが作成されました。

もしBIなどに連動する場合はここまでで十分使い物になりますが、
今スグ結果を見たい！!ついでに営業担当のフラグ割合も見たい！!!
という無茶な要望が来た場合にはさらにSQLで追い込みます。

下記のようにwriter（営業担当者）の値ごとでflagの各回数を数えて「count」に入れて、
その割合も「percentage」カラムに入れるクエリを作成します。

-- `hogehoge.hoge.hoge2`テーブルからwriterごとのフラグ数と割合を計算

-- 共通表現式(CTE)を使って各writerの合計フラグ数を取得
WITH writer_total AS (
  SELECT
    writer,
    COUNT(*) AS total_count
  FROM
    `hogehoge.hoge.hoge2`
  GROUP BY
    writer
)

-- 各writerごとのフラグの割合を計算
SELECT
  a.writer,                -- 営業担当
  a.flag,                  -- フラグ
  COUNT(*) AS count,       -- フラグの数
  ROUND((COUNT(*) / b.total_count) * 100, 1) AS percentage -- 各フラグの割合(パーセンテージ)を小数点第1位まで四捨五入
FROM
  `hogehoge.hoge.hoge2` a
JOIN
  writer_total b           -- 合計フラグ数とジョイン
ON
  a.writer = b.writer
GROUP BY
  a.writer,                -- writerごとにグループ化
  a.flag,                  -- flagごとにグループ化
  b.total_count            -- 合計フラグ数でグループ化
ORDER BY
  a.writer,                -- 結果をwriterごとに並べる
  a.flag                   -- 次にフラグごとに並べる

すると、各営業担当者ごとに立てたフラグの種類ごとで数と割合がカウントされました。

これにより、小山 淳二さんは40%の確率で受注を獲得している、
などの分析ができるようになりましたね。めでたしめでたし。