在Kusto中按时间窗口计算无前置对比的用户留存率

需求说明 #

需要按指定时间窗口计算用户留存率，核心要求是不排除前置时间窗口已出现的用户：每个窗口的初始用户为该窗口内所有活跃用户（无论之前是否活跃过），后续窗口统计这些初始用户的留存情况。

示例数据 #

let T = datatable(UserId:string, Timestamp:datetime)
[
    // May
    'A', datetime(2022-05-01),
    'D', datetime(2022-05-06),
    'C', datetime(2022-05-10),
    'B', datetime(2022-05-15),
    'E', datetime(2022-05-20),
    // June
    'C', datetime(2022-06-02),
    'T', datetime(2022-06-02),
    'J', datetime(2022-06-02),
    'H', datetime(2022-06-13),
    'T', datetime(2022-06-13),
    'G', datetime(2022-06-15),
    'B', datetime(2022-06-15),
    'S', datetime(2022-06-18),
    'M', datetime(2022-06-18),
    'L', datetime(2022-06-20),
    'Z', datetime(2022-06-25),
    // July
    'C', datetime(2022-07-04),
    'T', datetime(2022-07-04),
    'M', datetime(2022-07-04),
    'G', datetime(2022-07-10),
    'L', datetime(2022-07-10)
];

参数配置 #

• start = datetime(2022-05-01)
• end = datetime(2022-08-01)
• time window = 28d

预期结果 #

现有方案的局限 #

手动多次调用new_activity_metrics并union结果的方式存在明显不足：

• 无法动态适配任意起止时间，需要手动枚举每个窗口起始点，扩展性差
• new_activity_metrics默认会过滤前置窗口已活跃的用户，不符合需求中的全量用户统计逻辑

动态解决方案 #

以下查询可自动生成所有时间窗口，无需手动枚举，且完全满足无前置过滤的留存计算需求：

let start = datetime(2022-05-01);
let end = datetime(2022-08-01);
let window_size = 28d;

// 生成所有时间窗口的起始点
let windows = range from_Timestamp start to end step window_size;

// 预处理用户与所属窗口的关联（每个用户每个窗口仅保留一条记录）
let user_window_mapping = T
| summarize first_active = min(Timestamp) by UserId
| extend window_start = bin(first_active, window_size)
| where window_start >= start and window_start < end
| project UserId, window_start;

// 计算每个窗口的初始用户在后续窗口的留存情况
windows
| join kind=leftouter (
    user_window_mapping
    | project UserId, from_window = window_start
) on $left.from_Timestamp == $right.from_window
// 生成当前窗口的所有后续窗口（包括自身）
| mv-expand to_Timestamp = range(from_Timestamp, end, window_size) to typeof(datetime)
// 匹配用户在后续窗口的活跃情况
| join kind=leftouter (
    user_window_mapping
    | project UserId, to_window = window_start
) on UserId and $left.to_Timestamp == $right.to_window
// 按起始窗口和目标窗口分组统计
| summarize 
    Retained = dcountif(UserId, isnotnull(to_window)),
    TotalInitial = dcount(UserId)
    by from_Timestamp, to_Timestamp
// 计算留存率，处理初始用户数为0的场景
| extend RetentionRate = case(
    TotalInitial == 0, real(null),
    Retained * 1.0 / TotalInitial
)
// 调整列顺序并排序
| project from_Timestamp, to_Timestamp, Retained, RetentionRate
| sort by from_Timestamp, to_Timestamp

方案说明 #

1. 动态生成窗口：通过range函数自动生成所有符合条件的窗口起始点，适配任意起止时间和窗口大小
2. 用户窗口映射：预处理用户首次活跃所属的窗口，确保每个用户每个窗口仅统计一次
3. 留存匹配：对每个起始窗口，关联后续所有窗口的用户活跃情况，统计留存数
4. 留存率计算：处理初始用户数为0的特殊情况，返回NaN与预期结果一致

内容的提问来源于stack exchange，提问作者Naresh Kumar