見出し画像

Tableau FIXED計算の練習方法(RFM分析をExcel、SQL、Tableauそれぞれで行う)

@Seiji_Suna

TableauでのLOD計算が難しいという話を聞きます。
僕が説明する時はTableauのことはいったん忘れて、ExcelやSQLを使って説明します。
そうすると理解されやすいのでおススメです！(特にExcelだとデータの動きが分かりやすいのでおススメ)

Fixedの処理をTableau以外のツールで説明すると・・・

Excelだと

ピボットテーブルを作成し、集計した結果を元のテーブルにVlookupする。
{Fixed [顧客ID]:sum(売上)}
↓
ピボットテーブルの行に顧客ID、値に売上の合計を入れ、顧客IDで元のテーブルにVlookupする。

SQLだと

サブクエリで集計した結果を元のテーブルにJoinする。
{Fixed [顧客ID]:sum(売上)}
↓
顧客ID、sum(売上)をselectし、顧客IDでGroup byし、顧客名で元のテーブルにJoinする。

計算フィールドの考え方

非集計の計算フィールドを作ることは、使用しているテーブルに列を追加することと同じ意味です。
LOD計算をした結果と同じ列をExcelで作りにはどうすればいいか？を考えると理解しやすいです。

具体的な例

RFM分析を題材にしてExcel、SQL、Tableauそれぞれで行い、違いを把握します。
データはサンプルスーパーストアを使います。

RFM分析とは

RFM分析とは、顧客を以下3つの指標を使った分析手法です。

  • Recency 最終購入日(最後に購入した日から経過した日数)

    • 顧客が最後に購入したのがどれくらい最近かを評価。直近で購入した顧客ほど、今後も再購入する可能性が高いとされます。

  • Frequency 購入頻度(累計購入回数)

    • どれくらい頻繁に購入しているかを測定。頻繁に購入する顧客はロイヤリティが高いとされます。

  • Monetary 購入金額(累計購入金額)

    • どれくらいの金額を支出しているかを評価。高額な購入をする顧客は収益に大きく貢献します。

RFM分析の目的は主に3つあります。

  • 顧客セグメンテーション: 高価値顧客、潜在的に離脱する可能性がある顧客、新規顧客などに分類し、それぞれに対して適切なマーケティング施策を打つため。

  • リテンション施策: 再購買を促すキャンペーンやロイヤルティプログラムを計画し、顧客を維持する。

  • 売上向上: 高価値顧客や頻繁に購入する顧客に対して重点的なアプローチを行い、売上を増やす。

RFM分析をすることを目的にするとうまくいかないので、分析した先に何をするのか目的をはっきりさせると良いです。

まずデータの粒度の確認

データを集計する前に、粒度を確認しましょう。

サンプルスーパーストアの「注文」シートは、1行が購入した商品単位のデータです。

データによっては年や月単位にまとめられたものや、商品データのない発注単位のデータもあります。

最初にデータの粒度を把握したうえで、データの集計方法を検討しましょう。

①ExcelでRFM分析

ExcelでRFM分析を行う場合、ピボットテーブルが必須です。(これも使い方はGoogle先生に聞いてください)

Monetaryはピボットテーブルを作り、顧客IDを行に入れ、値に売上を入れると顧客ごとの累計購入金額が集計できます。

Monetaryをピボットテーブルで集計

Frequencyは買上のあった回数や日数を計算します。
今回は購入した日数で計算します。

サンプルスーパーストアのデータを見ると、1日に複数の商品を購入している場合があるため、そのまま買上のあった日数が計算できません。

ピボットテーブルで行に顧客ID、オーダー日を入れたものを作り、それをコピペしてもう一度ピボットテーブルをすることで日数の計算が可能です。

Frequencyをピボットテーブルで集計

元データの粒度が購入した商品単位だったので、日単位にしたあとに再度集計しています。

データを集計したい粒度に整える感覚をすぐに持つのは難しいと思います。
パズルゲームのようなものなので、ゲーム感覚で色々試してみましょう！

Recencyは最後に購入した日から何日経っているか計算します。

ピボットテーブルで行に顧客ID、値にオーダー日の最大値を入れます。

C1セルに基準となる日を入れ、C1セルとB列の値の差を取れば完了です。

Recencyをピボットテーブルで集計

RFMの3つが集計できたので、注文シートにVlookupでデータを取ってきます。

RFMをVlookupした後

RFMでは3つの軸にランクを付けることが多いです。
今回は以下のようにランクを分けます。

R : 1-89日、90-179日、180日-
F : 1日、2-5日、6日-
M : 10万未満、10万-20万未満、20万以上

IF分かマスタを作ってvlookupの近似値検索でランクを付けます。

RFMランクを追加

この注文テーブルからピボットテーブルを作り、RFMの軸を行と列にセットし、

RFM分析の結果

この結果から、売上のボリュームが一番大きいのはMランクとFランクが高く、Rランクが低いことが分かりました。
(RFM分析では売上・頻度の高い優良顧客が離反していないかどうかを調べ、離反を防止するために施策を打つための分析手法です。)

ここまでがExcelのRFM分析です。

ピボットテーブルを作成し、それを元のテーブルにvlookupするのが面倒ですよね。

データ量が少ない場合はこれで対応できますが、データ量が多くなるとExcelでは対応できなくなります。
そんな場合はSQLで集計することが可能です。

SQLでRFM分析

SQLはMS SQL想定で書いています。

DBの構築やDBのインポートはGoogle先生に聞いてください！

RFMを集計し、注文テーブルにjoinするクエリは以下です。

with M as(
select 
  [顧客 ID]
  ,sum([売上]) as Monetary
from [superstore].[dbo].[order]
group by [顧客 ID]
),
F as (
select
  [顧客 ID]
  ,count(DISTINCT [オーダー日]) as Frequency
from [superstore].[dbo].[order]
group by [顧客 ID]
),
R as (
select 
  [顧客 ID]
  ,datediff(day, max([オーダー日]), '2024-01-01') as Recency
from [superstore].[dbo].[order]
group by [顧客 ID]
)
SELECT [行 ID]
      ,[オーダー ID]
      ,[オーダー日]
      ,[出荷日]
      ,[出荷モード]
      ,[order].[顧客 ID]
      ,[顧客名]
      ,[顧客区分]
      ,[市区町村]
      ,[都道府県]
      ,[国/地域]
      ,[地域]
      ,[製品 ID]
      ,[カテゴリ]
      ,[サブカテゴリ]
      ,[製品名]
      ,[売上]
      ,[数量]
      ,[割引率]
      ,[利益]
	  ,[Recency]
	  ,[Frequency]
	  ,[Monetary]
  FROM [superstore].[dbo].[order]
  left outer join R on [R].[顧客 ID] = [superstore].[dbo].[order].[顧客 ID]
  left outer join F on [F].[顧客 ID] = [superstore].[dbo].[order].[顧客 ID]
  left outer join M on [M].[顧客 ID] = [superstore].[dbo].[order].[顧客 ID]

with句を使うとクエリが分かりやすくなるので便利です！

最初のwithの部分がExcelでのピボットテーブルを作る部分です。
一番したのleft outer joinのところが注文テーブルにvlookupでRFMを取ってきているところです。

データベースの元データを更新し、クエリを実行するたびに最新データを得ることができます。
(RFMごとにランクを付ける部分は省略します)

しかし、SQLだけではデータの可視化ができず、以下のような実行結果が得られます。

実行結果

可視化をするためには結果をExcelに入れてグラフを作るか、他の可視化ツールを使う必要があります。

Tableauを使うと簡単に可視化までできるようになります。

③TableauでRFM分析

TableauでのRFM分析です。RFMの計算フィールドは以下のとおりです。

R
{fixed [顧客 Id]:datediff('day', max([オーダー日]), #2024-01-01#)}

F
{fixed [顧客 Id]:countd([オーダー日])}

M
{fixed [顧客 Id]:sum([売上])}

これにランクを付けるディメンションを作ります。

Rランク
if [R] <= 89 then '1_1-89日'
elseif [R] <= 179 then '2_90-179日'
else '3_180日-'
end

Fランク
if [F] = 1 then '1_1日'
elseif [F] <= 5 then '2_2-5日'
else '3_6日-'
end

Mランク
if [M] < 100000 then '1_10万未満'
elseif [M] < 200000 then '2_10万-20万未満'
else '3_20万以上'
end

これを行と列に入れると以下のようになります。

TableauでRFM分析

クロス集計表を出すだけであればSQLでも十分に対応できると思います。

Tableauの一番の良さは、インタラクティブなフィルターアクションです。
RFM分析の表を選択することで、ランクごとの傾向を可視化することができます。

右上のランクを選択した場合のグラフ

このグラフを作ろうと思うとかなりの時間がかかります。

それをTableauが解決してくれます！
なんと便利なツールなのでしょうか。

僕は2009年頃からSQLとExcelを使ってデータの集計・可視化をしていました。
当時はデータの集計間違いや、データを更新する手間がかかる、日本のデータに関するリテラシーが今より低い、など苦労が多かったです。

Tableauは慣れるまでは使いづらかったのですが、今では無くてはならない存在です！

おわりに

DATASaberを受講するなかで、FixedなどLOD計算はExcelやSQLなど昔ながらのデータ集計をしたことが無い人にとっては実感しづらいと思いました。

原因は、データがどのような順番で処理されているかを理解せず、元のデータと結果を見ていることにあると思います。
Tableauの内部でどのように処理されているかを知ると、より理解が深まります！

ExcelやSQLでのレポート作成をやったことが無い人は、ぜひTableauで行っている集計をExcelとSQLを使って作って見てください！

余談ですが、僕はSQLでデータの集計を長年行っていたので、ついFixedを使いがちです。使えば便利だなと思いながら、脳内でSQLに変換してしまうのでinclude/excludeはあまり使いません。
include/excludeも便利なのでぜひ使ってください！

いいなと思ったら応援しよう！