企业核心业务分析指标(难度中)

1.每日用户留存明细

//以下例子是留存一天的
insert overwrite table dws_user_retention_day
partition(dt="2019-12-21")
select
    nm.mid_id,
    nm.user_id,
    ...,
    nm.create_date,
    1 retention_day
from
    dws_uv_detail_day ud           //日活表和新增表join但是注意日期不一样
join dws_new_mid_day nm      
    on ud.mid_id = nm.mid_id
where ud.dt="2019-12-21" and nm.create_date=date_add("2019-12-21",-1);

//用户留存数
insert into table ads_user_retention_day_count
select
    create_date,
    retention_day,
    count(*) retention_count
from
    dws_user_retention_day
where dt="2019-12-21"
group by create_date,retention_day;

 

//用户留存率
insert into table ads_user_retention_day_rate
select
    "2019-12-21",
    ar.create_date,
    ar.retention_day,
    ar.retention_count,
    am.new_mid_count,
    ar.retention_count/am.new_mid_count*100
from
    ads_user_retention_day_count ar
join
    ads_new_mid_count am
on ar.create_date=am.create_date

 

//沉默用户数，在7天前活跃一次，之后再也没活跃过
insert into table ads_slient_count
select 
    "2019-12-20"
    count(*)
from
    (select 
        mid_id
    from dws_uv_detail_day
        where dt<"2019-12-20"
        group by mid_id
        having count(*) = 1
        and min(dt) < date_add("2019-12-20",-7)
    )