淘宝用户行为深度解析：从数据清洗到RFM模型实战

1. 淘宝用户行为分析实战入门做电商数据分析的朋友们应该都清楚用户行为数据就像一座金矿关键看你怎么挖。今天我就用阿里云天池的淘宝用户购物行为数据集带大家走一遍从数据清洗到RFM模型搭建的全流程。这个数据集包含了2017年11月25日至12月3日期间约100万条用户行为记录涵盖点击、收藏、加购、支付四种典型行为。先说说为什么要分析用户行为。简单来说就是三个目的第一了解用户怎么逛淘宝第二找出哪些商品更受欢迎第三针对不同用户制定精准营销策略。比如我们发现某类商品加购多但购买少就可以考虑是不是价格偏高或者详情页不够吸引人。数据集包含五个关键字段用户ID唯一标识一个用户商品ID唯一标识一件商品商品类目ID商品所属分类行为类型pv点击、fav收藏、cart加购、buy支付时间戳行为发生的时间我用的工具是MySQLNavicat组合SQL处理百万级数据完全够用。如果你电脑配置一般可以像我一样先截取前100万条数据练手。原始数据超过1亿条全量分析可能需要更高配置。2. 数据清洗完整流程2.1 数据预处理第一步拿到数据第一步永远是清洗。我习惯先用Navicat直接导入Excel文件导入时可以顺便修改字段名。如果导入后想改字段名可以用这个SQLALTER TABLE 淘宝用户购物行为数据集 CHANGE COLUMN 用户ID user_id INT;重点检查三类问题空值处理用IS NULL条件查询各字段空值重复值检查一个用户在同一时间对同一商品只能有一种行为异常值排查检查时间戳是否都在2017年11月25日至12月3日范围内2.2 时间字段处理技巧原始时间戳是整数格式需要转换成可读日期。这里用到MySQL的FROM_UNIXTIME函数ALTER TABLE 数据集 ADD datetimes TIMESTAMP; UPDATE 数据集 SET datetimes FROM_UNIXTIME(timestamps);接着提取日期、时间、小时三个字段ALTER TABLE 数据集 ADD dates DATE; UPDATE 数据集 SET dates DATE(datetimes); ALTER TABLE 数据集 ADD hours CHAR(2); UPDATE 数据集 SET hours DATE_FORMAT(datetimes, %H);2.3 异常值处理实战通过统计量分析发现部分数据超出时间范围DELETE FROM 数据集 WHERE datetimes 2017-11-25 OR datetimes 2017-12-03;处理完的数据可以计算基本统计量总计数值、最小值、最大值平均值、方差、标准差众数、中位数、四分位数比如用这个SQL计算商品类目ID的统计量SELECT MAX(category_id) - MIN(category_id) AS 极差, ROUND(STDDEV(category_id),2) AS 标准差 FROM 数据集;3. 关键指标计算与分析3.1 PV/UV指标解读先看最基本的流量指标PV页面浏览量用户每次访问商品页面算1次UV独立访客数同一设备只计1次计算9天总PV和UVSELECT COUNT(*) AS 总PV, COUNT(DISTINCT user_id) AS 总UV FROM 数据集 WHERE behavior_type pv;结果发现总PV 89万UV 9706人平均每个用户访问92次日均PV约10万UV约1000人3.2 转化率深度分析用户典型行为路径点击 → 收藏/加购 → 支付。我们用漏斗分析转化率WITH funnel AS ( SELECT SUM(behavior_typepv) AS pv, SUM(behavior_typefav) SUM(behavior_typecart) AS fav_cart, SUM(behavior_typebuy) AS buy FROM 数据集 ) SELECT buy/fav_cart AS 加购到支付转化率, fav_cart/pv AS 点击到加购转化率 FROM funnel;实际分析发现点击到加购转化率仅9.33%加购到支付转化率24.37%整体转化率2.27%优化建议提高主图和详情页吸引力优化商品推荐精准度设置加购商品降价提醒3.3 用户活跃时段分析分析每小时用户行为分布SELECT hours, SUM(behavior_typepv) AS pv, SUM(behavior_typebuy) AS buy FROM 数据集 GROUP BY hours ORDER BY hours;发现两个明显高峰午间高峰12-14点晚间高峰20-22点运营策略高峰时段加大广告投放低谷时段安排系统维护夜间推送专属优惠4. RFM模型实战应用4.1 RFM指标计算RFM模型是用户分群的经典方法RRecency最近一次购买距今天数FFrequency购买频次MMonetary消费金额本数据集缺失计算R值和F值-- 最近购买时间 SELECT user_id, DATEDIFF(2017-12-03, MAX(dates)) AS R FROM 数据集 WHERE behavior_type buy GROUP BY user_id; -- 购买频次 SELECT user_id, COUNT(*) AS F FROM 数据集 WHERE behavior_type buy GROUP BY user_id;4.2 用户分层策略根据RFM评分将用户分为8类WITH rfm AS ( -- 计算每个用户的R/F值 ), rfm_score AS ( -- 对R/F进行5分制评分 ) SELECT CASE WHEN R_score3 AND F_score3 THEN 高价值用户 WHEN R_score3 AND F_score3 THEN 潜力用户 WHEN R_score3 AND F_score3 THEN 流失预警用户 ELSE 流失用户 END AS user_type, COUNT(*) FROM rfm_score GROUP BY user_type;4.3 针对性运营方案针对不同用户群体制定策略高价值用户VIP专属优惠潜力用户购物车营销流失预警用户召回优惠券流失用户低优先级维护5. 用户留存分析技巧5.1 留存率计算计算次日、7日留存率WITH daily_users AS ( SELECT DISTINCT dates, user_id FROM 数据集 ) SELECT a.dates AS 日期, COUNT(DISTINCT b.user_id)/COUNT(DISTINCT a.user_id) AS 次日留存率 FROM daily_users a LEFT JOIN daily_users b ON a.user_idb.user_id AND b.datesDATE_ADD(a.dates, INTERVAL 1 DAY) GROUP BY a.dates;5.2 留存趋势解读发现12月2日后留存率明显提升可能原因双12预热活动开始平台发放优惠券商品预售开启运营启示大促前加强老用户触达设置专属回流福利优化活动页面体验6. 商品分析实战6.1 热销商品分析找出PV和购买量TOP10商品-- 浏览量TOP10 SELECT item_id, COUNT(*) AS pv FROM 数据集 WHERE behavior_typepv GROUP BY item_id ORDER BY pv DESC LIMIT 10; -- 购买量TOP10 SELECT item_id, COUNT(*) AS buys FROM 数据集 WHERE behavior_typebuy GROUP BY item_id ORDER BY buys DESC LIMIT 10;6.2 商品复购分析计算商品复购率WITH user_buys AS ( SELECT item_id, user_id, COUNT(*) AS buy_times FROM 数据集 WHERE behavior_typebuy GROUP BY item_id, user_id ) SELECT item_id, SUM(buy_times1)/COUNT(*) AS 复购率 FROM user_buys GROUP BY item_id ORDER BY 复购率 DESC;高复购商品特征快消品食品、日用品消耗品化妆品、母婴会员制商品7. 分析报告撰写建议最后给几个输出分析报告的建议结论前置先写关键发现数据可视化多用趋势图、漏斗图建议具体每个结论配1-2条落地建议持续迭代定期更新分析结果比如转化率分析可以这样呈现问题加购到支付转化率低24.37% 原因价格敏感、竞品影响 建议设置加购商品专属折扣记住数据分析的终极目标是为业务决策提供支持。下次当你看到用户行为数据时不妨多问几个为什么挖掘数据背后的商业价值。