电商用户行为数据分析报告

时间：2023-03-15 23:34:01 | 来源：电子商务

时间：2023-03-15 23:34:01 来源：电子商务

一、背景目标

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

二、数据处理

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

SELECT * FROM UserBehavior_newGROUP BY user_id, item_id, cat_id, behavior, timestampsHAVING COUNT(*) > 1;2.2 检查缺失值：未发现缺失值。

SELECT COUNT(user_id), COUNT(item_id), COUNT(cat_id), COUNT(behavior), COUNT(timestamps)FROM UserBehavior_newWHERE user_id IS NULLOR item_id IS NULLOR cat_id IS NULLOR behavior IS NULLOR 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）：895636SELECT COUNT(behavior) AS PV FROM UserBehavior_new WHERE behavior = 'pv';-- 2.页面访客数（UV）：9739SELECT 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_newGROUP BY dateORDER 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_newGROUP BY timeORDER 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 ASSELECT cat_id, COUNT(*) AS 浏览量TOP10 FROM UserBehavior_newWHERE behavior = 'pv'GROUP BY cat_idORDER BY COUNT(*) DESCLIMIT 10;CREATE VIEW buy_top10_cat ASSELECT cat_id, COUNT(*) AS 购买量TOP10 FROM UserBehavior_newWHERE behavior = 'buy'GROUP BY cat_idORDER BY COUNT(*) DESCLIMIT 10;-- 根据item_id创建视图CREATE VIEW pv_top10_item ASSELECT item_id, COUNT(*) AS 浏览量TOP10 FROM UserBehavior_newWHERE behavior = 'pv'GROUP BY item_idORDER BY COUNT(*) DESCLIMIT 10;CREATE VIEW buy_top10_item ASSELECT item_id, COUNT(*) AS 购买量TOP10 FROM UserBehavior_newWHERE behavior = 'buy'GROUP BY item_idORDER BY COUNT(*) DESCLIMIT 10; 验证分析：根据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 aGROUP 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) ASSELECT user_id, COUNT(behavior) AS frequency FROM UserBehavior_newWHERE behavior = 'buy'GROUP BY user_idORDER 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 1WHEN R BETWEEN 7 AND 8 THEN 2WHEN R BETWEEN 5 AND 6 THEN 3WHEN R BETWEEN 3 AND 4 THEN 4WHEN R < 3 THEN 5 ELSE 0 END) AS R_scores,(CASE WHEN F < 2 THEN 1WHEN F BETWEEN 2 AND 4 THEN 2WHEN F BETWEEN 5 AND 10 THEN 3WHEN F BETWEEN 11 AND 20 THEN 4WHEN F > 20 THEN 5 ELSE 0 END) AS F_scoresFROM (SELECT user_id, DATEDIFF('2017-12-03', MAX(date)) AS R, COUNT(*) AS FFROM UserBehavior_newWHERE 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 bGROUP 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%，这部分客户如果没有进行维护，可能会造成流失。

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

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

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

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

数据源：