用python的dictreader得出的数据_用SQL解决用户行为分析

一、背景介绍

在电商行业普及率相当高的今天，人们早已适应从互联网上获取商品，消费模式由线下转到线上。在消费者尚未了解自己想要什么之前，互联网早已根据众多消费者和个人消费者的消费行为得出结论，分析消费行为的结论反作用于消费者，以促进消费行为，提升购买力成为各大电商的竞争力。

二、提出问题及分析思路

Q1：用户在一天中的浏览习惯和在一周内的浏览习惯有什么特征？

Q2：该平台最能吸引新用户的是什么产品？

Q3：消费者不同的行为如何转化为消费，减少客户流失？

Q4：对于不同层级的用户如何提升客户体验和服务？

Q5：该平台最具竞争力的产品有哪些？

三、数据描述

User Behavior是阿里巴巴提供的一个用户行为数据集，用于隐式反馈推荐问题的研究

数据集包含2017/11/25至2017/12/3之间（双十二购物节前期），约100万随机用户的行为。由于数据量较大，在此次分析中仅采用10万条数据进行分析，在navicat中进行数据导入时输入最后一行数据行：100000

数据集的每一条表示一条用户行为，包括：用户ID,商品ID,商品类目ID,行为类型,时间戳，以逗号分隔

1. 用户ID：整数类型，序列化后的用户ID
2. 商品ID：整数类型，序列化后的商品ID
3. 商户类目ID：整数类型，序列化后的商品所属类目ID
4. 行为类型：字符串，枚举类型，包括——'pv','buy','cart','fav'
5. 时间戳：行为发生的时间戳

行为类型

1. pv：商品详情页pv,等价于点击
2. buy：商品购买
3. cart：将商品加入购物车
4. fav：收藏商品

数据量说明

1. 用户数量：987994
2. 商品数量：4162024
3. 商品类目数量：9439
4. 所有行为数量：100150807

四、数据清洗

导入10 w条数据后，选择英文作为table名和column名可以避免在查询语句中输入中文字符导致错误，当将数据导出到Excel完成数据可视化分析时，需要注意将数据类型转化为数字

1.计算缺失值

根据查询，可见表中不存在缺失值，进行下一步，如存在缺失值则需判断缺失值所占整体数据的比重

2.删除重复值

根据业务需求进行判断是否需要对表的部分或全部进行去重处理，因该表用于分析消费者行为，无需去重处理

3.一致化处理

将时间戳timestamp转换为日期格式，由于直接更改column数据类型会导致报错，因此使用 from_unixtime() 函数

from_unixtime() 函数将timestamp列转换为：年-月-日 时:分:秒，年月日和时分秒中间用空格隔开

根据实际需要在timestamp列分别截取日期和时间，并另存为新的一列

atler table 更改表结构，增加date和hour两列

update table 更改表数据，使用substring()函数截取数据填充到新增加的date列和hour列中

substring(your_string,from,for)从第6个字符开始截取5个字符保存在date，从第12个字符开始截取2个字符保存在hour

4.异常值处理

因为数据来源于2017/11/25至2017/12/3之间，现查找处于该日期外的数据并删除（共44条）

五、数据分析及可视化

1. 流量行为分析：

• 按日/时统计pv、uv、pv/uv

例：同一天内的同一IP访问商品详情页的次数，随着访问次数的增加，pv累计计数，而uv则只计数一次

uv： unique visitor （day/hour）

pv：page view （day/hour）

pv/uv转化率：当一个IP多次访问同一商品详情页时，则意味着消费者的购买意向强烈

• 得出结论：pv和uv的变化趋势接近相同，都在12-2到达峰值，而pv/uv的转化率在12-1日达到峰值，（11-27至12-1为工作日），在此期间访问量偏低，而在（12-1至12-2）访问量持续攀升，查询可知是由于双十二大促的预售活动导致访问量的激增

• 得出结论：消费者在凌晨至六点的点击量处于波谷（休息时间），在一天中的20点至23点（工作日下班后），点击量处于波峰，用户处于活跃阶段，可在此时段投放产品预售活动或产品直播，刺激消费者购买

• 留存率

首先整合user表，计算出每个用户的任意行为（fav,cart,buy,pv），然后计算出每个用户首次出现任意行为的时间，用user中每条数据和最早行为时间做对比以求出时间差，最后将首次时间分组，求N日留存：N天人数/首次人数

• 得出结论：以留存间隔为横坐标（有任意行为的最早时间），以研究日期对留存的影响。可以发现，不同间隔的留存率均呈现同一种变化状态：整体呈逐日递减趋势，第三日留存趋势不同与整体趋势不同（未查明原由，暂且忽略），其余均在倒数第三天到达高峰，为12-1，该日的活动导致用户活跃

2.用户画像——用户购买行为

• 首次归因：根据消费者首次购买商品类型进行分析，得出网站最能吸引用户的产品，以便在后续的个性化推送及促销活动中准确定位用户，以提高用户购买率，同时可用于与同类型电商平台做对比，提升平台产品竞争力

• 得出结论：1464116和2735466类型产品在吸引新用户方面具有竞争力

• 行为统计：根据每日用户的点击量、加购量、收藏量、购买量进行统计

• 得出结论：pv的数量级远高于 cart，fav和buy,所以pv以主坐标显示，其余均以副坐标显示。变化趋势均趋近相同，在12-2日到达峰值，可以看到当日的活动非常具有吸引力

• AARRR漏斗模型分析行为转化率（用户流失）

消费者行为：pv、buy、cart、fav，无论有哪些行为最终我们想都想达到购买的目的,但每一环节都存在流失，统计该数据目的是为了减少用户在该环节的流失

pv,cart和fav都是以buy为导向，如何减少在这个过程中购买的流失，可以由上图得知，点击转化为购买远低于收藏或加购转为购买，而收藏加购后的流失率占比相当大，如何减少才此环节的流失，我们可以在以下几个方面进行分析：

Q1.收藏还是加购哪种行为更能提高转化？

首先因为购物车相对收藏更加明显看到价格波动和产品活动，其次购物车有产品数量限制，更少的产品留在购物车中，更能被用户看到

通过用户行为，不仅应该对已购买商品用户，发送活动促销通知，也因该对加购/收藏产品进行推送，以提高转化率

• 得出结论：可明显看到用户的习惯更倾向于将商品加到购物车而不是收藏夹

Q2.假设产品推送是否为用户需要产品？（根据用户浏览和购买可知）

• 得出结论： 由销量TOP10和浏览TOP10数据可知，重合度达到50%，说明推送产品转化为购买的粘合度一般，应该采取多种形式以提高转化率

• 复购率

复购率=有复购行为用户数/有购买行为用户数=65.87%

• 得出结论：用户的复购率高达65.87%，说明该平台的粘合度很高，平台具有很强的竞争力

用户画像——用户价值分析

• 用户分级RFM模型

RFM模型：用于对用户分级，根据现有数据，可计算获得R（最近一次消费的时间间隔）和F（消费频率），但已有数据中无法获得消费金额（M）

首先计算出每个有buy行为用户的R,F，并根据得出的数据找到R,F的最大值，用于给不同区间的R,F进行评分，因所有数据的时间均在（2017-12-04）之前，所以R值以（2017-12-04）为基准进行计算，且R越大分值越低（最近一次的购买时间距离现在的时间越长）

根据R,F评分机制，计算出每个用户的R,F值，并分别计算R,F的平均分，高于平均分的用（R低/F高）代替，低于平均分的用（R高/F低）代替

最后计算用户分级后（价值用户，保持用户，发展用户，挽留用户）所对应的人数

• 得出结论：

价值用户占比最低5%，该类用户购买力强，忠诚度高，可提供该类型用户更高水平的服务，以增加用户满意度，如：高级客服服务和专享礼券等；

保持用户占比最高61%，该类用户的消费频率高，但距最近一次交易时间较长，可对该类型用户通过短信发放消费券，以促进购买

发展用户占比10%，该类用户的购买率低，可根据该类型用户的购买习惯和浏览记录进行个性化推荐，以促进消费；

挽留用户占比24%，该类型用户的购买率低且距最近一次消费时间较长，可对该类型用户提供免费的VIP体验服务和vip体验礼券（限时），以提高用户粘合度

• 用户在一段时间内的购买频率

• 得出结论：根据复购分布图可知，消费者的复购排名最高的是当天，最低的是第八天，说明该平台在短时间内吸引消费者产生复购行为的可能性很大，用户粘合度高

• 商品点击量和购买量（得到835条数据）

• 得出结论：由散点图可知多数产品的点击量和购买量都偏低

• 畅销产品TOP10

畅销前十的产品：由于分析数据较少，单一产品的购买量较低

• 点击产品类型TOP10

• 畅销产品类型TOP10

• 得出结论：在购买前十产品类型和点击前十产品类型中，都存在的产品类型有：4145813,4801426,3002561,4756105,3607361，重合度有50%，该类产品具有很强的竞争力，更能吸引消费者，并且购买前十的转化率要高于浏览前十的转化率

六、合理化建议

• 根据用户行为时间分析：根据不同时间段/时间周期用户行为模式的不同，采取针对性的推送和促销手段，提升用户与平台的粘合度
• 根据消费行为分析：在用户发生首次购买行为后，定时通过客服/短信发放特殊优惠，以提高复购率，关注流失率高的环节，持续优化
• 用户价值分析：根据不同用户进行分级，采取不同方案，对于购买率高但购买力不足的用户给予适当优惠，对于优质用户提供个性化服务
• 商品分析：根据双十二前期的预售活动得到销量和点击量高的产品的促销手段，也适用于日常的小型促销/直播中，以刺激消费者购买