SHOEISHA iD

※旧SEメンバーシップ会員の方は、同じ登録情報(メールアドレス&パスワード)でログインいただけます

CodeZine編集部では、現場で活躍するデベロッパーをスターにするためのカンファレンス「Developers Summit」や、エンジニアの生きざまをブーストするためのイベント「Developers Boost」など、さまざまなカンファレンスを企画・運営しています。

ビジネスデータ解析のためのSQL入門

SQLでRFM分析に挑戦する

ビジネスデータ解析のためのSQL入門 第3回

  • X ポスト
  • このエントリーをはてなブックマークに追加

SQLサンプルと解説[2]

指標・ランクごとの顧客数の集計

 次に、上記で求めた顧客ごとのデータを元に各指標・各ランクでそれぞれ顧客数がどのくらいかを集計します。

 欲しい結果は以下の通りです。

 rfm_rank | r_sum | f_sum | m_sum
----------+-------+-------+-------
        3 |     1 |     1 |     1
        2 |     2 |     0 |     1
        1 |     1 |     3 |     2

 SQLはリスト4の通りになります。

リスト4 RFM分析―ランクごとの顧客集計(rfm-rank-2.sql)
With /*(1)*/
user_rank_table as (
    略(リスト3の中身)
),
index_table as ( /*(2)*/
              select 1 as rfm_index
    union all select 2 as rfm_index
    union all select 3 as rfm_index
),
rfm_flag as ( /*(3)*/
    select
        index_table.rfm_index,
        case when index_table.rfm_index = user_rank_table.r_rank then 1 else 0 end as r_flag,
        case when index_table.rfm_index = user_rank_table.f_rank then 1 else 0 end as f_flag,
        case when index_table.rfm_index = user_rank_table.m_rank then 1 else 0 end as m_flag
    from
        index_table
    cross join
        user_rank_table
)
select /*(4)*/
    rfm_index as rfm_rank,
    sum(r_flag) as r_sum,
    sum(f_flag) as f_sum,
    sum(m_flag) as m_sum
from
    rfm_flag
group by
    rfm_index
order by
    rfm_index desc
;

(1)With句

 ここではリスト3のSQLをサブクエリとして、With句によりuser_rank_tableと名付けています。With句はサブクエリに名前を付け、SQLの前に記述します。これでサブクエリをSQLの中で記述する場合に比べ、視認性を高めることができます。

 With句は次のように記述します。

WITH
  <クエリ名> AS ( select <列>  ~ )

(2)インデックステーブル

 ここでは、後ほどJOINで利用するための番号のみのテーブルを準備しています。

 番号「1」のみを表すテーブルを生成し、これを2、3と順にUNION句によりテーブル連結します。

 このクエリによって得られる途中結果は、以下の通りです。

  rfm_index
-----------
         1
         2
         3

(3)ランク値ごとにフラグを立てる

 リスト4で求めた顧客ごとのランクとインデックス(1~3)のクロスジョインで、各ランクの顧客の有無によりフラグを立てていきます。

 このクエリによって得られる途中結果は、以下の通りです。

 rfm_index | r_flag | f_flag | m_flag
-----------+--------+--------+--------
         1 |      1 |      1 |      1
         1 |      0 |      0 |      0
         1 |      0 |      1 |      0
         1 |      0 |      1 |      1
         2 |      0 |      0 |      0
         2 |      1 |      0 |      0
         2 |      0 |      0 |      1
         2 |      1 |      0 |      0
         3 |      0 |      0 |      0
         3 |      0 |      1 |      1
         3 |      1 |      0 |      0
         3 |      0 |      0 |      0

(4)フラグの集計

 (3)で求めたフラグの数を集計することにより、各指標・各ランクの顧客数が求められます。

図2 3次元図によるRFM分析の例
図2 3次元図によるRFM分析の例

 集計が終われば分析ですが、指標の軸が3つ以上の分析では通常、散布図がよく使われます。図2は少しデータ数を増やしたサンプルをRFM散布図で表現した例です。R・Fを縦横の軸に、Mを色の濃さで表しています。また、円の大きさと数値が顧客数を表しています。

 この例ではFランクの高い顧客が購買金額も大きくなる傾向にあるので、やや顧客数の多いRランク1(新規顧客)やRランク3(常連顧客)に対して購買頻度を高めるアプローチが効果的と考えられます。

次のページ
SQLサンプルと解説[3]

この記事は参考になりましたか?

  • X ポスト
  • このエントリーをはてなブックマークに追加
ビジネスデータ解析のためのSQL入門連載記事一覧

もっと読む

この記事の著者

WINGSプロジェクト 西 潤史郎(ニシ ジュンシロウ)

WINGSプロジェクトについて>有限会社 WINGSプロジェクトが運営する、テクニカル執筆コミュニティ(代表 山田祥寛)。主にWeb開発分野の書籍/記事執筆、翻訳、講演等を幅広く手がける。2018年11月時点での登録メンバは55名で、現在も執筆メンバを募集中。興味のある方は、どしどし応募頂きたい。著書記事多数。 RSS Twitter: @yyamada(公式)、@yyamada/wings(メンバーリスト) Facebook<個人紹介>フリーランスとしてデータ解析エンジニアとして主にビッグデータ関連の仕事をしています。TableauなどBIやビッグデータ処理などビジネスデータ解析環境の構築・運用、また解析系のアプリケーション開発やGoogleアナリティクス活用支援などを行っています。

※プロフィールは、執筆時点、または直近の記事の寄稿時点での内容です

山田 祥寛(ヤマダ ヨシヒロ)

静岡県榛原町生まれ。一橋大学経済学部卒業後、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本格入門」「これからはじめるReact実践入門」「はじめてのAndroidアプリ開発 Kotlin編 」他、著書多数

※プロフィールは、執筆時点、または直近の記事の寄稿時点での内容です

この記事は参考になりましたか?

この記事をシェア

  • X ポスト
  • このエントリーをはてなブックマークに追加
CodeZine(コードジン)
https://codezine.jp/article/detail/10346 2017/08/04 14:00

おすすめ

アクセスランキング

アクセスランキング

イベント

CodeZine編集部では、現場で活躍するデベロッパーをスターにするためのカンファレンス「Developers Summit」や、エンジニアの生きざまをブーストするためのイベント「Developers Boost」など、さまざまなカンファレンスを企画・運営しています。

新規会員登録無料のご案内

  • ・全ての過去記事が閲覧できます
  • ・会員限定メルマガを受信できます

メールバックナンバー

アクセスランキング

アクセスランキング