最終試験に向けて復習してみた -Ord8 HandsON Advanced Ⅰ-
DATA Saber Boot Camp Week6 "HandsOn - Advanced I" - YouTube
Q1：売上推移、製品別売上、カテゴリ別累積売上を見たいのですが、それぞれのチャートをなるべく広いスペースを使って表示したです。1画面内で切り換えながら表示できませんか？
動画時間：4:00-15:00/-17:00
・パラメーターで「どのグラフを表示しますか？」をつくる。リストで３つのグラフを整数1，2，3出切り換えできるようにする
・つくったパラメーターを計算フィールドにいれてメジャーにしてからディメンションにし、「表示切替」をつくる
⇒フィルタにいれる・パラメータの文字列で任意のグラフを選択したら、フィルター「表示切換」をパラメータの数字を表す
※任意のパラメータを選択しているときにグラフが表示され、そうでないときは表示されないようになる
・ダッシュボードのコンテナを利用する
⇒垂直コンテナにして、入れ込む※各シートのタイトルを消し、ダッシュボードのタイトルを表示し、タイトルで挿入「シート名」などすると切り換えたのみのタイトルが表示できる
Q2: データ内の最も新しい日付を基準日としたとき、最終購入日から基準日までの日数が60日未満の顧客は何名いるでしょうか？
動画時間：17:00-34:30/-35:00
・最終購入日：{MAX([オーダー日])} とし、購入自体の最終日をみる
⇒不連続にする
・各顧客の最終オーダー日：{FIXED[顧客 Id]:MAX([オーダー日])}
※最終購入日だけでやると、日付が違うディメンションの影響を受けて、日付が変わるため、顧客のみのディメンションで最終オーダー日を設定する必要がある
・最後の購入日はどのくらい前か？：DATEDIFF('day',[最終購入日],[各顧客の最終オーダー日])
⇒最終購入日と各顧客の最終オーダー日の差・最後の購入日はどのくらい前か？なので、60日未満を知るために、ビンにする
・行列にそれぞれ「最後の購入はどれくらい前か？（ビン）」と「個別のカウント(顧客Id)」をいれる
Q3: 顧客の購入回数別で、最も顧客数が多い購入回数はどれですか？購入回数はオーダーIdの個別カウントで算出することができます。
【オプション問題】1回のオーダーにおける金額の平均が高いのは何回購入している顧客ですか？
動画時間：35:00ｰ40:45/- 42:45/-48:40
・顧客ごとの購入回数：{FIXED [顧客Id]：COUNTD([オーダーId])
⇒「顧客ごとの購入回数」と「カウント（顧客Id）」で確認する
⇒「顧客ごとの購入回数」をディメンションにし、連続のままにすることで、行ごとのカウントがみれるため、0回の回数も確認できるようにする。
・メモリでの表現でなく、ヘッダでの表現にするために、「顧客ごとの購入回数」を1区切りのビンとすると単純なディメンションで表現できる⇒WINDOW_MAX(COUNTD[顧客Id])=COUNTD([顧客Id])として、色をつける
-オプション
・平均(売上)を単純に行にいれると「オーダーIdごとでN回購入している人」で割った平均がでて、1回のオーダーにおける平均とはならないため、「顧客ごとの購入回数」のディメンションの中でも、顧客Idごとでしっかりと平均をだすために、
「1オーダーあたりの売上」：｛INCLUDE[オーダーId]：SUM([売上])として、それを平均([1オーダーあたりの売上])として行に入れ込む
⇒WINDOW_MAX（AVG([1オーダーあたりの売上]))=AVG([1オーダーあたりの売上])として、色をかえる
メモ：INCLUDE計算は個人的に覚えずらい。。。
「ビュー内のディメンションを考慮した○○(ディメンション)ごとの集計関数□□の△△(集計関数)」と覚える。
Q4: 顧客ごとの合計売上を見たとき、どの金額レンジ（10万円単位）の顧客が多いでしょうか？
動画時間：48:40-51:00
・顧客ごとの合計売上：{FIXED [顧客Id]：SUM([売上])
・「顧客ごとの合計売上」をビンの作成で10万ごとにする
Q5: 各顧客を初回購入年毎にまとめて、オーダー年毎の売上を割合で表現してください。2016年の売上の何%が2013年からの継続顧客で占められているでしょうか？また、その結果を見て、スーパーストアに潜む課題について言及してください。なお、割合は1年分を100%として計算してください。
動画時間:57:30-1:03:15
・年(オーダー日)と合計(売上)を行列に入れる
⇒合計(売上)については、合計の割合にして、セルごとで計算する
・顧客別初回オーダー日：{FIXED[顧客 Id]:MIN([オーダー日])}
⇒マークの色に入れて、割合をラベルにすることで、各年の各顧客の初回購入年の割合がわかる
Q6: 初めて購入した顧客が、2回目に購入してくれるまでに何四半期空いていることが多いですか？顧客ごとの初回購入四半期ごとに見せてください。
【オプション問題】同じ分類で顧客1名あたありの売上が大きいのはどのグループでしょうか？結果をも元に、最低でも2回目の購入をどのくらいまでの期間中に行ってもらうべきか教えてください。
動画時間：1:03:15-/1:14:45/-1:20:45
・顧客別初回オーダー日： {FIXED[顧客名]:MIN(オーダー日)}
・顧客別2回目以降のオーダー日： IIF([購入日]>[初回購入日],[オーダー日],NULL)
・顧客別2回目のオーダー日： { FIXED [顧客名]:MIN(顧客別2回目以降のオーダー日)}
・1回目と2回目の購入Qの差： DATEDIFF('quater',[顧客別初回オーダー日],[顧客別2回目オーダー日])
・行列にそれぞれ、「1回目と2回目の購入Qの差」と「顧客別初回オーダー日」の四半期・不連続をいれる
⇒マークの色に個別のカウント(顧客 Id)をいれる
・NULLについては、別名の編集で、2回目以降は未購入とする
Q7: 購入月（年は除く）と曜日の組合せで、1日あたりの売上平均が高い組合せを教えてください。【オプション問題】LOD表現を使わないで出す方法も考えてみましょう。
動画時間：1:31:30ー/1:32:45-1:34:10
・日当たり売上(LOD)：{INCLUDE [オーダー日]：SUM([売上])}
・日当たり売上(Basic)：SUM([売上])/COUNTED([オーダー日])
Q8: 年 四半期ごとに、「カテゴリ」で「売上」の差を表示しています。この差の大きさにより年 四半期をソートしたいです。ソートは選択した「カテゴリ」に応じて変わりますが、3つのカテゴリは同時に表示しておいてください。(選択していないカテゴリのものは選択したカテゴリの降順で並んでいればよいです) ※ヒント：ディメンションのソートでは表計算を使用できません。
動画時間：1:34:10-/1:41:30ｰ1:44:50
・パラメータ作成で「ソートカテゴリ」を作成する
⇒文字列、家具/家電/事務用品とする
・ソート別売上(EXCLUDE)：{ EXCLUDE [カテゴリ]: SUM(IF [カテゴリ]=[ソートカテゴリ] then SUM([売上]) end)}
⇒行に入れ、「不連続」、簡易表計算「差」にする
※カテゴリを無視して、もし、カテゴリがソート用カテゴリと一致していたら売上を出すということ
※SUM(IF [カテゴリ]=[ソートカテゴリ] then [売上] end)を行に入れると、カテゴリがソートと一致していたら売上を出すが、ソート用カテゴリが 例)家具のとき、家具のみの売上がでて、その他の家電・事務用品はNULLになるため、このNULL行を取り除くために、EXCLUDE計算で「ビュー内の除外したいものを指定する」ことで、欲しいデータだけ取り出すことができる
・ソート別売上(FIXED)：{ FIXED DATETRUNC('quarter',[オーダー日]):SUM(if [カテゴリ]=[ソートカテゴリ] then [売上] end)}
Q9: 顧客別の購入間隔の平均(初回購入～最終購入の月数÷購入回数)ごとの顧客数と売上を見てください。売上がもっとも高い購入間隔を目標の購入間隔としたとき、現在最も人数の多い購入間隔のメンバーに対し、何か月間隔を縮めるような施策を上てばよいですか
動画時間：1:44:50-1:50:30
・顧客ごとの購入間隔(月)：｛FIXED[顧客Id]:DATEDIFF('mounth',MIN([オーダー日]),MAX(オーダー日))}
※=DATEDIFF('month',{FIXED[顧客Id]}:MIN([オーダー日])},｛FIXED[顧客Id]:MAX([オーダー日])})
・顧客別購入回数：｛FIXED[顧客Id]:COUNTED([オーダーId])｝
・顧客ごとの購入間隔の平均：ROUND(顧客ごとの購入間隔(月)/顧客別購入回数)
Q10: スーパーストアでは新規顧客獲得に苦戦しているという問題を発見しました。新規顧客獲得が比較的進んでいる地域※を見つけ出し、その施策を全国に展開したいと考えています。年月ごとの新規獲得顧客の累計推移を見たとき、最終月に第1位で着地している地域はどこでしょうか？また、その地域はいつから第1位でしょうか？その頃に始めたマーケティングの内容を確認したいと思っています。※地域のとりかた：顧客の住所が誤っていたため「DAY6_Customer_Master.csv」に以下リンクの「地方の情報付き都道府県リスト」を結合してください。コピペ可能な都道府県リスト（地方区分付き） - Qiita
動画時間：1:50:30-/2:03:00-2:06:35
・「Customer_Master.csv」と「注文」を顧客Idで結合する※都道府県で結合するとデータ欠損になるので注意・コピペした「地域/都道府県」を「Customer_Master.csv」の都道府県と結合する
⇒コピペした「地域/都道府県」について、都道府県部分を取り出すために、TRIM( SPLIT( [F1], "/", -1 ) )の計算でわける
・月(オーダー日)と個別のカウント(顧客 Id)の累計を行列にいれると、顧客Idで新規でなくそのあとも数回買った分も含まれるため、
新規/既存：{FIXED[顧客Id]:MIN([オーダー日])}=[オーダー日]として、顧客ごとの新規（初オーダー）分だけ取り出す
⇒「新規/既存」を行にいれ、新規だけみたいため、フィルターする
・地域（修正）ごとの累計をみるため、「地域(修正)」をマークの色にいれる
・顧客数が最後に1位の地域：WINDOW_MAX(SUM({FIXED[地域(修正)]:COUNTED([顧客 Id])}))=SUM({FIXED[地域(修正)]:COUNTD([顧客 Id])})※地域ごとの顧客数がマックスのときに顧客数を返す
・「顧客数が最後に1位の地域」をマークの色に入れても、表計算で、全ての地域が最後はMAXとなるので、全て真となるため、表計算の編集で、特定のディメンションとして、「月(オーダー日）」「新規/既存」「地域(修正)」にチェックを入れる
Q11: スーパーストアでは利益確保の観点から、サブカテゴリごとに平均割引率が基準値15%以上にならないように指導されています。しかしながらこの4年を通して平均割引率を算出したとき、いくつかのサブカテゴリが15%以上になってしまっているようです。割引率がオーバーした原因として、基準値以上割引をしているメーカーはいくつあるでしょうか？たくさんのメーカーが基準値以上割引しているために起こっているのか、どこか1つのメーカー大きく割引しているために全体的に割引率が上がってしまっているのかを一目で確認できるようにしてください。なお、サブカテゴリごとの平均割引率で見たときには15%以上ではないけれども、平均割引率15%以上のメーカーかが同等程度存在しているサブカテゴリがあれば、そのサブカテゴリと対処すべきメーカーを教えてください。
動画時間：2:06:35ｰ2:22:00
・メーカー情報は、別データソースとなり、ブレンディングしてもLOD計算では使えないため、①データソースで製品Idで結合するか、②製品Idとメーカーのデータを行にもってきて、メーカーで「プライマリーデータの作成」をする
・平均割引率が基準値を超えているメーカー： IIF({FIXED [サブカテゴリ],[メーカー]:AVG([割引率])}>=0.15,[メーカー],NULL)
※メーカーによってサブカテゴリが複数あるため、サブカテゴリもFIXEDする
・行列に平均(割引率)とサブカテゴリを入れる
・列にINDEX()を入れる
⇒INDEX()のマークに「平均割引率が基準値を超えているメーカー」を詳細にいれ、サブカテゴリごとの各メーカーを確認する
・二重軸にする
⇒INDEXを〇にするが、グラフが重なってみずらいため、ｰINDEX()として、メーカーINDEXを平均(割引率)のグラフと逆（ー）の方向に表示させる
・軸の同期をする
⇒値が平均割引率に比べて、INDEXが大きいため、-INDEX()*0.005として、グラフのバランスを整える。
・メーカーのｰINDEX()*0.005について、表計算で表下の計算となっているため、表計算を「割引率が基準値を超えているメーカー」ごとにする
⇒NULLの値もでているため、-INDEX(0)*0.005のマークの色に、「割引率が基準値を超えているメーカー」をもってくると、メーカーごとに色分けされる ⇒NULLの色だけ非表示にする
・マークの色に、AVG([割引率])>=0.15とする
この記事が気に入ったらサポートをしてみませんか？