用户行为分析模型实践（三）——H5通用分析模型

/**
 * 拼接通用化上报参数
 * @param {string} 重写路由事件类型
 */
function resetHistoryFun(type){
    // 将原先的方法复制出来
    let originMethod = window.history[type]
    // 当window.history[type]函数被执行时，这个return出来的函数就会被执行
    return function(){
        // 执行原先的方法
        let rs = originMethod.apply(this, arguments)
        // 然后自定义事件
        let e = new Event(type.toLocaleLowerCase())
        // 将原先函数的参数绑定到自定义的事件上去，原先的是没有的
        e.arguments = arguments
        // 然后用window.dispatchEvent()主动触发
        window.dispatchEvent(e)
        return rs;
    }
}
window.history.pushState = resetHistoryFun('pushState') // 覆盖原来的pushState方法
window.history.replaceState = resetHistoryFun('replaceState') // 覆盖原来的replaceState方法


window.addEventListener('pushstate', reportBothEvent)          
window.addEventListener('replacestate', reportBothEvent)

window.addEventListener('focus', ()=>{
    console.log('页面得到焦点')
});


window.addEventListener('blur', ()=>{
    console.log('页面失去焦点')
})

document.addEventListener('visibilitychange',  () => {
    if(document.hidden) {
        console.log('页面离开')
    } else {
        console.log('页面进入')
    }
})

## 明细层收口数据,统一id字段
SELECT  xx
       ,xx1
       ,CASE WHEN appid IN(1)       THEN  1   
             WHEN appid IN(2)   THEN  2  
             WHEN appid IN(3)       THEN  3    
             WHEN appid IN(4,5,6,...)       THEN  4      
        ELSE 0  END   AS  id_flag   
       ,CASE WHEN appid IN(1)      THEN  id1
             WHEN appid IN(2)  THEN  id2
             WHEN appid IN(3)      THEN  id3     
             WHEN appid IN(4,5,6,...)  THEN  IF(NVL(params['id1'],'')='',NVL(params['id2'],'NA'),params['id1'])            
       ELSE 'NA' END  AS  unique_id        
       ,appid        
  FROM ods_table_name_XXX  a     -- 各个接入业务线数据源 ods
 WHERE day='${today}'
   AND hour = '${etl_hour}'
   -- APPID 和 事件id 要匹配新增
   AND appid  in (1,2,3 ...)
   AND 事件id  in (XXX|167,XXX|168,...);


## id字段后续关联使用方式
## 增量关联全量，确定是否新用户
SELECT if(b.unique_id is null,1,0) AS is_new
FROM
(
SELECT *
  FROM table_XXX_hi
 WHERE day= '${today}'
   AND hour = '${etl_hour}'
 GROUP BY XX
       ) a
       -- 取全量表唯一 unique_id 作为关联条件，判断新老用户
       -- 新用户是相对于历史全量的
  LEFT JOIN ( SELECT unique_id,appid
                FROM      
               ( SELECT unique_id
                        ,appid
                        ,row_number() over(partition by unique_id,appid order by 活跃日期 asc)  as rn_0
                   FROM table_XXX_df
                  WHERE day='${etl_date}'
                ) a
                WHERE rn_0 = 1
             ) b
      ON a.unique_id = b.unique_id AND a.appid = b.appid;

## 利用bitmap思想，留存标签满8位转化为16进制组合到retain_tag之前，这样可以利用很少的位数记录较长的活跃情况
## 示例代码如下
SELECT user_unique_id
       ,if(length(tmp_retain_tag) = 8,is_active,concat(is_active,tmp_retain_tag))          as tmp_retain_tag
       --  如果tmp_retain_tag长度为8的时候，将数据转化为十六进制添加到retain_tag前，并将本字段清空，从头开始计数
       ,if(length(tmp_retain_tag) = 8,concat(con_tmp_retain_tag,retain_tag),retain_tag)    as retain_tag
       ,is_active
  FROM
(
    SELECT unique_id
            -- 前一天的临时存储，与con_tmp_retain_tag保持一致
           ,tmp_retain_tag                            
            -- 如果转换为十六进制后的长度不为2，则在左边添加0          
           ,if(length(conv(tmp_retain_tag,2,16)) = 2,conv(tmp_retain_tag,2,16),concat('0',conv(tmp_retain_tag,2,16))) as con_tmp_retain_tag 
            -- 历史轨迹
           ,retain_tag                                                                                      
           ,first_value(is_active) over(partition by unique_id,appid,topic_id  order by first_active_day desc)  as is_active
       FROM
      ( SELECT unique_id
               ,topic_id
               ,appid 
               ,first_active_day
               ,last_active_day
               -- 留存标签
               ,'0' as is_active
               ,tmp_retain_tag -- 形如 11101010
               ,retain_tag       -- 形如 A0E3
          FROM table_active_XX_df  -- 活跃全量表
         WHERE day= '${last_etl_date}'
         UNION ALL
        SELECT unique_id
               ,topic_id
               ,appid 
               ,day as first_active_day
               ,day as last_active_day
               -- 留存标签           
               ,'1' as is_active
               ,''  as tmp_retain_tag
               ,''  as retain_tag
          FROM table_active_XX_hi  -- 活跃明细表
         WHERE day= '${etl_date}'
         ) a
) b
WHERE rn =1;


## 留存指标统计：## 以3日内及第3日留存为例
WITH tmp_table AS (
SELECT DAY
       ,unique_id
       ,appid
       ,首次活跃日期
       ,CONCAT(tmp_retain_tag,retain_tag) AS login_trace    
   FROM (
        SELECT DAY
           ,unique_id
           ,tmp_retain_tag
               ,appid
               ,首次活跃日期
               ,IF(nvl(retain_tag,'') <> '',CONV(SUBSTR(retain_tag,1,8),16,2),'') AS retain_tag
              -- 如果retain_tag为空时，直接取空值。如果长度超过8位数，取最后八位数；如果长度不超过8位数，取全部。如果是30日内新用户，长度不超过8位
          FROM table_active_XX_df WHERE DAY = 统计日
        ) x1
)
## 以3日内及第3日留存为例：
SELECT -- 第N日留存指标：第N日来访
       ,SUM(IF(SUBSTR(login_trace,3,1) = '1',1,null))    AS retain_cnt_3th
       -- N日内留存指标:N日内访问过1次或N次
       ,SUM(IF(instr(SUBSTR(login_trace,2,2) ,'1')= 0,null,1))  AS retain_cnt_between_3th
 FROM (
       SELECT '统计日-2天' AS dt
              ,unique_id
              ,REVERSE(SUBSTR(login_trace,1,3)) AS login_trace
              ,appid
         FROM tmp_table WHERE SUBSTR(login_trace,3,1) = '1' AND 首次活跃日期 = 统计日-2天
) X GROUP BY dt,appid;