一、背景目标

近两年受疫情影响，居家办公、居家隔离、足不出户的情况屡见不鲜，且线上电子商务行业飞速发展，越来越多的用户通过线上网购满足需求。现使用MySQL提取关于淘宝在2017年11月25日至2017年12月3日的部分用户数据，分析和研究其用户行为，针对用户流失情况提出建议，并通过RFM模型提出相应的运营策略。

二、数据处理

2.1 检查重复值：未发现重复值。

SELECT * FROM UserBehavior_new GROUP BY user_id, item_id, cat_id, behavior, timestamps HAVING COUNT(*) > 1;

2.2 检查缺失值：未发现缺失值。

SELECT COUNT(user_id), COUNT(item_id), COUNT(cat_id), COUNT(behavior), COUNT(timestamps) FROM UserBehavior_new WHERE user_id IS NULL OR item_id IS NULL OR cat_id IS NULL OR behavior IS NULL OR timestamps IS NULL;

2.3 预处理：

-- 1.将时间戳转化为日期格式 ALTER TABLE UserBehavior_new ADD COLUMN datetime VARCHAR(256); UPDATE UserBehavior_new SET datetime = FROM_UNIXTIME(timestamps); ALTER TABLE UserBehavior_new ADD COLUMN date VARCHAR(256); UPDATE UserBehavior_new SET date = FROM_UNIXTIME(timestamps, %Y-%m-%d); ALTER TABLE UserBehavior_new ADD COLUMN time VARCHAR(256); UPDATE UserBehavior_new SET time = FROM_UNIXTIME(timestamps, %H-%i-%s);更新后的数据表-- 2.确保所有用户行为发生在2017/11/25-2017/12/03期间 SELECT date FROM UserBehavior_new WHERE date < 2017-11-25 or date > 2017-12-03; -- 删除异常数据 DELETE FROM UserBehavior_new WHERE date < 2017-11-25 or date > 2017-12-03;

三、数据分析

3.1 用户行为数据分析

-- 1.页面浏览量（PV）：895636 SELECT COUNT(behavior) AS PV FROM UserBehavior_new WHERE behavior = pv; -- 2.页面访客数（UV）：9739 SELECT COUNT(DISTINCT user_id) AS UV FROM UserBehavior_new; -- 3.平均页面访客数 = PV/UV ：92 -- 4.以日期为基准对各指标变化情况的分析 CREATE VIEW UserBehavior_date AS SELECT date, SUM(CASE WHEN behavior = pv THEN 1 ELSE 0 END) AS 浏览量, SUM(CASE WHEN behavior = cart THEN 1 ELSE 0 END) AS 加购量, SUM(CASE WHEN behavior = fav THEN 1 ELSE 0 END) AS 收藏量, SUM(CASE WHEN behavior = buy THEN 1 ELSE 0 END) AS 购买量 FROM UserBehavior_new GROUP BY date ORDER BY date;运行结果（每日用户行为数据）每日用户行为数据折线图

根据以上数据和图表，可观察到每日的浏览量、加购量和收藏量变化趋势基本相同，结合用户购买路径，可说明加购量和收藏量的增加是由浏览量的增加促进的。如果要提升用户的加购、收藏量，可以关注新增用户方面；但是，购买量的变化趋势有所不同，在周中相对高于其他、周末低于其他，说明用户一般在周末浏览商品，在周中买商品的情况较多；周末各指标相对值都较高，说明用户在周末的行为更活跃，可能与用户的工作、学习、放假时间相关，可以在周末增加产品推荐策略、提高产品曝光量等，从而促进用户购买行为。

2017年11月25日和12月1、2日都是周末时间。但对比11月25日的数据指标，12月2日的数据指标是明显上涨，结合即将到来的双十二活动，可能与双十二预热活动的营销效果有关。

-- 5.以时间为基准对各指标变化情况的分析 CREATE VIEW UserBehavior_time AS SELECT time, SUM(CASE WHEN behavior = PV THEN 1 ELSE 0 END) AS 浏览量, SUM(CASE WHEN behavior = cart THEN 1 ELSE 0 END) AS 加购量, SUM(CASE WHEN behavior = fav THEN 1 ELSE 0 END) AS 收藏量, SUM(CASE WHEN behavior = buy THEN 1 ELSE 0 END) AS 购买量 FROM UserBehavior_new GROUP BY time ORDER BY time;运行结果（每小时用户行为数据）每小时用户行为数据折线图

根据以上数据和图表，可观察到每小时的各指标变化基本相同。0-7点使用淘宝的用户处于低峰，符合人们正常作息时间规律；10-16点的用户活跃度相对较高，且购买量相比其他指标较高，可能处于工作学习状态的用户没有时间进行浏览，故对浏览过的、已经加入购物车的或收藏的商品进行购买；20-22点达到一天的高峰值，可能与用户行为习惯或睡前购物心理有关，这个时间大多是用户已在家休息放松、有时间逛淘宝，可以在这个时段推广运营活动、推送促销商品、发送商品相关信息等刺激用户购买。

-- 6.用户整体行为SELECT SUM(总量) AS 总量, SUM(浏览量) AS 浏览量, SUM(加购量) AS 加购量, SUM(收藏量) AS 收藏量, SUM(购买量) AS 购买量 FROM UserBehavior_view; SELECT CONCAT(ROUND(SUM(浏览量)/SUM(总量)*100,2),%) AS 浏览转化率, CONCAT(ROUND((SUM(加购量)+SUM(收藏量))/SUM(总量)*100,2),%) AS 加购/收藏转化率, CONCAT(ROUND(SUM(购买量)/SUM(总量)*100,2),%) AS 购买转化率 FROM UserBehavior_view;用户整体分布图

根据用户整体行为，点击率为89.61%，而加购/收藏转化率8.36%、购买转化率2.04%，说明用户对网站内容感兴趣，但转化到下一行为的用户数较少，需要进一步分析用户流失原因。

-- 7.用户各环节行为路径 SELECT SUM(CASE WHEN 浏览量 > 0 THEN 1 ELSE 0 END) AS 浏览量, SUM(CASE WHEN 浏览量 > 0 AND 购买量 > 0 AND 加购量 = 0 AND 收藏量 = 0 THEN 1 ELSE 0 END) AS 浏览→购买, SUM(CASE WHEN 浏览量 > 0 AND (加购量 > 0 OR 收藏量 > 0) THEN 1 ELSE 0 END) AS 浏览→加购/收藏, SUM(CASE WHEN 浏览量 > 0 AND 购买量 = 0 AND 加购量 = 0 AND 收藏量 = 0 THEN 1 ELSE 0 END) AS 浏览→流失, SUM(CASE WHEN 浏览量 > 0 AND (加购量 > 0 OR 收藏量 > 0) AND 购买量 > 0 THEN 1 ELSE 0 END) AS 浏览→加购/收藏→购买, SUM(CASE WHEN 浏览量 > 0 AND (加购量 > 0 OR 收藏量 > 0) AND 购买量 = 0 THEN 1 ELSE 0 END) AS 浏览→加购/收藏→流失 FROM UserBehavior_view;用户行为路径图

根据以上数据和行为路径图，用户通过浏览网站后无下一行为的流失占比仅为5.84%，说明平台对用户具有很大吸引力；"用户浏览→加购/收藏"的转化率较高，说明用户会先将商品加入购物车或收藏、再考虑是否购买，可以提高促销力度（如满减、折扣活动）或者增强产品交互界面效果等方面引导用户加购或收藏；"加购/收藏→购买"的引导转化中仍有25.52%的用户流失，需进一步分析用户流失原因。

8.基于用户行为路径的流失分析

假设验证分析思路

假设一：推荐的商品不是用户想购买的？

-- 根据cat_id创建视图 CREATE VIEW pv_top10_cat AS SELECT cat_id, COUNT(*) AS 浏览量TOP10 FROM UserBehavior_new WHERE behavior = pv GROUP BY cat_id ORDER BY COUNT(*) DESC LIMIT 10; CREATE VIEW buy_top10_cat AS SELECT cat_id, COUNT(*) AS 购买量TOP10 FROM UserBehavior_new WHERE behavior = buy GROUP BY cat_id ORDER BY COUNT(*) DESC LIMIT 10; -- 根据item_id创建视图CREATE VIEW pv_top10_item AS SELECT item_id, COUNT(*) AS 浏览量TOP10 FROM UserBehavior_new WHERE behavior = pv GROUP BY item_id ORDER BY COUNT(*) DESC LIMIT 10; CREATE VIEW buy_top10_item AS SELECT item_id, COUNT(*) AS 购买量TOP10 FROM UserBehavior_new WHERE behavior = buy GROUP BY item_id ORDER BY COUNT(*) DESC LIMIT 10;根据cat_id对比浏览量TOP10和购买量TOP10根据item_id对比浏览量TOP10和购买量TOP10

验证分析：根据caty_id对比分析浏览量TOP10和购买量TOP10，重合部分商品种类占比60%，重合度不高；根据item_id对比浏览量TOP10和购买量TOP10，重合度为0。综上所述，用户高浏览量不能带来高购买量，即平台推荐的商品不是用户想买的，假设一成立。

假设二：提供的商品种类少？

SELECT COUNT(DISTINCT cat_id) AS 商品类目数量, COUNT(DISTINCT item_id) AS 商品数量 FROM UserBehavior_new;运行结果

根据以上数据可知，一共有5793类商品，且涵盖了398972种商品，可供用户选择的商品非常多，故假设二不成立。

假设三：推荐的商品不吸引用户？

SELECT a.购买次数, COUNT(a.item_id) AS 商品数, CONCAT(ROUND(COUNT(a.item_id) / (SELECT COUNT(DISTINCT item_id) FROM UserBehavior_new WHERE behavior = buy) *100, 2), %) AS 占比 FROM (SELECT item_id, COUNT(*) AS 购买次数 FROM UserBehavior_new WHERE behavior = buy GROUP BY item_id) AS a GROUP BY a.购买次数 ORDER BY a.购买次数;运行结果

根据以上数据，购买1次的商品占比88.45%，即用户选择相同商品进行复购的情况很少，故商品对用户的吸引力不强，假设三成立。

综上所述，用户流失的主要环节在“加购/收藏”中，其流失的原因与“推荐的商品不是用户想购买的”、“推荐的商品不吸引用户”有关，对此应优化平台的推荐机制，根据用户的需求不断迭代，吸引更多用户进行复购。

3.2 产品数据分析

-- 1.总订单量：20359 -- 人均购买量 = 总订单量/总用户数 : 2.09SELECT COUNT(behavior) FROM UserBehavior_newWHERE behavior = buy; -- 2.复购率 CREATE VIEW buy_f_view(user_id, frequency) AS SELECT user_id, COUNT(behavior) AS frequency FROM UserBehavior_new WHERE behavior = buy GROUP BY user_id ORDER BY frequency;不同购买频次的用户数图购买频率占比图

根据以上数据和图表，产品销售量主要分布在购买1~3次的用户，其中购买次数为1次的用户占比34%，该类客户有较大发展空间；整体复购率为66%，可说明用户与商品的粘性度较高。其中购买次数为2~3次的用户占比40%，该类用户有助于提高转化率。

3.3 基于RFM模型的用户价值分析

由于数据源未给出用户消费相关金额，故本次只分析"R"和"F"两个指标，即最近一次消费时间和购买频率。再通过客户相关指标与均值的大小关系将客户分组。

打分规则-- 1.计算R、F值，并按价值打分 CREATE VIEW rf AS SELECT a.*, (CASE WHEN R > 8 THEN 1 WHEN R BETWEEN 7 AND 8 THEN 2 WHEN R BETWEEN 5 AND 6 THEN 3 WHEN R BETWEEN 3 AND 4 THEN 4 WHEN R < 3 THEN 5 ELSE 0 END) AS R_scores, (CASE WHEN F < 2 THEN 1 WHEN F BETWEEN 2 AND 4 THEN 2 WHEN F BETWEEN 5 AND 10 THEN 3 WHEN F BETWEEN 11 AND 20 THEN 4 WHEN F > 20 THEN 5 ELSE 0 END) AS F_scores FROM (SELECT user_id, DATEDIFF(2017-12-03, MAX(date)) AS R, COUNT(*) AS F FROM UserBehavior_new WHERE behavior = buy GROUP BY user_id) AS a;部分运行结果-- 2.计算打分的平均值 SELECT AVG(R_SCORES) AS avg_r, AVG(F_scores) AS avg_f FROM rf;运行结果用户分类规则-- 3.按照R值和F值的平均分对用户进行分类，并统计 SELECT 用户分类, COUNT(*) AS 用户数 FROM (SELECT user_id, R值, F值, (CASE WHEN R值=高 AND F值=高 THEN 重要价值客户 WHEN R值=高 AND F值=低 THEN 重要发展客户 WHEN R值=低 AND F值=高 THEN 重要保持展客户 WHEN R值=低 AND F值=低 THEN 一般价值客户 ELSE 0 END) AS 用户分类 FROM (SELECT user_id, R_scores, F_scores, (CASE WHEN R_scores > 4.2625 THEN 高 ELSE 低 END) AS R值, (CASE WHEN F_scores > 1.8710 THEN 高 ELSE 低 END) AS F值 FROM rf) AS a) AS b GROUP BY 用户分类 ORDER BY 用户数 DESC;用户分类统计结果占比

根据以上数据，重要价值客户占44%，这部分客户可能会成为忠实客户，应加强与用户的交互程度，比如提供会员卡等；重要保持客户占22%，这部分客户的购买频次高，可采取促销活动、商品折扣等方式刺激用户消费；一般价值客户占20%，这部分客户如果没有进行维护，可能会造成流失，可以通过活动短信、站内信息等方式唤回用户；重要发展客户占13%，这部分客户最近有购买记录，即购买意向较高，可以根据用户的以往消费记录进行个性化推荐，促进用户复购。

四、结论与建议

1. 基于日期的行为分析，用户一般在周末浏览商品，在周中购买商品的情况较多，且用户在周末的行为更活跃。

建议在周末增加产品推荐策略、提高产品曝光量等，从而促进用户购买行为。

2. 基于时间的行为分析，用户在0-7点较少使用淘宝，在10-16点的活跃度相对较高，且购买量相比其他指标较高；用户在20-22点的行为量是一天的峰值。

建议在20-22时段推广运营活动、推送促销商品、发送商品相关信息等刺激用户购买；在10-16时段可发送短信、站内消息提醒用户在购物车或收藏夹的商品；在其他时段减少商品的广告投放量，以节约成本。

3. 基于用户购买路径，"用户浏览→加购/收藏"的转化率较高；

建议提高促销力度（如满减、折扣活动）或者增强产品交互界面效果等方面引导用户加购或收藏。

4. 基于用户行为路径，用户流失的主要环节在“加购/收藏”中，其流失的原因与“推荐的商品不是用户想购买的”、“推荐的商品不吸引用户”有关。

建议优化平台的推荐机制，根据用户的需求不断迭代，吸引更多用户进行复购。

5. 重要价值客户占44%，这部分客户可能会成为忠实客户；一般价值客户占20%，这部分客户如果没有进行维护，可能会造成流失。

对于重要价值客户，建议加强与用户的交互程度，比如提供会员卡等；

对于重要保持客户，建议采取促销活动、商品折扣等方式刺激用户消费；

对于重要发展客户，建议根据用户的以往消费记录进行个性化推荐，促进用户复购；

对于一般价值客户，建议通过活动短信、站内信息等方式唤回用户、激发用户购买欲望。

数据源：