如何针对企业产品进行有效分析，看大佬用SQL分析淘宝用户行为

　　分析背景
　　淘宝网，是全球最受欢迎的网购零售平台之一，拥有近5亿的注册用户数，每天有超过6千万的固定访客，同时每天的在线商品数已经超过了8亿件，平均每分钟售出4.8万件商品。
　　作为电商行业的标杆，淘宝网的用户行为数据，一定程度上反映了用户的购买行为规律。本项目利用MySQL对淘宝用户行为数据进行分析，探索不同用户的消费习惯，结合店铺营销策略，以实现精准化运营，减少运营成本，增加店铺营收。 分析目标与思路
　　探索目标 ：每日不同时段流量走势，质量如何？用户行为转化情况怎样？如何提高留存、增加复购？如
　　何判断高价值用户，针对不同用户如何进行个性化营销？
　　分析思路 ： 流量指标分析：对比每日每小时的PV（访问量）、UV（访客数）、平均访问量（PV/UV），在不 同时间发布不同的营销活动，寻找更优质的拉新渠道。 行为转化分析：统计用户不同行为的转化情况，简化交易流程，提高转化率。 消费偏好分析：根据热销商品和热销类目，选定营销主题。 用户价值分析：对用户进行价值分层，针对不同层级的用户制定不同的营销策略。 数据准备
　　1.数据获取
　　数据集来源于淘宝APP移动端，2014年11月18日至2014年12月18日的用户行为数据。
　　2.数据理解
　　表中有7个字段：用户ID、商品ID、商品类目ID、用户行为类型、用户所在地理位置、用户行为时间、金额，一共有12256905条数据。限于电脑性能，仅提取前1048575条记录用作分析。
　　pv：点击，用户访问商品详情页。必须是商品详情页的流量，淘宝网首页、搜索结果页等页面的点击流量不算在内。 fav：收藏，用户将商品加入收藏夹，便于下次访问。收藏夹中的商品不可直接购买，必须再次发生＂点击＂行为后，在商品详情页方可购买。 cart：加购，用户将商品添加到购物车。用户可从购物车直接购买商品，无须再次产生＂点击＂行为。 buy：下单，用户点击提交订单购买商品。
　　3.数据清洗
　　3.1建表导数： create database taobao;
　　use taobao;
　　create table UserBehavior(
　　user_id int,
　　item_id int,
　　item_category int,
　　behavior_type varchar(10),
　　user_geohash varchar(10),
　　times datetime,
　　amount decimal(5,2)
　　);
　　load data infile ＂C:/ProgramData/MySQL/MySQL Server
　　8.0/Uploads/UserBehavior.csv＂
　　into table UserBehavior
　　fields terminated by ＂,＂
　　ignore 1 lines;
　　3.2缺失值处理：
　　查询结果中发现user_geohash字段存在缺失值，缺失比例超过50%。缺失严重的数据在后续分析中意义不大，可做删除处理。
　　3.3异常值检查
　　检查发现该数据集中不存在异常值。
　　3.4重复记录处理
　　该数据集有86478条重复记录，筛选不重复的记录共962097行。
　　3.5字段处理：
　　根据times字段增加计算字段用户行为日期、周和小时，排除后续分析不需要的user_geohash字段，并将筛选后的结果保存到新表。
　　清洗后的数据集共962097条记录，展示了2014-11-18至2014-12-18这一个月内，8477位用户对7095个类目下701609个商品产生的902050次点击，29016次加购，20930次收藏，10101次购买行为。 数据分析
　　1.流量指标分析 浏览量PV：页面浏览量或点击量Page View，用户每打开一个网页就记录1次PV，用户多次访问同一页面PV累计多次。 访客数UV：访问页面的独立访客Unique Visitor，用户访问该页面的一台电脑客户端为一个访客，以浏览器的cookie为依据，同一个用户多次访问只计算一个UV。 人均浏览量PV/UV：平均每个独立访客的浏览次数，体现网站对用户的吸引程度。
　　查询结果中发现浏览量PV、访客量UV和人均浏览量的总体变化趋势是相同的。11月18-29日，PV在25000-28000之间，UV在5800-6200之间，人均浏览量在4.1-4.6之间，成交量在260-320之间，销售额在20000-28000之间；11月30日-12月10日，PV在27000-31000之间，UV在6000-6400之间，人均浏览量在4.4-4.9之间，成交量在280-370之间，销售额在22000-30000之间；12月11日、12日因双12的缘故，PV分别达到了35851、51034，UV分别为6576、7049，人均浏览量分别为5.5、7.2，成交量分别为409、569，销售额分别为32022.71、46100.93；而在双12之后，PV也稳定在28000-30000之间，UV在6000-6200之间，人均浏览量在4.6-4.8之间，成交量在280-340之间，销售额在22000-28000之间，远远优于11月29日之前的数据，说明双12的营销活动对用户行为有显著影响。
　　同时，在11月21、28日，12月5日，浏览量PV、访客量UV、人均浏览量、成交量和销售额都出现了不同程度的下滑。细查发现以上三天都是周五，观察流量的周期性变化中发现，每周二到周四数据比较稳定，周五到周一会出现小幅度下滑，这跟大部分用户的工作和生活习惯息息相关。
　　从每天的时间段来看，21点和22点是用户活跃的高峰时间，人均浏览量在10次以上，22点之后一路下降，一直到凌晨4点降到最低值，4点到10点渐渐回温，10点到18点基本稳定，18点之后开始一路攀升，商家可以集中资源，在用户活跃度较高的时间段采取一些引流手段。
　　2.行为转化分析
　　浏览人数：点击产品详情页的用户数
　　收藏人数：收藏产品产品详情页的用户数
　　加购人数：将产品添加到购物车的用户数
　　购买人数：下单的用户数
　　在所有用户行为数据中浏览产品详情页后产生购买行为的用户只有4330人，占浏览用户的51%，也就是说，还有49%的用户行为是没有转化为成交的，用户在浏览商品详情页后出现了大量的流失。那么从浏览到购买，每个环节的转化率是多少？用户主要是在哪个环节流失的呢？
　　对比发现，从浏览到收藏的转化率仅为38.73%，而浏览到加购的转化率为61.41%。因为收藏和加购行为之间并没有直接的先后关系，且加入收藏后并没有可以下单的入口，如果需要购买必须重新点击商品进入详情页才能下单，所以用户更愿意将感兴趣或有购买意向的商品添加到购物车。
　　在浏览-加购-购买的购物行为路径中，加购转化率为61.41%，购买转化率为83.21%，也就是说大部分的用户更偏向于将商品添加到购物车后下单，可能是为了凑单参加满减优惠。
　　分析每天浏览-加购-购买的转化率情况发现，浏览-加购的转化率变化并不明显，双十二之前稳定在10%-13%之间，仅在双十二当天达到17.26%，且在双十二之后转化率明显下降。而加购-购买的转化率波动较大，在感恩节、服饰焕新和双十二活动前后分别达到了45.48%、44.21%、42.81%，而双十二之后的数据表现也明显优于双十二之前，说明部分商品在双十二之后依然有返场活动，促进用户消费。
　　3.消费偏好分析
　　根据二八法则即20%的产品贡献了80%的销售额甚至更多，将产品的贡献定量分析，哪些类目的商品贡献了多少的消费额。
　　在2066个商品类目中，销售额排名前623个类目贡献了646009.09的销售额，可集中人力和财力优化这些类目的商品及渠道。
　　4.用户价值分析
　　通过对用户价值的细分，进行差异化的精细运营，从而提升运营效率和用户体验。RFM模型是衡量客户价值和客户创利能力的重要工具，通过客户的消费时间间隔（Recency）、消费频率（Frequency）以及消费金额（Monetary）三项指标来描述客户的价值状况。 -- 查询每个用户消费时间间隔、消费频次、消费金额
　　+-----------+--------------+----------+----------+----------+
　　| user_id | 最近消费时间 | 间隔天数 | 购买次数 | 消费金额 |
　　+-----------+--------------+----------+----------+----------+
　　| 101260672 | 2014-11-20 | 29 | 1 | 72.55 |
　　| 116730636 | 2014-12-18 | 1 | 4 | 372.04 |
　　| 104811265 | 2014-12-09 | 10 | 1 | 120.71 |
　　| 106230218 | 2014-12-16 | 3 | 3 | 132.73 |
　　| 100684618 | 2014-12-12 | 7 | 3 | 203.67 |
　　| 103802946 | 2014-12-18 | 1 | 2 | 139.38 |
　　| 103891828 | 2014-12-15 | 4 | 6 | 496.24 |
　　| 116678892 | 2014-11-23 | 26 | 1 | 129.20 |
　　| 106557109 | 2014-11-22 | 27 | 1 | 38.06 |
　　| 104221274 | 2014-11-30 | 19 | 2 | 138.21 |
　　+-----------+--------------+----------+----------+----------+
　　-- RFM评分
　　+-----------+-------+-------+-------+
　　| user_id | R评分 | F评分 | M评分 |
　　+-----------+-------+-------+-------+
　　| 101260672 | 1 | 1 | 1 |
　　| 116730636 | 5 | 4 | 4 |
　　| 104811265 | 4 | 1 | 2 |
　　| 106230218 | 5 | 3 | 2 |
　　| 100684618 | 4 | 3 | 3 |
　　| 103802946 | 5 | 2 | 2 |
　　| 103891828 | 5 | 5 | 5 |
　　| 116678892 | 1 | 1 | 2 |
　　| 106557109 | 1 | 1 | 1 |
　　| 104221274 | 2 | 2 | 2 |
　　+-----------+-------+-------+-------+
　　-- RFM均值
　　+------------+------------+------------+
　　| avg(R评分) | avg(F评分) | avg(M评分) |
　　+------------+------------+------------+
　　| 3.5984 | 2.1039 | 2.2051 |
　　+------------+------------+------------+
　　-- RFM用户价值
　　+-----------+-------+-------+-------+-------+-------+------+------------+
　　| user_id | R评分 | F评分 | M评分 | R程度 | F程度 | M程度 | 用户价值 |
　　+-----------+-------+-------+-------+-------+-------+------+------------+
　　| 101260672 | 1 | 1 | 1 | 低 | 低 | 低 | 一般挽留用户 |
　　| 116730636 | 5 | 4 | 4 | 高 | 高 | 高 | 重要价值用户 |
　　| 104811265 | 4 | 1 | 2 | 高 | 低 | 低 | 一般发展用户 |
　　| 106230218 | 5 | 3 | 2 | 高 | 高 | 低 | 一般价值用户 |
　　| 100684618 | 4 | 3 | 3 | 高 | 高 | 高 | 重要价值用户 |
　　| 103802946 | 5 | 2 | 2 | 高 | 低 | 低 | 一般发展用户 |
　　| 103891828 | 5 | 5 | 5 | 高 | 高 | 高 | 重要价值用户 |
　　| 116678892 | 1 | 1 | 2 | 低 | 低 | 低 | 一般挽留用户 |
　　| 106557109 | 1 | 1 | 1 | 低 | 低 | 低 | 一般挽留用户 |
　　| 104221274 | 2 | 2 | 2 | 低 | 低 | 低 | 一般挽留用户 |
　　+-----------+-------+-------+-------+-------+-------+------+------------+
　　对用户进行价值分层后，针对不同价值类型的用户实行不同的营销策略。
　　一般挽留用户占比34.36%，占比最高，其交易时间间隔长，交易频率低，消费金额低，存在流失风险，可以及时与用户取得联系，明确流失原因或了解用户需求，想办法挽回用户。
　　一般发展用户占比31.29%，占比排名第二，其交易时间间隔短，但消费频率和消费金额都很低，可以利用推荐系统推荐其平时浏览的同类商品，或与此类客户有相同购买属性人群购买的商品，发送满减优惠券等，避免用户流失。
　　重要价值客户占比23.86%，其交易时间间隔短，消费频率高，消费金额高，应加强交流与互动，深入了解用户需求，提供个性化服务，增加用户粘性。可以对该类用户提供VIP服务机制，提升用户体验与忠诚度。 总结和建议
　　根据流量数据指标分析，每天的18-22时是用户活跃高峰期，可集中资源在该时间段进行引流与营销活动，如推送爆款及畅销商品，同时发送商家折扣优惠、直播带货及促销活动消息，提高商品购买率。
　　从用户行为转化漏斗分析，浏览到加购的转化率远远高于浏览到收藏的转化率，且加购到购买的转化率高达83.21%。交易流程越多，用户流失的可能性就越大，瞬间购买欲望的涌现，往往时效性较短，每多一步流程，就多一份流失率。所以删除不必要的跳转界面，让交易的完成时间更短，不要给用户过多的犹豫时间。例如优惠券的选择，还需要繁琐的领取步骤，满足优惠条件默认领取相应最大的优惠，并在最后付款界面突出优惠券带来的满减信息，促使用户的成交花费时间更短。关于购物车，可以增加一个清理机制，比如加入购物车以后多少天不成交就清除，每隔一段时间就提醒用户购物车内还有什么商品未下单，什么时间商品将被清除。收藏和购物车的功能有一定的同质性，关于这一点，淘宝没有做明显的区分，导致购物车只是简便版的收藏。反观同电商领域的拼多多，没有设置购物车功能，并且成交环节缩短至4步。
　　根据用户消费偏好分析，将流量更多地聚焦于畅销的商品，通过明星效应/网红推荐，结合主题活动和节日，打造爆款的产品，并利用爆款产品带动整体商品的销售。
　　根据RFM用户价值分类结果，一般挽留用户存在流失风险，可适当给予折扣或捆绑销售政策，通过一些补贴优惠，培养用户的购买习惯。一般发展用户存在价值潜力，要想办法主动联系客户，如短信、邮件、push提醒等方法，进行好物推荐等，提高用户的复购率。重要价值客户其交易时间间隔短，消费频率高，消费金额高，应加强交流与互动，比如优化个人信息界面，加入成长值系统，到达一定等级，享受一些特权或优惠，可参考腾讯游戏的心悦会员制度。