目录
注：此博文为根据 赵宏田 老师的 用户画像·方法论与工程化解决方案 一书读后笔记而来，仅供学习使用
0. 相关文章链接
1. 画像应用的落地
用户画像最终的价值还是要落地运行，为业务带来实际价值。这 里需要开发标签的数据工程师和需求方相互协作，将标签应用到业务中。否则开发完标签后，数据还是只停留在数据仓库中，没有为业务决策带来积极作用。
画像开发过程中，还需要开发人员组织数据分析、运营、客服等团队的人员进行画像应用上的推广。对于数据分析人员来说，可能会关注用户画像开发了哪些表、哪些字段以及字段的口径定义；对运营、客服等业务人员来说，可能更关注用户标签定义的口径，如何在 Web端使用画像产品进行分析、圈定用户进行定向营销，以及应用在业 务上数据的准确性和及时性。
只有业务人员在日常工作中真正应用画像数据、画像产品，才能更好地推动画像标签的迭代优化，带来流量提升和营收增长，产出业绩价值。
2. 案例背景介绍
某图书电商网站拥有超过千万的网购用户群体，所售各品类图书100余万种。用户在平台上可进行浏览、搜索、收藏、下单、购买等行 为。商城的运营需要解决两个问题：一方面在企业产品线逐渐扩张、 信息资源过载的背景下，如何在兼顾自身商业目标的同时更好地满足 消费者的需求，为用户带来更个性化的购物体验，通过内容的精准推荐，更好地提高用户的点击转化率；另一方面在用户规模不断增长的 背景下，运营方考虑建立用户流失预警机制，及时识别将要流失的用 户群体，采取运营措施挽回用户。
商城自建立以来，数据仓库中积累着大量的业务数据、日志数据 及埋点数据。如何充分挖掘沉淀在数据仓库中的数据的价值，有效支 持用户画像的建设，成为当前的重要工作。
3. 相关元数据
在本案例中，可以获取的数据按其类型分为：业务类数据和用户行为数据。其中业务类数据是指用户在平台上下单、购买、收藏物品、货物配送等与业务相关的数据；用户行为数据是指用户搜索某条 信息、访问某个页面、点击某个按钮、提交某个表单等通过操作行为产生（在解析日志的埋点表中）的数据。
涉及数据仓库中的表主要包括用户信息表、商品订单表、图书信 息表、图书类目表、App端日志表、web端日志表、商品评论表等。下面就用户画像建模过程中会用到的一些数据表做详细介绍。
3.1. 用户信息表
用户信息表（如下表）存放有关用户的各种信息，如用户姓名、 年龄、性别、电话号码、归属地等信息。
| 字段 | 字段类型 | 字段定义 | 备注 |
| user_id | character varying(50) | 用户编码 | |
| user name | character varying(50) | 用户姓名 | |
| user_status_id | integer | 用户状态 | 0:未注册；1:已注册；2:已注销 |
| mail_id | character varying(40) | 邮箱编码 | |
| birthday | character vaiying(40) | 用户生口 | |
| genderid | smallint | 性别 | 0：男；1：女；2:其他 |
| call_phone id | character varying(64) | 电话号码 | |
| is_has_photo | smallint | 是否有头像 | |
| gmtcreated | timestamp | 创建时间 | |
| gmtcreateddate | date | 注册日期 | |
| provincename | character varying(20) | 归属省 | 用户填写 ＞手机号归属地 ＞身份证归 属地 |
| city name | character varying(20) | 归属市 | 同上 |
| useraddress | character varying(320) | 详细地址 |
3.2. 商品订单表
商品订单表（如下表）存放商品订单的各类信息，包括订单编 号、用户id、用户姓名、订单生成时间、订单状态等信息。
| 字段 | 字段类型 | 字段定义 | 备注 |
| id | bigint | 自增主键 | |
| source id | bigint | 订单来源标识 | 0:App； 1：Web； 2: H5； 3:其他 |
| user id | character varying(50) | 用户编码 | |
| user name | character varying(50) | 用户姓名 | |
| crderid | integer | 订单号 | |
| sid_book_id | bigint | 图书编码 | |
| std_book_name | character varying(80) | 图书名称 | |
| createiime | timestamp | 订单生成时间 | |
| createdate | date | 订单日期 | |
| order_remark | character varying(80) | 订单备庄 | |
| statusid | bigint | 订单状态 | 1:待支付；2:已完成；3:已取消；4:已退款；5:支付失败 |
| status Ji me | timestamp | 订单状态时间 | |
| crdcr amount | double precision | 订单金额 | |
| payaccount | character varying | 付款账户 | |
| pay_type_id | character varying | 付款方式 |
3.3. 埋点日志表
埋点日志表（如下表）存放用户访问App时点击相关控件的打点记录。通过在客户端做埋点，从日志数据中解析出来。
| 字段 | 字段类型 | 字段定义 | 备注 |
| login_id | character varying(50) | 设备登录名 | 设备记录的用户登录名 |
| user_id | character varying(50) | 用户id | |
| session_id | character varying(50) | 设备id | |
| visit_time | timestamp | 访问时间 | 本次访问操作在日志表中的生成时间 |
| report_time | timestamp | 上报时间 | 终端记录用户点击按钮时间 |
| province | character varying(50) | 用户所在省份 | 通过IP地址解析获取用户所在省份 |
| city | character varying(50) | 用户所在城市 | 通过1P地址解析获取用户所在城市 |
| referer_url | character varying(50) | 上一个页面url | 上一个访问页面地址 |
| url | character varying(50) | 当前页面url | 当前访问页面的链接地址 |
| client | character varying(50) | 操作系统 | mac/windows |
| date_id | date | 登录日期 | YYYY-MM-DD |
| Ion | character varying(50) | 纬度 | 用户设备登录时所在经度 |
| lat | character varying(50) | 纬度 | 用户设备登录时所在纬度 |
3.4. 访问日志表
访问日志表（如下表）存放用户访问App的相关信息及用户的LBS 相关信息，通过在客户端埋点，从日志数据中解析出来。
| 字段 | 字段类型 | 字段定义 | 备注 |
| login_id | character varying(50) | 设备登录名 | 设备记录的用户登录名 |
| user_id | character varying(50) | 用户id | |
| session_id | character varying(50) | 设备ID | |
| date_id | date | 访问日期 | YYYY-MM-DD |
| visit_time | timestamp | 访问时间 | 本次访问操作在日志表中的生成时间 |
| report_time | timestamp | .上报时间 | 终端记录用户点击按钮时向 |
| province | character varying(50) | 用户所在省份 | 通过IP地址解析获取用户所在省汾 |
| city | character varying(50) | 用户所在城市 | 通过1P地址解析获取用户所在城市 |
| referer_url | character varying(50) | 上一个页面url | 上一个访问页面地址 |
| url | character varying(50) | 当前页面url | 当前访问页面的链接地址 |
| client | character varying(50) | 操作系统 | android/ios/win |
| Ion | character varying(50) | 经度 | 用户设备登录时所在经度 |
| lat | character varying(50) | 纬度 | 用户设备登录时所在纬度 |
3.5. 商品评论表
商品评论表（如下表）存放用户对商品的评论信息。
| 字段 | 字段类型 | 字段定义 | 备注 |
| user_id | character varying(15) | 用户id | |
| user_name | character varying(15) | 用户姓名 | |
| user_content | character varying(64) | 评论内容 | |
| user_images | character varying(15) | 评论图片 | |
| status_id | bigint | 评论状态 | 1：待审核；2:已审核；3:已屏蔽 |
| order_code | integer | 订单id,订单对应编号 | |
| create_time | character varying(15) | 创建时间 | |
| create_date | date | 创建日期 | |
| content_ip | character varying(15) | 评论用户中 | |
| modify_time | timestamp | 更新时间 |
3.6. 搜索日志表
搜索日志表（如下表）存放用户在App端搜索相关的日志数据。
| 字段 | 字段类型 | 字段定义 | 备注 |
| login_id | character varying(15) | 设备登录名 | 设备记录的用户登录名 |
| user id | character varying(15) | 用户id | |
| session_id | character varying(15) | 设备id | |
| search_rad | character varying(15) | 搜索id | |
| date_id | date | 搜索日期 | |
| visit_time | timestamp | 搜索时间 | |
| search_q | character varying(15) | 用户搜索的关键词 | |
| tag_name | character varying(15) | 标签内容 | 用户搜索关键词切词后与标签库模糊匹配 到的标签内容 |
| random_id | character varying(15) | 每个访次的随机数 |
3.7. 用户收藏表
用户收藏表（如下表）记录用户收藏图书的数据。
| 字段 | 字段类型 | 字段定义 | 备注 | |||
| user_id | character varying(15) | 用户id | ||||
| crcate_datc | date | 收藏日期 | ||||
| creat time | timestamp | 收藏时间 | ||||
| book_id | bigint | 图书id | ||||
| bookname | character varying(50) | 图书名称 | ||||
| status_id | bigint | 收藏状态 | 1 :收藏；0：取消收藏 | |||
| modify date | date | 修改日期 | ||||
| modify_time | timestamp | 修改时间 | ||||
3.8. 购物车信息表
购物车信息表（如下表）记录用户将图书加入购物车的数据。
| 字段 | 字段类型 | 字段定义 | 备注 |
| user_id | character varying(15) | 用户id | |
| book_id | bigint | 图书id | |
| book_name | character varying(50) | 图书名称 | |
| quantity | bigint | 图书数量 | |
| create_date | date | 创建日期 | |
| creat_time | timestamp | 创建时间 | |
| status_id | bigint | 图书状态 | 1：加入购物车；0：移出购物车 |
| modifydate | date | 修改日期 | |
| modify_time | timestamp | 修改时间 |
4. 画像表结构设计
表结构设计也是画像开发过程中需要解决的一个重要问题。
表结构设计的重点是要考虑存储哪些信息、如何存储(数据分区)、如何应用(如何抽取标签)这3个方面的问题。
不同业务背景有不同的设计方式，这里提供两种设计思路：一是 每日全量数据的表结构；二是每日增量数据的表结构。
Hive需要对输入进行全盘扫描来满足查询条件，通过使用分区可以优化查询。对于用户标签这种日加工数据，随着时间的推移，分区 数量的变动也是均匀的。
每日全量数据，即该表的日期分区中记录着截止到当天的全量用户数据。例如，“ select count(*) from userprofile where data='20180701' ” 这条语句查询的是userprofile表截止到2018年7月1日的全量用户数据。日全量数据的优势是方便查询，缺点是不便于探 查更细粒度的用户行为。
每日增量数据，即该表的日期分区中记录着当日的用户行为数 据。例如，同样是 “ select count(*) from userprofile where data='20180701' "，这条语句查询的是userprofile表在2018年7月1 日记录的当日用户行为数据。日增量数据可视为ODS层的用户行为画 像，在应用时还需要基于该增量数据做进一步的建模加工。
4.1. 日全量数据
日全量数据表中，在每天对应的日期分区中插入截止到当天为止的全量数据，用户进行查询时，只需查询最近一天的数据即可获得最 新全量数据。下面以一个具体的日全量表结构的例子来进行说明。
CREATE TABLE dw.userprofile_attritube_all (
'userid' string COMMENT 'userid',
'labelweight' string COMMENT '标签权重'
)
COMMENT 'userid 用户画像数据'
PARTITIONED BY (
data_date string COMMENT '数据日期',
'theme' string COMMENT '二级主题',
'labelid' string COMMENT '标签 id'
)这里userid表示用户id,labelweight表示标签权重，theme表示标签归属的二级主题，labelid表示一个标签id。通过“日期+标签归 属的二级主题+标签id”的方式进行分区，设置三个分区字段更便于开发和查询数据。该表结构下的标签权重仅考虑统计类型标签的权重， 如：历史购买金额标签对应的权重为金额数量，用户近30日访问天数 为对应的天数，该权重值的计算未考虑较为复杂的用户行为次数、行 为类型、行为距今时间等复杂情况。
通过表名末尾追加“_all”的规范化命名形式，可直观看出这是一张日全量表。
例如，对于主题类型为“会员”的标签，插入“20190101”日的 全量数据，可通过语句：insert overwrite table dw.userprofile_userlabel_all partition(data_date='20190101',theme='member', labelid='ATTRITUBE_U_05_001')来实现。查询截止到“20190101”日的被打上会员标签的用户量，可通过语句：select count(distinct userid)from dw.userprofile_userlabel_all where data_date='20190101'来实现。
4.2. 日增量数据
日增量数据表，即在每天的日期分区中插入当天业务运行产生的数据，用户进行查询时通过限制查询的日期范围，就可以找出在特定 时间范围内被打上特定标签的用户。下面以一个具体的日增量表结构的例子来说明。
CREATE TABLE dw.userprofile_act_feature_append (
labelid STRING COMMENT '标签 id',
cookieid STRING COMMENT '用户id',
act_cnt int COMMENT '行为次数',
tag_type_id int COMMENT '标签类型编码',
act_type_id int COMMENT '行为类型编码'
)
comment '用户画像-用户行为标签表'
PARTITIONED BY (data_date STRING COMMENT '数据 日期')这里，labelid表示标签名称；cookieid表示用户id；act_cnt表 示用户当日行为次数，如用户当日浏览某三级品类商品3次，则打上次 数为3；tag_type_id为标签类型，如母婴、3C、数码等不同类型； act_type_id表示行为类型，如浏览、搜索、收藏、下单等行为。分区方式为按日期分区，插入当日数据。
通过表名末尾追加“_append”的规范化命名形式，可直观看出这 是一张日增量表。
例如，某用户在“20180701”日浏览某3c电子商品4次 (act_cnt),即给该用户(userid)打上商品对应的三级品类标签 (tagid),标签类型(tag_type_id)为3c电子商品，行为类型 (act_type_id)为浏览。这里可以通过对标签类型和行为类型两个字 段配亶维度表的方式，对数据进行管理。例如对于行为类型(act_type_id)字段，可以设定1为购买行为、2为浏览行为、3为收 藏行为等，在行为标签表中以数值定义用户行为类型，在维度表中维 护每个数值对应的具体含义。
该日增量数据表可视为ODS层用户行为标签明细。在查询过程中， 例如对于某用户id为001的用户，查询其在“20180701”日 到“20180707”日被打上的标签，可通过命令：select*from dw.userprofile_act_feature_append where userid='001'and data_date>='20180701'and data_date<='20180707'查询。
该日增量的表结构记录了用户每天的行为带来的标签，但未计算 打在用户身上标签的权重，计算权重时还需做进一步建模加工。
4.3. 关于宽表设计
用户画像表结构如何设计，没有一定要遵循的固定的格式，符合业务需要、能满足应用即可。下面通过两个宽表（用户属性宽表 和 用户日活跃宽表）设计的案例，提供另一种解决方案的思路。
用户属性宽表
| 字段 | 字段类型 | 字段定义 | 备注 |
| userid | Bigint | 用户编码 | |
| cookieid | String | cookieid | |
| login name | String | 登录名称 | |
| username | String | 用户姓名 | |
| user_status_id | Int | 用户状态 | 0：未激活；1：已激活；2：作废 |
| genderid | Int | 用户性别 | 1：男；2：女；3：未知 |
| birthday | Im | 用户生日 | |
| user_age | Im | 用户年龄 | |
| constcllationname | String | 用户星座名称 | 白羊座，金牛座，双子座等 |
| zodiacname | Sting | 用户生肖名称 | 鼠，牛，虎等 |
| cellphoneid | String | 用户手机编码 | |
| certid | String | 用户证件号码 | |
| sourceid | Bigint | 用户注册来源 | 代码表 dim.source info |
| is_valid_cellphone | Ini | 是否认证手机标志 | 1 :是；0：杳 |
| extremecard_lype | String | 会员类型 | |
| last_7_log_days | String | 近7日登录天数 | |
| last 30 log days | String | 近30日登录天数 | |
| total order amount | Double | 累计购买总金额 | |
| total_order_pieces | Bigint | 累计购买总件数 | |
| lastly order amount | Double | 近一年购买总金额 | |
| lastlyorderpieces | Bigint | 近一年购买总件数 | |
| totalconsultnum | Bigint | 累计咨询次数 | |
| totalrefusenum | Bigint | 累计退货次数 | |
| t ota Irefundamo unt | Double | 累计退货金额 | |
| red_num | Bigint | 账户红包个数 | |
| red_amoimt | Double | 账户红包金额 | |
| couponnum | Bigint | 账户优惠券个数 | |
| coupon amount | Double | 账户优惠券金额 | |
| createtime | Timestamp | 注册时间 | |
| createdate | String | 注册日期 | |
| first_time | String | 首次访问时间 | |
| last_time | String | 最近一次访问时间 | |
| date_dale | String | 数据日期 |
用户日活跃宽表设计
| 字段 | 字段类型 | 字段定义 |
| user_id | String | 用户编码 |
| date_date | String | 日期 |
| peak_day_visit_App_sid | Bigint | 高峰时段App访问次数 |
| peak_day_order_App_cnt | Rigint | App高峰时段订单数 |
| peak_day_visit_App_duration | Bigint | App高峰时段访问时长 |
| day_visit_App_url | Bigint | App访问页面数 |
| day_visit_App_sid | Bigint | App访问次数 |
| peak_day_visit_App_url | Bigint | 高峰时段App访问页面数 |
| day_visit_App_duration | Bigint | App访问时K |
| day_visit_App_cnt | Bigint | App访问天数 |
注：再次声明，此博文为根据 赵宏田 老师的 用户画像·方法论与工程化解决方案 一书读后笔记而来，仅供学习使用
注：其他相关文章链接由此进 -> 用户画像文章汇总
扫一扫
887
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
