はじめに｜予算配分、なんとなくで決めていませんか？
「この媒体は今年も出しておきたいよね」
「とりあえず、前回と同じ構成でいこうか」
そんなふうに、広告予算を“経験と勘”で決めてしまっている現場、案外多いのではないでしょうか。
私は広告代理店でメディアプランナーをしていますが、実際に現場で多くのクライアントと接していて、「本当にその予算配分でいいのか？」とモヤモヤすることがよくありました。
本記事は、そんな状況に風穴を開ける内容です。
PythonとExcelを組み合わせれば、“合計予算の中でどの媒体にいくら投下すべきか”を、KPI最大化という視点で自動で計算できるようになります。
しかも、Python未経験でも、コードのコピペとExcel操作だけで実践可能。
実際にこの記事の手順通りに進めることで、私は社内で「お前どうやって作ったの！？」と聞かれるほどのツールを作ることができました。
ゴール：総予算の中で、KPIが最大化する予算配分を自動で算出する
今回の最終ゴールは、「予算の使い方でKPIを最大化する」こと。
具体的には、以下のような最適アロケーションを出すことができます。
- 例えば、予算3000万円の中で、TV・YouTube・Instagramなどの各媒体にいくら配分すれば、KPI（例：利用意向や認知向上）が最も伸びるか
- しかも、Excel上でソルバーを使って自動で解くことが可能
そのために、以下の2つを事前に準備します。
準備物については、すでに【前回の記事】で詳しく紹介した内容ですが、概要だけご紹介します。
【STEP①】 「媒体ごとのリーチ率推定式」をPythonで作る
まずは、Pythonを使って、出稿金額に対するリーチ率の曲線を作ります。
用意するデータ（Excel）
以下のような形で、媒体別に出稿金額とリーチ率の実績データを用意します：
このデータをPythonに読み込ませるだけで、出稿金額を変数にして、リーチ率を予測できる関数（ロジスティック曲線）を自動的に作れます。
Pythonコードのポイント
Pythonでは以下の処理を実行しています：
- 各媒体ごとに「a, b, K」というパラメータを自動で算出
- 数値スケール調整（x桁, y桁）を行い、式を整える
- 出力されるのは「K / (1 + a×(金額/10^x)^b) × 10^y」という形
この式を使えば、Excel上で「出稿金額→リーチ率」を即座に予測できます。
この辺りは数式を理解する必要はなく、脳死でOK。
関連記事から数式コピペでイケます。
▶ 関連記事：
詳しくはこちらの記事をお読みください！ ※コピペでOKなPythonコード付き
📎広告出稿金額に応じた各媒体リーチ率を予測する方法｜参考データ付き
【STEP②】 広告接触による態度変容率（リフト率）を活用
意識調査などのアンケートデータを使えば、「広告接触によってどれだけ態度が変わったか」＝リフト率を算出できます。
たとえば、以下のようなイメージです：
「企業好意率」が
- 非接触者：5%
- 接触者：10%
だった場合、リフトは 2.0倍 となります。
このリフト率 × リーチ率 = KPIへの貢献度として、Excel上で活用できます。
▶ 関連記事：
リフト率の算出方法については以下の記事で詳しく取り上げています！
📎意識調査から広告効果を読み解く方法｜態度変容をリフト値で評価する
【STEP③】 Excelで“自動最適配分”する手順（ソルバー活用）
いよいよ、この記事の核となる部分です。
ここでは、、
【STEP①】Pythonで作成したリーチ率の式
【STEP②】広告接触による態度変容率（リフト率）
を使って、予算配分の最適解を求めるExcelソルバーの設定手順を紹介します。
Excelの「データ」タブにあるアドイン機能で、「目的セルを最大化・最小化するために、変数セルの最適な値を探してくれる」自動最適化ツールです。
1分で完了するソルバー追加方法はこちらをご参照ください！
設定手順
- 各媒体の予算欄を変数セルとして設定
- 最適配分を行う媒体の組合わせ入力
- 各媒体予算に応じたKPI貢献値を設定
- KPI貢献値＝リーチ率 × リフト率
- 媒体組合わせごとのKPI貢献値を合計
- 制約条件を入力しソルバーを回す
- 差額（総予算−各媒体予算の合計） ＝ 0
上記を設定後、「解決」を押すだけで、KPIが最大化される予算配分が自動で導き出されます。
1. 各媒体の予算欄を変数セルとして設定
Excelに以下のような入力欄を作ってください。
【差額】は出稿する【総予算】と各媒体予算の【合計】を引き算する数式を入力してください。
媒体数と媒体名は任意のものを設定してくださいね！
2. 最適配分を行う媒体の組合わせ入力
最適配分を行いたい媒体の組み合わせが何通りあるのか、Excelに入力してください。
このような形で、各組合わせで1のフラグを割り振っていきましょう。
これ媒体数に応じて組合わせが何通りあるかなんて考えるの超めんどくさいんだけど！！
組合わせが何通りあるか分からないという方は、chatGPTに以下の文章をコピペして質問してみてください。
一瞬で何通りあるのか、フラグ付まで行ってくれます。
TV/TVer/YouTube/Radiko/Spotifyの5媒体の総組み合わせを知りたいです。表頭に媒体名、表側に組み合わせ数、値に0・1のフラグを入れる形式で全組み合わせを出力してください。
3. 各媒体予算に応じたリーチ率とリフト率の入力欄を設定
前述の
【STEP①】Pythonで作成したリーチ率の式
【STEP②】広告接触による態度変容率（リフト率）
で算出したパラメータをExcelに貼り付けましょう。
先程の各媒体の予算欄の下に、赤枠で囲っている部分を入力してください。
なお、予算に対するKPI貢献値は「リーチ率×リフト率」を掛け合わせることで算出可能ですが、Excel上での入力式が少し複雑なので以下の数式を参考にしてください。
4. 媒体組合わせごとのKPI貢献値を合計
先程の各組合わせごとのKPI貢献値がいくらになるかを計算していきます。
以下のイメージ、赤枠部分を作っていきます。
数式の通り、1のフラグがつく場合はKPI貢献値を出力し、フラグが付かない場合は100%となるようにしています。
フラグが付かないところを100%としていることに特に意味はないです笑
そしてO列では、各媒体数値を掛け算した値を入力しています。
各パターンで媒体を出稿した時のKPI貢献度を計算しています。
そして最後に、赤枠部分、各パターンにおけるKPI貢献度を足しあげてください。
ここが最大化するようにソルバーを回します。
5. 制約条件を入力しソルバーを回す
では、ここまでの情報をもとに最後にソルバーを回します。
ソルバーはExcelの「データ」項目から見つけることができます。
もし、ここにソルバーが見当たらなければこちらから設定してください。
ソルバーの設定条件は以下です。
ExcelのFMTはNOTEからダウンロードできます！
ここまでご解説した、“KPI最大化の各媒体最適配分”のアロケーターファイルをNOTEから特別にダウンロードできるようにしています。
そして、ダウンロードには以下の2点の理由から500円ほど頂戴しております。
- 構造化に手間がかかったものであるため
- 専門性が高く、即実践なCSVファイルがダウンロード可能なため
今後も有意義な情報発信ができるように努めてまいりますので、
何卒ご了承いただけますと幸いです。
作業後の世界｜“自動化できるマーケター”が社内でヒーローになる
上記条件を入力後、右下の「解決」を押せば、各媒体予算が自動的にKPIが最大化する予算に修正されます。
この仕組みが完成すれば、毎回予算配分に頭を悩ませる必要はありません。
クライアントや上司から「なんでこの配分にしたの？」と聞かれても、数値で裏付けされた回答ができます。
また、非エンジニアの事業会社マーケターであっても、「エクセルでここまでできるのか！」という驚きを与え、社内で一目置かれる存在になれるはずです。
まとめ｜配分を“感覚”から“科学”へ
- 勘に頼らず、根拠ある予算配分を行う
- Pythonでリーチ率の式を作り、Excelで自動化
- これからのメディアプランナーには、定量的な視点が求められる
広告予算の配分は、単なる“作業”ではなく、ビジネス成果に直結する設計です。
本記事を参考に、ぜひあなたの現場でも「科学されたメディア設計」にチャレンジしてみてください。
コメント