google-analytics - 使用 BigQuery 提取命中级别数据时，Google Analytics 指标被夸大了

Question

我正在尝试在我已链接到 bigquery 的 Google Analytics(分析)汇总属性中显示源属性名称。问题是，当我尝试以下一些指标时，一些指标变得非常膨胀。我猜这与重复字段有关，但不知道该怎么做。我尝试了许多解决方法，例如使用“max”，但这并没有显示每个属性名称。

除了用户和访问之外的所有指标似乎都被夸大了。

SELECT
  date,
  MAX(CASE
      WHEN EXISTS(  SELECT 1  FROM UNNEST(hits) hits  WHERE REGEXP_CONTAINS(hits.sourcePropertyInfo.sourcePropertyTrackingId, r'82272640')) THEN 'MUG'
      WHEN EXISTS (
    SELECT
      1
    FROM
      UNNEST(hits) hits
    WHERE
      hits.sourcePropertyInfo.sourcePropertyTrackingId = 'Social') THEN 'Social'ELSE 'Website' END) AS Property,
  geoNetwork.country AS Country,
 COUNT(DISTINCT CONCAT(cast(visitId AS STRING),fullVisitorId)) as visits,
 sum(totals.visits) as visits2,
  COUNT(DISTINCT(fullVisitorId)) AS Users,
 h.sourcePropertyInfo.sourcePropertyDisplayName as display,
  SUM((
    SELECT
      SUM(latencyTracking.pageLoadTime)
    FROM
      UNNEST(hits)
    WHERE
      page.pagePath = '/' ))/SUM((
    SELECT
      SUM(latencyTracking.pageLoadSample)
    FROM
      UNNEST(hits)
    WHERE
      page.pagePath = '/')) AS pageloadspeed,
  SUM(totals.newVisits) AS new_,
  SUM(totals.screenviews) AS PAGEVIEWS,
  SUM(totals.bounces) AS BOUNCES,
   sum(CASE
      WHEN device.isMobile = TRUE THEN (totals.visits)
      ELSE 0 END) mobilevisits,
  SUM(CASE
      WHEN trafficSource.medium = 'organic' THEN (totals.visits)
      ELSE 0 END) organicvisits,
  SUM(CASE
      WHEN EXISTS(  SELECT 1  FROM UNNEST(hits) hits  WHERE REGEXP_CONTAINS(hits.eventInfo.eventAction,'register$|registersuccess|new registration|account signup|registro')) THEN 1
      ELSE 0 END) AS NewRegistrations,
  SUM(CASE
      WHEN EXISTS(  SELECT 1  FROM UNNEST(hits) hits  WHERE REGEXP_CONTAINS(hits.eventInfo.eventAction, 'add to cart|add to bag|click to buy|ass to basket|comprar|addtobasket::')) THEN 1
      ELSE 0 END) AS ClickToBuy,
  SUM(totals.transactions) AS Transactions
FROM
  `project.dataset.ga_sessions_*`, UNNEST(hits) as h
WHERE
  1 = 1
  AND PARSE_TIMESTAMP('%Y%m%d', REGEXP_EXTRACT(_table_suffix, r'.*_(.*)')) BETWEEN TIMESTAMP('2017-05-01')
  AND TIMESTAMP('2017-05-01')
GROUP BY
  date,
  Country,
  display
ORDER BY
  visits DESC;

编辑:

我尝试从 FROM 子句中简单地删除 UNNEST(HITS) 命中作为 h，这给了我以下错误:

Error: Cannot access field sourcePropertyInfo on a value with type ARRAY> at [16:14]

我还尝试在子查询中使用它，如下所示:

(select h.sourcePropertyInfo.sourcePropertyDisplayName from unnest(hits) h) as displayname, 

并得到错误:

Scalar subquery produced more than one element

Answer 1

由于您需要在命中级别计算多个值，因此取消嵌套字段命中是最好的方法。缺点是您丢失了 session 级别的总计字段聚合，但您仍然可以解决它。

举个例子:

SELECT
  date,
  CASE
    WHEN REGEXP_CONTAINS(h.sourcePropertyInfo.sourcePropertyTrackingId, r'82272640') THEN 'MUG'
    WHEN h.sourcePropertyInfo.sourcePropertyTrackingId = 'Social' THEN 'Social'ELSE 'Website'
  END AS Property,
  geoNetwork.country AS Country,
  COUNT(DISTINCT CONCAT(CAST(visitId AS STRING),fullVisitorId)) AS visits,
  COUNT(DISTINCT(fullVisitorId)) AS Users,
  h.sourcePropertyInfo.sourcePropertyDisplayName AS display,
  SUM(CASE
      WHEN REGEXP_CONTAINS(h.page.pagepath, r'/') THEN h.latencyTracking.pageLoadTime END) / SUM(CASE
      WHEN REGEXP_CONTAINS(h.page.pagepath, r'/') THEN h.latencyTracking.pageLoadSample END) AS pageloadspeed,
  COUNT(DISTINCT
    CASE
      WHEN totals.newVisits = 1 THEN CONCAT(CAST(visitId AS STRING),fullVisitorId) END) new_visits,
  COUNT(CASE
         WHEN h.type = 'PAGE' THEN h.page.pagepath END) pageviews,
  SUM(CASE
       WHEN (h.isentrance = TRUE AND h.isexit = TRUE) THEN 1 END) bounces,
  COUNT(DISTINCT (CASE
        WHEN device.isMobile = TRUE THEN CONCAT(CAST(visitId AS STRING),fullVisitorId) END)) mobilevisits,
  COUNT(DISTINCT (CASE
        WHEN trafficSource.medium = 'organic' THEN CONCAT(CAST(visitId AS STRING),fullVisitorId) END)) organicvisits,
  SUM(CASE
       WHEN REGEXP_CONTAINS(h.eventInfo.eventAction,'register$|registersuccess|new registration|account signup|registro') THEN 1 END) AS NewRegistrations,
  SUM(CASE
       WHEN REGEXP_CONTAINS(h.eventInfo.eventAction, 'add to cart|add to bag|click to buy|ass to basket|comprar|addtobasket::') THEN 1 END) AS ClickToBuy,
  COUNT(h.transaction.transactionid) transactions
FROM
  `project_id.dataset_id.ga_sessions_*`,
  UNNEST(hits) AS h
WHERE
  1 = 1
  AND PARSE_TIMESTAMP('%Y%m%d', REGEXP_EXTRACT(_table_suffix, r'.*_(.*)')) BETWEEN TIMESTAMP('2017-05-01') AND TIMESTAMP('2017-05-01')
GROUP BY
  date,
  Country,
  display,
  Property

我对我们的数据集运行它，它似乎正在工作。我做了一些改变:

删除了 MAX属性的操作并将其添加到组中。

pageviews 被认为是 hit.type = 'PAGE' 的点击次数。不确定这对于屏幕浏览是否相同。

当有进入和退出事件时计算反弹。

总交易数是对交易 ID 的计数(希望这个字段也被填写在您的数据集中)。