Googleルッカースタジオでお金をかけず最低限の広告レポートを作ったので共有
どうも、まんじゅうマーケティングの尾崎です。
広告を運用したり、マーケ活動をする中で、ステークホルダーやクライアント、社内外の関係者へ、運用型広告の数値進捗を共有できる環境が必要なことありませんか？
毎回まとめてメールやチャット返すのは面倒だし、聞く側もタイムラグがあって不便ですよね。そこで、複数の情報をまとめて表示するダッシュボードの出番です。
ただ、完全自動で更新可能な機能や、詳細な分析が可能なツールとなると、お金がかかったり、導入のハードルが高かったり、機能過多になってしまう恐れもあります。
そこで、今回は
・非エンジニアで作る
・お金をかけない
・関係者に共有する用（≠分析用）
の視点で、運用型広告のレポートをGoogleルッカースタジオ（旧：Googleデータポータル）で作ってみました。
初めてのナレッジ記事なので、かなり走り書きしてます。ご了承ください🙏
▼ツールとしては、下記を使います。どちらも無料です。
・Googleルッカースタジオ（旧：Googleデータポータル）
・Googleスプレッドシート（媒体ごと）
▼このレポートの目的
関係者がアクセスし数値を把握できる状況を作り、コミュニケーションコストを下げる。
更新の手間を極力かけない。
▼レポートの項目は？
・広告媒体別の数値進捗（月次/週次/日次）
・広告全体を合計した数値進捗（月次/週次/日次）
・ただし週次の集計周期は木曜〜水曜
▼何が必要？
・広告媒体からダウンロードした日次ごとのデータ
・フォーマットを統一したスプレッドシート
・成型されたGoogleルッカースタジオ
▼イメージ図
▼完成したもの
・Googleルッカースタジオ
（ソースとなるスプレッドシートは、ルッカースタジオの1ページにURLあります）
※コピーを作成してよしなに使ってください🙏
GAを筆頭に、他のソースと連携したり、自社データと掛け合わせもしやすいはずです〜！
エンジニアやデータサイエンティストといった専門的な方々から見たら、もっといい方法があるかもしれません。ただ、あえて専門的でないことでレポートの構造を理解でき、自分でカスタマイズしやすくなるかなと考えています。
ソースを用意する
今回のレポートで表示する項目（列名）は以下です。
レポートに使用したソース例）
https://docs.google.com/spreadsheets/d/1E9nkWbVVWJz3gQko7zx7c76liB-BfM40-eYbaBsiIqk/edit?usp=sharing
・インプレッション数（imp）
・クリック数（click）
・コンバージョン数（CV）
・広告費
・CPC
・CTR
・CPA
・CVR
（必要に応じて個別に列増やしてください）
ソースのコツは、日付と列番号をすべてのシートで同じにしておくことです！列名とかシート名も統一しておいた方がいいです。
初回のみ
媒体ごとにスプレッドシートを用意します。（1つのスプレッドシートにまとめてもいいですが、ミスを減らすため、混乱しないように個別で用意しました。）
各スプレッドシート内に2つのシートを用意します
・シート名：シート1
・シート名：週次合計
※週計を月〜日の周期で集計する場合は「週次合計」不要です。
＜シート1＞
列を作ります。
どのスプレッドシートでも列名は下記の通りの順序で値を統一します。
・日付
・imp
・click
・CV
・広告費
・CTR
・CPC
・CPA
・CVR
・（媒体名）←なくても大丈夫ですが、今後カスタマイズする際に役に立つ
日付は「yyyy/mm/dd」の形式で統一し、入力（オートフィル）してください。ただし、GAとの連携を考える場合は「yyyy-mm-dd」の形式に統一しておいた方が便利な場面あります。
週次を木曜〜水曜日で集計したいので、A2（レポートのはじまり）は木曜日にします。
CTR以下はそれぞれの公式を入力してください。
＜週次合計＞
列を作ります。
どのスプレッドシートでも列名は下記の通りの順序で値を統一します。
・週の始点
・imp
・click
・CV
・広告費
・CTR
・CPC
・CPA
・CVR
・（媒体名）←なくても大丈夫ですが、今後カスタマイズする際に役に立つ
▼週の始点
=OFFSET('シート1'!$A$2,(ROW(A1)*7-7),0,1,1)
OFFSET関数でシート1のA2から7つづれで、週の始点になる日をもってきます。こちらもオートフィルで大丈夫です。
▼imp,click,CV,広告費
=SUM(OFFSET('シート1'!$B$2,(ROW($B1)*7-7),0,7,1))
OFFSETとSUM関数を使って、7日間ごとに集計します。こちらもオートフィルしてもらえると。上記はimpですが、click,CV,広告費も基点となる列をずらします。（ $Bを$C,$D,$Eに変えてください）
CTR以下はそれぞれの公式を入力してください。
これで、初期設定完了です！
ソースの更新（週1手動更新）
3ステップ3分で終わります
①広告媒体からimp,click,CV,広告費の日別レポートをダウンロード
②ダウンロードしたレポートから「シート1」に日付と列を合わせてコピペ
③「週次合計」を1列オートフィルで追加する
ダッシュボードをつくる
2種類のページを作成します。
・媒体ごとの数値がまとまったページ（媒体ページ）
・全媒体の合計がわかるページ（全体合計ページ）
媒体ページ
月次/週次/日次の3項目で作ります。
1ページ作ってしまえば、後はコピペして、データソースを変更するだけで追加できるのでご安心を。
▼月次
概要としては、「シート1」（日次）のデータを月ごとに表形式でまとめます。
①ページの対象となる媒体のスプレッドシートの「シート1」を、データソースに、追加します。
②期間のディメンション、ディメンションに「日付」を選択します。ディメンションのカレンダーマークをクリックし、タイプ「年、月」を選択します。
③指標にimp,click,CV,広告費を追加します。
④CPC,CTR,CPA,CVR用に指標を追加します。
例）CPCの場合
指標を追加をクリック。
下記の式を入力します。
SUM(広告費)/SUM(click)
タイプで通貨を選択します。（割合の場合は%）
CTR以下も、同様に公式を追加していきます。（SUMで囲うことを忘れずに）
▼週次
①ページの対象となる媒体のスプレッドシートの「週次合計」を、データソースに、追加します。
②ディメンションに「週の始点」を選択
③指標はあるだけそのまま追加してください
④必要に応じて、指標のタイプで通貨、％に変更して数値を整えます
▼日次
①ページの対象となる媒体のスプレッドシートの「シート1」を、データソースに、追加します。
週イチ更新で、日次は表だとあまり意味をなさないので、折れ線グラフとかで推移をみやすくした方がよいかもですね。
全体合計ページ
こちらも同じく、月次/週次/日次の3項目で作ります。
データの統合をしないといけないので、少しハードル感じますが大丈夫です！統合データは、日付で各スプレッドシートを突合させて、横一列に並べられたイメージです。
▼月次
大まかな流れ、各媒体の「シート1」を結合させ、統合データ（1つのシート）を作ります。それぞれを合計した指標を追加し、ディメンションで月別集計します。
①統合対象のソースに「年月」「週」を追加する。
[追加済みのデータソースの管理]から、統合対象となるすべてのソース（各媒体のシート1）を編集していきます。
[日付]の左にある「・・・の縦」をクリックして、複製を2つ作ります。複製した1つは、フィールド名「年月」、タイプを「年、月」、もう1つはフィールド名「週」、タイプ「ISOの年と週」に変更します。
これをしないと、うまくダッシュボード画面で表示することができません！
Unyoo.jpさんの記事を参考にしました。
②ソース（各シート1）を結合し、統合データをつくる
統合を管理から、「統合を追加」を選択します。
上記のように、ディメンションと指標をそろえます。
さらに媒体がある場合は、[別のテーブルを結合]で他の「シート1」追加していきます。
次に[結合を設定]でディメンションをそろえて選択します。
データソース名に「日次データの統合」と名前をつけて、保存します。
③ページにもどり、指標を作成する
データソースに先程つくった、統合データ「日次データの統合」を選択します。ディメンションは年月を選択。
現状はスプレッドシートが横並びでつらなっている状態ですので、同じ列名どうしで合計した指標を作っていきます。
例）imp(合計)
IFNULL(imp (表 1),0) + IFNULL(imp (表 2),0)
さらに媒体がある場合は、
+IFNULL(imp (表 3),0)
+IFNULL(imp (表 4),0)
と足していってください。
click,CV,広告費も同じように足し算していきます。
④CPAの指標を作成
(SUM(IFNULL(広告費 (表 1),0)) + SUM(IFNULL(広告費 (表 2),0)))
/ (SUM(IFNULL(CV (表 1),0)) + SUM(IFNULL(CV (表 2),0)))
媒体ごとに合計して、それらを足し算して割っています。
全体でみる時は、CPAだけあればいいかなと思い、CPAだけにしてます。
これで、全体の月次合計は完成です。
新しい媒体を追加した時に、統合データへの追加と、指標への追加が必要になります。
▼週次
週次は月次と違い、媒体が増えた際はソースを結合するだけで大丈夫です！
①各媒体の週次合計シートを結合した統合データを作成します。
さきほどと同じように、[統合の管理]から[統合を追加]をクリック。対象となるスプレッドシートの「週次合計」シートを結合させていきます。
ディメンションに「週の始点」、指標はimp,click,CV,広告費。
②結合の設定、結合条件に指標も含める
月次と違い、週次では[結合条件]にディメンションと指標どちらもそろえて選択します。（左右が同じになるように）
※table1のソースのセルには空白をなくすため、0で埋めて「左外部結合」してください。
データソース名に「週次データの統合」として、保存してください。
③ページに戻り、表をつくります
データソースに「週次データの統合」を選択します。
ディメンションは「週の始点日」、
imp,click,CV,広告費を指標に選択してください。（新しく指標を作る必要はありません）
④CPAの指標を作成
(SUM(IFNULL(広告費 ,0)) + SUM(IFNULL(広告費,0)))
/ (SUM(IFNULL(CV ,0)) + SUM(IFNULL(CV,0)))
▼日次
月次のグラフ複製します。ディメンションを日付に変更すれば、そのまま日別のデータとして使用できます。
はい、これで完成です！！！！
おつかれさまでした！！！👏
日々の更新業務について
週次での更新
①媒体から日別レポートを落として、スプレッドシートの「シート1」にコピペします。
②スプレッドシートの「週次合計」を1列だけオートフィール
これを、各媒体ごとにやります。以上で更新終了です。
新しい媒体が増えた時の更新
①すでに使っているスプレッドシートを複製してください。開始以前の列も残しておきます。
②ルッカースタジオにページを作ります。他のページを複製して、データソース追加し、変更するだけでOKです。
③全体合計の統合データに結合させる。
④全体合計のカスタム指標それぞれに、追加する。
▼月次
・imp,click,CV,広告費の全部
・CPA
▼週次
・CPA
まとめ
週次更新の広告レポートを作ってみました。（結構走り書きになってしまったので、不明点やおかしなところあったらTwitterでDMいただけると嬉しいです。）
最低限の項目、かつ表形式で表現していますが、ソースが整っているのでその他のグラフもつくりやすいかと思います。また、1ページにまとめると媒体ごとの比較もしやすくなりますし、ソースを1つにまとめるとドリルダウン形式でよりかっちょい表が作れます。よかったらアレンジしてみてください。
企業の環境に応じて必要な項目を追加したり、GAなどの外部サービスと連携したりすればそれなりの価値がでるかなと思っています。
BigQuery、MySQLとも連携できるので、SQLの理解があると、さらに便利で共有だけでなく分析までできるダッシュボード作れます。ソースの更新の部分もGASを使えば自動でできたりします。エンジニアやデータサインティストたちの力を借りれば、可能性は無限大です。
どなたかのお役にたてれば幸いです！
▼宣伝
マーケティングやグロースの総合支援を行っています。
（現在LP作成中で、仮の姿です、何卒🙏）
この記事が気に入ったらサポートをしてみませんか？