基于MySQL+Tableau的淘宝用户行为分析

2023-06-14 0 205

分析目录如下:

基于MySQL+Tableau的淘宝用户行为分析

一、分析背景及目的

随着移动互联网多年的发展,移动互联网不再依靠红利来经营和发展业务。对于电商企业的竞争愈来愈激烈,电商行业逐渐转变为精细化运营,结合市场、渠道、用户行为等数据分析,对用户展开有针对性的运营活动,提供个性化、差异化的运营策略,以实现运营目的的行为。

本文将使用 SQL 对 2017 年 11 月 25 日- 2017 年 12 月 3 日的淘宝用户行为数据进行分析,可发现其中存在的业务问题,并提供针对性的运营策略。

二、理解数据

UserBehavior是阿里巴巴提供的一个淘宝用户行为数据集,用于隐式反馈推荐问题的研究。
User Behavior Data from Taobao for Recommendation-数据集-阿里云天池tianchi.aliyun.com/dataset/dataDetail?dataId=649&userId=1基于MySQL+Tableau的淘宝用户行为分析
该数据集包含了 2017 年 11 月 25 日至 2017 年 12 月 3 日之间,约一百万随机用户的所有行为(行为包括点击、购买、加入购物车、收藏)。数据集的组织形式和 MovieLens-20M 类似,即数据集的每一行表示一条用户行为,由用户ID、商品ID、商品类目ID、行为类型和时间戳组成,并以逗号分隔。关于数据集中每一列的详细描述如下:
基于MySQL+Tableau的淘宝用户行为分析
数据集各字段及含义

2、数据大小说明

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

基于MySQL+Tableau的淘宝用户行为分析
数据大小说明

1、分析框架

;最后,从用户维度对用户行为差异,以及用户价值进行分析。

基于MySQL+Tableau的淘宝用户行为分析

2、 提出相关问题

根据以上的分析框架内容,提出以下相关问题:

(1)整体情况

首先,从页面访问量 PV、独立访客数UV、日新增用户数、新用户占比、平均访问深度(PV/UV)、日均 PV 和日均 UV、跳出率这些流量指标进行分析;其次,从复购率、回购率、用户行为转化率、购买转化率这几个转化指标进行分析,分析用户从点击->加入购物车->收藏->购买,各环节转化率如何?找出转化率最低的环节,并定位问题,找出原因,来优化转化路径。

(2)基于时间维度了解用户的行为习惯

可以观察每天用户的四种行为的变化趋势、以及每小时这四种行为的变化趋势,找出哪些日期和时间段是用户最活跃的时候,可以根据用户的活跃程度来制定不同的营销策略。

(3)基于商品维度分析用户的行为习惯

对浏览量、购买量前 10 的商品类目进行分析,找出热搜商品和热销商品。

(4) 基于用户维度分析用户的行为差异和用户价值

首先,分析出购买率前 10 的用户和购买率最后 10 名用户的四种行为特征,观察两类人群在四种行为上是否存在较大的差异;其次,基于 RFM 模型找出有价值的用户。

1、分析工具

分析工具:Python + Navicat + MySQL + Tableau

首先使用 Python 截取 300 万条淘宝用户行为数据,接下来在 MySQL 环境中使用 SQL 语句对数据进行清洗和查询,并结合 Tableau 绘制可视化图表。

(1)使用 ETL 工具 kettle 导数

由于 UserBehavior 数据集达到了1亿多条,如果单纯通过 Navicat 工具将数据导入 MySQL 数据库中,将会变的非常慢,所以可以使用 ETL 工具 kettle 进行数据的导入,这种方式可有效的提高导数效率。由于我的 kettle 工具有点小问题,这里就不使用这个工具来导数。

(2)使用 Python 导数

SQL 数据库中。

import pandas as pd from sqlalchemy import create_engine df = pd.read_csv(./2017/UserBehavior.csv,header=None,iterator=True,encoding=utf-8) head_data = df.get_chunk(100150807) # 读入全部数据 # 加入列名 head_data.columns = [user_id,items_id,category_id,behavior,timestamp] # 打乱数据框的顺序 head_data = head_data.reindex(np.random.permutation(head_data.index)) # 截取300万条数据来做分析 userbehavior = head_data[:3000000] # engine = create_engine(dialect+driver://username:password@host:port/database) # dialect — 数据库类型、driver — 数据库驱动选择、username — 数据库用户名、password — 用户密码 # host 服务器地址、port 端口、database 数据库 engine = create_engine(“mysql+pymysql://root:rootXXXX@localhost:3306/zhihu?charset=utf8”) userbehavior.to_sql(name=userbehavior, con=engine, if_exists=append,index=False) # index_label=id

3、数据预处理

(1)数据类型修改

导入的数据中用户ID、商品ID、商品所属类目ID都变成了整型,需要转换为字符串类型。

ALTER TABLE userbehavior MODIFY user_id VARCHAR(8); ALTER TABLE userbehavior MODIFY items_id VARCHAR(8); ALTER TABLE userbehavior MODIFY category_id VARCHAR(8);

(2)重复值检查

该数据表中存在很多用户id一样的记录,但不影响分析,因为一个用户可能在同一时段或不同时段出现重复浏览商品或购买商品等的行为,因此这里就不对此部分的数据进行处理。

(3)一致性处理

由于 timestamp 列为时间戳格式,我们需要使用 from_unixtime() 函数将数据转换为日期格式,为了方便后期的分析,可以添加一列数据,将时间戳格式转换为小时列。

# 添加两个字段 ALTER TABLE userbehavior ADD date VARCHAR(10); ALTER TABLE userbehavior ADD hour VARCHAR(4); # 将时间戳转换为日期格式 UPDATE userbehavior SET date=FROM_UNIXTIME(timestamp,%Y-%m-%d); # 将时间戳转换为小时格式 UPDATE userbehavior SET hour=SUBSTR(FROM_UNIXTIME(timestamp,%Y-%m-%d-%H),2); # 删除timestamp字段 ALTER TABLE userbehavior DROP timestamp;

(4)数据过滤

通过按日期进行排序,可以发现,数据集中存在很多异常时间,故需要将这部分时间剔除掉。

SELECT date FROM userbehavior WHERE date IS NOT NULL ORDER BY date;
基于MySQL+Tableau的淘宝用户行为分析

因为数据是 2017 年 11 月 25 日- 2017 年 12 月 3 日之间的数据,所以我们需要将异常时间的数据清洗掉。这里采用的是新创建一个 user 表,将查询得到的数据插入新表中。

CREATE TABLE user SELECT * FROM userbehavior WHERE date BETWEEN 2017-11-25 AND 2017-12-03

完成数据清洗后的数据结果如下所示:

基于MySQL+Tableau的淘宝用户行为分析
完成数据清洗后的数据

五、分析过程

1、整体情况

(1)PV、UV和平均访问深度

总访问量(Page View,简称 PV),指用户访问页面的总数,用户每访问一个网页,就算一个访问量,同一个页面刷新一次也算一个访问;

访客数(Unique Visitor,简称 UV),一台电脑为一个独立访客;

平均访问深度,也称为平均访问量,指用户每次浏览的页面的平均值,即平均每个 UV 访问了多少个 PV。
SELECT a.pv AS 总访问量, a.uv AS 总访问用户数, a.pv/a.uv AS 平均访问深度 FROM (SELECT COUNT(behavior) AS pv, (SELECT COUNT(DISTINCT user_id) FROM user) AS uv FROM user WHERE behavior=pv) AS a
基于MySQL+Tableau的淘宝用户行为分析

从整体数据来看,该 APP 在这一周内的总访问次数为 2684991 次,总访问用户数为 801942人,平均访问深度为 3.3,即每个人一周内大概会访问 3 个页面。

(2)日新增用户数和新用户占比

日新增用户是指客户端首次访问页面的用户数,这里用最小日期当做用户首次访问APP的日期,将新增用户数和总用户数对比就是新用户占比。

CREATE VIEW re_view AS — 视图 SELECT user_id, date, MIN(date) OVER(PARTITION BY user_id) AS first_date FROM user SELECT a.date AS 日期, a.total AS 总访客数, b.new AS 日新增访客数, CONCAT(ROUND((b.new/a.total)*100,2),%) AS 新访客占比 FROM (SELECT date, COUNT(DISTINCT user_id) AS total FROM user GROUP BY date) AS a LEFT JOIN (SELECT date, COUNT(DISTINCT user_id) AS new FROM re_view WHERE date=first_date GROUP BY date) AS b ON a.date=b.date GROUP BY a.date
基于MySQL+Tableau的淘宝用户行为分析

从上图可以看出,日新增用户数是不断在下降,在12月2日出现轻微的上升,但日新增用户数占比是在不断下降的,表明这段时间的日活跃用户大部分来自留存用户。

(3)跳出率

跳出率,也叫跳失率,就是只浏览了一个页面就离开的访问次数除以该页面的全部访问次数,可以反映页面内容受欢迎的程度,跳失率越大,页面内容越需要调整。

SELECT COUNT(a.ct) AS 只浏览一次的访问总数, (SELECT COUNT(behavior) FROM user WHERE behavior=pv) AS 总访问次数, CONCAT(ROUND((COUNT(a.ct)/(SELECT COUNT(behavior) FROM user WHERE behavior=pv))*100,2),%) AS 跳出率 FROM (SELECT user_id,COUNT(behavior) AS ct FROM user WHERE behavior=pv GROUP BY user_id HAVING COUNT(behavior)<=1) AS a
基于MySQL+Tableau的淘宝用户行为分析

从上图可以看出,用户跳失率为 7.88%,但由于每个平台所定义的口径不一样,所以暂时无法衡量这个跳失率算高还是低。用户跳出的原因有很多,这里也可以使用“人-货-场“的逻辑来进行思考,影响跳失率的影响有哪些。

(4)日均PV和日均UV

以天为单位展示商品的浏览量和用户数的变化趋势
SELECT date, SUM(IF(behavior=pv,1,0)) AS PV, COUNT(DISTINCT user_id) AS UV FROM user GROUP BY date ORDER BY date;
基于MySQL+Tableau的淘宝用户行为分析
日均 PV 和日均 UV

从上图可以看出,PV、UV 这两个指标的变化趋势几乎保持一致。在 2017 年 11 月 25 日-12 月 1 日之间,都是较为稳定的,而在 2017 年 12 月 2 日这天开始,PV 和 UV 都不断增加。可以就此提出假设:

假设 1:非工作日的 PV 和 UV 会得到大幅度提升;验证假设:通过查看日历发现,11 月 25 日和 11 月 26 日也是非工作日,但 PV 和 UV 并没有得到大幅度的提升,所以假设 1 不成立。 假设 2:可能是大促的影响;验证假设:12 月 2 日和 12 月 3 日距离“双十二”活动较近,可能是双 12 活动的预热阶段,吸引了一部分新老用户。以小时为单位展示商品的浏览量和用户数的变化趋势
SELECT hour, SUM(IF(behavior=pv,1,0)) AS PV, COUNT(DISTINCT user_id) AS UV FROM `user` GROUP BY hour ORDER BY hour
基于MySQL+Tableau的淘宝用户行为分析

上图显示各时间段的 PV 和 UV 的变化趋势。用户在每天的 1 点- 6 点的活跃度较低,符合大多数人的作息时间;在 7 点用户访问量和访客数开始慢慢得到回升,在 10 点-17 点之间用户的活跃度较为平稳,在 18 点用户开始活跃,且在 21 点达到了一天中的最高值。

从上图可以看出,用户最活跃的时段出现在 20:00-23:00 之间,说明这个时间段是一个黄金时间段,适合加大产品的宣传。

(5)复购率

复购率,是指在某段时间内产生二次或二次以上购买的用户占购买用户的总数。

SELECT COUNT(DISTINCT a.user_id) AS 总购买用户数, SUM(IF(a.ct>=2,1,0)) AS 购买两次以上的用户数, CONCAT(ROUND((SUM(IF(a.ct>=2,1,0))/COUNT(DISTINCT a.user_id))*100,2),%) AS 复购率 FROM (SELECT user_id,COUNT(behavior) AS ct FROM user WHERE behavior=buy GROUP BY user_id) AS a
基于MySQL+Tableau的淘宝用户行为分析

从上图可以看出,用户的复购率为 6.37%,也就是说,100个用户,仅有6个用户会选择购买多种商品。结合下图来看,用户在2017年11月25日-12月3日之间的复购率几乎处于稳定的状态,但在12月2日出现轻微的下降,但在12月3日又得到回升。

关于用户复购率出现变化的情况,还需要进一步进行分析。可以采用逻辑树分析方法,先排除由于数据准确性造成的原因后,可以从内外部因素去考虑。

基于MySQL+Tableau的淘宝用户行为分析

(6)回购率

回购用户是指在本月购买,下个月依然会回购的用户

SELECT SUBSTR(a.date,1,7) AS 日期, COUNT(DISTINCT a.user_id) AS 当月购买用户数, COUNT(DISTINCT b.user_id) AS 回购用户数, CONCAT(ROUND((COUNT(DISTINCT b.user_id)/COUNT(DISTINCT a.user_id))*100,2),%) AS 回购率 FROM (SELECT user_id, date FROM user WHERE behavior=buy GROUP BY date,user_id) AS a LEFT JOIN (SELECT user_id, date FROM user WHERE behavior=buy GROUP BY date,user_id) AS b ON a.user_id=b.user_id AND SUBSTR(a.date,6,2)=SUBSTR(b.date,6,2)1 GROUP BY DATE_FORMAT(a.date,%Y-%m);
基于MySQL+Tableau的淘宝用户行为分析

(7)用户行为转化漏斗率

用户消费行为转化分析可以采用漏斗图分析方法,漏斗图用于网站中某些关键路径的转化率的分析,不仅能显示用户从进入网站到实现购买的最终转化率,同时还可以展示整个关键路径中每一步的转化率。

漏斗分析是通过不同层次分解从而找到转化的逻辑。由于收藏和加入购物车没有先后之分,可以放在一起,作为加入购物车的行为一起进行分析。

SELECT behavior AS 用户行为类型, COUNT(behavior) AS 用户行为次数 FROM `user` GROUP BY behavior ORDER BY 用户行为次数 DESC

绘制出用户行为转化的漏斗图

基于MySQL+Tableau的淘宝用户行为分析

从用户的行为转化漏斗图来看,用户在浏览商品后,进入到加入购物车或收藏商品环节的比例为 9.40%,而在加入购物环节/收藏商品后进入购买商品的转化率为 24.11%。相对于第二环节来说,第一环节的转化率较低,这表明用户在浏览商品后,却很少选择加入购物车或收藏商品,说明用户对该商品并不是很满意;而在第二环节中,表明在加入购物车或收藏商品的行为中,大概会有四分之一将会被购买。

(8)用户购买行为转化率

SELECT behavior AS 用户行为类型, COUNT(DISTINCT user_id) AS 用户数 FROM user GROUP BY behavior ORDER BY 用户数 DESC
基于MySQL+Tableau的淘宝用户行为分析

这是用户从访问商品、加入购物车/收藏商品以及最终购买整个过程的漏斗图。

发现问题节点:在浏览商品到加入购物车/收藏商品的过程中,用户转化率急剧将至26.31%;问题分析:在确定问题节点为“加入购物车/收藏商品”后,开始分析该页面的数据;提出可能存在问题的原因,比如哪个渠道过来的用户的购买转化率低,这些用户的用户画像是什么等等?;问题拆解:根据可能存在的问题,进行拆解;解决问题:收集相关数据进行验证。

2、基于时间维度了解用户的行为习惯

(1)按日分析用户的行为习惯

SELECT date, SUM(IF(behavior=pv,1,0)) AS 访问量, SUM(IF(behavior=fav,1,0)) AS 收藏量, SUM(IF(behavior=cart,1,0)) AS 加入购物车量, SUM(IF(behavior=buy,1,0)) AS 购买量 FROM user GROUP BY date ORDER BY date;
基于MySQL+Tableau的淘宝用户行为分析

从以上数据来看,从11月25日-12月1日之间,各项指标都是比较平稳,而在12月2日-3日之间急剧上升,并达到了最大值。分析和前面的日均 PV、UV 的分析一致,可能是受双十二预热活动的影响,导致用户数急剧上升。

(2)按小时分析用户的行为习惯

SELECT hour, SUM(IF(behavior=pv,1,0)) AS 访问量, SUM(IF(behavior=fav,1,0)) AS 收藏量, SUM(IF(behavior=cart,1,0)) AS 加入购物车量, SUM(IF(behavior=buy,1,0)) AS 购买量 FROM user GROUP BY hour ORDER BY hour
基于MySQL+Tableau的淘宝用户行为分析

上图是2017年11月25日-12月3日之间用户每小时的四种行为变化。从上图看出,各项指标在0点-6点之间都是最低的,符合人们的作息时间。而在6点开始缓慢的上升,在早上10点-17点之间出现了第一个高峰期,在晚上20点-23点之间出现第二个高峰期。促销活动或商品推广活动可以在这两个高峰期进行重点投放。

3、基于商品维度分析用户的行为习惯

(1)找出浏览量最高的前 10 种商品类目(热搜商品)

SELECT category_id,COUNT(behavior) AS 商品浏览量 FROM user WHERE behavior=pv GROUP BY category_id ORDER BY 商品浏览量 DESC LIMIT 10;
基于MySQL+Tableau的淘宝用户行为分析

从图中可以看出,商品类目为 4756105 的商品,浏览量为134195次,其次为商品类目ID为2355072的商品,为94661次。前三排热搜商品类目分别为4756105、2355072、4145813,这些商品的浏览量较高,可能是商品的外观或商品的价格吸引了用户,但是否购买量高,可以进一步进行分析。

(2)找出购买量最高的前 10 种商品类目(热销商品)

SELECT category_id,COUNT(behavior) AS 商品购买量 FROM user WHERE behavior=buy GROUP BY category_id ORDER BY 商品购买量 DESC LIMIT 10;
基于MySQL+Tableau的淘宝用户行为分析

从购买量前 10 种商品类目来看,热搜商品排名前三的商品类目ID分别为1464116、2735466、4145813。从中也可以看出,浏览量高的商品,购买量并不高,说明热搜商品并没有引起用户的购买兴趣,无法很好的转化为实际的销量。

这里可以结合前面的漏斗图分析,分析是哪个环节出了问题,再针对具体环节进行分析和优化。

(3)找出购买率最高的前 10 种商品类目

SELECT a.category_id, a.点击次数, a.收藏次数, a.加入购物车次数, a.购买次数, CONCAT(ROUND((SUM(a.购买次数)/a.行为总数)*100,2),%) AS 购买率 FROM (SELECT category_id, COUNT(behavior) AS 行为总数, SUM(IF(behavior=pv,1,0)) AS 点击次数, SUM(IF(behavior=fav,1,0)) AS 收藏次数, SUM(IF(behavior=cart,1,0)) AS 加入购物车次数, SUM(IF(behavior=buy,1,0)) AS 购买次数 FROM user GROUP BY category_id) AS a GROUP BY category_id ORDER BY 购买率 DESC LIMIT 10;
基于MySQL+Tableau的淘宝用户行为分析

从上图可知,ID为 1781126 的商品类目购买率最高,明显比其他的商品类目高出很多,接下来单独针对该商品类目分析用户的四种行为。

SELECT COUNT(behavior) AS 行为总数, SUM(IF(behavior=pv,1,0)) AS 点击次数, SUM(IF(behavior=fav,1,0)) AS 收藏次数, SUM(IF(behavior=cart,1,0)) AS 加入购物车次数, SUM(IF(behavior=buy,1,0)) AS 购买次数 FROM user WHERE category_id =1781126
基于MySQL+Tableau的淘宝用户行为分析

从上图可以看出,该商品的点击次数只有一次,但购买次数却出现了 32 次。结合具体的用户ID发现,只有一位用户浏览了该商品,而其他的用户是直接购买该商品,表明这商品受大部分用户的喜欢,用户不需要浏览该商品,就选择了直接购买。

基于MySQL+Tableau的淘宝用户行为分析

4、基于用户维度分析用户的行为差异和用户价值

(1)找出购买率高的前 10 名用户

购买率=购买次数/(点击次数+收藏次数+加入购物车+购买次数)

SELECT a.user_id, a.items_id, a.点击次数, a.收藏次数, a.加入购物车次数, a.购买次数, CONCAT(ROUND((SUM(a.购买次数)/a.行为总数)*100,2),%) AS 购买率 FROM (SELECT user_id, items_id, COUNT(behavior) AS 行为总数, SUM(IF(behavior=pv,1,0)) AS 点击次数, SUM(IF(behavior=fav,1,0)) AS 收藏次数, SUM(IF(behavior=cart,1,0)) AS 加入购物车次数, SUM(IF(behavior=buy,1,0)) AS 购买次数 FROM user GROUP BY user_id) AS a GROUP BY user_id ORDER BY 购买率 DESC LIMIT 10;
基于MySQL+Tableau的淘宝用户行为分析
购买率前 10 名用户

从上图可以看出,购买率前 10 名的用户,点击次数都比较高,且用户在浏览商品后,很少收藏和加入购物车,而是直接进行购买,这类用户可能是根据自身的需求进行购买,看见合适的商品就直接购买,并不是会受广告等活动的影响。

(2)找出购买率最差的 10 名用户

SELECT a.user_id, a.items_id, a.点击次数, a.收藏次数, a.加入购物车次数, a.购买次数, CONCAT(ROUND((SUM(a.购买次数)/a.行为总数)*100,2),%) AS 购买率 FROM (SELECT user_id, items_id, COUNT(behavior) AS 行为总数, SUM(IF(behavior=pv,1,0)) AS 点击次数, SUM(IF(behavior=fav,1,0)) AS 收藏次数, SUM(IF(behavior=cart,1,0)) AS 加入购物车次数, SUM(IF(behavior=buy,1,0)) AS 购买次数 FROM user GROUP BY user_id) AS a GROUP BY user_id ORDER BY 购买率 LIMIT 10;
基于MySQL+Tableau的淘宝用户行为分析
购买率最差的 10 名用户

从上图可以看出,购买率低的人群可以分为两类:

低点击人群。这类人群在点击了商品之后,并没有产生加入购物车或收藏商品的行为,说明这些商品并没有引起用户的注意;高点击人群。最高点击商品的有高达22次,并把商品加入购物车,但后续没有继续进行购买,可能商品没有优惠或者该用户在数据采集周期内并没有想要购买的欲望等等都有可能,但大部分用户都是浏览过后并没有发生购买行为,可能是受商品价格或商品内容等的影响,用户没有做出购买行为。

(3)基于 RFM 模型找出有价值的用户

这部分内容可以看我之前发过的文章

由于不同用户对公司带来的以是不一样的,根据二八定律,我们可以得知,20% 的头部用户可以带来 80% 的收益,所以通过对用户进行分层,找到最优质价值的用户群,可以针对这部分用户实施精准化营销,也可以有效降低这部分用户的流失。

RFM 模型是衡量客户价值和客户创利能力的重要工具,是按照 R(Recency-近度)、F(Frequency-频度)、M(Monetary-额度)三个维度进行细分客户群体。

近度R(Recency):R代表客户最近的活跃时间距离数据采集点的时间距离。R越大,表示客户越久未发生交易,R越小,表示客户越近有交易发生。R越大则客户越困难会“沉睡”,流失的可能性越大。在这部分客户中,可能有些优质客户,值得公司通过一定的营销手段进行激活。

频度F(Frequency):F代表客户过去某段时间内(如一年、半年、30天等)的活跃频率。F越大,则表示客户同本公司的交易越频繁,不仅仅给公司带来人气,也带来稳定的现金流,是非常忠诚的客户;F越小,则表示客户不够活跃,且可能是竞争对手的常客。针对F较小、且消费额较大的客户,需要推出一定的竞争策略,将这批客户从竞争对手中争取过来。

额度M(Monetary):表示客户每次消费金额的多少。可以用最近一次消费金额,也可以用过去的平均消费金额,根据分析的目的不同,可以有不同的标识方法。一般来讲,单次交易金额较大的客户,支付能力强,价格敏感度低,是较为优质的客户,而每次交易金额很小的客户,可能在支付能力和支付意愿上较低。当然,也不算绝对的。

tips:这三个指标根据业务的不同,计算口径也会不一样,要根据实际业务情况来定义。

由于这部分数据没有明确给出用户的购买金额,故这里按照 RFM 模型的计算流程,仅采用 R 和 F 来对用户群体进行细分。

(1)计算 R、F 值

近度 R(Recency)的计算:由于该数据集是2017年11月25日至2017年12月3日之间的数据集,时间有点久远,这里的近度R将使用这个数据数据集最大的时间减去用户最近一次购买的时间。计算频度 F(Frequency):针对每位用户在这段时间的购买频率进行计算。
CREATE view RF_model AS –创建视图,方便后续的分析 SELECT user_id, DATEDIFF(2017-12-03,MAX(date)) AS R, COUNT(behavior) AS F FROM user WHERE behavior=buy GROUP BY user_id ORDER BY user_id
基于MySQL+Tableau的淘宝用户行为分析
计算得到的R、F值

(2)根据业务标准,给 R、F 打分

通过计算,R 的最小时间间隔为 0,最大时间间隔为 8;F 的最近一段时间购买频度最小为 1,最大的购买频度为 7,可以根据这两个指标指定一个打分表(最好的方式根据R和F对用户进行分组,找到每组里的最大、最小值作为打分标准)。

基于MySQL+Tableau的淘宝用户行为分析
打分表

根据打分表,给 R、F 打分

CREATE VIEW RF_score AS SELECT user_id, CASE WHEN R BETWEEN 0 AND 2 THEN 2 WHEN R BETWEEN 3 AND 5 THEN 1 WHEN R BETWEEN 6 AND 8 THEN 0 ELSE NULL END AS R_score, CASE WHEN F BETWEEN 0 AND 2 THEN 0 WHEN F BETWEEN 3 AND 5 THEN 1 WHEN F BETWEEN 6 AND 7 THEN 2 ELSE NULL END AS F_score FROM RF_model ORDER BY user_id
基于MySQL+Tableau的淘宝用户行为分析
R、F打分值

(3)计算 R、F 的平均值和标签

CREATE VIEW RF_class AS SELECT user_id, R_score AS R值打分, F_score AS F值打分, CASE WHEN R_score>(SELECT AVG(R_score) FROM RF_score) THEN ELSE END AS R_class, CASE WHEN F_score>(SELECT AVG(F_score) FROM RF_score) THEN ELSE END AS F_class FROM RF_score GROUP BY user_id
基于MySQL+Tableau的淘宝用户行为分析

(4)根据用户分类细则表,对用户进行分层

CREATE VIEW RF_label AS SELECT user_id, R值打分, F值打分, R_class, F_class, CASE WHEN R_class= AND F_class= THEN 价值用户 WHEN R_class= AND F_class= THEN 发展用户 WHEN R_class= AND F_class= THEN 保持用户 WHEN R_class= AND F_class= THEN 挽留用户 END AS label FROM RF_class GROUP BY user_id
基于MySQL+Tableau的淘宝用户行为分析

根据 RFM 模型分层结果,得出各用户群所占的比例:

SELECT a.label AS 用户类别, a.ct AS 用户数, a.ct/(SELECT COUNT(DISTINCT user_id) FROM RF_label) AS 用户占比 FROM (SELECT label, COUNT(DISTINCT user_id) AS ct FROM RF_label GROUP BY label) AS a GROUP BY a.label
基于MySQL+Tableau的淘宝用户行为分析

从上图我们可以看出,挽留用户占了大部分,为 62.79%,发展用户次之,为 36.65%,而价值用户占比仅为 0.34%,保持用户占比最低,仅为 0.22%。

六、结论与建议

本文分析了淘宝 2017 年 11 月 25 日至 2017 年 12 月 3 日之间的 300 万条用户行为数据,从用户的整体购物情况、用户行为路径等方面进行分析,结合分析结果,提出以下的建议:

(1)由用户购买行为转化可知,大部分用户在浏览了商品后,并没有产生加入购物车或收藏的行为,表明当前的产品对于用户的吸引力还不够,应需要进一步根据用户的用户画像,精准地为推荐用户感兴趣的商品;

(2)用户的活跃时段分别在早上 10:00-17:00 以及晚上的 20:00-23:00 之间,这个时间段是个黄金时间段,特别是在晚上的这个时间段,用户访问量和独立访客数都达到最大值,平台可以利用这个时间段投放广告,加大力度宣传产品;

(3)从热搜商品和热销商品上来看,热搜商品的购买率并不高,可以根据这部分用户进一步挖掘出用户弃购的原因,提高产品的购买率。在平台的主要投放渠道中,可以使用商品类目ID为 4756105、2355072、4145813、3607361、982926 这些热搜商品来引流,吸引更多的用户到平台来;在平台中推荐商品类目ID为 1464116、2735466、4145813、2885642 这四类热销商品,提高用户的购买量。而针对商品类目ID为 1781126 的商品,可以在引流或商品推荐页中投放,这类商品的购买率最高,深受用户的喜欢。

(4)根据用户价值分析,我们可以得到用户分层结果,可以根据每个用户群的特性,实现差异化营销:

较远,但消费频次较高,说明这类型的用户已经有一段时间没来消费了,对于这类用户可以采用邮件推送、短信推送、APP 推送等方式来唤醒用户,提高用户的粘性;发展用户。这类用户的特点是最近消费时间近,但消费频度低,对于这类用户可以适当的赠送优惠券或采取捆绑销售的方式来增加用户的消费频度;挽留用户。这类用户的最近消费时间较远,最近一段时间的消费频度较低,这类用户面临流失或已经流失的情况,应当采取挽留措施,如优惠券等方式召唤回这部分用户。

文中很多细节都需要慢慢补充和修改!

相关文章

发表评论
暂无评论
官方客服团队

为您解决烦忧 - 24小时在线 专业服务