sql - 比格查询 : How to query for a rolling monthly user active/churn

Question

所以我有一个包含新闻文章的网站，我试图计算每个月的 4 个用户类型。用户类型有:

<强>1。新用户:当月注册(首次查看文章)并在当月查看过文章的用户。

<强>2。保留用户:上个月的新用户或上个月和当月查看过文章的用户。

<强>3。流失用户:上个月未查看过文章的新用户或保留用户，或者上个月流失的用户。

<强>4。复活的用户:上个月流失的用户，在本月查看过一篇文章。

**User Table A - Unique User Article Views**
- Current month = 2019-04-01 00:00:00 UTC

| user_id    | viewed_at                 |
------------------------------------------
| 4          | 2019-04-01 00:00:00 UTC   |
| 3          | 2019-04-01 00:00:00 UTC   |
| 2          | 2019-04-01 00:00:00 UTC   |
| 1          | 2019-03-01 00:00:00 UTC   |
| 3          | 2019-03-01 00:00:00 UTC   |
| 2          | 2019-02-01 00:00:00 UTC   |
| 1          | 2019-02-01 00:00:00 UTC   |
| 1          | 2019-01-01 00:00:00 UTC   |


The table above outlines the following user types:

2019-01-01
* User 1: New

2019-02-01
* User 1: Retained
* User 2: New

2019-03-01
* User 1: Retained
* User 2: Churned
* User 3: New

2019-04-01
* User 1: Churned
* User 2: Resurrected
* User 3: Retained
* User 4: New

我想要的表计算每个月每种用户类型的不同 user_id。

| month_viewed_at           | ut_new | ut_retained | ut_churned | ut_resurrected
------------------------------------------------------------------------------------
| 2019-04-01 00:00:00 UTC   | 1      | 1           | 1          | 1
| 2019-03-01 00:00:00 UTC   | 1      | 1           | 1          | 0
| 2019-02-01 00:00:00 UTC   | 1      | 1           | 0          | 0
| 2019-01-01 00:00:00 UTC   | 1      | 0           | 0          | 0 

Answer 1

I simply am not sure where to start

希望您阅读我所有的评论并亲自尝试一些东西，但由于我没有看到任何更新，我想您仍然停留在这里 - 所以我们开始......

以下内容适用于 BigQuery 标准 SQL，应该为您提供指导

#standardSQL
WITH temp1 AS (
  SELECT user_id,
    FORMAT_DATE('%Y-%m', DATE(viewed_at)) month_viewed_at, 
    DATE_DIFF(DATE(viewed_at), '2000-01-01', MONTH) pos,
    DATE_DIFF(DATE(MIN(viewed_at) OVER(PARTITION BY user_id)), '2000-01-01', MONTH) first_pos
  FROM `project.dataset.table`
), temp2 AS (
  SELECT *, pos = first_pos AS new_user
  FROM temp1
), temp3 AS (
  SELECT *, LAST_VALUE(new_user) OVER(win) OR pos - 1 = LAST_VALUE(pos) OVER(win) AS retained_user
  FROM temp2
  WINDOW win AS (PARTITION BY user_id ORDER BY pos RANGE BETWEEN 1 PRECEDING AND 1 PRECEDING)
)
SELECT month_viewed_at,
  COUNTIF(new_user) AS new_users,
  COUNTIF(retained_user) AS retained_users
FROM temp3
GROUP BY month_viewed_at
-- ORDER BY month_viewed_at DESC

如果应用到您的示例数据 - 结果是

Row month_viewed_at new_users   retained_users   
1   2019-04         1           1    
2   2019-03         1           1    
3   2019-02         1           1    
4   2019-01         1           0    

在temp1中，我们通过将viewed_at格式化为所需的格式来准备数据，以便在输出广告中呈现，我们还将其转换为呈现自某些抽象数据(2000-02-02)以来的连续月份数，因此我们可以将分析功能与 RANGE 结合使用，而不是 ROWS
在 temp2 中，我们只是简单地识别新用户，在 temp3 中 - 保留用户

我认为，这是一个好的开始，所以我把剩下的留给你