見出し画像

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

@Seiji_suna

TableauではLOD計算が難しいという話を聞きます。

LODの計算がイメージしやすくなるよう、会社で教えている方法を解説します！
Tableauのe-learningの初級-中級くらいを学んだ後に実施してもらう場合が多いです。

データはサンプルスーパーストアを使います。

やることは、RFM分析をExcel、SQL、Tableauそれぞれで行うことです。

RFM分析とは、顧客を以下3つの指標を使った分析手法です。
・Recency 最終購入日(最後に購入した日から経過した日数)
・Frequency 購入頻度(累計購入回数)
・Monetary 購入金額(累計購入金額)

詳しくはGoogle先生やChatGPT先生に聞いてください。

まずデータの粒度の確認

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

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

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

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

①ExcelでRFM分析

最初にExcelから始めます。

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を受講するなかで、ord4とord10はExcelやSQLなど昔ながらのデータ集計をしたことが無い人にとっては実感しづらいと思いました。

LOD計算もExcelやSQLのデータ集計を経験していれば、理解がもっと深まるはずです。

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

この記事が気に入ったらサポートをしてみませんか？