はじめに
「複数コンバージョンテンプレート」をご利用いただくにあたっては下記の設定が必要になります。
設定内容
テーブル「〇〇_conversion」に対してのクエリの入力
「campaign_conversion」と「ad_conversion」と「keyword_conversion」にてカスタムクエリを入力して表示回数等の実績値とコンバージョンアクション別の実績値を1つのデータにまとめております。
「campaign_conversion」にてキャンペーン粒度のコンバージョン別のデータを抽出し、
「ad_conversion」にて広告グループ粒度、広告粒度のコンバージョン別のデータを抽出し、
「keyword_conversion」にてキーワード粒度のコンバージョン別のデータを抽出しております。
「ad_conversion」ではそれぞれの広告データに紐づくキャンペーンと広告グループ情報も保持しておりますので、「ad_conversion」にてキャンペーンと広告グループ、広告粒度のコンバージョン別のデータをまとめて抽出することも可能です。
どの粒度までコンバージョン別のデータが必要か、または極力使用するテーブルをまとめたい等によって変わる場合がございますので、上記情報をご参考にご設定ください。
- Looker Studioの編集画面にある「データを追加」をクリックして追加画面を選択してください、
- 「BigQuery」を選択して左側にあるメニュー「カスタムクエリ」を選択し、ATOMのデータが格納されているプロジェクトを選択してください。
- 「カスタムクエリを入力します」の欄にそれぞれのクエリを入力してデータの追加を行ってください。
campaign_conversionに入力するクエリ
入力するカスタムクエリは以下となりますのでコピーしてお使いください。
SELECT * FROM ( SELECT date, media, device, budget_name, advertiser_name, account_name, campaign_name, conversion_name, total_conversions FROM atom.ad_conversion ) PIVOT ( SUM(total_conversions) AS total_conversions FOR conversion_name IN ( -- ここで日本語からカラムに使える文字列に変換 'ConversionName_01' AS ConversionName_01, 'ConversionName_02' AS ConversionName_02, 'ConversionName_03' AS ConversionName_03, 'ConversionName_04' AS ConversionName_04, 'ConversionName_05' AS ConversionName_05 ) )
「-- ここで日本語からカラムに使える文字列に変換」
以下にコンバージョン名のリストを入力します。
左側に媒体上で設定しているコンバージョン名を、右側にてLookerStudioにて表示するコンバージョン名を入力してください。
※こちらの箇所でレポートに反映させたいコンバージョンアクション名の指定を行っていただく必要がございます。
※LookerStudioにて表示するコンバージョン名は日本語ではなく英語表記でご入力をお願いいたします。
例)媒体管理画面で「問い合わせ」のコンバージョン名を設定しており、そのデータを反映させたい場合'ConversionName_01' AS ConversionName_01,
の箇所に'問い合わせ' AS contact_inquiry,
と入力する。
ad_conversionに入力するクエリ
入力するカスタムクエリは以下となりますのでコピーしてお使いください。
WITH stg_ad_conversion AS ( SELECT * FROM ( SELECT date, media, device, budget_name, advertiser_name, account_name, campaign_name, adgroup_name, ad_name, ad_type, conversion_name, total_conversions FROM atom.ad_conversion ) PIVOT ( SUM(total_conversions) AS total_conversions FOR conversion_name IN ( -- ここで日本語からカラムに使える文字列に変換 'ConversionName_01' AS ConversionName_01, 'ConversionName_02' AS ConversionName_02, 'ConversionName_03' AS ConversionName_03, 'ConversionName_04' AS ConversionName_04, 'ConversionName_05' AS ConversionName_05 ) ) ), stg_ad AS ( SELECT budget_name, advertiser_name, account_name, campaign_name, adgroup_name, ad_name, media, device, date, ad_image1, ad_title1, ad_title2, ad_title3, ad_description1, ad_description2, ad_description3, impressions, clicks, cost, cost_fee FROM atom.ad ) SELECT stg_ad.ad_image1, stg_ad.ad_title1, stg_ad.ad_title2, stg_ad.ad_title3, stg_ad.ad_description1, stg_ad.ad_description2, stg_ad.ad_description3, stg_ad.impressions, stg_ad.clicks, stg_ad.cost, stg_ad.cost_fee, stg_ad_conversion.* FROM stg_ad LEFT JOIN stg_ad_conversion ON stg_ad.budget_name = stg_ad_conversion.budget_name AND stg_ad.advertiser_name = stg_ad_conversion.advertiser_name AND stg_ad.account_name = stg_ad_conversion.account_name AND stg_ad.campaign_name = stg_ad_conversion.campaign_name AND stg_ad.adgroup_name = stg_ad_conversion.adgroup_name AND stg_ad.ad_name = stg_ad_conversion.ad_name AND stg_ad.date = stg_ad_conversion.date
campaign_conversionに入力するクエリと同様、「-- ここで日本語からカラムに使える文字列に変換」
以下にコンバージョン名のリストを入力します。
左側に媒体上で設定しているコンバージョン名を、右側にてLookerStudioにて表示するコンバージョン名を入力してください。
※こちらの箇所でレポートに反映させたいコンバージョンアクション名の指定を行っていただく必要がございます。
keyword_conversionに入力するクエリ
入力するカスタムクエリは以下となりますのでコピーしてお使いください。
SELECT * FROM ( SELECT date, media, budget_name, advertiser_name, account_name, campaign_name, adgroup_name, keyword, match_type, conversion_name, total_conversions FROM atom.keyword_conversion ) PIVOT ( SUM(total_conversions) AS total_conversions FOR conversion_name IN ( -- ここで日本語からカラムに使える文字列に変換 'ConversionName_01' AS ConversionName_01, 'ConversionName_02' AS ConversionName_02, 'ConversionName_03' AS ConversionName_03, 'ConversionName_04' AS ConversionName_04, 'ConversionName_05' AS ConversionName_05 ) )
campaign_conversionに入力するクエリと同様、「-- ここで日本語からカラムに使える文字列に変換」
以下にコンバージョン名のリストを入力します。
左側に媒体上で設定しているコンバージョン名を、右側にてLookerStudioにて表示するコンバージョン名を入力してください。
※こちらの箇所でレポートに反映させたいコンバージョンアクション名の指定を行っていただく必要がございます。
追加したデータの名称変更とコンバージョン名の設定
前ステップでカスタムクエリを入力してデータを追加していただくと、「BigQuery カスタム SQL」という名称でデータソースが追加されます。
今後の管理のためにこちらの名称の変更やカスタムクエリを入力する際に設定したコンバージョン名をわかりやすい名称に変更することをお勧めいたします。
- メニュー「リソース」から「追加済のデータソースを管理」を選択します。
- 「BigQuery カスタム SQL」と表示されているデータソースの「編集」をクリックします。
- 左上にある「BigQuery カスタム SQL」の箇所をクリックし、「campaign_conversion」等のわかりやすい名称に変更します。
- ディメンション一覧にある「total_conversions_」で始まる名称が、カスタムクエリを入力する際に設定したコンバージョン名となりますので、「問い合わせ」等のレポートに表示する際にわかりやすい名称に変更して最後に「完了」をクリックします。
◆変更前
◆変更後
テーブル「campaign」に対してのフィールドの追加
データソース「campaign」を追加した後、データソース一覧の「campaign」の編集画面より「week」と「month」のフィールドを「date」を複製して作成します。
- 「date」を複製します(weekとmonthの2つ分複製)
- タイプを「年、月」と「曜日」に変更します
複製したdateの「日付」をクリックし、それぞれを「年、月」と「曜日」に変更します。
・年月の場合
・曜日の場合
上記作業をしていただくことによって、フィールド一覧に以下のように「month」と「week」が追加されます。
※こちらは月別レポートと曜日別レポートにて使用します。
テーブル「campaign_conversion」に対してのフィールドの追加
データソース「campaign_conversion」を追加した後、データソース一覧の「campaign_conversion」の編集画面より「week」と「month」のフィールドを「date」を複製して作成します。
- 「date」を複製します(weekとmonthの2つ分複製)
- タイプを「年、月」と「曜日」に変更します
複製したdateの「日付」をクリックし、それぞれを「年、月」と「曜日」に変更します。
上記作業をしていただくことによって、フィールド一覧に以下のように「month」と「week」が追加されます。
統合の設定
リソース 統合を管理 より、統合一覧を開き、それぞれの統合にて選択されているテーブルをサンプルデータから差し替えます。
「CP_CV」、「AG_CV」、「KW_CV」、「CP_CV_曜日用」に関しては、テーブルの差し替えに加えてクエリにて指定したコンバージョン名の設定も行う必要があります。 「◯◯_conversion」が選択されている方のテーブルに設定されている「CV01」等がサンプルのCV名になりますので、反映させたいコンバージョン名に差し替えて保存を行ってください。
CP_CVの場合
データをサンプルデータから切り替えると以下のように赤く「指標がありません」と表示されますので、こちらの修正と統合の再設定が必要になります。
テーブル1とテーブル2に設定していただくディメンションは以下となります。
・budget_name
・advertiser_name
・account_name
・campaign_name
・date
・media
・device
・month
右側のテーブル2に設定していただく指標は、カスタムクエリ入力時に指定したコンバージョン名を追加してください。
テーブルの間にある「統合の設定」をクリックし、統合の再設定を行います。
キャプチャのように、各テーブルで選択されているディメンションを全て選択し、統合を行ってください。
統合の再設定が完了すると、各テーブルのディメンションの右側にリンクされているアイコンが表示されます。
CP_CV_曜日用の場合
データをサンプルデータから切り替えると以下のように赤く「指標がありません」と表示されますので、こちらの修正と統合の再設定が必要になります。
テーブル1とテーブル2に設定していただくディメンションは以下となります。
・budget_name
・advertiser_name
・account_name
・campaign_name
・media
・device
・week
右側のテーブル2に設定していただく指標は、カスタムクエリ入力時に指定したコンバージョン名を追加してください。
テーブルの間にある「統合の設定」をクリックし、統合の再設定を行います。
キャプチャのように、各テーブルで選択されているディメンションを全て選択し、統合を行ってください。
統合の再設定が完了すると、各テーブルのディメンションの右側にリンクされているアイコンが表示されます。
AG_CVの場合
データをサンプルデータから切り替えると以下のように赤く「指標がありません」と表示されますので、こちらの修正と統合の再設定が必要になります。
テーブル1とテーブル2に設定していただくディメンションは以下となります。
・budget_name
・advertiser_name
・account_name
・campaign_name
・adgroup_name
・date
右側のテーブル2に設定していただく指標は、カスタムクエリ入力時に指定したコンバージョン名を追加してください。
テーブルの間にある「統合の設定」をクリックし、統合の再設定を行います。
キャプチャのように、各テーブルで選択されているディメンションを全て選択し、統合を行ってください。
統合の再設定が完了すると、各テーブルのディメンションの右側にリンクされているアイコンが表示されます。
KW_CVの場合
データをサンプルデータから切り替えると以下のように赤く「指標がありません」と表示されますので、こちらの修正と統合の再設定が必要になります。
テーブル1とテーブル2に設定していただくディメンションは以下となります。
※「quality_score」のみテーブル1だけの設定となります
・budget_name
・advertiser_name
・account_name
・campaign_name
・date
・keyword
・match_type
・media
・adgroup_name
・quality_score
右側のテーブル2に設定していただく指標は、カスタムクエリ入力時に指定したコンバージョン名を追加してください。
テーブルの間にある「統合の設定」をクリックし、統合の再設定を行います。
キャプチャのように、各テーブルで選択されている共通のディメンションを全て選択し、統合を行ってください。
※ 「quality_score」のみ選択は不要です
統合の再設定が完了すると、各テーブルのディメンションの右側にリンクされているアイコンが表示されます。
各ページの表にあるコンバージョン系指標の修正
データソースを切り替えていただいた後、各ページの表に設定されているコンバージョン系指標のエラーが発生するため修正を行う必要がございます。
「獲得件数」の修正
コンバージョン別のデータが反映されている表の「獲得件数」は、設定されているコンバージョン別数値の合計数を反映する様になっています。
- 修正する表を選択し、指標「獲得件数」の左側にある「AUT」ボタンをクリックして編集画面を開きます。
SUM(資料請求)+SUM(来場予約)+SUM(申込み完了)+SUM(問い合わせ)
のように「SUM()」の中に設定したコンバージョンアクション名を入力して合計値が反映されるように修正を行い、「適用」ボタンをクリックします。
「獲得率」の修正
コンバージョン別のデータが反映されている表の「獲得率」は、設定されているコンバージョン別数値の合計数をクリック数で割った値を反映する様になっています。
- 修正する表を選択し、指標「獲得率」の左側にある「AUT」ボタンをクリックして編集画面を開きます。
(SUM(資料請求)+SUM(来場予約)+SUM(申込み完了)+SUM(問い合わせ)) / SUM(clicks)
のように「SUM()」の中に設定したコンバージョンアクション名を入力して合計値が反映されるように修正を行い、「適用」ボタンをクリックします。
「獲得単価」の修正
コンバージョン別のデータが反映されている表の「獲得単価」は、利用金額を設定されているコンバージョン別数値の合計数で割った値を反映する様になっています。
- 修正する表を選択し、指標「獲得単価」の左側にある「AUT」ボタンをクリックして編集画面を開きます。
SUM(cost_fee) / (SUM(資料請求)+SUM(来場予約)+SUM(申込み完了)+SUM(問い合わせ))
のように「SUM()」の中に設定したコンバージョンアクション名を入力して合計値が反映されるように修正を行い、「適用」ボタンをクリックします。
コンバージョンアクションの修正
「指標が無効です」とエラーが出ている指標はコンバージョン別の値を設定していた箇所になるので、カスタムクエリで入力して設定していただいた「資料請求」等の指標に差し替えを行ってください。