Tips
      ホームに戻る
      1. Databeat ヘルプセンター
      2. Looker Studio
      3. 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」と「Purchase」いうカラム名に各サービスアカウントの下記コンバージョンポイントをまとめる場合

      • Google 広告
        - SignUps：コンバージョンアクション名「登録」
        - Purchase：コンバージョンアクション名「購入」

      • Yahoo!広告 検索広告
        - SignUps：コンバージョンアクション名「会員登録」
        - Purchase：コンバージョンアクション名「購入」

      • Yahoo!広告 ディスプレイ広告
        - SignUps：コンバージョンアクション名「会員登録完了」
        - Purchase：コンバージョンアクション名「購入」

      • Twitter広告
        - SignUps：コンバージョンアクション名「新規登録」
        - Purchase：コンバージョンアクション名「購入」

      • Facebook広告
        - SignUps：カスタムコンバージョン名「会員登録（ConversionAction1）」
        - Purchase：カスタムコンバージョン名「購入（ConversionAction11）」

      • LINE広告

        - SignUps：カスタムコンバージョン名「会員登録（ConversionAction1）」
        - Purchase：カスタムコンバージョン名「購入（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.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

      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,
      SUM(conversion_data.Purchase) as Purchase
      // ここまで


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


      // 以下追加
      LEFT JOIN
      (
      SELECT
      Date,
      CampaignId,
      CampaignName,
      Device,
      AdNetworkType1,
      AdNetworkType2,
      SUM(CASE
      WHEN conversion_data.ConversionTypeName = '登録' THEN conversion_data.Conversions
      ELSE 0 
      END) as SignUps,
      SUM(CASE
      WHEN conversion_data.ConversionTypeName = '購入' THEN conversion_data.Conversions
      ELSE 0 
      END) as Purchase
      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 = '会員登録'
         THEN conversion_data.Conversions 
      ELSE 0
      　　END) as SignUps,
      SUM(CASE
      WHEN conversion_data.ConversionTypeName = '購入' 
      　　THEN conversion_data.Conversions 
      ELSE 0
      　　END) as Purchase
      SUM(CASE
      WHEN conversion_data.ConversionTypeName = '会員登録完了' 
      　　THEN conversion_data.Conversions 
      ELSE 0
      　　END) as SignUps,
      SUM(CASE
      WHEN conversion_data.ConversionTypeName = '購入' 
      　　THEN conversion_data.Conversions 
      ELSE 0
      　　END) as Purchase

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

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

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

      3. SignUpsとPurchaseカラムに入れたいカスタムコンバージョンを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) as SignUps,
      SUM(ConversionActions11) as Purchase
      // ここまで


      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とPurchaseカラムに入れたいカスタムコンバージョンを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) as SignUps,
      SUM(ConversionPurchasesMetric) as Purchase
      // ここまで

      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とPurchaseカラムに入れたいカスタムコンバージョンを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) as SignUps,
      SUM(ConversionActions11) as Purchase
      // ここまで


      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とPurchaseカラムに入れる指標でSUMしてください。

      • 例1：SignUpsカラムにコンバージョン数、Purchaseカラムは0で出力したい場合
        SignUpsカラムはConversionsでSUMして、Purchaseカラムは0でSUMしてください。

      • 例2：SignUps、Purchaseカラムともに0で出力したい場合
        SignUps、Purchaseカラムともに0で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,
      SUM(0) as Purchase
      // ここまで

      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文による日付の絞り込みがLooker Studioと連動する書き方に変わっていない可能性がありますので、日付の絞り込みを行っているWHERE文が下記になっているかご確認ください。