在单个查询中求嵌套和未嵌套数据的总和

我正在处理嵌套的Google Analytics数据以构建查询,我需要取消3个级别以获得我需要的所有字段,但一旦取消我的 .totals 我认为字段太高了,因为它们的值正在重复。

例如,我无法识别命中级别的反弹,所以我需要使用 totals.bounces 获取此值。

我如何调整下面的查询,以获得反弹和收入的正确总和,以及未列账价值的总和?

SELECT
  customDimension.value AS UserID,
  # Visits from this individual
  SUM(totals.visits) AS visits,
  # Orders from this individual
  COUNT(DISTINCT hits.transaction.transactionId) AS orders,
  # AOV
  SAFE_DIVIDE(SUM(hits.transaction.transactionRevenue)/1000000 , COUNT(DISTINCT hits.transaction.transactionId)) AS AOV,
  #Bounces from this individual
   IFNULL(SUM(totals.bounces),
    0) AS bounces,
  IFNULL(SUM(hits.transaction.transactionRevenue)/1000000,
    0) AS revenue,
  # Conversion rate of the individual
  SAFE_DIVIDE(COUNT(DISTINCT hits.transaction.transactionId),COUNT(DISTINCT CONCAT(CAST(fullVisitorId AS STRING),CAST(visitId AS STRING)))) AS conversion_rate,

  ROUND(IFNULL(SUM(totals.bounces)/COUNT(DISTINCT CONCAT(CAST(fullVisitorId AS STRING),CAST(visitId AS STRING))),
      0),5) AS bounce_rate,

FROM
  `MY.DATA.ga_sessions_20*` AS t
CROSS JOIN
  UNNEST (hits) AS hits
CROSS JOIN
  UNNEST(t.customdimensions) AS customDimension
CROSS JOIN
  UNNEST(hits.product) AS hits_product

  WHERE parse_date('%y%m%d', _table_suffix) between 
DATE_sub(current_date(), interval 7 day) and
DATE_sub(current_date(), interval 1 day)

  AND customDimension.index = 2
  AND customDimension.value NOT LIKE "true"
  AND customDimension.value NOT LIKE "false"
  AND customDimension.value NOT LIKE "undefined"
  AND customDimension.value IS NOT NULL
GROUP BY
  UserID,
  hits.eventInfo.eventCategory