常见数据分析场景一留存分析数据分析sql留存率

趁着不忙，整理下工作中经常分析的场景！然后数据分析最重要的一点是搞清楚分析的场景和数据概念。

求小程序用户留存相关指标

数据需求背景:
某个品牌有一款小程序，为了增加用户粘性和忠诚度，小程序会定期举办一些线上活动（H5页面嵌入在小程序中），在产品上也会印发活动二维码，用户通过扫描产品身上的二维码或则老用户分享的二维码进入小程序，用户进入小程序的方式称为不同的渠道

需求：分析 2021年，首次在不同渠道和活动登陆的人数，30天/90天的二访人数、登陆天数和次数

文章图片

概念注意点:

"2021年的首次登陆人数" 以及"首次在2021年登陆的人数"：
前者是指该用户在2021年通过某个渠道或活动进行了2021年第一次登陆的人数，后者是在前者的基础上多了一个条件，即在2021年之前没有登陆过，需要加上新用户的筛选过滤条件；
"首次登陆"的概念，因为表格中列出了一些渠道和活动的枚举，需明确“首次登陆”是这些渠道和范围内的首次登陆，还是2021年所有活动和渠道的首次登陆，若是明确范围内的首次登陆，需要做一些渠道和活动的筛选，不然举个极端的情况：若用户在2021年的首次登陆全部来自于一个不在需求范围内的渠道或活动，则这张表格上的数据将全部为0；
时间字段选择：小程序的事件已经做了埋码，需明确这里要用的是“事件发生的时间”，有些采集程序会衍生出其它扩充的时间字段，例如SDK发包的时间，服务器记录事件发生的时间，服务器记录的时间因一些故障在极端情况下可能会和事件发生时间相去甚远；
首次登陆如何用sql实现？对用户分组，用开窗函数对事件发生时间进行升序排列，从排序为1的那条事件记录中判断登陆的渠道和活动。
首次登陆人数实现代码
select c.appid, count(0) from ( select a.openid, a.appid from ( select openid, appid, date, row_number() over( partition by openid order by time asc ) as num--根据时间排序 from coke.jice_events_new where substring(trim(date), 1, 4) = '2021' --范围圈选，在这些活动中的首次登陆 and appid in (1982,1923,1944,1964,1974,2009,2002,2010,1936,1959,1986,1989,1992,1958,1965,1960,1988,1980 ) ) a /*若是找首次在2021年登陆的人，在2020年没有登陆过，可以做个关联过滤 left join ( select openid from coke.jice_events_new where substring(trim(date), 1, 4) = '2020' group by openid ) b on a.openid = b.openid where b.openid is null */ where a.num = 1 group by a.openid, a.appid ) c group by c.appid

分应用维度：回访人数及回放登陆天数实现代码
select e.appid, count(0) from ( select c.appid, d.date, c.openid from ( select a.openid, a.date, a.appid from ( select openid, appid, date, row_number() over( partition by openid order by time asc ) as num from coke.jice_events_new where substring(trim(date), 1, 4) = '2021' and appid in (1982,1923,1944,1964,1974,2009,2002,2010,1936,1959,1986,1989,1992,1958,1965,1960,1988,1980 ) ) a where a.num = 1 group by a.openid, a.date, a.appid ) c join coke.jice_events_new d on c.openid = d.openid and c.appid = d.appid and trim(d.event)='applaunch' --登陆事件可以加上，减少数据量，提高sql效率 where --日期范围，首次登陆后30天内再次登陆 d.date > c.date and date_add(d.date, 0) <= date_add(c.date, 30) group by c.appid, d.date,--若计算登陆天数，一天登陆多次算一次，则需要将登陆日期加入分组 c.openid ) e group by e.appid

以上的留存是应用维度的，可以直接分组计算（由appid区分），但是若向查看一个应用不同的渠道留存情况（渠道是由媒体、广告位以及来源场景多个参数组成的且条件复杂），若对每个渠道单独用条件筛选计算，计算任务的数量会根据渠道的数量直接翻倍；本来一个数据处理任务可以搞定，却要重复建多个任务，区别只是筛选了不同渠道的条件；
解决办法：可先对表记录进行渠道条件判断，并将渠道赋值给临时表的新增字段，再进行渠道关联
某应用各渠道在2021年首次登陆后30天内回访人数代码实现，渠道的回访人数是指在2021年的首次登陆归属于该渠道，且在30天内再次回访该渠道
select e.label, count(0) from ( select c.openid, c.label from ( select a.openid, a.date, a.label from ( select openid, case when ( trim(md) = '' or md is null ) and trim(ct) in ('1011', '1012', '1013') then 'scg' when trim(md) = 'Others_Other' and trim(pl) in ('SMS2', 'SMS', 'SMS1', 'SMS4') then 'SMS' when trim(md) = 'Others_WeChat' and trim(pl) in ( 'KOPlus_Banner_HPKV', 'KOPlus_Banner_R', 'KOPlus_Banner_M', 'KOPlus_Subscription', 'KOPlus_KV_HP', 'MealPlatformbanner' ) then 'KO+' when ( ( ( trim(md) = '' or md is null ) and trim(pl) = 'friend_share' ) or ( trim(md) = 'origin' and trim(pl) = 'friend_share' ) or ( trim(md) = 'Others_WeChat' and trim(pl) = 'ccns_share_poster' ) or trim(md) = 'WEIXIN' ) then 'sharing' when ( trim(md) in('Others_Weibo', 'WEIBO', 'Other') or ( trim(md) = 'Others_WeChat' and trim(pl) <> 'ccns_share_poster' ) or ( trim(md) = 'Others_Other' and trim(pl) like 'dccc%' ) or ( trim(md) = 'Others_Other' and trim(pl) = 'wxpyq' ) ) then 'social media' when trim(md) in('OOH', 'Store', 'Others_Store') then 'offline posters' when ( trim(md) like 'Ads%' or trim(md) = 'Others_OOH' or ( trim(md) = 'Others_Other' and trim(pl) in ('video', 'MI_OTT') ) ) then 'digital ads' when trim(md) in ('Bottle', 'Others_Bottle') then 'bottle' else 'others' end as label, appid, date, row_number() over( partition by openid order by time asc ) as num from coke.jice_events_new where substring(trim(date), 1, 4) = '2021' and appid = 1789 ) a where a.num = 1 ) c join ( select openid, case when ( trim(md) = '' or md is null ) and trim(ct) in ('1011', '1012', '1013') then 'scg' when trim(md) = 'Others_Other' and trim(pl) in ('SMS2', 'SMS', 'SMS1', 'SMS4') then 'SMS' when trim(md) = 'Others_WeChat' and trim(pl) in ( 'KOPlus_Banner_HPKV', 'KOPlus_Banner_R', 'KOPlus_Banner_M', 'KOPlus_Subscription', 'KOPlus_KV_HP', 'MealPlatformbanner' ) then 'KO+' when ( ( ( trim(md) = '' or md is null ) and trim(pl) = 'friend_share' ) or ( trim(md) = 'origin' and trim(pl) = 'friend_share' ) or ( trim(md) = 'Others_WeChat' and trim(pl) = 'ccns_share_poster' ) or trim(md) = 'WEIXIN' ) then 'sharing' when ( trim(md) in('Others_Weibo', 'WEIBO', 'Other') or ( trim(md) = 'Others_WeChat' and trim(pl) <> 'ccns_share_poster' ) or ( trim(md) = 'Others_Other' and trim(pl) like 'dccc%' ) or ( trim(md) = 'Others_Other' and trim(pl) = 'wxpyq' ) ) then 'social media' when trim(md) in('OOH', 'Store', 'Others_Store') then 'offline posters' when ( trim(md) like 'Ads%' or trim(md) = 'Others_OOH' or ( trim(md) = 'Others_Other' and trim(pl) in ('video', 'MI_OTT') ) ) then 'digital ads' when trim(md) in ('Bottle', 'Others_Bottle') then 'bottle' else 'others' end as label,--另存一个标签字段用于关联 date from coke.jice_events_new where appid = 1789 and trim(event) = 'applaunch' and substring(trim(date), 1, 4) > '2020' ) d on c.openid = d.openid and c.label = d.label where d.date > c.date and date_add(d.date, 0) <= date_add(c.date, 30) group by c.openid, c.label ) e group by e.label

分析应用【常见数据分析场景一留存分析】可通过数据直观的看出不同活动和渠道的留存率，可以借用高留存的渠道进行扩展新用户，复用高留存的活动亮点和模式。