マナティ

ビッグデータ分析・活用のためのSQLレシピ

第4回 デシル分析でユーザーを10段階のグループに分ける

サービスを提供することは、ユーザーに対して何かしらの価値を提供することです。サービス運用にあたり、サービス提供側がユーザーに関して知りたい情報は、下記の2つに大きく分類できます。

・ユーザーの属性(年齢、性別、居住地)
・ユーザーの行動(購入する商品、利用する機能、利用する頻度)

本節では、ユーザーの属性と行動に関する情報を集計し、ユーザー行動の調査やサービス改善の糸口となるレポートと集計するSQLのうち、データを10分割して重要度を把握する「デシル分析」と呼ばれる手法を紹介します(デシル分析の「デシ」は10分の1を意味します)。
書籍ではChapter5-1「ユーザー全体の特徴・傾向を見つける」で解説している内容です。

 ユーザーの特徴を分析する際に、性別や年齢などのデータがあれば属性に分けて特徴を調べられます。
 デモグラフィックなデータが存在しない場合は、ユーザーのアクションで属性を定義できます。属性を与えることで、各属性の特徴を分析して、よりサービスの利用を推し進めるため、属性に適した施策を検討することが可能になります。

●図:デシル分析イメージ

4-1.png

 本項では、ユーザーの購入金額に応じてランク分けして、重要度を把握するレポートを作成します。デシル分析の手順は以下の通りです。

1 ユーザーを購入金額の多い順番に並べ替える。
2 並び替えたユーザーの上位から10%ずつデシル1 からデシル10 までのグループに割り当てる。
3 各グループの購入金額の合計を集計する。
4 全体の購入金額に対して、各デシルでの購入金額の割合(構成比)を計算する。
5 上位から累積でどの程度の比率を占めるかの構成比累計を集計する。

 上記の手順に従い、下表のレポートを作成するためのSQLをコード例に示します。

●表:デシル分析

4-2.png

 最初にユーザーを購入金額が多い順に並べ替え、並び替えたユーザーの上位から10%ずつデシル1からデシル10までのグループに割り当てます。等間隔にデータをグルーピングするには、NTILEウィンドウ関数を使用します。

●コード:購入額の多い順にユーザーグループを10 分割するクエリ
WITH
user_purchase_amount AS (
  SELECT
      user_id
    , SUM(amount) AS purchase_amount
  FROM
    action_log
  WHERE
    action = 'purchase'
  GROUP BY
    user_id
)
, users_with_decile AS (
  SELECT
      user_id
    , purchase_amount
    , ntile(10) OVER (ORDER BY purchase_amount DESC) AS decile
  FROM
    user_purchase_amount
)
SELECT *
FROM users_with_decile
;

sankaku.png

 user_id | purchase_amount | decile
---------+-----------------+--------
 U019    |           17800 |      1
 U006    |           16700 |      1
 U013    |           15700 |      1
 U021    |           14700 |      2
 U029    |           14600 |      2
 U004    |           14400 |      2
...
 U005    |            3200 |      9
 U012    |            2500 |      9
 U002    |            2100 |      9
 U016    |            2100 |     10
 U023    |            2000 |     10
 U018    |             100 |     10

次のステップ 続いて、各グループの合計・平均購入金額と、累計の購入金額、全体の購入金額などの集約を計算します。GROUP BYでデシルごとに集約し、集約関数とウィンドウ関数を組み合わせて、一度にこれらの値を計算できます。

●コード:10 分割したデシルごとの集約を行うクエリ
WITH
user_purchase_amount AS (
  -- 略
)
, users_with_decile AS (
  -- 略
)
, decile_with_purchase_amount AS (
  SELECT
    decile
    , SUM(purchase_amount) AS amount
    , AVG(purchase_amount) AS avg_amount
    , SUM(SUM(purchase_amount)) OVER (ORDER BY decile) AS cumulative_amount
    , SUM(SUM(purchase_amount)) OVER () AS total_amount
  FROM
    users_with_decile
  GROUP BY
    decile
)
SELECT *
FROM
  decile_with_purchase_amount
;

sankaku.png

 decile | amount | avg_amount | cumulative_amount | total_amount
--------+--------+------------+-------------------+--------------
      1 |  50200 |   16733.33 |             50200 |       280700
      2 |  43700 |   14566.66 |             93900 |       280700
      3 |  41600 |   13866.66 |            135500 |       280700
      4 |  38500 |   12833.33 |            174000 |       280700
      5 |  34300 |   11433.33 |            208300 |       280700
      6 |  28200 |    9400.00 |            236500 |       280700
      7 |  20400 |    6800.00 |            256900 |       280700
      8 |  11800 |    3933.33 |            268700 |       280700
      9 |   7800 |    2600.00 |            276500 |       280700
     10 |   4200 |    1400.00 |            280700 |       280700

次のステップ 最後に、構成比と構成比累計を計算します。

●コード:購入額の多いデシル順に構成比と構成比累計を計算するクエリ
WITH
user_purchase_amount AS (
  -- 略
)
, users_with_decile AS (
  -- 略
)
, decile_with_purchase_amount AS (
  -- 略
)
SELECT
    decile
  , amount
  , avg_amount
  , 100.0 * amount / total_amount AS total_ratio
  , 100.0 * cumulative_amount / total_amount AS cumulative_ratio
FROM
  decile_with_purchase_amount;

sankaku.png

 decile | amount | avg_amount | total_ratio | cumulative_ratio
--------+--------+------------+-------------+-------------------
      1 |  50200 |   16733.33 |       17.88 |            17.88
      2 |  43700 |   14566.66 |       15.56 |            33.45
      3 |  41600 |   13866.66 |       14.82 |            48.27
      4 |  38500 |   12833.33 |       13.71 |            61.98
      5 |  34300 |   11433.33 |       12.21 |            74.20
      6 |  28200 |    9400.00 |       10.04 |            84.25
      7 |  20400 |    6800.00 |        7.26 |            91.52
      8 |  11800 |    3933.33 |        4.20 |            95.72
      9 |   7800 |    2600.00 |        2.77 |            98.50
     10 |   4200 |    1400.00 |        1.49 |           100.00

ワンポイント
 本項で紹介したデシル分析を行った上で、各デシルの特徴を他の分析手法で細かく調査して傾向を見出すと、より詳細なユーザー像を把握できます。
 例えば、デシル7~10はまだ定着していないカスタマーなので、メルマガでリテンションを高めるなどの施策で成長を促すなど、高い精度の施策を計画立案する際に役立ちます。また、既存のメルマガ配信ツールと絡めて、デシル7~10に対して送信することが可能になると、よりデータが活用されていくことになるでしょう。

著者プロフィール

加嵜 長門(著者)
株式会社DMM.comラボ所属。慶應義塾大学大学院 政策・メディア研究科修士課程修了。大学院や学生ベンチャーにて、マルチメディアデータベースを対象とした検索やレコメンドアルゴリズムの研究およびサービス開発に従事し、現在DMM.comラボではビッグデータ活用基盤の構築に携わり、SparkやSQL on Hadoopを用いたレコメンド機能、ビッグデータ活用の研究開発を担当。 共著に『詳解Apache Spark』(技術評論社)。
田宮 直人(著者)
データコンサルタント。エンジニアとして大手新聞社の関連サービス、求人サービス、コミュニティサービスの開発に携わり、株式会社サイバーエージェント在籍時にデータアナリストへ転身、株式会社DMM.comラボではマーケティング開発部マネージャーとしてビッグデータ部を立ち上げる。現在はフリーランスとして、データの解析のみならず、データ解析環境の設計・構築、ログの設計、レコメンドAPIの作成など、データに関連する業務全般を担当している。