ダウンロード サンプルファイル (2.0 KB)
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の通りになります。
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関数でrのランク値(1,2,3)に「r_」のプレフィックスを付けます
(2)RランクごとのFランク数集計
Rランクごとにグルーピングした後、各Fランクの個数を数えて集計します。
まとめ
今回はRFM分析を取り上げました。
強力な顧客分析手法であり、購買履歴さえあればSQLで分析を実施できることがお分かりいただけたと思います。
次回はSQL分析結果の可視化をテーマとして取り上げる予定です。
この記事は参考になりましたか?
既に会員の方はを行ってください。
- この記事の著者
-
WINGSプロジェクト 西 潤史郎(ニシ ジュンシロウ)
< WINGSプロジェクト について>有限会社 WINGSプロジェクト が運営する、テクニカル執筆コミュニティ(代表 山田...
※プロフィールは、執筆時点、または直近の記事の寄稿時点での内容です
-
山田 祥寛(ヤマダ ヨシヒロ)
静岡県榛原町生まれ。一橋大学経済学部卒業後、NECにてシステム企画業務に携わるが、2003年4月に念願かなってフリーライターに転身。Microsoft MVP for Visual Studio and Development Technologies。執筆コミュニティ「WINGSプロジェクト」代表。主な著書に「独習シリーズ(Java・C#・Python・PHP・Ruby・JSP&サーブレットなど)」「速習シリーズ(ASP.NET Core・Vue.js・React・TypeScript・ECMAScript、Laravelなど)」「改訂3版JavaScript本格入門」「これからはじめるLaravel実践入門」「はじめてのAndroidアプリ開発 Kotlin編 」他、著書多数。
※プロフィールは、執筆時点、または直近の記事の寄稿時点での内容です
この記事は参考になりましたか?
この記事をシェア
