用户行为数据分析（下）——SQL+Excel

A. 导入数据到MySQL数据库中

B. 数据一览：

C. 分析工具：MySQL Workbench + EXCEL

D. 分析思路：

1.分析用户使用户行为的漏斗模型

a.APP访问用户总数（UV) & 页面总访问量（PV）

b. 跳失率=只点击一次浏览的用户数量/总用户访问量

在统计时间内，只有1人只浏览过一个页面就离开了APP，可以忽略不计，说明淘宝拥有足够的吸引力让用户停留在APP中。

c. 用户各类行为总数：

用户一般购买路径包括几种情况：

• 浏览→购买
• 浏览→加购/收藏→购买

从购买路径分析，收藏或加入购物车并无直接的先后顺序之分，两种行为都是在浏览到购买这一阶段内确定商品购买意向，所以将收藏与加购合并为同一阶段来研究；
从总体上来看，pv/buy约为45，说明需要有45次pv才能转化成1次buy

d. 独立行为用户漏斗模型计算：

上图是各类行为的用户数，buy/pv≈68%的付费率，超过2/3的用户进行了下单购买行为，可见淘宝用户的付费转化率相当高

e.用户购买次数分析

用户购买次数区间为1~72，由于数据统计期只有9天，购买次数在<=5次的为主，占比87.94%

2.不同时间尺度下用户行为模式分析

分别以每天和小时为单位分析用户购买行为，找出用户活跃规律。

a.分析每天的用户行为

如上图，由于12月2号和3号是周末，四个指标都明显上升，这也符合常规预期，说明在节假日用户有更多时间使用APP，从而促使点击和购买等行为的增多。

b.分析以小时为单位的用户行为

从上图可知，用户的每小时行为趋势还是相当符合人们的作息时间规律的，10:00~17:00上班时间，各项行为都处于较为平稳的状态，18:00左右下班时间由于通勤等因素，出现了明显下降波动，而后进入晚间的非工作时间，各项用户行为数量激增，直到十点后进入睡眠时间，热度开始逐步下降

从每小时人均行为数一图可以看出，一天中从17:00到第二天凌晨1点左右，用户的行为数高于平均水平，说明这一时间段是用户操作最频繁的时间，可在此时间段进行增加商品推送或促销等策略。

从点击和购买占比分析可以看出：

• 对照前一分析，晚上7点到凌晨一点的晚间时间段，用户的操作行为偏向于浏览。
• 用户在白天特别是在中午时间段，购买行为比率达到最高，可见，在午间时间段的用户使用淘宝下单购买商品的意向是最高的。

3.不同商品种类的用户行为分析

a. 购买次数分析

本次分析的商品种类有804825种，只购买一次的产品占82.65%，购买两次的产品占11.52%，没有出现购买用户数量非常集中的商品

• 说明商品售卖主要依靠长尾商品的累积效应，而非爆款商品的带动。
• 从另一角度看，说明淘宝上商品数量足够多，以致使用户可以有更多的选择，而不用局限于单一爆款

b.转化分析

SQL查询出点击量TOP100的商品，对照购买数，从中得出转化率最低的30种商品，说明这些吸引用户更多注意力的头部流量的商品并没有很好的转化为实际销量，需要及时找到问题根源所在并改进。

客户价值RFM模型分析

R-最近一次消费 (Recency)

F-消费频率 (Frequency)

M-消费金额 (Monetary)（数据中无消费金额，故此项不参与分析）

R_score 最近一次消费时间越近，分数越高
F_times 用户购买次数统计，次数越多，说明该用户价值越高

计算平均数：

依据两项平均数进行用户类型划分：

用户类型数量SQL统计：