移动互联网企业从粗放式到精细化运营管理过程中，需要结合市场、渠道、用户行为等数据分析，对用户开展有针对性的运营活动，提供个性化、差异化的运营策略，以实现运营业务指标。本项目利用sql对淘宝用户行为数据进行分析，通过用户行为分析业务问题，提供针对性的运营策略。
本次分析的目的是想通过对淘宝用户行为数据分析，为以下问题提供解释和改进建议：
1.分析用户使用APP过程中的常见电商分析指标，建立用户行为转化漏斗模型，确定各个环节的流失率，找到需要改进的环节
2.研究用户在不同时间尺度下的行为规律，找到用户在不同时间周期下的活跃规律，在用户活跃时间点推出相应营销策略
3.找到用户对不同种类商品的偏好，找到针对不同商品的营销策略
4.找出最具价值的核心付费用户群，对这部分用户的行为进行分析
两种导入方式：
新建数据库
导入数据表
选择导入前1000000行数据
需要注意源数据集中不包含字段名称行，导入时字段名行设置为0，第一个数据行设置为1。
设置字段类型
导入完成，耗时1分21秒
修改字段名
使用命令行连接MySQL，并进入到taobao数据库
创建新表user
载入源数据
# 统计重复值
SELECT *
FROM userbehavior
GROUP BY user_id,item,category,time
HAVING count(user_id)>1;
从查询结果可知，数据中不存在重复记录。
# 统计缺失值
SELECT count(user_id),count(item),count(category),count(behavior),count(time)
FROM userbehavior;
从查询结果来看，不存在缺失值，数据质量高。
# 新增date、hour时间字段
ALTER TABLE userbehavior
ADD date VARCHAR(20),
ADD hour VARCHAR(20);
# 时间格式转换
UPDATE userbehavior SET date = FROM_UNIXTIME(time,"%Y-%m-%d");
UPDATE userbehavior SET hour = FROM_UNIXTIME(time,"%H");
UPDATE userbehavior SET time = FROM_UNIXTIME(time);
# 调整一下time字段数据的样式
UPDATE userbehavior SET time = SUBSTRING_INDEX(time,'.',1);
由于数据集时间范围为2017-11-25至2017-12-3，因此需要对不在该时间范围内的异常数据进行过滤。
# 筛选异常数据
SELECT *
FROM userbehavior
WHERE date < '2017-11-25' or date > '2017-12-03';
# 过滤异常数据
DELETE FROM userbehavior
WHERE date < '2017-11-25' or date > '2017-12-03';
总共过滤掉470条异常记录。
# UV、PV、UV/PV指标统计
SELECT count(DISTINCT user_id) as UV,
sum(case when behavior='pv' then 1 else 0 end) as PV,
sum(case when behavior='buy' then 1 else 0 end) as Buy,
sum(case when behavior='cart' then 1 else 0 end) as Cart,
sum(case when behavior='fav' then 1 else 0 end) as Fav,
sum(case when behavior='pv' then 1 else 0 end)/count(DISTINCT user_id) as 'PV/UV'
FROM userbehavior;
访问用户总数（UV）：9739
页面总访问量（PV）：895636
9天时间内平均每人页面访问量（UV/PV）：约为92次
# 复购率
SELECT
sum(case when buy_amount>1 then 1 else 0 end) as "复购总人数",
count(user_id) as "购买总人数",
sum(case when buy_amount>1 then 1 else 0 end)/count(user_id) as "复购率"
FROM
(SELECT *,count(behavior) as buy_amount
FROM userbehavior
WHERE behavior = 'buy'
GROUP BY user_id) a;
从结果来看，复购率高达66.21%，反映淘宝的用户忠诚度较高。
# 跳出率
SELECT count(*) as "仅访问一次页面的用户数"
FROM
(SELECT user_id
FROM userbehavior
GROUP BY user_id
HAVING count(behavior)=1) a
统计结果表明，9天时间内，没有一名用户仅浏览一次页面就离开淘宝，跳失率为0。反映出商品或者商品详情页的内容对于用户具有足够的吸引力，让用户在淘宝驻留。
漏斗分析模型已经广泛应用于各行业的数据分析工作中，用以评估总体转化率、各个环节的转化率，以科学评估促销专题活动效果等，通过与其他数据分析模型结合进行深度用户行为分析，从而找到用户流失的原因，以提升用户量、活跃度、留存率，并提升数据分析与决策的科学性等。
# 电商行业转化率算法
利润=销售额X净利润率
=（购买人数X客单价）X净利润率
=进店人数X购买转化率X客单价X净利润率
=广告展现X广告转化率X购买转化率X客单价X净利润率
=推广展现X推广转化率X购买转化率X客单价X净利润率
=搜索展现X搜索转化率X购买转化率X客单价X净利润率
=*****X*****转化率X购买转化率X客单价X净利润率
# 用户总行为漏斗
SELECT behavior,COUNT(*)
FROM userbehavior
GROUP BY behavior
order by behavior desc;
用户总行为转化漏斗图：
独立访客（UV）的转化漏斗
# 独立访客转化漏斗
SELECT behavior,count(DISTINCT user_id)
FROM userbehavior
GROUP BY behavior
ORDER BY behavior DESC;
综合用户总行为转化漏斗图与独立访客转化漏斗图，可以发现：
针对上述环节改善转化率的建议：
题外话：大家在使用淘宝购物时，有没有过被店铺利用优惠券引导收藏加购的经历呢？为什么店铺愿意花一定的代价去做这件事？原因就是淘宝收藏加购的好处意义是直接提升宝贝权重排名，人气权重这个维度在搜索排序维度中权重占比有很大提升。淘宝收藏加购多了，会增加淘宝店铺权重，从而提升宝贝排名，流量自然也就多了。
# 每天的用户行为分析
SELECT
date,
count(DISTINCT user_id) as '每日用户数',
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
GROUP BY date;
每日用户行为数据变化：
在2017年11月25日-2017年12月3日统计窗口内，11月25-26日与12月2-3日为周末。
通过每日用户行为数据变化曲线可以分析：11月25日至12月1日，数据波动变化范围很小，在12月2-3日（周末），各项数据指标明显上涨，高于前7天的各项数据指标。由于在上一个周末（11月25-26日）的各项数据指标并未存在明显涨幅，因此推测在12月2-3日数据指标上涨可能与淘宝双12预热活动相关。
# 每时的用户行为分析
SELECT
hour,
count(DISTINCT user_id) as '每时用户数',
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
GROUP BY hour;
每时用户行为数据变化：
结果显示：在凌晨2-5点左右，各项数据指标进入低谷期；在9-18点之间，数据呈现一个小高峰，波动变化较小；在20-23点间，各数据指标呈现一个大高峰，并且在21点左右达到每日数据最大峰值，数据的变化趋势比较符合正常用户的作息规律。在制定运营策略时，可以利用这个规律来进行创收，选择在每天用户最活跃的时间段推出各种网店直播、直播带货等互动营销手段。
关于“受欢迎”的商品，可以从销量与浏览量两个维度（用户行为）去分析。但有的浏览量高的商品可能是因为被页面或广告等吸引而来，或者只是感兴趣，用户并不一定会购买；而销量高的产品有可能是用户真正需要的，搜索和点击购买的目标也比较明确。因此需要同时结合销量与浏览量两个维度去进行分析。
# 商品销量排行榜前10
SELECT item, count(behavior) as '购买次数'
FROM userbehavior
WHERE behavior='buy'
GROUP BY item
ORDER BY count(behavior) DESC
limit 10;
从商品销量排行榜可以发现，在被下单的17565件商品中，单个商品销量最多不超过17次，且仅有5件商品销量超过10次，反映出在分析的数据集中，并没有出现卖的比较火爆的商品。
# 商品浏览量排行榜前10
SELECT item, count(behavior) as '浏览次数'
FROM userbehavior
WHERE behavior='pv'
GROUP BY item
ORDER BY count(behavior) DESC
limit 10;
对销量前10与浏览量前10的商品进行表连接：
# 商品销量榜单与浏览量榜单表连接
SELECT a.item,a.`购买次数`,b.`浏览次数`
FROM
(SELECT item, count(behavior) as '购买次数'
FROM userbehavior
WHERE behavior='buy'
GROUP BY item
ORDER BY count(behavior) DESC
LIMIT 10) a
LEFT JOIN
(SELECT item, count(behavior) as '浏览次数'
FROM userbehavior
WHERE behavior='pv'
GROUP BY item
ORDER BY count(behavior) DESC
limit 10) b on a.item=b.item;
结果显示：商品销量榜单与商品浏览量榜单之间对应性差（仅有2件商品销量与浏览量同时进入前20榜单），反映浏览量高的商品其销量不一定高，销量高的商品其浏览量不一定高，因此需要同时结合销量与浏览量两个维度去进行分析。下面将以商品浏览量和销量两个维度指标来对商品进行四象限划分，分析不同类型商品对应的用户行为，并提出相应的改进措施。
商品销量与浏览量两个维度的界限值分别选取3、20（需要根据实际业务场景来确定界限值），将商品按照销量与浏览量划分为四个象限：
第Ⅰ象限： 商品浏览量与销量都高，说明商品购买转化率高，属于受用户欢迎的商品。
第Ⅱ象限： 商品的销量高，但是浏览量较低。产生这种现象可能有以下两种原因（B端与C端两个角度）：一是 该象限内的商品可能属于某类特定群体的刚需产品，这类特定群体搜索和浏览的目标比较明确；二是 该象限内的商品受众广，但是引流入口数量少。
第Ⅲ象限： 商品的浏览量低，销量也低。原因主要需要从B端进行分析：一是 该象限内商品的引流入口数量；二是 该象限内商品是否符合用户的需求？对用户的吸引力如何？
第Ⅳ象限： 商品的浏览量高，但是销量偏低，商品购买转化率低。其原因也可以分为B/C端进行分析：在B端方面，是否是广告的投放人群或者商品的推送目标有问题，并没有获取到对应商品的目标用户；是否是商品的定价与定位的原因，价格太贵且不符合主流消费群体的胃口；是否是商品详情页的图片、描述以及商品的评价较差；是否是客服的服务不到位、APP操作更新流程复杂等因素。在C端方面，用户的消费能力可能会对商品的销量有影响。
知识点：
按照商品销量对商品分类统计：
# 按照商品销量对商品进行分类统计
SELECT a.`购买次数`, count(a.item) as '商品量'
FROM
(SELECT item, count(behavior) as '购买次数'
FROM userbehavior
WHERE behavior='buy'
GROUP BY item
ORDER BY count(behavior) DESC) a
GROUP BY a.`购买次数`
ORDER BY count(a.item) DESC;
绘制不同销量对应商品量柱状图：
根据不同销量对应商品量数据，在被下单的17565件商品中，只购买一次的商品有15536件，占下单总商品的88.45%，说明在互联网环境下，以淘宝为代表的电商平台，其商品售卖主要是依靠长尾商品的累计效应，并非爆款商品的带动。
基于RFM模型进行用户分层分析，由于数据集中不包含订单金额，故本次分析中不考虑M维度，只分析R、F两个维度，对两个维度的指标进行分级打分,最终按照综合得分对用户分层。
计算用户的最近消费时间间隔R值（R值越小，说明用户最后消费时间越近），并对R值进行打分。根据R值结果，将其分为三个区间[0:2]，[3:5]以及[6:8]，分别赋予R_Score值3、2、1分。
# RFM模型——R维度分析
CREATE VIEW r_value as
SELECT user_id, min(时间间隔) as R
FROM
(SELECT user_id, DATEDIFF('2017-12-03',date) as '时间间隔'
FROM userbehavior
WHERE behavior='buy') a
GROUP BY user_id;
# 进行R维度打分
SELECT user_id, R,
case when R BETWEEN 0 and 2 then 3
when R BETWEEN 3 and 5 then 2
else 1 end as R_Score
FROM r_value
统计不同R_Score占比：
从R_Score占比中可以发现，超过半数用户会在购物当天的后两天内就再次购买，可以看出淘宝已经成为人们的日常购物习惯。
计算用户的消费频率F值，并对F值进行打分。根据F值结果（最大值72次），将其分为6个区间[1:9]，[10:19]，[20:29]，[30:39]，[40:49]以及[50:72]，分别赋予F_Score值1、2、3、4、5、6分。
# RFM模型——F维度分析
CREATE VIEW f_value as
SELECT user_id, count(behavior) as F
FROM userbehavior
WHERE behavior='buy'
GROUP BY user_id
ORDER BY count(behavior) DESC;
# 进行F维度打分
SELECT user_id, F,
case when F BETWEEN 1 and 9 then 1
when F BETWEEN 10 and 19 then 2
when F BETWEEN 20 and 29 then 3
when F BETWEEN 30 and 39 then 4
when F BETWEEN 40 and 49 then 5
else 6 end as F_Score
FROM f_value
统计不同F_Score占比：
从R_Score占比中可以发现，在统计窗口的9天时间内，96.76%的用户在淘宝平台消费1-9次。
RF综合打分：
# 进行R维度打分
CREATE VIEW r_score as
SELECT user_id, R,
case when R BETWEEN 0 and 2 then 3
when R BETWEEN 3 and 5 then 2
else 1 end as R_Score
FROM r_value
# 进行F维度打分
CREATE VIEW f_score as
SELECT user_id, F,
case when F BETWEEN 1 and 9 then 1
when F BETWEEN 10 and 19 then 2
when F BETWEEN 20 and 29 then 3
when F BETWEEN 30 and 39 then 4
when F BETWEEN 40 and 49 then 5
else 6 end as F_Score
FROM f_value
# RF综合打分
CREATE VIEW rf_score as
SELECT a.user_id, a.R_Score, b.F_Score, a.R_Score+b.F_Score as RF_Score
FROM r_score a JOIN f_score b on a.user_id=b.user_id
根据RF_Score值，对用户进行分层：划分为2-3分，4-5分，6-7分，8-9分四个等级，分别对应易流失用户、挽留用户、发展用户、忠诚用户。
# 用户分层
SELECT *,
case when RF_Score BETWEEN 2 and 3 then '易流失用户'
when RF_Score BETWEEN 4 and 5 then '挽留用户'
when RF_Score BETWEEN 6 and 7 then '发展用户'
else '忠诚用户' end as '用户分层'
FROM rf_score
统计不同类型用户占比：
用户分层结果：
用户分层效果分析： 从不同用户占比上来看，本次用户分层的效果不佳，可能有以下两个方面原因：1、两个维度的打分区间划分的不合理，并没有用户很好的分开，应该在做区间划分时提前看一下各维度的用户分布情况，结合实际业务场景需求，去确定区间界限值；2、两个维度划分的区间数不一致，且赋予的分值区间差异较大，这种操作其实相当于给两个维度赋予了不同的比重。
在本次用户分层中，可能采取RF两个维度的四象限划分更简单明了，且效果会更好。
以user_id为107932的用户为例，对其进行深入分析：
# user_id为107932用户行为分析
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 '购买数',
sum(case when behavior='buy' then 1 else 0 end)/sum(case when behavior='pv' then 1 else 0 end) as '购买转化率'
FROM userbehavior
WHERE user_id = 107932
GROUP BY date;
结果表明：该用户几乎每天都有消费，购买转化率也很高，但从未使用过收藏功能，购物车的使用频率也极低。对于高价值用户，可以分析其常购买的商品类型，再进行相关的商品推荐。由于商品种类数据为脱敏数据，本文无法分析该用户购买的商品类型。
本文从4个维度分析了淘宝用户行为数据共100万条，结论和建议如下：