DoubleClick Campaign Manager の転送

BigQuery Data Transfer Service for DoubleClick を使用すると、繰り返し実施する必要がある DoubleClick のレポートデータの読み込みジョブのスケジュールと管理を自動化できます。

サポートされるレポート

現在、DoubleClick Campaign Manager 用の BigQuery Data Transfer Service では、次のレポート オプションがサポートされています。

DoubleClick Campaign Manager レポートがどのように BigQuery のテーブルとビューに変換されるかについては、DoubleClick Campaign Manager レポートの変換をご覧ください。

レポート オプション サポート
サポートされている API バージョン 2017 年 6 月 26 日
スケジュール

作成時間に基づいて、8 時間ごと。

設定不可

ウィンドウの更新

過去 2 日間

設定不可

最大バックフィル期間

過去 60 日間

DoubleClick Campaign Manager では、データ転送ファイルが最大 60 日間保持されます。60 日を超えるファイルは DoubleClick Campaign Manager によって削除されます。

始める前に

DoubleClick Campaign Manager の転送を作成する前に:

  • BigQuery Data Transfer Service を有効にするために必要なすべての操作が完了していることを確認します。
  • DoubleClick データを保存するための BigQuery データセットを作成します。
  • DoubleClick Campaign Manager Data Transfer v2(DCM DTv2)ファイルにアクセスできることを確認します。 これらのファイルは DoubleClick チームによって Google Cloud Storage バケットに配信されます。DCM DTv2 ファイルにアクセスするには、DoubleClick Campaign Manager と直接契約しているかどうかによって次のステップが異なります。どちらの場合も、追加料金が適用される場合があります。

    DoubleClick Campaign Managerと契約している場合は、DoubleClick Campaign Manager のサポートに連絡して、DCM DTv2 ファイルを設定してください。

    DoubleClick Campaign Manager と契約していない場合は、代理店またはDoubleClick の販売パートナーが DCM DTv2 ファイルにアクセスできる可能性があります。これらのファイルへのアクセスについては、代理店または販売パートナーにお問い合わせください。

    この手順を完了すると、次のような Google Cloud Storage バケットが表示されます。

    dcdt_-dcm_account1234

    Google Cloud チームには、お客様の代理で DCM DTv2 ファイルを生成したり、アクセスを許可したりする権限がありません。DCM DTv2 ファイルへのアクセスについては、DoubleClick Campaign Manager のサポート、代理店、DoubleClick の販売パートナーにお問い合わせください。

  • 転送を作成するユーザーに、次の必要な権限があることを確認します。
    • DoubleClick Campaign Manager: Google Cloud Storage に保存されている DCM DTv2 ファイルへの読み取りアクセス権。アクセスは、Google Cloud Storage バケットを受け取ったエンティティによって管理されます。
    • Google Cloud Platform: 転送を作成するための bigquery.transfers.update 権限と、ターゲット データセットに対する bigquery.datasets.update 権限。プロジェクト レベルの事前定義 IAM 役割 bigquery.admin には、bigquery.transfers.update 権限と bigquery.datasets.update 権限が含まれています。BigQuery での IAM 役割の詳細については、アクセス制御をご覧ください。
  • BigQuery ウェブ UI を使用して転送を作成する場合は、権限ウィンドウが表示されるように、bigquery.cloud.google.com からのポップアップを許可します。転送を成功させるには、BigQuery Data Transfer Service 権限で DoubleClick でのキャンペーンを管理できるようにする必要があります。
  • 転送実行通知は、現時点ではアルファ版です。Cloud Pub/Sub に対する転送通知を設定する場合は、pubsub.topics.setIamPolicy 権限が必要です。メール通知を設定するだけの場合は、Pub/Sub の権限は必要ありません。詳細については、BigQuery Data Transfer Service の実行通知をご覧ください。

DoubleClick Campaign Manager の転送の設定

DoubleClick Campaign Manager の転送を設定するには、以下が必要です。

  • Cloud Storage バケット: 始める前にで説明されている DCM DTv2 ファイルの Google Cloud Storage バケット URI。バケット名は次のようになります。

      dcdt_-dcm_account1234
    
  • DoubleClick ID: DoubleClick ネットワーク、広告主、または Floodlight の ID。ネットワーク ID は、階層内の親です。

DoubleClick ID を取得するには、Google Cloud Storage ウェブ UI を使用し、DoubleClick Data Transfer Cloud Storage バケットのファイルを確認します。DoubleClick ID は、提供されている Cloud Storage バケット内のファイルを照合するために使用されます。この ID はファイル名に埋め込まれ、Cloud Storage バケット名には埋め込まれません。

例:

  • dcm_account1234_activity_* という名前のファイルでは、ID は 1234 です。
  • dcm_floodlight7890_activity_* という名前のファイルでは、ID は 7890 です。
  • dcm_advertiser567_activity_* という名前のファイルでは、ID は 567 です。

DoubleClick Campaign Manager 用の BigQuery データ転送を作成するには:

ウェブ UI

  1. BigQuery ウェブ UI に移動します。

    BigQuery ウェブ UI に移動

  2. [Transfers] をクリックします。

  3. [Add Transfer] をクリックします。

  4. [New Transfer] ページで、次の操作を行います。

    • [Source] で、[DCM Data Transfer] を選択します。
    • [Destination] で、該当するデータセットを選択します。
    • [Display Name] に転送名(例: My Transfer)を入力します。転送名には、他の転送と簡単に区別できる任意の名前を使用できます。必要であれば、後で修正できます。
    • [Cloud Storage Bucket] に、Data Transfer V2.0 ファイルを保存する Cloud Storage バケットの名前を入力します。バケット名を入力するときに、gs:// は入力しないでください。
    • [DoubleClick ID] に、該当する ID を入力します。

      チャンネル転送

    • (省略可)[Advanced] セクションを展開し、転送の実行通知を構成します。転送実行通知は、現時点ではアルファ版です。

      • [PubSub Topic] に、Pub/Sub トピック名(例: projects/myproject/topics/mytopic)を入力します。
      • [Send Email Notifications] をオンにして、転送実行失敗のメール通知を許可します。

        AdWords PubSub

  5. [Add] をクリックします。

  6. プロンプトが表示されたら、[Allow] をクリックして、DoubleClick キャンペーンの管理権限を BigQuery Data Transfer Service に付与します。権限ウィンドウが表示されるように、bigquery.cloud.google.com からのポップアップを許可します。

    転送の許可

コマンドライン

bq mk コマンドを入力して、転送作成フラグ --transfer_config を指定します。次のフラグも必要です。

  • --data_source
  • --target_dataset
  • --display_name
  • --params

    bq mk --transfer_config --project_id=[PROJECT_ID] --target_dataset=[DATASET] --display_name=[NAME] --params='[PARAMETERS]' --data_source=[DATA_SOURCE]
    

ここで

  • --project_id はプロジェクト ID です。
  • --target_dataset は転送設定のターゲット データセットです。
  • --display_name は転送設定の表示名です。転送名には、後で修正が必要になった場合に、この転送を簡単に特定できる任意の名前を使用できます。
  • --params には、作成される転送設定のパラメータを JSON 形式で指定します(例: --params='{"param":"param_value"}')。DoubleClick Camapaign Manager の場合、bucket パラメータと network_id パラメータを指定する必要があります。bucket は DCM DTv2 ファイルを含む Cloud Storage バケットです。network_id はネットワーク ID、Floodlight ID、または広告主 ID です。
  • --data_source はデータソースであり、ここでは dcm_dt(DoubleClick Campaign Manager)です。

また、--project_id フラグを指定して、特定のプロジェクトを指定することもできます。--project_id が指定されていない場合は、デフォルトのプロジェクトが使用されます。

たとえば、次のコマンドでは、DoubleClick ID 1234、Cloud Storage バケット dcdt_-dcm_account1234、およびターゲット データセット mydataset を使用して、My Transfer という名前の DoubleClick Campaign Manager 転送を作成します。転送はデフォルトのプロジェクトに作成されます。

bq mk --transfer_config --target_dataset=mydataset --display_name='My Transfer' --params='{"bucket": "dcdt_-dcm_account1234","network_id": "1234"}' --data_source=dcm_dt

コマンドを実行すると、次のようなメッセージが表示されます。

[URL omitted] Please copy and paste the above URL into your web browser and follow the instructions to retrieve an authentication code.

指示に従って、認証コードをコマンドラインに貼り付けます。

API

projects.locations.transferConfigs.create メソッドを使用し、TransferConfig リソースのインスタンスを指定します。

DoubleClick Campaign Manager の転送設定のトラブルシューティング

転送を設定する際に問題が発生した場合は、BigQuery Data Transfer Service 転送構成のトラブルシューティングDoubleClick Campaign Manager の転送に関する問題をご覧ください。

データのクエリ

データが BigQuery に転送されると、データは取り込み時間分割テーブルに書き込まれます。詳細については、分割テーブルをご覧ください。

自動生成されたビューを使用せずに、テーブルでクエリを直接実行する場合は、そのクエリで _PARTITIONTIME 疑似列を使用する必要があります。詳細については、分割テーブルのクエリをご覧ください。

DoubleClick サンプルクエリ

次の DoubleClick Campaign Manager サンプルクエリを使用して、転送されたデータを分析することができます。このクエリは、Google Cloud DatalabGoogle データスタジオ 360 などの可視化ツールでも使用できます。これらのクエリは、BigQuery で DoubleClick Campaign Manager データのクエリを開始する場合に役立つように用意されています。これらのレポートでできることに関するその他の質問については、DoubleClick Campaign Manager の技術担当者にお問い合わせください。

このサンプルでは、BigQuery の標準 SQL サポートを使用します。#standardSQL タグを使用して、標準 SQL を使用することを BigQuery に認識させる必要があります。#standardSQL 接頭辞の詳細については、クエリ接頭辞の設定をご覧ください。

以下のクエリで [DATASET] はデータセット名で置き換えます。[DOUBLECLICK_ID] は実際の DoubleClick ID で置き換えます。

最新のキャンペーン

以下のサンプルクエリは、最新のキャンペーンを取得します。

ウェブ UI

#standardSQL
SELECT Campaign, Campaign_ID FROM `[DATASET].match_table_campaigns_[DOUBLECLICK_ID]`
WHERE _DATA_DATE = _LATEST_DATE

コマンドライン

bq query --use_legacy_sql=false '
SELECT Campaign, Campaign_ID FROM `[DATASET].match_table_campaigns_[DOUBLECLICK_ID]`
WHERE _DATA_DATE = _LATEST_DATE'

キャンペーンごとのインプレッション数と個別ユーザー数

以下のサンプルクエリは、過去 30 日間のキャンペーンごとのインプレッション数と個別ユーザーの数を分析します。

ウェブ UI

#standardSQL
# START_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
# END_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
SELECT Campaign_ID, DATA_DATE AS Date, count(*) AS count, count(distinct User_ID) AS du
FROM `[DATASET].impression[DOUBLECLICK_ID]` WHERE
  _DATA_DATE BETWEEN [START_DATE] AND [END_DATE]
GROUP BY Campaign_ID, Date

コマンドライン

# START_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
# END_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
bq query --use_legacy_sql=false '
SELECT Campaign_ID, _DATA_DATE AS Date, count(*) AS count, count(distinct User_ID) AS du
FROM `[DATASET].impression_[DOUBLECLICK_ID]` WHERE
  _DATA_DATE BETWEEN [START_DATE] AND [END_DATE]
GROUP BY Campaign_ID, Date'

キャンペーンと日付で並べ替えた最新のキャンペーン

以下のサンプルクエリは、過去 30 日のキャンペーンをキャンペーンと日付で並べ替え、最新のキャンペーンを分析します。

ウェブ UI

#standardSQL
# START_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
# END_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
SELECT Campaign, Campaign_ID, Date FROM (
SELECT Campaign, Campaign_ID FROM `[DATASET].match_table_campaigns_[DOUBLECLICK_ID]`
  WHERE _DATA_DATE = _LATEST_DATE
), (
SELECT date AS Date
  FROM `bigquery-public-data.common_us.date_greg`
  WHERE Date BETWEEN [START_DATE] AND [END_DATE]
)
ORDER BY
  Campaign_ID, Date

コマンドライン

# START_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
# END_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
bq query --use_legacy_sql=false '
SELECT Campaign, Campaign_ID, Date FROM (
SELECT Campaign, Campaign_ID FROM `[DATASET].match_table_campaigns_[DOUBLECLICK_ID]`
  WHERE _DATA_DATE = _LATEST_DATE
), (
SELECT date AS Date
  FROM `bigquery-public-data.common_us.date_greg`
  WHERE Date BETWEEN [START_DATE] AND [END_DATE]
)
ORDER BY
  Campaign_ID, Date'

日付範囲内のキャンペーン別のインプレッション数と個別ユーザー数

以下のサンプルクエリは、[START_DATE] から [END_DATE] までに実施されたキャンペーンのインプレッション数と個別ユーザー数を分析します。

ウェブ UI

#standardSQL
# START_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
# END_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
SELECT
  base.*,
  imp.count AS imp_count,
  imp.du AS imp_du
FROM (
  SELECT
    *
  FROM (
    SELECT
      Campaign,
      Campaign_ID
    FROM
      `[DATASET].match_table_campaigns_[DOUBLECLICK_ID]`
    WHERE
      DATA_DATE = _LATEST_DATE ),
    (
    SELECT
      date AS Date
    FROM
      `bigquery-public-data.common_us.date_greg`
    WHERE
      Date BETWEEN [START_DATE]
      AND [END_DATE] ) ) AS base
LEFT JOIN (
  SELECT
    Campaign_ID,
    _DATA_DATE AS Date,
    COUNT() AS count,
    COUNT(DISTINCT User_ID) AS du
  FROM
    `[DATASET].impression[DOUBLECLICK_ID]`
  WHERE
    _DATA_DATE BETWEEN [START_DATE]
    AND [END_DATE]
  GROUP BY
    Campaign_ID,
    Date ) AS imp
ON
  base.Campaign_ID = imp.Campaign_ID
  AND base.Date = imp.Date
WHERE
  base.Campaign_ID = imp.Campaign_ID
  AND base.Date = imp.Date
ORDER BY
  base.Campaign_ID,
  base.Date

コマンドライン

# START_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
# END_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
bq query --use_legacy_sql=false '
SELECT
  base.*,
  imp.count AS imp_count,
  imp.du AS imp_du
FROM (
  SELECT
    *
  FROM (
    SELECT
      Campaign,
      Campaign_ID
    FROM
      `[DATASET].match_table_campaigns_[DOUBLECLICK_ID]`
    WHERE
      DATA_DATE = _LATEST_DATE ),
    (
    SELECT
      date AS Date
    FROM
      `bigquery-public-data.common_us.date_greg`
    WHERE
      Date BETWEEN [START_DATE]
      AND [END_DATE] ) ) AS base
LEFT JOIN (
  SELECT
    Campaign_ID,
    _DATA_DATE AS Date,
    COUNT() AS count,
    COUNT(DISTINCT User_ID) AS du
  FROM
    `[DATASET].impression[DOUBLECLICK_ID]`
  WHERE
    _DATA_DATE BETWEEN [START_DATE]
    AND [END_DATE]
  GROUP BY
    Campaign_ID,
    Date ) AS imp
ON
  base.Campaign_ID = imp.Campaign_ID
  AND base.Date = imp.Date
WHERE
  base.Campaign_ID = imp.Campaign_ID
  AND base.Date = imp.Date
ORDER BY
  base.Campaign_ID,
  base.Date'

キャンペーンごとのインプレッション、クリック、アクティビティ、個別ユーザーの数

以下のサンプルクエリは、過去 30 日間のキャンペーンごとのインプレッション数、クリック数、アクティビティ、個別のユーザー数を分析します。

ウェブ UI

#standardSQL
# START_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
# END_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
SELECT
  base.*,
  imp.count AS imp_count,
  imp.du AS imp_du,
  click.count AS click_count,
  click.du AS click_du,
  activity.count AS activity_count,
  activity.du AS activity_du
FROM (
  SELECT
    *
  FROM (
    SELECT
      Campaign,
      Campaign_ID
    FROM
      `[DATASET].match_table_campaigns_[DOUBLECLICK_ID]`
    WHERE
      DATA_DATE = _LATEST_DATE ),
    (
    SELECT
      date AS Date
    FROM
      `bigquery-public-data.common_us.date_greg`
    WHERE
      Date BETWEEN DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
      AND DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY) ) ) AS base
LEFT JOIN (
  SELECT
    Campaign_ID,
    _DATA_DATE AS Date,
    COUNT() AS count,
    COUNT(DISTINCT User_ID) AS du
  FROM
    `[DATASET].impression[DOUBLECLICK_ID]`
  WHERE
    DATA_DATE BETWEEN DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
    AND DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
  GROUP BY
    Campaign_ID,
    Date ) AS imp
ON
  base.Campaign_ID = imp.Campaign_ID
  AND base.Date = imp.Date
LEFT JOIN (
  SELECT
    Campaign_ID,
    DATA_DATE AS Date,
    COUNT() AS count,
    COUNT(DISTINCT User_ID) AS du
  FROM
    `[DATASET].click[DOUBLECLICK_ID]`
  WHERE
    _DATA_DATE BETWEEN DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
    AND DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
  GROUP BY
    Campaign_ID,
    Date ) AS click
ON
  base.Campaign_ID = click.Campaign_ID
  AND base.Date = click.Date
LEFT JOIN (
  SELECT
    Campaign_ID,
    _DATA_DATE AS Date,
    COUNT() AS count,
    COUNT(DISTINCT User_ID) AS du
  FROM
    `[DATASET].activity[DOUBLECLICK_ID]`
  WHERE
    _DATA_DATE BETWEEN DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
    AND DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
  GROUP BY
    Campaign_ID,
    Date ) AS activity
ON
  base.Campaign_ID = activity.Campaign_ID
  AND base.Date = activity.Date
WHERE
  base.Campaign_ID IN [CAMPAIGN_LIST]
  AND (base.Date = imp.Date
    OR base.Date = click.Date
    OR base.Date = activity.Date)
ORDER BY
  base.Campaign_ID,
  base.Date

コマンドライン

# START_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
# END_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
bq query --use_legacy_sql=false '
SELECT
  base.*,
  imp.count AS imp_count,
  imp.du AS imp_du,
  click.count AS click_count,
  click.du AS click_du,
  activity.count AS activity_count,
  activity.du AS activity_du
FROM (
  SELECT
    *
  FROM (
    SELECT
      Campaign,
      Campaign_ID
    FROM
      `[DATASET].match_table_campaigns_[DOUBLECLICK_ID]`
    WHERE
      DATA_DATE = _LATEST_DATE ),
    (
    SELECT
      date AS Date
    FROM
      `bigquery-public-data.common_us.date_greg`
    WHERE
      Date BETWEEN DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
      AND DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY) ) ) AS base
LEFT JOIN (
  SELECT
    Campaign_ID,
    _DATA_DATE AS Date,
    COUNT() AS count,
    COUNT(DISTINCT User_ID) AS du
  FROM
    `[DATASET].impression[DOUBLECLICK_ID]`
  WHERE
    DATA_DATE BETWEEN DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
    AND DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
  GROUP BY
    Campaign_ID,
    Date ) AS imp
ON
  base.Campaign_ID = imp.Campaign_ID
  AND base.Date = imp.Date
LEFT JOIN (
  SELECT
    Campaign_ID,
    DATA_DATE AS Date,
    COUNT() AS count,
    COUNT(DISTINCT User_ID) AS du
  FROM
    `[DATASET].click[DOUBLECLICK_ID]`
  WHERE
    _DATA_DATE BETWEEN DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
    AND DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
  GROUP BY
    Campaign_ID,
    Date ) AS click
ON
  base.Campaign_ID = click.Campaign_ID
  AND base.Date = click.Date
LEFT JOIN (
  SELECT
    Campaign_ID,
    _DATA_DATE AS Date,
    COUNT() AS count,
    COUNT(DISTINCT User_ID) AS du
  FROM
    `[DATASET].activity[DOUBLECLICK_ID]`
  WHERE
    _DATA_DATE BETWEEN DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
    AND DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
  GROUP BY
    Campaign_ID,
    Date ) AS activity
ON
  base.Campaign_ID = activity.Campaign_ID
  AND base.Date = activity.Date
WHERE
  base.Campaign_ID IN [CAMPAIGN_LIST]
  AND (base.Date = imp.Date
    OR base.Date = click.Date
    OR base.Date = activity.Date)
ORDER BY
  base.Campaign_ID,
  base.Date'

キャンペーン活動

以下のサンプルクエリは、過去 30 日間のキャンペーン活動を分析します。このクエリで、[CAMPAIGN_LIST] はクエリのスコープ内で関心のある DoubleClick キャンペーンを含むカンマ区切りのリストで置き換えます。

ウェブ UI

#standardSQL
# START_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
# END_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
SELECT
  base.*,
  activity.count AS activity_count,
  activity.du AS activity_du
FROM (
  SELECT
    *
  FROM (
    SELECT
      Campaign,
      Campaign_ID
    FROM
      `[DATASET].match_table_campaigns_[DOUBLECLICK_ID]`
    WHERE
      DATA_DATE = _LATEST_DATE ),
    (
    SELECT
      mt_at.Activity_Group,
      mt_ac.Activity,
      mt_ac.Activity_Type,
      mt_ac.Activity_Sub_Type,
      mt_ac.Activity_ID,
      mt_ac.Activity_Group_ID
    FROM
      `[DATASET].match_table_activity_cats[DOUBLECLICK_ID]` AS mt_ac
    JOIN (
      SELECT
        Activity_Group,
        Activity_Group_ID
      FROM
        `[DATASET].match_table_activity_types_[DOUBLECLICK_ID]`
      WHERE
        _DATA_DATE = _LATEST_DATE ) AS mt_at
    ON
      mt_at.Activity_Group_ID = mt_ac.Activity_Group_ID
    WHERE
      _DATA_DATE = _LATEST_DATE ),
    (
    SELECT
      date AS Date
    FROM
      `bigquery-public-data.common_us.date_greg`
    WHERE
      Date BETWEEN [START_DATE]
      AND [END_DATE] ) ) AS base
LEFT JOIN (
  SELECT
    Campaign_ID,
    Activity_ID,
    _DATA_DATE AS Date,
    COUNT() AS count,
    COUNT(DISTINCT User_ID) AS du
  FROM
    `[DATASET].activity_[DOUBLECLICK_ID]`
  WHERE
    _DATA_DATE BETWEEN DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
    AND DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
  GROUP BY
    Campaign_ID,
    Activity_ID,
    Date ) AS activity
ON
  base.Campaign_ID = activity.Campaign_ID
  AND base.Activity_ID = activity.Activity_ID
  AND base.Date = activity.Date
WHERE
  base.Campaign_ID IN [CAMPAIGN_LIST]
  AND base.Activity_ID = activity.Activity_ID
ORDER BY
  base.Campaign_ID,
  base.Activity_Group_ID,
  base.Activity_ID,
  base.Date

コマンドライン

# START_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
# END_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
bq query --use_legacy_sql=false '
SELECT
  base.*,
  activity.count AS activity_count,
  activity.du AS activity_du
FROM (
  SELECT
    *
  FROM (
    SELECT
      Campaign,
      Campaign_ID
    FROM
      `[DATASET].match_table_campaigns_[DOUBLECLICK_ID]`
    WHERE
      DATA_DATE = _LATEST_DATE ),
    (
    SELECT
      mt_at.Activity_Group,
      mt_ac.Activity,
      mt_ac.Activity_Type,
      mt_ac.Activity_Sub_Type,
      mt_ac.Activity_ID,
      mt_ac.Activity_Group_ID
    FROM
      `[DATASET].match_table_activity_cats[DOUBLECLICK_ID]` AS mt_ac
    JOIN (
      SELECT
        Activity_Group,
        Activity_Group_ID
      FROM
        `[DATASET].match_table_activity_types_[DOUBLECLICK_ID]`
      WHERE
        _DATA_DATE = _LATEST_DATE ) AS mt_at
    ON
      mt_at.Activity_Group_ID = mt_ac.Activity_Group_ID
    WHERE
      _DATA_DATE = _LATEST_DATE ),
    (
    SELECT
      date AS Date
    FROM
      `bigquery-public-data.common_us.date_greg`
    WHERE
      Date BETWEEN [START_DATE]
      AND [END_DATE] ) ) AS base
LEFT JOIN (
  SELECT
    Campaign_ID,
    Activity_ID,
    _DATA_DATE AS Date,
    COUNT() AS count,
    COUNT(DISTINCT User_ID) AS du
  FROM
    `[DATASET].activity_[DOUBLECLICK_ID]`
  WHERE
    _DATA_DATE BETWEEN DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
    AND DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
  GROUP BY
    Campaign_ID,
    Activity_ID,
    Date ) AS activity
ON
  base.Campaign_ID = activity.Campaign_ID
  AND base.Activity_ID = activity.Activity_ID
  AND base.Date = activity.Date
WHERE
  base.Campaign_ID IN [CAMPAIGN_LIST]
  AND base.Activity_ID = activity.Activity_ID
ORDER BY
  base.Campaign_ID,
  base.Activity_Group_ID,
  base.Activity_ID,
  base.Date'
このページは役立ちましたか?評価をお願いいたします。

フィードバックを送信...