スプレッドシートでのWeb広告の進捗管理に使える関数一覧(Supermetricsを利用)

Spreadsheet

Supermetricsに関する情報がWeb上に少なく、自社でも導入に手こずってしまったため、少しでも、多くの広告運用者の負担を軽くできればと思い、記事にしてみたいと思います。
私も随時調べながらの更新になるため、アップデートがあれば、記事にも更新を加えていきます。

Supermetricsのインストール方法などは、既に多くの方が書いているので、以下リンクをご参考ください。
https://try.analytica.jp/article/how-to-apply-supermetrics

この記事では、進捗管理に使える関数を紹介します。

日付ごとのステータス判別


日付ごとに、「未開始」→「本日開始」→「消化不足(5%、10%、15%、20%、25%)」「超過(5%、10%、15%、20%、25%)」「問題なし(5%以内なら)」→「終了済み」のステータスとしています。
消化金額は、Supermetricsでシートに引っ張ってきており、予算・開始日・終了日を予め設定して、日数進捗に対する実際の消化進捗を比較して、%を算出しています。

昨日までの消化金額の算出

※以下の”*******”には、該当のスプレッドシートIDが入ります。

SUMとQUERYとIMPORTRANGEの組み合わせです。
まず、IMPORTRANGEで他のシートから該当データを引っ張ってきます。
IMPORTRANGEで、合計したいディメンションと数値の2列分を選択しておきます。
(上記例では、日別×コストを算出しているので、日別~コストの列を2列分選択しています。)

次に、QYERY関数を使用します。

上記のselect以下の記述で、「昨日までのコスト」を抽出しています。

Col2(IMPORTRANGEで引っ張ってきた、2列目の数値)を抽出
ただし、col1(IMPORTANGEで引っ張ってきた、1列目の数値)の条件が以下に合致するもののみ。

条件は、text関数で記述しています。
today関数で、今日までの期間を意味するので、-1で昨日までの期間を指定しています。
加えて、text関数で表記を年月日の形式に変換してます。

つまり、「col1が、昨日までの期間に合致するcol2の列を抽出」という指示内容になります。

最後に、上記までの記述すべてを、おなじみSUM関数で括ると、「col1が、昨日までの期間に合致するcol2の列を抽出」して、合算という式に仕上がります。

昨日の消化金額の算出

※以下の”*******”には、該当のスプレッドシートIDが入ります。

先程の記述とほぼ同じです。

selectの期間指定が、「>=」から「=」に置き換わっただけです。
コストのマスターデータに、日別のコストをSupermetricsで自動更新にしておけば、このように、昨日の消化金額も簡単な計算式で把握できます。

予算の消化進捗のパーセント表示

最終的に、「①目標消化金額 – ②現在の消化金額」で、目標に対する金額の超過・遅れがどの程度なのかを算出します。
(この算出結果を、「日別ごとのステータス判別」の関数の指定先にすることで、予算の消化進捗のパーセント表示が可能です。)

現在の消化金額は、先程の「昨日までの消化金額」で算出してますので、①の関数を設定することで、算出可能です。

関数が長いので、式の概要の説明を入れます。

開始日よりも、前の日付でもエラーが出ないよう、日数進捗が、0(開始日当日)もしくは1以上(終了済み)、その他(開始日よりも後で、終了日よりも前)というIF条件としてます。
中身自体はシンプルで、開始期間中であれば、「1日あたりの消化金額目安」と「配信日数(今日 – 開始日)」となってます。

あとは、「1日あたりの消化金額目安」も、エラーを避けるためIF条件で入れていますが、
基本は、終了日から開始日を引いて1を足した日数を「予算残金」で割ってるだけです。

「予算残金」は、予算から昨日までの消化金額で算出しています。
※前述の「昨日までの消化金額」です。