MTD YTDの作り方
マーケティング業務において売上の分析をするためにMTDやYTDといった指標を使うことがあります。
今回はそれをtaleauで計算する方法を紹介したいと思います。
※参考にしたのはTableau社のプリセールスエンジニアの杉村さんのTableauを極めるのDay4です
MTD/YTDとは
MTDとは
その月の初日から同月のその時点までの数値、
YTDとは
年の初日から今日までの数値を表します。
通常のレポートであれば今日まででよいのですが
今回はサンプルスーパーストアのデータを使うのでレポート日を任意で設定してそこまでのMTD、YTDを表すように作成します。
作成手順
①レポート日を設定するパラメータを作る
②MTDの計算フィールド作成
【条件】
レポート日より前のオーダー日であること かつ レポート日と同じ月であること（月の差が0）
以下で計算します。
②去年のMTDの計算フィールド作成
【条件】
オーダー日に1年足した日付がレポート日より前であること かつ オーダー日に1年足した日の月とレポート日の月が同じであること
※ZN関数は計算結果がNULLの場合も0を返すという関数になります。②の計算にも適用しました。
③YTDの計算フィールド作成
【条件】
レポート日より前のオーダー日であること かつ レポート日と同じ年であること（年の差が0）
以下で計算します。
④去年のYTDの計算フィールド作成
【条件】
オーダー日に1年足した日付がレポート日より前であること かつ オーダー日に1年足した日付がレポート日と同じ年であること
以下で計算します。
今日までのレポートでよい場合は
計算フィールドを作成する際にレポート日のパラメーターを使用せず
today()に置き換えればOKです。
レポート日を2014/5/23に設定したらこのような結果になります。
念のため2013/5/23のレポートも出してみると、2014/5/23の1年前のデータにはちゃんとこの日の数字が入っていますね。
そして2012年のデータはサンプルスーパーストアにはないので去年の数字は0が入っています。
（ZN関数のおかげでNULLになっていない）
自分が求めたい数字がどのような計算で求められるか考えるのは難しいですが
他の方が使っている計算式を読み解くと「あ～～その考え方か～～～！！！」とすっきりします（笑）
1から自分で考えられるようになるのはなかなか難しいですが、色々と試しながら考え方を習得していきたいです…！
WINDOW関数について
DATA Saber TrainingのIntermediate2で突然出てきて、その後Advancedでも多用されているWINDOW関数について理解があいまいだったので備忘録として整理したいと思います。
WINDOW関数とは
WIDOW関数とはWINDOW内の集計結果を返す関数のことで
WINDOW_SUM、WINDOW_MAX、WINDOW_AVGなどがよく使われます。
WINDOW内の集計結果…？と言葉だけではわからないので実際にサンプルスーパーストアのデータで計算したいと思います。
これはオーダー年、カテゴリごとに見たSUM([売上])になります。
代表的なWINDOW関数（WINDOW_SUM、WINDOW_MAX、WINDOW_AVG )を入れて計算結果を見てみましょう。
①WINDOW_SUM
数式：WINDOW_SUM(SUM([売上])
上記のような結果になります。
これはオーダー年ごとの売上の合計値を計算しています。
なので計算結果は2013年はどのカテゴリも同じ値が入っています。
ちなみに、どの範囲で集計するのかは「次を使用して計算」から変更することができます。
（デフォルトは表（横）に沿った計算になります）
表（下）を使用した計算結果はこちら↓
表（下）に沿った計算をするとカテゴリごとの合計値が計算されます。
※参考までにLOD計算のEXCLUDEを使っても同じ結果を出すことができます！
数式
表（横）→{EXCLUDE[カテゴリ]:SUM([売上])}
表（下）{EXCLUDE[オーダー日]:SUM([売上])}
②WINDOW_MAX
数式：WINDOW_MAX(SUM([売上])
それぞれ表（横）と表（下）に沿った計算を表示しています。
表（横）を使った計算だと、その年の中で売上が1番大きかったカテゴリの数字が返ってきます。
2013年であれば家電のカテゴリが1番売上が大きいので14,084,755という数字になります。
表（下）を使った計算だと、そのカテゴリの中で売上が1番大きかった年の数字が返ってきます。
家電カテゴリであれば2015年の売上が1番大きいので24,615,551という数字が返ってきます。
③WINDOW_AVG
数式：WINDOW_AVG(SUM([売上])
同じように表（横）と表（下）に沿った計算を表示しています。
表（横）を使った計算だと、その年のカテゴリごと売上の平均が返ってきます。
2013年だと(家具13,659,370+家電14,084,755+事務用品10,127,254)÷3=12,623,793という計算になります。
表（下）を使った計算だと、そのカテゴリの4年間の売上平均が返ってきます。
Vizでの活用方法
WINDOW関数がどのような計算なのかがわかったところでVizでの活用方法を紹介します。
WINDOW関数をVizで使うときは「色」に入れることがほとんどだと思うのでその説明をしたいと思います。
よく使われる2つのパターンを紹介します。
活用例①売上が最大値のグラフを色付けして強調したい
使う関数：WINDOW_MAX
数式の例：WINDOW_MAX(SUM([売上]))=SUM([売上])
これはWINDOW_MAXで計算した値と同じ値のものを「真」としてそれ以外のものを「偽」とする、という意味です。
年、カテゴリごとに見た売上のグラフで、にWINDOW関数を入れてデフォルトの表（横）を使って計算するとこのようになります。
表（横）なのでオーダー年に沿って最大値を計算し、それと同じ値のものを「真」としてオレンジ色に塗っています。
2013年、2014年は家具と家電の売上が拮抗していて、色の塗分けがないと一目ではどちらが最大かわかりにくいですが、オレンジ色に塗られていることですぐに最大値がでどれか認識できますね。
カテゴリだと3つだけなので色塗りをしなくても最大値を認識するのにそこまで時間がかからないですが、列に「地域」を追加してグラフの数を増やすと色塗りをしないとわかりにくいです。
「地域」を使用してWINDOW_MAXを計算しています。
ぱっと見たときの理解度が全然違いますね。
上記のように列にカテゴリ、地域の2つが入っているときに「表（横）」を使って計算すると、オーダー年という切り口で見たときのカテゴリ&地域別売上の最大値になってしまい、以下のように1つしか真になりません。
何を使って計算しているかを意識しないと思っていたものと違う結果が返ってきてしまうので、注意が必要です。
表（横）とか表（下）というのは集計表をイメージするとわかりやすいと思います！
ちなみに表（下）を使って計算するとこのようになります。
カテゴリごとに売上が最大だった年のグラフがオレンジで塗られます。
活用例②平均値を超えているグラフを色付けして強調したい
使う関数：WINDOW_AVG
数式の例：WINDOW_AVG(SUM([売上]))<=SUM([売上])
これはWINDOW_AVGを使って計算した売上平均の値以上のものを「真」、平均より小さいものを「偽」とする、という意味です。
年、地域ごとに見た売上のグラフで、WINDOW関数を色に入れてデフォルトの表（横）を使って計算するとこのようになります。
オーダー年ごとの地域別売上の平均を計算し、それ以上の地域は「真」としてオレンジ色に塗っています。
わかりやすいようにアナリティクスラインで平均線も表示してみました。
アナリティクスラインだけでも表現できますが例えば2013年の九州の売上は平均を超えているのかわかりにくいです。
まとめ
今回紹介した活用例①も②もWINDOW関数を使わなくてもグラフをよく見ればわかりますが、色塗りがされていた方が断然わかりやすいことが明らかだと思います。
Tableauは大量のデータをビジュアライズ化することに適したツールです。
その特性を最大限に活用するにもこのような技はどんどん活用していきたいですね！！
RFM分析のやり方
Advanced1のQ2~4で解いた問題の復習で
サンプルスーパーストアのデータを活用してRFM分析をしたいと思います。
RFM分析とはRecency（最新購入日）、Frequency（購入頻度）、Monetary（購入金額）の３軸で顧客を分類する方法です。
優良顧客、離反顧客、新規顧客などグループ分けすることによって、それぞれのグループにあったコミュニケーションをとることができるようになります。
それでは早速作成していきます。
1.Recency
最終購入日からどれくらい経過したかを調べます。
①顧客ごとの最終購入日を出す
②基準日（=今回はサンプルスーパーストアの中の1番最新の購入日）との日数の差を出す
Recencyだけをグラフにしたものがこちら↓
ビンを作成して30日ごとの分布を見れるようにしました
（追記：間違えてオーダーIDの個別カウントをしているのですが顧客IDの個別カウントが正しいです）
③ランク分けをする
今回は以下の5つにランク分けをしました。
担当するサービスに合わせた設定が必要です。
2.Frequency
顧客ごとの購入回数を調べます。
Frequencyだけをグラフにしたものがこちら↓
6回購入者が一番多いなんて継続率の高い事業ですね。
②ランク分けをする
こちらも5つのランクに分けました。
1回だけのランクは必ず作成したほうが良いと思います。
3.Monetary
顧客ごとの売り上げを調べます。
Monetaryだけをグラフにしたものがこちら↓
②ランク分けをする
こちらも5つのランクに分けました。
4.RFM分析
RFM分析といってもTableauは二次元なので2つの項目ずつ関係性を見るのがよいと思います。
①R×F
縦軸にFrequency Rank、横軸にRecency Rank、色にオーダーごとの売上の平均を入れてビジュアライズしました。
テキストには顧客名の個別カウントを入れています。
色は何を入れるのか悩んだのですが…
Monetaryで作成した「顧客ごとの売上」の合計を入れても当然人数が多いほど色が濃くなるし、平均を入れたら当然Frequency Rankが高いほど色が濃くなるし…
今回はオーダーごとの売上の平均を入れてみましたが示唆は得られなかったです。
もしかして何度も購入している人は1回あたりのオーダー金額が低いのかなと思ったのですが、そうでもなかったです。
左上の購入頻度が高く、購入期間があいていない層が多くいますが、
右上の直近の新規ユーザーが0なので新規獲得に課題があります。
また90日以上購入がないユーザーもかなりいるのでリテンション施策が必要だと思います。
②R×M
縦軸にRecency Rank、横軸にMonetary Rank、色とテキストには顧客名の個別カウントを入れています。
（R×Fのときは色を使って無理やり売上の要素を入れてみたのですがいまいちだったのでやめました）
30万円以上の購入層になると90日以内に購入しているユーザーが過半数になりました。
購入金額が高い層は複数回購入している割合が高いので当然かもしれないです。
③F×M
縦軸にFrquency Rank、横軸にMonetary Rank、色とテキストには顧客名の個別カウントを入れています。
これは本当に当たり前の結果で購入回数が多いほど購入金額も高いという結果になりました。
RFM全ての掛け合わせでビジュアライズしてみましたが、
R×F 以外は有益な情報は得られませんでした…。
F×MやR×Mで有益な情報が得られる場合もあるのでしょうか？
5.ダッシュボードの作成
Recency Rank とFrequency Rankの表と
サブカテゴリ別購入回数のハイライト表でダッシュボードを作成しました。
購入頻度が高いユーザーとそれ以外のユーザーで購入品の違いがあるか見ることができます。
↓10回以上購入しているユーザー
↓それ以外のユーザー
10回以上購入しているユーザーはそれ以外のユーザーと比較して文房具の購入回数が多いように見えます。
つまり文房具購入したユーザーは今後も継続的に購入してくれる可能性が高いので、
文房具のプロモーション強化などを検討するとよいかもしれません。
Tableau Publicのリンク
https://public.tableau.com/app/profile/kaoru.kishiue/viz/RFM_16557188378230/Recency?publish=yes
今後マーケティング施策の検討に活用したいと思い備忘録として残しました。
他の人が作成したVizも参考にしてより良いビジュアライズ手法を学んでいきたいと思います。
世界の女性議員の比率のデータでVizを作成してみた
世界の女性議員の比率のデータを使ってVizを作成しました。
＜データ引用元＞
今回はMakeover Mondayのデータを活用しました。
Tableauの練習になるデータを見つけるのが少し苦戦しましたが、
世界中のTableauユーザーの方が作ったVizを見るのは勉強になりました！
１．作成したViz
https://public.tableau.com/app/profile/kaoru.kishiue/viz/FEMALEPOLITICALREPRESENTATION/10#1
■工夫したところ
・世界地図とページ機能を使って推移が表示されるようにした。
・ハイライト表を使って国ごと、年ごと推移がわかるようにした。
・2019年女性比率TOP10の国が10年ごとにどのように推移しているかわかるようにし、ツールヒントにランキングが表示されるようにした。
■備忘録
・ページに入れる項目は「不連続」でなければならない。
・2019年、2009年、1999年だけの数値を比較したいとき、フィルターで選択してもよいが今回は以下の計算フィルターを使用した。
DATEDIFF('year',[Year],TODAY())=3
or DATEDIFF('year',[Year],TODAY())=13
or DATEDIFF('year',[Year],TODAY())=23
・TOP10でセットやグループを作成してしまうと、ランキングがそのセット内のランキングだけになってしまうのでTOP10以外の「非表示」にする。
２．気づき
・1997年と2019年の世界地図を比較すると、アフリカ南東部の国が著しく比率が伸びている。
・日本は1997年4.6%→2019年10.1%と世界的に見ても低い水準。
・2019年のTOP10を見るとスウェーデンやフィンランドは過去から高い比率を維持していたが、ルワンダやボリビアなど急激に比率が上がっている国がある。
------------------------------------------------
なぜルワンダやボリビアで急激に女性議員の比率が伸びているのか調べてみると日本では採用されていない「クオータ制」という制度がありました！
クオータ制はノルウェー発祥の制度で、政治では議員、企業では役員などで、女性の割合が一定になるようにする制度のことです。
ルワンダでは憲法で国の「指導的機関の地位のうち少なくとも30％を女性が占めるものとする」と規定され、急激に女性の議席率が伸びたそうです。
議席数に対して女性の比率を定めている国以外にも候補者数に対して一定の基準を定めている国や、
憲法で規定していなくても政党が独自で党内の女性議員の比率を定めている国がありました。
私は（一応）法学部出身なのですが、恥ずかしながらこのような制度があることを知りませんでした。
日本は先進国の中でも男女格差が大きい国だとよく言われていて、男女格差を測るジェンダーギャップ指数はG7の中でも最下位です。（2021年3月の発表では153か国中120位。G7に限定しなくても低すぎる順位ですね。)
このような状況を改善するためにも、日本の現状をきちんと理解すること、男女平等が実現している国ではどのような制度を導入しているのか知ることが大切だと思いました。
今回のViz作成では
データを見て、なんでだろう？と考えたり調べることで新しい気づきを得ることができる良い体験ができました。
食料生産量のデータでVizを作ってみた
今回は1960年度～2020年度の食料生産量のデータを使ってVizを作成してみました。
＜データ引用元＞
データの接続が苦手だったので前回詳しく書いたのですが慣れてきたので省略します。
１．作成したViz
https://public.tableau.com/app/profile/kaoru.kishiue/viz/_16551100148690/1?publish=yes
■工夫したところ
・1つめのシートは年ごとの推移とカテゴリごとの傾向が見えるようにエリアチャートを使用した
・2つめのシートはカテゴリの生産量の推移とサブカテゴリの生産量の推移を表して内訳が見れるようにした
・エリアチャートでカテゴリを選択したらフィルターがかかるようにアクションを設定した
２．気づき
①日本の食料生産量は1986年をピークに減少傾向が続いている
②特に顕著なのは魚介類 内訳をみると飼肥料と塩干、くん製のサブカテゴリが特に減少している
③肉類、牛乳及び乳製品のカテゴリは増加している
カテゴリ単位で生産量が減少していても、サブカテゴリごとに見ると増加している項目もありデータをきちんと深堀していく必要性を感じました。
家計調査のデータでVizを作ってみた
今回は、県庁所在地別の1世帯あたりの品目別支出金額のデータを使って
肉類の年間支出金額の傾向がわかるようにVizを作成しました。
＜データ引用元＞
１．Tableauへ接続する前にデータを整える
e-statsにあるデータをそのまま接続するとtableauがうまく読み込めなさそうだったので不要な行や列を削除しました。
・今回は都道府県（県庁所在地）ごとにまとめるので都市階級、地域ごとのデータは削除
・支出金額の比較をしたいので数量の列は削除
・肉の種類ごとに分かれているが、一部合計した数字が入っているので削除
（編集前のデータで接続してもTableau側の操作で整えられそうですが、ビギナーなのでひとまず接続前に編集しました。）
＜Before＞
＜After＞
かなりすっきりしました。
２．データ接続
①データソースの追加
そのまま接続するとこんなかんじ。
NULLがあったり、肉の種類ごとに列が分かれていてTableauでは処理がしにくいので編集。
・データインタープリターの使用
・肉の種類の列を選択してピボット
・不要な列の非表示
横並びのデータ（アンスタックデータ）から縦並びのデータ（スタックデータ）に変わりました。
②都道府県情報、地域情報のシートと結合
元のデータだと県庁所在地情報しか入っていないので、都道府県情報や地域情報が入ったテーブルと結合しました。
3．Vizの作成
今回作成したダッシュボードです！
https://public.tableau.com/app/profile/kaoru.kishiue/viz/2020_16542441520020/sheet0
■工夫したところ
・肉の種類をドロップダウンのフィルターで選択できるようにした
・日本地図を使ってざっくりどの地域が支出金額が多いかわかるようにした
・ツリーマップで地域ごとの平均支出金額がわかるようにした
・都道府県別の棒グラフを作成して上位の都道府県がわかるようにした
・ツリーマップと棒グラフは地域ごとに色分けした
・ツールヒントで必要な情報が表示されるように編集した
・ダッシュボードアクションを設定した
■備忘録
①マップ
・詳細→都道府県、色→金額を入れる
②ツリーマップ
・地方別平均支出金額を計算
ROUND*1,0)
・サイズ→地方別平均、色→地域を入れる
③都道府県ランキング
・列→金額、行→都道府県、色→地域を入れる
・軸の編集>各行または列の独立した軸範囲 を選択
４．気づき
①牛肉の支出金額は近畿地方が多い
（近畿地方は2位の中国地方より約1万円も多く圧倒的！）
②鶏肉の支出金額は九州地方が多い
③豚肉の支出金額は東日本が多い
④他の生鮮肉は北海道（ジンギスカン？）と熊本県（馬肉？）がツートップ
⑤寒い地域（北海道、東北、信越）は合いびき肉の支出金額が少ない
牛肉の傾向がかなり顕著で興味深いです。
神戸牛、近江牛などたしかにブランド牛が多いイメージですね。
５．最後に
師匠と相談してなんとなく選んだテーマでしたが、肉の種類ごとに地域傾向があって予想以上に面白い結果が得られました。
こういう切り口のデータもあったらもっとわかりやすいかも…などと試行錯誤しながVizを作成するのも初めてで楽しかったです。
一人前のDATA Saberになれるよう色々なデータでVizを作成に挑戦したいと思います。
