用户画像基础（九）

校验维度

（一）数据准确性

1. 与已知的真实数据进行对比
   • 在用户画像标签建设过程中，可能会有一些已知的真实数据可以作为参考。例如，用户在注册时提供的基本信息，如性别、年龄、地区等，可以与用户画像中的相应标签进行对比，以检查标签取值的准确性。
   • 如果企业有其他可靠的数据源，如第三方数据提供商提供的用户信息，也可以用来进行对比校验。
2. 检查数据范围的合理性
   • 对于数值型标签，需要检查其取值是否在合理的范围内。例如，消费金额不能为负数，登录次数不能超过一定的上限。
   • 可以根据业务经验和常识来确定合理的数据范围。例如，对于一个电商平台，用户的消费金额通常在一定的范围内，超出这个范围的取值可能是错误的。
3. 验证数据格式的正确性
   • 确保标签数据的格式符合预期，例如日期格式、数字格式等。
   • 如果标签数据的格式不正确，可能会导致后续的数据分析和应用出现问题。

（二）数据完整性

1. 检查标签的覆盖度
   • 标签应该能够覆盖到足够多的用户，避免出现大量用户没有被打上标签的情况。
   • 可以计算标签的覆盖率，即被打上标签的用户数量占总用户数量的比例。如果覆盖率过低，可能需要检查标签的计算方法是否存在问题。
2. 检查缺失值的处理
   • 对于可能存在缺失值的标签，需要检查是否进行了合理的处理，如填充默认值、标记为未知等。–访问标签 7日访问 nvl(null,0)
   • 缺失值的处理方式应该根据标签的具体情况和业务需求来确定。例如，对于用户的性别标签，如果存在缺失值，可以根据用户的其他行为特征进行推测，或者标记为未知。

（三）数据一致性

1. 跨表一致性检查
   • 如果同一个标签在不同的数据源或数据表中有不同的取值，需要进行一致性检查。
   • 例如，用户的消费金额在订单表和用户行为表中应该保持一致。可以通过对比两个表中的数据来检查一致性。
2. 时间序列一致性检查
   • 对于随时间变化的标签，需要检查其在不同时间点的取值是否符合预期的变化趋势。1-6
   • 例如，用户的活跃度标签在一段时间内应该保持相对稳定，或者按照一定的规律变化。如果出现异常的波动，可能需要检查标签的计算方法是否存在问题。

（四）业务合理性

1. 结合业务规则进行检查
   • 根据业务知识和规则，检查标签的取值是否符合业务逻辑。
   • 例如，对于高价值用户标签，应该根据用户的消费金额、购买频率等指标进行合理的定义和划分。如果标签的取值与业务规则不符，可能需要调整标签的计算方法。
2. 与业务目标的一致性
   • 检查标签是否能够满足业务需求，是否与业务目标相一致。
   • 例如，标签是否能够帮助营销部门进行精准营销，是否能够为产品优化提供有价值的洞察。如果标签不能满足业务目标，可能需要重新设计标签或者调整标签的计算方法。

校验方式

（一）抽样检查

1. 随机抽取一部分用户数据，对其标签进行人工检查。
   • 可以选择不同类型的用户，如高活跃度用户、低活跃度用户、新用户等，检查标签的准确性和合理性。
   • 人工检查可以发现一些自动化校验无法发现的问题，如标签取值的逻辑错误、与业务实际情况不符等。
2. 对于重要的标签或者存在疑虑的标签，可以增加抽样的比例，进行更详细的检查。
   • 例如，对于高价值用户标签，可以抽取较大比例的用户数据进行人工检查，以确保标签的准确性和可靠性。

（二）数据统计分析

1. 计算标签的统计指标，如均值、中位数、标准差等，检查其分布是否合理。
   • 例如，对于用户的消费金额标签，可以计算其均值和标准差，判断是否存在异常值。如果均值过高或过低，或者标准差过大，可能需要检查标签的计算方法是否存在问题。
2. 进行数据可视化分析，如绘制直方图、箱线图等，直观地展示标签数据的分布情况，发现潜在的问题。
   • 数据可视化可以帮助开发者更直观地了解标签数据的分布特征，发现异常值、偏态分布等问题。例如，通过绘制用户活跃度标签的直方图，可以观察到活跃度的分布情况，判断是否存在异常值或者不合理的分布。

（三）对比测试

1. 在上线前进行 A/B 测试，将用户分为两组，分别使用不同版本的标签进行分析和应用。
   • 通过比较两组用户的行为和业务指标，评估标签的效果和准确性。
   • A/B 测试可以帮助开发者确定哪种版本的标签更适合实际业务需求，同时也可以发现标签可能存在的问题。例如，对于一个新的用户画像标签，可以将用户分为两组，一组使用旧版本的标签，另一组使用新版本的标签，然后比较两组用户的购买转化率、留存率等指标，评估新版本标签的效果。
2. 与旧版本的标签进行对比，检查新版本标签是否在准确性、覆盖度等方面有明显的提升。
   • 如果新版本标签在某些方面不如旧版本标签，可能需要重新考虑标签的设计和计算方法。

（四）数据验证工具

1. 使用数据验证工具，如数据质量监控软件、数据校验框架等，对标签数据进行自动化的检查和验证。
   • 这些工具可以检查数据的格式、范围、一致性等方面的问题，并生成详细的报告。
   • 数据验证工具可以提高校验的效率和准确性，减少人工检查的工作量。例如，使用数据质量监控软件可以实时监测标签数据的质量，及时发现问题并进行处理。
2. 结合数据血缘分析工具，了解标签数据的来源和计算过程，便于发现潜在的问题和错误。
   • 数据血缘分析工具可以帮助开发者追踪标签数据的来源和计算过程，了解数据的流转情况。如果在某个环节出现问题，可以快速定位并解决。例如，通过数据血缘分析工具可以发现某个标签的数据来源出现错误，或者计算过程中存在逻辑错误。

SQL 举例说明

（一）数据准确性校验

1. 与已知的真实数据进行对比
   • 假设我们有一个用户表 users，其中包含字段 user_id（用户 ID）、registered_gender（注册时的性别）和 profile_gender（用户画像中的性别标签）。以下是一个 SQL 查询语句，用于检查用户画像中的性别标签与注册时的性别是否一致：

SELECT user_id, registered_gender, profile_gender
FROM users
WHERE registered_gender <> profile_gender;

这个查询语句将返回用户画像中的性别标签与注册时的性别不一致的用户记录。开发者可以进一步分析这些记录，找出导致不一致的原因。

2. 检查数据范围的合理性

• 假设我们有一个用户消费表 user_purchases，其中包含字段 user_id（用户 ID）和 purchase_amount（消费金额）。以下是一个 SQL 查询语句，用于检查消费金额是否为正数：

SELECT user_id, purchase_amount
FROM user_purchases
WHERE purchase_amount <= 0;

这个查询语句将返回消费金额为负数或零的用户记录。开发者可以进一步分析这些记录，找出导致消费金额不合理的原因。

3. 验证数据格式的正确性

• 假设我们有一个用户表 users，其中包含字段 user_id（用户 ID）和 birth_date（出生日期）。以下是一个 SQL 查询语句，用于检查出生日期的格式是否正确：

SELECT user_id, birth_date
FROM users
WHERE birth_date NOT LIKE '%YYYY-MM-DD%';

这个查询语句将返回出生日期格式不正确的用户记录。开发者可以进一步分析这些记录，找出导致出生日期格式错误的原因。

（二）数据完整性校验

1. 检查标签的覆盖度
   • 假设我们有一个用户表 users，其中包含字段 user_id（用户 ID）和 active_status（用户活跃度标签）。以下是一个 SQL 查询语句，用于计算用户活跃度标签的覆盖率：

SELECT COUNT(DISTINCT user_id) AS total_users,
       COUNT(DISTINCT user_id) FILTER (WHERE active_status IS NOT NULL) AS active_users,
       COUNT(DISTINCT user_id) FILTER (WHERE active_status IS NULL) AS inactive_users,
       (COUNT(DISTINCT user_id) FILTER (WHERE active_status IS NOT NULL) / COUNT(DISTINCT user_id)) * 100 AS coverage_percentage
FROM users;

这个查询语句将返回总用户数量、有活跃度标签的用户数量、没有活跃度标签的用户数量以及活跃度标签的覆盖率。开发者可以根据覆盖率的结果，判断标签的覆盖度是否足够。

2. 检查缺失值的处理

• 假设我们有一个用户表 users，其中包含字段 user_id（用户 ID）和 gender（用户性别标签）。以下是一个 SQL 查询语句，用于检查性别标签的缺失值处理情况：

SELECT user_id, gender
FROM users
WHERE gender IS NULL;

这个查询语句将返回性别标签为缺失值的用户记录。开发者可以进一步分析这些记录，确定是否需要对缺失值进行处理，以及采取何种处理方式。

（三）数据一致性校验

1. 跨表一致性检查
   • 假设我们有一个用户订单表 user_orders，其中包含字段 user_id（用户 ID）和 order_amount（订单金额），以及一个用户行为表 user_behaviors，其中包含字段 user_id（用户 ID）和 total_spent（用户总消费金额）。以下是一个 SQL 查询语句，用于检查两个表中的消费金额是否一致：

SELECT uo.user_id, uo.order_amount, ub.total_spent
FROM user_orders uo
JOIN user_behaviors ub ON uo.user_id = ub.user_id
WHERE uo.order_amount <> ub.total_spent;

这个查询语句将返回订单表中的订单金额与用户行为表中的总消费金额不一致的用户记录。开发者可以进一步分析这些记录，找出导致不一致的原因。

2. 时间序列一致性检查

• 假设我们有一个用户活跃度表 user_activity，其中包含字段 user_id（用户 ID）、activity_date（活动日期）和 activity_level（活跃度级别）。以下是一个 SQL 查询语句，用于检查用户活跃度在时间序列上的一致性：

SELECT user_id, activity_date, activity_level,
       LAG(activity_level) OVER (PARTITION BY user_id ORDER BY activity_date) AS previous_activity_level
FROM user_activity;

这个查询语句将返回用户的活动日期、活跃度级别以及上一个活动日期的活跃度级别。开发者可以通过比较当前活跃度级别和上一个活跃度级别，判断用户活跃度在时间序列上是否保持一致。如果出现异常的波动，可能需要检查标签的计算方法是否存在问题。

（四）业务合理性校验

1. 结合业务规则进行检查
   • 假设我们有一个用户画像表 user_profiles，其中包含字段 user_id（用户 ID）、purchase_frequency（购买频率）和 high_value_user（高价值用户标签）。以下是一个 SQL 查询语句，用于检查高价值用户标签的定义是否符合业务规则：

SELECT user_id, purchase_frequency, high_value_user
FROM user_profiles
WHERE (purchase_frequency < 3 AND high_value_user = TRUE)
   OR (purchase_frequency >= 3 AND high_value_user = FALSE);

这个查询语句将返回购买频率与高价值用户标签不一致的用户记录。开发者可以根据业务规则，调整高价值用户标签的计算方法，确保其符合业务逻辑。

2. 与业务目标的一致性

• 假设我们有一个用户画像表 user_profiles，其中包含字段 user_id（用户 ID）、product_preference（产品偏好标签）和 marketing_campaign（营销活动目标用户标签）。以下是一个 SQL 查询语句，用于检查产品偏好标签是否与营销活动目标用户标签一致：

SELECT user_id, product_preference, marketing_campaign
FROM user_profiles
WHERE product_preference NOT IN (marketing_campaign);

这个查询语句将返回产品偏好标签与营销活动目标用户标签不一致的用户记录。开发者可以根据业务目标，调整产品偏好标签的计算方法，确保其能够为营销活动提供有价值的洞察。

上线前保证用户画像的标签质量方式

（一）标签质量测试

1. 交叉验证
   • 定义：通过使用不同的数据源或方法对标签进行验证，以确保标签的准确性和可靠性。
   • 举例：对于用户的年龄标签，可以使用用户注册信息和第三方数据进行交叉验证。如果两者之间存在较大差异，需要进一步检查数据来源和计算方法。
   • SQL 示例：假设我们有一个用户表users，其中包含字段user_id（用户 ID）、age_registered（注册时的年龄）和age_calculated（通过其他数据计算得到的年龄）。以下是一个 SQL 查询语句，用于检查两个年龄标签的一致性：

SELECT user_id, age_registered, age_calculated
FROM users
WHERE age_registered <> age_calculated;

2. 逻辑拆解

• 定义：将复杂的标签计算逻辑拆分成多个简单的步骤，逐一进行验证，以确保整个计算过程的正确性。
• 举例：对于一个高价值用户标签，可能由多个子标签组成，如消费金额高、购买频率高、忠诚度高等。可以分别对这些子标签进行验证，然后再检查它们的组合逻辑是否正确。
• SQL 示例：假设我们有一个用户消费表user_purchases，其中包含字段user_id、purchase_amount（消费金额）和purchase_frequency（购买频率）。以下是一个 SQL 查询语句，用于检查高价值用户标签的子标签：

SELECT user_id, purchase_amount, purchase_frequency
FROM user_purchases
WHERE purchase_amount > 1000 AND purchase_frequency > 5;

3. 逻辑佐证

• 定义：通过寻找其他相关数据或证据来支持标签的合理性和准确性。
• 举例：对于一个活跃用户标签，可以通过用户的登录次数、浏览时长、操作频率等多个指标来佐证。如果这些指标之间存在较强的相关性，那么活跃用户标签的准确性就更高。
• SQL 示例：假设我们有一个用户行为表user_behaviors，其中包含字段user_id、login_count（登录次数）、browse_duration（浏览时长）和operation_frequency（操作频率）。以下是一个 SQL 查询语句，用于检查活跃用户标签的逻辑佐证：

SELECT user_id, login_count, browse_duration, operation_frequency
FROM user_behaviors
WHERE login_count > 10 AND browse_duration > 60 AND operation_frequency > 5;

（二）标签覆盖度验证

1. 定义：检查标签能够覆盖到的用户数量，确保标签具有足够的代表性和实用性。
2. 举例：对于一个新推出的用户画像标签，需要检查它能够覆盖到的用户比例。如果覆盖度过低，可能需要重新考虑标签的定义和计算方法。
3. SQL 示例：假设我们有一个用户表users，其中包含字段user_id和一个新的标签字段new_tag。以下是一个 SQL 查询语句，用于计算标签的覆盖度：

SELECT COUNT(DISTINCT user_id) AS total_users,
       COUNT(DISTINCT user_id) FILTER (WHERE new_tag IS NOT NULL) AS tagged_users,
       (COUNT(DISTINCT user_id) FILTER (WHERE new_tag IS NOT NULL) / COUNT(DISTINCT user_id)) * 100 AS coverage_percentage
FROM users;

（三）标签数据波动预测 - 数据分箱

1. 定义：将标签数据按照一定的规则进行分箱，然后分析每个箱内数据的波动情况，以预测标签数据的稳定性和可靠性。
2. 举例：对于用户的消费金额标签，可以将消费金额分为不同的区间，然后观察每个区间内的用户数量和消费金额的变化趋势。如果某个区间内的数据波动较大，可能需要进一步检查数据的来源和计算方法。
3. SQL 示例：假设我们有一个用户消费表user_purchases，其中包含字段user_id和purchase_amount（消费金额）。以下是一个 SQL 查询语句，用于对消费金额进行分箱并计算每个箱内的用户数量和平均消费金额：

SELECT purchase_bin, COUNT(DISTINCT user_id) AS user_count, AVG(purchase_amount) AS average_amount
FROM (
         SELECT user_id, purchase_amount,
                CASE
                    WHEN purchase_amount <= 100 THEN '0-100'
                    WHEN purchase_amount <= 200 THEN '101-200'
                    -- 添加更多分箱区间
                    ELSE '>200' END AS purchase_bin
         FROM user_purchases
     ) AS subquery
GROUP BY purchase_bin;

上线中保证每天产出的标签质量，及时性

（一）标签监控

1. NULL 值率
   • 定义：检查标签数据中 NULL 值的比例，确保标签数据的完整性。
   • 举例：如果某个标签的 NULL 值率过高，可能意味着数据采集或计算过程中存在问题。
   • SQL 示例：假设我们有一个用户表users，其中包含字段user_id和一个标签字段tag。以下是一个 SQL 查询语句，用于计算标签的 NULL 值率：

SELECT (COUNT(*) - COUNT(tag)) / COUNT(*) * 100 AS null_value_percentage
FROM users;

2. 环比、同比波动预警

• 定义：通过比较当前时间段与上一时间段或去年同期的标签数据变化情况，及时发现异常波动并发出预警。
• 举例：如果某个标签的环比或同比波动超过一定的阈值，可能需要进一步检查数据的来源和计算方法，或者是否存在业务上的异常情况。
• SQL 示例：假设我们有一个用户行为表user_behaviors，其中包含字段user_id、date（日期）和一个标签字段tag。以下是一个 SQL 查询语句，用于计算标签的环比和同比波动：

WITH current_period AS (
    SELECT tag, COUNT(DISTINCT user_id) AS user_count
    FROM user_behaviors
    WHERE date BETWEEN '2024-08-01' AND '2024-08-31'
    GROUP BY tag
),
previous_period AS (
    SELECT tag, COUNT(DISTINCT user_id) AS user_count
    FROM user_behaviors
    WHERE date BETWEEN '2024-07-01' AND '2024-07-31'
    GROUP BY tag
)
SELECT c.tag, c.user_count AS current_user_count, p.user_count AS previous_user_count,
       ((c.user_count - p.user_count) / p.user_count) * 100 AS month_on_month_growth_rate
FROM current_period c
JOIN previous_period p ON c.tag = p.tag;

3. 标签产出时间预警

• 定义：监控标签的产出时间，确保标签能够按时生成，以满足业务需求。
• 举例：如果标签的产出时间延迟，可能会影响到后续的数据分析和业务决策。可以设置预警机制，当标签产出时间超过一定的阈值时，发出警报并通知相关人员进行处理。
• SQL 示例：假设我们有一个标签生成时间表tag_generation_times，其中包含字段tag_name（标签名称）、generation_time（生成时间）和expected_time（预期生成时间）。以下是一个 SQL 查询语句，用于检查标签的产出时间是否延迟：

SELECT tag_name, generation_time, expected_time,
       CASE
           WHEN generation_time > expected_time THEN 'Delayed'
           ELSE 'On time' END AS status
FROM tag_generation_times;

4. 标签效果衰竭预警

• 定义：监测标签的效果是否随着时间的推移而逐渐减弱，以便及时调整标签的定义和计算方法，或者采取其他措施来提高标签的有效性。
• 举例：对于一个用于精准营销的用户画像标签，如果发现该标签对用户的转化率逐渐降低，可能需要重新评估标签的准确性和实用性。
• SQL 示例：假设我们有一个营销活动表marketing_campaigns，其中包含字段campaign_id（营销活动 ID）、tag_used（使用的标签）、conversion_rate（转化率）和date（日期）。以下是一个 SQL 查询语句，用于监测标签效果的衰竭情况：

WITH tag_performance AS (
    SELECT tag_used, date, SUM(CASE WHEN conversion = TRUE THEN 1 ELSE 0 END) / COUNT(*) AS conversion_rate
    FROM marketing_campaigns
    GROUP BY tag_used, date
)
SELECT tag_used, date, conversion_rate,
       LAG(conversion_rate) OVER (PARTITION BY tag_used ORDER BY date) AS previous_conversion_rate,
       (conversion_rate - LAG(conversion_rate) OVER (PARTITION BY tag_used ORDER BY date)) / LAG(conversion_rate) OVER (PARTITION BY tag_used ORDER BY date) * 100 AS percentage_change
FROM tag_performance;

上线后监控用户画像指标的健康度

（一）画像表元数据监控分析，对冷热标签预警

1. 定义：监控用户画像表的元数据，包括表结构、字段类型、数据量等，以及分析标签的使用频率和热度，对冷热度变化较大的标签发出预警。
2. 举例：如果某个标签在一段时间内很少被使用，可能意味着该标签已经不再适用于当前的业务需求，可以考虑对其进行优化或删除。相反，如果某个标签的使用频率突然增加，可能需要进一步检查该标签的数据质量和准确性。
3. SQL 示例：假设我们有一个用户画像表user_profiles，其中包含多个标签字段。以下是一个 SQL 查询语句，用于统计每个标签的使用频率：

这段SQL分为两部分：

子查询 (subquery)：

从表 user_profiles 中提取并计算每个用户的age_group（年龄段）、gender（性别）、location（位置）等字段。

UNPIVOT 操作：

将子查询中的宽表（多列）转换为长表形式（列变为行），即将 age_group、gender、location 转化为统一的标签格式。

GROUP BY 和统计：

对生成的标签进行分组，统计每个标签的使用次数。

子查询 (subquery)

SELECT 
    CASE 
        WHEN age < 30 THEN 'young' 
        WHEN age >= 30 AND age < 50 THEN 'middle_aged' 
        WHEN age >= 50 THEN 'old' 
    END AS age_group,
    -- 添加更多标签字段的判断
    gender, 
    location
FROM user_profiles

作用：

CASE 语句：

按照用户的 age（年龄）划分为3个年龄段：

• 年龄小于30：young。

• 年龄在30到49之间：middle_aged。

• 年龄50及以上：old。

结果存入列 age_group。

字段提取：

同时提取用户的 gender 和 location 字段，作为标签候选。

用途：

提取用户画像的相关信息，用于后续分析。

示例数据：

假设 user_profiles 数据如下：

age	gender	location
25	male	New York
35	female	Los Angeles
55	male	Chicago

子查询结果：

age_group	gender	location
young	male	New York
middle_aged	female	Los Angeles
old	male	Chicago

UNPIVOT 操作

UNPIVOT (tag_name FOR tag_value IN (age_group, gender, location)) AS unpivoted

作用：

UNPIVOT 将多列（age_group、gender、location）转换为两列：

tag_name：表示字段名称（如 age_group、gender、location）。

tag_value：表示对应字段的值（如 young、male、New York）。

用途：

将宽表转为长表，便于统一处理和统计标签。

UNPIVOT 示例：

对于子查询的结果，UNPIVOT 后的数据为：

tag_name	tag_value
age_group	young
gender	male
location	New York
age_group	middle_aged
gender	female
location	Los Angeles
age_group	old
gender	male
location	Chicago

外部查询

SELECT tag_name, COUNT(*) AS usage_count
FROM (subquery)
GROUP BY tag_name;

作用：

GROUP BY：

按 tag_name 对标签分组。

COUNT(*)：

统计每个标签的使用次数。

示例数据结果：

基于上述 UNPIVOT 的数据，统计后的结果如下：

tag_name	usage_count
age_group	3
gender	3
location	3

（二）hook 采集 SQL 语句，对不合理标签进行重构

1. 定义：通过 hook 技术采集用户画像标签生成的 SQL 语句，分析 SQL 语句的执行效率和合理性，对不合理的标签进行重构，以提高标签的生成效率和数据质量。
2. 举例：如果发现某个标签的生成 SQL 语句执行时间过长，或者存在复杂的嵌套查询和连接操作，可以考虑优化 SQL 语句，或者重新设计标签的计算方法。
3. SQL 示例：假设我们使用了一个数据库监控工具，可以采集到用户画像标签生成的 SQL 语句。以下是一个对采集到的 SQL 语句进行分析的示例：