用户画像

本章目标：能设计一套用户标签体系，用 SQL 算出统计型标签和生命周期标签，用位图做人群圈选，并说清 ID-Mapping 和隐私边界。 前置知识：进阶维度建模、实时数仓与 Flink 预计用时：110 分钟

运营常说“把近 30 天买过两次以上、客单价高于 200 的北京用户拉出来做活动”。这句话背后是一套用户画像系统：把散落在订单、行为、资料里的信息加工成标签，再按条件组合成人群。没有画像，每次活动都要数据开发临时写 SQL。

1. 为什么需要用户画像

用户画像解决三个问题：

• 统一口径：什么叫“活跃用户”、什么叫“高价值用户”，全站只有一套定义。
• 快速圈人：运营自助选择标签组合，分钟级得到人群包，不用排队等开发。
• 可追踪效果：人群包有版本和快照，活动结束后能对比投放前后指标。

画像不是一张大宽表就完事。标签会频繁新增和修改，存储和计算方式要能支撑这种变化。

2. 核心概念

2.1 标签的三种来源

统计型最基础，规则型最容易解释，模型型需要算法团队配合。先把统计型和规则型做扎实，不要一上来就上模型。

2.2 标签的存储形态

明细标签表（长表）
user_id | tag_code | tag_value | update_time
U01     | lifecycle | 沉睡复购   | 2024-03-10
U01     | city      | 北京       | 2024-03-10

画像宽表（宽表）
user_id | lifecycle | city | order_cnt_30d | gmv_180d
U01     | 沉睡复购   | 北京 | 2             | 200.00

长表便于新增标签和追溯，宽表便于查询和圈人。常见做法是长表做加工，宽表做服务，定期从长表刷新。

2.3 人群圈选与位图

人群是标签条件的组合，例如“北京 + 高价值活跃 + 近 30 天有下单”。如果每个条件都去扫宽表，数据量大时很慢。

位图把每个用户映射到一个 bit 位，一个标签值对应一个整数：

北京用户     01011010
高价值活跃   00110000
近 30 天下单 01111000
交集         00010000  → 第 5 位用户

按位与做交集、按位或做并集、按位取反做排除，圈人变成整数运算。ClickHouse 的 bitmap、Doris 的 BITMAP、Redis 的 BITCOUNT 都支持这种思路。

2.4 ID-Mapping

同一个人可能有三套 ID：设备号、手机号、业务账号。下单用账号，浏览用设备，活动页用手机号。不打通就会把一个人算成三个用户。

映射表结构：

id_type | id_value        | one_id
device  | dev-a1          | U100
phone   | phone_hash_001  | U100
account | acc-778         | U100

one_id 是全站统一用户 ID。映射要处理一对多冲突（一个手机号绑定多个账号）、历史合并和拆分，规则必须由业务确认。

2.5 隐私红线

画像系统大量使用个人信息，必须遵守最小必要原则：

• 手机号、邮箱、身份证不存明文，存加盐哈希或加密值。
• 可识别信息和标签数据分表存放，按权限申请访问。
• 支持用户撤回授权，撤回后停止加工和投放。
• 人群包只给统计结果或匿名 ID，不直接导出明文联系方式。

3. 动手示例

用 DuckDB 建一张小型订单表，完整走一遍“加工标签、生成宽表、位图圈人”。

3.1 准备用户行为数据

CREATE OR REPLACE TABLE user_orders (
    user_id VARCHAR,
    order_id VARCHAR,
    order_ts TIMESTAMP,
    amount DECIMAL(12, 2),
    status VARCHAR
);

INSERT INTO user_orders VALUES
    ('U01', '1001', TIMESTAMP '2024-01-01 10:00:00', 120.00, 'paid'),
    ('U01', '1002', TIMESTAMP '2024-01-15 12:00:00',  80.00, 'paid'),
    ('U02', '1003', TIMESTAMP '2024-01-02 09:00:00', 260.00, 'paid'),
    ('U03', '1004', TIMESTAMP '2024-02-03 20:00:00',  99.00, 'paid'),
    ('U02', '1005', TIMESTAMP '2024-02-10 11:00:00',  45.00, 'canceled'),
    ('U04', '1006', TIMESTAMP '2024-03-05 08:30:00', 310.00, 'paid'),
    ('U03', '1007', TIMESTAMP '2024-03-06 19:00:00',  75.00, 'paid'),
    ('U05', '1008', TIMESTAMP '2024-03-07 15:00:00', 150.00, 'paid'),
    ('U04', '1009', TIMESTAMP '2024-03-08 16:00:00', 220.00, 'paid'),
    ('U01', '1010', TIMESTAMP '2024-03-09 21:00:00',  60.00, 'canceled');

把统计日期固定为 2024-03-10，保证每次运行结果一致。

3.2 计算 RFM 指标

CREATE OR REPLACE TABLE user_rfm AS
SELECT
    user_id,
    DATE '2024-03-10' - MAX(CAST(order_ts AS DATE)) AS recency_days,
    COUNT(*) AS frequency,
    SUM(amount) AS monetary
FROM user_orders
WHERE status = 'paid'
GROUP BY user_id;

SELECT * FROM user_rfm ORDER BY user_id;

结果：

U01  55  2  200.00
U02  68  1  260.00
U03   4  2  174.00
U04   2  2  530.00
U05   3  1  150.00

只有 paid 订单参与计算，取消订单不计入频次和金额。

3.3 打生命周期标签

CREATE OR REPLACE TABLE user_profile AS
SELECT
    r.*,
    CASE
        WHEN recency_days <= 7 AND frequency >= 2 THEN '高价值活跃'
        WHEN recency_days <= 30 THEN '活跃'
        WHEN frequency >= 2 THEN '沉睡复购'
        ELSE '流失风险'
    END AS lifecycle_tag
FROM user_rfm r;

SELECT user_id, recency_days, frequency, monetary, lifecycle_tag
FROM user_profile
ORDER BY user_id;

按规则，U03、U04 是“高价值活跃”，U05 是“活跃”，U01 是“沉睡复购”，U02 是“流失风险”。阈值来自业务，不要照抄，要和运营确认。

3.4 生成位图并圈人

CREATE OR REPLACE TABLE user_bitmap AS
SELECT
    user_id,
    lifecycle_tag,
    1 << CAST(SUBSTR(user_id, 2) AS INTEGER) AS bit
FROM user_profile;

CREATE OR REPLACE TABLE crowd_high_value AS
SELECT
    bit_or(bit) AS crowd_bitmap,
    bit_count(bit_or(bit)) AS user_cnt
FROM user_bitmap
WHERE lifecycle_tag = '高价值活跃';

SELECT * FROM crowd_high_value;

结果是一个整数 24（第 3、4 位为 1）和人数 2。真实系统中用户 ID 会有几十亿，位图分桶存储，圈人时对各分桶做位运算再汇总。

3.5 ID-Mapping 查询

CREATE OR REPLACE TABLE id_mapping (
    id_type VARCHAR,
    id_value VARCHAR,
    one_id VARCHAR
);

INSERT INTO id_mapping VALUES
    ('device',  'dev-a1',        'U100'),
    ('phone',   'phone_hash_001', 'U100'),
    ('account', 'acc-778',        'U100'),
    ('device',  'dev-b2',        'U200');

SELECT one_id
FROM id_mapping
WHERE id_value = 'phone_hash_001';

查询返回 U100。上游所有行为表在写入画像前，都要先把各自的 ID 映射成 one_id，否则标签会分散在多个“假用户”上。

4. 常见坑

4.1 标签口径没有文档

“活跃用户”有人按登录算，有人按下单算，圈出来的人数差几倍。每个标签必须有名称、定义、计算逻辑、更新频率和负责人。

4.2 宽表无限加列

每来一个需求就加一列，宽表几百列，更新一次要重写全表。长表存标签、宽表只保留高频标签，冷门标签按需查询。

4.3 用最新标签回看历史

用户现在是“高价值”，不代表三个月前也是。做历史分析要用标签快照或按事件时间重算，不能用当前值倒推。

4.4 ID 映射冲突不处理

一个手机号对应多个账号，直接取第一条会把行为拼到错误的人身上。冲突要有优先级和人工确认机制，合并和拆分都要留审计记录。

4.5 人群包不可复现

运营今天圈了 10 万人，明天用同样条件变成 9.8 万，无法解释。人群包要保存标签版本、计算时间和完整条件。

4.6 明文导出个人信息

把手机号、地址直接导出给外部渠道，既违规也容易泄露。只输出匿名 ID 或加密标识，由投放系统在受控环境内完成匹配。

5. 在线练习

自测：

• 能写出“最近一次购买距今天数”的聚合 SQL。
• 能解释同一个用户为什么会被算成多个用户。

6. 面试延伸

面试题：用户画像系统怎么分层？

是什么：源数据层（订单、行为、资料）、标签加工层（统计/规则/模型）、标签存储层（长表、宽表、位图）、应用层（圈人、推荐、触达）。

解决什么问题：标签生产和标签使用解耦，新增标签不影响上层应用，圈人不用每次扫明细。

我的实操边界：本章用 DuckDB 加工了小型标签表和位图，未做过亿级用户的画像平台。

面试题：标签宽表和长表怎么选？

是什么：长表一行一个标签值，便于扩展和追溯；宽表一行一个用户，便于查询和圈人。

解决什么问题：长表解决“标签多、变化快”，宽表解决“查询快、组合方便”，通常两者配合。

我的实操边界：本章两种形态都建过，未做过宽表的自动刷新调度。

面试题：位图为什么适合人群圈选？

是什么：每个用户占一个 bit，标签值对应一个位图，交集、并集、排除都能用位运算完成。

解决什么问题：传统 JOIN 圈人要扫多张表，位图把集合运算压缩成整数运算，速度快、存储省。

我的实操边界：本章在 DuckDB 里用 bit_or 模拟，未使用 ClickHouse/Doris 的生产位图。

面试题：ID-Mapping 的难点是什么？

是什么：多渠道 ID 统一、一对多冲突、历史合并拆分、映射关系随时间变化。

解决什么问题：不统一 ID，同一个人会被重复计算，标签和行为链路断裂。

我的实操边界：本章设计了映射表结构，未实现概率匹配和实时合并。

7. 本章验收

• 能说出统计型、规则型、模型型标签的区别，并各举一例。
• 能用 SQL 算出用户最近购买天数、频次和累计金额。
• 能按业务规则打生命周期标签，并解释每个阈值的含义。
• 能用位图表示一个人群，并算出交集人数。
• 能设计 ID-Mapping 表，并说明一对多冲突怎么处理。
• 能列出至少三条画像系统的隐私保护措施。