SQLサンプルと解説［3］
RFMを2次元表で分析する
RFMの3つの指標のうち2つを使い、2次元表で顧客を捉える分析手法もあります。以下ではRFを用いた2次元表の分析を例に示します。
図3のようにRFのランクごとに顧客のステージを分類します。
購買の頻度が高く直近でも購買している（表の左上の）顧客は「優良顧客」、直近購買日が古い（表の下の）顧客は離れていってしまった「離脱顧客」になります。
「旧優良顧客」はこのまま購買行動をとらなければ離脱する状態にある顧客で、再度購買行動をとってもらうことで、再び優良顧客になるような施策（新商品の告知など）を行います。
「新規顧客」は2回目3回目と購買してもらえれば優良顧客になるため、そうなるような施策（新規顧客限定のクーポンなど）を行います。
データは時系列で蓄積しておき、施策を行った際に各分類の顧客数がどのように変化しているか、といった検証に活用します。
RFを用いた2次元の集計において、欲しい結果は以下の通りです。
r_rank | f_3 | f_2 | f_1 --------+-----+-----+----- r_3 | 0 | 0 | 1 r_2 | 1 | 0 | 1 r_1 | 0 | 0 | 1
SQLはリスト5の通りになります。
リスト5 RFM分析SQL―RF2次元集計（rfm-2d.sql）
With user_rank_table as (
略（リスト1の中身）
)
select
concat('r_',r_rank) as rank,/*（1）*/
count(case when f_rank = 3 then 1 end) as f_3,/*（2）*/
count(case when f_rank = 2 then 1 end) as f_2,
count(case when f_rank = 1 then 1 end) as f_1
from
user_rank_table
group by
r_rank
order by
rank desc
;
（1）Rランクの生成
Concat関数でｒのランク値（1,2,3）に「r_」のプレフィックスを付けます
（2）RランクごとのFランク数集計
Rランクごとにグルーピングした後、各Fランクの個数を数えて集計します。
まとめ
今回はRFM分析を取り上げました。
強力な顧客分析手法であり、購買履歴さえあればSQLで分析を実施できることがお分かりいただけたと思います。
次回はSQL分析結果の可視化をテーマとして取り上げる予定です。
