google-analytics - 如何根据事件在 BigQuery 中创建 channel 路径？

Question

我想在用户级别的 Bigquery 中创建 channel 路径。我希望在交易发生时结束路径。下次访问将开始新的路径。目前，我每个用户都有一条路径来汇总所有交易。请参阅下面提供的代码。我还包括了当前的 OUTPUT TABLE 和所需的 OUTPUT TABLE。

我的想法是创建一个新列来计算交易。该值将从 0 开始，并且需要在事务发生后增加 1。然后我会将此值与 user_id 值合并，并将聚合字符串分组到该变量上。但我不知道该怎么做。

提前致谢!

圭多

#standardSQL
WITH yourTable AS (
  SELECT 1 AS user_id,'1a' as visit_id, '2017-01-01 14:10:12' AS DATETIME, 
'google cpc' AS channelgrouping, 0 AS transaction , 1 as visit UNION ALL
  SELECT 1, '1b', '2017-01-01 20:10:12', 'email', 1, 1 UNION ALL
  SELECT 1, '1c','2017-01-03 08:10:12', 'direct', 0, 1 UNION ALL
  SELECT 1, '1d','2017-01-04 13:10:14', 'organic', 1, 1
)
SELECT 
  user_id, 
  STRING_AGG(channelgrouping, ' > ' ORDER BY DATETIME) AS channelgrouping_path,
  SUM(transaction) AS transaction,
  SUM(visit) AS visits
 FROM yourTable
GROUP BY user_id

输出表

user_id|channgelgrouping_path                |Transactions|Visits
1      |google cpc > email > direct > organic| 2          | 4

期望的输出表

user_id|channgelgrouping_path                |Transactions|Visits
1      |google cpc > email                   | 1          | 2
1      |direct > organic                     | 1          | 2

Answer 1

试试下面

#standardSQL
WITH yourTable AS (
  SELECT 1 AS user_id,'1a' AS visit_id, '2017-01-01 14:10:12' AS DATETIME, 
'google cpc' AS channelgrouping, 0 AS transaction , 1 AS visit UNION ALL
  SELECT 1, '1b', '2017-01-01 20:10:12', 'email', 1, 1 UNION ALL
  SELECT 1, '1c','2017-01-03 08:10:12', 'direct', 0, 1 UNION ALL
  SELECT 1, '1d','2017-01-04 13:10:14', 'organic', 1, 1
)
SELECT
  user_id, 
  STRING_AGG(channelgrouping, ' > ' ORDER BY DATETIME) AS channelgrouping_path,
  SUM(transaction) AS transaction,
  SUM(visit) AS visits
FROM (
  SELECT 
    *, 
    SUM(transaction) OVER(PARTITION BY user_id ORDER BY datetime 
                ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    ) AS grp
  FROM yourTable
)
GROUP BY user_id, IFNULL(grp, 0)