同期群cohort留存分析

1 概述

1.1 概念

Cohort Analysis，群组分析，也就是对这些具有不同特征的人群进行对比分析，以发现他们在时间维度下的行为差异，用户留存分析时常使用群组分析。

1.2 价值

同期群留存分析可以更好的帮助分析用户留存情况，及时发现问题节点，并帮助做出迭代的决策。

2 示例场景

3 实现思路

按月统计，计算每月留存人数及留存率。

4 实现步骤

4.1 数据源

4.1.1 上传excel文件

4.1.2 添加表资源

4.2 数据模型

4.2.1 新建数据模型

4.2.2 新建SQL查询

基于创建的数据源，完成相关计算，如“首购日期”、“首购距今时长”等

SQL代码

with t as (
    select
        订单日期,
        客户_ID,
        销售额,
        trunc(订单日期, 'month') as 购买月份
    from
        PUBLIC.订单
    where
        订单日期 <= '2017-12-31'
),
t1 as (
    select
        购买月份,
        客户_ID,
        sum(销售额) as 月销售额
    from
        t
    group by
        购买月份,
        客户_ID
    order by
        购买月份,
        客户_ID
),
t2 as (
    select
        客户_ID,
        min(购买月份) as 首购月份
    from
        t1
    group by
        客户_ID
),
t3 as (
    select
        购买月份,
        首购月份,
        datediff('mm', 首购月份, 购买月份) as 月份间隔,
        '2017-12-31' as 分析截止日期,
        datediff('mm', 购买月份, '2017-12-31') as 月份趋势辅助列,
        t1.客户_ID,
        月销售额,
        to_char(首购月份, 'yyyy-mm') as 首购月份年月
    from
        t1
        left join t2 on t1.客户_ID = t2.客户_ID
)
select
    *
from
    t3
order by
    客户_ID,
    购买月份