【Excelで出来る】対数近似曲線で広告予算のシミュレーションを行う方法

広告配信を行っていると予算拡張に対してどこまで成果が伴うのか、悩んだことのある方は多いと思います。具体的には下記のような悩みです。

• 現在活用している広告施策はどこまでなら伸ばしても広告成果が見合うか
• いくら以上の配信からは別施策にアロケーションしたら良いのか
• 獲得効率とCV数が最も効率的に最大化できる予算はどこなのか

こういった悩みを解消しようとすると、運用型広告の専門的な知識が必要となったり、過去/未来のなかで獲得効率に影響を与えそうな変数を考慮する必要があるなど、難しい点が多くあります。
そこで本記事では広告予算が適切なのか、専門的な知識無しに誰でも簡単に分析できる方法をお伝えします。

適切な広告予算に関して何も糸口のつかめない状態から、少しでも考えるきっかけになることを目的にした、Excelのみで簡単に予算と獲得効率のシミュレーションを作成する方法です。

>>「BtoB事業特化型の広告運用支援サービス」について詳しく見る<<

分析のロジック：対数近似曲線

今回は広告配信実績に基づく対数近似曲線を用いた分析を行います。
近似曲線とは、散布図で表示された「複数のデータ」のなるべく近くを通るように引いた、直線または曲線のことで、散布図のデータ分布の傾向を明確に把握したり、データがない将来の予測を行うときに活用できる分析方法です。
近似曲線はいくつか種類がありますが、今回はその中でも対数近似曲線を用いた分析方法をご紹介します。

• 対数近似曲線の特徴
  データの変化率が急速に増減し、その後でレベルアウトする（一定になる）場合に適している。

一般的に運用型広告は下記のような経過を辿ることが多く、この傾向が対数近似曲線の特徴と一致するため、分析手法として用いられています。

1. 市場に対して投資額が小さい場合、投資額を増やすと獲得数は疑似直線的に増加する
2. 投資額が大きくなると最も獲得効率がいいターゲットへの配信が飽和し、獲得効率が徐々に下がっていく
3. 最終的に投資額の増減が獲得数の増減に影響しない段階に至る

分析のために必要な準備物

今回の分析作業に必要なものはExcelと広告データのみです。まずExcelについては下記にフォーマットを用意していますので、ダウンロードのうえご活用ください。

Exelでできる獲得効率の高い予算配分の分析
（上記のリンクからExcelファイルのフォーマットをダウンロードできます）

広告データについては下記2種類のデータを用意してください。

• 分析にあたって必要なデータ
  1. 日別の広告配信金額
  2. 1と同じ時系列での広告KPI

補足
・ 広告の粒度は広告グループ/キャンペーン/媒体単位/広告全体などは自由ですが、まとめすぎてしまうと傾向が把握しづらくなるため、媒体単位までを推奨しています
・ データ粒度は日別としていますが、十分なデータ量を確保できるのであれば週別や月別でも構いません
・ 広告KPIはimpやクリック数、CV数など様々な指標が考えられますが、本記事ではCV数として以降の説明を記載します

分析の流れ

ここから具体的な作業手順を記載いたしますが、詳細説明をお伝えする前に全体像をつかみやすいよう分析手順を先んじてご案内いたします。

<分析手順>

1. Excelシートに広告データを記載し、グラフを作成する
2. 算出された関数式をもとに、分析を行う
3. 自社の状況を考慮し、広告予算を決定する

このように大まかに3つのステップで、簡易的な分析ができます。分析結果はデータさえ揃っていれば数分で算出が可能ですので、ぜひ読了後は試してみていただけると嬉しいです。

それではこれより実際の作業手順を先程ご案内したExcelをもとに説明いたします。

1. Excelシートに広告データを記載し、グラフを作成する

「分析作成シート」のセルA4~C4列に、日付、配信金額、CV数の順に記載してください。（こちらに記載するデータは先述の通り分析したい内容を記載いただければ良いため、必ずしも参考通りの項目を入れて頂く必要はありません。）
※配信金額欄に0が入っていると、図②の「近似曲線のオプション」設定時にエラーが出る為、配信自体がない日は行ごと削除してください。

【図①：広告データの記載イメージ】

こちらを記載いただくことでExcel内に散布図グラフが自動で作成されるようになっています。続いて作成された散布図に対して、近似曲線を追加してください。

【図②：散布図に近似曲線を追加する方法】

作成されたグラフのいずれかのプロットを右クリックすることで、②の図のようなオプションが表示されます。オプションが表示された後は「近似曲線のオプション」内の「対数近似」を選択し、「グラフに数式を表示する」「グラフにR-2乗値を表示する」にチェックしてください。

2. 算出された関数式をもとに分析を行う

これまでの作業をいただくと、グラフ内に関数式が表示されます。図③に表記されている赤枠部分が関数式です。

【図③：配信結果をもとにした関数式の表示】

こちらの関数式は過去の配信結果を説明する式となっており、広告配信金額（x）によってCV数(y)がどのように変化するのかを表しています。今回の例では関数式をY＝A ln(x) – Bと表現した際のAとBの値をこの後の分析で活用します。(今回の例では、A=4.925、B=35.966です。)

補足
・ R2乗値とは
シミュレーションした予測数値が実際の数値と比べてどのくらい一致しているか（シミュレーションの誤差）を示す指標のこと。
R2は一般的に0～1の範囲で表現され、値が大きいほど適切なデータで、値が小さいほど適切なデータではないという意味になります。
・ R2乗値の判断目安
　・ 0.6以下：誤差が大きすぎて予測値として意味をなしていない
　・ 0.8以上：誤差が少ない良い予測値
　・ 0.9以上：精度が高すぎて過学習を疑う数値
参考：決定係数R2って何？は今日でお終い！3分でわかるR二乗とは

上記A,Bの値をExcel内に入力したあと、予算をセルのG58以降に入力いただくと分析結果が算出されます。分析結果では、想定のCV数や、予算増加によりどれくらいCV数が更に期待できるかをまとめています。

なお、対数近似曲線はグラフの傾きは鈍化していくものの、理論上無限に上昇をするモデルのため、実データが無い領域の数値については予測の精度が低くなる傾向があることは念頭に置いて分析を行いましょう。
（例：過去実績では予算250万までの配信しか出していないが、予算500万の配信シミュレーションを行う場合など）

3. 自社の状況を考慮し、広告予算を決定する

【図④：予算変動によるCV数の変化を分析】

分析結果の見方ですが、例えば日予算が4万円の際は、CV数が16.22件でCPAが2,466円になります。そこに対して日予算を6万円まで伸ばすと、CV数は18.22件、CPA3,293円になります。ただし、2万円を追加して得られたCV数は2件のため、この際は2件の獲得のために2万円を活用したのと同義になります。そこで純増分のCV数だけを加味した増分CPAは、10,015円という評価になります。

このように日予算を増減させ分析結果を確認することで、どれくらいまで配信金額を増やしても自社の目標内で広告配信ができるかを確認することが可能になります。

「誰でも簡単に作成できる」ゆえの、本分析のデメリットと活用を避けたほうが良いケース

最後に今回ご紹介した分析結果があまり参考にならないケースを紹介します。前提として今回紹介した分析方法は簡易的ではあるものの、弊社でも実際の支援の場で活用しているように実用性の高いものとなっています。ただし、簡易的であるがゆえに分析結果に反映すべき要素は多分に残されているため、どのようなケースでは本分析活用を避けたほうが良いのか補足します。

ケース1：実績データに異常値や外れ値が多く含まれている場合

今回の分析は過去の実績データの多数派を説明できる関数を作成する作業をしているため、平均から外れる値や異常値が多く含まれていると参考にならないことが多いです。

【例】
・ セールの頻度が例年より増減しており、同一の配信金額でもCV数に大きな違いが出ている
・ 在庫がなくなった期間の広告効果が下がっている
・ 広告アカウントの最適化地点を変更したため、成果に差が出ている

ケース2：過去と現在の間に大きな環境変化が起きている場合

次にサービス・製品の根本のパフォーマンスが分析前後で大きく変わってしまっている場合も分析結果が役に立ちづらいです。上記で説明した要素が特定時期なのに対し、こちらは継続的に影響を与える要素のイメージです。

【例】
・ コロナによって消費行動が冷え込んでしまい、サイト全体のCVRが下がってしまった
・ LP変更前後で成果が大きく変わっている
・ TV出演により知名度があがり、製品/サービスが選ばれやすくなっている

ケース3：過去の実績データと未来に期待するデータの差が大きい場合

今までの数倍の予算を広告に活用する際や新しい媒体、施策の挑戦の際の分析にも活用が難しいことが多いです。今回の分析結果は過去の配信結果をもとにしているため、過去と分析したい内容に差があればあるほど精度が落ちてしまいます。

【例】
・ 新たに認知施策を取り入れていく
・ 競合との競争に勝つために数倍の予算を組みたい
・ 今まではテスト的に数十万円しか配信していなかったが、今年は数百万円の投資をしたい

上記のような3つのケースでは、本分析を信頼してしまうと誤った意思決定に繋がってしまうため、広告運用の専門性を持っている方に相談いただく方が良い示唆につながると思います。

まとめ

今回ご紹介した分析方法は簡易的ではありますが、弊社でも支援の場で意思決定に活用したり、広告主との予算策定における議論のたたき台として活用していたりします。広告予算の分析やシミュレーション作成といわれると、数字をなんとなく変更し、起こり得そうな数字に加工してしまうこともあると思うのですが、今回ご紹介したような方法で根拠をもって算出することが可能になります。ぜひ日々の広告運用にお役立ていただけると幸いです。