手机APP信息统计分析系统

用户行为分析
新增用户
1.前天的新增用户
最早的启动app的时间就是昨天

		select

count(*)

from

(select min(createdatms) mintime

from ext_startup_logs

where appid = 'sdk34734'

group by deviceid

having mintime >= getdaybegin(-2) and mintime < getdaybegin(-1)

)t ;

	2.本周的新增用户
		最早的启动app的时间在这一周之内

	select
count(*)
from
(select min(createdatms) mintime
from ext_startup_logs
where appid = 'sdk34734'
group by deviceid
having mintime >= getweekbegin() and mintime < getweekbegin(1)
) t ;

	3.本月的新增用户
		最早的启动app的时间在这一月之内

	select
count(*)
from
(select min(createdatms) mintime
from ext_startup_logs
where appid = 'sdk34734'
group by deviceid
having mintime >= getmonthbegin() and mintime < getmonthbegin(1)
)t ;

活跃用户
	1.前天的日活

	select

count(distinct deviceid)

from ext_startup_logs

where appid = 'sdk34734'

and createdatms >= getdaybegin(-2) and createdatms < getdaybegin(-1);

	2.本周的周活

	select
count(distinct deviceid)
from ext_startup_logs
where appid = 'sdk34734'
and createdatms >= getweekbegin() and createdatms < getweekbegin(1);

	3.本月的月活

	select
count(distinct deviceid)
from ext_startup_logs
where appid = 'sdk34734'
and createdatms >= getmonthbegin() and createdatms < getmonthbegin(1);

	4.本周每一天的日活

	select
formattime(createdatms,'yyyy/MM/dd') day ,count(distinct deviceid)
from ext_startup_logs
where appid = 'sdk34734'
and createdatms >= getweekbegin() and createdatms < getweekbegin(1)
group by formattime(createdatms,'yyyy/MM/dd');

	5.半年的每月的月活

select
formattime(createdatms,'yyyyMM') stdate, count(distinct deviceid) stcount
from ext_startup_logs
where ym >= formattime(getmonthbegin(-5),'yyyyMM') and appid ='sdk34734'
group by formattime(createdatms,'yyyyMM');

	6.连续三周的活跃用户

	select deviceid , count(distinct(formattime(createdatms,'yyyyMMdd',0))) c
from ext_startup_logs
where appid = 'sdk34734'
and concat(ym,day) >= formattime(getweekbegin(-2),'yyyyMMdd')
group by deviceid
having c = 3;

沉默用户
	沉默用户（只访问过一次，超过2天没访问）

	select
count(*)
from
(select deviceid , count(createdatms) dcount,min(createdatms) dmin
from ext_startup_logs
where appid = 'sdk34734'
group by deviceid
having dcount = 1 and dmin < getdaybegin(-1)
)t;

启动次数
	前天的app启动次数

	select
count(deviceid)
from ext_startup_logs
where appid = 'sdk34734'
and ym = formattime(getdaybegin(),'yyyyMM') and day = formattime(getdaybegin(-1),'dd');

版本分布
	前天每个版本的使用用户数

	select
appversion,count(distinct deviceid)
from ext_startup_logs
where appid = 'sdk34734'
and ym = formattime(getdaybegin(),'yyyyMM') and day = formattime(getdaybegin(-1),'dd')
group by appversion;

	本周每天各版本版本使用用户数

	select
formattime(createdatms,'yyyyMMdd'),appversion , count(distinct deviceid)
from ext_startup_logs
where appid = 'sdk34734'
and concat(ym,day) >= formattime(getweekbegin(),'yyyyMMdd')
group by formattime(createdatms,'yyyyMMdd'), appversion;

用户留存
	周留存：本周访问并且是上周的新增用户

	select

distinct s.deviceid

from ext_startup_logs s

where appid = 'sdk34734'

and concat(ym,day) >= formattime(getweekbegin(),'yyyyMMdd')

and concat(ym,day) < formattime(getweekbegin(1),'yyyyMMdd')

and deviceid in (

select distinct t.deviceid

from (

select tt.deviceid , min(tt.createdatms) mintime

from ext_startup_logs tt

where tt.appid = 'sdk34734'

group by tt.deviceid having mintime >= getweekbegin(-1) and mintime < getweekbegin()

) t); 

新鲜度分析
	前天新增用户/前天的活跃用户
自由主题