16.2在BigQuery中创建您的第一个查询

目标

  • 浏览BigQuery UI
  • 在BigQuery中创建SQL查询
  • 将SQL查询的结果保存在BigQuery中的数据集中

上下文

当Google Analytics数据位于BigQuery中时,维度、量度和其他变量都是嵌套的。 此外,Google Analytics数据每天加载到不同的表中。 这意味着尝试将BigQuery中的Google Analytics表直接连接到Adobe Experience Platform非常困难,而不是一个好主意。

解决此问题的方法是将Google Analytics数据转换为可读格式,使引入Adobe Experience Platform更简单。

练习16.2.1 — 创建一个数据集以保存新的BigQuery表

转至BigQuery Console

演示

在​Explorer​中,您将看到您的项目ID。 单击您的项目ID(不要单击​bigquery-public-data​数据集)。

演示

您可以看到还没有数据集,因此现在我们创建一个。
单击​创建数据集

演示

屏幕右侧将显示​创建数据集​菜单。

演示

对于​数据集ID,请使用以下命名约定。 对于其他字段,请保留默认设置。

命名 示例
ldap_BigQueryDataSets vangeluw_BigQueryDataSets

演示

接下来,单击​创建数据集

演示

然后,您将在创建数据集后返回BigQuery控制台。

演示

练习16.2.2 — 创建您的第一个SQL BigQuery

接下来,您将在BigQuery中创建第一个查询。 此查询的目标是获取Google Analytics样本数据并进行转换,以便在Adobe Experience Platform中摄取。 转到​EDITOR​选项卡。

演示

请复制以下SQL查询并将其粘贴到该查询编辑器中。 阅读查询并了解Google AnalyticsBigQuery语法。

SELECT
  CONCAT(fullVisitorId, CAST(hitTime AS String), '-', hitNumber) AS _id,
  TIMESTAMP(DATETIME(Year_Current, Month_Current, Day_Current, Hour, Minutes, Seconds)) AS timeStamp,
  fullVisitorId as GA_ID,
  -- Fake CUSTOMER ID
  CONCAT('3E-D4-',fullVisitorId, '-1W-93F' ) as customerID,
  Page,
  Landing_Page,
  Exit_Page,
  Device,
  Browser,
  MarketingChannel,
  TrafficSource,
  TrafficMedium,
  -- Enhanced Ecommerce
  TransactionID,
  CASE
      WHEN EcommerceActionType = '2' THEN 'Product_Detail_Views'
      WHEN EcommerceActionType = '3' THEN 'Adds_To_Cart'
      WHEN EcommerceActionType = '4' THEN 'Product_Removes_From_Cart'
      WHEN EcommerceActionType = '5' THEN 'Product_Checkouts'
      WHEN EcommerceActionType = '6' THEN 'Product_Refunds'
    ELSE
    NULL
  END
     AS Ecommerce_Action_Type,
  -- Entrances (metric)
  SUM(CASE
      WHEN isEntrance = TRUE THEN 1
    ELSE
    0
  END
    ) AS Entries,
    
--Pageviews (metric)
    COUNT(*) AS Pageviews,
    
 -- Exits 
    SUM(
    IF
      (isExit IS NOT NULL,
        1,
        0)) AS Exits,
        
 --Bounces
   SUM(CASE
      WHEN isExit = TRUE AND isEntrance = TRUE THEN 1
    ELSE
    0
  END
    ) AS Bounces,
        
  -- Unique Purchases (metric)
  COUNT(DISTINCT TransactionID) AS Unique_Purchases,
  -- Product Detail Views (metric)
  COUNT(CASE
      WHEN EcommerceActionType = '2' THEN fullVisitorId
    ELSE
    NULL
  END
    ) AS Product_Detail_Views,
  -- Product Adds To Cart (metric)
  COUNT(CASE
      WHEN EcommerceActionType = '3' THEN fullVisitorId
    ELSE
    NULL
  END
    ) AS Adds_To_Cart,
  -- Product Removes From Cart (metric)
  COUNT(CASE
      WHEN EcommerceActionType = '4' THEN fullVisitorId
    ELSE
    NULL
  END
    ) AS Product_Removes_From_Cart,
  -- Product Checkouts (metric)
  COUNT(CASE
      WHEN EcommerceActionType = '5' THEN fullVisitorId
    ELSE
    NULL
  END
    ) AS Product_Checkouts,
  -- Product Refunds (metric)
  COUNT(CASE
      WHEN EcommerceActionType = '7' THEN fullVisitorId
    ELSE
    NULL
  END
    ) AS Product_Refunds
  FROM (
  SELECT
    -- Landing Page (dimension)
    CASE
      WHEN hits.isEntrance = TRUE THEN hits.page.pageTitle
    ELSE NULL
  END
    AS Landing_page,
    
        -- Exit Page (dimension)
    CASE
      WHEN hits.isExit = TRUE THEN hits.page.pageTitle
    ELSE
    NULL
  END
    AS Exit_page,
    
    hits.page.pageTitle AS Page,
    hits.isEntrance,
    hits.isExit,
    hits.hitNumber as hitNumber,
    hits.time as hitTime,
    date as Fecha,
    fullVisitorId,
    visitStartTime,
    device.deviceCategory AS Device,
    device.browser AS Browser,
    channelGrouping AS MarketingChannel,
    trafficSource.source AS TrafficSource,
    trafficSource.medium AS TrafficMedium,
    hits.transaction.transactionId AS TransactionID,
    CAST(EXTRACT(YEAR FROM CURRENT_DATE()) AS INT64) AS Year_Current,
    CAST(EXTRACT(MONTH FROM CURRENT_DATE()) AS INT64) AS Month_Current,
     CAST(EXTRACT(DAY FROM CURRENT_DATE()) AS INT64) AS Day_Current,
    CAST(EXTRACT(DAY FROM DATE_SUB(CURRENT_DATE(),INTERVAL 1 DAY)) AS INT64) AS Day_Current_Before,
    CAST(FORMAT_DATE('%Y', PARSE_DATE("%Y%m%d", date)) AS INT64) AS Year,
  CAST(FORMAT_DATE('%m', PARSE_DATE("%Y%m%d",date)) AS INT64) AS Month,
  CAST(FORMAT_DATE('%d', PARSE_DATE("%Y%m%d",date)) AS INT64) AS Day,
    CAST(EXTRACT (hour FROM TIMESTAMP_SECONDS(hits.time)) AS INT64) AS Hour,
  CAST(EXTRACT (minute FROM TIMESTAMP_SECONDS(hits.time)) AS INT64) AS Minutes,
  CAST(EXTRACT (second FROM TIMESTAMP_SECONDS(hits.time)) AS INT64) AS SecondS,
    hits.eCommerceAction.action_type AS EcommerceActionType
  
  FROM
    `bigquery-public-data.google_analytics_sample.ga_sessions_*`,
     UNNEST(hits) AS hits
  WHERE
    _table_suffix BETWEEN '20170101'
    AND '20170331'
    AND totals.visits = 1
    AND hits.type = 'PAGE'
    )
    
GROUP BY
  1,
  2,
  3,
  4,
  5,
  6,
  7,
  8,
  9,
  10,
  11,
  12,
  13,
  14
    
  ORDER BY 2 DESC

准备就绪后,单击​运行​以运行查询:

演示

执行查询可能需要几分钟。

查询运行完毕后,您将在​查询结果​中看到以下输出。

演示

练习16.2.3 — 保存BigQuery SQL查询的结果

下一步是通过单击​保存结果​按钮保存查询的输出。

演示

作为输出的位置,选择​BigQuery表

演示

然后您将看到一个新的弹出窗口,其中预填充了​项目名称​和​数据集名称。 数据集名称应该是您在本练习开始时创建的数据集,其命名约定如下:

命名 示例
ldap_BigQueryDataSets vangeluw_BigQueryDataSets

您现在需要输入表名称。 请使用此命名规范:

命名 示例
ldap_GAdataTableBigQuery vangeluw_GAdataTableBigQuery

演示

单击​保存

可能需要一段时间,直到数据在您创建的表中准备就绪。 几分钟后,刷新浏览器。 然后,您应在数据集中看到BigQuery项目中​Explorer​下的ldap_GAdataTableBigquery表。

演示

您现在继续下一个练习,将此表连接到Adobe Experience Platform。

下一步:16.3将Connect GCP和BigQuery连接到Adobe Experience Platform

返回模块16

返回到所有模块

在此页面上