全てのプロモーションの日々の数値管理や予算管理をGoogle データポータルを使って行うことができます。

一つのSQLを設定するだけで、全てのプロモーションを横断してデータを閲覧することができます。また、プロモーションが新規追加された場合でも、自動的にデータが反映されます。

Databeat Explore 全プロモーション数値管理テンプレート

https://datastudio.google.com/open/1LyT5bUeqzL0Y_EL7Q3H02QWq6YAGCBKW

全プロモーション数値管理用SQL

このSQLをGoogle データポータルのカスタムクエリに貼り付けてください。

※ 後部の行の「FROM `**********`.databeat.campaign_summary」の「**********」の部分を自社のBigQueryのプロジェクトIDに変更してご利用ください。

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(ServiceAccountBudget ORDER BY Date_ DESC)[OFFSET(0)] as ServiceAccountBudget, ARRAY_AGG(ServiceAccountBalance ORDER BY Date_ DESC)[OFFSET(0)] as ServiceAccountBalance, ARRAY_AGG(PromotionBudget ORDER BY Date_ DESC)[OFFSET(0)] as PromotionBudget, ARRAY_AGG(PromotionBudgetIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as PromotionBudgetIncludingFees, ARRAY_AGG(PromotionRemainingBudget ORDER BY Date_ DESC)[OFFSET(0)] as PromotionRemainingBudget, ARRAY_AGG(PromotionRemainingBudgetIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as PromotionRemainingBudgetIncludingFees, ARRAY_AGG(PromotionBudgetProgressRate ORDER BY Date_ DESC)[OFFSET(0)] as PromotionBudgetProgressRate, ARRAY_AGG(PromotionBudgetProgressRateIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as PromotionBudgetProgressRateIncludingFees, 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(PromotionImpressions ORDER BY Date_ DESC)[OFFSET(0)] as PromotionImpressions, ARRAY_AGG(PromotionClicks ORDER BY Date_ DESC)[OFFSET(0)] as PromotionClicks, ARRAY_AGG(PromotionConversions ORDER BY Date_ DESC)[OFFSET(0)] as PromotionConversions, ARRAY_AGG(PromotionConversionValue ORDER BY Date_ DESC)[OFFSET(0)] as PromotionConversionValue, ARRAY_AGG(PromotionAllConversions ORDER BY Date_ DESC)[OFFSET(0)] as PromotionAllConversions, ARRAY_AGG(PromotionAllConversionValue ORDER BY Date_ DESC)[OFFSET(0)] as PromotionAllConversionValue, ARRAY_AGG(PromotionCost ORDER BY Date_ DESC)[OFFSET(0)] as PromotionCost, ARRAY_AGG(PromotionCostAverageYesterday ORDER BY Date_ DESC)[OFFSET(0)] as PromotionCostAverageYesterday, ARRAY_AGG(PromotionCostAverageLastThreeDays ORDER BY Date_ DESC)[OFFSET(0)] as PromotionCostAverageLastThreeDays, ARRAY_AGG(PromotionCostAverageLastSevenDays ORDER BY Date_ DESC)[OFFSET(0)] as PromotionCostAverageLastSevenDays, ARRAY_AGG(PromotionCostAverageThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as PromotionCostAverageThisPeriod, ARRAY_AGG(PromotionCostIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as PromotionCostIncludingFees, ARRAY_AGG(PromotionCostAverageYesterdayIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as PromotionCostAverageYesterdayIncludingFees, ARRAY_AGG(PromotionCostAverageLastThreeDaysIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as PromotionCostAverageLastThreeDaysIncludingFees, ARRAY_AGG(PromotionCostAverageLastSevenDaysIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as PromotionCostAverageLastSevenDaysIncludingFees, ARRAY_AGG(PromotionCostAverageThisPeriodIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as PromotionCostAverageThisPeriodIncludingFees, ARRAY_AGG(EstimatedPromotionCostUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedPromotionCostUsingYesterday, ARRAY_AGG(EstimatedPromotionCostUsingLastThreeDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedPromotionCostUsingLastThreeDaysAverage, ARRAY_AGG(EstimatedPromotionCostUsingLastSevenDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedPromotionCostUsingLastSevenDaysAverage, ARRAY_AGG(EstimatedPromotionCostUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedPromotionCostUsingThisPeriod, ARRAY_AGG(EstimatedPromotionCostUsingYesterdayIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedPromotionCostUsingYesterdayIncludingFees, ARRAY_AGG(EstimatedPromotionCostUsingLastThreeDaysAverageIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedPromotionCostUsingLastThreeDaysAverageIncludingFees, ARRAY_AGG(EstimatedPromotionCostUsingLastSevenDaysAverageIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedPromotionCostUsingLastSevenDaysAverageIncludingFees, ARRAY_AGG(EstimatedPromotionCostUsingThisPeriodIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedPromotionCostUsingThisPeriodIncludingFees, ARRAY_AGG(PromotionConversionsYesterday ORDER BY Date_ DESC)[OFFSET(0)] as PromotionConversionsYesterday, ARRAY_AGG(PromotionConversionsLastThreeDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as PromotionConversionsLastThreeDaysAverage, ARRAY_AGG(PromotionConversionsLastSevenDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as PromotionConversionsLastSevenDaysAverage, ARRAY_AGG(PromotionConversionsThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as PromotionConversionsThisPeriod, ARRAY_AGG(EstimatedPromotionConversionsUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedPromotionConversionsUsingYesterday, ARRAY_AGG(EstimatedPromotionConversionsUsingLastThreeDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedPromotionConversionsUsingLastThreeDaysAverage, ARRAY_AGG(EstimatedPromotionConversionsUsingLastSevenDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedPromotionConversionsUsingLastSevenDaysAverage, ARRAY_AGG(EstimatedPromotionConversionsUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedPromotionConversionsUsingThisPeriod, ARRAY_AGG(PromotionConversionValueYesterday ORDER BY Date_ DESC)[OFFSET(0)] as PromotionConversionValueYesterday, ARRAY_AGG(PromotionConversionValueLastThreeDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as PromotionConversionValueLastThreeDaysAverage, ARRAY_AGG(PromotionConversionValueLastSevenDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as PromotionConversionValueLastSevenDaysAverage, ARRAY_AGG(PromotionConversionValueThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as PromotionConversionValueThisPeriod, ARRAY_AGG(EstimatedPromotionConversionValueUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedPromotionConversionValueUsingYesterday, ARRAY_AGG(EstimatedPromotionConversionValueUsingLastThreeDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedPromotionConversionValueUsingLastThreeDaysAverage, ARRAY_AGG(EstimatedPromotionConversionValueUsingLastSevenDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedPromotionConversionValueUsingLastSevenDaysAverage, ARRAY_AGG(EstimatedPromotionConversionValueUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedPromotionConversionValueUsingThisPeriod, ARRAY_AGG(PromotionAllConversionsYesterday ORDER BY Date_ DESC)[OFFSET(0)] as PromotionAllConversionsYesterday, ARRAY_AGG(PromotionAllConversionsLastThreeDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as PromotionAllConversionsLastThreeDaysAverage, ARRAY_AGG(PromotionAllConversionsLastSevenDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as PromotionAllConversionsLastSevenDaysAverage, ARRAY_AGG(PromotionAllConversionsThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as PromotionAllConversionsThisPeriod, ARRAY_AGG(EstimatedPromotionAllConversionsUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedPromotionAllConversionsUsingYesterday, ARRAY_AGG(EstimatedPromotionAllConversionsUsingLastThreeDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedPromotionAllConversionsUsingLastThreeDaysAverage, ARRAY_AGG(EstimatedPromotionAllConversionsUsingLastSevenDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedPromotionAllConversionsUsingLastSevenDaysAverage, ARRAY_AGG(EstimatedPromotionAllConversionsUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedPromotionAllConversionsUsingThisPeriod, ARRAY_AGG(PromotionAllConversionValueYesterday ORDER BY Date_ DESC)[OFFSET(0)] as PromotionAllConversionValueYesterday, ARRAY_AGG(PromotionAllConversionValueLastThreeDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as PromotionAllConversionValueLastThreeDaysAverage, ARRAY_AGG(PromotionAllConversionValueLastSevenDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as PromotionAllConversionValueLastSevenDaysAverage, ARRAY_AGG(PromotionAllConversionValueThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as PromotionAllConversionValueThisPeriod, ARRAY_AGG(EstimatedPromotionAllConversionValueUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedPromotionAllConversionValueUsingYesterday, ARRAY_AGG(EstimatedPromotionAllConversionValueUsingLastThreeDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedPromotionAllConversionValueUsingLastThreeDaysAverage, ARRAY_AGG(EstimatedPromotionAllConversionValueUsingLastSevenDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedPromotionAllConversionValueUsingLastSevenDaysAverage, ARRAY_AGG(EstimatedPromotionAllConversionValueUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedPromotionAllConversionValueUsingThisPeriod, ARRAY_AGG(PromotionClicksYesterday ORDER BY Date_ DESC)[OFFSET(0)] as PromotionClicksYesterday, ARRAY_AGG(PromotionClicksLastThreeDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as PromotionClicksLastThreeDaysAverage, ARRAY_AGG(PromotionClicksLastSevenDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as PromotionClicksLastSevenDaysAverage, ARRAY_AGG(PromotionClicksThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as PromotionClicksThisPeriod, ARRAY_AGG(EstimatedPromotionClicksUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedPromotionClicksUsingYesterday, ARRAY_AGG(EstimatedPromotionClicksUsingLastThreeDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedPromotionClicksUsingLastThreeDaysAverage, ARRAY_AGG(EstimatedPromotionClicksUsingLastSevenDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedPromotionClicksUsingLastSevenDaysAverage, ARRAY_AGG(EstimatedPromotionClicksUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedPromotionClicksUsingThisPeriod, ARRAY_AGG(PromotionImpressionsYesterday ORDER BY Date_ DESC)[OFFSET(0)] as PromotionImpressionsYesterday, ARRAY_AGG(PromotionImpressionsLastThreeDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as PromotionImpressionsLastThreeDaysAverage, ARRAY_AGG(PromotionImpressionsLastSevenDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as PromotionImpressionsLastSevenDaysAverage, ARRAY_AGG(PromotionImpressionsThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as PromotionImpressionsThisPeriod, ARRAY_AGG(EstimatedPromotionImpressionsUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedPromotionImpressionsUsingYesterday, ARRAY_AGG(EstimatedPromotionImpressionsUsingLastThreeDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedPromotionImpressionsUsingLastThreeDaysAverage, ARRAY_AGG(EstimatedPromotionImpressionsUsingLastSevenDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedPromotionImpressionsUsingLastSevenDaysAverage, ARRAY_AGG(EstimatedPromotionImpressionsUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedPromotionImpressionsUsingThisPeriod, ARRAY_AGG(TargetPromotionImpressions ORDER BY Date_ DESC)[OFFSET(0)] as TargetPromotionImpressions, ARRAY_AGG(TargetPromotionImpressionsProgressRate ORDER BY Date_ DESC)[OFFSET(0)] as TargetPromotionImpressionsProgressRate, ARRAY_AGG(TargetPromotionClicks ORDER BY Date_ DESC)[OFFSET(0)] as TargetPromotionClicks, ARRAY_AGG(TargetPromotionClicksProgressRate ORDER BY Date_ DESC)[OFFSET(0)] as TargetPromotionClicksProgressRate, ARRAY_AGG(TargetPromotionCtr ORDER BY Date_ DESC)[OFFSET(0)] as TargetPromotionCtr, ARRAY_AGG(TargetPromotionCtrDiff ORDER BY Date_ DESC)[OFFSET(0)] as TargetPromotionCtrDiff, ARRAY_AGG(TargetPromotionCostPerClick ORDER BY Date_ DESC)[OFFSET(0)] as TargetPromotionCostPerClick, ARRAY_AGG(TargetPromotionCostPerClickDiff ORDER BY Date_ DESC)[OFFSET(0)] as TargetPromotionCostPerClickDiff, ARRAY_AGG(TargetPromotionCostPerClickIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as TargetPromotionCostPerClickIncludingFees, ARRAY_AGG(TargetPromotionCostPerClickIncludingFeesDiff ORDER BY Date_ DESC)[OFFSET(0)] as TargetPromotionCostPerClickIncludingFeesDiff, ARRAY_AGG(TargetPromotionConversions ORDER BY Date_ DESC)[OFFSET(0)] as TargetPromotionConversions, ARRAY_AGG(TargetPromotionConversionsProgressRate ORDER BY Date_ DESC)[OFFSET(0)] as TargetPromotionConversionsProgressRate, ARRAY_AGG(TargetPromotionConversionRate ORDER BY Date_ DESC)[OFFSET(0)] as TargetPromotionConversionRate, ARRAY_AGG(TargetPromotionConversionRateDiff ORDER BY Date_ DESC)[OFFSET(0)] as TargetPromotionConversionRateDiff, ARRAY_AGG(TargetPromotionAllConversions ORDER BY Date_ DESC)[OFFSET(0)] as TargetPromotionAllConversions, ARRAY_AGG(TargetPromotionAllConversionsProgressRate ORDER BY Date_ DESC)[OFFSET(0)] as TargetPromotionAllConversionsProgressRate, ARRAY_AGG(TargetPromotionAllConversionRate ORDER BY Date_ DESC)[OFFSET(0)] as TargetPromotionAllConversionRate, ARRAY_AGG(TargetPromotionAllConversionRateDiff ORDER BY Date_ DESC)[OFFSET(0)] as TargetPromotionAllConversionRateDiff, ARRAY_AGG(TargetPromotionCostPerConversion ORDER BY Date_ DESC)[OFFSET(0)] as TargetPromotionCostPerConversion, ARRAY_AGG(TargetPromotionCostPerConversionDiff ORDER BY Date_ DESC)[OFFSET(0)] as TargetPromotionCostPerConversionDiff, ARRAY_AGG(TargetPromotionCostPerConversionIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as TargetPromotionCostPerConversionIncludingFees, ARRAY_AGG(TargetPromotionCostPerConversionIncludingFeesDiff ORDER BY Date_ DESC)[OFFSET(0)] as TargetPromotionCostPerConversionIncludingFeesDiff, ARRAY_AGG(TargetPromotionCostPerAllConversion ORDER BY Date_ DESC)[OFFSET(0)] as TargetPromotionCostPerAllConversion, ARRAY_AGG(TargetPromotionCostPerAllConversionDiff ORDER BY Date_ DESC)[OFFSET(0)] as TargetPromotionCostPerAllConversionDiff, ARRAY_AGG(TargetPromotionCostPerAllConversionIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as TargetPromotionCostPerAllConversionIncludingFees, ARRAY_AGG(TargetPromotionCostPerAllConversionIncludingFeesDiff ORDER BY Date_ DESC)[OFFSET(0)] as TargetPromotionCostPerAllConversionIncludingFeesDiff, ARRAY_AGG(TargetPromotionConversionValue ORDER BY Date_ DESC)[OFFSET(0)] as TargetPromotionConversionValue, ARRAY_AGG(TargetPromotionConversionValueProgressRate ORDER BY Date_ DESC)[OFFSET(0)] as TargetPromotionConversionValueProgressRate, ARRAY_AGG(TargetPromotionAllConversionValue ORDER BY Date_ DESC)[OFFSET(0)] as TargetPromotionAllConversionValue, ARRAY_AGG(TargetPromotionAllConversionValueProgressRate ORDER BY Date_ DESC)[OFFSET(0)] as TargetPromotionAllConversionValueProgressRate, ARRAY_AGG(TargetPromotionConversionValuePerCost ORDER BY Date_ DESC)[OFFSET(0)] as TargetPromotionConversionValuePerCost, ARRAY_AGG(TargetPromotionConversionValuePerCostDiff ORDER BY Date_ DESC)[OFFSET(0)] as TargetPromotionConversionValuePerCostDiff, ARRAY_AGG(TargetPromotionConversionValuePerCostIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as TargetPromotionConversionValuePerCostIncludingFees, ARRAY_AGG(TargetPromotionConversionValuePerCostIncludingFeesDiff ORDER BY Date_ DESC)[OFFSET(0)] as TargetPromotionConversionValuePerCostIncludingFeesDiff, ARRAY_AGG(TargetPromotionAllConversionValuePerCost ORDER BY Date_ DESC)[OFFSET(0)] as TargetPromotionAllConversionValuePerCost, ARRAY_AGG(TargetPromotionAllConversionValuePerCostDiff ORDER BY Date_ DESC)[OFFSET(0)] as TargetPromotionAllConversionValuePerCostDiff, ARRAY_AGG(TargetPromotionAllConversionValuePerCostIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as TargetPromotionAllConversionValuePerCostIncludingFees, ARRAY_AGG(TargetPromotionAllConversionValuePerCostIncludingFeesDiff ORDER BY Date_ DESC)[OFFSET(0)] as TargetPromotionAllConversionValuePerCostIncludingFeesDiff, 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(AccountCostAverageLastThreeDays ORDER BY Date_ DESC)[OFFSET(0)] as AccountCostAverageLastThreeDays, ARRAY_AGG(AccountCostAverageLastSevenDays ORDER BY Date_ DESC)[OFFSET(0)] as AccountCostAverageLastSevenDays, 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(AccountCostAverageLastThreeDaysIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as AccountCostAverageLastThreeDaysIncludingFees, ARRAY_AGG(AccountCostAverageLastSevenDaysIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as AccountCostAverageLastSevenDaysIncludingFees, 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(EstimatedAccountCostUsingLastThreeDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountCostUsingLastThreeDaysAverage, ARRAY_AGG(EstimatedAccountCostUsingLastSevenDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountCostUsingLastSevenDaysAverage, ARRAY_AGG(EstimatedAccountCostUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountCostUsingThisPeriod, ARRAY_AGG(EstimatedAccountCostUsingYesterdayIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountCostUsingYesterdayIncludingFees, ARRAY_AGG(EstimatedAccountCostUsingLastThreeDaysAverageIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountCostUsingLastThreeDaysAverageIncludingFees, ARRAY_AGG(EstimatedAccountCostUsingLastSevenDaysAverageIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountCostUsingLastSevenDaysAverageIncludingFees, ARRAY_AGG(EstimatedAccountCostUsingThisPeriodIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountCostUsingThisPeriodIncludingFees, ARRAY_AGG(AccountConversionsYesterday ORDER BY Date_ DESC)[OFFSET(0)] as AccountConversionsYesterday, ARRAY_AGG(AccountConversionsLastThreeDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as AccountConversionsLastThreeDaysAverage, ARRAY_AGG(AccountConversionsLastSevenDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as AccountConversionsLastSevenDaysAverage, ARRAY_AGG(AccountConversionsThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as AccountConversionsThisPeriod, ARRAY_AGG(EstimatedAccountConversionsUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountConversionsUsingYesterday, ARRAY_AGG(EstimatedAccountConversionsUsingLastThreeDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountConversionsUsingLastThreeDaysAverage, ARRAY_AGG(EstimatedAccountConversionsUsingLastSevenDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountConversionsUsingLastSevenDaysAverage, ARRAY_AGG(EstimatedAccountConversionsUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountConversionsUsingThisPeriod, ARRAY_AGG(AccountConversionValueYesterday ORDER BY Date_ DESC)[OFFSET(0)] as AccountConversionValueYesterday, ARRAY_AGG(AccountConversionValueLastThreeDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as AccountConversionValueLastThreeDaysAverage, ARRAY_AGG(AccountConversionValueLastSevenDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as AccountConversionValueLastSevenDaysAverage, ARRAY_AGG(AccountConversionValueThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as AccountConversionValueThisPeriod, ARRAY_AGG(EstimatedAccountConversionValueUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountConversionValueUsingYesterday, ARRAY_AGG(EstimatedAccountConversionValueUsingLastThreeDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountConversionValueUsingLastThreeDaysAverage, ARRAY_AGG(EstimatedAccountConversionValueUsingLastSevenDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountConversionValueUsingLastSevenDaysAverage, ARRAY_AGG(EstimatedAccountConversionValueUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountConversionValueUsingThisPeriod, ARRAY_AGG(AccountAllConversionsYesterday ORDER BY Date_ DESC)[OFFSET(0)] as AccountAllConversionsYesterday, ARRAY_AGG(AccountAllConversionsLastThreeDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as AccountAllConversionsLastThreeDaysAverage, ARRAY_AGG(AccountAllConversionsLastSevenDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as AccountAllConversionsLastSevenDaysAverage, ARRAY_AGG(AccountAllConversionsThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as AccountAllConversionsThisPeriod, ARRAY_AGG(EstimatedAccountAllConversionsUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountAllConversionsUsingYesterday, ARRAY_AGG(EstimatedAccountAllConversionsUsingLastThreeDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountAllConversionsUsingLastThreeDaysAverage, ARRAY_AGG(EstimatedAccountAllConversionsUsingLastSevenDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountAllConversionsUsingLastSevenDaysAverage, ARRAY_AGG(EstimatedAccountAllConversionsUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountAllConversionsUsingThisPeriod, ARRAY_AGG(AccountAllConversionValueYesterday ORDER BY Date_ DESC)[OFFSET(0)] as AccountAllConversionValueYesterday, ARRAY_AGG(AccountAllConversionValueLastThreeDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as AccountAllConversionValueLastThreeDaysAverage, ARRAY_AGG(AccountAllConversionValueLastSevenDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as AccountAllConversionValueLastSevenDaysAverage, ARRAY_AGG(AccountAllConversionValueThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as AccountAllConversionValueThisPeriod, ARRAY_AGG(EstimatedAccountAllConversionValueUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountAllConversionValueUsingYesterday, ARRAY_AGG(EstimatedAccountAllConversionValueUsingLastThreeDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountAllConversionValueUsingLastThreeDaysAverage, ARRAY_AGG(EstimatedAccountAllConversionValueUsingLastSevenDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountAllConversionValueUsingLastSevenDaysAverage, ARRAY_AGG(EstimatedAccountAllConversionValueUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountAllConversionValueUsingThisPeriod, ARRAY_AGG(AccountClicksYesterday ORDER BY Date_ DESC)[OFFSET(0)] as AccountClicksYesterday, ARRAY_AGG(AccountClicksLastThreeDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as AccountClicksLastThreeDaysAverage, ARRAY_AGG(AccountClicksLastSevenDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as AccountClicksLastSevenDaysAverage, ARRAY_AGG(AccountClicksThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as AccountClicksThisPeriod, ARRAY_AGG(EstimatedAccountClicksUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountClicksUsingYesterday, ARRAY_AGG(EstimatedAccountClicksUsingLastThreeDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountClicksUsingLastThreeDaysAverage, ARRAY_AGG(EstimatedAccountClicksUsingLastSevenDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountClicksUsingLastSevenDaysAverage, ARRAY_AGG(EstimatedAccountClicksUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountClicksUsingThisPeriod, ARRAY_AGG(AccountImpressionsYesterday ORDER BY Date_ DESC)[OFFSET(0)] as AccountImpressionsYesterday, ARRAY_AGG(AccountImpressionsLastThreeDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as AccountImpressionsLastThreeDaysAverage, ARRAY_AGG(AccountImpressionsLastSevenDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as AccountImpressionsLastSevenDaysAverage, ARRAY_AGG(AccountImpressionsThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as AccountImpressionsThisPeriod, ARRAY_AGG(EstimatedAccountImpressionsUsingYesterday ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountImpressionsUsingYesterday, ARRAY_AGG(EstimatedAccountImpressionsUsingLastThreeDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountImpressionsUsingLastThreeDaysAverage, ARRAY_AGG(EstimatedAccountImpressionsUsingLastSevenDaysAverage ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountImpressionsUsingLastSevenDaysAverage, ARRAY_AGG(EstimatedAccountImpressionsUsingThisPeriod ORDER BY Date_ DESC)[OFFSET(0)] as EstimatedAccountImpressionsUsingThisPeriod, ARRAY_AGG(TargetAccountImpressions ORDER BY Date_ DESC)[OFFSET(0)] as TargetAccountImpressions, ARRAY_AGG(TargetAccountImpressionsProgressRate ORDER BY Date_ DESC)[OFFSET(0)] as TargetAccountImpressionsProgressRate, ARRAY_AGG(TargetAccountClicks ORDER BY Date_ DESC)[OFFSET(0)] as TargetAccountClicks, ARRAY_AGG(TargetAccountClicksProgressRate ORDER BY Date_ DESC)[OFFSET(0)] as TargetAccountClicksProgressRate, ARRAY_AGG(TargetAccountCtr ORDER BY Date_ DESC)[OFFSET(0)] as TargetAccountCtr, ARRAY_AGG(TargetAccountCtrDiff ORDER BY Date_ DESC)[OFFSET(0)] as TargetAccountCtrDiff, ARRAY_AGG(TargetAccountCostPerClick ORDER BY Date_ DESC)[OFFSET(0)] as TargetAccountCostPerClick, ARRAY_AGG(TargetAccountCostPerClickDiff ORDER BY Date_ DESC)[OFFSET(0)] as TargetAccountCostPerClickDiff, ARRAY_AGG(TargetAccountCostPerClickIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as TargetAccountCostPerClickIncludingFees, ARRAY_AGG(TargetAccountCostPerClickIncludingFeesDiff ORDER BY Date_ DESC)[OFFSET(0)] as TargetAccountCostPerClickIncludingFeesDiff, ARRAY_AGG(TargetAccountConversions ORDER BY Date_ DESC)[OFFSET(0)] as TargetAccountConversions, ARRAY_AGG(TargetAccountConversionsProgressRate ORDER BY Date_ DESC)[OFFSET(0)] as TargetAccountConversionsProgressRate, ARRAY_AGG(TargetAccountConversionRate ORDER BY Date_ DESC)[OFFSET(0)] as TargetAccountConversionRate, ARRAY_AGG(TargetAccountConversionRateDiff ORDER BY Date_ DESC)[OFFSET(0)] as TargetAccountConversionRateDiff, ARRAY_AGG(TargetAccountAllConversions ORDER BY Date_ DESC)[OFFSET(0)] as TargetAccountAllConversions, ARRAY_AGG(TargetAccountAllConversionsProgressRate ORDER BY Date_ DESC)[OFFSET(0)] as TargetAccountAllConversionsProgressRate, ARRAY_AGG(TargetAccountAllConversionRate ORDER BY Date_ DESC)[OFFSET(0)] as TargetAccountAllConversionRate, ARRAY_AGG(TargetAccountAllConversionRateDiff ORDER BY Date_ DESC)[OFFSET(0)] as TargetAccountAllConversionRateDiff, ARRAY_AGG(TargetAccountCostPerConversion ORDER BY Date_ DESC)[OFFSET(0)] as TargetAccountCostPerConversion, ARRAY_AGG(TargetAccountCostPerConversionDiff ORDER BY Date_ DESC)[OFFSET(0)] as TargetAccountCostPerConversionDiff, ARRAY_AGG(TargetAccountCostPerConversionIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as TargetAccountCostPerConversionIncludingFees, ARRAY_AGG(TargetAccountCostPerConversionIncludingFeesDiff ORDER BY Date_ DESC)[OFFSET(0)] as TargetAccountCostPerConversionIncludingFeesDiff, ARRAY_AGG(TargetAccountCostPerAllConversion ORDER BY Date_ DESC)[OFFSET(0)] as TargetAccountCostPerAllConversion, ARRAY_AGG(TargetAccountCostPerAllConversionDiff ORDER BY Date_ DESC)[OFFSET(0)] as TargetAccountCostPerAllConversionDiff, ARRAY_AGG(TargetAccountCostPerAllConversionIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as TargetAccountCostPerAllConversionIncludingFees, ARRAY_AGG(TargetAccountCostPerAllConversionIncludingFeesDiff ORDER BY Date_ DESC)[OFFSET(0)] as TargetAccountCostPerAllConversionIncludingFeesDiff, ARRAY_AGG(TargetAccountConversionValue ORDER BY Date_ DESC)[OFFSET(0)] as TargetAccountConversionValue, ARRAY_AGG(TargetAccountConversionValueProgressRate ORDER BY Date_ DESC)[OFFSET(0)] as TargetAccountConversionValueProgressRate, ARRAY_AGG(TargetAccountAllConversionValue ORDER BY Date_ DESC)[OFFSET(0)] as TargetAccountAllConversionValue, ARRAY_AGG(TargetAccountAllConversionValueProgressRate ORDER BY Date_ DESC)[OFFSET(0)] as TargetAccountAllConversionValueProgressRate, ARRAY_AGG(TargetAccountConversionValuePerCost ORDER BY Date_ DESC)[OFFSET(0)] as TargetAccountConversionValuePerCost, ARRAY_AGG(TargetAccountConversionValuePerCostDiff ORDER BY Date_ DESC)[OFFSET(0)] as TargetAccountConversionValuePerCostDiff, ARRAY_AGG(TargetAccountConversionValuePerCostIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as TargetAccountConversionValuePerCostIncludingFees, ARRAY_AGG(TargetAccountConversionValuePerCostIncludingFeesDiff ORDER BY Date_ DESC)[OFFSET(0)] as TargetAccountConversionValuePerCostIncludingFeesDiff, ARRAY_AGG(TargetAccountAllConversionValuePerCost ORDER BY Date_ DESC)[OFFSET(0)] as TargetAccountAllConversionValuePerCost, ARRAY_AGG(TargetAccountAllConversionValuePerCostDiff ORDER BY Date_ DESC)[OFFSET(0)] as TargetAccountAllConversionValuePerCostDiff, ARRAY_AGG(TargetAccountAllConversionValuePerCostIncludingFees ORDER BY Date_ DESC)[OFFSET(0)] as TargetAccountAllConversionValuePerCostIncludingFees, ARRAY_AGG(TargetAccountAllConversionValuePerCostIncludingFeesDiff ORDER BY Date_ DESC)[OFFSET(0)] as TargetAccountAllConversionValuePerCostIncludingFeesDiff,PromotionNote,AccountNote
FROM `**********`.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

全プロモーション数値管理用SQLに含まれる指標

このSQLに含まれる指標の一覧は、下記URLからご確認ください。

https://docs.google.com/spreadsheets/d/1MTQa8fPARxEpBPfhvba_R1AMh2W1frctYKFv0gW4lXI/edit?usp=sharing

※ ServiceAccountBudget(サービスアカウント予算)とServiceAccountBalance(サービスアカウント残高)は、アカウントの階層にドリルダウンしたときのみご利用いただけます。

Databeat Explore 全プロモーション数値管理テンプレートを利用する方法

STEP 1:Google データポータルにデータソースを登録する

1. Google データポータルを開いて、Google アカウントでログインしてください。

2. 画面左上の[ 作成 ]ボタンをクリックして、[ データソース ]を選択してください。

3. 画面左上の「無題のデータソース」を変更して、データソースに名前を付けてください。
※ 例:「全プロモーション数値管理」

4. Google コネクタから[ BigQuery ]を選択してください。

5. 画面左のメニューから「カスタムクエリ」を選択してください。

6. プロジェクトの中から対象のプロジェクトを選択してください。

7. カスタムクエリの入力欄に「全プロモーション数値管理用SQL」を貼り付けてください。

※ 後部の行の「FROM `**********`.databeat.campaign_summary」の「**********」の部分を自社のBigQueryのプロジェクトIDに変更してご利用ください。

8. 「Enable date range parameters」にチェックを入れてください。

9. 画面右上の[ 接続 ]ボタンをクリックしてください。

10. 「パラメーターの共有を許可しますか?」画面が表示されますので、[ 許可 ]ボタンをクリックしてください。

11. フィールド編集画面で「Month_」のタイプを「日付」に変更してください。

12. これでデータソースの登録は完了です。

STEP 2:Databeat Explore 全プロモーション数値管理テンプレートを複製する

1. 「Databeat Explore 全プロモーション数値管理テンプレート」を開いてください。

https://datastudio.google.com/open/1LyT5bUeqzL0Y_EL7Q3H02QWq6YAGCBKW

2. 画面右上の[ このレポートのコピーを作成 ]ボタンをクリックしてください。

3. 新しいデータソースにSTEP 1で作成したデータソースを選択して、[ レポートをコピー ]をクリックしてください。

4. これでDatabeat Explore 全プロモーション数値管理テンプレートの利用設定は完了です。

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