初心者でもすぐできるエクセルでデータ分析

身近にある社内のデータを活用したい

と思っても、自社に分析ツールがなかったり、分析業務に適した人材がいないから無理・・・と、断念された企業は多いんじゃないでしょうか。

せっかくデータ活用をやってみようと思った企業が、ツールの習得や人材不足で断念してしまうのは残念なことです。
データ活用を本格的にするなら、データを集約するための基盤といった環境整備を含めて行うのが望ましいですが、データ分析はビジネスユーザーにとって身近なツールのエクセルですぐにはじめることが可能です。

ピボットを使ったクロス集計も立派な分析なのですが、今回はデータ分析っぽさを感じられるように、Web広告の過去の実績データをエクセルで分析して、1日の広告予算を増加した場合に何件CVが増えるかのシミュレーション作成方法をご紹介します。

分析には実績のローデータを使用

一般的にはこの決まった数値というものがあればよいのですが、業種や取扱う商材、LPの有無、コンバージョンポイントによって数値は変わってきます。
何より、自社で役立つ分析じゃないと意味がありませんので、自社の広告の配信データをご用意ください。

分析には何も手を加えていない状態の広告配信結果の生のデータ、所謂ローデータ（Raw Data）を使用します。

何を分析するかによって必要なデータは変わってきますが、1日の広告予算の適正を見るのであれば、日付、費用、CVのデータがあれば可能です。

代理店に運用を任せている場合には、代理店に「Web広告の日別データが欲しい」または「Web広告のローデータが欲しい」と依頼すれば提供してもらえるはずです。

ローデータを手に入れたとして、表形式のまま見て何かを判断するのは困難です。
そこで、誰が見ても判断しやすいようにエクセルでグラフの作成と計算を行います。

計算を行うといっても難しい設定や長い計算式を書いたりする必要はなく、エクセルにある基本機能を使って簡単にできます。

エクセルで近似曲線を作成

エクセルでローデータのファイルを開き、X軸にコスト、Y軸にコンバージョンを指定して散布図のグラフを作成します。

散布図の点をどれでもいいので右クリックして、「近似曲線の追加」を押します。すると線形近似の直線が追加されます。

この直線を右クリックして、「近似曲線の書式設定」を押します。「対数近似」選択して「グラフに数式を表示する」にチェックを入れてください。

グラフには対数近似曲線と共に「y = ax + b」の数式が表示されます。

この数式は後で1日の広告予算とCVをシミュレーションに使用します。

「対数近似」を選択した理由としては、Web広告の成果は直線的に右肩上がりにコンバージョンが増え続けるということはなく、急速に増加し、その後鈍化するという性質であることから選択しています。

グラフからはX軸が2万円以降の明らかな鈍化が見て取れ、予算を倍の4万円にしたとしてもCVが倍になる可能性が低いことが分かります。

近似曲線からシミュレーション作成

それでは、具体的に広告予算がいくらで、どれくらいのCVが期待できるかのシミュレーションを作成します。

ここで、上記グラフに表示された「y = ax + b」の数式を使用します。

「y = 4.2551ln(x) – 32.949」の数式を使って広告予算を1万円から1万円ずつ増加した場合のCV、CPAを計算したシミュレーションが以下の表になります。

各数値の計算式は

• CV：4.2551 * ln(1日の広告費) – 32.949
• CPA：1日の広告費 / CV

となります。

1日の広告費を1万円から倍の2万円にした場合のCVは9.2、CPAは2,176円です。

現在の1日の広告予算が1万円でCVを倍の1日12件獲得したいと思った場合、現在のCPAから単純に計算すると「1,602円 * 12 ＝ 19,224円」となります。
それでは、19,224円を過去のデータを元にしたシミュレーションに当てはめると
「4.2551 * ln(19,224円) – 32.949 ＝ 9」で目標に届かないということが分かります。

最後に

過去のデータを元にしたシミュレーションを作成すれば、後はさらに予算の追加を検討したり、広告自体の取捨選択を行うなど具体的なアクションを起こせます。

もし、上司から「予算を倍にしたらCVも倍にできるよね」と無茶振りされたとしても裏付けのある数値を元に話をすることができます。

代理店に任せっきりにせずに、CPAを抑えた状態で効果を最大化する予算を立てることも出来ます。

データ分析・データ活用は、実データを元に現状把握し、仮設を立て、実行、そして検証を繰り返していくことで、どんな企業でも成果を上げていくことができると思っています。

今回のシミュレーションの作成方法は、誰にでも扱いやすいエクセルでを使った簡単な分析手法です。この方法は、Web広告にしか使えないわけではありません。広告宣伝費と来店数など関連がありそうな2つのデータを使って、まずは小さな規模から自社の現状把握とシミュレーションから試してみてください。

もし、自分達では難しい、どうやればいいか分からなくて不安だとお困りの場合は、データ活用の自走化を支援していますのでお気軽にご相談ください。

アド・セイルへのお問い合わせはこちらから

この記事を書いた人