用户行为分析模型实践（三）——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_newFROM(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) bWHERE 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;