エクセルを使って、広告費から集客人数を予測する方法をご紹介します。
使う分析方法は「回帰分析」です。
回帰分析は、データ同士の関係性を数式にして、その数式から予測値を求める手法です。
今回は、回帰分析の中でも、２つの数値から関係性を割り出す、「単回帰分析」で行います。
予測値を求める数式
単回帰分析は次の回帰式で求められます。
今回の回帰分析では、広告費（説明変数）を回帰式のxに入力すれば、計算で予測値のy(集客数)が求められます。
といっても難しい言葉が出てきてよくわからないと思いますが、エクセルを使えば簡単にxやyを算出することができるのです。
ではさっそく予想集客数を求めていきます。
動画解説
動画解説はこちらをご覧ください。テキスト解説はこのまま下へ読み進んでください。
広告費20万投入した場合の集客数は？
次のような広告費と集客数のデータがあります。予測したいのは、広告費２０万を投入した場合の集客数です。
数値をグラフ化し、散布図を作ります。散布図で、２つの数値にどうのような相関関係があるかを目で確認します。
散布図は縦軸に集客数、横軸に広告費にします。
広告費と集客数の関係性を調べる
最初に広告費と集客数の相関関係を調べます。
相関係数を出す関数は「CORREL」です。
相関係数を入れる「F3」セルに、「=CORREL(B3:B14,C3:C14)」と入れます。
広告費と集客数の相関係数が求められました。
相関係数は「0.7」以上あり、強い相関関係があることがわかります。
散布図にして、関係性を目で確認します。
このとき、「外れ値」がないか目で確認するようにしてください。
外れ値とは、平均から大きく外れた値（たまたま出た可能性が高い数値）で、これがあると数式に悪影響を及ぼします。
外れ値があれば取り除くようにします。
散布図にする範囲を選び（B3:C14）、「挿入」→「グラフ」で「散布図」を選びます。
グラフが挿入されました。
近似曲線を追加する
次にグラフに「近似曲線」を追加します。
散布図上にプロットされているデータを右クリックし「近似曲線の追加」をクリックします。
「近似曲線のオプション」から「線形近似」をクリック。
続いて「グラフに数式を表示する」と「グラフにR-2乗値を表示する」にチェックを入れ、作業ウィンドウを閉じます。
近似曲線と回帰式が挿入されました。
この数式をもとに、集客数の予測値を算出します。
平均値を出す
次に、広告費と集客数の平均値を算出します。
平均値を出す関数は「AVERAGE」です。数式は「=AVERAGE(B3:B14)」です。「(B3:B14)」は範囲を示す数式です。
セル「E7」に数式を入力したら、ドラッグして「F7」にも入力します。
広告費と集客数の平均値が算出されました。
回帰曲線上に平均値が乗っているか確認します。
セル「E7」「F7」をコピーして、グラフをクリックし、「ホーム」→「貼り付け」から「形式を選択して貼り付け」を選びます。
「新しい系列」と「列」をクリックし、「先頭列を項目列として使用する」にチェックを入れたら、OKボタンを押します。
平均値が回帰曲線上にあることを確認できました。
予想集客数を出す
広告費に対する集客数の予測値を出します。
が導き出された数値です。
この数式に広告費の２０万を算入します。
回帰式：y=0.0002×200000-0.172
セル「F12」に、「=0.0002*E12-0.1792」と数式を入力します。
予想集客数が「39.8人」と算出されました。
データの信頼性を見る
広告費と集客数の散布図に線形の近似曲線を引き、回帰式から予測値を求めました。
回帰式と同時に表示した「R2乗」は「0.618」になり、回帰式はデータの６１％を説明しています。
回帰式は「0.5」以上で使えると判断できますので、若干精度高めの回帰式といえます。
目標集客数から広告費を求める
回帰式の数式は「y=ax+b」です。
この場合目標集客数から広告費を求めるなら、「x=(y-b)÷a」とすればよいことになります。
たとえば、集客数30人を達成するための広告費を求めるには、回帰式「30人=0.0002×広告費＋(-0.1792)」を下記の式に直します。
・(30-(-0.1792))÷0.0002=150896
となり、広告費に約15万円必要と算出できます。
予測できる範囲があります。
ただし注意点があります。
それは「回帰分析で使った数値を超えていると、予測値の信用は無くなる」ということです。
今までの流れで見ていきますと、「y=ax+b」に数値を入れていけば、予測値が算出されていくことになります。
では、100万広告費を投入した場合の集客数はどうなるでしょう？
今回使用したデータには、89800円～298000円までの範囲しか入っていません。
この範囲に入ってない数値で予測することを「外挿」といい、予測の精度が不安定になってしまうのです。
単回帰分析で使える予測は、範囲があることを覚えておいてください。
まとめ
予測は予測でしかありませんが、何の指標もなく突っ込んでも勝てる確率はあがりません。
広告は投資です。
ギャンブルではありません。
えいやあで勝てるのは、ビギナーズラックのうちだけです。
最初はたまたま集客できても、続けていけば負けの確率が上がります。
データ分析で勝てる確率を上げましょう。
コメント