如何获取自BigQuery上一次会话以来的天数 [英] How to get Days since last session in BigQuery

问题描述

我正在尝试从BigQuery中的原始GA数据中获取自上次会话以来的天数.我看到当前会话的visitStartTime，但是如何获取上一个会话时间?

I'm trying to get Days since last session out of my raw GA data in BigQuery. I see visitStartTime for the current session, but how can I get the previous session time?

推荐答案

不确定这是否是您所需要的，但希望这能给您一些有关获得前几天的见识:

Not sure if this is what you need but hopefully this gives you some insights on getting previous days:

SELECT
  fv,
  IF(dts IS NULL, NULL, DATE_DIFF(CURRENT_DATE(), PARSE_DATE("%Y%m%d", dts), DAY) ) count_days
FROM(
  SELECT 
    fullvisitorid fv,
    ARRAY_AGG(DISTINCT date ORDER BY date DESC)[SAFE_OFFSET(1)] dts
  FROM `dataset.ga_sessions_*`
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)) AND  FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 0 DAY))
  GROUP BY 1
)

正如您在评论中所说，您将每天运行此分析，因此我将 CURRENT_DATE()变量用作参考.

As you said in your comment, you'll be running this analyzes daily so I used the CURRENT_DATE() variable as reference.

它为每个客户提供自上次会话以来经过了多少天.如果之前没有会话，则它将返回 NULL (因此您可以在分析中保留绝对值以供参考).

It gives for each customer how many days passed since last session. If there's no session before, then it returns NULL (so you can keep absolute values for reference in your analyzes).

您可以尝试使用此查询并根据自己的需要进行调整，例如获取自上次会话以来过去几天的平均值，等等.

You can play around with this query and adapt as you see fit, such as taking averages of past days since last session and so on.

这篇关于如何获取自BigQuery上一次会话以来的天数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！