关于数据分析:常见数据分析场景一留存分析

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

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

“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 辨别），然而若向查看一个利用不同的渠道留存状况（渠道是由媒体、广告位以及起源场景多个参数组成的且条件简单），若对每个渠道独自用条件筛选计算，计算工作的数量会依据渠道的数量间接翻倍；原本一个数据处理工作能够搞定，却要反复建多个工作，区别只是筛选了不同渠道的条件；
解决办法：可先对表记录进行渠道条件判断，并将渠道赋值给长期表的新增字段，再进行渠道关联

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

可通过数据直观的看出不同流动和渠道的留存率，能够借用高留存的渠道进行扩大新用户，复用高留存的流动亮点和模式。

关于数据分析:常见数据分析场景一留存分析

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

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

概念留神点:

首次登陆人数实现代码

分利用维度：回访人数及回放登陆天数实现代码

某利用各渠道在 2021 年首次登陆后 30 天内回访人数代码实现，渠道的回访人数是指在 2021 年的首次登陆归属于该渠道，且在 30 天内再次回访该渠道

剖析利用