2017.05.04
第4回 デシル分析でユーザーを10段階のグループに分ける
サービスを提供することは、ユーザーに対して何かしらの価値を提供することです。サービス運用にあたり、サービス提供側がユーザーに関して知りたい情報は、下記の2つに大きく分類できます。
・ユーザーの属性（年齢、性別、居住地）
・ユーザーの行動（購入する商品、利用する機能、利用する頻度）
本節では、ユーザーの属性と行動に関する情報を集計し、ユーザー行動の調査やサービス改善の糸口となるレポートと集計するSQLのうち、データを10分割して重要度を把握する「デシル分析」と呼ばれる手法を紹介します（デシル分析の「デシ」は10分の1を意味します）。
書籍ではChapter5-1「ユーザー全体の特徴・傾向を見つける」で解説している内容です。
ユーザーの特徴を分析する際に、性別や年齢などのデータがあれば属性に分けて特徴を調べられます。
デモグラフィックなデータが存在しない場合は、ユーザーのアクションで属性を定義できます。属性を与えることで、各属性の特徴を分析して、よりサービスの利用を推し進めるため、属性に適した施策を検討することが可能になります。
本項では、ユーザーの購入金額に応じてランク分けして、重要度を把握するレポートを作成します。デシル分析の手順は以下の通りです。
1 ユーザーを購入金額の多い順番に並べ替える。
2 並び替えたユーザーの上位から10%ずつデシル1 からデシル10 までのグループに割り当てる。
3 各グループの購入金額の合計を集計する。
4 全体の購入金額に対して、各デシルでの購入金額の割合（構成比）を計算する。
5 上位から累積でどの程度の比率を占めるかの構成比累計を集計する。
上記の手順に従い、下表のレポートを作成するためのSQLをコード例に示します。
最初にユーザーを購入金額が多い順に並べ替え、並び替えたユーザーの上位から10%ずつデシル1からデシル10までのグループに割り当てます。等間隔にデータをグルーピングするには、NTILEウィンドウ関数を使用します。
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
;
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でデシルごとに集約し、集約関数とウィンドウ関数を組み合わせて、一度にこれらの値を計算できます。
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
;
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;
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に対して送信することが可能になると、よりデータが活用されていくことになるでしょう。
