エクセルを使って、広告費から集客人数を予測する方法をご紹介します。

使う分析方法は「回帰分析」です。

回帰分析は、データ同士の関係性を数式にして、その数式から予測値を求める手法です。

今回は、回帰分析の中でも、2つの数値から関係性を割り出す、「単回帰分析」で行います。

 

予測値を求める数式

単回帰分析は次の回帰式で求められます。

単回帰分析の回帰式:y=ax+b y目的変数 x説明変数

今回の回帰分析では、広告費(説明変数)を回帰式のxに入力すれば、計算で予測値のy(集客数)が求められます。

といっても難しい言葉が出てきてよくわからないと思いますが、エクセルを使えば簡単にxやyを算出することができるのです。

ではさっそく予想集客数を求めていきます。

 

 

動画解説

動画解説はこちらをご覧ください。テキスト解説はこのまま下へ読み進んでください。

 

 

広告費20万投入した場合の集客数は?

次のような広告費と集客数のデータがあります。予測したいのは、広告費20万を投入した場合の集客数です。

 

数値をグラフ化し、散布図を作ります。散布図で、2つの数値にどうのような相関関係があるかを目で確認します。

散布図は縦軸に集客数、横軸に広告費にします。

 

 

広告費と集客数の関係性を調べる

最初に広告費と集客数の相関関係を調べます。

相関係数を出す関数は「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.0002x-0.1792

が導き出された数値です。

この数式に広告費の20万を算入します。

回帰式:y=0.0002×200000-0.172

 

セル「F12」に、「=0.0002*E12-0.1792」と数式を入力します。

 

予想集客数が「39.8人」と算出されました。

 

 

データの信頼性を見る

広告費と集客数の散布図に線形の近似曲線を引き、回帰式から予測値を求めました。

回帰式と同時に表示した「R2乗」は「0.618」になり、回帰式はデータの61%を説明しています。

回帰式は「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円までの範囲しか入っていません。

 

この範囲に入ってない数値で予測することを「外挿」といい、予測の精度が不安定になってしまうのです。

単回帰分析で使える予測は、範囲があることを覚えておいてください。

 

 

まとめ

予測は予測でしかありませんが、何の指標もなく突っ込んでも勝てる確率はあがりません。

広告は投資です。

ギャンブルではありません。

えいやあで勝てるのは、ビギナーズラックのうちだけです。

最初はたまたま集客できても、続けていけば負けの確率が上がります。

データ分析で勝てる確率を上げましょう。

この記事が気に入ったら
いいね ! しよう

【無料】社長のための社会保険料削減マニュアルをメルマガ登録でプレゼント

【無料】社長の手元キャッシュを最大化するメルマガ。無料レポート「社長の手元にお金が残らない9つの理由」プレゼント

集客と販促に強くなるメディアはこちらから/満席プロジェクト