时间戳

首页 > 解决方案 > Bigquery 中的新副本和保留副本

sql - Bigquery 中的新副本和保留副本

问题描述

我正在使用 BigQuery 和 DataStudio 来显示整个几周内绘制的保留情况，当用户是新用户然后在同一周再次使用该应用程序时，我遇到了问题，他既是新用户又在我的计算中保留，我希望它以便他在使用该应用程序的第一周是新用户，然后如果他在 2 周内再次使用该应用程序，他将被“保留”。

这是我的查询：

SELECT
UserID,
DATE,
DATE_DIFF(DATE,PreviousSessionDATE, DAY) as DaysBetweenSessions,
(SELECT
 CASE
WHEN DaysBetweenSessions <= 14 THEN 'Retained'
WHEN DaysBetweenSessions >14 THEN 'Returned'
WHEN DaysBetweenSessions IS NULL AND FirstSessionDATE = DATE THEN 'New'
WHEN DaysBetweenSessions IS NULL THEN 'User has an old version without Retention Parameters'
END) as User_Type
FROM
app_project.analytics_*********.events_*
GROUP BY
1,2,3,4
ORDER BY
DATE DESC,
DaysBetweenSessions DESC,
1,2,3,4

然后结果通常会很好，除非用户多次使用该应用程序并获得 1-14 作为 DaysBetweenSessions 然后在同一周内被计为新的和保留的。

然后在 DataStudio 中，我将执行 YEARWEEK(DATE) 以每周可视化并将 Count_Distinct(UserID) 作为我的指标。

关于我如何整理以便新用户仅在第一周内被视为新用户的任何想法，即使用户在该周内进行培训？ 

Current output in BQ:
UserID     DATE        DaysBetweenSessions     User_Type
123        20180801    NULL                    "New"
123        20180801    0                       "Retained"

和期望的输出

UserID     DATE        DaysBetweenSessions     User_Type
123        20180801    NULL                    "New"

标签： sqlgoogle-bigquerygoogle-data-studioretention

解决方案

可能有一种更简洁的方法可以做到这一点，但是......

WITH CTE AS
(SELECT
UserID,
DATE,
DATE_DIFF(DATE,PreviousSessionDATE, DAY) as DaysBetweenSessions,
(SELECT
CASE
WHEN DaysBetweenSessions <= 14 THEN 'Retained'
WHEN DaysBetweenSessions >14 THEN 'Returned'
WHEN DaysBetweenSessions IS NULL AND FirstSessionDATE = DATE THEN 'New'
WHEN DaysBetweenSessions IS NULL THEN 'User has an old version without Retention 
Parameters'
END) as User_Type,
(SELECT
CASE
WHEN DaysBetweenSessions <= 7 THEN 0
WHEN DaysBetweenSessions >7 THEN 1
WHEN DaysBetweenSessions IS NULL AND FirstSessionDATE = DATE THEN 0
WHEN DaysBetweenSessions IS NULL THEN 2
END) as DaysBetween
FROM
app_project.analytics_*********.events_*
GROUP BY
1,2,3,4
ORDER BY
DATE DESC,
DaysBetweenSessions DESC,
1,2,3,4),

Result as 
(SELECT *, min(User_Type) OVER (PARTITION BY UserID, DaysBetween) minUser_Type
FROM CTE)

SELECT UserID,
DATE,
DaysBetweenSessions,
User_type 
FROM Result 
WHERE NOT (User_Type <> 'New' AND minUser_Type = 'New')

第二部分应添加一个维度，该维度是该周按字母顺序排列的最低 User_Type（因此，如果您将任何内容重命名为按字母顺序小于“新”，它将不起作用，最好使用数字）。

最后一部分应该去掉那周有“新”但 User_Type 行不是“新”的那些。

推荐阅读