此页面由 Cloud Translation API 翻译。

为营销分析数据构建 ELT 流水线

本教程将向您展示如何设置 ELT 工作流，以提取、加载和转换 BigQuery 中的营销分析数据。

典型的 ELT 工作流会定期从数据源提取新的客户数据，并将其加载到 BigQuery 中。然后，将非结构化数据处理为有意义的指标。在本教程中，您将使用 BigQuery Data Transfer Service 设置营销分析数据转移作业，从而创建 ELT 工作流。然后，您可以安排 Dataform 定期对数据运行转换。

在本教程中，您将使用 Google Ads 作为数据源，但您也可以使用 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 (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 (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 Admin (roles/bigquery.admin)
Dataform Admin (roles/dataform.admin)

如需详细了解如何授予角色，请参阅管理对项目、文件夹和组织的访问权限。

您也可以通过自定义角色或其他预定义角色来获取所需的权限。

安排定期数据转移

为了让 BigQuery 始终包含数据源中的最新营销数据，请使用 BigQuery Data Transfer Service 设置周期性数据转移，以便按计划提取和加载数据。

在本教程中，您将使用 Google Ads 作为示例数据源。如需查看 BigQuery Data Transfer Service 支持的数据源的完整列表，请参阅支持的数据源。

前往 Google Cloud 控制台中的“数据转移”页面。

转到“数据传输”
点击 创建转移作业。
在来源类型部分的来源字段中，选择 Google Ads。
在数据源详细信息部分，执行以下操作：
1. 在客户 ID 字段中，输入您的 Google Ads 客户 ID。
2. 在报告类型部分，选择标准。标准报告包含一组标准报告和字段，如 Google Ads 报告转换中所述。
  - 对于刷新时段，输入 5。
在目标设置部分的数据集字段中，选择您创建用来存储数据的数据集。
在转移配置名称部分的显示名称中，输入 Marketing tutorial。
在时间表选项部分，执行以下操作：
- 在重复频率部分，选择天数。
- 对于 At，输入 08:00。
点击保存。

保存配置后，BigQuery Data Transfer Service 会开始数据转移。根据转移配置中的设置，数据转移作业每天在 8:00（世界协调时间 [UTC]）运行一次，并从 Google Ads 中提取过去 5 天的数据。

您可以监控正在进行的转移作业，以检查每次数据转移的状态。

查询表数据

当数据转移到 BigQuery 时，这些数据会写入注入时间分区表。如需了解详情，请参阅分区表简介。

如果您要直接查询表，而不是使用自动生成的视图，则必须在查询中使用 _PARTITIONTIME 伪列。如需了解详情，请参阅查询分区表。

以下部分展示了一些示例查询，您可以使用这些查询来检查已转移的数据。

广告系列效果

以下示例查询分析了最近 30 天内 Google Ads 广告系列的效果。

控制台

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 Ads 客户 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 Ads 客户 ID。

创建 Dataform 仓库

创建数据转移配置以从 Google Ads 转移最新数据后，设置 Dataform 以定期转换营销分析数据。借助 Dataform，您可以安排定期进行数据转换，并与其他数据分析师协作，使用 SQL 定义这些转换。

创建一个 Dataform 代码库来存储构成转换代码的 SQLX 查询。

在 Google Cloud 控制台中，前往 Dataform 页面。

前往 Dataform
点击 创建制品库。
在创建仓库页面上，执行以下操作：
1. 在代码库 ID 字段中，输入 marketing-tutorial-repository。
2. 在区域列表中，选择一个区域。
3. 点击创建。

marketing-tutorial-repository 代码库现在会显示在您的 Dataform 代码库列表中。

如需详细了解 Dataform 代码库，请参阅关于 Dataform 代码库。

创建并初始化 Dataform 开发工作区

创建 Dataform 开发工作区，以便您在提交更改并将其推送到代码库之前，在代码库中处理转换代码。

在 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。

如需详细了解 Dataform 开发工作区，请参阅开发工作区概览。

将 Google Ads 表格声明为表格来源

按照以下步骤操作，将新转移的 Google Ads 表格声明为数据源，从而将其连接到 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 表格声明为数据源。此示例将 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

提交并推送您的更改

在开发工作区中进行更改后，您可以按照以下步骤提交这些更改并将其推送到代码库：

在 marketing-tutorial-workspace 工作区中，点击提交 1 项更改。
在新提交窗格中，在添加提交消息字段中输入提交说明。
点击提交所有更改。
在 marketing-tutorial-workspace 工作区中，点击推送到默认分支。

成功将更改推送到代码库后，系统会显示“工作区已更新”消息。

安排数据转换

定义数据转换文件后，安排数据转换。

创建正式版

在 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。
在身份验证下，选择使用用户凭据执行

在时间表频率部分中，执行以下操作：

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 旁边，点击更多菜单，然后选择删除。
在删除代码库窗口中，输入代码库的名称以确认删除。
请点击删除来确认操作。