用户行為分析模型實踐（三）——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;