TVCMが昨対比を何％押し上げたか？をGoogle Spread Sheet やEXCELで（敢えて乱暴に）分析する

「テレビCMやったけどさ、どんだけ効果があったのよ？」と質問されたときにどう回答するのが適切だろうか。

データ分析ができる人にとってはデータさえ揃えば難しい話ではない。投下したTVCMのGRPを説明変数として売上なり申込件数を目的変数とした予測モデルを作れば良い。しかし、データ分析の素養がない人に予測モデルの式本体を見せたところでチンプンカンプンだろうし、報告する人が普通の人だった場合、RやPythonでモデリングしろ、というわけにもいかない。

例えば、緊急事態宣言下の4月にオンライン教材を売っていた企業が、TVCMを放映してみたエリアと放映してみなかったエリアでA/Bテストしていたとしよう。¹ その場合、どのようなデータ分析をすればよいか考えてみたい

モデルの検討

報告すべき情報、わかりたい情報を決める

売上を予測するモデルを作ることも、TVCM効果の有無を検定する方法もあるだろう。しかし管理職以上は統計学もデータ分析の素養はないし、効果は”量的に”把握をしたいだろう。そして今回のケースは、「それって緊急事態宣言の影響もあるんじゃない？」とか「そもそも今年がオンライン学習元年でよそも伸びてない？」と指摘されるだろうから、それとも区別したい。

そこで、 昨対比で申込件数が何％あがったか を求めることにした。

取得できるデータを確認する

今回入手できる情報は、"各県で緊急事態宣言があったかどうか"と"TVCMを放映したかどうか" といういずれもゼロと1の数値。そして、本年度の申込件数と昨年度の前年同期の申込件数だ。²

手法を決める

今回はEXCELやGoogle Spread Sheetで可能な作業として、「線形の重回帰分析」とした。

• 目的変数：申込件数の昨対比（本年度の申込件数／昨年度の申込件数＊100：→％にした）
• 説明変数：「TVCM放映の有無」と「緊急事態宣言の有無」
• 切片：あり

これはハッキリ言って嘘である。

「y = a1x1 + a2x2 +b」といったお互いに独立の変数が線形で関係している、という式を検討しているわけだが、昨対比などというものは、

• TVCMも緊急事態宣言もあった場合：a1 * a2倍（％）
• TVCMを放映し緊急事態宣言がなかった場合：a1倍（％）
• TVCM放映がなく緊急事態宣言はあった場合：a2倍（％）
• TVCM放映がなく緊急事態宣言もなかった場合：1倍（100％）

というわけだから、2つの変数が「足し算」になっているはずがない。

とはいえ、掛け算を足し算に戻すには、対数を取らなければならないし、対数変換についてこれる文系役員などそうそう居ない。³ かといって再び「昨対比」を避けてしまうと、

「本年度の申込件数／一定人口辺り＝昨年度の申込件数／一定人口辺り + TVCMが加えてくれる件数 + 緊急事態宣言が加えてくれる件数 + その他の要因」

という効果の量的なイメージが判断し辛いものが出来上がる⁴

だが、この手法であれば、TVCMと緊急事態宣言の効果は一応別々で出てくるし、EXCELやGoogle Spread Sheetで結果が出てくるのだから、よくわからない人でも見様見真似で算出することができる。

分析の実際

Google Spread Sheetの作業

• LINEST(目的変数の列,説明変数の列群,定数項の有無,出力結果の詳細の有無)

をシートに入力する

今回の場合は、

• 目的変数：昨対比
• 説明変数の列群：TVCM放映の有無　と　緊急事態宣言の有無

となる。

結果の見方

このような出力が出てくる。重要なところに色付けをした。

99.3 は切片に当たる。昨対比を％表記にしたので切片は本来100であるべきだが少しずれている。

31.3は一つめの変数「TVCM放映の有無」に掛かる係数だ。「TVCM放映は、約31％押し上げる効果があった」と読みとける

11.7は二つ目の変数「緊急事態宣言の有無」に掛かる係数で、「緊急事態宣言があったエリアでは、約12％押し上げることになった」と読める。

もう1箇所網掛けした0.994は、これらの変数2つによって目的変数（の分散）を約99％は説明できている、と解釈する。

なお、Google Spread Sheet のLINEST関数の出力はかなり直感に反している。1つ目の変数の係数→2つ目の変数の係数と左から並んで欲しいものだが、なぜか右から、切片→1つ目の変数→2つ目の変数の係数と並ぶ。わりと面倒だ。

その他の出力の値に興味がある人は公式ドキュメントを参照してほしい

support.google.com

EXCELの場合

EXCELにはもう少しグラフィカルインターフェイスな機能がある。EXCELの場合は丁寧にまとめている人が多いので割愛する。

sigma-eye.com

今回のデータを作った方法

EXCELで乱数を発生させた。

• TVCM放映の有無と緊急事態宣言の有無：RANDBETWEEN(0,1)
  • ゼロと1の乱数を発生させてくれる。
• 2019年度の申込件数：RANDBETWEEN(100,150)
  • 使用する関数は同じ。数値の上下を変えた

効果の数値設定と目的変数の作成

TVCMの効果を1.3倍、緊急事態宣言の効果を1.1倍として、

• TVCM効果：IF(TVCM放映の有無=1,1.3,1)
  • 放映があった場合は1.3、なければ1を掛け算したいため
• 緊急事態宣言効果：IF(緊急事態宣言の有無=1,1.1,1)
  • 放映があった場合は1.1、なければ1を掛け算したいため

という新たな変数を作り

• 2020年度の申込件数：ROUND(2019年度申込件数TVCM放映効果緊急事態宣言効果+RANDBETWEEN(-2,2),0)
  • 昨年度の申込件数にTVCM放映効果と緊急事態宣言効果を乗算、更に2件程度は上下にブレるように乱数を加えた上で、最後に小数点以下四捨五入している

を求めている。こんな感じだ。因みにリロードしてしまったので上で貼り付けた数値と乱数がまた更新されている。

先程はかなり乱暴に線形の重回帰分析モデルにしてしまったのだが、31％や12％は上振れしているものの、そこまで真の値からは離れていない。統計学者やデータサイエンティストからすれば余りにも気持ち悪い話かもしれないが、"偉い人に普通の人が効果を説明する" 行為を日常的に行うことがデータドリブン経営なのだということとなると、この程度のいい加減さを我慢しないといつまでも組織として根付くことはないのではないだろうか。この先、データ分析の教養がある学生が社会人になってくれるようになれば話はべつかもしれないが、その間、今の20代～60代の文系社会人が何もしなくて良いということにもならないだろう。⁵

---

1. 極めて当たり前だが、放映しなかったエリアが存在しなければ効果検証できない。そして多くの日本企業のマーケティング・プロモーションは、事前の効果検証もせずに一発で一か八かの勝負をするので、"TVCMを放映しないエリア"なんてものが存在しないことの方が寧ろ多い。この問題の根本原因はデータ分析リテラシーの欠如ではなく、「スケジュールに余裕がなくなるレベルの組織の意思決定の遅さ」「A/Bテストによって発生するエリア別の不公平を許容できないビジネスモデルや取引先との力関係」などの方だ。↩

2. 簡単のため各エリアの人口は同じだとしている。実際には人口○○人辺りや、ターゲット○○人辺りなどにすればよい。↩

3. 現実には多項ロジスティック回帰分析をしたいことも多いし、対数変換を避けてでも嘘をつくべきかは非常に悩ましい。↩

4. 本当を言うとStanを使ってシミュレーションしてしまえば、効果量を設定して推定値を出せば良い。データ分析がわかる自分が毎回計算してあげれば良い、ということであればこれで構わないし、自分が効果の程を把握したいだけならそれで充分だ。だが組織としてそれで良いのか？というとそうではないだろう。↩

5. “文系に統計学の教養がない"という前提も実は嘘だ。心理学・社会学では日常的に使うし、最近は経済学部でも因果推論という名称で社会学的アプローチを参照し始めている。更には文学部でも文献をデータ分析して文書の著者を推定するなど統計学をフル活用する分野もある。とはいえ、理系でデータ分析を使わないのは建築学部のデザイン系だとかかなり限られていると思うので、総じて文系がビジネスパーソンとしての教養不足で苦しむことになると思う。↩