記錄在工作中建立的用戶流失模型

一、數(shù)據(jù)監(jiān)控

通過日?qǐng)?bào)、周報(bào)監(jiān)控用戶留存率及流失率

二、發(fā)現(xiàn)問題

用戶流失率行業(yè)內(nèi)較高

三、分析目的

通過現(xiàn)有數(shù)據(jù)搭建用戶流失模型，采用手段提高留存降低流失

四、數(shù)據(jù)分析

1. sql取數(shù)

代碼見文章末尾

image.png

附：sql邏輯

select i.uid,i.userid,i.daudays,i.recentdate,i.playdate,i.download,i.likes,k.uid,k.songlist,k.artist,k.scene,k.album,k.song
from
(select g.uid,e.userid,g.daudays,g.recentdate,g.playdate,g.download,h.likes
from
(select e.uid,e.userid,e.daudays,e.recentdate,e.playdate,f.download
from
(select c.uid,c.userid,c.daudays,c.recentdate,d.playdate
from
(select a.uid,a.userid,a.daudays,b.recentdate
from
(select uid,userid,count(distinct cdate) as daudays
from logformat.log_format_app_startup 
where cdate>='20200215' and cdate<='20200317'  
and version like '8%'
and producttype='apk'
group by 1,2) as a--近30天設(shè)備活躍天數(shù)

join

(select t.uid,t.cdate as recentdate
from
(select uid,cdate,
row_number() over (partition by uid order by cdate desc) as rank
from logformat.log_format_app_startup 
where cdate>='20200215' and cdate<='20200317'
and version like '8%'
and producttype='apk')t
where rank=1
) as b--最近一次活躍的日期--
on a.uid = b.uid) as c

left join

(select tt.uid,tt.cdate as playdate
from
(select uid,cdate,
row_number() over (partition by uid order by cdate desc) as rank
from logformat.log_format_app_startup 
where cdate>='20200215' and cdate<='20200317'
and version like '8%'
and producttype='apk'
and caction in ('play','localplay'))tt
where rank=1
) as d--近最近一次發(fā)起播放的日期
on c.uid = d.uid) as e

left join

(select event_baiduid,count(1) as download
from music_mds_bhv_download
where event_day>='20200215' and  event_day<='20200317' 
and event_terminal_type='wiseclient' and event_baiduid<>'' and event_baiduid is not null
group by 1) as f--近30天下載次數(shù)--
on e.uid = f.event_baiduid) as g

left join 


(select event_baiduid,count(1) as likes
from music_mds_bhv_fav
where event_day>='20200215' and  event_day<='20200317' 
and event_terminal_type='wiseclient' and event_baiduid<>'' and event_baiduid is not null
and fav_type in ('android_fav','iphone_fav')
group by 1) as h --近30天收藏次數(shù)--
on g.uid = h.event_baiduid) as i

left join

(select j.uid,j.songlist,j.artist,j.scene,j.album,j.song
from
(select event_urlparams ['cuid'] as uid,
sum(case when event_urlparams['pathtype']='songlist' then 1 else 0 end) as songlist ,
sum(case when event_urlparams['pathtype']='artist' then 1 else 0 end) as artist ,
sum(case when event_urlparams['pathtype']='scene' then 1 else 0 end) as scene ,
sum(case when event_urlparams['pathtype']='album' then 1 else 0 end) as album ,
sum(case when event_urlparams['pathtype']='' then 1 else 0 end) as song 
from udw.udw_event
where event_day>='20200215' and  event_day<='20200317' 
and event_pid='121' and event_action in ('music_mobile_app_play','music_mobile_app_other')
and event_urlparams['action'] in ('play','localplay')
and event_urlparams ['mod'] = 'ios'
group by 1

union all

select event_urlparams ['cuid'] as uid,
sum(case when (size(split(event_urlparams['from'],'_'))>2 and regexp_extract(event_urlparams['from'],'^(.*)\\_(.*?)\\_([0-9,]+)$',3) <> '')='songlist') then 1 else 0) as songlist,
sum(case when (size(split(event_urlparams['from'],'_'))>2 and regexp_extract(event_urlparams['from'],'^(.*)\\_(.*?)\\_([0-9,]+)$',3) <> '')='artist') then 1 else 0) as artist,
sum(case when (size(split(event_urlparams['from'],'_'))>2 and regexp_extract(event_urlparams['from'],'^(.*)\\_(.*?)\\_([0-9,]+)$',3) <> '')='scene') then 1 else 0) as scene,
sum(case when (size(split(event_urlparams['from'],'_'))>2 and regexp_extract(event_urlparams['from'],'^(.*)\\_(.*?)\\_([0-9,]+)$',3) <> '')='album') then 1 else 0) as album,
sum(case when (size(split(event_urlparams['from'],'_'))>2 and regexp_extract(event_urlparams['from'],'^(.*)\\_(.*?)\\_([0-9,]+)$',3) <> '')='') then 1 else 0) as song
from udw.udw_event
where event_day>='20200215' and  event_day<='20200317' 
and event_pid='121' and event_action in ('music_mobile_app_play','music_mobile_app_other')
and event_urlparams['action'] in ('play','localplay')
and event_urlparams ['mod'] = 'android'
group by 1) as j ) as k--是否在僅30天內(nèi)從歌單、場(chǎng)景、藝人、專輯、單曲維度發(fā)起過播放--
on i.uid = k.uid