ID-MAP

開發(fā)用戶標(biāo)簽的時候，有項非常重要的內(nèi)容——ID-MApping，即把用戶不同來源的身份標(biāo)識通過數(shù)據(jù)手段識別為同一個主體。用戶的屬性、行為相關(guān)數(shù)據(jù)分散在不同的數(shù)據(jù)來源中，通過ID-MApping能夠 把用戶在不同場景下的行為串聯(lián)起來，消除數(shù)據(jù)孤島。圖3-7展示了用 戶與設(shè)備間的多對多關(guān)系。圖3-8展示了同一用戶在不同平臺間的行為 示意圖。

圖3-7 用戶和設(shè)備間的多對多關(guān)系

圖3-8 串聯(lián)同一個用戶在不同平臺間行為

舉例來說，用戶在未登錄App的狀態(tài)下，在App站內(nèi)訪問、搜索相關(guān)內(nèi)容時，記錄的是設(shè)備id（即cookieid）相關(guān)的行為數(shù)據(jù)。而用戶在登錄App后，訪問、收藏、下單等相關(guān)的行為記錄的是賬號id（即 userid）相關(guān)行為數(shù)據(jù)。雖然是同一個用戶，但其在登錄和未登錄設(shè)備時記錄的行為數(shù)據(jù)之間是未打通的。通過ID-MApping打通userid和 cookieid的對應(yīng)關(guān)系，可以在用戶登錄、未登錄設(shè)備時都能捕獲其行為軌跡。

下面通過一個案例介紹如何通過Hive的ETL工作完成ID-Mapping 的數(shù)據(jù)清洗工作。

緩慢變化維是在維表設(shè)計中常見的一種方式，維度并不是不變的，隨時間也會發(fā)生緩慢變化。如用戶的手機號、郵箱等信息可能會隨用戶的狀態(tài)變化而改變，再如商品的價格也會隨時間變化而調(diào)整上 架的價格。因此在設(shè)計用戶、商品等維表時會考慮用緩慢變化維來開發(fā)。同樣，在設(shè)計ID-Mapping表時，由于一個用戶可以在多個設(shè)備上 登錄，一個設(shè)備也能被多個用戶登錄，所以考慮用緩慢變化維表來記錄這種不同時間點的狀態(tài)變化（圖3-9）。

圖3-9 ID-Mapping拉鏈表

拉鏈表是針對緩慢變化維表的一種設(shè)計方式，記錄一個事物從開 始到當(dāng)前狀態(tài)的全部狀態(tài)變化信息。

在上圖中，通過拉鏈表記錄了userid每一次關(guān)聯(lián)到不同cookieid 的情況。如userid為44463729的用戶，在20190101這天登錄某設(shè) 備，在6號那天變換了另一個設(shè)備登錄。其中start_date表示該記錄的 開 始 日 期 ， end_date 表 示 該 記 錄 的 結(jié) 束 日 期 ， 當(dāng) end_date 為 99991231時，表示該條記錄當(dāng)前仍然有效。

首先需要從埋點表和訪問日志表里面獲取到cookieid和userid同 時出現(xiàn)的訪問記錄。下面案例中，ods.page_event_log是埋點日志 表 ， ods.page_view_log 是 訪 問 日 志 表 ， 將 獲 取 到 的 userid 和 cookieid 信 息 插 入 cookieid-userid 關(guān) 系 表 （ods.cookie_user_signin）中。代碼執(zhí)行如下：

INSERT OVERWRITE TABLE ods.cookie_user_signin PARTITION (data_date = '${data_date}')

SELECT t.* FROM ( SELECT userid, cookieid, from_unixtime(eventtime,'yyyyMMdd') as signdate

FROM ods.page_event_log -- 埋點表 WHERE data_date = '${data_date}'

UNION ALL SELECT userid, cookieid,

from_unixtime(viewtime,'yyyyMMdd') as signdate

FROM ods.page_view_log -- 訪問日志表

WHERE data_date = '${data_date}' ) t

創(chuàng)建ID-Map的拉鏈表，將每天新增到ods.cookie_user_signin 表中的數(shù)據(jù)與拉鏈表歷史數(shù)據(jù)做比較，如果有變化或新增數(shù)據(jù)則進(jìn)行更新。

CREATE TABLE `dw.cookie_user_zippertable`(

`userid` string COMMENT '賬號ID',

`cookieid` string COMMENT '設(shè)備ID',

`start_date` string COMMENT 'start_date',

`end_date` string COMMENT 'end_date')

COMMENT 'id-map拉鏈表'

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'

創(chuàng)建完成后，每天ETL調(diào)度將數(shù)據(jù)更新到ID-Mapping拉鏈表中， 任務(wù)執(zhí)行如下。

INSERT OVERWRITE TABLE dw.cookie_user_zippertable

SELECT t.*

FROM (

SELECT t1.user_num, t1.mobile, t1.reg_date, t1.start_date,

CASE WHEN t1.end_date = '99991231'

AND t2.userid IS NOT NULL THEN '${data_date}'

ELSE t1.end_date END AS end_date

FROM dw.cookie_user_zippertable t1

LEFT JOIN (SELECT * FROM ods.cookie_user_signin

WHERE data_date='${data_date}' )t2 ON t1.userid = t2.userid

UNION SELECT userid, cookieid, '${data_date}'

AS start_date, '99991231' AS end_date

FROM ods.cookie_user_signin

WHERE data_date = '${data_date}'

) t

數(shù)據(jù)寫入表中，如圖3-9所示。

對于該拉鏈表，可查看某日（如20190801）的快照數(shù)據(jù)。

select *

from dw.cookie_user_zippertable

where start_date<='20190801' and end_date>='20190801'

例如，目前存在一個記錄userid和cookieid關(guān)聯(lián)關(guān)系的表，但是 為多對多的記錄（即一個userid對應(yīng)多條cookieid記錄，以及一條 cookieid對應(yīng)多條userid記錄）。這里可以通過拉鏈表的日期來查看 某個時間點userid對應(yīng)的cookieid。查看某個用戶（如32101029） 在某天（如20190801）關(guān)聯(lián)到的設(shè)備id（圖3-10）。

select cookieid

from dw.cookie_user_zippertable

where userid='32101029'

and start_date<='20190801' and end_date>='20190801'

圖3-10 某用戶在拉鏈表中記錄

上圖可看出用戶‘32101029’在歷史中曾登錄過3個設(shè)備，通過限定時間段可找到特定時間下用戶的登錄設(shè)備。

在開發(fā)中需要注意關(guān)于userid與cookieid的多對多關(guān)聯(lián)，如果不加條件限制就做關(guān)聯(lián)，很可能引起數(shù)據(jù)膨脹問題。

在實際應(yīng)用中，會遇到許多需要將userid和cookieid做關(guān)聯(lián)的情 況。例如，需要在userid維度開發(fā)出該用戶近30日的購買次數(shù)、購買 金額、登錄時長、登錄天數(shù)等標(biāo)簽。前兩個標(biāo)簽可以很容易地從相應(yīng) 的業(yè)務(wù)數(shù)據(jù)表中根據(jù)算法加工出來，而登錄時長、登錄天數(shù)的數(shù)據(jù)存儲在相關(guān)日志數(shù)據(jù)中，日志數(shù)據(jù)表記錄的userid與cookieid為多對多 關(guān)系。因此在結(jié)合業(yè)務(wù)需求開發(fā)標(biāo)簽時，要確定好標(biāo)簽口徑定義。

本節(jié)中通過案例介紹了將userid和cookieid打通的一種解決方案，實踐中還存在需要將用戶在不同平臺間（如Web端和App端）行為打通的應(yīng)用場景。