趁着不忙，整顿下工作中常常剖析的场景！而后数据分析最重要的一点是搞清楚剖析的场景和数据概念。
求小程序用户留存相干指标
数据需要背景:
某个品牌有一款小程序，为了减少用户粘性和忠诚度，小程序会定期举办一些线上流动（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 ) cgroup 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 ) egroup 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 ) egroup by e.label剖析利用
可通过数据直观的看出不同流动和渠道的留存率，能够借用高留存的渠道进行扩大新用户，复用高留存的流动亮点和模式。