Data analysis

MySQL淘宝用户行为分析

使用MySQL进行数据分析

Posted by 佘晓斌 on August 17, 2019

一、 提出问题

本次分析的目的是想通过对淘宝用户行为数据分析，为以下问题提供解释和改进建议：

1.分析用户使用APP过程中的常见电商分析指标，确定各个环节的流失率，找到需要改进的环节

2.研究用户在不同时间尺度下的行为规律，找到用户在不同时间周期下的活跃规律

3.找到用户对不同种类商品的偏好，找到针对不同商品的营销策略

4.找出最具价值的核心付费用户群，对这部分用户的行为进行分析

为了分析这些问题，我们使用以下两种模型进行分析

1.基于AARRR漏斗模型分析用户行为

本文通过常用的电商数据分析指标，采用AARRR漏斗模型拆解用户进入APP后的每一步行为。

AARRR模型是根据用户使用产品全流程的不同阶段进行划分的，针对每一环节的用户流失情况分析出不同环节的优化优先级，主要通过以下个各阶段来进行分析：

AARRR模型

2.基于RFM模型找出有价值的用户

image

由于不同用户对公司带来的收益差别很大，而且根据二八定律20%的做有价值用户能带来80%的收益，因此需要对用户进行价值评价，找到最有价值的用户群，并针对这部分用户进行差异化的营销。

这里参考著名的 RFM 模型对用户进行评价：

R-Recency（最近一次购买时间），R指用户上一次消费的时间，上一次购物时间距今最近的顾客通常在近期响应营销活动的可能性也最大，对于APP而言，很久没有购物行为可能意味着用户放弃了APP的使用，重新唤起用户也需要更多的成本。

F-Frequency（消费频率），F指用户在某段时间内的购物次数，消费频率越高意味着这部分用户对产品的满意度最高，用户粘性最好，忠诚度也最高。

M-Money（消费金额），M指用户在某段时间内的购物金额，这也是为公司带来价值的最直接体现，而消费金额较高的用户在用户总体中人数较少，却能创造出更多价值，是需要重点争取的对象。

这三个维度互相关联，反映了每个用户的现在价值和潜在价值，将每个维度分成5个区间进行评分，通过计算分数找到最有价值的用户，并对用户进行分类，可以有针对性的不同类型用户采用不同的营销策略。

二、理解数据

数据来源：https://tianchi.aliyun.com/data

1.数据集介绍

本数据集(UserBehavior.csv)包含了2017年11月25日至2017年12月3日之间，有行为的约一百万随机用户的所有行为（行为包括点击、购买、加购、喜欢）。数据集的每一行表示一条用户行为，由用户ID、商品ID、商品类目ID、行为类型和时间戳组成，并以逗号分隔。

2.字段含义

image

行为类型：

1569743824213

关于数据集大小的一些说明如下：

1569743893895

由于原数据庞大（大于3G），运行速率缓慢，占用空间极大，因此只抽取原数据的5%进行分析。

详细处理过程：https://dwz.cn/HVY0nV2A

三、数据清洗

详细清洗结果如下：https://dwz.cn/9pWcqepD

四、构建模型

1. 基于AARRR模型分析用户行为

利用AARRR模型分析用户行为，此处数据主要涉及用户刺激和购买转化的环节，通过用户从浏览到最终购买整个过程的流失情况，包括浏览、收藏、加入购物车和购买环节，得到2017年11月25日至2017年12月3日这个时间段内的各项指标如下：

SELECT  COUNT(DISTINCT userId) uv,
	(SELECT COUNT(*) FROM tb2_copy1 WHERE behavior = 'pv') pv,
	(SELECT COUNT(*) FROM tb2_copy1 WHERE behavior = 'pv') / COUNT(DISTINCT userId) as 'pv/uv'
FROM tb2_copy1;

image

网页访问用户总数（UV)：49396

页面总访问量（PV）：4447807

  • 流量问题，日PV、日uv、人均页面访问数是多少？
-- 日流量数据
SELECT 
	date, COUNT(*) pv, COUNT(DISTINCT userId) uv,
	COUNT(*) / COUNT(DISTINCT userId) 平均访问量
FROM tb2_copy1
WHERE behavior = 'pv'
GROUP BY date;

image

（1）跳失率

-- 跳失率=只点击一次浏览的用户数量/总用户访问量
SELECT 
	count(*), COUNT(*)/ 49396 as 流失率 
	FROM
(SELECT 
 	userId FROM tb2_copy1 
 	GROUP BY userId 
 	HAVING COUNT(behavior) = 1) as t;

image

  • 在这九天的时间区间内，只有5个人只浏览了一个页面就离开了，占总访问量的0.01%，占比几乎可以忽略不计，说明淘宝拥有足够的吸引力让用户停留在APP中。

（2）用户总行为数漏斗计算

  • 转化率（用户激活）
SELECT
	behavior, count(*)
FROM
	tb2_copy1
GROUP BY behavior;

image

image

  • 由于收藏和加入购物车都为浏览和购买阶段之间确定购买意向的用户行为，且不分先后顺序，因此将其算作一个阶段。从上图可知，从浏览到有购买意向只有5%的转化率，当然有一部分用户是直接购买而未通过收藏和加入购物车，但也说明大多数用户浏览页面次数较多，而使用购物车和收藏功能较少，购买次数占使用购物车和收藏功能的23.8%， 说明从浏览到进行收藏和加入购物阶段是指标提升的重点环节。

（3）独立访客漏斗模型计算

SELECT COUNT(DISTINCT userId) 独立访客数
FROM tb2_copy1
GROUP BY behavior
ORDER BY COUNT(DISTINCT userId) DESC;

image

image

  • 上面是每一步用户行为的独立用户数，可以看到使用淘宝的用户中有68%的付费用户，用户付费转化率相当高。

（4）复购率

# 复购率是多少？
SELECT COUNT(*) 复购人数
FROM
(SELECT 
	DISTINCT userId, count(behavior)
FROM 
	tb2_copy1
WHERE behavior = 'buy'
GROUP BY userId 
HAVING COUNT(behavior) >= 2) as t；


SELECT COUNT(DISTINCT userId) 购买人数总计
FROM tb2_copy1
WHERE behavior = 'buy';

SELECT (22242 / 33620) 复购率;

image

  • 根据《精益数据分析》中的理论，如果年重复购买率达到60%及以上，则说明公司进入忠诚度模式，应将经营中心放在客户忠诚度上，即鼓励忠诚客户更加频繁地消费，不过目前我们分析的仅仅是9天的数据，所以公司到底属于什么样的模式，还需要继续观察才能得出结论

2. 不同时间尺度下用户行为模式分析

  • 一般情况下，我们可以分析用户月、周、日的 活跃度情况，不过数据仅有九天的数据量，所以下面只分析用户每天的活跃度情况以及每小时的行为情况，找出用户活跃规律

（1）分析九天中每天的用户行为

SELECT
	date,
	sum(case WHEN behavior = 'pv' THEN 1 ELSE 0 END) pv_count,
	sum(case WHEN behavior = 'buy' THEN 1 ELSE 0 END) buy_count,
	sum(case WHEN behavior = 'cart' THEN 1 ELSE 0 END) cart_count,
	sum(case WHEN behavior = 'fav' THEN 1 ELSE 0 END) fav_count
FROM 
	tb2_copy1
GROUP BY date
ORDER BY date;

image

image

  • 从上图中可以看出一开始用户的各项行为是数据波动不大，但是12/1是一个转折点，12/2和12/3相比前几日有一个明显的增长，最明显的是购物车（cart_count），涨幅最大。这两天是周六和周日，但是同为周末的11/25和11/26并没表现出如此明显的增长，因此排除日期规律这样的内在因素，而去考虑受到到外在刺激的可能性。

  • 例如，根据网络搜索，2017年双十二活动的预热时间是从12/1开始，因此不排除用户受此影响而增加对淘宝的访问和操作，而购买行为并没有太大的涨幅可能是用户准备在双12当天集体剁手的缘故

（2）分析一天内用户每小时的行为

-- 2.分时用户行为
SELECT HOUR(time)+1 as hours,
SUM(case WHEN behavior = 'pv' THEN 1 ELSE 0 END) pv_count,
SUM(case WHEN behavior = 'buy' THEN 1 ELSE 0 END) buy_count,
sum(case WHEN behavior = 'cart' THEN 1 ELSE 0 END) cart_count,
sum(case WHEN behavior = 'fav' THEN 1 ELSE 0 END) fav_count
FROM tb2_copy1
GROUP BY hours
ORDER BY hours;

image

image

  • 一天内只有两个高峰期，分别为中午11~14点是和晚上10点~11点左右，对应了许多上班族中午和晚上的休息时间

3.不同商品种类的用户行为

  • 统计所有商品的购买次数，同时找到购买次数、浏览次数、收藏次数和加入购物车次数最多的商品。
SELECT times, COUNT(itemId)
FROM 
	(SELECT itemId, COUNT(userId) times
	FROM tb2_copy1
	WHERE behavior = 'buy'
	GROUP BY itemId) as t 
GROUP BY times
ORDER BY COUNT(*) DESC;

# 商品总数
SELECT COUNT(DISTINCT itemId) 
FROM tb2_copy1
WHERE behavior = 'buy';

image

image

  • 在本次统计的数据中，只购买一次的产品有57452种，购买两次的产品有9091种，本次分析的商品中用户购买的共有71870种商品，却没有出现购买用户数量非常集中的商品，购买一次的商品占到79.94%，说明商品售卖主要依靠长尾商品的累积效应，而非爆款商品的带动。
# 销量前20位的商品
SELECT
itemId,COUNT(*)
FROM 
	tb2_copy1
WHERE behavior = 'buy'
GROUP BY itemId
ORDER BY COUNT(*) desc LIMIT 20;

image

  • 列出销量前20位的商品，itemID为3122135的商品销量最高，为67次，那么是否浏览次数最高的商品销量也最高呢，对商品浏览量也进行了统计：
# 浏览前20的商品
SELECT itemId,COUNT(*) FROM tb2_copy1
WHERE behavior = 'pv' GROUP BY itemId
ORDER BY COUNT(*) desc LIMIT 20;

image

  • 我们看到浏览数最高的商品为812879，并且销量最高的商品浏览数都没有进到前20名，而浏览次数最高的商品也是如此，说明这些吸引用户更多注意力的商品没有很好的转化为实际销量，还有很大的优化空间。

image

  • 上图为收藏数前二十的商品，可以看到浏览量第一的商品812879收藏数为45

1569690545754

  • 在加入购物车次数最多的前20个商品中，可以看到销量第二的商品在购物车次数中排在第一位，而销量第二的商品也具有较多的收藏数（第八位）

4.基于RFM理论找出有价值的用户

我们首先统计出用户购买商品的最大值和最小值，数据源中没有相关的金额数据，因此通过 R 和 F 的数据对客户价值进行打分。

（1）R-最近购买时间

-- 创建子图 pay_R
#一般在业务中'2017-12-03'为当前时间，max(date)：用户最近一次购买时间
create view pay_B as
select userId, 
      DATEDIFF('2017-12-03',max(date)) as B
from tb2_copy1 where behavior='buy' GROUP BY userId;

SELECT * FROM pay_B;

# 划分标准
CREATE VIEW R AS
SELECT userId, 
(CASE WHEN B <= 2 THEN 5 
WHEN B <=3  THEN 4
WHEN B <=4  THEN 3
WHEN B <=6 THEN 2
ELSE 1 END) AS R FROM pay_B
ORDER BY R DESC;

image

（2）F-消费频率

-- 创建子图 pay_C
CREATE VIEW pay_C AS
SELECT userId, count(*) A
FROM tb2_copy1
WHERE behavior = 'buy'
GROUP BY userId;

SELECT * FROM pay_C;

# F等级划分

CREATE VIEW F AS
SELECT userId,
(CASE WHEN A <= 2 THEN 1 
WHEN A <= 4 THEN 2
WHEN A <= 8 THEN 3
WHEN A <= 10 THEN 4
ELSE 5 END) AS F
FROM pay_C
ORDER BY F DESC;

image

（3）建立RFM模型

#1.R平均值：3.9325
SELECT AVG(R) FROM r;  

#2.F平均值：1.6622
SELECT AVG(F) FROM f;

#3.用户八大类等级划分，由于该数据没有M值，故只建立了4个分类
CREATE view 客户类型视图 AS
SELECT userId, 
	(case when R >3.9325 AND F > 1.6622 THEN '价值客户'
	 WHEN R >3.9325 AND F <= 1.6622 THEN '发展客户'
	 WHEN R <= 3.9325 AND F > 1.6622 then '保持客户'
	 WHEN R <= 3.9325 AND F <= 1.6622 then '潜在/挽留客户' end) as 客户类型 FROM 
(SELECT f.userId,R,F FROM f JOIN r ON f.userId = r.userId) as t;

image

-- 查看各类型客户分布
SELECT 
	客户类型, COUNT(*)
FROM 
	客户类型视图
GROUP BY 客户类型;

image

image

  • 价值客户：近期交易的高频率用户占比35%，高价值客户采取一些客户关怀活动，频率不要太过频繁，这部分是平台忠实客户做好维护；
  • 发展客户：近期交易的低频率用户占比33%，具有巨大的潜力推荐平时常浏览的同类商品，提高用户购物频率，转化成忠诚用户；
  • 保持客户：无近期交易的高频率用户占比7%，近期没有消费过，有可能发展为流失的重要客户，需要做好客户的挽回，通过发一些电子优惠券之类的营销活动吸引用户回来；
  • 潜在/挽留客户：无近期交易的低频率用户占比25%，这部分客户很有可能是已经或者即将流失的客户，针对这部分客户找到流失原因，并根据联系方式，尝试联系问其不满意的地方，对有相似特征的群体进行预警，针对其原因进行改善。

五. 结论与建议

本文分析了淘宝用户行为数据共200万条，从四个不同角度提出业务问题，使用AARRR模型和RFM模型分析数据给出如下结论和建议。

1.通过AARRR模型分析用户使用的各个环节

1）获取用户

由于数据中没有给出每个用户第一次的登陆的时间，我们暂且把浏览行为视为用户的获取。

2）激活用户

用户行为包括点击、放进购物车、收藏以及购买。由于收藏和加入购物车都为浏览和购买阶段之间确定购买意向的用户行为，且不分先后顺序，因此将其算作一个阶段。

从浏览到有购买意向只有5.5%的转化率，当然有一部分用户是直接购买，但也说明大多数用户以浏览页面为主而购买转化较少，此处为转化漏斗中需要改善和提高的环节。

针对这一环节改善转化率的建议有：

  • 优化电商平台的搜索匹配度和推荐策略，主动根据用户喜好推荐相关的商品，优化商品搜索的准确度和聚合能力，对搜索结果排序优先级进行优化。
  • 在商品详情页的展示上突出用户关注的重点信息，精简信息流的呈现方式，减少用户寻找信息的成本
  • 优化加入购物车和收藏按键的触达，用户在滑屏时也能方便触达，增加功能使用的次数。

3）提高留存

淘宝APP的留存相对而言较为稳定，让用户保持使用淘宝电商平台的频率相对而言更加重要。

4）增加收入

使用APP的用户中有61%的付费用户，付费转化率相当高。而且用户的复购率也达到了66.1%，根据《精益数据分析》中的理论，如果年重复购买率达到60%及以上，则说明公司进入忠诚度模式，应将经营中心放在客户忠诚度上，即鼓励忠诚客户更加频繁地消费。

5）用户推荐

淘宝本身用户基数庞大，知名度高，个人认为在一二线城市的用户基本已经达到饱和，传播工作需要针对三四线城市的渠道下沉，在这些地区针对用户价格敏感度高的特性开展类似拼多多的拼团转发和打折促销活动，扩大这部分用户的使用率。

2.研究用户在不同时间尺度下的行为规律，找到用户在不同时间周期下的活跃规律

一个月中的消费活动在平时以一周为周期进行波动，而双十二促销期间各项指标达到高峰。一周中的高峰期在周末，符合上班族作息时间中的空闲时期。而平时一天中有两个高峰期，中午12点左右和晚十点左右，双十二期间由于活动时间的关系凌晨的销量最高。

针对高峰期进行营销活动收益最高，此时使用人数最多，活动容易触达用户，营销活动的形式可以通过促销、拼团等形式进行。

3.找到用户对不同种类商品的偏好，找到针对不同商品的营销策略

商品售卖主要依靠长尾商品的累积效应，而非爆款商品的带动。销量最高的商品浏览数都没有进到前20名，而浏览次数最高的商品也是如此。说明这些吸引用户更多注意力的商品没有很好的转化为实际销量。

针对浏览量高而销量不高的这部分商品，需要提高的是用户从点击进入商品详情页到最终购买的体验。作为商家端可以从以下几个方面提高销售额：

  • 商品详情页的实际价格是否相比展示价格偏差过大，有的商家为了吸引用户点击在商品展示页投放的价格具有较强吸引力，但实际价格偏高，在用户心中反而引起反感
  • 详情页的信息流展示是否合理，是否将用户最想看到的部分置于容易看到的位置，便于信息的获取
  • 优化商品展示的形式，利用视频等方式给用户更直观的感受，提高照片的美观程度
  • 评论区评价管理，尤其对于差评区的用户反馈进行认真对待，提高自身服务质量

4.通过RFM模型找出最具价值的核心付费用户群，对这部分用户的行为进行分析

R和F评分都很高的用户是体系中的最有价值用户，需要重点关注。并且活动投放时需谨慎对待，不要引起用户反感。

对于R值为4而F值为0的用户，用户粘性不强而消费时间间隔较短，运营活动可以重点针对这部分用户，提高用户使用产品的频率，可以通过拼团打折、积分兑换等活动唤起用户注意力。

CATALOG
    FEATURED TAGS
    Reprint Linux Hadoop SQL Git Python Django Data analysis
    FRIENDS