选择用户ID首次/最近购买的日期

我正在bigquery中使用google分析数据,希望将上次访问和首次访问的日期聚合到用户id级别,但是我的代码当前正在返回该用户的最大访问日期,只要他们在选定的日期范围内购买,因为我正在使用 MAX() .

如果我删除 最大() 我必须 GROUP 通过 DATE ,但我不希望这样,因为这将为每个用户id返回多行。

这是我的代码,它返回每个用户的一系列日期- last_visit_date 当前正在工作,因为它是唯一可以简单查看用户活动最后日期的日期。有什么建议可以告诉我 last_ord_date 选择订单实际发生的日期?

SELECT
  customDimension.value AS UserID,
  # Last order date
  IF(COUNT(DISTINCT hits.transaction.transactionId) > 0,
    (MAX(DATE)),
    "unknown") AS last_ord_date,

  # first visit date
  IF(SUM(totals.newvisits) IS NOT NULL,
    (MAX(DATE)),
    "unknown") AS first_visit_date,

  # last visit date
  MAX(DATE) AS last_visit_date,

  # first order date
  IF(COUNT(DISTINCT hits.transaction.transactionId) > 0,
    (MIN(DATE)),
    "unknown") AS first_ord_date

FROM
  `XXX.XXX.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 30 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