扫码加入数据分析学习群

ID-MAP

开发用户标签的时候，有项非常重要的内容——ID-MApping，即把用户不同来源的身份标识通过数据手段识别为同一个主体。用户的属性、行为相关数据分散在不同的数据来源中，通过ID-MApping能够 把用户在不同场景下的行为串联起来，消除数据孤岛。图3-7展示了用 户与设备间的多对多关系。图3-8展示了同一用户在不同平台间的行为 示意图。

图3-7 用户和设备间的多对多关系

图3-8 串联同一个用户在不同平台间行为

举例来说，用户在未登录App的状态下，在App站内访问、搜索相关内容时，记录的是设备id（即cookieid）相关的行为数据。而用户在登录App后，访问、收藏、下单等相关的行为记录的是账号id（即 userid）相关行为数据。虽然是同一个用户，但其在登录和未登录设备时记录的行为数据之间是未打通的。通过ID-MApping打通userid和 cookieid的对应关系，可以在用户登录、未登录设备时都能捕获其行为轨迹。

下面通过一个案例介绍如何通过Hive的ETL工作完成ID-Mapping 的数据清洗工作。

缓慢变化维是在维表设计中常见的一种方式，维度并不是不变的，随时间也会发生缓慢变化。如用户的手机号、邮箱等信息可能会随用户的状态变化而改变，再如商品的价格也会随时间变化而调整上 架的价格。因此在设计用户、商品等维表时会考虑用缓慢变化维来开发。同样，在设计ID-Mapping表时，由于一个用户可以在多个设备上 登录，一个设备也能被多个用户登录，所以考虑用缓慢变化维表来记录这种不同时间点的状态变化（图3-9）。

图3-9 ID-Mapping拉链表

拉链表是针对缓慢变化维表的一种设计方式，记录一个事物从开 始到当前状态的全部状态变化信息。

在上图中，通过拉链表记录了userid每一次关联到不同cookieid 的情况。如userid为44463729的用户，在20190101这天登录某设 备，在6号那天变换了另一个设备登录。其中start_date表示该记录的 开 始 日 期 ， end_date 表 示 该 记 录 的 结 束 日 期 ， 当 end_date 为 99991231时，表示该条记录当前仍然有效。

首先需要从埋点表和访问日志表里面获取到cookieid和userid同 时出现的访问记录。下面案例中，ods.page_event_log是埋点日志 表 ， ods.page_view_log 是 访 问 日 志 表 ， 将 获 取 到 的 userid 和 cookieid 信 息 插 入 cookieid-userid 关 系 表 （ods.cookie_user_signin）中。代码执行如下：

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

创建ID-Map的拉链表，将每天新增到ods.cookie_user_signin 表中的数据与拉链表历史数据做比较，如果有变化或新增数据则进行更新。

CREATE TABLE `dw.cookie_user_zippertable`(

`userid` string COMMENT '账号ID',

`cookieid` string COMMENT '设备ID',

`start_date` string COMMENT 'start_date',

`end_date` string COMMENT 'end_date')

COMMENT 'id-map拉链表'

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'

创建完成后，每天ETL调度将数据更新到ID-Mapping拉链表中， 任务执行如下。

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

数据写入表中，如图3-9所示。

对于该拉链表，可查看某日（如20190801）的快照数据。

select *

from dw.cookie_user_zippertable

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

例如，目前存在一个记录userid和cookieid关联关系的表，但是 为多对多的记录（即一个userid对应多条cookieid记录，以及一条 cookieid对应多条userid记录）。这里可以通过拉链表的日期来查看 某个时间点userid对应的cookieid。查看某个用户（如32101029） 在某天（如20190801）关联到的设备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个设备，通过限定时间段可找到特定时间下用户的登录设备。

在开发中需要注意关于userid与cookieid的多对多关联，如果不加条件限制就做关联，很可能引起数据膨胀问题。

在实际应用中，会遇到许多需要将userid和cookieid做关联的情 况。例如，需要在userid维度开发出该用户近30日的购买次数、购买 金额、登录时长、登录天数等标签。前两个标签可以很容易地从相应 的业务数据表中根据算法加工出来，而登录时长、登录天数的数据存储在相关日志数据中，日志数据表记录的userid与cookieid为多对多 关系。因此在结合业务需求开发标签时，要确定好标签口径定义。

本节中通过案例介绍了将userid和cookieid打通的一种解决方案，实践中还存在需要将用户在不同平台间（如Web端和App端）行为打通的应用场景。