google-bigquery - SQL计算留存曲线

Question

我们有一个事件表，用户注册并玩我们的游戏。

假设我们有三个字段

timestamp ts
int  userId
int  eventId               (I.e. 1 = Register, 2 = Login)

我们要计算的是给定日期及以后的留存率。如果用户在上周(即 7 天)玩过，则应被视为活跃

例如假设我想为 2013-08-01 注册的用户设置保留曲线

输出表可能是这样的

Date         Day     Reg 2013-08-01,  Active,    Retention
2013-08-01   1       24 567           24 567     100%
2013-08-02   2       24 567           24 567     100%
2013-08-03   3       24 567           24 567     100%
2013-08-04   4       24 567           24 567     100%
2013-08-05   5       24 567           24 567     100%
2013-08-05   6       24 567           24 567     100%
2013-08-05   7       24 567           24 567     100%
2013-08-05   8       24 567           24 125     98.2%
2013-08-05   9       24 567           24 027     97.8%
2013-08-05  10       24 567           23 997     97.5%
2013-08-05  11       24 567           23 200     96.3%
2013-08-05  12       24 567           22 890     95.3%
....

我的 SQL 技能简直太糟糕了!为提出此 SQL 的任何人提供免费啤酒(或 GT's In Stockholm ...)!

Answer 1

为了获得活跃用户的数量，我可能会尝试在 Google BigQuery 中做类似的事情:

SELECT count(distinct U1.userId, 1000000) as activeUser, 
       left(U1.startTime, 10) AS day 
FROM [YourDataSet.YourTable] as U1 
JOIN EACH [YourDataSet.YourTable] as U2 on U1.userId = U2.userId 
WHERE U2.startTime = U1.startTime -- if the user came today OR
OR (U2.startTime < U1.startTime AND 
    TIMESTAMP(U2.startTime) >= DATE_ADD(TIMESTAMP(U1.startTime), -7, "DAY")) -- if the user came sometime in the past and not more than 7 days
GROUP BY day
ORDER BY day

*请注意，在我的例子中，日期是一个字符串。对于您的特定问题，您可能需要添加自定义条件来处理您的事件类型。还请验证条件以检查它是否早于 7 天，因为我没有测试过这部分。

此查询仅允许获取活跃用户数。对于其余部分，您可能需要在另一个查询中完成。也许有一种方法可以通过联合或类似的东西同时解决所有问题，但这将是一个非常长的查询。希望这对您有所帮助!