自上次会话以来的天数-适用于具有会话级自定义维度的用户-BigQuery-Google Analytics(分析) [英] Days since last session - for users with session level custom dimension - BigQuery - Google Analytics

问题描述

我有以下SQL查询，该查询来自Lunametrics博客.工作良好.但是，我想做的是在显示会话级别自定义变量的值的附加列.

I've got the following SQL query, which I got from the Lunametrics blog. Works fine. However, what I want to be able to do is have an additional column showing the value of a session level custom variable.

我现在拥有的是:

SELECT
  fullvisitorid,
  visitId,
  DATEDIFF(SEC_TO_TIMESTAMP(visitStartTime), 
SEC_TO_TIMESTAMP(prevVisitStartTime)) AS daysSinceLastSession,
 FLOOR((visitStartTime - prevVisitStartTime)/60) as minutesSinceLastSession
FROM (
 SELECT
fullvisitorid,
visitId,
visitStartTime,
LAG(visitStartTime) OVER (PARTITION BY fullvisitorid ORDER BY visitStartTime 
ASC) AS prevVisitStartTime
FROM
TABLE_DATE_RANGE([DATA],
TIMESTAMP ('2017-04-01'),
TIMESTAMP ('2017-04-08')))

我一直在尝试为索引为30的自定义维度引入列，但没有成功.基本上，我只希望能够看到包含上次自定义维度的访问者自上届会议以来的日子.我的代码是:

I've been trying to introduce a column for a custom dimension with index 30, but without success. Basically, I just want to be able to see the days since last session for visitors which contained this custom dimension. My code for this is:

max(case when hits.customdimensions.index = 30 then hits.customdimensions.value end) customerId

这显示了我认为我可以将这个自定义维度作为新列引入的方法，但是不起作用.

this shows how I thought I could introduce this custom dimension as a new column, but does not work.

 SELECT
 fullvisitorid,
 max(case when hits.customdimensions.index = 30 then 
hits.customdimensions.value end) customerId,
 visitId,
 DATEDIFF(SEC_TO_TIMESTAMP(visitStartTime), 
 SEC_TO_TIMESTAMP(prevVisitStartTime)) AS daysSinceLastSession,
 FLOOR((visitStartTime - prevVisitStartTime)/60) as minutesSinceLastSession
 FROM (
 SELECT
    fullvisitorid,
    max(case when hits.customdimensions.index = 30 then 
    hits.customdimensions.value end) customerId,
    visitId,
    visitStartTime,
    LAG(visitStartTime) OVER (PARTITION BY fullvisitorid ORDER BY 
    visitStartTime ASC) AS prevVisitStartTime,



   FROM
   TABLE_DATE_RANGE([DATA],
   TIMESTAMP ('2017-04-01'),
   TIMESTAMP ('2017-04-08')))

感谢收到任何建议.

推荐答案

MAX是一个聚合函数-您必须以某种方式将其分组，或者在整个表中使用 GROUP BY 或在行内，例如 WITHIN RECORD 或 WITHIN hits

MAX is an aggregation function - you have to group it somehow, either over the whole table with GROUP BY or within the row, e.g. with WITHIN RECORD or WITHIN hits

要获取每个会话的自定义维度值，您需要 MAX(IF(hits.customdimensions.index = 30，hits.customdimensions.value，NULL))WITHIN RECORD

To get custom dimension values for each session you need to MAX( IF(hits.customdimensions.index = 30,hits.customdimensions.value,NULL) ) WITHIN RECORD

此行首先生成每个会话的值列表( RECORD )和 customDimension -如果index为30，则为值，否则为NULL: MAX(NULL，NULL，NULL，...，< hits.cd30>的值，...，NULL，NULL，...<另一个hits.cd30> ;, ...，NULL)

This line is first producing a list of values for each session (RECORD) and customDimension - the value if index is 30 and NULL else: MAX(NULL, NULL, NULL, ..., <value for hits.cd30>, ... , NULL, NULL, ... <another hits.cd30>, ... , NULL)

其中 NULL 是可能的最低值.字符串按字母顺序排序(实际上是按代码表排序，但它们包含按字母顺序排序的字符)-由于您是在会话级汇总匹配级别的customDimensions，因此列表中可能包含多个非null值，以用于具有多个匹配项的会话，因为是多个cd30. MAX()返回字母中最新出现的一个:"aab"<"aac"<"b"

Where NULL is the lowest possible value. Strings are sorted alphabetically (actually by code table, but they contain characters sorted alphabetically) - since you're aggregating hit-level customDimensions on session level your list might contains multiple non-null values for sessions with more than one hit, because there might be multiple cd30s. MAX() returns the one that comes latest in the alphabet: "aab" < "aac" < "b"

这篇关于自上次会话以来的天数-适用于具有会话级自定义维度的用户-BigQuery-Google Analytics(分析)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！