1.新手漏斗
SELECT
a.pt as 日期,
count(DISTINCT a.device_id) as 新增激活用户,
round(sum(g1)/count(DISTINCT a.device_id), 3) as 成功进入游戏曝光,
round(sum(if(game_level>=1, 1, 0))/count(DISTINCT a.device_id), 3) as 通过第一关,
round(sum(g2)/count(DISTINCT a.device_id), 3) as 1元弹框红包弹框曝光,
round(sum(g3)/count(DISTINCT a.device_id), 3) as 游戏页一元红包点击,
round(sum(g4)/count(DISTINCT a.device_id), 3) as 领取第一关奖励点击,
round(sum(if(game_level>=2, 1, 0))/count(DISTINCT a.device_id), 3) as 通过第二关,
round(sum(if(game_level>=3, 1, 0))/count(DISTINCT a.device_id), 3) as 通过第三关,
round(sum(if(game_level>=4, 1, 0))/count(DISTINCT a.device_id), 3) as 通过第四关,
round(sum(if(game_level>=5, 1, 0))/count(DISTINCT a.device_id), 3) as 通过第五关,
round(sum(if(game_level>=6, 1, 0))/count(DISTINCT a.device_id), 3) as 通过第六关,
round(sum(if(game_level>=9, 1, 0))/count(DISTINCT a.device_id), 3) as 通过第九关,
round(sum(if(game_level>=10, 1, 0))/count(DISTINCT a.device_id), 3) as 通过第10关,
round(sum(if(game_level>=15, 1, 0))/count(DISTINCT a.device_id), 3) as 通过第15关,
round(sum(if(game_level>=20, 1, 0))/count(DISTINCT a.device_id), 3) as 通过第20关,
round(sum(g5)/count(DISTINCT a.device_id), 3) as 第五关活动机会曝光,
round(count(DISTINCT wx_open_id)/count(DISTINCT a.device_id), 3) as 微信登录,
round(count(DISTINCT id_card)/count(DISTINCT a.device_id), 3) as 身份证绑定,
round(sum(if(COALESCE(money, 0)>0, 1,0))/count(DISTINCT a.device_id), 3) as 提现成功
from (
SELECT if(Sort_array(guide)[size(guide)-1] >=1, 1, 0) as g1,
if(Sort_array(guide)[size(guide)-1] >= 2, 1, 0) as g2,
if(Sort_array(guide)[size(guide)-1] >=3, 1, 0) as g3,
if(Sort_array(guide)[size(guide)-1] >=4, 1, 0) as g4,
if(Sort_array(guide)[size(guide)-1] >= 5, 1, 0) as g5,
device_id, wx_open_id, pt, id_card
from
sync_mongo_box.extract_hztt_app_source__userinfo
WHERE pt BETWEEN '${start}' and '${end}'
AND SUBSTR(create_time,1,10) = pt
) as a
LEFT JOIN
(
select device_id, tg_num as game_level, pt from sync_mongo_box.extract_hztt_app_source__userdaystat
WHERE pt BETWEEN '${start}' and '${end}' and tg_num >= 1
) as c
on a.device_id = c.device_id and a.pt=c.pt
LEFT JOIN
(
SELECT sum(COALESCE(money, 0)) as money, device_id, pt
FROM sync_mongo_box.extract_hztt_app_source__extractcashrecord
WHERE pt BETWEEN '${start}' and '${end}'
AND status = 3 GROUP by device_id, pt
) as b
on a.device_id = b.device_id and a.pt = b.pt
group by a.pt;
推人游戏启动漏斗
with a as(
select device_id,pt from sync_mongo_box.extract_trmoney_app_source__userinfo
WHERE pt BETWEEN '${start}' and '${end}'
AND SUBSTR(create_time,1,10) = '${end}' and create_time >="2020-02-26 19:00" GROUP by device_id,pt -- 新增表
),
b as(
select device_id,ngx_date,act_page,event_name from liquid_log_project.log_trmoney_android
where ngx_date BETWEEN '${start}' and '${end}' and ngx_date_time >="2020-02-26 19:00"
)
SELECT b.ngx_date,
count( distinct if(event_name="b_entry_page" and act_page = "p_welcome", a.device_id, null)) 进入loading页的用户,
count( distinct if(event_name="b_click_enter", device_id, null)) 同意隐私协议的用户,
count( distinct if(event_name="b_game_plugin_install_already", device_id, null)) 插件安装成功的用户,
count( distinct if(event_name="b_entry_page" and act_page= "p_home_activity", device_id, null)) 成功进入app首页的用户,
count( distinct if(event_name="b_click_game_start", device_id, null)) 点击开始游戏的用户,
count( distinct if((event_name="b_entey_page" and act_page = "b_game_plugin_init")
or event_name = "b_game_plugin_init", device_id, null)) 开始启动插件的用户,
count( distinct if(event_name="b_entry_page" and act_page="p_game_plugin", device_id, null)) 成功进入插件游戏的用户
from a LEFT join b on a.device_id = b.device_id and a.pt = b.ngx_date
GROUP by b.ngx_date;
游戏加载时长是否成功进入app
--odps sql
--********************************************************************--
--author:odps-game
--create time:2020-02-25 17:50:12
--********************************************************************--
--推人游戏加载时长&&是否成功进入app
with a as(
select device_id,pt from sync_mongo_box.extract_trmoney_app_source__userinfo
WHERE pt BETWEEN '${start}' and '${end}'
AND SUBSTR(create_time,1,10) = pt GROUP by device_id,pt -- 新增表
),
b as(
-- p_welcome p_splash
select * from (select duration,device_id,ngx_date
,row_number()OVER(PARTITION by device_id,ngx_date order by ngx_date_time asc,duration DESC ) rn
from liquid_log_project.log_trmoney_android
where ngx_date BETWEEN '${start}' and '${end}' and event_name ="b_leave_page"
and act_page = "p_welcome" ) a where rn=1-- 首次启动时长 duration -- 一个人有多条只取时间最小的那一条
), c as (
select device_id,ngx_date from liquid_log_project.log_trmoney_android
where ngx_date BETWEEN '${start}' and '${end}' and event_name ="b_entry_page"
and act_page = "p_home_activity" GROUP by device_id,ngx_date -- 成功进入app
)
SELECT a.pt,a.device_id,b.duration,if(c.device_id is not null ,1,0) isin -- 日期、device_id、启动时长、是否进入app
from a LEFT join b on a.device_id = b.device_id and a.pt = b.ngx_date
LEFT join c on a.device_id = c.device_id and a.pt = c.ngx_date;
LTV 模版
--odps sql
--********************************************************************--
--author:odps-game
--create time:2019-11-21 10:42:43
--********************************************************************--
with new as ( --新增
SELECT user_id
,pt
FROM basic_data_center.odps_base_box_user_day_stat
WHERE pt BETWEEN '${bdatestart}'
AND '${bdateend}'
AND day_age = 0
and version_name='5.4.9.0'
)
,stat as (--活跃加三天
SELECT user_id
,pt
,duration
FROM basic_data_center.odps_base_box_user_day_stat
WHERE pt BETWEEN '${bdatestart}'
AND SUBSTR(DATEADD(TO_DATE('${bdateend}','yyyy-mm-dd'),3,'dd'),1,10)
)
SELECT new.pt AS 日期
,CASE WHEN basic_data_center.getbucketid(new.user_id,18) BETWEEN 0 AND 49 THEN 'A'
WHEN basic_data_center.getbucketid(new.user_id,18) BETWEEN 50 AND 99 THEN 'B'
END AS 策略
,sum(if(DATEDIFF(to_date(stat.pt,'yyyy-mm-dd'),to_date(new.pt,'yyyy-mm-dd'),'dd')=0,1,0 )) as 新增人数
,sum(if(DATEDIFF(to_date(stat.pt,'yyyy-mm-dd'),to_date(new.pt,'yyyy-mm-dd'),'dd')=0,duration,0 )) as 当天新增人数的总时长
,sum(if(DATEDIFF(to_date(stat.pt,'yyyy-mm-dd'),to_date(new.pt,'yyyy-mm-dd'),'dd')BETWEEN 0 and 1,duration,0 )) as `当天新增人数2日内时长(当天和第二天)`
,sum(if(DATEDIFF(to_date(stat.pt,'yyyy-mm-dd'),to_date(new.pt,'yyyy-mm-dd'),'dd')=2,duration,0 )) as `当天新增人数的+2天总时长`
,sum(if(DATEDIFF(to_date(stat.pt,'yyyy-mm-dd'),to_date(new.pt,'yyyy-mm-dd'),'dd')=3,duration,0 )) as `当天新增人数的+3天总时长`
FROM new
,stat
WHERE new.user_id = stat.user_id
GROUP BY new.pt
,CASE WHEN basic_data_center.getbucketid(new.user_id,18) BETWEEN 0 AND 49 THEN 'A'
WHEN basic_data_center.getbucketid(new.user_id,18) BETWEEN 50 AND 99 THEN 'B'
END
;
宝箱新增LTV
with
data0 as(
select pt,device_id
FROM basic_data_center.extract_ttgame_extra_app_source__userdaystat
WHERE pt between '${date_start}' AND '${date_end}' and day_age = 0
),
data1 as(
select pt,device_id , SUM(COALESCE(reward_coin, 0)) as total_reward,
SUM(COALESCE(total_video_count, 0)) as video_count
,SUM(COALESCE(cg_game_time, 0)) as game_time
FROM basic_data_center.extract_ttgame_extra_app_source__userdaystat
WHERE pt between '${date_start}' AND SUBSTR(DATEADD(TO_DATE('${date_end}','yyyy-mm-dd'),7,'dd'),1,10)
GROUP BY pt,device_id
),
data2 as (
SELECT device_id,sum(COALESCE(money, 0)) as money, pt
FROM basic_data_center.extract_ttgame_extra_app_source__extractcashrecord
WHERE pt between '${date_start}' AND SUBSTR(DATEADD(TO_DATE('${date_end}','yyyy-mm-dd'),7,'dd'),1,10) AND status = 3 GROUP by pt,device_id
),all_data as (
SELECT data1.pt, data1.device_id,total_reward,video_count
,game_time,money
from data1 LEFT join data2 on data1.device_id = data2.device_id and data1.pt = data2.pt
)
SELECT
data0.pt
,sum(if(DATEDIFF(to_date(all_data.pt,'yyyy-mm-dd'),to_date(data0.pt,'yyyy-mm-dd'),'dd')=0,1,0 )) as 新增总人数
,round(
sum(if(DATEDIFF(to_date(all_data.pt,'yyyy-mm-dd'),to_date(data0.pt,'yyyy-mm-dd'),'dd')=0,game_time,0 ))
/
sum(if(DATEDIFF(to_date(all_data.pt,'yyyy-mm-dd'),to_date(data0.pt,'yyyy-mm-dd'),'dd')=0,1,0 ))
/60
,3
)as 新增人均时长
,round(
sum(if(DATEDIFF(to_date(all_data.pt,'yyyy-mm-dd'),to_date(data0.pt,'yyyy-mm-dd'),'dd')=0,video_count,0 ))
/
sum(if(DATEDIFF(to_date(all_data.pt,'yyyy-mm-dd'),to_date(data0.pt,'yyyy-mm-dd'),'dd')=0,1,0 ))
,3
)as 新增人均视频数
,round(
sum(if(DATEDIFF(to_date(all_data.pt,'yyyy-mm-dd'),to_date(data0.pt,'yyyy-mm-dd'),'dd')=0,total_reward,0 ))
/
sum(if(DATEDIFF(to_date(all_data.pt,'yyyy-mm-dd'),to_date(data0.pt,'yyyy-mm-dd'),'dd')=0,1,0 ))
,3
)as 新增人均名义补贴
,round(
sum(if(DATEDIFF(to_date(all_data.pt,'yyyy-mm-dd'),to_date(data0.pt,'yyyy-mm-dd'),'dd')=0,money,0 ))
/
sum(if(DATEDIFF(to_date(all_data.pt,'yyyy-mm-dd'),to_date(data0.pt,'yyyy-mm-dd'),'dd')=0,1,0 ))
,3
)as 新增人均体现金额
,round(
sum(if(DATEDIFF(to_date(all_data.pt,'yyyy-mm-dd'),to_date(data0.pt,'yyyy-mm-dd'),'dd') BETWEEN 0 and 2 ,game_time,0 ))
/
sum(if(DATEDIFF(to_date(all_data.pt,'yyyy-mm-dd'),to_date(data0.pt,'yyyy-mm-dd'),'dd')=0,1,0 ))
/60
,3
)as 3日人均时长
,round(
sum(if(DATEDIFF(to_date(all_data.pt,'yyyy-mm-dd'),to_date(data0.pt,'yyyy-mm-dd'),'dd') BETWEEN 0 and 2 ,video_count,0 ))
/
sum(if(DATEDIFF(to_date(all_data.pt,'yyyy-mm-dd'),to_date(data0.pt,'yyyy-mm-dd'),'dd')=0,1,0 ))
,3
)as 3日人均视频数
,round(
sum(if(DATEDIFF(to_date(all_data.pt,'yyyy-mm-dd'),to_date(data0.pt,'yyyy-mm-dd'),'dd') BETWEEN 0 and 2 ,total_reward,0 ))
/
sum(if(DATEDIFF(to_date(all_data.pt,'yyyy-mm-dd'),to_date(data0.pt,'yyyy-mm-dd'),'dd')=0,1,0 ))
,3
)as 3日人均名义补贴
,round(
sum(if(DATEDIFF(to_date(all_data.pt,'yyyy-mm-dd'),to_date(data0.pt,'yyyy-mm-dd'),'dd') BETWEEN 0 and 2 ,money,0 ))
/
sum(if(DATEDIFF(to_date(all_data.pt,'yyyy-mm-dd'),to_date(data0.pt,'yyyy-mm-dd'),'dd')=0,1,0 ))
,3
)as 3日人均提现金额
FROM data0
,all_data
WHERE data0.device_id = all_data.device_id
GROUP BY data0.pt;
页面渗透统计
--odps sql
--********************************************************************--
--author:odps-game
--create time:2019-11-14 12:08:37
--********************************************************************--
with
a as (
SELECT user_id AS day_active_person ,ngx_date AS pt
FROM liquid_log_project.log_box_android_master
WHERE ngx_date = '${date}'
AND event_name = "h_enter_page" and act_page = "p_index" GROUP BY ngx_date,user_id
),sign_num as (
SELECT user_id AS sign_num_user_id,ngx_date AS pt
FROM liquid_log_project.log_box_android_master
WHERE ngx_date = '${date}'
AND event_name = "h_enter_page" and act_page = "p_signIn" GROUP BY ngx_date,user_id
)
,task_list as (
SELECT user_id AS task_user_id,ngx_date AS pt
FROM liquid_log_project.log_box_android_master
WHERE ngx_date = '${date}'
AND event_name = "h_enter_page" and act_page = "p_taskList" GROUP BY ngx_date,user_id
)
,activity_list as (
SELECT user_id AS activity_user_id,ngx_date AS pt
FROM liquid_log_project.log_box_android_master
WHERE ngx_date = '${date}'
AND event_name = "h_enter_page" and act_page = "p_activityList" GROUP BY ngx_date,user_id
)
,p_lottery as (
SELECT user_id AS p_lottery_user_id,ngx_date AS pt
FROM liquid_log_project.log_box_android_master
WHERE ngx_date = '${date}'
AND event_name = "h_enter_page" and act_page = "p_lottery" GROUP BY ngx_date,user_id
),p_carveup as (
SELECT user_id AS p_carveup_user_id,ngx_date AS pt
FROM liquid_log_project.log_box_android_master
WHERE ngx_date = '${date}'
AND event_name = "h_enter_page" and act_page = "p_carveUp" GROUP BY ngx_date,user_id
),mingame_chooseroom as (
SELECT user_id AS chooseroom_user_id,ngx_date AS pt
FROM liquid_log_project.log_box_android_master
WHERE ngx_date = '${date}'
AND event_name = "h_enter_page" and act_page = "p_chooseRoom" GROUP BY ngx_date,user_id
),mingame_matchgame as (
SELECT user_id AS p_matchgame_user_id,ngx_date AS pt
FROM liquid_log_project.log_box_android_master
WHERE ngx_date = '${date}'
AND event_name = "h_enter_page" and act_page = "p_matchGame" GROUP BY ngx_date,user_id
),mingame_p_game_list as (
SELECT user_id AS p_game_list_user_id,ngx_date AS pt
FROM liquid_log_project.log_box_android_master
WHERE ngx_date = '${date}'
AND event_name = "h_enter_page" and act_page = "p_gameList" GROUP BY ngx_date,user_id
)
select a.pt as date,count(DISTINCT day_active_person) as all_person, --日活
count(DISTINCT sign_num_user_id) as sign_person_num, --打卡人数
count(DISTINCT task_user_id) as task_person_num,--任务赚币人数
count(DISTINCT activity_user_id) as activity_person_num, --活动送币人数
count(DISTINCT p_lottery_user_id) as activity_person_jq_num, --活动送币奖券人数
count(DISTINCT p_carveup_user_id) as carveup_person_num, --瓜分百万人数
count(DISTINCT chooseroom_user_id) as mingame_chooseroom, --小游戏选择房间人数
count(DISTINCT p_matchgame_user_id) as mingame_matchgame, --小游戏匹配人数
count(DISTINCT p_game_list_user_id) as mingame__game_list --小游戏游戏列表人数
from a LEFT join sign_num on a.day_active_person = sign_num.sign_num_user_id and a.pt= sign_num.pt
LEFT join task_list on a.day_active_person = task_list.task_user_id and a.pt= task_list.pt
LEFT join activity_list on a.day_active_person = activity_list.activity_user_id and a.pt= activity_list.pt
LEFT join p_lottery on a.day_active_person = p_lottery.p_lottery_user_id and a.pt= p_lottery.pt
LEFT join p_carveup on a.day_active_person = p_carveup.p_carveup_user_id and a.pt= p_carveup.pt
LEFT join mingame_chooseroom on a.day_active_person = mingame_chooseroom.chooseroom_user_id and a.pt= mingame_chooseroom.pt
LEFT join mingame_matchgame on a.day_active_person = mingame_matchgame.p_matchgame_user_id and a.pt= mingame_matchgame.pt
LEFT join mingame_p_game_list on a.day_active_person = mingame_p_game_list.p_game_list_user_id and a.pt= mingame_p_game_list.pt
GROUP by a.pt;
各项时长统计
--odps sql
--********************************************************************--
--author:zhaoyingjie
--create time:2019-11-01 10:39:36
--********************************************************************--
with games as ( --人均时长
select COUNT(a.user_id) AS new_add_nums,sum(game_times) as all_game_times, a.pt
FROM (
SELECT pt,_id as user_id
from sync_mongo_box.extract_box_gamezone_source__userdetails
WHERE pt='${date}' and substr(create_time, 1, 10)='${date}'
GROUP BY _id,pt
) as a
LEFT JOIN
(
SELECT pt,user_id
,SUM(
COALESCE(game_duration,0) --小游戏时长
+ COALESCE(huangdi_time,0) --皇帝时长
+ COALESCE(fishing_time,0) -- 捕鱼时长
+ COALESCE(ddz_times,0) --斗地主时长
+ COALESCE(game_activity_cg_time,0) -- 闯关时长
+ COALESCE(majiang_times,0) -- 麻将时长
) AS game_times
FROM sync_mongo_box.extract_box_gamezone_game_source__gamedaystat
WHERE pt = '${date}' GROUP BY pt,user_id
)as b
on a.user_id = b.user_id and a.pt = b.pt
GROUP BY a.pt
)
,pingtai as ( --人均平台时长
select COUNT(a.user_id) AS new_add_nums,sum(total_playtime) as all_pingtai_times, a.pt
FROM (
SELECT pt,_id as user_id
from sync_mongo_box.extract_box_gamezone_source__userdetails
WHERE pt='${date}' and substr(create_time, 1, 10)='${date}'
GROUP BY _id,pt
) as a
LEFT JOIN
(
SELECT user_id,ngx_date,round((SUM(duration)/1000), 2) as total_playtime
FROM liquid_log_project.log_box_android_master
WHERE ngx_date = '${date}'
AND event_name='b_leave_page'
AND SUBSTR(target_url,1,56) = 'https://saiyan_game_center.liquidnetwork.com/game_center'
AND duration < 3600000 and duration > 0
and user_id in (
SELECT user_id FROM sync_mongo_box.extract_box_gamezone_source__userdaystat
WHERE pt= '${date}'
)GROUP by user_id,ngx_date
)as b
on a.user_id = b.user_id and a.pt = b.ngx_date
GROUP BY a.pt
),
game_times as (
select a.pt,
COUNT(if(ddz_times is null,null,a.user_id)) AS ddz_nums,--斗地主人数
COUNT(if(majiang_times is null,null,a.user_id)) AS mj_nums, --麻将人数
COUNT(if(game_duration ==0,null,a.user_id)) AS mingame_nums, --小游戏人数
COUNT(if(huangdi_time is null,null,a.user_id)) AS huangdi_time_nums, --皇帝人数
COUNT(if(fishing_time is null,null,a.user_id)) AS fishing_time_nums, --捕鱼人数
COUNT(if(game_activity_cg_time is null,null,a.user_id)) AS game_activity_cg_nums, --闯关人数
round(sum(if(ddz_times is null,0,ddz_times)) /COUNT(if(ddz_times is null,null,a.user_id))/ 60,2) as avge_ddz_times, --斗地主人均时长
round(sum(if(majiang_times is null,0,majiang_times)) /COUNT(if(majiang_times is null,null,a.user_id))/ 60,2) as avge_majiang_times, --麻将人均时长
round(sum(if(game_duration==0,0,game_duration)) /COUNT(if(game_duration==0,null,a.user_id))/ 60,2) as avge_mingame_times, --小游戏人均时长
round(sum(if(huangdi_time is null,0,huangdi_time)) /COUNT(if(huangdi_time is null,null,a.user_id))/ 60,2) as avge_huangdi_times, --皇帝人均时长
round(sum(if(fishing_time is null,0,fishing_time)) /COUNT(if(fishing_time is null,null,a.user_id))/ 60,2) as avge_fishing_times, --捕鱼人均时长
round(sum(if(game_activity_cg_time is null,0,game_activity_cg_time)) /COUNT(if(game_activity_cg_time is null,null,a.user_id))/ 60,2) as avge_game_activity_times --捕鱼人均时长
FROM (
SELECT pt,_id as user_id
from sync_mongo_box.extract_box_gamezone_source__userdetails
WHERE pt='${date}' and substr(create_time, 1, 10)='${date}'
) as a
LEFT JOIN
(
SELECT pt,user_id, ddz_times ,fishing_time, majiang_times, game_duration, huangdi_time,game_activity_cg_time
FROM sync_mongo_box.extract_box_gamezone_game_source__gamedaystat
WHERE pt = '${date}'
)as b
on a.user_id = b.user_id
GROUP BY a.pt
)
SELECT games.pt,games.new_add_nums,ROUND((all_game_times+all_pingtai_times) / games.new_add_nums,2) as avge_times,ROUND(all_game_times/games.new_add_nums,2) as avge_game_times,
avge_ddz_times,avge_majiang_times,avge_fishing_times,avge_huangdi_times
,ROUND(mingame_nums/games.new_add_nums,3) as shentou_mingame ,ROUND(ddz_nums/games.new_add_nums,3) as shentou_ddz
,ROUND(mj_nums/games.new_add_nums,3) as shentou_mj,ROUND(fishing_time_nums/games.new_add_nums,3) as shentou_fishing
,ROUND(huangdi_time_nums/games.new_add_nums,3) as shentou_huangdi
,ROUND(game_activity_cg_nums/games.new_add_nums,3) as shentou_game_activity
from games,pingtai,game_times where games.pt = pingtai.pt and games.pt = game_times.pt
专区ROI
--odps sql
--********************************************************************--
--author:odps-game
--create time:2019-11-18 20:04:53
--********************************************************************--
with
a as ( -- 平台奖励
SELECT pt,user_id
,SUM(
COALESCE(punchcard_a_flash_coin,0)
+ COALESCE(patchcard_a_flash_coin,0)
+ COALESCE(dayactivity_a_flash_coin,0)
+ COALESCE(newperson_a_flash_coin,0)
+ COALESCE(dailytask_a_flash_coin,0)
+ COALESCE(gu_a_flash_coin,0) -- 瓜分奖励
+ COALESCE(guc_a_flash_coin,0) -- 瓜分安慰奖励
+ COALESCE(p_a_flash_coin,0) -- 进步奖励
+ COALESCE(pc_a_flash_coin,0) -- 进步安慰奖励
+ COALESCE(agc_a_flash_coin,0) -- 奖券奖励
+ COALESCE(88173991_agc_a_flash_coin,0) --消消乐
+ COALESCE(88107542_agc_a_flash_coin,0) --摩登大楼
+ COALESCE(88190422_agc_a_flash_coin,0) --爆爆球球
+ COALESCE(88111422_agc_a_flash_coin,0) --球别掉
+ COALESCE(88165662_agc_a_flash_coin,0) --守护飞飞
+ COALESCE(cg_a_flash_coin,0) -- 闯关奖励奖励
+ COALESCE(giftpackage_0_a_flash_coin, 0)
+ COALESCE(giftpackage_1_a_flash_coin, 0)
+ COALESCE(giftpackage_2_a_flash_coin, 0)
+ COALESCE(giftpackage_3_a_flash_coin, 0)
+ COALESCE(giftpackage_4_a_flash_coin, 0)
+ COALESCE(reward_coin,0)
+ COALESCE(finish_box, 0)*2500
) AS userdaystat_fishing,
SUM(COALESCE(video_count,0)) as patch_card_video
FROM sync_mongo_box.extract_box_gamezone_source__userdaystat
WHERE pt BETWEEN '${t_start}' and '${t_end}'
and client_version >= '5.4.7.0' and basic_data_center.getbucketid(user_id, 5) BETWEEN '${t_num}' and '${e_num}' GROUP BY user_id,pt
)
,b as ( --游戏内奖励、回收、视频数、时长
SELECT pt,user_id,SUM(
COALESCE(huangdi_task,0)
+ COALESCE(fishing_game_obtain_flash,0)
+ COALESCE(reward_coin,0)
+ COALESCE(a_ddz,0) -- 斗地主奖励
+ COALESCE(mj_task_flash_coin, 0) -- 麻将游戏内奖励
) AS gameuserdaystat_fishing
,SUM(
+ ABS(COALESCE(fishing_game_consum_flash,0)) --- 捕鱼回收门票
+ ABS(COALESCE(minigame_need_coin,0)) --- 小游戏门票
+ ABS(COALESCE(r_ddz,0)) -- 斗地主回收
) AS consum_flash
,SUM(
COALESCE(if (game_duration < 0,0,game_duration),0)
+ COALESCE(if (huangdi_time < 0,0,huangdi_time),0)
+ COALESCE(if (fishing_time < 0,0,fishing_time),0)
+ COALESCE(if (ddz_times < 0,0,ddz_times),0) --斗地主时长
+ COALESCE(if (game_activity_cg_time < 0,0,game_activity_cg_time),0) -- 闯关时长
+ COALESCE(if (majiang_times < 0,0,majiang_times),0) -- 麻将时长
) AS game_times
,SUM(
COALESCE(total_video_count,0)
+ COALESCE(fishing_watch_video_times,0) -- 捕鱼看视频数
+ COALESCE(ddz_watch_video,0) -- 斗地主看视频数
+ COALESCE(majiang_watch_video, 0) -- 麻将游戏内视频数
+ COALESCE(mj_task_video, 0) -- 麻将任务视频
) AS game_video
FROM sync_mongo_box.extract_box_gamezone_game_source__gamedaystat
WHERE pt BETWEEN '${t_start}' and '${t_end}' GROUP BY pt,user_id
)
,c as( --皇帝视频数
SELECT user_id,ngx_date,COUNT(1) AS huangdi_video_count
FROM liquid_log_project.log_box_android_master
WHERE ngx_date BETWEEN '${t_start}' and '${t_end}'
AND event_name IN ('task_video_success','speed_video_success','box_video_success','offline_video_success')
AND GET_JSON_OBJECT(other_column,'$.game_id') = "42104418"
GROUP by user_id,ngx_date
),d as( -- 游戏平台所占时长
SELECT a.user_id,a.ngx_date,round((SUM(duration)/1000), 2) as total_playtime from
(
SELECT user_id
,duration
,ngx_date
FROM liquid_log_project.log_box_android_master
WHERE ngx_date BETWEEN '${t_start}' and '${t_end}'
AND event_name = 'b_leave_page'
AND SUBSTR(target_url,1,56) = 'https://saiyan_game_center.liquidnetwork.com/game_center'
AND duration < 3600000
AND duration > 0
)a LEFT SEMI JOIN (
SELECT user_id
,pt
FROM sync_mongo_box.extract_box_gamezone_source__userdaystat
WHERE pt BETWEEN '${t_start}' and '${t_end}'
GROUP BY pt,user_id
) b
on a.user_id=b.user_id
and a.ngx_date=b.pt
GROUP BY a.ngx_date,a.user_id
),f as ( --转圈奖励
SELECT user_id,pt,SUM(COALESCE(red_envelopes_game_module_flash,0)) as red_game_flash, --游戏专区转圈
SUM(COALESCE(red_envelopes_doudizhu_flash,0)) as doudizhu_flash, -- 首页斗地主转圈
SUM(COALESCE(third_order_flash_saiyisai,0)) red_zq, --红包新游戏中心 转圈
SUM(COALESCE(games_reward_flash,0)) games_reward_flash, --游戏激励视频奖励
SUM(COALESCE(games_read_count_reward,0)) games_read_count_reward, --游戏转圈阶梯奖励
SUM(COALESCE(size(games_red_reward_y_list),0)) games_red_video_count --游戏转圈视频数
from sync_mongo_box.extract_source__userdaystat
where pt BETWEEN '${t_start}' and '${t_end}'
GROUP BY user_id,pt
)
select '${t_num}' "--" '${e_num}' as 桶号,
a.pt,COUNT(a.user_id) AS 日活, --日活
SUM(red_game_flash) as 转圈奖励, --转圈奖励
ROUND((SUM(red_game_flash + doudizhu_flash + games_reward_flash + games_read_count_reward)) / COUNT(a.user_id)) as 人均转圈补贴, --人均转圈补贴
SUM(userdaystat_fishing+gameuserdaystat_fishing + red_game_flash + doudizhu_flash +red_zq + games_reward_flash + games_read_count_reward) AS 补贴闪电币, --补贴闪电币
ROUND(
SUM(userdaystat_fishing+gameuserdaystat_fishing + red_game_flash + doudizhu_flash +red_zq + games_reward_flash +games_read_count_reward - consum_flash) / COUNT(a.user_id)
) as 人均补贴, --人均补贴
SUM(consum_flash) AS 回收闪电币, --回收闪电币
SUM(game_times) +SUM(total_playtime) AS 总时长, --总时长
ROUND(
(
(SUM(game_times) + SUM(total_playtime)) / COUNT(a.user_id) / 60
)
,2) as 人均总时长, --人均总时长
SUM(game_times) AS 游戏时长, --游戏时长
ROUND(
(SUM(game_times) / COUNT(a.user_id)) / 60
,2) as 人均游戏时长, --人均游戏时长
SUM(game_video) + sum(huangdi_video_count) +sum(patch_card_video) + sum(games_red_video_count) AS 视频数, --视频数
ROUND(
(SUM(game_video) + sum(huangdi_video_count) +sum(patch_card_video) + sum(games_red_video_count)) / COUNT(a.user_id)
,2) as 人均视频数 --人均视频数
from a LEFT join b on a.user_id = b.user_id and a.pt = b.pt
LEFT join c on a.user_id = c.user_id and a.pt = c.ngx_date
LEFT JOIN d on a.user_id = d.user_id and a.pt = d.ngx_date
LEFT join f on a.user_id = f.user_id and a.pt = f.pt
GROUP by a.pt
专区新手任务提现
with user as (
SELECT user_id,pt FROM (
SELECT * from basic_data_center.odps_base_box_user_day_stat
WHERE pt BETWEEN '${bdatestart}' and '${bdateend}'
and version_name >='5.4.4.5'
-- and day_age = 0 --判断新老用户
and day_age != 0 --老用户
)a join (
SELECT pt,user_id FROM sync_mongo_box.extract_box_gamezone_source__userdaystat where day_age=0
and pt BETWEEN '${bdatestart}' and '${bdateend}'
)b on a.user_id = b.user_id and a.pt=b.pt GROUP by user_id,pt
)
,duration as ( --full join 保证全部用户
SELECT COALESCE(a.user_id,b.user_id) AS user_id
,COALESCE(duration,0)+COALESCE(total_playtime,0) AS allduration --两部分的时长
,COALESCE(a.pt,b.pt) AS pt
FROM ( --第一部分的时长
SELECT user_id
,COALESCE(game_duration,0)+COALESCE(huangdi_time,0)+COALESCE(fishing_time,0)+COALESCE(ddz_times,0)+COALESCE(game_activity_cg_time,0) AS duration
,pt
FROM sync_mongo_box.extract_box_gamezone_game_source__gamedaystat
WHERE pt BETWEEN '${bdatestart}'
AND split_part(DATEADD(CONCAT('${bdateend}',' 00:00:00'), 6,'dd'),' ',1)
) a FULL
JOIN ( --游戏中心的时长
SELECT user_id
,ngx_date AS pt
,SUM(duration)/1000 AS total_playtime
FROM liquid_log_project.log_box_android_master
WHERE ngx_date BETWEEN '${bdatestart}' and split_part(DATEADD(CONCAT('${bdateend}',' 00:00:00'), 6,'dd'),' ',1)
AND event_name = 'b_leave_page'
AND SUBSTR(target_url,1,56) = 'https://saiyan_game_center.liquidnetwork.com/game_center'
AND duration < 3600000
AND duration > 0
AND user_id IN ( SELECT user_id FROM sync_mongo_box.extract_box_gamezone_source__userdaystat WHERE pt BETWEEN '${bdatestart}' AND '${bdateend}' )
GROUP BY user_id
,ngx_date
) b
ON a.user_id = b.user_id
AND a.pt = b.pt
)
,videos as ( --full join 保证全部用户
SELECT COALESCE(d.user_id,c.user_id) AS user_id
,COALESCE(videos,0) +COALESCE(games_red_video_count,0) AS allvideos --三者相加的视频数
,COALESCE(d.pt,c.pt) AS pt
FROM (
SELECT COALESCE(a.user_id,b.user_id) AS user_id
,COALESCE(videos,0) +COALESCE(huangdi_video_count,0) AS videos --皇帝+其他的视频数
,COALESCE(a.pt,b.pt) AS pt
FROM ( --皇帝视频数
SELECT user_id
,ngx_date AS pt
,COUNT(1) AS huangdi_video_count
FROM liquid_log_project.log_box_android_master
WHERE ngx_date BETWEEN '${bdatestart}'
AND split_part(DATEADD(CONCAT('${bdateend}',' 00:00:00'), 1,'dd'),' ',1)
AND event_name IN ('task_video_success','speed_video_success','box_video_success','offline_video_success')
AND GET_JSON_OBJECT(other_column,'$.game_id') = "42104418"
GROUP BY user_id
,ngx_date
) a FULL
JOIN ( --其他的视频数
SELECT user_id
,COALESCE(total_video_count,0) +COALESCE(fishing_watch_video_times,0) +COALESCE(ddz_watch_video,0) +COALESCE(majiang_watch_video,0 ) +COALESCE(mj_task_video,0) AS videos
,pt
FROM sync_mongo_box.extract_box_gamezone_game_source__gamedaystat
WHERE pt BETWEEN '${bdatestart}'
AND split_part(DATEADD(CONCAT('${bdateend}',' 00:00:00'), 6,'dd'),' ',1)
) b
ON a.user_id = b.user_id
AND a.pt = b.pt
) d FULL
JOIN ( --补贴表里的视频数
SELECT user_id
,SUM(COALESCE(SIZE(games_red_reward_y_list),0)) games_red_video_count --游戏转圈视频数
,pt
FROM sync_mongo_box.extract_source__userdaystat
WHERE pt BETWEEN '${bdatestart}'
AND split_part(DATEADD(CONCAT('${bdateend}',' 00:00:00'), 6,'dd'),' ',1)
group by user_id,pt
) c
ON d.user_id = c.user_id
AND d.pt = c.pt
)
,subsidy as (
SELECT COALESCE(a.user_id,b.user_id) AS user_id
,COALESCE(a.subsidy,0)+COALESCE(b.subsidy,0) AS allsubsidy --两者的补贴
,COALESCE(a.pt,b.pt) AS pt
FROM ( --第一部分的补贴
SELECT user_id
,SUM(red_envelopes_game_module_flash)+SUM(red_envelopes_doudizhu_flash)+SUM(third_order_flash_saiyisai)+SUM(games_reward_flash)+SUM(games_read_count_reward)
as subsidy
,pt
FROM sync_mongo_box.extract_source__userdaystat
WHERE pt BETWEEN '${bdatestart}'
AND split_part(DATEADD(CONCAT('${bdateend}',' 00:00:00'), 6,'dd'),' ',1)
group by user_id,pt
) a FULL
JOIN ( --第二部分的补贴
SELECT COALESCE(a.user_id,b.user_id) AS user_id
,COALESCE(a.subsidy,0) +COALESCE(b.subsidy,0) AS subsidy --皇帝+其他的视频数
,COALESCE(a.pt,b.pt) AS pt
FROM (
SELECT user_id
,pt
,COALESCE(huangdi_task,0)+COALESCE(fishing_rank_flash,0)+COALESCE(fishing_game_obtain_flash,0)+COALESCE(reward_coin,0)
+COALESCE(a_ddz,0) +COALESCE(mj_task_flash_coin,0)-COALESCE(fishing_game_consum_flash,0)-COALESCE(minigame_need_coin,0)
-COALESCE(r_ddz,0) as subsidy
FROM sync_mongo_box.extract_box_gamezone_game_source__gamedaystat
WHERE pt BETWEEN '${bdatestart}'
AND SPLIT_PART(DATEADD(CONCAT('${bdateend}',' 00:00:00'), 6,'dd'),' ',1)
) a FULL
JOIN ( --其他的补贴
SELECT user_id
,pt
,COALESCE(punchcard_a_flash_coin,0)+COALESCE(patchcard_a_flash_coin,0)+COALESCE(dayactivity_a_flash_coin,0)+COALESCE(newperson_a_flash_coin,0)
+COALESCE(dailytask_a_flash_coin,0) +COALESCE(gu_a_flash_coin,0)+COALESCE(guc_a_flash_coin,0)+COALESCE(p_a_flash_coin,0)
+COALESCE(pc_a_flash_coin,0) +COALESCE(agc_a_flash_coin,0) +COALESCE(88173991_agc_a_flash_coin,0)+COALESCE(88107542_agc_a_flash_coin,0)+COALESCE(88190422_agc_a_flash_coin,0)
+COALESCE(88111422_agc_a_flash_coin,0) +COALESCE(88165662_agc_a_flash_coin,0) +COALESCE(cg_a_flash_coin,0)+COALESCE(giftpackage_0_a_flash_coin,0)+COALESCE(giftpackage_1_a_flash_coin,0)
+COALESCE(giftpackage_2_a_flash_coin,0) +COALESCE(giftpackage_3_a_flash_coin,0) +COALESCE(giftpackage_4_a_flash_coin,0)+COALESCE(reward_coin,0)+COALESCE(finish_box,0)*2500
as subsidy
FROM sync_mongo_box.extract_box_gamezone_source__userdaystat
WHERE pt BETWEEN '${bdatestart}'
AND SPLIT_PART(DATEADD(CONCAT('${bdateend}',' 00:00:00'), 6,'dd'),' ',1)
) b
ON a.user_id = b.user_id
AND a.pt = b.pt
) b
ON a.user_id = b.user_id
AND a.pt = b.pt
)
,last_duration as (
SELECT user.pt
,CASE WHEN basic_data_center.getbucketid(user.user_id, 11) BETWEEN 0 AND 84 THEN 'A'
WHEN basic_data_center.getbucketid(user.user_id, 11) BETWEEN 85 AND 89 THEN 'B'
WHEN basic_data_center.getbucketid(user.user_id, 11) BETWEEN 90 AND 94 THEN 'C'
WHEN basic_data_center.getbucketid(user.user_id, 11) BETWEEN 95 AND 99 THEN 'D'
ELSE 'E'
END AS bucket_type
, sum(if(DATEDIFF(to_date(duration.pt,'yyyy-mm-dd'),TO_DATE(user.pt,'yyyy-mm-dd'),'dd' )=0,1,0)) as newuser --新增人数
, sum(if(DATEDIFF(to_date(duration.pt,'yyyy-mm-dd'),TO_DATE(user.pt,'yyyy-mm-dd'),'dd' )=0,allduration,0)) as duration0 --当日总时长
, sum(if(DATEDIFF(to_date(duration.pt,'yyyy-mm-dd'),TO_DATE(user.pt,'yyyy-mm-dd'),'dd' )BETWEEN 0 and 2,allduration,0)) as duration3 --三日内时长
, sum(if(DATEDIFF(to_date(duration.pt,'yyyy-mm-dd'),TO_DATE(user.pt,'yyyy-mm-dd'),'dd' )BETWEEN 0 and 6,allduration,0)) as duration7 --七日内时长
FROM user LEFT
JOIN duration
ON user.user_id = duration.user_id
GROUP BY user.pt
,CASE WHEN basic_data_center.getbucketid(user.user_id, 11) BETWEEN 0 AND 84 THEN 'A'
WHEN basic_data_center.getbucketid(user.user_id, 11) BETWEEN 85 AND 89 THEN 'B'
WHEN basic_data_center.getbucketid(user.user_id, 11) BETWEEN 90 AND 94 THEN 'C'
WHEN basic_data_center.getbucketid(user.user_id, 11) BETWEEN 95 AND 99 THEN 'D'
ELSE 'E'
END
)
,last_videos as (
SELECT user.pt
,CASE WHEN basic_data_center.getbucketid(user.user_id, 11) BETWEEN 0 AND 84 THEN 'A'
WHEN basic_data_center.getbucketid(user.user_id, 11) BETWEEN 85 AND 89 THEN 'B'
WHEN basic_data_center.getbucketid(user.user_id, 11) BETWEEN 90 AND 94 THEN 'C'
WHEN basic_data_center.getbucketid(user.user_id, 11) BETWEEN 95 AND 99 THEN 'D'
ELSE 'E'
END AS bucket_type
, sum(if(DATEDIFF(to_date(videos.pt,'yyyy-mm-dd'),TO_DATE(user.pt,'yyyy-mm-dd'),'dd' )=0,allvideos,0)) as videos0 --当日总时长
, sum(if(DATEDIFF(to_date(videos.pt,'yyyy-mm-dd'),TO_DATE(user.pt,'yyyy-mm-dd'),'dd' )BETWEEN 0 and 2,allvideos,0)) as videos3 --三日内时长
, sum(if(DATEDIFF(to_date(videos.pt,'yyyy-mm-dd'),TO_DATE(user.pt,'yyyy-mm-dd'),'dd' )BETWEEN 0 and 6,allvideos,0)) as videos7 --七日内时长
FROM user LEFT
JOIN videos
ON user.user_id = videos.user_id
GROUP BY user.pt
,CASE WHEN basic_data_center.getbucketid(user.user_id, 11) BETWEEN 0 AND 84 THEN 'A'
WHEN basic_data_center.getbucketid(user.user_id, 11) BETWEEN 85 AND 89 THEN 'B'
WHEN basic_data_center.getbucketid(user.user_id, 11) BETWEEN 90 AND 94 THEN 'C'
WHEN basic_data_center.getbucketid(user.user_id, 11) BETWEEN 95 AND 99 THEN 'D'
ELSE 'E'
END
)
,last_subsidy as (
SELECT user.pt
,CASE WHEN basic_data_center.getbucketid(user.user_id, 11) BETWEEN 0 AND 84 THEN 'A'
WHEN basic_data_center.getbucketid(user.user_id, 11) BETWEEN 85 AND 89 THEN 'B'
WHEN basic_data_center.getbucketid(user.user_id, 11) BETWEEN 90 AND 94 THEN 'C'
WHEN basic_data_center.getbucketid(user.user_id, 11) BETWEEN 95 AND 99 THEN 'D'
ELSE 'E'
END AS bucket_type
, sum(if(DATEDIFF(to_date(subsidy.pt,'yyyy-mm-dd'),TO_DATE(user.pt,'yyyy-mm-dd'),'dd' )=0,allsubsidy,0)) as subsidy0 --当日总时长
, sum(if(DATEDIFF(to_date(subsidy.pt,'yyyy-mm-dd'),TO_DATE(user.pt,'yyyy-mm-dd'),'dd' )BETWEEN 0 and 2,allsubsidy,0)) as subsidy3 --三日内时长
, sum(if(DATEDIFF(to_date(subsidy.pt,'yyyy-mm-dd'),TO_DATE(user.pt,'yyyy-mm-dd'),'dd' )BETWEEN 0 and 6,allsubsidy,0)) as subsidy7 --七日内时长
FROM user LEFT
JOIN subsidy
ON user.user_id = subsidy.user_id
GROUP BY user.pt
,CASE WHEN basic_data_center.getbucketid(user.user_id, 11) BETWEEN 0 AND 84 THEN 'A'
WHEN basic_data_center.getbucketid(user.user_id, 11) BETWEEN 85 AND 89 THEN 'B'
WHEN basic_data_center.getbucketid(user.user_id, 11) BETWEEN 90 AND 94 THEN 'C'
WHEN basic_data_center.getbucketid(user.user_id, 11) BETWEEN 95 AND 99 THEN 'D'
ELSE 'E'
END
)
SELECT
last_duration.pt as 日期
,last_duration.bucket_type as 分桶情况
,newuser as 新增人数
,ROUND(duration0/newuser,2)/60 as 当日人均时长分
, ROUND(subsidy0/newuser,2)/100000 as 当日人均补贴元
, ROUND(videos0/newuser,2) as 当日人均视频数
, ROUND(duration3/newuser,2)/60 as 三日人均时长分
, ROUND(subsidy3/newuser,2)/100000 as 三日人均补贴元
, ROUND(videos3/newuser,2) as 三日人均视频数
, ROUND(duration7/newuser,2)/60 as 七日人均时长分
, ROUND(subsidy7/newuser,2)/100000 as 七日人均补贴元
, ROUND(videos7 /newuser,2) as 七日人均视频数
from last_duration,last_videos,last_subsidy
WHERE last_duration.pt =last_videos.pt and last_duration.bucket_type =last_videos.bucket_type
and last_duration.pt =last_subsidy.pt and last_duration.bucket_type =last_subsidy.bucket_type;
活跃留存模版
--odps sql
--********************************************************************--
--author:odps-game
--create time:2019-11-21 16:22:46
--********************************************************************--
with user_t as(
select pt,user_id from workspace_saiyisai.odps_calc_user_roi
where pt between '${date_start}' AND '${date_end}'
),
userdaystat as(
select pt,user_id from workspace_saiyisai.odps_calc_user_roi
where pt between '${date_start}' AND SUBSTR(DATEADD(TO_DATE('${date_end}','yyyy-mm-dd'),14,'dd'),1,10)
)
select
CASE
-- 实验分桶修改 start
WHEN basic_data_center.getbucketid(user_t.user_id,'8') BETWEEN 0 AND 5 THEN "a"
WHEN basic_data_center.getbucketid(user_t.user_id,'8') BETWEEN 6 AND 10 THEN "b"
WHEN basic_data_center.getbucketid(user_t.user_id,'8') BETWEEN 6 AND 10 THEN "b"
WHEN basic_data_center.getbucketid(user_t.user_id,'8') BETWEEN 6 AND 10 THEN "b"
-- 实验分桶修改 end
END AS 策略,
user_t.pt as 日期,
count(distinct user_t.user_id) 活跃,
sum(
if (DATEDIFF(CONCAT(userdaystat.pt, ' 00:00:00'), CONCAT(user_t.pt, ' 00:00:00'), 'dd') = 1, 1, 0)
) 留存1,
sum(
if (DATEDIFF(CONCAT(userdaystat.pt, ' 00:00:00'), CONCAT(user_t.pt, ' 00:00:00'), 'dd') = 3, 1, 0)
) 留存3,
sum(
if (DATEDIFF(CONCAT(userdaystat.pt, ' 00:00:00'), CONCAT(user_t.pt, ' 00:00:00'), 'dd') = 7, 1, 0)
) 留存7,
sum(
if (DATEDIFF(CONCAT(userdaystat.pt, ' 00:00:00'), CONCAT(user_t.pt, ' 00:00:00'), 'dd') = 10, 1, 0)
) 留存10,
sum(
if (DATEDIFF(CONCAT(userdaystat.pt, ' 00:00:00'), CONCAT(user_t.pt, ' 00:00:00'), 'dd') = 14, 1, 0)
) 留存14,
ROUND(
sum(
if (DATEDIFF(CONCAT(userdaystat.pt, ' 00:00:00'), CONCAT(user_t.pt, ' 00:00:00'), 'dd') = 1, 1, 0)
) /
count(distinct user_t.user_id)
,3
) as 留存1率,
ROUND(
sum(
if (DATEDIFF(CONCAT(userdaystat.pt, ' 00:00:00'), CONCAT(user_t.pt, ' 00:00:00'), 'dd') = 3, 1, 0)
) /
count(distinct user_t.user_id)
,3
) as 留存3率,
ROUND(
sum(
if (DATEDIFF(CONCAT(userdaystat.pt, ' 00:00:00'), CONCAT(user_t.pt, ' 00:00:00'), 'dd') = 7, 1, 0)
) /
count(distinct user_t.user_id)
,3
) as 留存7率,
ROUND(
sum(
if (DATEDIFF(CONCAT(userdaystat.pt, ' 00:00:00'), CONCAT(user_t.pt, ' 00:00:00'), 'dd') = 10, 1, 0)
) /
count(distinct user_t.user_id)
,3
) as 留存10率,
ROUND(
sum(
if (DATEDIFF(CONCAT(userdaystat.pt, ' 00:00:00'), CONCAT(user_t.pt, ' 00:00:00'), 'dd') = 14, 1, 0)
) /
count(distinct user_t.user_id)
,3
) as 留存14率
from user_t LEFT JOIN userdaystat
on user_t.user_id = userdaystat.user_id
group by user_t.pt,
-- 实验分桶修改 start
CASE WHEN basic_data_center.getbucketid(user_t.user_id,'') BETWEEN 0 AND 99 THEN "20-99"
-- 实验分桶修改 end
END;
全量ROI模版
-- --odps sql
-- --********************************************************************--
-- --author:odps-game
-- --create time:2019-11-21 15:50:26
-- --********************************************************************--
select pt,
CASE
-- 实验分桶修改 start
WHEN basic_data_center.getbucketid(user_id,'${bucket_num}') BETWEEN 0 AND 9 THEN '跳一跳'
WHEN basic_data_center.getbucketid(user_id,'${bucket_num}') BETWEEN 10 AND 19 THEN '箭头'
WHEN basic_data_center.getbucketid(user_id,'${bucket_num}') BETWEEN 20 AND 99 THEN '对照组'
-- 实验分桶修改 end
END AS 策略,
count(1) as 日活,round(sum(video_count) / count(1),2) as 人均视频数,
round(sum(times) / count(1),3) as 人均时长,round(sum(max_flash_coin + red_coin) / count(1),3) as 人均补贴,
round(sum(income) / count(1),3) as 人均收入,ROUND(sum(possible_reward) / count(1),3) as 人均可提补贴
from workspace_saiyisai.odps_calc_user_roi where pt between "${sdate}"
and "${edate}"
GROUP by pt,CASE
-- 实验分桶修改 start
WHEN basic_data_center.getbucketid(user_id,'${bucket_num}') BETWEEN 0 AND 9 THEN '跳一跳'
WHEN basic_data_center.getbucketid(user_id,'${bucket_num}') BETWEEN 10 AND 19 THEN '箭头'
WHEN basic_data_center.getbucketid(user_id,'${bucket_num}') BETWEEN 20 AND 99 THEN '对照组'
-- 实验分桶修改 end
END;
实验LTV模版
--odps sql
--********************************************************************--
--author:odps-read
--create time:2019-11-25 14:08:06
--********************************************************************--
with
userdaystat as (
select pt,user_id from workspace_saiyisai.odps_calc_user_roi
where pt between '${date_start}' AND '${date_end}'
and box_day_age = 0 and client_version >= "5.4.4.5" and day_age = 0
),
odps_calc_user_roi as ( --roi user表
select * from workspace_saiyisai.odps_calc_user_roi
where pt between '${date_start}' AND SUBSTR(DATEADD(TO_DATE('${date_end}','yyyy-mm-dd'),7,'dd'),1,10)
)
SELECT userdaystat.pt AS 日期
,CASE
-- 实验分桶修改 start
WHEN basic_data_center.getbucketid(userdaystat.user_id,'${bucket_num}') BETWEEN 0 AND 84 THEN '对照'
WHEN basic_data_center.getbucketid(userdaystat.user_id,'${bucket_num}') BETWEEN 85 AND 89 THEN '改版'
WHEN basic_data_center.getbucketid(userdaystat.user_id,'${bucket_num}') BETWEEN 90 AND 94 THEN '提现'
WHEN basic_data_center.getbucketid(userdaystat.user_id,'${bucket_num}') BETWEEN 95 AND 99 THEN '改版+提现'
-- 实验分桶修改 end
END AS 策略
,sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd')=0,1,0 )) as 新增总人数
,round(
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd')=0,times,0 ))
/
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd')=0,1,0 ))
/60
,3
)as 新增人均时长
,round(
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd')=0,max_flash_coin+red_coin,0 ))
/
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd')=0,1,0 ))
,3
)
as 新增人均补贴
,round(
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd')=0,possible_reward,0 ))
/
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd')=0,1,0 ))
,3
)
as 新增人均可提补贴
,round(
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd')=0,income,0 ))
/
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd')=0,1,0 ))
,3
)
as 新增人均收入
,round(
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd')=0,video_count,0 ))
/
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd')=0,1,0 ))
,3
)
as 新增人均视频数
,round(
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd') BETWEEN 0 and 2 ,times,0 ))
/
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd')=0,1,0 ))
/60
,3
)as 3日人均时长
,round(
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd') BETWEEN 0 and 2 ,max_flash_coin+red_coin,0 ))
/
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd')=0,1,0 ))
,3
)
as 3日人均补贴
,round(
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd') BETWEEN 0 and 2 ,video_count,0 ))
/
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd')=0,1,0 ))
,3
)
as 3日人均视频数
,round(
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd') BETWEEN 0 and 2 ,possible_reward,0 ))
/
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd')=0,1,0 ))
,3
)
as 3日人均可提补贴
,round(
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd') BETWEEN 0 and 2,income,0 ))
/
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd')=0,1,0 ))
,3
)
as 3日人均收入
,round(
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd') BETWEEN 0 and 6 ,times,0 ))
/
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd')=0,1,0 ))
/60
,3
)as 7日人均时长
,round(
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd') BETWEEN 0 and 6 ,max_flash_coin+red_coin,0 ))
/
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd')=0,1,0 ))
,3
)
as 7日人均补贴
,round(
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd') BETWEEN 0 and 6 ,video_count,0 ))
/
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd')=0,1,0 ))
,3
)
as 7日人均视频数
,round(
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd') BETWEEN 0 and 6 ,possible_reward,0 ))
/
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd')=0,1,0 ))
,3
)
as 7日人均可提补贴
,round(
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd') BETWEEN 0 and 6,income,0 ))
/
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd')=0,1,0 ))
,3
)
as 7日人均收入
FROM userdaystat
,odps_calc_user_roi
WHERE userdaystat.user_id = odps_calc_user_roi.user_id
GROUP BY userdaystat.pt,
CASE
-- 实验分桶修改 start
WHEN basic_data_center.getbucketid(userdaystat.user_id,'${bucket_num}') BETWEEN 0 AND 84 THEN '对照'
WHEN basic_data_center.getbucketid(userdaystat.user_id,'${bucket_num}') BETWEEN 85 AND 89 THEN '改版'
WHEN basic_data_center.getbucketid(userdaystat.user_id,'${bucket_num}') BETWEEN 90 AND 94 THEN '提现'
WHEN basic_data_center.getbucketid(userdaystat.user_id,'${bucket_num}') BETWEEN 95 AND 99 THEN '改版+提现'
-- 实验分桶修改 end
END;
新增留存模版
--odps sql
--********************************************************************--
--author:odps-game
--create time:2019-11-21 16:22:46
--********************************************************************--
with user_t as(
select pt,user_id from workspace_saiyisai.odps_calc_user_roi
where pt between '${date_start}' AND '${date_end}' and day_age = 0
),
userdaystat as(
select pt,user_id from workspace_saiyisai.odps_calc_user_roi
where pt between '${date_start}' AND SUBSTR(DATEADD(TO_DATE('${date_end}','yyyy-mm-dd'),14,'dd'),1,10)
)
select
CASE
-- 实验分桶修改 start
WHEN basic_data_center.getbucketid(user_t.user_id,'') BETWEEN 0 AND 99 THEN "20-99"
-- 实验分桶修改 end
END AS 策略,
user_t.pt as 日期,
count(distinct user_t.user_id) 新增,
sum(
if (DATEDIFF(CONCAT(userdaystat.pt, ' 00:00:00'), CONCAT(user_t.pt, ' 00:00:00'), 'dd') = 1, 1, 0)
) 留存1,
sum(
if (DATEDIFF(CONCAT(userdaystat.pt, ' 00:00:00'), CONCAT(user_t.pt, ' 00:00:00'), 'dd') = 3, 1, 0)
) 留存3,
sum(
if (DATEDIFF(CONCAT(userdaystat.pt, ' 00:00:00'), CONCAT(user_t.pt, ' 00:00:00'), 'dd') = 7, 1, 0)
) 留存7,
sum(
if (DATEDIFF(CONCAT(userdaystat.pt, ' 00:00:00'), CONCAT(user_t.pt, ' 00:00:00'), 'dd') = 10, 1, 0)
) 留存10,
sum(
if (DATEDIFF(CONCAT(userdaystat.pt, ' 00:00:00'), CONCAT(user_t.pt, ' 00:00:00'), 'dd') = 14, 1, 0)
) 留存14,
ROUND(
sum(
if (DATEDIFF(CONCAT(userdaystat.pt, ' 00:00:00'), CONCAT(user_t.pt, ' 00:00:00'), 'dd') = 1, 1, 0)
) /
count(distinct user_t.user_id)
,3
) as 留存1率,
ROUND(
sum(
if (DATEDIFF(CONCAT(userdaystat.pt, ' 00:00:00'), CONCAT(user_t.pt, ' 00:00:00'), 'dd') = 3, 1, 0)
) /
count(distinct user_t.user_id
),3
) as 留存3率,
ROUND(
sum(
if (DATEDIFF(CONCAT(userdaystat.pt, ' 00:00:00'), CONCAT(user_t.pt, ' 00:00:00'), 'dd') = 7, 1, 0)
) /
count(distinct user_t.user_id)
,3
) as 留存7率,
ROUND(
sum(
if (DATEDIFF(CONCAT(userdaystat.pt, ' 00:00:00'), CONCAT(user_t.pt, ' 00:00:00'), 'dd') = 10, 1, 0)
) /
count(distinct user_t.user_id)
,3
) as 留存10率,
ROUND(
sum(
if (DATEDIFF(CONCAT(userdaystat.pt, ' 00:00:00'), CONCAT(user_t.pt, ' 00:00:00'), 'dd') = 14, 1, 0)
) /
count(distinct user_t.user_id)
,3
) as 留存14率
from user_t LEFT join userdaystat
on user_t.user_id = userdaystat.user_id
and userdaystat.pt in (
SUBSTR(DATEADD(TO_DATE(user_t.pt,'yyyy-mm-dd'),1,'dd'),1,10),
SUBSTR(DATEADD(TO_DATE(user_t.pt,'yyyy-mm-dd'),3,'dd'),1,10),
SUBSTR(DATEADD(TO_DATE(user_t.pt,'yyyy-mm-dd'),7,'dd'),1,10),
SUBSTR(DATEADD(TO_DATE(user_t.pt,'yyyy-mm-dd'),10,'dd'),1,10),
SUBSTR(DATEADD(TO_DATE(user_t.pt,'yyyy-mm-dd'),14,'dd'),1,10)
)
group by user_t.pt,
CASE
-- 实验分桶修改 start
WHEN basic_data_center.getbucketid(user_t.user_id,'') BETWEEN 0 AND 99 THEN "20-99"
-- 实验分桶修改 end
END;
新增ROI模版
-- --odps sql
-- --********************************************************************--
-- --author:odps-game
-- --create time:2019-11-21 14:47:44
-- --********************************************************************--
select pt, CASE
-- 实验分桶修改 start
WHEN basic_data_center.getbucketid(user_id,'${bucket_num}') BETWEEN 0 AND 9 THEN '跳一跳'
WHEN basic_data_center.getbucketid(user_id,'${bucket_num}') BETWEEN 10 AND 19 THEN '箭头'
WHEN basic_data_center.getbucketid(user_id,'${bucket_num}') BETWEEN 20 AND 99 THEN '对照组'
-- 实验分桶修改 end
END AS 策略,
count(1) as 日活,round(sum(video_count) / count(1),2) as 人均视频数,
round(sum(times) / count(1),3) as 人均时长,round(sum(max_flash_coin + red_coin) / count(1),3) as 人均补贴,
round(sum(income) / count(1),3) as 人均收入,ROUND(sum(possible_reward) / count(1),3) as 人均可提补贴
from workspace_saiyisai.odps_calc_user_roi where pt between "${sdate}"
and "${edate}" and day_age = 0
GROUP by pt,CASE
-- 实验分桶修改 start
WHEN basic_data_center.getbucketid(user_id,'${bucket_num}') BETWEEN 0 AND 9 THEN '跳一跳'
WHEN basic_data_center.getbucketid(user_id,'${bucket_num}') BETWEEN 10 AND 19 THEN '箭头'
WHEN basic_data_center.getbucketid(user_id,'${bucket_num}') BETWEEN 20 AND 99 THEN '对照组'
-- 实验分桶修改 end
END;
