ホームに戻る

Databeat ヘルプセンター

Looker Studio

Tips

Looker Studioで特定のコンバージョンポイントをコンバージョンとして出力する方法

Looker Studioで特定のコンバージョンポイントをコンバージョンとして出力するためのSQLの説明です。

※ 本ヘルプ記事はSQLの作成（書き換え）が伴います。

※ SQLの書き方に関するお問い合わせは、カスタマーサポートで対応することができませんのでご了承ください。

※ SQLの作成（書き換え）が難しい場合、別途お見積もりの上、有償での対応も承っておりますのでカスタマーサポートまでお問い合わせください。

※ 各テーブル（各レポートタイプ）で定義されていない指標を使うことはできません。

※ プリセットのテーブル定義「データポータル_キャンペーン」を利用します。

手順

1. プリセットのテーブル定義「データポータル_キャンペーン」からデータベースを作成してください。

2. ［ 1 ］で作ったデータベースのSQLをコピーしてください。

3. ［ 2 ］でコピーしたSQLが参照しているテーブル名（output_feed_table_<半角英数字>）を確認してください。

4. BigQueryの管理画面から［ 3 ］のテーブル名を検索して、テーブルを構成しているSQLをコピーしてください。

5. ［ 4 ］でコピーしたSQLを下記ルールで書き換えてください。

※ 「クエリを新規作成」からBigQueryの管理画面内でSQLの書き換えが可能です。BigQueryの管理画面で書き換えることで、修正箇所がエラーとして通知されますのでおすすめです。

SQLの書き換え

以下、［ 4 ］でコピーしたSQLが下記の場合を例に説明します。

「SignUps」というカラム名に各サービスアカウントの下記コンバージョンアクションをまとめる場合

Google 広告のコンバージョンアクション名「登録」と「購入」
Yahoo!広告検索広告のコンバージョンアクション名「会員登録」と「有料会員登録」
Yahoo!広告ディスプレイ広告のコンバージョンアクション名「会員登録完了」と「課金」
Twitter広告のコンバージョンアクション「新規登録」と「購入」
Facebook広告のコンバージョンアクション「会員登録（ConversionAction1）」と「有料登録（ConversionAction11）」
LINE広告のコンバージョンアクション「会員登録（ConversionAction1）」と「有料登録（ConversionAction11）」

※ SQL内の

「プロジェクトID」：Google Cloud PlatformのプロジェクトIDです。

「リレーションID_A」

「リレーションID_B」

「リレーションID_C」

「リレーションID_D」

「リレーションID_E」

「媒体アカウントID_A」

「媒体アカウントID_B」

「媒体アカウントID_C」

「媒体アカウントID_D」

「媒体アカウントID_E」

はそれぞれ実際の値と読み替えてください。

WITH CampaignSummary AS (
SELECT
 *
FROM `プロジェクトID`.databeat.campaign_summary
WHERE (PromotionAccountRelationId IN (
'リレーションID_A',
'リレーションID_B',
'リレーションID_C',
'リレーションID_D',
'リレーションID_E') AND PARSE_DATE('%Y%m%d', '20000101') <= DATE(Date_) AND DATE(Date_) <= DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY))
)

SELECT
 CampaignSummary.Currency,CampaignSummary.CurrencyRoundType,CampaignSummary.CurrencyPrecision,CampaignSummary.AvgRoundType,CampaignSummary.AvgPrecision,CampaignSummary.PercentageRoundType,CampaignSummary.PercentagePrecision,SAFE_CAST(SUM(Impressions) as INT64) as Impressions,SAFE_CAST(SUM(Clicks) as INT64) as Clicks,SAFE_CAST(SUM(Cost*FeeCoefficient) as FLOAT64) as CostIncludingFees,SUM(AllConversions) as AllConversions,SAFE_CAST(SUM(Conversions) as FLOAT64) as Conversions,SAFE_CAST(SUM(Cost) as FLOAT64) as Cost,SAFE_CAST(SUM(VideoViews) as INT64) as VideoViews,SAFE_CAST(SUM(ConversionValue) as FLOAT64) as ConversionValue,SUM(AllConversionValue) as AllConversionValue,CampaignSummary.PromotionId,ARRAY_AGG(PromotionName ORDER BY Date_ DESC)[OFFSET(0)] as PromotionName,ARRAY_AGG(ServiceNameJA ORDER BY Date_ DESC)[OFFSET(0)] as ServiceNameJA,CampaignSummary.AccountId,CampaignSummary.PromotionAccountRelationId,ARRAY_AGG(AccountName ORDER BY Date_ DESC)[OFFSET(0)] as AccountName,CampaignSummary.CampaignAdvertisingChannelType,CampaignSummary.CampaignId_,ARRAY_AGG(CampaignName ORDER BY Date DESC)[OFFSET(0)] as CampaignName,Date,DayOfWeekJA,CampaignSummary.Month_,CampaignSummary.ServiceId
FROM
 `プロジェクトID`.google_ads_媒体アカウントID_A.campaign AS data
RIGHT JOIN
 CampaignSummary
ON
 data.Date = DATE(CampaignSummary.Date_) AND
 data.CampaignId = CampaignSummary.CampaignId_ AND
 CampaignSummary.PromotionAccountRelationId = 'リレーションID_A'
WHERE
 (PARSE_DATE('%Y%m%d', '20000101') <= Date AND Date <= DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY))
GROUP BY
 CampaignSummary.Currency,CampaignSummary.CurrencyRoundType,CampaignSummary.CurrencyPrecision,CampaignSummary.AvgRoundType,CampaignSummary.AvgPrecision,CampaignSummary.PercentageRoundType,CampaignSummary.PercentagePrecision,CampaignSummary.PromotionId,CampaignSummary.AccountId,CampaignSummary.PromotionAccountRelationId,CampaignSummary.CampaignAdvertisingChannelType,CampaignSummary.CampaignId_,Date,DayOfWeekJA,CampaignSummary.Month_,CampaignSummary.ServiceId



UNION ALL
SELECT
 CampaignSummary.Currency,CampaignSummary.CurrencyRoundType,CampaignSummary.CurrencyPrecision,CampaignSummary.AvgRoundType,CampaignSummary.AvgPrecision,CampaignSummary.PercentageRoundType,CampaignSummary.PercentagePrecision,SAFE_CAST(SUM(Impressions) as INT64) as Impressions,SAFE_CAST(SUM(Clicks) as INT64) as Clicks,SAFE_CAST(SUM(Cost*FeeCoefficient) as FLOAT64) as CostIncludingFees,SAFE_CAST(SUM(AllConversions) as FLOAT64 ) as AllConversions,SAFE_CAST(SUM(Conversions) as FLOAT64) as Conversions,SAFE_CAST(SUM(Cost) as FLOAT64) as Cost,SAFE_CAST(SUM(VideoViews) as INT64) as VideoViews,SUM(IFNULL(ConversionValue , 0)) as ConversionValue,SUM(IFNULL(AllConversionValue , 0)) as AllConversionValue,CampaignSummary.PromotionId,ARRAY_AGG(PromotionName ORDER BY Date_ DESC)[OFFSET(0)] as PromotionName,ARRAY_AGG(ServiceNameJA ORDER BY Date_ DESC)[OFFSET(0)] as ServiceNameJA,CampaignSummary.AccountId,CampaignSummary.PromotionAccountRelationId,ARRAY_AGG(AccountName ORDER BY Date_ DESC)[OFFSET(0)] as AccountName,CampaignSummary.CampaignAdvertisingChannelType,CampaignSummary.CampaignId_,ARRAY_AGG(CampaignName ORDER BY Date DESC)[OFFSET(0)] as CampaignName,Date,DayOfWeekJA,CampaignSummary.Month_,CampaignSummary.ServiceId
FROM
 `プロジェクトID`.twitter_ads_媒体アカウントID_B.campaign AS data
RIGHT JOIN
 CampaignSummary
ON
 data.Date = DATE(CampaignSummary.Date_) AND
 data.CampaignId = CampaignSummary.CampaignId_ AND
 CampaignSummary.PromotionAccountRelationId = 'リレーションID_B'
WHERE
 (PARSE_DATE('%Y%m%d', '20000101') <= Date AND Date <= DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY))
GROUP BY
 CampaignSummary.Currency,CampaignSummary.CurrencyRoundType,CampaignSummary.CurrencyPrecision,CampaignSummary.AvgRoundType,CampaignSummary.AvgPrecision,CampaignSummary.PercentageRoundType,CampaignSummary.PercentagePrecision,CampaignSummary.PromotionId,CampaignSummary.AccountId,CampaignSummary.PromotionAccountRelationId,CampaignSummary.CampaignAdvertisingChannelType,CampaignSummary.CampaignId_,Date,DayOfWeekJA,CampaignSummary.Month_,CampaignSummary.ServiceId



UNION ALL
SELECT
 CampaignSummary.Currency,CampaignSummary.CurrencyRoundType,CampaignSummary.CurrencyPrecision,CampaignSummary.AvgRoundType,CampaignSummary.AvgPrecision,CampaignSummary.PercentageRoundType,CampaignSummary.PercentagePrecision,SAFE_CAST(SUM(Impressions) as INT64) as Impressions,SAFE_CAST(SUM(Clicks) as INT64) as Clicks,SAFE_CAST(SUM(Cost*FeeCoefficient) as FLOAT64) as CostIncludingFees,SAFE_CAST(SUM(AllConversions) as FLOAT64 ) as AllConversions,SAFE_CAST(SUM(Conversions) as FLOAT64) as Conversions,SAFE_CAST(SUM(Cost) as FLOAT64) as Cost,SAFE_CAST(SUM(VideoViews) as INT64) as VideoViews,SAFE_CAST(SUM(ConversionValue) as FLOAT64) as ConversionValue,SUM(AllConversionValue) as AllConversionValue,CampaignSummary.PromotionId,ARRAY_AGG(PromotionName ORDER BY Date_ DESC)[OFFSET(0)] as PromotionName,ARRAY_AGG(ServiceNameJA ORDER BY Date_ DESC)[OFFSET(0)] as ServiceNameJA,CampaignSummary.AccountId,CampaignSummary.PromotionAccountRelationId,ARRAY_AGG(AccountName ORDER BY Date_ DESC)[OFFSET(0)] as AccountName,CampaignSummary.CampaignAdvertisingChannelType,CampaignSummary.CampaignId_,ARRAY_AGG(CampaignName ORDER BY Date DESC)[OFFSET(0)] as CampaignName,Date,DayOfWeekJA,CampaignSummary.Month_,CampaignSummary.ServiceId
FROM
 `プロジェクトID`.facebook_ads_媒体アカウントID_C.adgroup AS data
RIGHT JOIN
 CampaignSummary
ON
 data.Date = DATE(CampaignSummary.Date_) AND
 data.CampaignId = CampaignSummary.CampaignId_ AND
 CampaignSummary.PromotionAccountRelationId = 'リレーションID_C'
WHERE
 (PARSE_DATE('%Y%m%d', '20000101') <= Date AND Date <= DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY))
GROUP BY
 CampaignSummary.Currency,CampaignSummary.CurrencyRoundType,CampaignSummary.CurrencyPrecision,CampaignSummary.AvgRoundType,CampaignSummary.AvgPrecision,CampaignSummary.PercentageRoundType,CampaignSummary.PercentagePrecision,CampaignSummary.PromotionId,CampaignSummary.AccountId,CampaignSummary.PromotionAccountRelationId,CampaignSummary.CampaignAdvertisingChannelType,CampaignSummary.CampaignId_,Date,DayOfWeekJA,CampaignSummary.Month_,CampaignSummary.ServiceId



UNION ALL
SELECT
 CampaignSummary.Currency,CampaignSummary.CurrencyRoundType,CampaignSummary.CurrencyPrecision,CampaignSummary.AvgRoundType,CampaignSummary.AvgPrecision,CampaignSummary.PercentageRoundType,CampaignSummary.PercentagePrecision,SAFE_CAST(SUM(Impressions) as INT64) as Impressions,SAFE_CAST(SUM(Clicks) as INT64) as Clicks,SAFE_CAST(SUM(Cost*FeeCoefficient) as FLOAT64) as CostIncludingFees,SUM(AllConversions) as AllConversions,SAFE_CAST(SUM(Conversions) as FLOAT64) as Conversions,SAFE_CAST(SUM(Cost) as FLOAT64) as Cost,SUM(IFNULL(VideoViews , 0)) as VideoViews,SAFE_CAST(SUM(ConversionValue) as FLOAT64) as ConversionValue,SUM(AllConversionValue) as AllConversionValue,CampaignSummary.PromotionId,ARRAY_AGG(PromotionName ORDER BY Date_ DESC)[OFFSET(0)] as PromotionName,ARRAY_AGG(ServiceNameJA ORDER BY Date_ DESC)[OFFSET(0)] as ServiceNameJA,CampaignSummary.AccountId,CampaignSummary.PromotionAccountRelationId,ARRAY_AGG(AccountName ORDER BY Date_ DESC)[OFFSET(0)] as AccountName,CampaignSummary.CampaignAdvertisingChannelType,CampaignSummary.CampaignId_,ARRAY_AGG(CampaignName ORDER BY Date DESC)[OFFSET(0)] as CampaignName,Date,DayOfWeekJA,CampaignSummary.Month_,CampaignSummary.ServiceId
FROM
 `プロジェクトID`.yahoo_sponsored_search_媒体アカウントID_D.campaign AS data
RIGHT JOIN
 CampaignSummary
ON
 data.Date = DATE(CampaignSummary.Date_) AND
 data.CampaignId = CampaignSummary.CampaignId_ AND
 CampaignSummary.PromotionAccountRelationId = 'リレーションID_D'
WHERE
 (PARSE_DATE('%Y%m%d', '20000101') <= Date AND Date <= DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY))
GROUP BY
 CampaignSummary.Currency,CampaignSummary.CurrencyRoundType,CampaignSummary.CurrencyPrecision,CampaignSummary.AvgRoundType,CampaignSummary.AvgPrecision,CampaignSummary.PercentageRoundType,CampaignSummary.PercentagePrecision,CampaignSummary.PromotionId,CampaignSummary.AccountId,CampaignSummary.PromotionAccountRelationId,CampaignSummary.CampaignAdvertisingChannelType,CampaignSummary.CampaignId_,Date,DayOfWeekJA,CampaignSummary.Month_,CampaignSummary.ServiceId



UNION ALL
SELECT
 CampaignSummary.Currency,CampaignSummary.CurrencyRoundType,CampaignSummary.CurrencyPrecision,CampaignSummary.AvgRoundType,CampaignSummary.AvgPrecision,CampaignSummary.PercentageRoundType,CampaignSummary.PercentagePrecision,SAFE_CAST(SUM(Impressions) as INT64) as Impressions,SAFE_CAST(SUM(Clicks) as INT64) as Clicks,SAFE_CAST(SUM(Cost*FeeCoefficient) as FLOAT64) as CostIncludingFees,SAFE_CAST(SUM(AllConversions) as FLOAT64 ) as AllConversions,SAFE_CAST(SUM(Conversions) as FLOAT64) as Conversions,SAFE_CAST(SUM(Cost) as FLOAT64) as Cost,SAFE_CAST(SUM(VideoViews) as INT64) as VideoViews,SAFE_CAST(SUM(ConversionValue) as FLOAT64) as ConversionValue,SUM(AllConversionValue) as AllConversionValue,CampaignSummary.PromotionId,ARRAY_AGG(PromotionName ORDER BY Date_ DESC)[OFFSET(0)] as PromotionName,ARRAY_AGG(ServiceNameJA ORDER BY Date_ DESC)[OFFSET(0)] as ServiceNameJA,CampaignSummary.AccountId,CampaignSummary.PromotionAccountRelationId,ARRAY_AGG(AccountName ORDER BY Date_ DESC)[OFFSET(0)] as AccountName,CampaignSummary.CampaignAdvertisingChannelType,CampaignSummary.CampaignId_,ARRAY_AGG(CampaignName ORDER BY Date DESC)[OFFSET(0)] as CampaignName,Date,DayOfWeekJA,CampaignSummary.Month_,CampaignSummary.ServiceId
FROM
 `プロジェクトID`.yahoo_display_network_媒体アカウントID_E.campaign AS data
RIGHT JOIN
 CampaignSummary
ON
 data.Date = DATE(CampaignSummary.Date_) AND
 data.CampaignId = CampaignSummary.CampaignId_ AND
 CampaignSummary.PromotionAccountRelationId = 'リレーションID_E'
WHERE
 (PARSE_DATE('%Y%m%d', '20000101') <= Date AND Date <= DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY))
GROUP BY
 CampaignSummary.Currency,CampaignSummary.CurrencyRoundType,CampaignSummary.CurrencyPrecision,CampaignSummary.AvgRoundType,CampaignSummary.AvgPrecision,CampaignSummary.PercentageRoundType,CampaignSummary.PercentagePrecision,CampaignSummary.PromotionId,CampaignSummary.AccountId,CampaignSummary.PromotionAccountRelationId,CampaignSummary.CampaignAdvertisingChannelType,CampaignSummary.CampaignId_,Date,DayOfWeekJA,CampaignSummary.Month_,CampaignSummary.ServiceId


UNION ALL
SELECT
 CampaignSummary.Currency,CampaignSummary.CurrencyRoundType,CampaignSummary.CurrencyPrecision,CampaignSummary.AvgRoundType,CampaignSummary.AvgPrecision,CampaignSummary.PercentageRoundType,CampaignSummary.PercentagePrecision,SAFE_CAST(SUM(Impressions) as INT64) as Impressions,SAFE_CAST(SUM(Clicks) as INT64) as Clicks,SAFE_CAST(SUM(Cost*FeeCoefficient) as FLOAT64) as CostIncludingFees,SAFE_CAST(SUM(AllConversions) as FLOAT64 ) as AllConversions,SAFE_CAST(SUM(Conversions) as FLOAT64) as Conversions,SAFE_CAST(SUM(Cost) as FLOAT64) as Cost,SAFE_CAST(SUM(VideoViews) as INT64) as VideoViews,SAFE_CAST(SUM(ConversionValue) as FLOAT64) as ConversionValue,SUM(AllConversionValue) as AllConversionValue,CampaignSummary.PromotionId,ARRAY_AGG(PromotionName ORDER BY Date_ DESC)[OFFSET(0)] as PromotionName,ARRAY_AGG(ServiceNameJA ORDER BY Date_ DESC)[OFFSET(0)] as ServiceNameJA,CampaignSummary.AccountId,CampaignSummary.PromotionAccountRelationId,ARRAY_AGG(AccountName ORDER BY Date_ DESC)[OFFSET(0)] as AccountName,CampaignSummary.CampaignAdvertisingChannelType,CampaignSummary.CampaignId_,ARRAY_AGG(CampaignName ORDER BY Date DESC)[OFFSET(0)] as CampaignName,Date,DayOfWeekJA,CampaignSummary.Month_,CampaignSummary.ServiceId
FROM
 `プロジェクトID`.line_ads_媒体アカウントID_F.adgroup AS data
RIGHT JOIN
 CampaignSummary
ON
 data.Date = DATE(CampaignSummary.Date_) AND
 data.CampaignId = CampaignSummary.CampaignId_ AND
 CampaignSummary.PromotionAccountRelationId = 'リレーションID_C'
WHERE
 (PARSE_DATE('%Y%m%d', '20000101') <= Date AND Date <= DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY))
GROUP BY
 CampaignSummary.Currency,CampaignSummary.CurrencyRoundType,CampaignSummary.CurrencyPrecision,CampaignSummary.AvgRoundType,CampaignSummary.AvgPrecision,CampaignSummary.PercentageRoundType,CampaignSummary.PercentagePrecision,CampaignSummary.PromotionId,CampaignSummary.AccountId,CampaignSummary.PromotionAccountRelationId,CampaignSummary.CampaignAdvertisingChannelType,CampaignSummary.CampaignId_,Date,DayOfWeekJA,CampaignSummary.Month_,CampaignSummary.ServiceId

Google 広告アカウントに対しての書き換え

1. UNION ALL を区切りとしてGoogle 広告のCampaignテーブルからSELECTしている箇所を抜粋して修正してください。

2. campaign_conversionテーブルをjoinし、重複するカラム名を修正してください。

3. case文を追加してください。

SELECT
 CampaignSummary.Currency,
 CampaignSummary.CurrencyRoundType,
 CampaignSummary.CurrencyPrecision,
 CampaignSummary.AvgRoundType,
 CampaignSummary.AvgPrecision,
 CampaignSummary.PercentageRoundType,
 CampaignSummary.PercentagePrecision,
 SAFE_CAST(SUM(Impressions) as INT64) as Impressions,
 SAFE_CAST(SUM(Clicks) as INT64) as Clicks,
 SAFE_CAST(SUM(Cost*FeeCoefficient) as FLOAT64) as CostIncludingFees,

 // 変更
 // SUM(AllConversions) as AllConversions,
 SUM(data.AllConversions) as AllConversions,

 // 変更
 // SAFE_CAST(SUM(Conversions) as FLOAT64) as Conversions,
 SAFE_CAST(SUM(data.Conversions) as FLOAT64) as Conversions,

 SAFE_CAST(SUM(Cost) as FLOAT64) as Cost,
 SAFE_CAST(SUM(VideoViews) as INT64) as VideoViews,

 // 変更
 // SAFE_CAST(SUM(ConversionValue) as FLOAT64) as ConversionValue,
 SAFE_CAST(SUM(data.ConversionValue) as FLOAT64) as ConversionValue,

 // 変更
 // SUM(AllConversionValue) as AllConversionValue,
 SUM(data.AllConversionValue) as AllConversionValue,


 CampaignSummary.PromotionId,
 ARRAY_AGG(PromotionName ORDER BY Date_ DESC)[OFFSET(0)] as  PromotionName,
 ARRAY_AGG(ServiceNameJA ORDER BY Date_ DESC)[OFFSET(0)] as ServiceNameJA,
 CampaignSummary.AccountId,
 CampaignSummary.PromotionAccountRelationId,
 ARRAY_AGG(AccountName ORDER BY Date_ DESC)[OFFSET(0)] as AccountName,
 CampaignSummary.CampaignAdvertisingChannelType,
 CampaignSummary.CampaignId_,

 // 変更
 // ARRAY_AGG(CampaignName ORDER BY Date DESC)[OFFSET(0)] as CampaignName,
 ARRAY_AGG(data.CampaignName ORDER BY data.Date DESC)[OFFSET(0)] as CampaignName,

 // 変更
 // Date,
 data.Date,

 // 変更
 // DayOfWeekJA,
 data.DayOfWeekJA,

// 以下追加
SUM(conversion_data.SignUps) as SignUps
// ここまで


FROM
 `プロジェクトID`.google_ads_媒体アカウントID_A.campaign AS data


// 以下追加
LEFT JOIN
  (
    SELECT
      Date,
      CampaignId,
      CampaignName,
      Device,
      AdNetworkType1,
      AdNetworkType2,
      SUM(CASE
        WHEN conversion_data.ConversionTypeName = '登録' OR conversion_data.ConversionTypeName = '購入' THEN conversion_data.Conversions
        ELSE 0 
        END) as SignUps
    FROM
      `プロジェクトID`.google_ads_媒体アカウントID_A.campaign_conversion AS conversion_data
    WHERE
      conversion_data.Date BETWEEN PARSE_DATE('%Y%m%d', @DS_START_DATE)
      AND PARSE_DATE('%Y%m%d', @DS_END_DATE)
    GROUP BY
      Date,
      CampaignId,
      CampaignName,
      Device,
      AdNetworkType1,
      AdNetworkType2
    ORDER BY Date) as conversion_data
ON
  DATA.Date = conversion_data.Date
  AND DATA.CampaignId = conversion_data.CampaignId
  AND DATA.Device = conversion_data.Device
  AND　IFNULL( DATA.AdNetworkType1,'`') = 
IFNULL( conversion_data.AdNetworkType1,'`')
  AND IFNULL(DATA.AdNetworkType2, '`') = IFNULL(conversion_data.AdNetworkType2, '`')
// ここまで

RIGHT JOIN
 CampaignSummary
ON
 data.Date = DATE(CampaignSummary.Date_) AND
 data.CampaignId = CampaignSummary.CampaignId_ AND
 CampaignSummary.PromotionAccountRelationId = 'リレーションID_A'
WHERE
 // 変更
 // (PARSE_DATE('%Y%m%d', '20000101') <= Date AND Date <= DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY))
 (PARSE_DATE('%Y%m%d', @DS_START_DATE) <= data.Date AND data.Date <= PARSE_DATE('%Y%m%d', @DS_END_DATE))


GROUP BY
 CampaignSummary.Currency,CampaignSummary.CurrencyRoundType,CampaignSummary.CurrencyPrecision,CampaignSummary.AvgRoundType,CampaignSummary.AvgPrecision,CampaignSummary.PercentageRoundType,CampaignSummary.PercentagePrecision,CampaignSummary.PromotionId,CampaignSummary.AccountId,CampaignSummary.PromotionAccountRelationId,CampaignSummary.CampaignAdvertisingChannelType,CampaignSummary.CampaignId_,Date,DayOfWeekJA,CampaignSummary.Month_,CampaignSummary.ServiceId

Yahoo!広告アカウントに対しての書き換え

上記「Google広告アカウントに対しての書き換え」と同様ですので、google_ads_をyahoo_sponsored_search_やyahoo_display_network_と読み替えて書き換えてください。

それぞれcase文は下記となります。

SUM(CASE
  WHEN conversion_data.ConversionTypeName = '会員登録' OR conversion_data.ConversionTypeName = '有料会員登録' 
　　THEN conversion_data.Conversions 
  ELSE 0
　　END) as SignUps

SUM(CASE
  WHEN conversion_data.ConversionTypeName = '会員登録完了' OR conversion_data.ConversionTypeName = '課金' 
　　THEN conversion_data.Conversions 
  ELSE 0
　　END) as SignUps

Facebook広告アカウントに対しての書き換え

1. UNION ALL を区切りとしてFacebook広告のCampaignテーブルからSELECTしている箇所を抜粋して修正してください。

2. 広告グループテーブルを参照するように書き換えてください。

3. SignUpsカラムに入れたいカスタムコンバージョンをSUMしてください。

SELECT
 CampaignSummary.Currency,
 CampaignSummary.CurrencyRoundType,
 CampaignSummary.CurrencyPrecision,
 CampaignSummary.AvgRoundType,
 CampaignSummary.AvgPrecision,
 CampaignSummary.PercentageRoundType,
 CampaignSummary.PercentagePrecision,
 SAFE_CAST(SUM(Impressions) as INT64) as Impressions,
 SAFE_CAST(SUM(Clicks) as INT64) as Clicks,
 SAFE_CAST(SUM(Cost*FeeCoefficient) as FLOAT64) as CostIncludingFees,
 SAFE_CAST(SUM(AllConversions) as FLOAT64 ) as AllConversions,
 SAFE_CAST(SUM(Conversions) as FLOAT64) as Conversions,
 SAFE_CAST(SUM(Cost) as FLOAT64) as Cost,
 SAFE_CAST(SUM(VideoViews) as INT64) as VideoViews,
 SAFE_CAST(SUM(ConversionValue) as FLOAT64) as ConversionValue,
 SUM(AllConversionValue) as AllConversionValue,
 CampaignSummary.PromotionId,
 ARRAY_AGG(PromotionName ORDER BY Date_ DESC)[OFFSET(0)] as PromotionName,
 ARRAY_AGG(ServiceNameJA ORDER BY Date_ DESC)[OFFSET(0)] as ServiceNameJA,
 CampaignSummary.AccountId,
 CampaignSummary.PromotionAccountRelationId,
 ARRAY_AGG(AccountName ORDER BY Date_ DESC)[OFFSET(0)] as AccountName,
 CampaignSummary.CampaignAdvertisingChannelType,
 CampaignSummary.CampaignId_,
 ARRAY_AGG(CampaignName ORDER BY Date DESC)[OFFSET(0)] as CampaignName,
 Date,
 DayOfWeekJA,
 CampaignSummary.Month_,
 CampaignSummary.ServiceId

// 以下追加
,
SUM(ConversionActions1+ConversionActions11) as SignUps
// ここまで


FROM
 // 変更
 // `プロジェクトID`.facebook_ads_媒体アカウントID_C.campaign AS data
 `プロジェクトID`.facebook_ads_媒体アカウントID_C.adgroup AS data
RIGHT JOIN
    CampaignSummary
ON
    data.Date = DATE(CampaignSummary.Date_) AND
    data.CampaignId = CampaignSummary.CampaignId_ AND
    CampaignSummary.PromotionAccountRelationId = '885b8548-d15e-413f-a92a-c556c3e585b7'
WHERE
  // 変更
  // (PARSE_DATE('%Y%m%d', '20000101') <= Date AND Date <= DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY))
  (PARSE_DATE('%Y%m%d', @DS_START_DATE) <= data.Date AND data.Date <= PARSE_DATE('%Y%m%d', @DS_END_DATE))
GROUP BY
    CampaignSummary.Currency,CampaignSummary.CurrencyRoundType,CampaignSummary.CurrencyPrecision,CampaignSummary.AvgRoundType,CampaignSummary.AvgPrecision,CampaignSummary.PercentageRoundType,CampaignSummary.PercentagePrecision,CampaignSummary.PromotionId,CampaignSummary.AccountId,CampaignSummary.PromotionAccountRelationId,CampaignSummary.CampaignAdvertisingChannelType,CampaignSummary.CampaignId_,Date,DayOfWeekJA,CampaignSummary.Month_,CampaignSummary.ServiceId

Twitter広告アカウントに対しての書き換え

1. UNION ALL を区切りとしてTwitter広告のCampaignテーブルからSELECTしている箇所を抜粋して修正してください。

2. SignUpsカラムに入れたいカスタムコンバージョンをSUMしてください。

SELECT
 CampaignSummary.Currency,
 CampaignSummary.CurrencyRoundType,
 CampaignSummary.CurrencyPrecision,
 CampaignSummary.AvgRoundType,
 CampaignSummary.AvgPrecision,
 CampaignSummary.PercentageRoundType,
 CampaignSummary.PercentagePrecision,
 SAFE_CAST(SUM(Impressions) as INT64) as Impressions,
 SAFE_CAST(SUM(Clicks) as INT64) as Clicks,
 SAFE_CAST(SUM(Cost*FeeCoefficient) as FLOAT64) as CostIncludingFees,
 SAFE_CAST(SUM(AllConversions) as FLOAT64 ) as AllConversions,
 SAFE_CAST(SUM(Conversions) as FLOAT64) as Conversions,
 SAFE_CAST(SUM(Cost) as FLOAT64) as Cost,
 SAFE_CAST(SUM(VideoViews) as INT64) as VideoViews,
 SUM(IFNULL(ConversionValue , 0)) as ConversionValue,
 SUM(IFNULL(AllConversionValue , 0)) as AllConversionValue,
 CampaignSummary.PromotionId,
 ARRAY_AGG(PromotionName ORDER BY Date_ DESC)[OFFSET(0)] as PromotionName,
 ARRAY_AGG(ServiceNameJA ORDER BY Date_ DESC)[OFFSET(0)] as ServiceNameJA,
 CampaignSummary.AccountId,
 CampaignSummary.PromotionAccountRelationId,
 ARRAY_AGG(AccountName ORDER BY Date_ DESC)[OFFSET(0)] as AccountName,
 CampaignSummary.CampaignAdvertisingChannelType,
 CampaignSummary.CampaignId_,
 ARRAY_AGG(CampaignName ORDER BY Date DESC)[OFFSET(0)] as CampaignName,
 Date,
 DayOfWeekJA,
 CampaignSummary.Month_,
 CampaignSummary.ServiceId


// 以下追加
,
SUM(ConversionActions+ConversionPurchasesMetric) as SignUps
// ここまで

FROM
 `プロジェクトID`.twitter_ads_媒体アカウントID_B.campaign AS data
RIGHT JOIN
 CampaignSummary
ON
 data.Date = DATE(CampaignSummary.Date_) AND
 data.CampaignId = CampaignSummary.CampaignId_ AND
 CampaignSummary.PromotionAccountRelationId = 'リレーションID_B'
WHERE
  // 変更 
  // (PARSE_DATE('%Y%m%d', '20000101') <= Date AND Date <= DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY))
  (PARSE_DATE('%Y%m%d', @DS_START_DATE) <= data.Date AND data.Date <= PARSE_DATE('%Y%m%d', @DS_END_DATE))

GROUP BY
 CampaignSummary.Currency,CampaignSummary.CurrencyRoundType,CampaignSummary.CurrencyPrecision,CampaignSummary.AvgRoundType,CampaignSummary.AvgPrecision,CampaignSummary.PercentageRoundType,CampaignSummary.PercentagePrecision,CampaignSummary.PromotionId,CampaignSummary.AccountId,CampaignSummary.PromotionAccountRelationId,CampaignSummary.CampaignAdvertisingChannelType,CampaignSummary.CampaignId_,Date,DayOfWeekJA,CampaignSummary.Month_,CampaignSummary.ServiceId

LINE広告アカウントに対しての書き換え

1. UNION ALL を区切りとしてLINE広告のCampaignテーブルからSELECTしている箇所を抜粋して修正してください。

2. SignUpsカラムに入れたいカスタムコンバージョンをSUMしてください。

SELECT
 CampaignSummary.Currency,
 CampaignSummary.CurrencyRoundType,
 CampaignSummary.CurrencyPrecision,
 CampaignSummary.AvgRoundType,
 CampaignSummary.AvgPrecision,
 CampaignSummary.PercentageRoundType,
 CampaignSummary.PercentagePrecision,
 SAFE_CAST(SUM(Impressions) as INT64) as Impressions,
 SAFE_CAST(SUM(Clicks) as INT64) as Clicks,
 SAFE_CAST(SUM(Cost*FeeCoefficient) as FLOAT64) as CostIncludingFees,
 SAFE_CAST(SUM(AllConversions) as FLOAT64 ) as AllConversions,
 SAFE_CAST(SUM(Conversions) as FLOAT64) as Conversions,
 SAFE_CAST(SUM(Cost) as FLOAT64) as Cost,
 SAFE_CAST(SUM(VideoViews) as INT64) as VideoViews,
 SAFE_CAST(SUM(ConversionValue) as FLOAT64) as ConversionValue,
 SUM(AllConversionValue) as AllConversionValue,
 CampaignSummary.PromotionId,
 ARRAY_AGG(PromotionName ORDER BY Date_ DESC)[OFFSET(0)] as PromotionName,
 ARRAY_AGG(ServiceNameJA ORDER BY Date_ DESC)[OFFSET(0)] as ServiceNameJA,
 CampaignSummary.AccountId,
 CampaignSummary.PromotionAccountRelationId,
 ARRAY_AGG(AccountName ORDER BY Date_ DESC)[OFFSET(0)] as AccountName,
 CampaignSummary.CampaignAdvertisingChannelType,
 CampaignSummary.CampaignId_,
 ARRAY_AGG(CampaignName ORDER BY Date DESC)[OFFSET(0)] as CampaignName,
 Date,
 DayOfWeekJA,
 CampaignSummary.Month_,
 CampaignSummary.ServiceId

// 以下追加
,
SUM(ConversionActions1+ConversionActions11) as SignUps
// ここまで


FROM
 `プロジェクトID`.line_ads_媒体アカウントID_F.campaign AS data

RIGHT JOIN
    CampaignSummary
ON
    data.Date = DATE(CampaignSummary.Date_) AND
    data.CampaignId = CampaignSummary.CampaignId_ AND
    CampaignSummary.PromotionAccountRelationId = '885b8548-d15e-413f-a92a-c556c3e585b7'
WHERE
  // 変更
  // (PARSE_DATE('%Y%m%d', '20000101') <= Date AND Date <= DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY))
  (PARSE_DATE('%Y%m%d', @DS_START_DATE) <= data.Date AND data.Date <= PARSE_DATE('%Y%m%d', @DS_END_DATE))
GROUP BY
    CampaignSummary.Currency,CampaignSummary.CurrencyRoundType,CampaignSummary.CurrencyPrecision,CampaignSummary.AvgRoundType,CampaignSummary.AvgPrecision,CampaignSummary.PercentageRoundType,CampaignSummary.PercentagePrecision,CampaignSummary.PromotionId,CampaignSummary.AccountId,CampaignSummary.PromotionAccountRelationId,CampaignSummary.CampaignAdvertisingChannelType,CampaignSummary.CampaignId_,Date,DayOfWeekJA,CampaignSummary.Month_,CampaignSummary.ServiceId

その他のサービスアカウントに対しての書き換え

1. UNION ALL を区切りとして該当のアカウントのCampaignテーブルからSELECTしている箇所を抜粋して修正してください。

2. SignUpsカラムにConversionsをSUMしてください。

SELECT
 CampaignSummary.Currency,
 CampaignSummary.CurrencyRoundType,
 CampaignSummary.CurrencyPrecision,
 CampaignSummary.AvgRoundType,
 CampaignSummary.AvgPrecision,
 CampaignSummary.PercentageRoundType,
 CampaignSummary.PercentagePrecision,
 SAFE_CAST(SUM(Impressions) as INT64) as Impressions,
 SAFE_CAST(SUM(Clicks) as INT64) as Clicks,
 SAFE_CAST(SUM(Cost*FeeCoefficient) as FLOAT64) as CostIncludingFees,
 SAFE_CAST(SUM(AllConversions) as FLOAT64 ) as AllConversions,
 SAFE_CAST(SUM(Conversions) as FLOAT64) as Conversions,
 SAFE_CAST(SUM(Cost) as FLOAT64) as Cost,
 SAFE_CAST(SUM(VideoViews) as INT64) as VideoViews,
 SUM(IFNULL(ConversionValue , 0)) as ConversionValue,
 SUM(IFNULL(AllConversionValue , 0)) as AllConversionValue,
 CampaignSummary.PromotionId,
 ARRAY_AGG(PromotionName ORDER BY Date_ DESC)[OFFSET(0)] as PromotionName,
 ARRAY_AGG(ServiceNameJA ORDER BY Date_ DESC)[OFFSET(0)] as ServiceNameJA,
 CampaignSummary.AccountId,
 CampaignSummary.PromotionAccountRelationId,
 ARRAY_AGG(AccountName ORDER BY Date_ DESC)[OFFSET(0)] as AccountName,
 CampaignSummary.CampaignAdvertisingChannelType,
 CampaignSummary.CampaignId_,
 ARRAY_AGG(CampaignName ORDER BY Date DESC)[OFFSET(0)] as CampaignName,
 Date,
 DayOfWeekJA,
 CampaignSummary.Month_,
 CampaignSummary.ServiceId


// 以下追加
,
SUM(Conversions) as SignUps
// ここまで

FROM
 `プロジェクトID`.媒体名_媒体アカウントID.campaign AS data
RIGHT JOIN
 CampaignSummary
ON
 data.Date = DATE(CampaignSummary.Date_) AND
 data.CampaignId = CampaignSummary.CampaignId_ AND
 CampaignSummary.PromotionAccountRelationId = 'リレーションID_B'
WHERE
  // 変更 
  // (PARSE_DATE('%Y%m%d', '20000101') <= Date AND Date <= DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY))
  (PARSE_DATE('%Y%m%d', @DS_START_DATE) <= data.Date AND data.Date <= PARSE_DATE('%Y%m%d', @DS_END_DATE))

GROUP BY
 CampaignSummary.Currency,CampaignSummary.CurrencyRoundType,CampaignSummary.CurrencyPrecision,CampaignSummary.AvgRoundType,CampaignSummary.AvgPrecision,CampaignSummary.PercentageRoundType,CampaignSummary.PercentagePrecision,CampaignSummary.PromotionId,CampaignSummary.AccountId,CampaignSummary.PromotionAccountRelationId,CampaignSummary.CampaignAdvertisingChannelType,CampaignSummary.CampaignId_,Date,DayOfWeekJA,CampaignSummary.Month_,CampaignSummary.ServiceId

エラーとなった場合

修正が漏れている等は、BigQueryの管理画面でクエリを実行しようとするとエラーとなって案内されるますので、エラーに従い修正箇所を確認してください。

その他アカウントも同様の書き方で対応可能となっておりますのでぜひチャレンジください。

Q&A

Q1. Looker Studioで日付の絞り込みが効かない

A1. WHERE文による日付の絞り込みがデータポータルと連動する書き方に変わっていない可能性がありますので、日付の絞り込みを行っているWHERE文が下記になっているかご確認ください。

(PARSE_DATE('%Y%m%d', @DS_START_DATE) <= data.Date 
AND data.Date <= PARSE_DATE('%Y%m%d', @DS_END_DATE))