此文档为学习笔记,原课程为佐罗老师的Power BI 自动化用户运营分析
根据行为用户数据,计算流失用户
创建一张日期表(维度表):
_日期表 = ADDCOLUMNS (
CALENDAR ( date(2022,12,01),date(2023,01,31) )
,"年", YEAR ( [Date] )
,"月", MONTH ( [Date] )
)
_日期表:
Date | 年 | 月 |
---|---|---|
2022/12/1 | 2022 | 12 |
2022/12/2 | 2022 | 12 |
2022/12/3 | 2022 | 12 |
… | … | … |
2022/12/31 | 2022 | 12 |
2023/1/1 | 2023 | 1 |
2023/1/2 | 2023 | 1 |
2023/1/3 | 2023 | 1 |
… | … | … |
2023/1/31 | 2023 | 1 |
创建一张用户行为表(事实表):
_用户行为 =
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")
}
return selectcolumns(tb,"用户行为key",[Value1],"用户key",[Value2],"日期key",[Value3])
_用户行为:
用户行为key | 用户key | 日期key |
---|---|---|
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 |
_日期表和_用户行为表的关系是 :一对多关系
流失用户模型
流失用户的定义
- 在t时长无任何有效行为
- t时长前有过有效行为
- 且t时常结束前恰好在[t1,t2]内
- [t1,t2]内无有效行为,若存在有效行为,则必须晚于t时常在[t1,t2]中的结束点
如:
①已知用户U002有2次用户行为
用户行为key | 用户key | 日期key |
---|---|---|
1 | u002 | 2023/01/06 |
3 | u002 | 2023/01/07 |
②定义流失节点是:7天
③2023/1/6
的前7+1天(即:2022/12/29-2023/1/5),未发生有效行为
,故流失用户要从2023/1/6
后开始算起
PS:7+1=7天后开始算流失
④2023/1/7
的前7+1天(即:2022/12/30-2023/1/6),有发生有效行为
,2023/1/7
的时间点是活跃用户节点
,故流失用户又要从2023/1/7
后开始算起
⑤2023/1/7
用户行为的后7+1天(即:2023/1/8-2023/1/15),未发生有效行为
,那么流失用户的时间节点就是2023/1/15
日期key | 有过用户行为 |
---|---|
2022/12/29 | - |
… | - |
2023/1/5 | - |
2023/1/6 | ✔ |
2023/1/7 | ✔ |
… | - |
2023/1/15 | 流失时间节点 |
DAX表达式
以用户u002为例,时间到了2023/1/15
_user_lost_7 =
//...][t1=days ...days ... t1-1][t1,t2][...
var days=7+1
var t1=firstdate('_日期表'[date]) //2023/1/15
var t2=lastdate('_日期表'[date]) //2023/1/15
var filter_dates_t1t2=datesbetween('_日期表'[date],t1,t2) // 2023/1/15--2023/1/15
var filter_dates_for_maybe_lost=datesbetween('_日期表'[date],t1 - days,t1 - 1) //可能会流失的时间点:2023/1/7--2023/1/14
var users_maybe_lost=calculatetable(values('_用户行为'[用户key]),filter_dates_for_maybe_lost) //可能会流失的用户 2023/1/7时的u002
var user_lost=filter(
//从可能会流失的用户中筛选:
users_maybe_lost,//可能会流失的用户
var user=calculatetable(values('_用户行为'[用户key]),all('_日期表')) //用户表
//在可能会流失的用户中,最近一次有用户行为的时间点:2023/1/7
var user_last_point_in_days=calculate(lastdate('_用户行为'[日期key]),filter_dates_for_maybe_lost,user)
//最终流失的时间点=最近一次有用户行为的时间+(7+1):2023/1/15
var user_lost_point_in_t1t2=user_last_point_in_days + days
//取用户在当前时间内首次用户行为:无
var user_first_point_in_t1t2=calculate(firstdate('_用户行为'[日期key]),filter_dates_t1t2,user) //无
//如果最终流失的时间点在当前时间区间内,且用户在当前时间内首次行为时间为空 或 晚于 最终流失的时间点,则算为流失
return
(user_lost_point_in_t1t2>=t1 && user_lost_point_in_t1t2<=t2) &&
(user_first_point_in_t1t2 =blank() || user_first_point_in_t1t2 > user_lost_point_in_t1t2)
)
return countrows(user_lost)
下图所示:u002的用户行为时间点及流失时间点
使用参数
①新建参数表
_参数_流失天数_阈值_ =
var tb=GENERATESERIES(1,60,1)
return SELECTCOLUMNS(tb,"param",[Value])
参数_流失天数_阈值:
param |
---|
1 |
2 |
3 |
… |
60 |
②新建度量值
SELECTEDVALUE返回_参数_流失天数_阈值_[param]
唯一引用值,默认值是7
_参数_流失天数_阈值_ = SELECTEDVALUE('_参数_流失天数_阈值_'[param],7)
③流失用户的DAX表达式:
_user_lost_7 =
//...][t1=days ...days ... t1-1][t1,t2][...
var days=[_参数_流失天数_阈值_] + 1 //引用参数
var t1=firstdate('_日期表'[date]) //2023/1/15
var t2=lastdate('_日期表'[date]) //2023/1/15
var filter_dates_t1t2=datesbetween('_日期表'[date],t1,t2) // 2023/1/15--2023/1/15
var filter_dates_for_maybe_lost=datesbetween('_日期表'[date],t1 - days,t1 - 1) //可能会流失的时间点:2023/1/7--2023/1/14
var users_maybe_lost=calculatetable(values('_用户行为'[用户key]),filter_dates_for_maybe_lost) //可能会流失的用户 2023/1/7时的u002
var user_lost=filter(
//从可能会流失的用户中筛选:
users_maybe_lost,//可能会流失的用户
var user=calculatetable(values('_用户行为'[用户key]),all('_日期表')) //用户表
//在可能会流失的用户中,最近一次有用户行为的时间点:2023/1/7
var user_last_point_in_days=calculate(lastdate('_用户行为'[日期key]),filter_dates_for_maybe_lost,user)
//最终流失的时间点=最近一次有用户行为的时间+(7+1):2023/1/15
var user_lost_point_in_t1t2=user_last_point_in_days + days
//取用户在当前时间内首次用户行为:无
var user_first_point_in_t1t2=calculate(firstdate('_用户行为'[日期key]),filter_dates_t1t2,user) //无
//如果最终流失的时间点在当前时间区间内,且用户在当前时间内首次行为时间为空 或 晚于 最终流失的时间点,则算为流失
return
(user_lost_point_in_t1t2>=t1 && user_lost_point_in_t1t2<=t2) &&
(user_first_point_in_t1t2 =blank() || user_first_point_in_t1t2 > user_lost_point_in_t1t2)
)
return countrows(user_lost)
下图所示:当参数选择15
天,那么流失点是2023/1/23