DAX学习-计算留存用户

此文档为学习笔记，原课程为佐罗老师的Power BI 自动化用户运营分析

根据行为用户数据，计算留存用户

创建一张日期表(维度表)：

_日期表 = ADDCOLUMNS (
CALENDAR ( date(2022,12,01),today() )
,"年", YEAR ( [Date] )
,"月", MONTH ( [Date] )
)

_日期表：

创建一张用户行为表(事实表)：

_用户行为 = 
var tb={
(1,"u001","2022/12/01"),
(2,"u001","2022/12/15"),
(3,"u001","2023/01/02"),
(1,"u002","2023/01/06"),
(3,"u002","2023/01/07"),
(2,"u003","2023/01/01"),
(3,"u004","2023/01/15"),
(1,"u004","2023/01/16"),
(3,"u005","2023/01/11"),
(2,"u002","2023/02/06")
}
return selectcolumns(tb,"用户行为key",[Value1],"用户key",[Value2],"日期key",[Value3])

_用户行为:

_日期表和_用户行为表的关系是 ：一对多关系

留存用户模型

留存用户的定义

• 过去有过访问的用户，在指定日期后的n天再次访问，即算作留存用户
• 当前日期区间 [t1,t2] 内存在有效行为
• 经过一段时间，在x天后，y天内再次出现有效行为
• 留存用户可分为新用户留存及活跃用户留存

如：

当前日期是：2022/12/1~~2023/2/9，参考日期表

1/当前日期范围中，活跃用户为u001和u002，活跃用户计算参考DAX学习-活跃用户计算

2/当前日期范围中，新用户为u001，新用户计算参考DAX学习-新用户计算

3/指定x天：1天

4/指定y天：7天

5/用户u001：

1. 首次访问点2022年12月
2. 该时间往后x天-y天：2023/1/1~~2023/1/7
3. 其中2023/1/2再次访问，故属于留存用户
4. DAX学习-新用户计算计算得出：u001是新用户
5. u001在2023/1/2是新用户留存

6/用户u002：

1. 首次访问点2023年1月
2. 该时间往后x天-y天：2023/2/1~~2023/2/7
3. 其中2023/2/6再次访问，故属于留存用户
4. DAX学习-活跃用户计算计算得出：u002是活跃用户
5. u002在2023/2/6是活跃用户留存

DAX表达式

• 留存用户
• 以下表达式以2022年12月为例

_users_keep = 
//留存用户
//...][t1,t2][...][t3,t4][...
var x=1
var y=7
var t1=min('_日期表'[Date])  //2022/12/1
var t2=max('_日期表'[Date])  //2022/12/31
var t3=t2+x  //2023/1/1
var t4=t2+y  //2023/1/7
var dates_fiter_t1t2=DATESBETWEEN('_日期表'[Date],t1,t2)  //2022/12/1~~2022/12/31
var dates_fiter_t3t4=DATESBETWEEN('_日期表'[Date],t3,t4)  //2023/1/1~~2023/1/7
var users_t1t2=calculatetable(values('_用户行为'[用户KEY]),dates_fiter_t1t2)  //取t1t2有用户行为的用户
var users_t3t4=calculatetable(values('_用户行为'[用户KEY]),dates_fiter_t3t4)  //取t3t4有用户行为的用户
var users_keep=intersect(users_t1t2,users_t3t4)  //取两者交集，即在t1t2有过用户行为的，又在t3t4又有用户行为的，就算留存用户

return countrows(users_keep)

• 留存用户-新用户
• 以下表达式以2022年12月为例

_users_keep_for_new=
//新用户留存
//...][t1,t2][...][t3,t4][...
var x=1
var y=7
var t1=min('_日期表'[Date])  //2022/12/1
var t2=max('_日期表'[Date])  //2022/12/31
var t3=t2+x  //2023/1/1
var t4=t2+y  //2023/1/7
var dates_fiter_t1t2=DATESBETWEEN('_日期表'[Date],t1,t2)  //2022/12/1~~2022/12/31
var dates_fiter_t3t4=DATESBETWEEN('_日期表'[Date],t3,t4)  //2023/1/1~~2023/1/7
var users_t1t2=calculatetable(values('_用户行为'[用户KEY]),dates_fiter_t1t2)  //取t1t2有用户行为的用户
//取在t1t2有用户行为的用户，那些是新用户
var users_new=filter(
    users_t1t2,
    var firstpoint=calculate(min('_日期表'[Date]),all('_日期表'))
    return firstpoint>=t1 && firstpoint<=t2
)
var users_t3t4=calculatetable(values('_用户行为'[用户KEY]),dates_fiter_t3t4)  //取t3t4有用户行为的用户
var users_keep=intersect(users_new,users_t3t4)  //取两者交集，即在t1t2有过用户行为的新用户，又在t3t4又有用户行为的，就算新用户留存

return countrows(users_keep)

• 留存用户-活跃用户
  -以下表达式以2023年1月为例

_users_keep_for_return=
//活跃用户留存
//...][t1,t2][...][t3,t4][...
var x=1
var y=7
var t1=min('_日期表'[Date])  //2023/1/1
var t2=max('_日期表'[Date])  //2023/1/31
var t3=t2+x  //2023/2/1
var t4=t2+y  //2023/2/7
var dates_fiter_t1t2=DATESBETWEEN('_日期表'[Date],t1,t2)  //2023/1/1~~2023/1/31
var dates_fiter_t3t4=DATESBETWEEN('_日期表'[Date],t3,t4)  //2023/2/1~~2023/2/7
var users_t1t2=calculatetable(values('_用户行为'[用户KEY]),dates_fiter_t1t2)  //取t1t2有用户行为的用户
//取在t1t2有用户行为的用户，那些是留存用户
var users_return=filter(
    users_t1t2,
    var firstpoint=calculate(min('_日期表'[Date]),all('_日期表'))
    return firstpoint<t1
)
var users_t3t4=calculatetable(values('_用户行为'[用户KEY]),dates_fiter_t3t4)  //取t3t4有用户行为的用户
var users_keep=intersect(users_return,users_t3t4)  //取两者交集，即在t1t2有过用户行为的留存用户，又在t3t4又有用户行为的，就算活跃用户留存

return countrows(users_keep)

如下图：2022年12有1个用户留存下来，2023年1月也有个用户留存下来