マーケティング分析データ用の ELT パイプラインを構築する

このチュートリアルでは、BigQuery でマーケティング分析データを抽出、読み込み、変換する ELT ワークフローを設定する方法について説明します。

一般的な ELT ワークフローでは、データソースから新しい顧客データを定期的に抽出し、BigQuery に読み込みます。非構造化データは、意味のある指標に処理されます。このチュートリアルでは、BigQuery Data Transfer Service を使用してマーケティング分析データ転送を設定し、ELT ワークフローを作成します。次に、データに対して定期的な変換を実行するように Dataform をスケジュールします。

このチュートリアルでは、データソースとして Google 広告を使用しますが、BigQuery Data Transfer Service でサポートされているデータソースであればどれでも使用できます。

始める前に

Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Roles required to select or create a project

Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

Go to project selector

Verify that billing is enabled for your Google Cloud project.

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Roles required to select or create a project

Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

Go to project selector

Verify that billing is enabled for your Google Cloud project.

必要なロール

このチュートリアルを完了するために必要な権限を取得するには、プロジェクトに対する次の IAM ロールを付与するよう管理者に依頼してください。

BigQuery 管理者（roles/bigquery.admin）
Dataform 管理者（roles/dataform.admin）

ロールの付与については、プロジェクト、フォルダ、組織へのアクセス権の管理をご覧ください。

必要な権限は、カスタムロールや他の事前定義ロールから取得することもできます。

定期的なデータ転送をスケジュールする

BigQuery をデータソースの最新のマーケティングデータで最新の状態に保つには、BigQuery Data Transfer Service を使用して定期的なデータ転送を設定し、スケジュールに従ってデータを抽出して読み込みます。

このチュートリアルでは、Google 広告をデータソースの例として使用します。BigQuery Data Transfer Service でサポートされているデータソースの一覧については、サポートされているデータソースをご覧ください。

Google Cloud コンソールの [データ転送] ページに移動します。

[データ転送] に移動
[転送を作成] をクリックします。
[ソースタイプ] セクションで、[ソース] として [Google Ads] を選択します。
[データソースの詳細] セクションで、次の操作を行います。
1. [お客様 ID] に Google 広告のお客様 ID を入力します。
2. [レポートタイプ] で [標準] を選択します。標準レポートには、Google 広告レポートの変換で詳しく説明されている標準セットのレポートとフィールドが含まれます。
  - [更新ウィンドウ] に「5」と入力します。
[転送先の設定] セクションの [データセット] には、データを保存するために作成したデータセットを選択します。
[転送構成名] セクションの [表示名] に「Marketing tutorial」と入力します。
[スケジュールオプション] セクションで:
- [繰り返しの頻度] で [日] を選択します。
- [時刻] に「08:00」と入力します。
[保存] をクリックします。

構成を保存すると、BigQuery Data Transfer Service がデータ転送を開始します。転送構成の設定に基づいて、データ転送は毎日午前 8 時（UTC）に 1 回実行され、Google 広告から過去 5 日分のデータが抽出されます。

進行中の転送ジョブをモニタリングして、各データ転送のステータスを確認できます。

テーブルデータをクエリする

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

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

次のセクションでは、転送されたデータの確認に使用できるサンプルクエリを示します。

キャンペーンのパフォーマンス

次のサンプルクエリでは、過去 30 日間の Google 広告キャンペーンの掲載結果を分析します。

Console

SELECT
  c.customer_id,
  c.campaign_name,
  c.campaign_status,
  SUM(cs.metrics_impressions) AS Impressions,
  SUM(cs.metrics_interactions) AS Interactions,
  (SUM(cs.metrics_cost_micros) / 1000000) AS Cost
FROM
  `DATASET.ads_Campaign_CUSTOMER_ID` c
LEFT JOIN
  `DATASET.ads_CampaignBasicStats_CUSTOMER_ID` cs
ON
  (c.campaign_id = cs.campaign_id
  AND cs._DATA_DATE BETWEEN
  DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY) AND DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY))
WHERE
  c._DATA_DATE = c._LATEST_DATE
GROUP BY
  1, 2, 3
ORDER BY
  Impressions DESC

bq

  bq query --use_legacy_sql=false '
  SELECT
    c.customer_id,
    c.campaign_name,
    c.campaign_status,
    SUM(cs.metrics_impressions) AS Impressions,
    SUM(cs.metrics_interactions) AS Interactions,
    (SUM(cs.metrics_cost_micros) / 1000000) AS Cost
  FROM
    `DATASET.ads_Campaign_CUSTOMER_ID` c
  LEFT JOIN
    `DATASET.ads_CampaignBasicStats_CUSTOMER_ID` cs
  ON
    (c.campaign_id = cs.campaign_id
    AND cs._DATA_DATE BETWEEN
    DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY) AND DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY))
  WHERE
    c._DATA_DATE = c._LATEST_DATE
  GROUP BY
    1, 2, 3
  ORDER BY
    Impressions DESC'

次のように置き換えます。

DATASET: 転送されたテーブルを保存するために作成したデータセットの名前
CUSTOMER_ID: Google 広告のお客様 ID

キーワード数

次のサンプルクエリは、キャンペーン、広告グループ、ステータスごとにキーワードを分析します。このクエリでは、KeywordMatchType 関数を使用しています。キーワードのマッチタイプを使用すると、広告をトリガーする検索語句を制御できます。キーワードのマッチタイプの詳細については、キーワードのマッチタイプについてをご覧ください。

コンソール

  SELECT
    c.campaign_status AS CampaignStatus,
    a.ad_group_status AS AdGroupStatus,
    k.ad_group_criterion_status AS KeywordStatus,
    k.ad_group_criterion_keyword_match_type AS KeywordMatchType,
    COUNT(*) AS count
  FROM
    `DATASET.ads_Keyword_CUSTOMER_ID` k
    JOIN
    `DATASET.ads_Campaign_CUSTOMER_ID` c
  ON
    (k.campaign_id = c.campaign_id AND k._DATA_DATE = c._DATA_DATE)
  JOIN
    `DATASET.ads_AdGroup_CUSTOMER_ID` a
  ON
    (k.ad_group_id = a.ad_group_id AND k._DATA_DATE = a._DATA_DATE)
  WHERE
    k._DATA_DATE = k._LATEST_DATE
  GROUP BY
    1, 2, 3, 4

bq

  bq query --use_legacy_sql=false '
  SELECT
    c.campaign_status AS CampaignStatus,
    a.ad_group_status AS AdGroupStatus,
    k.ad_group_criterion_status AS KeywordStatus,
    k.ad_group_criterion_keyword_match_type AS KeywordMatchType,
    COUNT(*) AS count
  FROM
    `DATASET.ads_Keyword_CUSTOMER_ID` k
  JOIN
    `DATASET.ads_Campaign_CUSTOMER_ID` c
  ON
    (k.campaign_id = c.campaign_id AND k._DATA_DATE = c._DATA_DATE)
  JOIN
    `DATASET.ads_AdGroup_CUSTOMER_ID` a
  ON
    (k.ad_group_id = a.ad_group_id AND k._DATA_DATE = a._DATA_DATE)
  WHERE
    k._DATA_DATE = k._LATEST_DATE
  GROUP BY
    1, 2, 3, 4'

次のように置き換えます。

DATASET: 転送されたテーブルを保存するために作成したデータセットの名前
CUSTOMER_ID: Google 広告のお客様 ID

Dataform リポジトリを作成する

Google 広告から最新のデータを転送するデータ転送構成を作成したら、マーケティング分析データを定期的に変換するように Dataform を設定します。Dataform を使用すると、定期的なデータ変換をスケジュール設定できます。また、他のデータアナリストと共同で作業しながら、SQL を使用してこれらの変換を定義できます。

変換コードを構成する SQLX クエリを保存する Dataform リポジトリを作成します。

Google Cloud コンソールで、[Dataform] ページに移動します。

Dataform に移動
[ リポジトリを作成] をクリックします。
[リポジトリの作成] ページで、次の操作を行います。
1. [リポジトリ ID] フィールドに「marketing-tutorial-repository」と入力します。
2. [リージョン] リストでリージョンを選択します。
3. [作成] をクリックします。

これで、marketing-tutorial-repository リポジトリが Dataform リポジトリのリストに表示されます。

Dataform リポジトリの詳細については、Dataform リポジトリについてをご覧ください。

Dataform 開発ワークスペースを作成して初期化する

Dataform 開発ワークスペースを作成して、変更をリポジトリに commit して push する前に、リポジトリ内の変換コードを操作できるようにします。

Google Cloud コンソールで、[Dataform] ページに移動します。

Dataform に移動
[marketing-tutorial-repository] をクリックします。
[ 開発ワークスペースを作成] をクリックします。
[開発ワークスペースを作成] ウィンドウで、次の操作を行います。
1. [ワークスペース ID] フィールドに「marketing-tutorial-workspace」と入力します。
2. [作成] をクリックします。
開発ワークスペースページが表示されます。
[ワークスペースを初期化] をクリックします。

marketing-tutorial-workspace 開発ワークスペースが、marketing-tutorial-repository リポジトリの [開発ワークスペース] タブに表示されます。また、definitions ディレクトリに *first_view.sqlx と *second_view.sqlx という 2 つのサンプルファイルが表示されます。

Dataform 開発ワークスペースの詳細については、開発ワークスペースの概要をご覧ください。

Google 広告のテーブルをテーブルソースとして宣言する

次の手順に沿って、新しく移行した Google 広告テーブルをデータソースとして宣言し、Dataform に接続します。

データソース宣言用の SQLX ファイルを作成する

Dataform では、definitions/ ディレクトリに SQLX ファイルを作成して、データソースの宛先を宣言します。

Google Cloud コンソールで、[Dataform] ページに移動します。

[Dataform] ページに移動
[marketing-tutorial-repository] を選択します。
[marketing-tutorial-workspace] を選択します。
[ファイル] ペインで、definitions/ の横にある [その他] メニューをクリックします。
[ファイルを作成] をクリックします。
[新しいファイルを作成] ペインで、次の操作を行います。
1. [ファイルパスを追加] フィールドで、definitions/ の後に名前「definitions/googleads-declaration.sqlx」を入力します。
2. [ファイルを作成] をクリックします。

データソースを宣言する

definitions/googleads-declaration.sqlx を編集して、転送された Google 広告テーブルをデータソースとして宣言します。この例では、ads_Campaign テーブルをデータソースとして宣言します。

開発ワークスペースの [ファイル] ペインで、データソース宣言の SQLX ファイルをクリックします。

このファイルに次のコードスニペットを入力します。

    config {
        type: "declaration",
        database: "PROJECT_ID",
        schema: "DATASET",
        name: "ads_Campaign_CUSTOMER_ID",
    }

変換を定義する

definitions/ ディレクトリに SQLX ファイルを作成して、データ変換を定義します。このチュートリアルでは、daily_performance.sqlx という名前のファイルを使用して、クリック数、インプレッション数、費用、コンバージョン数などの指標を集計する毎日の変換を作成します。

変換 SQLX ファイルを作成する

[ファイル] ペインで、definitions/ の横にある（その他）メニューをクリックして、[ファイルを作成] を選択します。
[ファイルパスを追加] フィールドに「definitions/daily_performance.sqlx」と入力します。
[ファイルを作成] をクリックします。

変換 SQLX ファイルを定義する

[ファイル] ペインで definitions/ ディレクトリを開きます。

daily_performance.sqlx を選択し、次のクエリを入力します。

    config {
        type: "table",
        schema: "reporting",
        tags: ["daily", "google_ads"]
    }
    SELECT
        date,
        campaign_id,
        campaign_name,
    SUM(clicks) AS total_clicks
    FROM
        `ads_Campaign_CUSTOMER_ID`
    GROUP BY
        date,
        campaign_id,
        campaign_name
        ORDER BY
        date DESC

変更を commit して push する

開発ワークスペースで変更を加えたら、次の手順で変更を commit してリポジトリに push できます。

marketing-tutorial-workspace ワークスペースで、[Commit 1 change] をクリックします。
[新規 commit] ペインの [commit メッセージを追加] フィールドに、commit の説明を入力します。
[Commit all changes] をクリックします。
marketing-tutorial-workspace ワークスペースで、[デフォルトのブランチに push] をクリックします。

変更がリポジトリに正常に push されると、「Workspace is up to date」というメッセージが表示されます。

データ変換をスケジュールする

データ変換ファイルを定義したら、データ変換をスケジュールします。

製品版リリースを作成する

Dataform の本番環境リリースにより、データ変換の結果で環境が常に更新されます。次の手順では、データ変換を保存する marketing-tutorial-repository リポジトリの main ブランチを指定する方法について説明します。

Google Cloud コンソールで、[Dataform] ページに移動します。

[Dataform] ページに移動
[marketing-tutorial-repository] を選択します。
[リリースとスケジュール] タブをクリックします。
[製品版リリースの作成] をクリックします。
[リリース構成を作成] ペインで、次の設定を構成します。
1. [リリース ID] フィールドに「transformations」と入力します。
2. [Git commitish] フィールドはデフォルト値の main のままにします。
3. [スケジュールの頻度] セクションで、[オンデマンド] を選択します。
[作成] をクリックします。

ワークフロー構成を作成する

本番環境リリースを作成したら、リポジトリで指定されたスケジュールでデータ変換を実行するワークフロー構成を作成できます。次の手順では、transformations ファイルから毎日の変換をスケジュールする方法について説明します。

Google Cloud コンソールで、[Dataform] ページに移動します。

[Dataform] ページに移動
[marketing-tutorial-repository] を選択します。
[リリースとスケジュール] タブをクリックします。
[ワークフロー構成] セクションで、[作成] をクリックします。
[ワークフロー構成を作成] ペインの [構成 ID] フィールドに、「transformations」と入力します。
[リリース構成] メニューで transformations を選択します。
[認証] で、[Execute with user credentials] を選択します。

[スケジュールの頻度] セクションで、次の操作を行います。

1. Select **Repeat**.
1. For **Repeats**, select `Daily`.
1. For **At time**, enter `10:00 AM`.
1. For **Timezone**, select `Coordinated Universal Time (UTC)`.

[タグの選択] をクリックします。
[実行するタグを選択] フィールドで、[毎日] を選択します。
[作成] をクリックします。

作成したワークフロー構成は、transformations リリース構成によって作成された最新のコンパイル結果全体を実行します。

クリーンアップ

このページで使用したリソースについて、 Google Cloud アカウントに課金されないようにするには、次の操作を行います。

BigQuery で作成したデータセットを削除する

BigQuery アセットの料金が発生しないようにするには、dataform というデータセットを削除します。

Google Cloud コンソールで、[BigQuery] ページに移動します。

BigQuery に移動
[エクスプローラ] パネルでプロジェクトを開き、[dataform] を選択します。
（操作）メニューをクリックして、[削除] を選択します。
[データセットの削除] ダイアログで、フィールドに「delete」と入力してから、[削除] をクリックします。

Dataform 開発ワークスペースと構成を削除する

Dataform 開発ワークスペースの作成に費用はかかりませんが、開発ワークスペースを削除するには、次の操作を行います。

Google Cloud コンソールで、[Dataform] ページに移動します。

Dataform に移動
[quickstart-repository] をクリックします。
[リリースとスケジュール] タブをクリックします。
[リリース構成] セクションで、production 構成の横にある（その他）メニューをクリックし、[削除] をクリックします。
[ワークフロー構成] セクションで、transformations 構成の横にある（その他）メニューをクリックし、[削除] をクリックします。
[開発ワークスペース] タブで、quickstart-workspace によって（その他）メニューをクリックし、[削除] を選択します。
[削除] をクリックして確定します。

Dataform リポジトリを削除する

Dataform リポジトリの作成に費用はかかりませんが、リポジトリを削除する手順は次のとおりです。

Google Cloud コンソールで、[Dataform] ページに移動します。

Dataform に移動
quickstart-repository から、（その他）メニューをクリックし、[削除] を選択します。
[リポジトリの削除] ウィンドウで、リポジトリの名前を入力して削除を確定します。
[削除] をクリックして確定します。