页面回访指标开发（次日回访、7日回访）

回访数据组成部分：页面、用户、次数、次日是否访问、七日是否访问、日期

page_id	页面id
user_id	用户id
visit_cnt	访问次数
has_return_visit_2d	次日有回访
has_return_visit_7d	7日有回访
pt_date	日期

指标定义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 visit
SELECT 
    page_id, -- 页面
	user_id, -- 用户
	pt_date, -- 日期
	count(1) visit_cnt --次数
FROM
    tmp.user_page_log
WHERE 
    pt_date BETWEEN '${date_ymd}' AND date_add('${date_ymd}',6)
GROUP BY
    page_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_date
FROM 
    upv  a
LEFT JOIN -- 当日数据关联次日数据
    upv  b
ON DATE_SUB(b.pt_date, 1) = a.pt_date
    AND a.page_id = b.page_id
    AND a.user_id = b.user_id
LEFT JOIN
    upv  c
ON DATE_SUB(c.pt_date, 5) = b.pt_date -- 次日数据关联7日数据
    AND b.page_id = c.page_id
    AND b.user_id = c.user_id;

 

指标定义2：当日用户a 访问页面001，次日该用户又访问001页面，那么有次日回访行为；

次日到7日内该用户访问了001页面，那么称为7日回访（相当于7日回访包含次日回访）

WITH t1 as( -- page visit
SELECT page_id         page_id
       ,user_id        user_id
       ,pt_date        pt_date
       ,count(1)       visit_cnt
FROM dwd.dwd_fact_at_user_page_visit_inc_d
WHERE pt_date BETWEEN '${date_ymd}' AND date_add('${date_ymd}',6)
      AND user_id <> ''
      AND page_id IS NOT NULL
      AND 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_date                    pt_date
       ,user_id                    user_id
       ,page_id                    page_id
       ,visit_cnt                  visit_cnt
FROM 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_7d
FROM t2;