1、分析环境
数据来源：阿里云天池。
分析软件：sql + Excel
资源链接：
navicat premium15 mac版本，下载链接在这里http://www.pc6.com/mac/111878.html，拿走不谢，12版本有bug，深受其苦。
使用的postgresql数据库，下载链接在这里https://www.runoob.com/postgresql/mac-install-postgresql.html，请叫我雷锋
2、项目背景简介
（一）项目背景
经历过互联网流量粗放式增长的红利年代，在行业天花板高度有限的增长空间下，对现有存量的精细化运营成为营销和运营人员的普遍共识，而对于精细化运营的最好诠释莫过满足用户和消费者的个性化需求，与用户一同成长，从而获得一种长期持久的生命力，在互联网年代，消费者也同时拥有用户两重身份，尽管在一定程度上延长购买决策流程，并可能在任何一个环节跳出或流失，但也留下清晰的数字轨迹，而作为一个数据分析师可以通过数据的分析与反馈捕捉出关键的决策信息，在运营人员的干预下，为下一次的成交铺垫了可能。本次分析将以淘宝用户行为数据进行分析，为制定精细化的用户运营策略提供参考。
（二）数据来源与介绍
数据介绍如下
（三）项目目的
本次分析以淘宝用户消费行为为核心概念，顺着谁（who）、在什么时候（when）、在哪儿（where）、买了什么（what）、怎么买的（how）的思路，同时结合现有的数据资料，构建出消费主体纬度、消费时间维度、消费商品维度、以及消费动作四大维度，试图对消费者在2017年11月25日至2017年12月3日之间的消费行为模式进行分析呈现，试图回答以下几个问题：
1、时间维度：消费者在不同时间尺度下的行为规律是什么？——目的：根据消费者的活跃周期采取相应的营销策略，时空即场景，是行为发生的重要两个维度，本次分析中，只有时间数据。
2、商品维度：现阶段各类商品的销售状况如何？消费者对哪类商品需求量最大？商品的类型可以划分为哪些？——采用象限划分法——策略驱动
3、动作维度：根据用户的淘宝使用行为又可细分为页面浏览行为和下单购买行为，浏览行为对应流量指标，可通过pv、uv、跳出率等进行测量，下单行为对应购买指标，通过支付率、复购率、回购率等指标进行测量，从浏览到下单的过程，可通过漏斗模型分析转化率——目的：将分析所得数据与往日相关数据指标进行对比，找出存在异常的环节。——漏斗模型，流程推演
4、消费者主体纬度：除了年龄、性别这类人口统计学指标，在消费者行为学中，更为重要的是消费者行为相关的指标，在这里，主要采用rfm模型对消费者进行分类，针对不同类型的消费者采取相应的营销策略，因此可以再针对各类消费者的消费行为进行分析，找到各类型消费者的一个典型消费画像，采取更精准的营销策略。——多维度法：精细化驱动
（四）数据导入
1、创建database，点击表格，导入数据
2、在这里注意，源数据中没有字段名，同时源数据量过大，在这里只选择了前100w条数据
3、在这里navicat会自动匹配合适的数据类型，可改可不改，后面还可以重新更改
4、导入成功
3、数据清理
（一）列名重命名
命名尽量一目了然又简洁，因为本次分析中只有一个表，也没有设置主键
（二）重复值查看
消费者每一次行为都是独一无二的，因此要对全部的字段进行分类汇总显示，结果显示没有重复数值。
SELECT * FROM userbehavior GROUP BY user_id,item,category,behavior,time HAVING count(user_id)>1;
（三）缺失值处理
因为count不会把null计算在内，因此本次数据的质量较高，没有空值，空值的处理一般是删除，取平均值等
SELECT count(user_id),count(item),count(category),count(behavior),count(time) FROM userbehavior;
（四）时间格式转换：时间格式转换学习
在时间格式转换这里遇到了一些trouble，心态一度崩溃，原本打算采用update 语句对数据库中的time进行更新，同时新增加两列分别为date，及hour，date是从时间戳中截取出来的yyyy_mm_dd部分，hour即从时间戳中截取出来的hour部分
首先是发现时间为unix时间戳类型，但是悲催的是postgresql中好像不支持将数据设置成这种类型，也就意味着后续操作都无法进行，wtf，整个人暴走，查遍全网没有找到解决方法，当然重新换个数据库比如mysql，那可能心态更会爆炸……
……
好，既然无法直接在navicat中将数据格式设置为时间戳类型，那只能走曲线救国路线（此处@某人，说改成自己会写的语法来实现，启发了我）
首先将time数据老老实实设置为varchar类型，尝试利用to_timestamp(text, text)将字符串转换成时间戳类型，有点不可思议，竟然成功了，令人感动
SELECT TO_TIMESTAMP(time) as T FROM UserBehavior ;
这就给我接下来新的思路，既然这样可以的话，想起来学过的视图，不如创建一个新的视图，作为一个临时表，之后的查询都可以在这个表上进行。
CREATE VIEW ub_new(new_id,new_item,new_category,new_behavior,new_time,new_date,new_hour) AS SELECT user_id,item,category,behavior,TO_TIMESTAMP(time),to_char(TO_TIMESTAMP(time),\'yyyy-MM-dd\'),EXTRACT(HOUR FROM TO_TIMESTAMP(time)) FROM UserBehavior;
emmmm，其实找到这个解决办法，花了两个小时的时间……一直在纠结在postgresql中unix时间戳格式如何设置或者转化，太难了。主要是对navicat的使用和视图还不熟练，只掌握了基础的语法，能够完成语句的编辑，但一些好用的工作技巧还需要探索。
（五）异常值处理
由于数据集时间范围为2017-11-25至2017-12-3，因此需要对不在该时间范围内的异常数据进行过滤。
SELECT * FROM ub_new WHERE new_date < \'2017-11-25\' or new_date > \'2017-12-03\';
有1389条不符合要求的数据,删除之后，还剩余999530条数据
DELETE FROM ub_new WHERE new_date < \'2017-11-25\' or new_date > \'2017-12-03\';
4、数据分析
-
分析框架
（一）时间维度
1、消费者每日的消费行为变化趋势
SELECT new_hour,count(DISTINCT new_id), SUM(CASE WHEN new_behavior = \'pv\' THEN 1 ELSE 0 END), SUM(CASE WHEN new_behavior = \'cart\' THEN 1 ELSE 0 END), SUM(CASE WHEN new_behavior = \'fav\' THEN 1 ELSE 0 END), SUM(CASE WHEN new_behavior = \'buy\' THEN 1 ELSE 0 END) FROM ub_new GROUP BY new_hour
从消费者每日行为数据来看，数据较为符合用户的日常作息规律，白天整体较为活跃，凌晨2——5点进入低谷。
白天时段9—18点，用户总数波动较小，且整体维持在较高水平，说明在不同时段，不同的用户都有其淘宝浏览及购物的需求，应当再根据用户的类型进行精细化运营
从18点——22点，用户淘宝浏览及购物出现一个小高峰，说明下班后及睡前是用户偏爱购物的时段，在这一时段，可以相应推出的营销活动来吸引用户的关注，提高用户的下单率。
2、2017年11月25日至2017年12月3日期间，消费者行为数据
SELECT new_date,count(DISTINCT new_id), SUM(CASE WHEN new_behavior = \'pv\' THEN 1 ELSE 0 END), SUM(CASE WHEN new_behavior = \'cart\' THEN 1 ELSE 0 END), SUM(CASE WHEN new_behavior = \'fav\' THEN 1 ELSE 0 END), SUM(CASE WHEN new_behavior = \'buy\' THEN 1 ELSE 0 END) FROM ub_new GROUP BY new_date
在11月25日——12月3日的统计窗口内，1月25-26日与12月2-3日为周末
各项指标在12月2日和12月3日出现了显著的增长，但对比上一个周末，排除休息的因素，猜想应该与双十二的活动预热相关。
（二）商品维度
1、商品销量排行前10
SELECT new_item,COUNT(new_item) AS amount_sales FROM ub_new WHERE new_behavior = \'buy\' GROUP BY new_item ORDER BY amount_sales DESC LIMIT 10;
2、根据浏览量维度和下单量维度对商品类型进行象限划分
由于诸多原因，在这里将各类商品的浏览量、加购量、收藏量和购买量创建视图
CREATE VIEW ub_category(category,pv_total,cart_total,fav_total,buy_total) AS SELECT new_category, SUM(CASE WHEN new_behavior = \'pv\' THEN 1 ELSE 0 END), SUM(CASE WHEN new_behavior = \'cart\' THEN 1 ELSE 0 END), SUM(CASE WHEN new_behavior = \'fav\' THEN 1 ELSE 0 END), SUM(CASE WHEN new_behavior = \'buy\' THEN 1 ELSE 0 END) FROM ub_new GROUP BY new_category
在此视图上进行操作，在这里想根据浏览量和下单量两个维度对商品进行划分，因为不同商品之间浏览和购买的差异较大，因此根据二八法则来进行象限的划分
因此buy_total=3以及pv_total=70为划分界限，
buy_tota>3以及pv_total>70为高浏览量高销量产-A类产品（流量品类）
buy_tota<=3以及pv_total>70为高浏览量低销量产品—B类产品（白嫖品类）
buy_tota<=3以及pv_total<=70为低浏览量低销量产品—C类产品（冷宫品类）
buy_tota>3以及pv_total<=70为低浏览量高销量产品——D类产品(宝藏品类）
SELECT SUM(CASE WHEN pv_total > 70 AND buy_total > 3 THEN 1 ELSE 0 END), SUM(CASE WHEN pv_total > 70 AND buy_total <= 3 THEN 1 ELSE 0 END), SUM(CASE WHEN pv_total <= 70 AND buy_total <= 3 THEN 1 ELSE 0 END), SUM(CASE WHEN pv_total <= 70 AND buy_total > 3 THEN 1 ELSE 0 END) FROM ub_category
总体看来，全部商品类型中有80%为C类和D类，A类和B类共计占20%
A类产品高流量、高销量的流量品类，在日常生活中对应高频、低价类商品，针对此类商品事宜采取日常促销策略，同时在产品设计和价格上进行适当的组合和设计，推出满足不同人群不同场景使用需求，追求规模化效应，属于主打品类。逛优衣库、名创优品的感觉
B类产品属于高流量、低销量品类，将其称为白嫖品类，因为光看不买。可能对应高价，低频类产品，消费者需要多次浏览、不断进行对比、查看评论等方式进行消费决策，
究其原因，可以从to b和to c两个维度进行分析，对于商家来说，可能是因为投入了大量的营销精力，吸引了不少消费者到达商品详情页，可能是目标消费者不精准、产品本身的质量、价格等因素，使得成交量偏低，对于消费者来说，可能是超过消费范围， 类似逛verymoda、拉夏贝尔、only类，品牌溢价较高、性价比相对不高的品牌
C类产品低流量、低销量，冷宫类产品，就是大家平时看的少，买的也少，对于商家b端而言：可能由于自身生产规模较小，产品又不是刚需，或是缺乏竞争力和明确的定位，同时缺乏引流成本，更无法以销促产，不能形成良性循环，生命垂危，针对此类产品要么抱大腿，傍大款，合并形成规模化生产，降低价格；要么破釜沉舟，改良产品，增加推广。类似逛商场里的不知名小店，风格混乱、性价比一般、设计过时、过段时间就快关门。当让也存在另一种可能就是那种低价低频的产品，比如比较小众又低廉的产品：老鼠药～、危险的化学药品
D类产品，称之为宝藏品类，流量低，销量高，有两种原因，首先是产品比较小众，但是消费者精准且购买力比较强，类比逛奢侈品店，可以适当增加对精准人群的促销，也有可能是产品处于市场导入期，缺乏市场推广，此时就需要增加流量投入，促进销售
（三）动作维度
1、流量指标
1.1 pv、uv、下单量、pv/uv
SELECT count(DISTINCT new_id), SUM(CASE WHEN new_behavior = \'pv\' THEN 1 ELSE 0 END), SUM(CASE WHEN new_behavior = \'buy\' THEN 1 ELSE 0 END), SUM(CASE WHEN new_behavior = \'pv\' THEN 1 ELSE 0 END)/count(DISTINCT new_id) FROM ub_new
11.25-12.3日期间：
总访客数UV为9739
页面总访问量PV为895636
总下单量为20395
人均访问次数为91次
1.2跳失率：浏览单页面即退出的用户/全部访问用户
SELECT COUNT(*) from (SELECT new_id ,COUNT(*) FROM ub_new WHERE new_behavior = \'pv\' GROUP BY new_id HAVING COUNT(*) = 1) as t
一次浏览就跳出页面的人数共计有7人，累计7次，跳出率=7/9739，可见跳出率非常低，说明网页内容和体验较好
2、购买指标
2.1成交率：下单用户在总的客流量中的占比。
20395/895636 = 2.27%
2.2复购率：复购率是一段时间内多次消费的用户占总消费用户数比。
首先求出在11.25——12.3这段期间内同一类产品消费次数大于1的用户的数量
SELECT COUNT(*) FROM (SELECT new_id,COUNT(new_id) FROM ub_new WHERE new_behavior = \'buy\' GROUP BY new_id HAVING COUNT(new_id) > 1) as t
说明在11.25——12.3这段时间内用户的复购率为4429/20395=21.7%，这个数据需要同往日相似时段进行对比才能做出比较合理的分析。
另外在本次是将消费者购买任何一类产品都计为一次购买，反应消费者对平台或者物流的信任认可程度。但不能较好的反应对某类产品的忠诚度。
2.3回购率：回购率率是一段时间内消费过的用户，在 下一段时间内仍旧消费的占比。
本次分析的总时间一共为为9日，将11.25—11.28，以及11.30—12.3作为两个时间窗口进行计算
SELECT new_id,COUNT(new_id) FROM (SELECT DISTINCT(new_id) FROM ub_new WHERE new_behavior = \'buy\' AND (new_date BETWEEN \'2017-11-25\' AND \'2017-11-28\') UNION SELECT DISTINCT(new_id) FROM ub_new WHERE new_behavior = \'buy\' AND (new_date BETWEEN \'2017-11-30\' AND \'2017-12-03\')) as t GROUP BY new_id HAVING COUNT(new_id)>1
结果显示为空，说明不存在在这两个时间窗口产生回购行为的客户，因为时间间隔较小，本次统计实际意义很小，仅仅作为sql语句练习。
3、转化率：常用漏斗模型：首页—商品详情页—加入购物车—提交订单—支付订单
3.1 pv——cart——buy转化漏斗
SELECT count(DISTINCT new_id), SUM(CASE WHEN new_behavior = \'pv\' THEN 1 ELSE 0 END), SUM(CASE WHEN new_behavior = \'cart\' THEN 1 ELSE 0 END), SUM(CASE WHEN new_behavior = \'buy\' THEN 1 ELSE 0 END) FROM ub_new
3.2独立访客转化漏斗
SELECT new_behavior,COUNT(DISTINCT new_id) FROM ub_new GROUP BY new_behavior
（四）主体维度
1、rfm模型分层模型
rfm模型是衡量客户价值和客户创利能力的重要工具和手段从 最近一次消费 (Recency)、消费频率 (Frequency)、消费金额 (Monetary)三个维度对客户价值进行衡量，但是数据集中不包括money维度，因此主要从f和r两个维度进行分析
1.1最近一次消费 (Recency)维度分析
首先计算不同消费者最近一次的消费间隔
SELECT new_id,MIN((DATE\'2017-12-03\'-DATE(new_date))) FROM ub_new WHERE new_behavior = \'buy\' GROUP BY new_id
代码的意思是：取出有下单行为的消费者最近一次的消费者间隔
在这里需要的注意的是，不能按照间隔去对消费进行计数汇总，因为会把有多次购买的用户重复计数
第二步对不同类型的消费者的消费频率进行打分：
0-2计为3分
3-5计为2分
6-8计为1分
SELECT SUM(CASE WHEN min >= 0 AND min <=2 THEN 1 ELSE 0 END) , SUM(CASE WHEN min >= 3 AND min <=5 THEN 1 ELSE 0 END) , SUM(CASE WHEN min >= 6 AND min <=8 THEN 1 ELSE 0 END) FROM R_table
然后按照0-2，3-5，6-8进行维度划分，依次命名为赋予3分，2分及1分
由图可知，半数多用户会在购买2天内再次消费
1.2消费频率 (Frequency)维度分析
首先计算不同用户的购买频率
CREATE VIEW F_table AS SELECT new_id,COUNT(new_id) FROM ub_new WHERE new_behavior = \'buy\' GROUP BY new_id
结果发现，最高消费频率为72次，最低为1次，将其分为1-9，10—19，20—29，30—39，40—49，50—72 共计6个区间，分别记分为1，2，3，4，5，6
SELECT SUM(CASE WHEN count >= 1 AND count <=9 THEN 1 ELSE 0 END) AS F_1, SUM(CASE WHEN count >= 10 AND count <=19 THEN 1 ELSE 0 END)F_2, SUM(CASE WHEN count >= 20 AND count <=29 THEN 1 ELSE 0 END)F_3, SUM(CASE WHEN count >= 30 AND count <=39 THEN 1 ELSE 0 END)F_4, SUM(CASE WHEN count >= 40 AND count <=49 THEN 1 ELSE 0 END)F_5, SUM(CASE WHEN count >= 50 AND count <= 72 THEN 1 ELSE 0 END)F_6 FROM f_table
由图表可知，越97%的消费者只产生了1-9次购买行为
1.3用户打分及分层
首先给两个维度分别赋分
--F维度打分 CREATE VIEW F_score AS SELECT new_id,count, (CASE WHEN count >= 1 AND count <=9 THEN 1 WHEN count >= 10 AND count <=19 THEN 2 WHEN count >= 20 AND count <=29 THEN 3 WHEN count >= 30 AND count <=39 THEN 4 WHEN count >= 40 AND count <=49 THEN 5 ELSE 6 END) AS F_s FROM F_table --R维度打分 CREATE VIEW R_score AS SELECT new_id,min,(CASE WHEN min >= 0 AND min <=2 THEN 3 WHEN min >= 3 AND min <=5 THEN 2 ELSE 1 END) AS R_score FROM R_table
对用户进行综合打分并进行分层
SELECT SUM(CASE WHEN rf BETWEEN 2 AND 3 THEN 1 ELSE 0 END), SUM(CASE WHEN rf BETWEEN 4 AND 5 THEN 1 ELSE 0 END), SUM(CASE WHEN rf BETWEEN 6 AND 7 THEN 1 ELSE 0 END), SUM(CASE WHEN rf BETWEEN 8 AND 9 THEN 1 ELSE 0 END) FROM rf
易流失用户和挽留用户占据了大部分比例，针对这类人群可以采取主动拉取的策略，通过活动促销、优惠券、团购、设计产品组合等方式来进行。
分类效果不是很好，因为基本上仍未将用户很好的区分开，颗粒较粗。同时两个维度的权重也有所不同，因此对用户分值进行直接相加也不尽合理。
5、结论及建议
（一）从时间维度来看：
以小时为尺度，针对淘宝这个app而言，用户的购买场景随时随地都在发生，除了深夜凌晨休息时段，白天用户的购买行为较为随意，在夜晚迎来一个小高峰，但是因为是统计的每小时的总访客数、页面访问量、购买总数等数据，实际上针对不同的用户来说，其购物习惯也会存在差异，而利用这些数据构建的模型并不能很好的提供足够的信息来反映某类产品或者某类用户的购买行为，难以提供较为精准化的运营意见。
以天为尺度，用户的购买行为存在周末与工作日的区别，同时用户行为也会收到节日促销的显著影响，因此策划合理的活动促销，是当下电商运营的主要手段，因此对数据分析师而言，更应该能够针对每次活动的数据进行精细化的分析，为下次活动促销提出行之有效的改进建议，提升活动运营效果，形成一个增长闭环。
（二）从商品维度来看：
从商品销售排行的结果来看，在窗口时间内，销量排行前十的商品也仅有17个，啊，多么得令人震惊，当然也有可能是这部分数据的问题，但是也充分说明，尽管一个爆款商品的出现是能够凭借一己之力拉动市场的大部分营收的，不管针对一个店铺还是一个品牌而言，爆品思维也是非常重要的，不过同样根据二八法则，剩下80%的品类的尽管只创造了20%的收益，但也充分说明了长尾小众市场的重要意义在于对于各类个性化场景和需求的满足，因此针对部分商家而言，可以在这些被主流忽视的领域外，确立自己的定位，再利用二八法则，打造自己的爆品。
从商品分类的结果来看，针对各类产品的营销策略已经进行了比较充分的论述，在此不再赘述。
（三）从动作维度来看
从浏览行为纬度，各类指标反映出，app的内容和页面吸引是较为优质的，跳出率几乎为0，当然这更可能是因为消费者自身的强烈的购买或者浏览目的，尽管得出了uv、pv、成交量等各类指标，但是这些指标也只有根据特定的业务目进行对比才有意义，比如，想发现最近的uv、pv成交量是否有明显增长或者异常，这需要跟往常的指标进行对比，发现其波动是否在合理范围之内，如果想知道app跟竞品的差异，可以跟行业的一个水平进行对比，从而对自身产品所处的行业位置进行一个评估，从而采取相应的营销策略来进行巩固或者竞争。（不过好像也不太可能拿到竞品的数据？？这是一个值得思考的问题）
从购买行为纬度，主要是复购率、回购率、支付率这类指标，复购率有不同的计算公式，在本次分析中，复购率指的是在一段时间内，多次购买的人数占总购买人数的比例，这反映出平台的健康度和忠诚度，当然用户也有可能在一天内下单多次算不算复购，有不同的统计口径，但是对于一个团队来说，统一口径的数据就是有效度的。复购率高说明可以从产品、平台、以及消费者的纬度进行解释，无论如何，提升复购率需要综合性的营销策略，回购率是指在两段时间内均发生购买行为的人数占总购买人数的比例，回购率能够从较长的时间维度上反映出客户的忠诚度，一般而言针对不同的品类可能会从月度或者季度的时间尺度上来衡量用户的回购率。支付率是下单人数占/总浏览量的比例，在这里我将之理解为，我看了两次，但是我其中只买了一次，支付率就是50%，当然随着我看的次数的增加，我下单的概率其实都不能确定，因此可以把每次的我都算成一个新的人，所以我认可支付率这个指标的构建方式。
当然从浏览到支付中间会经历：首页—商品详情页—加入购物车—提交订单—支付订单的一个行为过程，虽然本分析中缺乏一些相关的数据，但是我们也不难发现，针对每个环节可能存在的导致用户流失或者终止购买行为的因素都要进行一个精细化的分析，并通过运营来提升相关指标，最终实现各环节转化率的提升，数据分析的意义就在于针对每次运营的效果进行一个量化的评估，并且不断提出新的假设，实践，并在下一次的执行通过数据中得到验证，这是一个非常典型的闭环与迭代的思维。这也说明，数据分析不能脱离业务而存在，因为只有懂业务，才能够提供真正有效的解决办法。
（四）从主体纬度来看
对用户进行分层的意义在于，针对不同的用户进行采取不同的运营策略，发掘各类用户的价值，从而实现综合效益最大化，rfm模型是一个常用的客户管理模型，但是需要结合具体的业务和数据进行指标的构建，在不同的业务场景中，各个维度的权重也是不同的。
6、其他：
遇到的报错信息，没有解决，绕着走，后来发现，如果代码语句过于复杂的时候，就会产生这个信息，这时候只需要把代码进行简单化拆分即可。
本项目作为一个新手练习，整体的分析框架和思路都借鉴了这篇博客，但也经过个人的探索，思考了一些东西，在此表示感谢：
