④エクセルによるデータ分析 : 実践

分析の実績

①マーケティング分析の実践
・ファネル分析（A/Bテスト）
・施策の優先度（バブルチャート、パレート分析）
・コホート分析
・相関分析
②総合演習
・データベース形式のデータを元に集計
→グラフ化まで行って分析

ファネル分析とは

①マーケティングのステップごとに切り分けて原因・施策を考える
・広告を打った → 利益は出なかった、だけでは改善策がわからない
・100万円を使って広告を打った
→①55万人が広告を見た（＝広告の表示回数）
→②8万人が広告をクリックして、Webサイトを訪問した（広告クリック数）
→③５千人が購入した（1つ100円）
・売上は５千人×100円＝50万円、利益は、50-100=マイナス50万円
・黒字にするためには、①~③それぞれ改善できないか議論する
・例 : ②クリック数を増やすために、広告をデザイン性をあげよう

ファネル分析の結果を比較して検証する

①比較は重要
・何かと比較することで、初めてそれが「いいか悪いか」が分かる
②期間比較
・過去と比較して、よくなっているか確認する
③A/Bテスト
・2つの広告デザインをランダムで配信する
・どちらの広告の方が購入者が多いか比較して、優良な広告を見つける

A/Bテスト

①2つの広告文のテスト結果
・広告A歯、広告はクリックされやすいが、その後の購入までは繋がらない
→「絶対ダイエットできる」と誇張した表現は、むしろ逆効果
②今回使う関数は・・・
・SUMIF関数
・広告AまたはBのデータを集計したい
・例えば、1月だけの広告Aまたは広告Bのデータを集計したいときは、SUMIF関数を使う

バブルチャート

①商品別の利益率と売上成長率
・課題は「会社全体の利益率が上げて、財務の健全性を高めたい」
・どの商品の利益率向上を目指すべきか・・・商品Bまたは商品D

①売上の大きさ = バブルに大きさ
・明らかにBの方が売上が大きいので、優先度は高い
・売り上げの小さい商品Dの利益率を改善しても、あまり全体に影響はない
②バブルチャートは3つの評価軸を一度に見て、優先度を判断できる
・利益率、売上成長率、売上の規模

ヒートマップ

①色は重要
・直観で「ここの数字はいいな」がわかる
・条件付き書式

・緑数値は「11.07%以上の数値」
・赤数値は「11.07%以下の数値」

パレート分析

店舗の客数を世代別に分解して施策を考える
①パレート分析
・全体の数値の大部分は、一部の要素が生み出している
・売上の80％は、20％の優良顧客が生まれている
　・80 : 20の法則
②なぜ重要か
・KPI = マーケティング大賞の優先順位を考える
・50代~70代の顧客を優先的に営業して、40代以下は気にしない

パレート図の作成ステップ

・年齢データを、世代別に分けるのがポイント
・50代以上（> = 50）、60代以上（> = 60）と累計値を計算する

コホート分析

①とある商品販売サイトの顧客の継続利用率を分析する
・1月入会者に比べて、10月入会者の継続率が下がっている
→継続利用を促進するアイデアが必要（定期的にメール配信するなど）

コホート分析②

①計算ステップ
・縦に入会つき、横に利用者数を集計する（SUMIFS関数）
・利用1ヶ月を100％にした継続利用率を計算する
・利用率ゼロ（利用実績データがない）ところを削除（IF関数）

①相関分析とは

①それぞれのマーケティング支所が連動しているか？は大事
・販売数が増える → 売上も増える
→ イメージしやすい
・テレビCMを打つ → 商品ブランドが上がる → 売上も増える
→ 本当？
・過去のデータの傾向から判断する「相関分析」で判断
②相関分析がわかると
・自信を持って「マーケティング投資すれば売上が増える」と判断できる
③広告宣伝費と売上の関係
・広告宣伝費が増えるほど売上が上がる傾向

②相関分析 R2

①近似曲線の作り方
・散布図
・近似曲線
・線形近似
・R2

③相関分析 正/負の相関

①相関の種類
・気温は上がれば、Tシャツが売れる → 正の相関
・気温が上がれば、コートが売れない → 負の相関
→どちらもR2は0.90なので注意！相関の向きもチェック

④相関分析 相関関係、因果関係

①相関
・気温とTシャツの販売数には関係がある
○ 気温が上がる → Tシャツが売れる
× Tシャツが売れる → 気温が上がる
・相関分析だけでは、どちらが原因でどちらが結果か分かりにくい
②因果関係
・原因と結果
・納得感のある仮説を考える
・それは逆じゃないか？と疑問を持つ

⑤相関分析 売上予測

①広告宣伝費と売り上げの関係
・広告宣伝費を250にすると、売上は500くらい

⑥相関分析 予測値を計算する

①近似曲線を計算式にする
・y（縦軸:売上） = 1.4 ×（横軸:広告宣伝費）+ 160
・510 = 1.4 × 250 + 160
②近似曲線
・計算式を表示する
・傾き、切片を表示
・xを計算式に代入して、yを求める
③関数でも計算できる
・傾き : SLOPE（y軸の範囲、x軸の範囲）
・切片 : INTERCEPT（y軸の範囲、x軸の範囲）

⑦相関分析 外れ値

①広告宣伝費と売上
・外れた数字を使わないと、相関が高い
・R2は0.41と高くない・・・なぜか？
・一つだけ外れている数字がある
→外して考えると数値が高くなる結果もある（外した結果 R2 = 0.9402）
→ただ外していい理由が必要！

②外れ値
・データの中には例外（外れ値）も存在する
・この数字を相関の計算から外す場合がある
③季節要因
・クリスマスで広告宣伝費をかけなくても商品の売り上げが伸びた
→売り上げを、2つのKPI（季節要因による売上と、通常の売上）に分ける

⑧相関分析 グループ分け

①広告宣伝費と売り上げ
・R2は0.62と、それほど高くない・・・なぜか？

・おそらく2つのグループに分かれているのではないか（仮説）

2つのグループを分けて相関を取ると、それぞれ高い数値になる

①営業分析 期待値

①売り上げの見込みを計算
・今年の売り上げ、どれくらいいけそう？
②期待値の計算
・受注した時に見込める売上 × 受注できそうな確率

②営業分析 価格

①価格の決め方
・アンケート結果を元に、最も利益を最大化できる価格は？
・材料費は1個500円とする
②想定される購入者の計算
・価格を2000円の場合、購入者は20人＋5人＝25人
・5000円までなら購入するという顧客は、2000円なら当然購入する