※ 本ヘルプ記事は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. データポータル で日付の絞り込みが効かない

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

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

回答が見つかりましたか?