Taobao电商用户行为分析

       通过阿里天池提供的2017年11月25日-2017年12月3日的用户行为数据，尝试分析得出对业务有用的优化建议。

一、数据来源

1、 数据集-阿里云天池 (aliyun.com)

2、网上搜集得来的数据SQL文件

二、理解数据

三、提出问题

1、各环节转化率如何？转化率低的原因？

2、用户访问峰值时段？

3、用户RFM分级？

4、转化率top10的商品品类、商品？

四、分析思路

五、数据预处理

1、数据整体认知

select count(distinct user_id) as 用户数,
count(distinct item_id) as 商品数,
count(distinct category_id) as 品类数 
from userbehavior;

+--------+--------+--------+
| 用户数 | 商品数 | 品类数 |
+--------+--------+--------+
|    983 |  64467 |   3128 |
+--------+--------+--------+
1 row in set (3.49 sec)

共统计983名用户，64467个商品，3128个品类。

2、重复值查询处理

select * from userbehavior
group by item_id,user_id,timestamp
having count(*)>1;

Empty set (0.00 sec)

无重复值

3、缺失值查询处理

select * from userbehavior
where user_id is null
or item_id is null
or category_id is null
or behavior_type is null
or timestamp is null;

Empty set (0.00 sec)

无缺失值

4、时间戳处理

alter table userbehavior add dates date;//添加一个date格式的dates(日)字段
update userbehavior set dates =from_unixtime(timestamp);//使用from_unixtime函数转换时间戳

alter table userbehavior add times time;//添加一个time格式的times(时)字段
update userbehavior set times =from_unixtime(timestamp);//使用from_unixtime函数转换时间戳

alter table userbehavior add datetimes datetime;//添加一个datetime格式的datetimes(日+时)字段
update userbehavior set datetimes =from_unixtime(timestamp);//使用from_unixtime函数转换时间戳

alter table userbehavior add weekdays varchar(20);//添加一个varchar格式的weekdays(星期)字段
update userbehavior set weekdays =weekday(datetimes)+1;

mysql> select dates,times,datetimes,weekdays from userbehavior limit 5;
+------------+----------+---------------------+----------+----------+----------+
| dates          | times       | datetimes                  | weekdays |
+------------+----------+---------------------+-----------+----------+----------+
| 2017-11-25 | 06:15:33 | 2017-11-25 06:15:33 | 6        |
| 2017-11-25 | 09:21:25 | 2017-11-25 09:21:25 | 6        |
| 2017-11-25 | 15:04:53 | 2017-11-25 15:04:53 | 6        |
| 2017-11-25 | 15:49:06 | 2017-11-25 15:49:06 | 6        |
| 2017-11-25 | 21:28:01 | 2017-11-25 21:28:01 | 6        |
+------------+----------+---------------------+----------+
5 rows in set (0.00 sec)

转换成功

5、异常值处理

删除超出11.25-12.3时间范围内的数据。

delete from userbehavior where dates<'2017-11-25' or dates>'2017-12-03';

Query OK, 44 rows affected (1.45 sec)

删除44行异常值。

六、模型分析

1、AIPL漏斗分析

AIPL模型是常见的营销模型之一，包含认知、兴趣、购买和忠诚四个方面。

A-Awareness 认知：pv

I-Interested 兴趣：cart,fav

P-Purchase 购买：buy

L-Loyalty 忠诚：复购（时间间隔较短，剔除考虑）

在PowerBI中导入MySQL数据源，并新建“浏览”、“收藏加购”、“下单”列。

浏览 = IF('taobao userbehavior'[behavior_type]="pv",1,0)
收藏加购 = IF('taobao userbehavior'[behavior_type]= "cart" || 'taobao userbehavior'[behavior_type]= "fav",1,0)
下单 = IF('taobao userbehavior'[behavior_type]="buy",1,0) 

绘制漏斗图如下

        如图所示，整体转化率2.3%，浏览-收藏加购转化率9.13%，收藏加购-下单转化率25.65%（远高于上一环节）。

2、RFM模型

RFM分析是根据客户活跃程度和交易金额的贡献，进行客户价值细分的一种方法。

R（Recency）：用户最近一次交易时间的间隔。

F（Frequency）：用户在最近一段时间内交易的频率/次数。

M（Monetary）：用户在最近一段时间内交易的金额。

本模型缺少关于交易金额M的数据，故暂剔除。

（1）获取R（最近一次消费的时间间隔）

    创建视图存放有购买记录的用户最近购买的时间（max(datetimes)）与2017-12-03之间的间隔时间，查询得出间隔天数区间为[0,8]。

create view a as
  select user_id,datediff('2017-12-03 23:59;59',max(datetimes)) as datediff 
     from userbehavior 
         where behavior_type='buy'
         group by user_id;

将R的评分分为4档（间隔越小，R分值越高）：

间隔天数0-2天为3分，

间隔天数3-4天为2分，

间隔天数5-6天为1分，

间隔天数7-8天为0分。

create view r as
select user_id,(case when datediff between 0 and 2 then 3
                     when datediff between 3 and 4 then 2
                     when datediff between 5 and 6 then 1
                     when datediff between 7 and 8 then 0
                     else null end) as r_score
from a;

评分完成

（2）获取F（消费频率）

查询有购买记录用户的购买次数：

create view b as 
select user_id,sum(case when behavior_type='buy' then 1 else 0 end) as '购买次数'
from userbehavior
group by user_id
having sum(case when behavior_type='buy' then 1 else 0 end)>0
order by 购买次数;

671 rows in set (0.31 sec)

得出671条查询结果，其中同一用户ID购买次数最多为43次，最少为1次。

将F的评分分为4档（频率越高，F分值越高）

1-10次为0分，

11-20次为1分，

21-30次为2分，

31-43次为3分。

create view f as
select user_id,(case when 购买次数 between 1 and 10 then 0
                     when 购买次数 between 11 and 20 then 1
                     when 购买次数 between 21 and 30 then 2
                     when 购买次数 between 31 and 43 then 3
                     else null end
) as f_score
from b;

评分完成。

（3）综合评价

select r.user_id,r.r_score,f.f_score,r.r_score+f.f_score 'r+f'
from r left join f
on r.user_id=f.user_id;

得出671名有购买记录的用户总分情况如下：

+---------+---------+---------+------+------+
| user_id   | r_score | f_score | r+f  |
+---------+---------+---------+------+------+
| 1000001 |       3     |       0     |    3 |
| 100002   |       2     |       0     |    2 |
| 1000060 |       3     |       0     |    3 |
| 1000070 |       1     |       0     |    1 |
| 1000135 |       1     |       0     |    1 |
| 1000154 |       0     |       0     |    0 |
| 1000169 |       2     |       0     |    2 |

671 rows in set (2.00 sec)

根据r+f总分绘制条形图

       由上图，划分0-1分为一般价值客户，2-3分为重要发展客户，4-6分为重要价值客户。进行用户RFM分类：

select user_id,r_score,f_score,sum,(case when rfm.sum in (0,1) then '一般价值客户'
					                     when rfm.sum in (2,3) then '重要发展客户'
					                     when rfm.sum in (4,5,6) then '重要价值客户'
					                     else null end) as '客户分类'
from rfm;

 3、商品分析

（1）转化率top10的商品品类

select category_id, concat(round(购买量*100/浏览量,3),'%') as 转化率 from
(select category_id,
        sum(case when behavior_type='buy' then 1 else 0 end) as 购买量,
        count(*) as 浏览量
from userbehavior
group by category_id) as t
order by 转化率 desc
limit 10;

+-------------+--------+
| category_id | 转化率 |
+-------------+--------+
| 3747017     | 9.890% |
| 2774299     | 9.677% |
| 4119922     | 9.524% |
| 4611962     | 9.091% |
| 3372283     | 9.091% |
| 874415      | 9.091% |
| 5140516     | 9.091% |
| 4470576     | 9.091% |
| 3419760     | 9.091% |
| 318219      | 9.091% |
+-------------+--------+
10 rows in set (0.39 sec)

（2）转化率top10的商品

select item_id, concat(round(购买量*100/浏览量,3),'%') as 转化率 from
(select item_id,
        sum(case when behavior_type='buy' then 1 else 0 end) as 购买量,
        count(*) as 浏览量
from userbehavior
group by item_id) as t1
order by 转化率 desc
limit 10;

 item_id    | 转化率 |
+---------+--------+
| 1984229 | 9.091% |
| 3732060 | 9.091% |
| 2304016 | 9.091% |
| 320169  | 9.091% |
| 4771138 | 9.091% |
| 2627840 | 9.091% |
| 1413154 | 9.091% |
| 4337988 | 9.091% |
| 3794565 | 9.091% |
| 1684440 | 9.091% |
+---------+--------+
10 rows in set (0.38 sec)

七、总结及建议

1. 整体转化率2.3%，收藏加购-下单转化率25.65%，浏览-收藏加购转化率仅9.13%。建议更换推送产品，提升推送产品的质量及吸引力。

2. RFM综合评分大于等于4的重点价值客户占比1.79%，建议可积极推送营销信息； 重点发展客户517人，占比77%，建议重点向此类人群推送促销信息。

3. 建议针对转化率top10的商品品类及商品进行积极推送，提高整体转化率。