受賞記念！「確率モデルで需要予測」「統計モデルで効果検証」Excel配布
このnoteは「データから確かな意思決定を行えるようにしたい」実務者の方。または、そうした方を育成したい経営者や管理職の方向けに執筆したものです。
自己紹介
（株）秤 代表の小川と申します。セールスプロモーション業界で4年、電通グループなどの広告会社の営業、プランナーとして10年強。データ分析を軸にしたコンサルティング支援で4年強。マーケティング戦略から戦術まで幅広く関わってきました。2018年11月には「Excelでできるデータドリブン・マーケティング」という書籍も出版しました。
50万部を超える販売実績を誇る「統計が最強の学問である」シリーズ著書の西内啓氏からもご推薦頂くことができました。
「TVCMやインターネット広告などのマーケティング施策が、それぞれ売上をどれだけ増やしているか？」効果を定量化し、予算配分の最適化試算まで行うマーケティング・ミックス・モデリング（MMM)という分析を学べる書籍です。業務委託でパナソニック（株）D-Locator’s HUBのアドバイザリーメンバー、パナソニックの新規事業から生まれたチョコレートドリンクマシンを販売するミツバチプロダクツ（株）のデジタルマーケティングマネージャーなど、10程度の役割で活動している「複業マーケター」です。
D-Locator’s HUBは、データを活用したマーケティングや商機の提案をパナソニック社内で横断的に行っているメンバーのチーム名です。Digitalizationへの道を拓くLocators（水先案内人）という意味が込められています。
2020年11月13日に、研修プラットフォームのストアカが主催するストアカアワード2020の新人賞をいただくことができました。20回の開催で400人に参加頂きました。
本noteではストアカで開催している講義を紹介します。Excelの演習データの一部を配布して無料で講義を体験できる様にしました。マーケティングの名著「確率思考の戦略論」で紹介されていた需要予測の分析を学ぶことができる「確率モデルで需要予測」と、
TVCMやWEB広告などの施策が売り上げに対してどれだけ貢献しているかを定量化し、予算配分の最適化を行うマーケティング・ミックス・モデリング（MMM）を学ぶことができる「統計モデルで効果検証」を紹介します。
確率モデルで需要予測
マーケティングの名著「確率思考の戦略論」で紹介された需要予測のガンマ・ポアソン・リーセンシー・モデルなどの分析を
「実際にどのようにして活用できるか？」
を学ぶことができます。著者の森岡毅氏は数学を用いた独自の確率統計ノウハウなどを駆使し、低迷していたユニバーサル・スタジオ・ジャパンをV字再建した日本を代表するマーケターです。もう1名の今西聖貴氏は市場構造の解析および需要予測モデル開発運用における世界の第一人者です。
同書で語られていたエピソードに、USJが10周年となる2011年に前年7万人のハロウィーンの集客を倍の14万人以上にできると需要を予測し、そこに注力する戦略を打ち出したことがあります。その意思決定に使われた分析法がガンマ・ポアソン・リーセンシー・モデルです。
森岡氏は注力すべきターゲット消費者（WHO）を独身女性に定め、彼女たちがハロウィーンに本当は何を期待しているか？という本質的なインサイト（WHAT）を捉え、そこに的を定めました。こうした戦略設計から導いた打ち手（HOW）のハロウィーン・ホラーナイトによって、走攻守ならぬWHAT、WHO、HOWが合致し、予測をはるかに上回る40万人の集客を達成しました。前年比で33万人の増加です。この企画では主に地元ニーズにフォーカスしたそうです。地元の方の来場と購入の一人あたり単価が1.5万円としておよそ50憶円の経済効果です。多くのマーケターはこのケーススタディの施策（HOW)に着目したのではないかと思いますが、もっともすごいことは、10月のハロウィンニーズに注力するWHATを確率モデルから導いたことです。10月は同パークの最大の需要期でした。通常であれば、感覚的に閑散期の1～2月や5～6月のほうが伸びしろと捉えるのではないでしょうか？
競合のTDLの来場者を予測する方法のひとつとしても使用されていました。「最後にいつ◯◯したか？」というアンケート回答（リーセンシーデータ）を用いた分析で、消費者の需要を構造化的に把握することが出来ます。書籍で紹介されていた数式を実装したExcelファイルを触ってみましょう。
ガンマ・ポアソン・リーセンシー・モデルの数式をExcelに実装
以下の表は書籍の279ページに掲載されていたものを引用しました。
制汗剤の購入において、2週間以内、2週間から1ヶ月など、最後にいつ買ったのか？というデータの実績値（実査）と予測値です。実績値は消費者アンケートなどから取得できますので、その回答結果から、消費者の購買などのアクションの分布をつかさどるNBDモデルの重要な係数2つ（MとK）をExcelのソルバーで計算します。得られたモデルから需要を構造的に把握することができます。
【演習体験データ①】Bookを開いてみてください。
B31セルのMと、B33セルのKが、市場構造を把握するための重要な係数（パラメーター）です。この値をM＝1.37552／K＝4.061（さきほどの制汗剤の例）に変更してみてください。
1年間の購買回数が0回、1回、2回の方がそれぞれ何％なのか？という市場浸透率Pr（オレンジ色のセル）と、最後に購入したのが、2週間以内、2週間から1ヶ月など、それぞれの期間において全体の何％なのか？という期間別浸透率Pn（黄色のセル）が自動計算されます。
黄色のセルの期間別浸透率Pnの予測値は書籍で紹介されていたP279 の予測値と一致しているのを確認してみてください。
消費者アンケートによって、最後にいつ買ったか？行ったか？食べたか？などのデータ（期間別浸透率Pn）を得て、それをもとに予測値と実績値の予測誤差を最小化するMとKを求め、市場浸透率Prから市場構造を把握することで、競合分析や需要予測などに活用できます。
マーケティング実務で実際にどう使うか？理解しやくするために、Twitter広告を使って自主調査した内容を教材にしました。下記のnoteでは2020年のGWの1週間の需要を把握したマクドナルドとケンタッキーフライドチキンの分析と、7月の1か月の需要を把握したレッドブルとモンスターエナジーの分析を紹介しています。
両方とも講義時に調査結果の集計データを演習データとして提供します。実際の調査を教材にして学ぶことができます。
統計モデルで効果検証
時系列データ解析によって、TVCMやWEB広告が売り上げをいくら増やしていたかを定量化するMMM（マーケティング・ミックス・モデリング）をExcelで実装する方法を学びながら、正しい効果検証に必要な因果推論の基礎知識を知ることができます。
「何に注力し、何を捨てるべきか？」
これをデータから導く、戦略的な意思決定を行うスキルを得ることができます。拙書「Excelでできるデータドリブン・マーケティング」の演習をみっちり行えば分析自体はできるのですが、統計や多変量解析、億単位の予算規模のマーケティング施策の実行や効果検証などの実務経験がないとハードルが高いので、わかりやすく学べるようにした講義です。
「MMMとは？」マンガ風に解説します。
MMM分析ツールで分析を体験
これより提供する演習ファイルは、私がある時期に1ユーザーの年間ライセンス料が1,000万円近い高価なMMM分析ソフトを使っていた経験を活かして作成したものです。アルコール飲料の売上の時系列データを使います。Excelの分析ツールの回帰分析は操作に時間がかかりますし、多重共線性というエラーを把握するVIF統計量という値を計算することが出来ません。それらの課題をクリアし、作業を効率化をするためにマクロを組みました。【MMM_modeling】Bookで予測モデルを探索し、最終の分析モデルのデータを【MMM_simulation】Bookに転記して予算配分を試算する方法を体系化しました。
【演習体験データ②】Bookを開いてみてください。
■「元データ＆折れ線グラフ」Sheet■
アルコール飲料、TVCM、紙媒体、OOH（アウト・オブ・ホームメディア）、WEB広告の5つの変数と、時系列グラフが記載されています。
118週の時系列データです。それぞれの変数の単位は売上は本数、TVCMはGRP（グロス・レーティング・ポイント）、紙媒体とOOH（アウト・オブホームメディア※交通や屋外広告など）は出稿金額（円）でWEB広告はクリック数です。このデータを元に、売上に対して、4つの施策がどれだけ貢献しているか？数理モデルで効果を定量化していきます。次に、「ソルバー回帰」Sheetを開き、赤く囲んだセルにそれぞれTVCM・紙媒体・OOH・WEB広告と入力してみてください。
HLOOKUPという関数で「ローデータ」Sheetの5行目（データラベル）と合致する変数を参照するようになっています。候補となる変数を組み替える作業をラクにするために関数を組んでいます。次に、「LINEST回帰」というボタンを1から4まで順番に押してみてください。LINESTは回帰分析を行うための関数です。
LINEST回帰1：目的変数（売上）に対して説明変数1つTVCM
LINEST回帰2：目的変数（売上）に対して説明変数2つ（TVCM,紙媒体）
LINEST回帰3：目的変数（売上）に対して説明変数3つ（TVCM,紙媒体,OOH）
LINEST回帰4：目的変数（売上）に対して説明変数3つ（TVCM,紙媒体,OOH,WEB広告）
「ソルバー回帰」Sheetの右上には折れ線グラフが自動生成されます。青い線は目的変数（売上）の実績値で、赤い線は予測値です。説明変数を4つ使ったLINEST回帰4のほうが実績値と予測値の乖離が小さくなっています。
次に、Excelの最適化計算ツールのソルバーを使って、TVCMなど各施策の効果が翌週に〇％ずつ持続する仮定を考慮した「残存効果」や、投下量が増えれば増えるほど、売上が増える効果が逓減する「非線形な影響」を考慮するための値（パラメーター）を探索していきます。予測精度を上げながら効果の推定も確かなものにしていきます。ソルバーを使うためには、Excelオプションのアドインの「設定」をクリックします。
ソルバーアドインにチェックを入れます。よろしければ「分析ツール」「分析ツール－VBA」もオンにしてみてください。
統計モデルで効果検証では、これらの機能を使います。
「OK」を押して、Excel操作画面に戻ったら「データ」タブをクリックし、「ソルバー」をクリックします。
あらかじめソルバーの各項目の設定がされています（各項目の説明は割愛します）。
プロモーション4変数のうち、「残存効果〇％を加味するか？どれくらい非線形な効果を加味するか？その基準値となるパラメータ△」の〇と△を可変しながら最適な値を探索します。「解決」というボタンを押してみましょう。
回帰分析のアルゴリズムの基準となる「予測と実績の差分の値を2乗した足し合わせた値」を最小化する計算を行っているため、その値が小さく変化していきます。最適化計算が終わったらOKを押して、再びLINEST回帰4を押し直し、VIFも押してみてください。
（※5）「VIF」ボタンは、多重共線性という致命的なエラーを把握する為の値（VIF統計量）を計算します。絶対値で10未満が望ましいとされています。
青枠の補正R2はExcel独自の用語で、正確には自由度調整済み決定係数と言います。予測式によって、目的変数の変動をどの程度説明できているか？分析モデルの予測精度の目安となる指標です。赤枠のうち、係数は回帰分析によって求めた係数です。残存効果（〇）と累乗（△）という値が、それぞれ「残存効果〇％を加味するか？」とどれくらい非線形な効果を加味するか？その基準となるパラメータ△」です。たとえばTVCMの残存効果は49.2%と計算されています。
MMMの分析結果で分析者が最も興味があるのは「効果数」です。TVCMは55,564,001となっています。これは推定した係数と、残存効果と非線形な影響を加味して変形した値を掛け合わせ、それを合計することで導いた「TVCMによってアルコール飲料の売上数が何本増えたか？」を推計した値です。なお、残存効果と非線形な影響を加味するためにソルバーを実行する前にLINEST回帰4を行った分析結果は下記でした。
自由度調整済み決定係数（補正R2)は0.520640から0.593553まで上がっていたことがわかります。また、TVCMの効果数は12,942,785で、ソルバー実行後にLINEST回帰4を行った場合の効果数55,564,001の23％強しかありません。なお、両方の分析結果ともOOHの係数はマイナスになっており、効果数もマイナスです。
実際の分析では、折れ線グラフを見て予測値と実績値が大きく乖離している箇所から、売上に影響していると考えられる新たな説明変数を追加したり、月次の季節性を考慮するための変数を加えたり、OOHとWEB広告は1週間遅れて効果があると仮定するタイムラグを加味したりといったことを繰り返し、予測精度の高いモデルを探索していきます。そうした手順を行なっていった結果、最良と思われた分析モデルが下記です。
文字が細かくて見えないと思いますので【演習体験データ③】Bookの「ソルバー回帰」Sheetを開いて確認してみてください。
青枠の自由度調整済み決定係数は0.863678まで上がっていて予測値と実績値の乖離もさらに小さくなっています。
OOHの係数もプラスになり、効果数は6,521,468になっています。赤枠のTVCMの効果数は69,034,678です。残存効果などを加味せずにLINEST4回帰で分析した時の効果数は12,942,785で約18.7%と、乖離が大きくなっています。筆者が開発した【MMM_modeling】Bookを用いて残存効果と非線形な影響を加味することで予測精度を上げ、じわじわ効いているTVCMなどの施策の効果を過小に推定するリスクを減らすことができます。
次に、シミュレーションはどんな風に行うか？拙書の演習手順を抜粋して紹介します。
シミュレーションについて
最終的に使用した分析モデルのデータを【MMM_modeling】Bookから【MMM_simulation】Bookに貼り付けてグラフを生成します。
※以降は書籍で紹介する演習手順を参照します。図の番号から引用箇所を特定できます。
横軸が週あたりの投下金額、縦軸が各施策がアルコール飲料の売上を増やす本数です。傾きが最も急なWEB広告が最も効率よく売上本数を増やしていました。TVCMは横軸の投下量が増えると縦軸の効果数の増加量が逓減していく非線形な影響が考慮されています。このモデルを基準とし、各施策の投下量の実績を100パーセントとした際にそれぞれ何パーセントに変更し、更に全体の予算は実績予算を超えないという制約をかけ、売上数を最大化できるか？を目的関数として【MMM_simulation】Bookでソルバーの最適化計算を行うと下記の結果になります。
WEB広告に現状の7倍配分しましょうという極端な結果です。私が過去に使っていたMMM分析ソフトはこうした杓子定規な試算結果となり、実戦に使えませんでした。そこで考えたのは施策の投下量ごとの単価変動を考慮してチューニングするシミュレーション法です。例えば、WEB広告はリスティングなど入札制で機械的に値付けされる運用型広告が多く、一般的に投下量を増やすとCPC（クリックパーコスト）など仕入れ単価が上がる傾向にあります。反面、TVCMなどは人的な交渉による為、一般的に投下量を増やすことで単価を下げやすくなります。効率だけでなく、違う理由から、ある施策については実績に対して70パーセント以上は投下する。といった制約をかけることもときに必要です。拙書の演習では、そうした現実的な前提を考慮した補正や制約条件を加えながら、最適な予算配分を算出していきます。最終的な試算結果が下記です。
納得感のある配分案になったのではないでしょうか？このシミュレーションでは、実際の予算と同じ予算で約10％以上売上本数が増える見込みとなっています。施策ごとに予算が増えるとどれだけ単価が上がるのか？または、下がるのか？現実的な前提を任意に設定してシミュレーションできるようにし、実戦で使えるものにしました。
【告知】データから確かな意思決定を行える状態を目指すために
※以下、告知内容は適宜更新します。
ストアカ講義のご紹介
いかがだったでしょうか？時系列データ解析や確率分布などの数理モデルを駆使し、意思決定を確かなものにする方法を学びませんか？その第1歩として「確率モデルで需要予測」「統計モデルで効果検証」のセット受講をおすすめしています。両講義とも質疑応答含む3.5時間7,800円のZoom研修です。前者のほうが難易度は低いです。グループワークで参加者同士理解を深めることができる構成にしました。後者は専門知識の説明が多く、より集中して聞いて頂く必要があるため、参加者同士の交流が無いZoomウェビナー形式で行っています。
今後、AutoML（オートマシンラーニング）など、高度な分析を扱いやすくする手法が増え、マーケティング分析や意思決定の自動化がさらに加速するはずです。「統計モデルで効果検証」の講義で時系列データ解析による推定または予測とはいかなるものか？そうした感覚を身に着けておくことは、これから分析や意思決定の自動化を進める際の必須知識になるはずです。「確率モデルで需要予測」と比べると若干難易度は上がりますが、ぜひチャレンジいただきたい内容です。両講義とも講義終了から1週間Zoom内でご覧頂ける復習用動画も用意しています。
さらに、本気でデータドリブンな組織づくりを目指す企業には、エクセル経営で成功したワークマンの様な組織を目指す並走型支援を行っています。以下noteもお読み頂けますと幸いです。
インタラクティブ動画「複業マーケター『秤貴史』」
2020年は特別な年になってしまいましたが、2021年、前進するのみです。私は2019年12月に会社を登記してしばらくはサラリーマンしながらの経営をしていましたが夏に会社を辞めて独立しました。セルフブランディングも含めてSNS（主にnote＋Twitter）を活用して発信し、多くの企業を支援する機会をいただきました。会社の登記1周年を機に、「マーケティング」「セルフブランディング」「働き方」をテーマに合計6万文字のnoteを執筆しました。12個のnoteを360度動画で再現されたカフェの中から探す技術や、私と怪しいキャラの映像がリアルタイムにスイッチングする技術、人物のトリミングはAIツールを使用、アクションカメラInsta360のAI編集（自撮り棒を消す、分身の術）など、最新の映像テクノロジーを盛り込んだインタラクティブ動画をエバンジェリスト兼データアナリストの役割を担う、インタラクティブ動画プラットフォームMIL社の協力のもと作りました。登場するキャラクターが渋谷のカフェでテスト販売中のチョコレートドリンクマシンで作る「チョコティー」を飲みに行くまでのシーンをVJ気分で切り替えて楽しんで頂けるものです。お時間のある際にご覧になっていただけますと幸いです。
おまけですが、こんな企画をなぜやっているか？「秤」のグラサンをかけていますが、後半は真面目に語っているYouTuber風動画も公開しています。
追加情報（2023年12月18日更新）
クッキー規制で目減りする効果計測の課題を解決法をnoteにしました。無料で使えるMETA社の高機能なMMM（マーケティング・ミックス・モデリング）ツール「Robyn」を徹底解説する2時間強のYouTube講義を公開しました。