活用事例
      ホームに戻る
      1. Databeat ヘルプセンター
      2. 活用事例

      SQLサンプル集

      Databeatに登録されている全てのプロモーションやアカウント、アカウント内のキャンペーン別のデータを一括で出力することのできるSQLサンプルです。

      下記ヘルプ記事の方法でご利用いただくことが可能です。

      Google スプレッドシートにデータを出力する方法

      Databeat Exploreで発行したSQLの設定方法

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

      1. 全てのプロモーションを一括で出力する（アカウント別）
      2. 全てのプロモーションを一括で出力する（アカウント&日別）
      3. 全てのプロモーションを一括で出力する（キャンペーン別）
      4. 全てのプロモーションを一括で出力する（キャンペーン&日別）
      5. 全てのプロモーションを一括で出力する（アカウント別）
      6. 全てのプロモーションを一括で出力する（アカウント&日別）
      7. 全てのプロモーションを一括で出力する（キャンペーン別）
      8. 全てのプロモーションを一括で出力する（キャンペーン&日別）

      1〜4はDatabeatに登録されているプロモーションのデータが全て出力されます。

      5〜8はDatabeatに登録されている全てのプロモーションから、アカウントやキャンペーンの重複を排除してデータが出力されます。

      ・全て、「FROM `databeat-client-014`」の箇所を自社のプロジェクトIDに変更することでそのままご利用いただけます。

      ・設定した出力期間に出稿実績のあるもののみが出力対象です

      SQLの編集を行われる場合、そのままコピペすると見づらくなっておりますので、BigQueryに一度貼り付けていただき、「展開」メニューから「クエリを書式設定」していただくと編集しやすくなります。

      BigQueryについて

      ①全てのプロモーションを一括で出力する（アカウント別）

      下記ヘルプ記事で使用されているものと同一になりますので引用になりますので、合わせてご確認ください。

      全てのプロモーションをまとめて数値管理する方法 - Google スプレッドシート編（Connected Sheets）

      SELECT Month_, ARRAY_AGG(Fee ORDER BY Date_ DESC)[OFFSET(0)] as Fee, ARRAY_AGG(FeeType ORDER BY Date_ DESC)[OFFSET(0)] as FeeType, ARRAY_AGG(FeeFormula ORDER BY Date_ DESC)[OFFSET(0)] as FeeFormula, ARRAY_AGG(FeeCoefficient ORDER BY Date_ DESC)[OFFSET(0)] as FeeCoefficient, ARRAY_AGG(PromotionName ORDER BY Date_ DESC)[OFFSET(0)] as PromotionName, ARRAY_AGG(AccountName ORDER BY Date_ DESC)[OFFSET(0)] as AccountName, ARRAY_AGG(ServiceId ORDER BY Date_ DESC)[OFFSET(0)] as ServiceId, ARRAY_AGG(ServiceName ORDER BY Date_ DESC)[OFFSET(0)] as ServiceName, ARRAY_AGG(ServiceNameJA ORDER BY Date_ DESC)[OFFSET(0)] as ServiceNameJA, ARRAY_AGG(ServiceAccountId ORDER BY Date_ DESC)[OFFSET(0)] as ServiceAccountId, ARRAY_AGG(AccountBudget ORDER BY Date_ DESC)[OFFSET(0)] as AccountBudget, ARRAY_AGG(AccountBudgetIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as AccountBudgetIncludingFees, ARRAY_AGG(AccountRemainingBudget ORDER BY Date_ DESC)[OFFSET(0)] as AccountRemainingBudget, ARRAY_AGG(AccountRemainingBudgetIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as AccountRemainingBudgetIncludingFees, ARRAY_AGG(AccountBudgetProgressRate ORDER BY Date_ DESC)[OFFSET(0)] as AccountBudgetProgressRate, ARRAY_AGG(AccountBudgetProgressRateIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as AccountBudgetProgressRateIncludingFees, ARRAY_AGG(WorkingDays ORDER BY Date_ DESC)[OFFSET(0)] as WorkingDays, ARRAY_AGG(RemainingDays ORDER BY Date_ DESC)[OFFSET(0)] as RemainingDays, ARRAY_AGG(AccountImpressions ORDER BY Date_ DESC)[OFFSET(0)] as AccountImpressions, ARRAY_AGG(AccountClicks ORDER BY Date_ DESC)[OFFSET(0)] as AccountClicks, ARRAY_AGG(AccountConversions ORDER BY Date_ DESC)[OFFSET(0)] as AccountConversions, ARRAY_AGG(AccountConversionValue ORDER BY Date_ DESC)[OFFSET(0)] as AccountConversionValue, ARRAY_AGG(AccountAllConversions ORDER BY Date_ DESC)[OFFSET(0)] as AccountAllConversions, ARRAY_AGG(AccountAllConversionValue ORDER BY Date_ DESC)[OFFSET(0)] as AccountAllConversionValue, ARRAY_AGG(AccountCost ORDER BY Date_ DESC)[OFFSET(0)] as AccountCost, ARRAY_AGG(AccountCostAverageYesterday ORDER BY Date_ DESC)[OFFSET(0)] as AccountCostAverageYesterday, ARRAY_AGG(AccountCostAverageThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as AccountCostAverageThisPeriod, ARRAY_AGG(AccountCostIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as AccountCostIncludingFees, ARRAY_AGG(AccountCostAverageYesterdayIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as AccountCostAverageYesterdayIncludingFees, ARRAY_AGG(AccountCostAverageThisPeriodIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as AccountCostAverageThisPeriodIncludingFees, ARRAY_AGG(AccountUsageBudgetPerWorkingDays ORDER BY Date_ DESC)[OFFSET(0)] as AccountUsageBudgetPerWorkingDays, ARRAY_AGG(AccountUsageBudgetPerWorkingDaysIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as AccountUsageBudgetPerWorkingDaysIncludingFees, ARRAY_AGG(AccountUsageRemainingBudgetPerRemainingDays ORDER BY Date_ DESC)[OFFSET(0)] as AccountUsageRemainingBudgetPerRemainingDays, ARRAY_AGG(AccountUsageRemainingBudgetPerRemainingDaysIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as AccountUsageRemainingBudgetPerRemainingDaysIncludingFees, ARRAY_AGG(EstimatedAccountCostUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountCostUsingYesterday, ARRAY_AGG(EstimatedAccountCostUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountCostUsingThisPeriod, ARRAY_AGG(EstimatedAccountCostUsingYesterdayIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountCostUsingYesterdayIncludingFees, ARRAY_AGG(EstimatedAccountCostUsingThisPeriodIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountCostUsingThisPeriodIncludingFees, ARRAY_AGG(AccountConversionsYesterday ORDER BY Date_ DESC)[OFFSET(0)] as AccountConversionsYesterday, ARRAY_AGG(AccountConversionsThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as AccountConversionsThisPeriod, ARRAY_AGG(EstimatedAccountConversionsUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountConversionsUsingYesterday, ARRAY_AGG(EstimatedAccountConversionsUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountConversionsUsingThisPeriod, ARRAY_AGG(AccountConversionValueYesterday ORDER BY Date_ DESC)[OFFSET(0)] as AccountConversionValueYesterday, ARRAY_AGG(AccountConversionValueThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as AccountConversionValueThisPeriod, ARRAY_AGG(EstimatedAccountConversionValueUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountConversionValueUsingYesterday, ARRAY_AGG(EstimatedAccountConversionValueUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountConversionValueUsingThisPeriod, ARRAY_AGG(AccountAllConversionsYesterday ORDER BY Date_ DESC)[OFFSET(0)] as AccountAllConversionsYesterday, ARRAY_AGG(AccountAllConversionsThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as AccountAllConversionsThisPeriod, ARRAY_AGG(EstimatedAccountAllConversionsUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountAllConversionsUsingYesterday, ARRAY_AGG(EstimatedAccountAllConversionsUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountAllConversionsUsingThisPeriod, ARRAY_AGG(AccountAllConversionValueYesterday ORDER BY Date_ DESC)[OFFSET(0)] as AccountAllConversionValueYesterday, ARRAY_AGG(AccountAllConversionValueThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as AccountAllConversionValueThisPeriod, ARRAY_AGG(EstimatedAccountAllConversionValueUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountAllConversionValueUsingYesterday, ARRAY_AGG(EstimatedAccountAllConversionValueUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountAllConversionValueUsingThisPeriod, ARRAY_AGG(AccountClicksYesterday ORDER BY Date_ DESC)[OFFSET(0)] as AccountClicksYesterday, ARRAY_AGG(AccountClicksThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as AccountClicksThisPeriod, ARRAY_AGG(EstimatedAccountClicksUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountClicksUsingYesterday, ARRAY_AGG(EstimatedAccountClicksUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountClicksUsingThisPeriod, ARRAY_AGG(AccountImpressionsYesterday ORDER BY Date_ DESC)[OFFSET(0)] as AccountImpressionsYesterday, ARRAY_AGG(AccountImpressionsThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as AccountImpressionsThisPeriod, ARRAY_AGG(EstimatedAccountImpressionsUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountImpressionsUsingYesterday, ARRAY_AGG(EstimatedAccountImpressionsUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountImpressionsUsingThisPeriod 

      FROM `databeat-client-014`.databeat.campaign_summary 

      WHERE DATE(Date_) BETWEEN PARSE_DATE('%Y%m%d', @DS_START_DATE) AND PARSE_DATE('%Y%m%d', @DS_END_DATE) AND Month_ BETWEEN DATE_TRUNC(PARSE_DATE('%Y%m%d', @DS_START_DATE), MONTH) AND DATE_TRUNC(PARSE_DATE('%Y%m%d', @DS_END_DATE), MONTH) 

      GROUP BY PromotionId, Month_, PromotionAccountRelationId,PromotionNote,AccountNote

      ②全てのプロモーションを一括で出力する（アカウント&日別）

      SELECT Month_, ARRAY_AGG(Fee ORDER BY Date_ DESC)[OFFSET(0)] as Fee, ARRAY_AGG(FeeType ORDER BY Date_ DESC)[OFFSET(0)] as FeeType, ARRAY_AGG(FeeFormula ORDER BY Date_ DESC)[OFFSET(0)] as FeeFormula, ARRAY_AGG(FeeCoefficient ORDER BY Date_ DESC)[OFFSET(0)] as FeeCoefficient, ARRAY_AGG(PromotionName ORDER BY Date_ DESC)[OFFSET(0)] as PromotionName, ARRAY_AGG(AccountName ORDER BY Date_ DESC)[OFFSET(0)] as AccountName, ARRAY_AGG(ServiceId ORDER BY Date_ DESC)[OFFSET(0)] as ServiceId, ARRAY_AGG(ServiceName ORDER BY Date_ DESC)[OFFSET(0)] as ServiceName, ARRAY_AGG(ServiceNameJA ORDER BY Date_ DESC)[OFFSET(0)] as ServiceNameJA, ARRAY_AGG(ServiceAccountId ORDER BY Date_ DESC)[OFFSET(0)] as ServiceAccountId, ARRAY_AGG(AccountBudget ORDER BY Date_ DESC)[OFFSET(0)] as AccountBudget, ARRAY_AGG(AccountBudgetIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as AccountBudgetIncludingFees, ARRAY_AGG(AccountRemainingBudget ORDER BY Date_ DESC)[OFFSET(0)] as AccountRemainingBudget, ARRAY_AGG(AccountRemainingBudgetIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as AccountRemainingBudgetIncludingFees, ARRAY_AGG(AccountBudgetProgressRate ORDER BY Date_ DESC)[OFFSET(0)] as AccountBudgetProgressRate, ARRAY_AGG(AccountBudgetProgressRateIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as AccountBudgetProgressRateIncludingFees, ARRAY_AGG(WorkingDays ORDER BY Date_ DESC)[OFFSET(0)] as WorkingDays, ARRAY_AGG(RemainingDays ORDER BY Date_ DESC)[OFFSET(0)] as RemainingDays, ARRAY_AGG(AccountImpressions ORDER BY Date_ DESC)[OFFSET(0)] as AccountImpressions, ARRAY_AGG(AccountClicks ORDER BY Date_ DESC)[OFFSET(0)] as AccountClicks, ARRAY_AGG(AccountConversions ORDER BY Date_ DESC)[OFFSET(0)] as AccountConversions, ARRAY_AGG(AccountConversionValue ORDER BY Date_ DESC)[OFFSET(0)] as AccountConversionValue, ARRAY_AGG(AccountAllConversions ORDER BY Date_ DESC)[OFFSET(0)] as AccountAllConversions, ARRAY_AGG(AccountAllConversionValue ORDER BY Date_ DESC)[OFFSET(0)] as AccountAllConversionValue, ARRAY_AGG(AccountCost ORDER BY Date_ DESC)[OFFSET(0)] as AccountCost, ARRAY_AGG(AccountCostAverageYesterday ORDER BY Date_ DESC)[OFFSET(0)] as AccountCostAverageYesterday, ARRAY_AGG(AccountCostAverageThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as AccountCostAverageThisPeriod, ARRAY_AGG(AccountCostIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as AccountCostIncludingFees, ARRAY_AGG(AccountCostAverageYesterdayIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as AccountCostAverageYesterdayIncludingFees, ARRAY_AGG(AccountCostAverageThisPeriodIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as AccountCostAverageThisPeriodIncludingFees, ARRAY_AGG(AccountUsageBudgetPerWorkingDays ORDER BY Date_ DESC)[OFFSET(0)] as AccountUsageBudgetPerWorkingDays, ARRAY_AGG(AccountUsageBudgetPerWorkingDaysIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as AccountUsageBudgetPerWorkingDaysIncludingFees, ARRAY_AGG(AccountUsageRemainingBudgetPerRemainingDays ORDER BY Date_ DESC)[OFFSET(0)] as AccountUsageRemainingBudgetPerRemainingDays, ARRAY_AGG(AccountUsageRemainingBudgetPerRemainingDaysIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as AccountUsageRemainingBudgetPerRemainingDaysIncludingFees, ARRAY_AGG(EstimatedAccountCostUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountCostUsingYesterday, ARRAY_AGG(EstimatedAccountCostUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountCostUsingThisPeriod, ARRAY_AGG(EstimatedAccountCostUsingYesterdayIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountCostUsingYesterdayIncludingFees, ARRAY_AGG(EstimatedAccountCostUsingThisPeriodIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountCostUsingThisPeriodIncludingFees, ARRAY_AGG(AccountConversionsYesterday ORDER BY Date_ DESC)[OFFSET(0)] as AccountConversionsYesterday, ARRAY_AGG(AccountConversionsThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as AccountConversionsThisPeriod, ARRAY_AGG(EstimatedAccountConversionsUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountConversionsUsingYesterday, ARRAY_AGG(EstimatedAccountConversionsUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountConversionsUsingThisPeriod, ARRAY_AGG(AccountConversionValueYesterday ORDER BY Date_ DESC)[OFFSET(0)] as AccountConversionValueYesterday, ARRAY_AGG(AccountConversionValueThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as AccountConversionValueThisPeriod, ARRAY_AGG(EstimatedAccountConversionValueUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountConversionValueUsingYesterday, ARRAY_AGG(EstimatedAccountConversionValueUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountConversionValueUsingThisPeriod, ARRAY_AGG(AccountAllConversionsYesterday ORDER BY Date_ DESC)[OFFSET(0)] as AccountAllConversionsYesterday, ARRAY_AGG(AccountAllConversionsThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as AccountAllConversionsThisPeriod, ARRAY_AGG(EstimatedAccountAllConversionsUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountAllConversionsUsingYesterday, ARRAY_AGG(EstimatedAccountAllConversionsUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountAllConversionsUsingThisPeriod, ARRAY_AGG(AccountAllConversionValueYesterday ORDER BY Date_ DESC)[OFFSET(0)] as AccountAllConversionValueYesterday, ARRAY_AGG(AccountAllConversionValueThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as AccountAllConversionValueThisPeriod, ARRAY_AGG(EstimatedAccountAllConversionValueUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountAllConversionValueUsingYesterday, ARRAY_AGG(EstimatedAccountAllConversionValueUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountAllConversionValueUsingThisPeriod, ARRAY_AGG(AccountClicksYesterday ORDER BY Date_ DESC)[OFFSET(0)] as AccountClicksYesterday, ARRAY_AGG(AccountClicksThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as AccountClicksThisPeriod, ARRAY_AGG(EstimatedAccountClicksUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountClicksUsingYesterday, ARRAY_AGG(EstimatedAccountClicksUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountClicksUsingThisPeriod, ARRAY_AGG(AccountImpressionsYesterday ORDER BY Date_ DESC)[OFFSET(0)] as AccountImpressionsYesterday, ARRAY_AGG(AccountImpressionsThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as AccountImpressionsThisPeriod, ARRAY_AGG(EstimatedAccountImpressionsUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountImpressionsUsingYesterday, ARRAY_AGG(EstimatedAccountImpressionsUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountImpressionsUsingThisPeriod,DATE(Date_) as Date_

      FROM `databeat-client-014`.databeat.campaign_summary 

      WHERE DATE(Date_) BETWEEN PARSE_DATE('%Y%m%d', @DS_START_DATE) AND PARSE_DATE('%Y%m%d', @DS_END_DATE) AND Month_ BETWEEN DATE_TRUNC(PARSE_DATE('%Y%m%d', @DS_START_DATE), MONTH) AND DATE_TRUNC(PARSE_DATE('%Y%m%d', @DS_END_DATE), MONTH) 

      GROUP BY PromotionId, Date_, Month_, PromotionAccountRelationId

      ③全てのプロモーションを一括で出力する（キャンペーン別）

      SELECT Month_, ARRAY_AGG(Fee ORDER BY Date_ DESC)[OFFSET(0)] as Fee, ARRAY_AGG(FeeType ORDER BY Date_ DESC)[OFFSET(0)] as FeeType, ARRAY_AGG(FeeFormula ORDER BY Date_ DESC)[OFFSET(0)] as FeeFormula, ARRAY_AGG(FeeCoefficient ORDER BY Date_ DESC)[OFFSET(0)] as FeeCoefficient, ARRAY_AGG(PromotionName ORDER BY Date_ DESC)[OFFSET(0)] as PromotionName, ARRAY_AGG(AccountName ORDER BY Date_ DESC)[OFFSET(0)] as AccountName, ARRAY_AGG(ServiceId ORDER BY Date_ DESC)[OFFSET(0)] as ServiceId, ARRAY_AGG(ServiceName ORDER BY Date_ DESC)[OFFSET(0)] as ServiceName, ARRAY_AGG(ServiceNameJA ORDER BY Date_ DESC)[OFFSET(0)] as ServiceNameJA, ARRAY_AGG(ServiceAccountId ORDER BY Date_ DESC)[OFFSET(0)] as ServiceAccountId, ARRAY_AGG(AccountBudget ORDER BY Date_ DESC)[OFFSET(0)] as AccountBudget, ARRAY_AGG(AccountBudgetIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as AccountBudgetIncludingFees, ARRAY_AGG(AccountRemainingBudget ORDER BY Date_ DESC)[OFFSET(0)] as AccountRemainingBudget, ARRAY_AGG(AccountRemainingBudgetIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as AccountRemainingBudgetIncludingFees, ARRAY_AGG(AccountBudgetProgressRate ORDER BY Date_ DESC)[OFFSET(0)] as AccountBudgetProgressRate, ARRAY_AGG(AccountBudgetProgressRateIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as AccountBudgetProgressRateIncludingFees, ARRAY_AGG(WorkingDays ORDER BY Date_ DESC)[OFFSET(0)] as WorkingDays, ARRAY_AGG(RemainingDays ORDER BY Date_ DESC)[OFFSET(0)] as RemainingDays, ARRAY_AGG(AccountImpressions ORDER BY Date_ DESC)[OFFSET(0)] as AccountImpressions, ARRAY_AGG(AccountClicks ORDER BY Date_ DESC)[OFFSET(0)] as AccountClicks, ARRAY_AGG(AccountConversions ORDER BY Date_ DESC)[OFFSET(0)] as AccountConversions, ARRAY_AGG(AccountConversionValue ORDER BY Date_ DESC)[OFFSET(0)] as AccountConversionValue, ARRAY_AGG(AccountAllConversions ORDER BY Date_ DESC)[OFFSET(0)] as AccountAllConversions, ARRAY_AGG(AccountAllConversionValue ORDER BY Date_ DESC)[OFFSET(0)] as AccountAllConversionValue, ARRAY_AGG(AccountCost ORDER BY Date_ DESC)[OFFSET(0)] as AccountCost, ARRAY_AGG(AccountCostAverageYesterday ORDER BY Date_ DESC)[OFFSET(0)] as AccountCostAverageYesterday, ARRAY_AGG(AccountCostAverageThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as AccountCostAverageThisPeriod, ARRAY_AGG(AccountCostIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as AccountCostIncludingFees, ARRAY_AGG(AccountCostAverageYesterdayIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as AccountCostAverageYesterdayIncludingFees, ARRAY_AGG(AccountCostAverageThisPeriodIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as AccountCostAverageThisPeriodIncludingFees, ARRAY_AGG(AccountUsageBudgetPerWorkingDays ORDER BY Date_ DESC)[OFFSET(0)] as AccountUsageBudgetPerWorkingDays, ARRAY_AGG(AccountUsageBudgetPerWorkingDaysIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as AccountUsageBudgetPerWorkingDaysIncludingFees, ARRAY_AGG(AccountUsageRemainingBudgetPerRemainingDays ORDER BY Date_ DESC)[OFFSET(0)] as AccountUsageRemainingBudgetPerRemainingDays, ARRAY_AGG(AccountUsageRemainingBudgetPerRemainingDaysIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as AccountUsageRemainingBudgetPerRemainingDaysIncludingFees, ARRAY_AGG(EstimatedAccountCostUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountCostUsingYesterday, ARRAY_AGG(EstimatedAccountCostUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountCostUsingThisPeriod, ARRAY_AGG(EstimatedAccountCostUsingYesterdayIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountCostUsingYesterdayIncludingFees, ARRAY_AGG(EstimatedAccountCostUsingThisPeriodIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountCostUsingThisPeriodIncludingFees, ARRAY_AGG(AccountConversionsYesterday ORDER BY Date_ DESC)[OFFSET(0)] as AccountConversionsYesterday, ARRAY_AGG(AccountConversionsThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as AccountConversionsThisPeriod, ARRAY_AGG(EstimatedAccountConversionsUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountConversionsUsingYesterday, ARRAY_AGG(EstimatedAccountConversionsUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountConversionsUsingThisPeriod, ARRAY_AGG(AccountConversionValueYesterday ORDER BY Date_ DESC)[OFFSET(0)] as AccountConversionValueYesterday, ARRAY_AGG(AccountConversionValueThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as AccountConversionValueThisPeriod, ARRAY_AGG(EstimatedAccountConversionValueUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountConversionValueUsingYesterday, ARRAY_AGG(EstimatedAccountConversionValueUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountConversionValueUsingThisPeriod, ARRAY_AGG(AccountAllConversionsYesterday ORDER BY Date_ DESC)[OFFSET(0)] as AccountAllConversionsYesterday, ARRAY_AGG(AccountAllConversionsThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as AccountAllConversionsThisPeriod, ARRAY_AGG(EstimatedAccountAllConversionsUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountAllConversionsUsingYesterday, ARRAY_AGG(EstimatedAccountAllConversionsUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountAllConversionsUsingThisPeriod, ARRAY_AGG(AccountAllConversionValueYesterday ORDER BY Date_ DESC)[OFFSET(0)] as AccountAllConversionValueYesterday, ARRAY_AGG(AccountAllConversionValueThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as AccountAllConversionValueThisPeriod, ARRAY_AGG(EstimatedAccountAllConversionValueUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountAllConversionValueUsingYesterday, ARRAY_AGG(EstimatedAccountAllConversionValueUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountAllConversionValueUsingThisPeriod, ARRAY_AGG(AccountClicksYesterday ORDER BY Date_ DESC)[OFFSET(0)] as AccountClicksYesterday, ARRAY_AGG(AccountClicksThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as AccountClicksThisPeriod, ARRAY_AGG(EstimatedAccountClicksUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountClicksUsingYesterday, ARRAY_AGG(EstimatedAccountClicksUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountClicksUsingThisPeriod, ARRAY_AGG(AccountImpressionsYesterday ORDER BY Date_ DESC)[OFFSET(0)] as AccountImpressionsYesterday, ARRAY_AGG(AccountImpressionsThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as AccountImpressionsThisPeriod, ARRAY_AGG(EstimatedAccountImpressionsUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountImpressionsUsingYesterday, ARRAY_AGG(EstimatedAccountImpressionsUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountImpressionsUsingThisPeriod, ARRAY_AGG(CampaignName_ ORDER BY Date_ DESC)[OFFSET(0)] as CampaignName, ARRAY_AGG(CampaignAdvertisingChannelType ORDER BY Date_ DESC)[OFFSET(0)] as CampaignAdvertisingChannelType, ARRAY_AGG(CampaignAdvertisingChannelSubType ORDER BY Date_ DESC)[OFFSET(0)] as CampaignAdvertisingChannelSubType, ARRAY_AGG(CampaignImpressions ORDER BY Date_ DESC)[OFFSET(0)] as CampaignImpressions, ARRAY_AGG(CampaignClicks ORDER BY Date_ DESC)[OFFSET(0)] as CampaignClicks, ARRAY_AGG(CampaignConversions ORDER BY Date_ DESC)[OFFSET(0)] as CampaignConversions, ARRAY_AGG(CampaignAllConversions ORDER BY Date_ DESC)[OFFSET(0)] as CampaignAllConversions, ARRAY_AGG(CampaignCost ORDER BY Date_ DESC)[OFFSET(0)] as CampaignCost, ARRAY_AGG(CampaignCostAverageYesterday ORDER BY Date_ DESC)[OFFSET(0)] as CampaignCostAverageYesterday, ARRAY_AGG(CampaignCostAverageThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as CampaignCostAverageThisPeriod, ARRAY_AGG(CampaignCostIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as CampaignCostIncludingFees, ARRAY_AGG(CampaignCostAverageYesterdayIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as CampaignCostAverageYesterdayIncludingFees, ARRAY_AGG(CampaignCostAverageThisPeriodIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as CampaignCostAverageThisPeriodIncludingFees, ARRAY_AGG(EstimatedCampaignCostUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedCampaignCostUsingYesterday, ARRAY_AGG(EstimatedCampaignCostUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedCampaignCostUsingThisPeriod, ARRAY_AGG(EstimatedCampaignCostUsingYesterdayIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedCampaignCostUsingYesterdayIncludingFees, ARRAY_AGG(EstimatedCampaignCostUsingThisPeriodIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedCampaignCostUsingThisPeriodIncludingFees, ARRAY_AGG(CampaignConversionsThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as CampaignConversionsThisPeriod, ARRAY_AGG(EstimatedCampaignConversionsUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedCampaignConversionsUsingYesterday, ARRAY_AGG(EstimatedCampaignConversionsUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedCampaignConversionsUsingThisPeriod, ARRAY_AGG(CampaignConversionValueYesterday ORDER BY Date_ DESC)[OFFSET(0)] as CampaignConversionValueYesterday, ARRAY_AGG(CampaignConversionValueThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as CampaignConversionValueThisPeriod, ARRAY_AGG(EstimatedCampaignConversionValueUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedCampaignConversionValueUsingYesterday, ARRAY_AGG(EstimatedCampaignConversionValueUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedCampaignConversionValueUsingThisPeriod, ARRAY_AGG(CampaignAllConversionsYesterday ORDER BY Date_ DESC)[OFFSET(0)] as CampaignAllConversionsYesterday, ARRAY_AGG(CampaignAllConversionsThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as CampaignAllConversionsThisPeriod, ARRAY_AGG(EstimatedCampaignAllConversionsUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedCampaignAllConversionsUsingYesterday, ARRAY_AGG(EstimatedCampaignAllConversionsUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedCampaignAllConversionsUsingThisPeriod, ARRAY_AGG(CampaignAllConversionValueYesterday ORDER BY Date_ DESC)[OFFSET(0)] as CampaignAllConversionValueYesterday, ARRAY_AGG(CampaignAllConversionValueThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as CampaignAllConversionValueThisPeriod, ARRAY_AGG(EstimatedCampaignAllConversionValueUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedCampaignAllConversionValueUsingYesterday, ARRAY_AGG(EstimatedCampaignAllConversionValueUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedCampaignAllConversionValueUsingThisPeriod, ARRAY_AGG(CampaignClicksYesterday ORDER BY Date_ DESC)[OFFSET(0)] as CampaignClicksYesterday, ARRAY_AGG(CampaignClicksThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as CampaignClicksThisPeriod, ARRAY_AGG(EstimatedCampaignClicksUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedCampaignClicksUsingYesterday, ARRAY_AGG(EstimatedCampaignClicksUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedCampaignClicksUsingThisPeriod, ARRAY_AGG(CampaignImpressionsYesterday ORDER BY Date_ DESC)[OFFSET(0)] as CampaignImpressionsYesterday, ARRAY_AGG(CampaignImpressionsThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as CampaignImpressionsThisPeriod, ARRAY_AGG(EstimatedCampaignImpressionsUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedCampaignImpressionsUsingYesterday, ARRAY_AGG(EstimatedCampaignImpressionsUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedCampaignImpressionsUsingThisPeriod 

      FROM `databeat-client-014`.databeat.campaign_summary 

      WHERE DATE(Date_) BETWEEN PARSE_DATE('%Y%m%d', @DS_START_DATE) AND PARSE_DATE('%Y%m%d', @DS_END_DATE) AND Month_ BETWEEN DATE_TRUNC(PARSE_DATE('%Y%m%d', @DS_START_DATE), MONTH) AND DATE_TRUNC(PARSE_DATE('%Y%m%d', @DS_END_DATE), MONTH) 

      GROUP BY PromotionId, Month_, PromotionAccountRelationId, CampaignId_

      ④全てのプロモーションを一括で出力する（キャンペーン&日別）

      SELECT Month_, ARRAY_AGG(Fee ORDER BY Date_ DESC)[OFFSET(0)] as Fee, ARRAY_AGG(FeeType ORDER BY Date_ DESC)[OFFSET(0)] as FeeType, ARRAY_AGG(FeeFormula ORDER BY Date_ DESC)[OFFSET(0)] as FeeFormula, ARRAY_AGG(FeeCoefficient ORDER BY Date_ DESC)[OFFSET(0)] as FeeCoefficient, ARRAY_AGG(PromotionName ORDER BY Date_ DESC)[OFFSET(0)] as PromotionName, ARRAY_AGG(AccountName ORDER BY Date_ DESC)[OFFSET(0)] as AccountName, ARRAY_AGG(ServiceId ORDER BY Date_ DESC)[OFFSET(0)] as ServiceId, ARRAY_AGG(ServiceName ORDER BY Date_ DESC)[OFFSET(0)] as ServiceName, ARRAY_AGG(ServiceNameJA ORDER BY Date_ DESC)[OFFSET(0)] as ServiceNameJA, ARRAY_AGG(ServiceAccountId ORDER BY Date_ DESC)[OFFSET(0)] as ServiceAccountId, ARRAY_AGG(AccountBudget ORDER BY Date_ DESC)[OFFSET(0)] as AccountBudget, ARRAY_AGG(AccountBudgetIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as AccountBudgetIncludingFees, ARRAY_AGG(AccountRemainingBudget ORDER BY Date_ DESC)[OFFSET(0)] as AccountRemainingBudget, ARRAY_AGG(AccountRemainingBudgetIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as AccountRemainingBudgetIncludingFees, ARRAY_AGG(AccountBudgetProgressRate ORDER BY Date_ DESC)[OFFSET(0)] as AccountBudgetProgressRate, ARRAY_AGG(AccountBudgetProgressRateIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as AccountBudgetProgressRateIncludingFees, ARRAY_AGG(WorkingDays ORDER BY Date_ DESC)[OFFSET(0)] as WorkingDays, ARRAY_AGG(RemainingDays ORDER BY Date_ DESC)[OFFSET(0)] as RemainingDays, ARRAY_AGG(AccountImpressions ORDER BY Date_ DESC)[OFFSET(0)] as AccountImpressions, ARRAY_AGG(AccountClicks ORDER BY Date_ DESC)[OFFSET(0)] as AccountClicks, ARRAY_AGG(AccountConversions ORDER BY Date_ DESC)[OFFSET(0)] as AccountConversions, ARRAY_AGG(AccountConversionValue ORDER BY Date_ DESC)[OFFSET(0)] as AccountConversionValue, ARRAY_AGG(AccountAllConversions ORDER BY Date_ DESC)[OFFSET(0)] as AccountAllConversions, ARRAY_AGG(AccountAllConversionValue ORDER BY Date_ DESC)[OFFSET(0)] as AccountAllConversionValue, ARRAY_AGG(AccountCost ORDER BY Date_ DESC)[OFFSET(0)] as AccountCost, ARRAY_AGG(AccountCostAverageYesterday ORDER BY Date_ DESC)[OFFSET(0)] as AccountCostAverageYesterday, ARRAY_AGG(AccountCostAverageThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as AccountCostAverageThisPeriod, ARRAY_AGG(AccountCostIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as AccountCostIncludingFees, ARRAY_AGG(AccountCostAverageYesterdayIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as AccountCostAverageYesterdayIncludingFees, ARRAY_AGG(AccountCostAverageThisPeriodIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as AccountCostAverageThisPeriodIncludingFees, ARRAY_AGG(AccountUsageBudgetPerWorkingDays ORDER BY Date_ DESC)[OFFSET(0)] as AccountUsageBudgetPerWorkingDays, ARRAY_AGG(AccountUsageBudgetPerWorkingDaysIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as AccountUsageBudgetPerWorkingDaysIncludingFees, ARRAY_AGG(AccountUsageRemainingBudgetPerRemainingDays ORDER BY Date_ DESC)[OFFSET(0)] as AccountUsageRemainingBudgetPerRemainingDays, ARRAY_AGG(AccountUsageRemainingBudgetPerRemainingDaysIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as AccountUsageRemainingBudgetPerRemainingDaysIncludingFees, ARRAY_AGG(EstimatedAccountCostUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountCostUsingYesterday, ARRAY_AGG(EstimatedAccountCostUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountCostUsingThisPeriod, ARRAY_AGG(EstimatedAccountCostUsingYesterdayIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountCostUsingYesterdayIncludingFees, ARRAY_AGG(EstimatedAccountCostUsingThisPeriodIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountCostUsingThisPeriodIncludingFees, ARRAY_AGG(AccountConversionsYesterday ORDER BY Date_ DESC)[OFFSET(0)] as AccountConversionsYesterday, ARRAY_AGG(AccountConversionsThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as AccountConversionsThisPeriod, ARRAY_AGG(EstimatedAccountConversionsUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountConversionsUsingYesterday, ARRAY_AGG(EstimatedAccountConversionsUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountConversionsUsingThisPeriod, ARRAY_AGG(AccountConversionValueYesterday ORDER BY Date_ DESC)[OFFSET(0)] as AccountConversionValueYesterday, ARRAY_AGG(AccountConversionValueThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as AccountConversionValueThisPeriod, ARRAY_AGG(EstimatedAccountConversionValueUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountConversionValueUsingYesterday, ARRAY_AGG(EstimatedAccountConversionValueUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountConversionValueUsingThisPeriod, ARRAY_AGG(AccountAllConversionsYesterday ORDER BY Date_ DESC)[OFFSET(0)] as AccountAllConversionsYesterday, ARRAY_AGG(AccountAllConversionsThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as AccountAllConversionsThisPeriod, ARRAY_AGG(EstimatedAccountAllConversionsUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountAllConversionsUsingYesterday, ARRAY_AGG(EstimatedAccountAllConversionsUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountAllConversionsUsingThisPeriod, ARRAY_AGG(AccountAllConversionValueYesterday ORDER BY Date_ DESC)[OFFSET(0)] as AccountAllConversionValueYesterday, ARRAY_AGG(AccountAllConversionValueThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as AccountAllConversionValueThisPeriod, ARRAY_AGG(EstimatedAccountAllConversionValueUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountAllConversionValueUsingYesterday, ARRAY_AGG(EstimatedAccountAllConversionValueUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountAllConversionValueUsingThisPeriod, ARRAY_AGG(AccountClicksYesterday ORDER BY Date_ DESC)[OFFSET(0)] as AccountClicksYesterday, ARRAY_AGG(AccountClicksThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as AccountClicksThisPeriod, ARRAY_AGG(EstimatedAccountClicksUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountClicksUsingYesterday, ARRAY_AGG(EstimatedAccountClicksUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountClicksUsingThisPeriod, ARRAY_AGG(AccountImpressionsYesterday ORDER BY Date_ DESC)[OFFSET(0)] as AccountImpressionsYesterday, ARRAY_AGG(AccountImpressionsThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as AccountImpressionsThisPeriod, ARRAY_AGG(EstimatedAccountImpressionsUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountImpressionsUsingYesterday, ARRAY_AGG(EstimatedAccountImpressionsUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountImpressionsUsingThisPeriod, ARRAY_AGG(CampaignName_ ORDER BY Date_ DESC)[OFFSET(0)] as CampaignName, ARRAY_AGG(CampaignAdvertisingChannelType ORDER BY Date_ DESC)[OFFSET(0)] as CampaignAdvertisingChannelType, ARRAY_AGG(CampaignAdvertisingChannelSubType ORDER BY Date_ DESC)[OFFSET(0)] as CampaignAdvertisingChannelSubType, ARRAY_AGG(CampaignImpressions ORDER BY Date_ DESC)[OFFSET(0)] as CampaignImpressions, ARRAY_AGG(CampaignClicks ORDER BY Date_ DESC)[OFFSET(0)] as CampaignClicks, ARRAY_AGG(CampaignConversions ORDER BY Date_ DESC)[OFFSET(0)] as CampaignConversions, ARRAY_AGG(CampaignAllConversions ORDER BY Date_ DESC)[OFFSET(0)] as CampaignAllConversions, ARRAY_AGG(CampaignCost ORDER BY Date_ DESC)[OFFSET(0)] as CampaignCost, ARRAY_AGG(CampaignCostAverageYesterday ORDER BY Date_ DESC)[OFFSET(0)] as CampaignCostAverageYesterday, ARRAY_AGG(CampaignCostAverageThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as CampaignCostAverageThisPeriod, ARRAY_AGG(CampaignCostIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as CampaignCostIncludingFees, ARRAY_AGG(CampaignCostAverageYesterdayIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as CampaignCostAverageYesterdayIncludingFees, ARRAY_AGG(CampaignCostAverageThisPeriodIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as CampaignCostAverageThisPeriodIncludingFees, ARRAY_AGG(EstimatedCampaignCostUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedCampaignCostUsingYesterday, ARRAY_AGG(EstimatedCampaignCostUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedCampaignCostUsingThisPeriod, ARRAY_AGG(EstimatedCampaignCostUsingYesterdayIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedCampaignCostUsingYesterdayIncludingFees, ARRAY_AGG(EstimatedCampaignCostUsingThisPeriodIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedCampaignCostUsingThisPeriodIncludingFees, ARRAY_AGG(CampaignConversionsThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as CampaignConversionsThisPeriod, ARRAY_AGG(EstimatedCampaignConversionsUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedCampaignConversionsUsingYesterday, ARRAY_AGG(EstimatedCampaignConversionsUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedCampaignConversionsUsingThisPeriod, ARRAY_AGG(CampaignConversionValueYesterday ORDER BY Date_ DESC)[OFFSET(0)] as CampaignConversionValueYesterday, ARRAY_AGG(CampaignConversionValueThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as CampaignConversionValueThisPeriod, ARRAY_AGG(EstimatedCampaignConversionValueUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedCampaignConversionValueUsingYesterday, ARRAY_AGG(EstimatedCampaignConversionValueUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedCampaignConversionValueUsingThisPeriod, ARRAY_AGG(CampaignAllConversionsYesterday ORDER BY Date_ DESC)[OFFSET(0)] as CampaignAllConversionsYesterday, ARRAY_AGG(CampaignAllConversionsThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as CampaignAllConversionsThisPeriod, ARRAY_AGG(EstimatedCampaignAllConversionsUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedCampaignAllConversionsUsingYesterday, ARRAY_AGG(EstimatedCampaignAllConversionsUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedCampaignAllConversionsUsingThisPeriod, ARRAY_AGG(CampaignAllConversionValueYesterday ORDER BY Date_ DESC)[OFFSET(0)] as CampaignAllConversionValueYesterday, ARRAY_AGG(CampaignAllConversionValueThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as CampaignAllConversionValueThisPeriod, ARRAY_AGG(EstimatedCampaignAllConversionValueUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedCampaignAllConversionValueUsingYesterday, ARRAY_AGG(EstimatedCampaignAllConversionValueUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedCampaignAllConversionValueUsingThisPeriod, ARRAY_AGG(CampaignClicksYesterday ORDER BY Date_ DESC)[OFFSET(0)] as CampaignClicksYesterday, ARRAY_AGG(CampaignClicksThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as CampaignClicksThisPeriod, ARRAY_AGG(EstimatedCampaignClicksUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedCampaignClicksUsingYesterday, ARRAY_AGG(EstimatedCampaignClicksUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedCampaignClicksUsingThisPeriod, ARRAY_AGG(CampaignImpressionsYesterday ORDER BY Date_ DESC)[OFFSET(0)] as CampaignImpressionsYesterday, ARRAY_AGG(CampaignImpressionsThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as CampaignImpressionsThisPeriod, ARRAY_AGG(EstimatedCampaignImpressionsUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedCampaignImpressionsUsingYesterday, ARRAY_AGG(EstimatedCampaignImpressionsUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedCampaignImpressionsUsingThisPeriod, DATE(Date_) as Date_ 

      FROM `databeat-client-014`.databeat.campaign_summary

      WHERE DATE(Date_) BETWEEN PARSE_DATE('%Y%m%d', @DS_START_DATE) AND PARSE_DATE('%Y%m%d', @DS_END_DATE) AND Month_ BETWEEN DATE_TRUNC(PARSE_DATE('%Y%m%d', @DS_START_DATE), MONTH) AND DATE_TRUNC(PARSE_DATE('%Y%m%d', @DS_END_DATE), MONTH) 

      GROUP BY PromotionId, Date_, Month_, PromotionAccountRelationId, CampaignId_

      ⑤全てのプロモーションを一括で出力する（アカウント別）

      ※ 複数のプロモーションに同一のアカウントが紐付いていても重複することなく出力できます。

      SELECT Month_ as Month, ServiceId, ServiceName, ServiceNameJA, ServiceAccountId,AccountName, SUM(CampaignImpressionsYesterday) as Impressions, SUM(CampaignClicksYesterday) as Clicks, SUM(CampaignConversionsYesterday) as Conversions, SUM(CampaignCostAverageYesterday) as Cost, SUM(CampaignCostAverageYesterdayIncludingFees) as CostIncludingFees 

      FROM ( SELECT Month_, Date_, ServiceId, ServiceName, ServiceNameJA, ServiceAccountId, CampaignId_,ARRAY_AGG(AccountName ORDER BY Date_ DESC)[OFFSET(0)] as AccountName, ARRAY_AGG(CampaignName_ ORDER BY Date_ DESC)[OFFSET(0)] as CampaignName, ARRAY_AGG(CampaignImpressionsYesterday ORDER BY Date_ DESC)[OFFSET(0)] as CampaignImpressionsYesterday, ARRAY_AGG(CampaignClicksYesterday ORDER BY Date_ DESC)[OFFSET(0)] as CampaignClicksYesterday, ARRAY_AGG(CampaignConversionsYesterday ORDER BY Date_ DESC)[OFFSET(0)] as CampaignConversionsYesterday, ARRAY_AGG(CampaignCostAverageYesterday ORDER BY Date_ DESC)[OFFSET(0)] as CampaignCostAverageYesterday, ARRAY_AGG(CampaignCostAverageYesterdayIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as CampaignCostAverageYesterdayIncludingFees, ARRAY_AGG(CampaignAdvertisingChannelType ORDER BY Date_ DESC)[OFFSET(0)] as CampaignAdvertisingChannelType,

      FROM `databeat-client-014`.databeat.campaign_summary 

      WHERE DATE(Date_) BETWEEN PARSE_DATE('%Y%m%d', @DS_START_DATE) AND PARSE_DATE('%Y%m%d', @DS_END_DATE) AND Month_ BETWEEN DATE_TRUNC(PARSE_DATE('%Y%m%d', @DS_START_DATE), MONTH) AND DATE_TRUNC(PARSE_DATE('%Y%m%d', @DS_END_DATE), MONTH) 

      GROUP BY Month_, Date_, ServiceId, ServiceName, ServiceNameJA, ServiceAccountId, CampaignId_ ) 

      GROUP BY ServiceId, ServiceName, ServiceNameJA, ServiceAccountId,Accountname, Month_

      このSQLで出力できる内容は下記の通りです。

      • Month →月
      • ServiceId → サービスID
      • ServiceName → サービス名
      • ServiceNameJA → サービス名（日本語）
      • ServiceAccountId → サービスアカウントID
      • AccountName → アカウント名
      • Impressions → 表示回数
      • Clicks → クリック数
      • Conversions → コンバージョン数
      • Cost → 費用
      • CostIncludingFees → （手数料込）費用

      ⑥全てのプロモーションを一括で出力する（アカウント&日別）

      ※ 複数のプロモーションに同一のアカウントが紐付いていても重複することなく出力できます。

      SELECT Month_ as Month,DATE(Date_) as Date, ServiceId, ServiceName, ServiceNameJA, ServiceAccountId,AccountName, SUM(CampaignImpressionsYesterday) as Impressions, SUM(CampaignClicksYesterday) as Clicks, SUM(CampaignConversionsYesterday) as Conversions, SUM(CampaignCostAverageYesterday) as Cost, SUM(CampaignCostAverageYesterdayIncludingFees) as CostIncludingFees 

      FROM ( SELECT Month_, Date_, ServiceId, ServiceName, ServiceNameJA, ServiceAccountId, CampaignId_,ARRAY_AGG(AccountName ORDER BY Date_ DESC)[OFFSET(0)] as AccountName, ARRAY_AGG(CampaignName_ ORDER BY Date_ DESC)[OFFSET(0)] as CampaignName, ARRAY_AGG(CampaignImpressionsYesterday ORDER BY Date_ DESC)[OFFSET(0)] as CampaignImpressionsYesterday, ARRAY_AGG(CampaignClicksYesterday ORDER BY Date_ DESC)[OFFSET(0)] as CampaignClicksYesterday, ARRAY_AGG(CampaignConversionsYesterday ORDER BY Date_ DESC)[OFFSET(0)] as CampaignConversionsYesterday, ARRAY_AGG(CampaignCostAverageYesterday ORDER BY Date_ DESC)[OFFSET(0)] as CampaignCostAverageYesterday, ARRAY_AGG(CampaignCostAverageYesterdayIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as CampaignCostAverageYesterdayIncludingFees, ARRAY_AGG(CampaignAdvertisingChannelType ORDER BY Date_ DESC)[OFFSET(0)] as CampaignAdvertisingChannelType, 

      FROM `databeat-client-014`.databeat.campaign_summary 

      WHERE DATE(Date_) BETWEEN PARSE_DATE('%Y%m%d', @DS_START_DATE) AND PARSE_DATE('%Y%m%d', @DS_END_DATE) AND Month_ BETWEEN DATE_TRUNC(PARSE_DATE('%Y%m%d', @DS_START_DATE), MONTH) AND DATE_TRUNC(PARSE_DATE('%Y%m%d', @DS_END_DATE), MONTH) 

      GROUP BY Month_, Date_, ServiceId, ServiceName, ServiceNameJA, ServiceAccountId, CampaignId_ ) 

      GROUP BY ServiceId, ServiceName, ServiceNameJA, ServiceAccountId,Accountname, Month_,Date_
      このSQLで出力できる内容は下記の通りです。
      • Month →月
      • Date → 日
      • ServiceId → サービスID
      • ServiceName → サービス名
      • ServiceNameJA → サービス名（日本語）
      • ServiceAccountId → サービスアカウントID
      • AccountName → アカウント名
      • Impressions → 表示回数
      • Clicks → クリック数
      • Conversions → コンバージョン数
      • Cost → 費用
      • CostIncludingFees → （手数料込）費用

      ⑦全てのプロモーションを一括で出力する（キャンペーン別）

      ※ 複数のプロモーションに同一のアカウントが紐付いていても重複することなく出力できます。

      SELECT Month_ as Month, ServiceId, ServiceName, ServiceNameJA, ServiceAccountId,AccountName, CampaignName, CampaignAdvertisingChannelType, SUM(CampaignImpressionsYesterday) as Impressions, SUM(CampaignClicksYesterday) as Clicks, SUM(CampaignConversionsYesterday) as Conversions, SUM(CampaignCostAverageYesterday) as Cost, SUM(CampaignCostAverageYesterdayIncludingFees) as CostIncludingFees 

      FROM ( SELECT Month_, Date_, ServiceId, ServiceName, ServiceNameJA, ServiceAccountId, CampaignId_,ARRAY_AGG(AccountName ORDER BY Date_ DESC)[OFFSET(0)] as AccountName, ARRAY_AGG(CampaignName_ ORDER BY Date_ DESC)[OFFSET(0)] as CampaignName, ARRAY_AGG(CampaignImpressionsYesterday ORDER BY Date_ DESC)[OFFSET(0)] as CampaignImpressionsYesterday, ARRAY_AGG(CampaignClicksYesterday ORDER BY Date_ DESC)[OFFSET(0)] as CampaignClicksYesterday, ARRAY_AGG(CampaignConversionsYesterday ORDER BY Date_ DESC)[OFFSET(0)] as CampaignConversionsYesterday, ARRAY_AGG(CampaignCostAverageYesterday ORDER BY Date_ DESC)[OFFSET(0)] as CampaignCostAverageYesterday, ARRAY_AGG(CampaignCostAverageYesterdayIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as CampaignCostAverageYesterdayIncludingFees, ARRAY_AGG(CampaignAdvertisingChannelType ORDER BY Date_ DESC)[OFFSET(0)] as CampaignAdvertisingChannelType,

      FROM `databeat-client-014`.databeat.campaign_summary 

      WHERE DATE(Date_) BETWEEN PARSE_DATE('%Y%m%d', @DS_START_DATE) AND PARSE_DATE('%Y%m%d', @DS_END_DATE) AND Month_ BETWEEN DATE_TRUNC(PARSE_DATE('%Y%m%d', @DS_START_DATE), MONTH) AND DATE_TRUNC(PARSE_DATE('%Y%m%d', @DS_END_DATE), MONTH) 

      GROUP BY Month_, Date_, ServiceId, ServiceName, ServiceNameJA, ServiceAccountId, CampaignId_ ) 

      GROUP BY ServiceId, ServiceName, ServiceNameJA, ServiceAccountId,AccountName, CampaignName, CampaignId_, CampaignAdvertisingChannelType, Month_
      このSQLで出力できる内容は下記の通りです。
      • Month →月
      • ServiceId → サービスID
      • ServiceName → サービス名
      • ServiceNameJA → サービス名（日本語）
      • ServiceAccountId → サービスアカウントID
      • AccountName → アカウント名
      • CampaignName → キャンペーン名
      • CampaignAdvertisingChannelType → キャンペーンの広告チャネル
      • Impressions → 表示回数
      • Clicks → クリック数
      • Conversions → コンバージョン数
      • Cost → 費用
      • CostIncludingFees → （手数料込）費用

      ⑧全てのプロモーションを一括で出力する（キャンペーン&日別）

      ※ 複数のプロモーションに同一のアカウントが紐付いていても重複することなく出力できます。

      SELECT Month_ as Month, DATE(Date_) as Date, ServiceId, ServiceName, ServiceNameJA, ServiceAccountId,AccountName, CampaignName, CampaignAdvertisingChannelType, SUM(CampaignImpressionsYesterday) as Impressions, SUM(CampaignClicksYesterday) as Clicks, SUM(CampaignConversionsYesterday) as Conversions, SUM(CampaignCostAverageYesterday) as Cost, SUM(CampaignCostAverageYesterdayIncludingFees) as CostIncludingFees 

      FROM ( SELECT Month_, Date_, ServiceId, ServiceName, ServiceNameJA, ServiceAccountId, CampaignId_,ARRAY_AGG(AccountName ORDER BY Date_ DESC)[OFFSET(0)] as AccountName, ARRAY_AGG(CampaignName_ ORDER BY Date_ DESC)[OFFSET(0)] as CampaignName, ARRAY_AGG(CampaignImpressionsYesterday ORDER BY Date_ DESC)[OFFSET(0)] as CampaignImpressionsYesterday, ARRAY_AGG(CampaignClicksYesterday ORDER BY Date_ DESC)[OFFSET(0)] as CampaignClicksYesterday, ARRAY_AGG(CampaignConversionsYesterday ORDER BY Date_ DESC)[OFFSET(0)] as CampaignConversionsYesterday, ARRAY_AGG(CampaignCostAverageYesterday ORDER BY Date_ DESC)[OFFSET(0)] as CampaignCostAverageYesterday, ARRAY_AGG(CampaignCostAverageYesterdayIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as CampaignCostAverageYesterdayIncludingFees, ARRAY_AGG(CampaignAdvertisingChannelType ORDER BY Date_ DESC)[OFFSET(0)] as CampaignAdvertisingChannelType, 

      FROM `databeat-client-014`.databeat.campaign_summary 

      WHERE DATE(Date_) BETWEEN PARSE_DATE('%Y%m%d', @DS_START_DATE) AND PARSE_DATE('%Y%m%d', @DS_END_DATE) AND Month_ BETWEEN DATE_TRUNC(PARSE_DATE('%Y%m%d', @DS_START_DATE), MONTH) AND DATE_TRUNC(PARSE_DATE('%Y%m%d', @DS_END_DATE), MONTH) 

      GROUP BY Month_, Date_, ServiceId, ServiceName, ServiceNameJA, ServiceAccountId, CampaignId_ )

      GROUP BY ServiceId, ServiceName, ServiceNameJA, ServiceAccountId,AccountName, CampaignName, CampaignId_, CampaignAdvertisingChannelType, Month_, Date_
      このSQLで出力できる内容は下記の通りです。
      • Month →月
      • Date → 日
      • ServiceId → サービスID
      • ServiceName → サービス名
      • ServiceNameJA → サービス名（日本語）
      • ServiceAccountId → サービスアカウントID
      • AccountName → アカウント名
      • CampaignName → キャンペーン名
      • CampaignAdvertisingChannelType → キャンペーンの広告チャネル
      • Impressions → 表示回数
      • Clicks → クリック数
      • Conversions → コンバージョン数
      • Cost → 費用
      • CostIncludingFees → （手数料込）費用