我是fengbailc：
多年以来电子商务业务快速发展，尤其是移动客户端发展迅猛，移动互联网时代的到来让原本就方便快捷的网上购物变得更加便利，而淘宝作为国内最大的电商交易平台更是具有巨大的流量优势。
淘宝APP的功能日益复杂，但都离不开最基本的收藏、购物车和购买功能，本文利用sql对淘宝用户行为数据进行分析，通过用户行为分析业务问题。
一. 提出问题
本次分析的目的是想通过对淘宝用户行为数据分析，为以下问题提供解释和改进建议：
1.分析用户使用APP过程中的常见电商分析指标，确定各个环节的流失率，找到需要改进的环节
2.研究用户在不同时间尺度下的行为规律，找到用户在不同时间周期下的活跃规律
3.找到用户对不同种类商品的偏好，找到针对不同商品的营销策略
4.找出最具价值的核心付费用户群，对这部分用户的行为进行分析
为了分析这些问题，我们使用以下两种模型进行分析
1.基于AARRR漏斗模型分析用户行为
本文通过常用的电商数据分析指标，采用AARRR漏斗模型拆解用户进入APP后的每一步行为。
AARRR模型是根据用户使用产品全流程的不同阶段进行划分的，针对每一环节的用户流失情况分析出不同环节的优化优先级，主要通过以下个各阶段来进行分析：
2.基于RFM模型找出有价值的用户
由于不同用户对公司带来的收益差别很大，而且根据二八定律20%的做有价值用户能带来80%的收益，因此需要对用户进行价值评价，找到最有价值的用户群，并针对这部分用户进行差异化的营销。
这里参考著名的 RFM 模型对用户进行评价：
R-Recency（最近一次购买时间），R指用户上一次消费的时间，上一次购物时间距今最近的顾客通常在近期响应营销活动的可能性也最大，对于APP而言，很久没有购物行为可能意味着用户放弃了APP的使用，重新唤起用户也需要更多的成本。
F-Frequency（消费频率），F指用户在某段时间内的购物次数，消费频率越高意味着这部分用户对产品的满意度最高，用户粘性最好，忠诚度也最高。
M-Money（消费金额），M指用户在某段时间内的购物金额，这也是为公司带来价值的最直接体现，而消费金额较高的用户在用户总体中人数较少，却能创造出更多价值，是需要重点争取的对象。
这三个维度互相关联，反映了每个用户的现在价值和潜在价值，将每个维度分成5个区间进行评分，通过计算分数找到最有价值的用户，并对用户进行分类，可以有针对性的不同类型用户采用不同的营销策略。
二. 理解数据
1. 数据来源
阿里云天池：tianchi.aliyun.com/data
数据集包含了2014年11月18日至2014年12月18日之间，一个月内的淘宝APP移动端用户行为数据。由于总记录为1亿条，数量过大，此处只处理200万条数据作为代表，这部分数据中包含9969名用户的行为记录。
2.各字段含义
三. 数据清洗
1.数据选择
item_category 列地理位置的数据大多是空值 NULL ，难以补充和研究。所以后面不做和位置有关的分析。
2.列名重命名
将原有列名简化为id，item，behavior，category
3.删除重复值
用户的购买行为由于时间精确到小时，确实会存在少量用户在一小时内重复购买或浏览统一商品的行为，因此不对此部分数据进行处理。
4. 缺失值处理
只有item_category中存在大量NOT NULL，难以研究，已将其去除不做分析，其余数据中没有缺失值
5.一致化处理
时间数据中的日期和小时存在于一列中，需要将其分开以便分别研究每日和一段日期内的数据变化，故将其拆分成两列
ALTERTABLECHANGEtimedateCHAR(20);ALTERTABLEuserADDtimeCHAR(20);UPDATEuserSETtime=date;UPDATEuserSETdate=SUBSTRING(datefrom1FOR10);UPDATEuserSETtime=SUBSTRING(timefrom12FOR2);
对于用户行为，为了方便展示，将数字1,2,3,4改为pv、fav、cart、pay
UPDATEuserSETbehavior=REPLACE(behavior,1,'pv');UPDATEuserSETbehavior=REPLACE(behavior,2,'fav');UPDATEuserSETbehavior=REPLACE(behavior,3,'cart');UPDATEuserSETbehavior=REPLACE(behavior,4,'pay');
完成数据清洗后的数据：
四. 构建模型
1.分析用户行为的漏斗模型
利用AARRR模型分析用户行为，此处数据主要涉及用户刺激和购买转化的环节，通过用户从浏览到最终购买整个过程的流失情况，包括浏览、收藏、加入购物车和购买环节，得到一个月内的各项指标如下：
APP访问用户总数（UV)：9969
页面总访问量（PV）：1885533
SELECTCOUNT(DISTINCTid)ASUV,(SELECTCOUNT(*)FROMuserWHEREbehavior='pv')ASPV,(SELECTCOUNT(*)FROMuserWHEREbehavior='pv')/COUNT(DISTINCTid)AS'PV/UV'FROMuser;
跳失率=只点击一次浏览的用户数量/总用户访问量
SELECTCOUNT(*)FROM(SELECTidFROMuserGROUPBYidHAVINGCOUNT(behavior)=1)ASA;
当统计时间为30天时，只有53个人只浏览过一个页面就离开了APP，占总访问量的0.53%，占比几乎可以忽略不计，说明淘宝拥有足够的吸引力让用户停留在APP中。
用户总行为数漏斗计算：
SELECTbehavior,COUNT(*)FROMuserGROUPBYbehavior;
根据分析得到结果，使用excel绘制漏斗：
由于收藏和加入购物车都为浏览和购买阶段之间确定购买意向的用户行为，且不分先后顺序，因此将其算作一个阶段。从上面图片，可以看到从浏览到有购买意向只有5%的转化率。当然有一部分用户是直接购买而未通过收藏和加入购物车，但也说明大多数用户浏览页面次数较多，而使用购物车和收藏功能较少，而购买次数占使用购物车和收藏功能的20.5，说明从浏览到进行收藏和加入购物车的阶段是指标提升的重点环节。
独立访客漏斗模型计算：
SELECTbehavior,COUNT(DISTINCTid)AS'用户数'FROMuserGROUPBYbehaviorORDERBYCOUNT(DISTINCTid)DESC;
上面是每一步用户行为的独立用户数，可以看到使用APP的用户中有61%的付费用户，用户付费转化率相当高。
2.不同时间尺度下用户行为模式分析
分别以月、周和日为单位分析用户购买行为，找出用户活跃规律。
1）分析一个月中每天的用户行为
SELECTdate,SUM(CASEWHENbehavior='pv'THEN1ELSE0END)AS'浏览数',SUM(CASEWHENbehavior='fav'THEN1ELSE0END)AS'收藏数',SUM(CASEWHENbehavior='cart'THEN1ELSE0END)AS'购物车',SUM(CASEWHENbehavior='pay'THEN1ELSE0END)AS'付费数'FROMuserGROUPBYdateORDERBYdate;
根据分析结果，以月为周期绘制出下面观察用户行为变化曲线
从图表中我们发现在2014/12/12日四项用户指标均达到了高峰，此时正值双十二促销期间，几项指标中购买数涨幅最大，收藏数涨幅最小，这是由于双十二活动期间许多用户已经提前选择好商品，留到这一天购买所致。
同时收藏通常是与购买行为异步的用户行为，在购买行为发生前一段时间才会出现，因此提高幅度不如其他几项指标。
加入购物车的行为在批量购买商品时为购买的前置动作，通常购物车中会有多件商品，因此加入购物车的行为发生次数同样大幅增加。
2）分析一周内每日的用户行为
我们取双十二及与之相隔较远的另一周的七日内用户行为进行对比，可以看到明显不同。
从左到右为周一到周日的数据，在平时，周五为一周内各项指标最低的一天，而到周末达到最高峰。推测是上班族周五下班后忙着放松和休息，而周末有充足的精力，购买能力增加。
而双十二当天为周五，促销结束后周末的用户活跃度最低，因此平日运营可以将活动集中在周末进行，而双十二期间集中精力做好促销让用户购买冲动充分释放，结束后可以暂缓一段时间。
3）分析一天内用户每小时的行为
我们仍取双十二和相隔较远的一个周五进行对比，此处我们取2014/11/28日当天的用户数据
上图为12-12与12-11两天的用户行为变化。由于双十二的许多活动集中在零点，因此在0点之前购买数处在很低的水平，而购物车的行为数剧增，用户此时在为0点的抢购做准备。
而0点之后购买数达到第一个高峰，双十二当天早上八点到10点之间迎来了第二个高峰，此时部分用户早起购买大量商品，而晚上八点到十点经历了第三个高峰，促销活动可以针对这几个高峰期进行重点投放。
而11月28日一天内只有两个高峰期，分别为11到13是和晚上10点左右，对应了许多上班族中午和晚上的休息时间，和双12期间相比，没有了凌晨的购物高峰，也符合大部分人的作息时间。
3.不同商品种类的用户行为
统计所有商品的购买次数，同时找到购买次数、浏览次数、收藏次数和加入购物车次数最多的商品。
SELECTCOUNT(item)FROM(SELECTitem,COUNT(id)AS'times'FROMuserWHEREbehavior='pay'GROUPBYitem)ASbuy_timesGROUPBYtimesORDERBYCOUNT(*) ;
在本次统计的数据中，只购买一次的产品有17221种，购买两次的产品有806种，本次分析的商品中用户购买的共有19486种商品，却没有出现购买用户数量非常集中的商品，购买一次的商品占到88.4%，说明商品售卖主要依靠长尾商品的累积效应，而非爆款商品的带动。
列出销量前20位的商品，item_ID为303205878的商品销量最高，为12次，那么是否浏览次数最高的商品销量也最高呢，对商品浏览量也进行了统计：
我们看到浏览数最高的商品为112921337，并且销量最高的商品浏览数只排到第19位，而浏览次数最高的商品甚至没有进入销量前20，说明这些吸引用户更多注意力的商品没有很好的转化为实际销量。
上图为收藏数前二十的商品，这些商品的浏览数为4-5，可以看到销量第一的商品303205878收藏数为4。
在加入购物车次数最多的前20个商品中，可以看到销量第一的商品收藏数排在第二位，而销量第二的商品也具有较多的收藏数，说明收藏数与销量的关系更为直接。
4.基于RFM理论找出有价值的用户
我们首先统计出用户购买商品的最大值和最小值，数据源中没有相关的金额数据，因此通过 R 和 F 的数据对客户价值进行打分。
1）R-最近购买时间
用户数据的时间范围为一个月，最近购买时间的区间为0-30，将其分为5档，0-6,7-12,13-18,19-24,25-30分别对应评分0到4
CREATEVIEWpay_BASSELECTid,DATEDIFF('2014-12-18',MAX(date))ASBFROMuserWHEREbehavior='pay'GROUPBYid;SELECTid, (CASEWHENBBETWEEN25AND30THEN0WHENBBETWEEN19AND24THEN1WHENBBETWEEN13AND18THEN2WHENBBETWEEN7AND12THEN3WHENBBETWEEN0AND6THEN4ELSEnullEND)ASRFROMpay_BORDERBYRDESC;
2）F-消费频率
付费用户中消费次数从低到高为1-161次，将其分为5档，1-32，33-64，65-96，97-128,129-161分别对应评分0到4
CREATEVIEWpay_FASSELECTid,COUNT(*)ASAFROMuserWHEREbehavior='pay'GROUPBYid;SELECTid, (CASEWHENABETWEEN1AND32THEN0WHENABETWEEN33AND64THEN1WHENABETWEEN65AND96THEN2WHENABETWEEN97AND128THEN3WHENABETWEEN129AND161THEN4ELSEnullEND)ASFFROMpay_FORDERBYF DES;
将R和F的评分合并后得到分析结果：
从上面图表中，可以得到用户的RFM分数。其中用户ID为122338823的用户R和F评分都为4分，是体系中的最有价值用户，需要重点关注，并且活动投放时需谨慎对待，不要引起用户反感。
对于R值为4而F值为0的用户，用户粘性不强而消费时间间隔较短，运营活动可以重点针对这部分用户，提高用户使用产品的频率，可以通过拼团打折、积分兑换等活动唤起用户注意力。
五. 结论与建议
本文分析了淘宝APP用户行为数据共200万条，从四个不同角度提出业务问题，使用AARRR模型和RFM模型分析数据给出如下结论和建议。
1.通过AARRR模型分析用户使用的各个环节
1）获取用户
由于数据中没有给出每个用户第一次的登陆的时间，我们暂且把浏览行为视为用户的获取。
2）激活用户
用户行为包括点击、放进购物车、收藏以及购买。由于收藏和加入购物车都为浏览和购买阶段之间确定购买意向的用户行为，且不分先后顺序，因此将其算作一个阶段。
从浏览到有购买意向只有5%的转化率，当然有一部分用户是直接购买，但也说明大多数用户以浏览页面为主而购买转化较少，此处为转化漏斗中需要改善和提高的环节。
针对这一环节改善转化率的建议有：
优化电商平台的搜索匹配度和推荐策略，主动根据用户喜好推荐相关的商品，优化商品搜索的准确度和聚合能力，对搜索结果排序优先级进行优化。
在商品详情页的展示上突出用户关注的重点信息，精简信息流的呈现方式，减少用户寻找信息的成本
优化加入购物车和收藏按键的触达，用户在滑屏时也能方便触达，增加功能使用的次数。
3）提高留存
淘宝APP的留存相对而言较为稳定，让用户保提高持使用淘宝电商平台的频率相对而言更加重要。
4）增加收入
使用APP的用户中有61%的付费用户，付费转化率相当高。
5）用户推荐
淘宝本身用户基数庞大，知名度高，个人认为在一二线城市的用户基本已经达到饱和，传播工作需要针对三四线城市的渠道下沉，在这些地区针对用户价格敏感度高的特性开展类似拼多多的拼团转发和打折促销活动，扩大这部分用户的使用率。
2.研究用户在不同时间尺度下的行为规律，找到用户在不同时间周期下的活跃规律
一个月中的消费活动在平时以一周为周期进行波动，而双十二促销期间各项指标达到高峰。一周中的高峰期在周末，符合上班族作息时间中的空闲时期。而平时一天中有两个高峰期，中午12点左右和晚十点左右，双十二期间由于活动时间的关系凌晨的销量最高。
针对高峰期进行营销活动收益最高，此时使用人数最多，活动容易触达用户，营销活动的形式可以通过促销、拼团等形式进行。
3.找到用户对不同种类商品的偏好，找到针对不同商品的营销策略
商品售卖主要依靠长尾商品的累积效应，而非爆款商品的带动。销量最高的商品浏览数只排到第19位，而浏览次数最高的商品甚至没有进入销量前20，说明这些吸引用户更多注意力的商品没有很好的转化为实际销量。
针对浏览量高而销量不高的这部分商品，需要提高的是用户从点击进入商品详情页到最终购买的体验。作为商家端可以从以下几个方面提高销售额：
商品详情页的实际价格是否相比展示价格偏差过大，有的商家为了吸引用户点击在商品展示页投放的价格具有较强吸引力，但实际价格偏高，在用户心中反而引起反感
详情页的信息流展示是否合理，是否将用户最想看到的部分置于容易看到的位置，便于信息的获取
优化商品展示的形式，利用视频等方式给用户更直观的感受，提高照片的美观程度
评论区评价管理，尤其对于差评区的用户反馈进行认真对待，提高自身服务质量
4.通过RFM模型找出最具价值的核心付费用户群，对这部分用户的行为进行分析
R和F评分都很高的用户是体系中的最有价值用户，需要重点关注。并且活动投放时需谨慎对待，不要引起用户反感。
对于R值为4而F值为0的用户，用户粘性不强而消费时间间隔较短，运营活动可以重点针对这部分用户，提高用户使用产品的频率，可以通过拼团打折、积分兑换等活动唤起用户注意力。
本文来自猴子社群会员，来源：https://zhuanlan.zhihu.com/p/60519874
网友评论