【Sisense Widget】サクッとデシル分析してみる

こんにちは。Turtleです。

ついにプレミアリーグ20/21シーズンが開幕した。早速9/15, AM04:15~のブライトンvsチェルシーをリアルタイムで観戦したのだが、やっぱり応援している我らがチェルシーが勝利すると気持ちが良いね。

とはいえ試合内容はまだ改善の余地がありそう。そもそもCLバイエルン戦（8/9）が終了してからまだ一ヶ月くらいしか経ってない。オフシーズンもプレシーズンも十分に無いのだから、コンディションが万全なわけがない（それはどのチームも似たような状況なんだけどさ）
新戦力のチームへのフィット具合と、選手一人一人のコンディションが上がることでチームがこれからどう変わっていくのかが楽しみで仕方がない今シーズン。寝不足気味の未来が見えた...

おっと、気を抜くとChelsea Knowledgeになってしまいそうだ...

気を取り直して。ここはSisense Knowledge。
今回はサクッとデシル分析をやっていく。あくまで「サクッと」ね。

1. デシル分析とは

今回行っていく「デシル分析」とは、全顧客を購入金額が高い順に10等分して、そのグループ（デシル1～10）の購入比率や売上高構成比を算出する分析方法のことである。この比率や構成比によって、売上によく貢献してくれている優良顧客層を知ることができる。また、そこから深堀りしていくことで優良顧客層の特徴を発見することができる。

以下は100人の顧客を購入金額順に並べて10グループにランク付けする際のイメージ。

ただし、購入金額だけによる分類なので、例えば一度だけ高額な買い物をしてその後は一度も購入したことがないような顧客も上位のグループに含まれる可能性があるということは考慮しておかなければならない。

今回は、【Sisenseはじめの一歩：データモデリング編】で使用したデータを使って、デシル分析のための簡単なピボットテーブルとパレート図を作成していく。まだ読んだことが無い方はデータのダウンロードもこちらからできるので是非。

...え、パレート図って何ですかって？そんな方にピッタリな、Michaelの記事があるんですよ。是非参考にしてください。

完成イメージはこちら。

2. 準備

デシル分析と今回の目的物について簡単に押さえたところで、さっそく顧客を売上金額に応じて10のグループに分けていく。

最初にゴールを確認しておこう。今回は以下のようなデータモデルを目指す。[Fact_売上], [Dim_顧客]に[Dim_デシル]を加えた3つのテーブルを顧客IDで繋ぐシンプルなものだ。なお、[Dim_デシル]は「顧客ID」と「デシル」の2つのカラムから成るテーブルとなっている。（顧客IDをキーにして[Dim_顧客]とジョインする選択肢がもちろん考えられるが、今回は分かりやすくするために別テーブルにする）

つまり、[Dim_デシル]を新しくつくるだけなんだけど...これが意外と面倒だった。Sisenseはカスタムテーブルを作成する際にSQLを書くのだが、With句やウィンドウ関数を使用することができない。この制約があるため、少しSQLは工夫が必要となる。（ntileとか使いたいですよね...）

今回は以下のように顧客を10分割した。もちろん他にも方法はたくさんあると思うので、是非いろいろ試してみてほしい。（もっと良い方法があれば是非ご教授ください）

SELECT
    [顧客ID]
-- ラインに基づいて顧客を10分割
  , (CASE
     WHEN total <= line9 THEN 'デシル10'
     WHEN line9 < total AND total <= line8 THEN 'デシル9'
     WHEN line8 < total AND total <= line7 THEN 'デシル8'
     WHEN line7 < total AND total <= line6 THEN 'デシル7'
     WHEN line6 < total AND total <= line5 THEN 'デシル6'
     WHEN line5 < total AND total <= line4 THEN 'デシル5'
     WHEN line4 < total AND total <= line3 THEN 'デシル4'
     WHEN line3 < total AND total <= line2 THEN 'デシル3'
     WHEN line2 < total AND total <= line1 THEN 'デシル2'
     WHEN line1 < total THEN 'デシル1'
     ELSE NULL
     END) AS デシル
FROM
    (
    SELECT [顧客ID], SUM([売上金額]) AS total
      FROM [Fact_売上]
     GROUP BY [顧客ID]
    ) tbl_total

CROSS JOIN

    (
    SELECT
-- 売上金額に応じて10分割するためのラインを作成
        PERCENTILE(total, 0.1) AS line9
      , PERCENTILE(total, 0.2) AS line8
      , PERCENTILE(total, 0.3) AS line7
      , PERCENTILE(total, 0.4) AS line6
      , PERCENTILE(total, 0.5) AS line5
      , PERCENTILE(total, 0.6) AS line4
      , PERCENTILE(total, 0.7) AS line3
      , PERCENTILE(total, 0.8) AS line2
      , PERCENTILE(total, 0.9) AS line1
    FROM
-- 顧客IDごとに売上金額の合計を算出
        (
        SELECT [顧客ID], SUM([売上金額]) AS total
          FROM [Fact_売上]
         GROUP BY [顧客ID]
        ) tmp
    ) tbl_line

上記のSQLを順番に説明していく。まずは[tbl_line]の部分から。ここでは顧客を合計売上金額に基づいて10分割するための基準線を作成している。

先ほど確認した[tbl_line]と、顧客IDごとに合計売上金額を算出した[tbl_total]をCROSS JOINし、顧客IDごとの合計金額と分割基準となるラインを横並びにする。

CASE文を使って、合計売上金額に基づいて顧客をそれぞれどのデシルに所属させるかを決定する。

以上、簡単な説明で申し訳ないが、[Dim_デシル]テーブルを作成するSQLの説明である。ざっくり内容を確認したら先述のSQLコードを貼り付けて[Dim_デシル]を作成しビルドする。これで準備は完了だ。

3. ウィジェット作成

ピボットテーブル

まずはピボットテーブルを作成する。値として今回は「顧客数」「売上金額」「売上金額比率」「累積売上金額比率」「1人当たり売上金額」の計５つを算出している。どれも難しくない数式なので、是非トライしてみてほしい。

できただろうか。解答例は以下の通り。

①顧客数：# of unique 顧客ID もしくは COUNT([顧客ID])
➁売上金額：SUM([売上金額])
③売上金額比率：CONTRIBUTION(SUM([売上金額]))
④累積売上金額比率：RSUM(CONTRIBUTION(SUM([売上金額])))
⑤1人当たり売上金額：SUM([売上金額]) / # of unique 顧客ID

CONTRIBUTIONやRSUMがいまいちよくわからないという方は、冒頭でリンクを張っておいたMichaelの記事を読んでみてほしい。（冒頭に戻るのが面倒な方のために、もう一度リンクを張っておくね）

出来上がったピボットテーブルを見ると分かるように、デシル1の全体の10%の顧客で売上の半分以上、デシル1~3の全体の30%の顧客で売上の実に85%もの割合を占めているんだね。こうしてみると、デシル1に分類される顧客は1人当たりの購入金額も凄まじい...一体どんな買い物をしているのか気になる方は、是非深掘って調べてみてください！

パレート図

次はパレート図を作成する。先ほど作成した「売上金額」「累積売上金額比率」を縦棒と線で表したウィジェットなのだが...パレート図に関しては度々登場したMichaelの記事と全く同じ方法で作成することができる。是非自分で作成してみてほしい。

完成図は以下の通り。ピボットテーブルの数値を見てもデシル1がずば抜けて売上金額に貢献していることや、累積売上金額比率がデシル1~3の全体の30%の顧客の段階で80%を超えていることは分かるのだが、パレート図にして可視化することでそれが一目で分かるようになった。

4. まとめ

今回は、【Sisenseはじめの一歩：データモデリング編】で使用したデータを使って、デシル分析のための簡単なピボットテーブルとパレート図を作成してみた。SisenseのSQLはWith句やウィンドウ関数が使えないので不便ではあるが...使えないなら使えないでやりようはいくらでもある。

実は昔デシル分析やろうとして、SQLでウィンドウ関数が使えないと分かってあっさりやめちゃったことがあって...ふと思い出してやってみたら意外と簡単にできたという話。まともにやりもせず簡単に投げ出すのは良くないね。

できないことは代替案を探してできるだけ無くしていきたいよね。

ではまた。