回访数据组成部分：页面、用户、次数、次日是否访问、七日是否访问、日期
指标定义1：当日用户a 访问页面001，次日该用户又访问001页面，那么有次日回访行为；
当日访问过001页面，次日访问过001页面，同时7日访问过001页面，那么7日有回访行为；
次日回访、7日回访、N日回访（N>7）,数据成递减趋势。
周期：最高回访7日为例，每次需要更新7天的数据
思路：拿当日 group by user_id ,page_id数据，关联次日group by user_id ,page_id数据，拿次日处理后数据，接着关联7日 group by user_id ,page_id 数据。
代码：
-- 开启动态分区set hive.exec.dynamic.partition=TRUE;set hive.exec.dynamic.partition.mode=nonstrict;-- 业务逻辑WITH upv as( -- user page visitSELECT page_id, -- 页面user_id, -- 用户pt_date, -- 日期count(1) visit_cnt --次数FROMtmp.user_page_logWHERE pt_date BETWEEN '${date_ymd}' AND date_add('${date_ymd}',6)GROUP BYpage_id,user_id,pt_date)SELECT a.page_id,a.user_id,a.visit_cnt,IF(b.visit_cnt IS NULL, 0, 1) has_visit_2d, -- 次日回访IF(c.visit_cnt IS NULL, 0, 1) has_visit_7d, -- 7日回访a.pt_dateFROM upv aLEFT JOIN -- 当日数据关联次日数据upv bON DATE_SUB(b.pt_date, 1) = a.pt_dateAND a.page_id = b.page_idAND a.user_id = b.user_idLEFT JOINupv cON DATE_SUB(c.pt_date, 5) = b.pt_date -- 次日数据关联7日数据AND b.page_id = c.page_idAND b.user_id = c.user_id;
指标定义2：当日用户a 访问页面001，次日该用户又访问001页面，那么有次日回访行为；
次日到7日内该用户访问了001页面，那么称为7日回访（相当于7日回访包含次日回访）
WITH t1 as( -- page visitSELECT page_id page_id,user_id user_id,pt_date pt_date,count(1) visit_cntFROM dwd.dwd_fact_at_user_page_visit_inc_dWHERE pt_date BETWEEN '${date_ymd}' AND date_add('${date_ymd}',6)AND user_id <> ''AND page_id IS NOT NULLAND page_id <> ''GROUP BY page_id,user_id,pt_date),t2 AS (SELECT LAG(pt_date,1)OVER(PARTITION BY user_id,page_id ORDER BY pt_date DESC) next_date,pt_datept_date,user_iduser_id,page_idpage_id,visit_cnt visit_cntFROM t1)SELECT user_id user_id,page_id page_id,visit_cnt visit_cnt,pt_date pt_date,next_date next_date,IF(DATEDIFF(next_date,pt_date) = 1,1,0) has_visit_2d,IF(next_date IS NULL,0,1)has_visit_7dFROM t2;
如果觉得《页面回访指标开发（次日回访 7日回访）》对你有帮助，请点赞、收藏，并留下你的观点哦！
