戎易大数据 | 数据分析实操篇：基于MySQL和Tableau的淘宝用户购物行为数据分析

本文来源公众号“戎易大数据”，仅用于学术分享，侵权删，干货满满。

原文链接：数据分析实操篇：基于MySQL和Tableau的淘宝用户购物行为数据分析

1项目介绍

为提高平台GMV和实现精细化运营，本项目首先使用MySQL（实际上是用Navicat Premium连接了MySQL，方便数据导入）对来自某电商的数据集进行数据预处理，然后通过多维度拆解，从用户和商品两个大的角度分别进行分析，最后借助Tableau搭建仪表盘实现数据可视化。

2工具使用

Navicat、MySQL、Tableau

3理解数据

本数据集包含了2017年11月25日至2017年12月3日之间，有行为的约一百万随机用户的所有行为（行为包括点击、购买、加购、喜欢）。数据集的组织形式和MovieLens-20M类似，即数据集的每一行表示一条用户行为，由用户ID、商品ID、商品类目ID、行为类型和时间戳组成，并以逗号分隔。关于数据集中每一列的详细描述如下：

4数据处理

4.1导入数据

4.1.1命令提示符

在命令提示符中打开MySQL

4.1.2数据导入

打开Navicat，点击左上角连接MySQL，点击下一步

打开连接，右键新建数据库

双击数据库，点击右边有一行‘导入向导’，选择CSV文件，除了下图的这一步修改字段名称行为0，第一个数据行为1，都直接跳过就行，这里注意，最后一个数据行这个选项需要根据电脑水平量力而行，原数据有10000w条，我第一次的时候查询800w条的SQL电脑就会直接蓝屏，最后只用了700w条：

4.2数据预处理

4.2.1修改列名

ALTER TABLE userbehavior CHANGE f1 user_id INT,CHANGE f2 item_id INT,CHANGE f3 category_id INT,CHANGE f4 behavior_type VARCHAR ( 5 ),CHANGE f5 time_stamp INT;

4.2.2查找空值

SELECT
FROMuserbehaviorWHEREuser_id IS NULLOR item_id IS NULLOR category_id IS NULLOR behavior_type IS NULLOR time_stamp IS NULL;

没有返回值，说明不存在null值。

4.2.3重复值

思路是按照user_id和item_id和time_stamp分组，通过count()查看是否只有一条记录（因为一个用户在某一时间查看某个商品必定是唯一的）：

SELECTuser_id,item_id,time_stampFROMuserbehaviorGROUP BYuser_id,item_id,time_stampHAVINGcount(*)>1

出现了5条返回值。

下一步去重的思路是给表增加一列id，并设置为自增的主键，这样重复的记录会有很多个id值，只保留最大值即可：

ALTER TABLE userbehavior ADD id INT FIRST;ALTER TABLE userbehavior MODIFY id INT PRIMARY KEY auto_increment;

删除重复值：

DELETE userbehaviorFROMuserbehavior,(SELECTuser_id,item_id,time_stamp,max( id ) AS max_idFROMuserbehaviorGROUP BYuser_id,item_id,time_stampHAVINGcount(*) > 1) AS df1WHEREuserbehavior.user_id = df1.user_idAND userbehavior.item_id = df1.item_idAND userbehavior.time_stamp = df1.time_stampAND userbehavior.id < df1.max_id;

4.2.4处理异常值&字段调整

将超过时间范围的异常数据删除，即只保留2017-11-25 00:00:00至2017-12-03 23:59:59的数据。
为此我们将时间戳拆开方便后续使用，下面我新增了一列datetimes，数据类型是timestamp，（0）的意思是到秒为止不包括毫秒那些；另外from_unixtime（）有两个参数，第二个格式参数我没写，如果为空默认会使用%Y-%m-%d %H:%i:%s的格式：

ALTER TABLE userbehavior ADD datetimes TIMESTAMP ( 0 );UPDATE userbehaviorSET datetimes = from_unixtime( time_stamp );DELETEFROMuserbehaviorWHEREdatetimes > '2017-12-03 23:59:59'OR datetimes < '2017-11-25 00:00:00';

另外，考虑到后面需要对用户在不同时段做了什么进行分析，利用substring将具体的日期和小时提取出来新增两列：

ALTER TABLE userbehavior ADD dates CHAR ( 10 );ALTER TABLE userbehavior ADD hours CHAR ( 2 );UPDATE userbehaviorSET dates = substring( datetimes, 1, 10 ),hours = substring( datetimes, 12, 2 );

5数据分析

5.1整体思路

针对提高GMV，我将GMV拆解成浏览人数×转化率×客单价，这份数据集里里面没有消费金额信息，所以我们主要考虑浏览情况和转化情况，浏览情况主要就是PV/UV这些，我还延伸考虑了留存率；转化情况我想采用漏斗模型看各环节转化率，同时拆分时间看各时间段消费情况。
至于精细化运营，我的思路是从用户和商品两个角度进行分析，用户方面用RFM模型分层，产品方面做类似的特征分析，总体如图所示：

5.2具体实现

5.2.1获客情况

页面浏览量PV
独立访客数UV
浏览深度PV/UV

CREATE TABLE df_pv_uv ( dates CHAR ( 10 ), PV INT ( 9 ), UV INT ( 9 ), PVUV DECIMAL ( 10, 2 ) );INSERT INTO df_pv_uv SELECTdates,count(IF( behavior_type = 'pv', 1, NULL )) AS PV,count( DISTINCT user_id ) AS UV,round(( count( IF ( behavior_type = 'pv', 1, NULL ))/ count( DISTINCT user_id )), 2 ) AS 'PVUV'FROMuserbehaviorGROUP BYdates;

运行结果如下：

5.2.2用户留存

次日留存率：

CREATE TABLE df_retention_1( dates CHAR ( 10 ), retention_1 FLOAT );INSERT INTO df_retention_1SELECTub1.dates,count( ub2.user_id )/ count( ub1.user_id ) AS retention_1FROM( SELECT DISTINCT user_id, dates FROM userbehavior ) AS ub1LEFT JOIN ( SELECT DISTINCT user_id, dates FROM userbehavior ) AS ub2 ON ub2.user_id = ub1.user_idAND ub2.dates = date_add( ub1.dates, INTERVAL 1 DAY )GROUP BYub1.dates;

运行结果:

三日留存率（由于这份数据一共只有九天）：

CREATE TABLE df_retention_3( dates CHAR ( 10 ),retention_3 FLOAT );INSERT INTO df_retention_3SELECTub1.dates,count( ub2.user_id )/ count( ub1.user_id ) AS retention_3FROM( SELECT DISTINCT user_id, dates FROM userbehavior ) AS ub1LEFT JOIN ( SELECT DISTINCT user_id, dates FROM userbehavior ) AS ub2 ON ub2.user_id = ub1.user_idAND ub2.dates = date_add( ub1.dates, INTERVAL 3 DAY )GROUP BYub1.dates;

运行结果：

5.2.3用户行为

注意，用户行为类型共有四种，它们分别是

5.2.4用户转化率

CREATE VIEW user_behavior_total ASSELECTuser_id,item_id,count(IF( behavior_type = 'pv', 1, NULL )) AS PV,count(IF( behavior_type = 'fav', 1, NULL )) AS FAV,count(IF( behavior_type = 'cart', 1, NULL )) AS CART,count(IF( behavior_type = 'buy', 1, NULL )) AS BUYFROMuserbehaviorGROUP BYuser_id,item_id;

运行结果：

但事实上在这里我们不关心用户到底浏览了几次，是不是收藏了又取消然后再次收藏，我们只需要知道用户是否有过这种行为即可，因此接下来对用户行为进行归一标准化，有过这种行为我们记为1，没有记为0：

CREATE VIEW user_behavior_total_standard ASSELECTuser_id,item_id,IF( PV > 0, 1, 0 ) AS ifpv,IF( FAV > 0, 1, 0 ) AS iffav,IF( CART > 0, 1, 0 ) AS ifcart,IF( BUY > 0, 1, 0 ) AS ifbuyFROMuser_behavior_totalGROUP BYuser_id,item_id;

用concat函数把用户对商品的所有行为合并起来（就是把原来四列合并为一列）：

CREATE VIEW user_path ASSELECTuser_id,item_id,concat( ifpv, iffav, ifcart, ifbuy ) AS pathFROMuser_behavior_total_standard;

运行结果应该是这个样子的，path是1011就代表了某用户对某商品有过浏览，加入购物车，购买的行为：

这里用正则表达式进行筛选，为了方便查看，用case when语句加入了注释列：

CREATE VIEW user_path_num ASSELECTpath,CASE

WHEN path = 1101 THEN    '浏览-收藏-/-购买'    WHEN path = 1011 THEN    '浏览-/-加购-购买'    WHEN path = 1111 THEN    '浏览-收藏-加购-购买'    WHEN path = 1001 THEN    '浏览-/-/-购买'    WHEN path = 1010 THEN    '浏览-/-加购-/'    WHEN path = 1100 THEN    '浏览-收藏-/-/'    WHEN path = 1110 THEN    '浏览-收藏-加购-/'      ELSE '浏览-/-/-/'

END AS description,count(*) AS path_numFROMuser_pathWHEREpath REGEXP '^1'GROUP BYpath;

运行结果：

进行该部分的数据汇总，以漏斗图的方式进行分析：

CREATE TABLE df_buy_path( buy_path VARCHAR ( 55 ),buy_path_num INT ( 9 ) );
INSERT INTO df_buy_pathSELECT'浏览',sum( path_num ) AS buy_path_numFROMuser_path_num;
INSERT INTO df_buy_pathSELECT'浏览后收藏加购',sum(IF(path = 1101OR path = 1100OR path = 1010OR path = 1011OR path = 1110OR path = 1111,path_num,NULL)) AS buy_path_numFROMuser_path_num;
INSERT INTO df_buy_pathSELECT'浏览后收藏加购后购买',sum(IF( path = 1101 OR path = 1011 OR path = 1111, path_num, NULL )) AS buy_path_numFROMuser_path_num;

运行结果：

5.2.5用户定位

RFM模型示意图：

但由于数据集中没有消费金额，这里只采用前两个维度。

首先计算R值和F值（以视图的形式存储），由于RFM要求用户是消费过的，我们这里筛选出behavior_type为buy的用户，然后按照用户分组：

-- R计算CREATE VIEW c ASSELECTuser_id,max( dates ) AS 'last_buy_date'FROMuserbehaviorWHEREbehavior_type = 'buy'GROUP BYuser_id;
--F计算CREATE VIEW d ASSELECTuser_id,count( user_id ) AS 'buy_times'FROMuserbehaviorWHEREbehavior_type = 'buy'GROUP BYuser_id;

为了方便同时对两组数据进行处理，将他们合并成一个新表：

CREATE TABLE df_rfm_model( user_id INT ( 9 ),recency CHAR ( 10 ),frequency INT ( 9 ) );
INSERT INTO df_rfm_modelSELECTuser_id,last_buy_date,buy_timesFROMcJOIN d USING ( user_id );

运行结果：

对每个用户的recency和frequency这两个变量进行打分量化：

-- 量化RALTER TABLE df_rfm_model ADD r_score INT ( 9 );UPDATE df_rfm_modelSET r_score =CASE

WHEN recency = '2017-12-03' THEN 100    WHEN recency = '2017-12-02' OR recency = '2017-12-01' THEN 80    WHEN recency = '2017-11-30' OR recency = '2017-11-29' THEN 60    WHEN recency = '2017-11-28' OR recency = '2017-11-27' THEN 40    ELSE 20

END;
-- 量化FALTER TABLE df_rfm_model ADD f_score INT ( 9 );UPDATE df_rfm_modelSET f_score =CASE

WHEN frequency > 15 THEN 100    WHEN frequency BETWEEN 12 AND 14 THEN 90    WHEN frequency BETWEEN 9 AND 11 THEN 70    WHEN frequency BETWEEN 6 AND 8 THEN 50    WHEN frequency BETWEEN 3 AND 5 THEN 30    ELSE 10    END;

运行结果：

前面的RFM模型图，那里面各个坐标轴其实就是整体的平均值，比整体平均值高了就在坐标轴右侧（或上侧等等），所以给上一步的表加上两列，希望能表示r_score和f_score的平均值，然后对每一行作比较即可。

CREATE TABLE df_rfm_avg(user_id int(9),recency char(10),r_score int(9),avg_r DECIMAL(6,4),frequency int(9),f_score int(9),avg_f DECIMAL(6,4));
INSERT INTO df_rfm_avgSELECTe.user_id as user_id,recency,r_score,avg_r,frequency,f_score,avg_fFROM( SELECTuser_id,avg( r_score ) over () AS avg_r,avg( f_score ) over () AS avg_fFROM df_rfm_model ) AS eJOIN df_rfm_model USING ( user_id );

运行结果：

最后从两个维度把用户分成四类（经典case when），存储结果就好：

create table df_rfm_result(user_class varchar(5),user_class_num int(9));
insert into df_rfm_resultselectuser_class,count() as user_class_numfrom(select ,casewhen (f_score >= avg_f and r_score >= avg_r) then '价值用户'when (f_score >= avg_f and r_score < avg_r) then '保持用户'when (f_score < avg_f and r_score >= avg_r) then '发展用户'else '挽留用户'end as user_classfromdf_rfm_avg) as ggroup byuser_class;

运行结果：

5.2.6商品热度

统计热销TOP10的商品和品类：

-- 热门品类CREATE TABLE df_popular_category ( category_id INT ( 9 ), category_pv INT ( 9 ) );INSERT INTO df_popular_category SELECTcategory_id,count(IF( behavior_type = 'pv', 1, NULL )) AS category_pvFROMuserbehaviorGROUP BYcategory_idORDER BYcount(IF( behavior_type = 'pv', 1, NULL )) DESCLIMIT 10;

-- 热门商品CREATE TABLE df_popular_item ( item_id INT ( 9 ), item_pv INT ( 9 ) );INSERT INTO df_popular_item SELECTitem_id,count(IF( behavior_type = 'pv', 1, NULL )) AS item_pvFROMuserbehaviorGROUP BYitem_idORDER BYcount(IF( behavior_type = 'pv', 1, NULL )) DESCLIMIT 10;

运行结果：

热门品类

热门商品

5.2.7商品特征

从原表中根据category_id进行

分组，统计每个类别是商品被浏览、收藏、加入购物车、购买的次数及转化率：

CREATE TABLE df_category_conv_rate (category_id INT ( 9 ),PV INT ( 9 ),FAV INT ( 9 ),CART INT ( 9 ),BUY INT ( 9 ),category_conv_rate FLOAT);INSERT INTO df_category_conv_rateSELECTcategory_id,count(IF( behavior_type = 'pv', 1, NULL )) AS PV,count(IF( behavior_type = 'fav', 1, NULL )) AS FAV,count(IF( behavior_type = 'cart', 1, NULL )) AS CART,count(IF( behavior_type = 'buy', 1, NULL )) AS BUY,count(DISTINCTIF( behavior_type = 'buy', user_id, NULL ))/ count( DISTINCT user_id ) AS category_conv_rateFROMuserbehaviorGROUP BYcategory_idORDER BYcategory_conv_rate DESC;

运行结果:

6Tableau数据可视化与仪表盘搭建

6.1数据可视化与分析

6.1.1用户流量分析

第一个数据源是df_pv_uv，把PV和UV绘制成双轴图：

1.由于我们的数据集是从星期六开始，从理论上来讲周末的流量会大一些，统计周期的前两天和后两天的PV值刚好印证了这一特点。第二个周末的PV值要显著高于第一个周末，推测原因是由于进入12月商家为了“双十二”提前开启了预热活动，以此吸引用户。

2.虽然PV值有一定波动，但UV值基本不变，维持在25k左右，这表明热衷于购物的用户很大一部分都是同一批人，即有很大一部分都可能是老客户，一方面商家在做活动时可以有所偏向这部分群体，另一方面商家也需要调整手段来吸引更多新用户。

4.PV/UV记为浏览深度，一方面周末浏览深度的增加一定程度上表明商家的广告、优惠活动成功吸引到了用户，使得用户多次点击查看，另一方面理论上也不排除商家页面设计更加复杂，用户们需要花费更多时间查看的可能。

6.1.2用户留存分析

此数据源需要把df_retention_1和df_retention_3用dates连接起来：

1、留存率在12月之前一直稳定在80%左右，进入12月份后急剧上升，持续逼近100%，显著说明商家的活动成功吸引到了用户，为我们排除了之前页面设计更加复杂的推测。

2、本图中留存率迅速衰减的原因是由于数据集跨度不够导致的，但我们可以设想继续分析“双十二”活动附近几天的用户留存率变化在一定程度上评估某一次活动的效果，进而商家就能知道用户喜欢什么，进而调整自己的策划方案，实现用户的长期价值。

6.1.3用户行为分析

将数据集df_timeseries中的日期和小时设置一个分层结构，PV绘制堆积图，其余绘制折线图，然后设置双轴：

1.从波峰波谷来看，完全符合国民的日常作息，流量高峰出现在夜间8-10点，低峰在0-6点，同时购买量、收藏量、加购量呈现出明显的正相关，这也符合一般认知，商家需要做的是在高峰期保证平台正常运行以免给用户带来不好的体验感。

2.商家可以针对流量高峰采取行动，例如先是在高峰期采取更多推送，用户不但不反感还更有可能点击查看，然后采用限时限量优惠，比如每天九点限量发放满减优惠券以此实现收益扩大化。

6.1.4用户转化率分析

数据集是df_buy_path，这里把它制作成漏斗图：

1.从浏览到收藏加购来看，大多数用户在浏览后并没有进行收藏或加入购物车。需要分析用户在浏览时遇到的问题，例如产品展示不吸引人，或是促销信息不明确等等，商家需要考虑的是推出限时优惠或打折促销，激励用户尽快收藏和加入购物车，毕竟从之前的分析来看收藏加购和购买呈现出明显的正相关性。

2.从收藏加购到购买的转化率仅为0.51%，表明用户在收藏或加入购物车后，因价格、支付流程、物流问题等等因素未完成购买，建议商家分阶段进行A/B测试，尝试不同的页面设计、促销策略和用户体验优化，找到最佳方案。

6.1.5用户定位分析

用RFM模型对用户进行分类以圆环图展示，数据集是df_rfm_result：

1.这里我认为看整个平台的用户定位分析可能意义不大，更好的是看平台之间的对比或者是每个商家的用户分层状况，以此来评估这个商家或者平台的经营状况如何。

2.就商家的用户定位分析而言，针对不同的用户群体，他们可以指定不同的策略，比如针对价值用户要继续维持之前的服务不能松懈，针对发展用户，可以采取第二次购买优惠的手段来提高他们的购买频率，针对保持用户，属于一段时间没来的忠实用户，可以采取给这类用户单独发放优惠券的形式来吸引他们，最后对于挽留用户，应尽可能地采取措施挽回，整体上实现精细化运营。