用户留存标签快速搭建实操指南：零门槛落地全步骤拆解

一、前置准备

1. 工具准备：安装MySQL 8.0，Windows下载地址：https://dev.mysql.com/downloads/mysql/8.0.html，双击安装选「Developer Default」，密码设为123456；Mac用户执行命令：brew install mysql@8.0，安装后启动服务。

2. 测试数据：无需准备真实数据，复制以下SQL到MySQL执行，生成可直接用的用户行为表：

```sql -- 创建用户行为表 CREATE TABLE user_behavior ( user_id INT, operate_time DATETIME, operate_type VARCHAR(20) ); -- 插入10条符合规则的测试数据 INSERT INTO user_behavior VALUES (1, '2024-05-01 08:00:00', 'login'), (1, '2024-05-03 09:00:00', 'click'), (2, '2024-05-02 10:00:00', 'login'), (2, '2024-05-10 11:00:00', 'login'), (3, '2024-04-25 07:00:00', 'login'), (3, '2024-05-01 08:00:00', 'login'), (4, '2024-04-10 09:00:00', 'login'), (5, '2024-05-05 10:00:00', 'login'), (5, '2024-05-20 11:00:00', 'click'), (6, '2024-04-20 08:00:00', 'login'); ```

二、核心标签搭建（3类常用留存标签）

1. 7日留存用户标签

逻辑：首次登录后7天内有有效操作的用户，标记为「是」，否则为「否」。执行完整SQL：

```sql -- 创建7日留存标签表 CREATE TABLE user_retention_7d AS SELECT user_id, '是' AS is_7d_retention, MIN(operate_time) AS first_login_time, MAX(operate_time) AS last_action_time FROM user_behavior WHERE operate_type = 'login' GROUP BY user_id HAVING DATEDIFF(MAX(operate_time), MIN(operate_time)) BETWEEN 0 AND 7; ```

重点：必须确保operate_type的登录值正确，若你的数据登录字段是'signin'，替换'login'为对应值即可

2. 30日活跃用户标签

逻辑：最近30天内有任意操作的用户，标记为「是」。执行SQL：

```sql -- 创建30日活跃标签表 CREATE TABLE user_active_30d AS SELECT DISTINCT user_id, '是' AS is_30d_active FROM user_behavior WHERE operate_time >= DATE_SUB(NOW(), INTERVAL 30 DAY); ```

重点：NOW()是MySQL内置当前时间函数，无需修改；若数据时间单位是小时，替换为INTERVAL 720 HOUR（30天=720小时）

3. 流失预警用户标签

逻辑：最近15天无操作，但近30天内有操作的用户，标记为「是」。执行SQL：

```sql -- 创建流失预警标签表 CREATE TABLE user_risk_loss AS SELECT u.user_id, '是' AS is_risk_loss FROM (SELECT DISTINCT user_id FROM user_behavior WHERE operate_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)) u LEFT JOIN (SELECT DISTINCT user_id FROM user_behavior WHERE operate_time >= DATE_SUB(NOW(), INTERVAL 15 DAY)) v ON u.user_id = v.user_id WHERE v.user_id IS NULL; ```

三、标签合并与验证

将3类标签合并到用户总表，确保每个用户都有完整标签：

```sql -- 创建用户标签总表 CREATE TABLE user_tags AS SELECT u.user_id, IFNULL(r.is_7d_retention, '否') AS is_7d_retention, IFNULL(a.is_30d_active, '否') AS is_30d_active, IFNULL(l.is_risk_loss, '否') AS is_risk_loss FROM (SELECT DISTINCT user_id FROM user_behavior) u LEFT JOIN user_retention_7d r ON u.user_id = r.user_id LEFT JOIN user_active_30d a ON u.user_id = a.user_id LEFT JOIN user_risk_loss l ON u.user_id = l.user_id; ```

验证标签正确性，执行查询：

```sql -- 查询7日留存用户数 SELECT COUNT() AS 7日留存用户数 FROM user_tags WHERE is_7d_retention = '是'; -- 查询流失预警用户数 SELECT COUNT() AS 流失预警用户数 FROM user_tags WHERE is_risk_loss = '是'; ```

四、常见问题快速解决

• 问题1：执行SQL报错「表不存在」 → 执行SHOW TABLES;查看所有表，不存在则复制前置准备的建表和插数据SQL重新执行。
• 问题2：时间格式错误 → 执行DESCRIBE user_behavior;查看operate_time字段类型，若不是DATETIME，执行：ALTER TABLE user_behavior MODIFY operate_time DATETIME;。
• 问题3：标签数量为0 → 执行SELECT FROM user_behavior LIMIT 10;查看数据，确认operate_time和operate_type字段值正常，若登录操作记录为空，补加登录数据后重新执行标签SQL。
• 问题4：用PostgreSQL数据库 → 替换NOW()为CURRENT_TIMESTAMP，DATE_SUB为INTERVAL 30 DAY，DATEDIFF用EXTRACT(DAY FROM (MAX(operate_time)-MIN(operate_time)))，其他逻辑不变。