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;