トップ > blog > 【Google 広告】RSAのアセット毎データをLooker Studioで自動化する方法を見つけたので共有申し上げます

【Google 広告】RSAのアセット毎データをLooker Studioで自動化する方法を見つけたので共有申し上げます

Google広告のレスポンシブ検索広告の効果検証でお困りの方向けに、アセット毎のデータを効果的に自動レポーティングする方法をご紹介します。

blog

はじめまして！JADEで広告運用を中心にしている畑田と申します！

いきなりですが、「Google 広告でレスポンシブ検索広告しか作れなくなったけど、その効果検証はどうしたらいいの？」と困っている方、多いのではないでしょうか？

今回の記事は、JADE社内で使っている「レスポンシブ検索広告のアセット毎のデータを見ることができるレポート」の作り方を解説しました。 この方法であれば、一度作成すれば新しいアセットを追加しても日次で自動更新されるため、日々の分析がかなり楽になります。

途中でJavaScriptを使ったり、SQLを使ったりする箇所はありますが、知識がなくても作成いただけるよう、基本コピペで作れるようにしております。

ぜひレポートを作成して日々の業務で使ってみてください！

本日のゴール

  • Google 広告で作成したレスポンシブ検索広告のアセットごとのデータを、Looker Studioに自動的に出力されるようにする。

どんな人におすすめか

  • レスポンシブ検索広告を作ったは良いが、どう成果を見ていけばよいのか悩んでいる人
  • Looker Studio でレスポンシブ検索広告のアセットごとのデータを出力する方法は知っているが、手動更新が大変なので、新しいアセットを追加したら自動で更新されるようにできればいいなと思っている人

事前準備

BigQueryを使えるようにしておく

BigQuery エンタープライズ向けデータ ウェアハウス | Google Cloud

Google Cloud に登録し、BigQuery を利用できる状態にしておいてください。

BigQueryは扱うデータ量が多くなれば費用がかかりますが、10 GB のストレージと 1 か月あたり最大 1 TB のクエリについては無料で利用できます。そのため、今回の内容でのみBigQueryを使用する場合は無料の範囲内で使えると思います。

※念の為費用が発生するのを極力阻止したい場合は、上限予算の設定をしておくことをお勧めします。

BigQuery の費用を管理する | Google Cloud 公式ブログ

また、企業によっては、すでにBigQueryを導入し使用している場合もあるでしょう。その場合はBigQueryの管理を担当されている方に、BigQueryの使い方などをこの記事を元に軽くコミュニケーションとっていただくのが良いかと思います。

未導入かつ法人の場合は、念のためBigQueryの使用に関する社内決裁をとるのをお忘れなく。

レポートの作成手順

概ねの流れは次のとおりです。

  1. Google スプレッドシートを新規作成する

  2. Google 広告管理画面からレスポンシブ検索広告のデータをスプレッドシートに出力する

  3. 出力したスプレッドシートをBigQueryに取り込む

  4. BigQueryに取り込んだデータを整形する

  5. BigQuery で整形したデータとGoogle 広告のデータをLooker Studioで結合して混合データを作る

  6. Looker Studioでレポートを作る

1. Google スプレッドシートを新規作成する

まずは広告データを出力するためのGoogle スプレッドシートを新規作成してください。

↓Google スプレッドシートのTOP↓

Google Sheets: オンライン スプレッドシート エディタ | Google Workspace

スプレッドシートのタイトルを広告アカウント名などの任意の名前に変更しましょう。

タイトル変更以外にシートに何か手を加える必要はありませんので、タイトルを変更したら作成したスプレッドシートのURLをメモ帳などにコピペして保存しておきます。

※スプレッドシートには広告の見出しや説明文などのデータが入るため、見えてはいけないものを見せてはいけない人に共有しないよう、保存場所には注意してください。

2.Google 広告管理画面からレスポンシブ検索広告のデータをスプレッドシートに出力する

続いて、Google 広告から先程作成したスプレッドシートへ広告見出しなどのデータ出力をする設定を行います。

Google広告管理画面を開き、

ツールと設定 ＞ 一括操作 ＞ スクリプト

を開いて、スクリプトの作成を行います。

スクリプトの新規作成

スクリプト管理画面内の青い＋ボタンをクリックし、スクリプトを新規作成します。

スクリプトを新規作成したら、元々入っていた文字は全て消して、以下のコードをコピペします。

その後、コード内の

SpreadsheetApp.openByUrl("ここに先程のスプレッドシートのURLを入れる") の「ここに先程のスプレッドシートのURLを入れる」の文言を、先程①で保存しておいたスプレッドシートのURLに置き換えます。

function main() { const sheet = SpreadsheetApp.openByUrl("ここに先程のスプレッドシートのURLを入れる"); 
AdWordsApp.report('SELECT ResponsiveSearchAdDescriptions, ResponsiveSearchAdHeadlines' + ' FROM AD_PERFORMANCE_REPORT ' 
+ ' WHERE AdType = RESPONSIVE_SEARCH_AD ').exportToSheet(sheet.getActiveSheet()); 
}

スクリプト名を『無名のスクリプト』から任意の名前に変更します。 画像右側赤枠の「新しいスクリプトエクスペリエンス」をオフにします。

スクリプト名の変更とその他必要な設定のやり方

スクリプトが完成したので、「実行」ボタンを押すと、先程作成したスプレッドシートにデータが出力されます。 ※スクリプトの実行にはアカウントの承認が必要な場合があります。

スクリプト実行後、画像のように2列のデータがスプレッドシートに出力されます。

出力後のスプレッドシート

A列：「ResponsiveSearchAdDescriptions」には「説明文」 B列：「ResponsiveSearchAdHeadlines」には「見出し」 という形で、レスポンシブ検索広告の各アセットのデータがJSON形式で格納されています。

構成としては、A、Bの各セルに

  • assetText：設定した見出し、説明文のテキスト
  • assetId：アセットのID
  • assetPerformanceLabel：アセットのパフォーマンス(最良、良、学習中など)
  • assetApprovalStatus：審査のステータス(承認、不承認など)

が存在しています。

このうちレポートで使うのは「assetText」と「assetId」です。 後ほどBigQueryにこのデータを取り込んで加工しますが、現時点ではここまでできていればOKです。

自動化のための設定

スクリプトの一覧画面で以下赤枠内の時間設定をすれば、毎日その時間にスクリプトが動きます。 その際に新しいアセットがあれば、スプレッドシートに追加で出力してくれます。

スクリプト自動化のための設定

3.スプレッドシートに出力したデータをBigQueryへデータソースとして取り込む

まずは以下のURLからBigQueryを開きます。

BigQuery エンタープライズ向けデータ ウェアハウス | Google Cloud

事前にアカウントの登録をしてあれば、おそらく以下の画面になると思います。

青いボタンの「コンソールへ移動」を押します。

BigQueryのコンソールへ移動する画面

「コンソールへ移動」を押すと、以下のような画面に遷移するかと思います。

ここで、「データを追加する」を選択します。

BigQueryのデータを追加する

データソースは、「Googleドライブ」を選択します。

BigQueryのデータソース追加画面

以下の画面になるので、画像に記載の文言の通りに入力して進めてください。

テーブルの作成画面

※シート範囲の「データを出力したシート名」とは、スプレッドシート下部のシート名です。(以下画像参照)

 

スプレッドシートのシート名指定箇所

全て入力できたら「テーブルを作成」を押してここの準備は完了です！

4. BigQueryに取り込んだデータを整形する

続いて先程スプレッドシートからBigQueryに取り込んだデータを、SQLを使って「アセットID」と「アセットテキスト(広告見出し、説明文)」ごとに分ける加工をしていきます。

まずはBigQuery内で「クエリを新規作成」して以下の画面を表示します。

BigQueryのクエリ新規作成画面

枠内に以下のクエリをコピペします。

SELECT
  JSON_EXTRACT_SCALAR(rsah, '$.assetId') AS RSA_assetId,
  JSON_EXTRACT_SCALAR(rsah, '$.assetText') AS RSA_assetText
FROM
  `先ほど作ったデータセット内のテーブルを指定`,
  UNNEST(JSON_EXTRACT_ARRAY(ResponsiveSearchAdDescriptions, '$')) rsah

UNION ALL

SELECT
  JSON_EXTRACT_SCALAR(rsah, '$.assetId') AS RSA_assetId,
  JSON_EXTRACT_SCALAR(rsah, '$.assetText') AS RSA_assetText
FROM
  `先ほど作ったデータセット内のテーブルを指定`,
  UNNEST(JSON_EXTRACT_ARRAY(ResponsiveSearchAdHeadlines, '$')) rsah

上記コード内の「先程作ったデータセット内のテーブルを指定」は、先程作ったテーブルの以下のテーブルIDと置き換えます。

作成したテーブルIDのコピー場所

ここまで入力が終わったら「実行」を押してみてください。

正しくクエリが記述できていれば、画像のような表が結果に表示されると思います。

アンネストのクエリ結果

表示された結果に間違いがなさそうであれば、「保存」を押して「ビューを保存」を選択します。

ビューとは、作成したSQLのSELECT文に名前を付けてデータベース上に保存しておける仕組みです。

「保存しておけば、過去に作ったクエリ結果をいつでも簡単に呼び出せるもの」だと考えてもらえれば大丈夫です。

ビューの保存方法

保存時には画像のように、先程BigQueryで使用したプロジェクト、データセットを指定し、テーブルには任意の名前をつけてください。

保存名

ここまでできればOKです！

5.BigQuery で整形したデータとGoogle 広告のデータをLooker Studioで結合して混合データを作る

いよいよLooker Studioでレポートを作成していきます。

まずはLooker Studioを開きましょう。

Looker Studio: ビジネス分析情報の可視化  |  Google Cloud

まずは「作成」から新しくレポートを作成します。

新しくレポートを作成すると以下の画面になるので、2つのデータソースを追加しましょう。※2つ目のデータは上部メニューの「データを追加」から追加できます。

画像の赤枠を選択します。

  • Google 広告(今回レポートを作成したい広告アカウントを接続する)
  • BigQuery(BigQueryで先ほど④の手順で作成したビューを接続する)

データソースの選択

BigQueryのビューを接続して作成されたデータソースは、「RSA_assetId」のタイプが「テキスト」となっていたら、「数値」へ変更します。

テキストを数値に変換する

データソースの追加が完了したら、「グラフを追加」を押し、表を選択します。 その表の編集画面で「データを統合」を選択します。(以下画像参照)

LookerStudioで表の作成

データの統合では、先程追加した以下のデータソースを選択し、AssetIDをキーとして「完全外部結合」で結合します。

  • Google 広告　Asset ID(Google 広告のデータソース)
  • BigQuery　RSA_assetId(手順4で指定したカラム名)

混合データの完全外部結合

ここでは、ディメンション、指標は以下の項目を選択します。

選択をしたら、保存をクリックします。

混合データの外部結合の指標選択

その後、作成している表の中でCTRとCVRのフィールドを作成します。 作成方法は以下の通りです。 1.「指標を追加」→「フィールドを作成」を選択

フィールドの新規作成方法

2.CTR・CVRのフィールドをそれぞれ作る(コピペでOK)

CTRの計算式

SUM(クリック数) / SUM(表示回数)

CVRの計算式

SUM(コンバージョン) / SUM(クリック数)

6.Looker Studioでレポートを作る

完成系はこのような形になります。

(これはダミーです。)

レポートの完成系

表には以下のディメンションと指標を設定します。

ディメンションと指標

次に先ほど作成した表を使いやすくするために「コントロール(フィルタのようなもの)」を追加します。

レポート上部のメニュー内にある「コントロール」を選択して、「プルダウン リスト」を選択します。

おすすめは、「Ad ID」・「Asset type」・「Date」・「広告グループ」です。

設定は一例ですが、以下のようにすると使いやすいと思います。

Dateは任意の期間でご自由に設定するのが良いと思います。

コントロールフィールドの設定

コントロールは使いやすいように配置して、広告グループごとにみたり、広告IDごとにみたりと色々とためしてみて、好きな体裁でレポートを作成してみてください。

コントロールフィールドのレポートへの設定

レポートの作成については以上となります！

ご注意事項

見出し・説明文がnullになるものがある

見出し、説明文が「null」になることがあります。これは、削除済みのアセットや、レスポンシブディスプレイ広告やレスポンシブ動画広告の見出し、説明文などが該当するようです。 今回の記事の手順で取得したデータがどのように出力されるか、あらゆる条件での確認はしていませんので、実際に表示されるレポートが正しいかどうかは、よくご注意のうえご覧ください。

集計行について

作成したレポートの集計行の数値は、実際に広告が表示された回数やかかった費用などの数値よりも多く表示されます。

各アセットは、他のアセットとともに表示されるものであり、その重複分がすべて合計して表示されるためです。

集計行は表示しない、もしくは表示はしても参考にしないようにしてください。

最後に

JADEでは、このレスポンシブ検索広告のレポートをはじめ、広告のデータやGAのデータなどを様々な切り口から分析できるレポートをテンプレート化し、日々の広告運用に活用しています。

BigQueryやLooker Studioを駆使して広告データを分析していくと、広告の管理画面だけでは見つけることが難しい、発見や気づきがあります。

ぜひ「はじめてLooker Studioを触った」という方は、今回のレポート作成をきっかけにLooker Studioでのレポート作成を色々試してみてはいかがでしょうか？

今回のレポートがみなさんの日々の広告運用のお役に立てれば幸いです！

＼ JADEのサービス紹介ページが新しくなりました！ ／

JADEでは、SEOコンサルティング、広告運用代行、Google Analytics4設定・運用サポートなどをご提供しています。

サービス詳細はこちら

ikoanadatah