淘宝用户行为分析(Mysql)

2020-11-15 本文已影响0人 kaka22

数据背景

来源：阿里移动推荐算法
背景：2014年是阿里巴巴集团移动电商业务快速发展的一年，例如2014双11大促中移动端成交占比达到42.6%，超过240亿元。相比PC时代，移动端网络的访问是随时随地的，具有更丰富的场景数据，比如用户的位置信息、用户访问的时间规律等。
这个比赛的目的是：要使用训练数据(2014年11月18日至2014年12月18日)建立推荐模型，并输出用户在接下来一天(2014年12月19日)对商品子集购买行为的预测结果。
而我的目标则是从该数据进行随机抽样，并用mysql进行分析，提高自己对电商指标体系的认识。

数据详情

tianchi_mobile_recommend_train_user命名为USER表：

user_id，用户标识，抽样&字段脱敏
item_id，商品标识，字段脱敏
behavior_type，用户对商品的行为类型，包括浏览(1)、收藏(2)、加购物车(3)、购买(4)
user_geohash, 用户位置的空间标识，可以为空, 由经纬度通过保密的算法生成
item_category, 商品分类标识, 字段脱敏
time, 行为时间, 精确到小时级别

提出问题

分析用户使用APP过程中的常用电商指标，了解运营现状，查看各个环节的流失率，并找到需要改进的环节。
研究用户在不同维度下的行为规律，了解用户行为特征，优化运营策略。
研究用户的价值、针对不同价值的用户进行精细化运营
研究用户生命周期，针对不同周期的用户采取不同的策略

电商指标

我对电商指标的理解，是基于六个维度的：

用户
商品
商品类别
用户行为
行为地址
时间

基于用户和商品、商品类别可以分析用户整体的购买偏好。
基于用户和用户行为可以分析PV、UV、PV/UV、跳失率、总订单量、用户行为之间的转化率等。
基于用户和时间可以分析用户购买的时间偏好。
基于商品类别和用户行为可以分析不同商品类别的转化率的差异。
基于商品类别和时间可以分析不同商品类别的热销时间段。
基于用户行为和时间可以分析用户的行为特征。
基于时间和别的字段，结合RFM模型可以给用户价值打标签。
基于AARRR模型，可以分析用户的生命周期，划分不同用户所处的周期阶段。

数据的导入及清洗

将csv导入mysql的方法：

命令行：关于如何使用命令提示符将CSV文件导入MySQL
python：将大csv文件导入mysql数据库
下面的实例用python抽取100000条数据，并导入mysql的数据库：taobao。

from sqlalchemy import create_engine
import pandas as pd
import pymysql
import numpy as np
#读取数据
data = pd.read_csv('user.csv',encoding = 'gbk')
data = data.sample(n=1000000)
#创建连接数据库对象
engine = create_engine('mysql+pymysql://用户名:密码@localhost/数据库名?charset=utf8')
#存入数据库
data.to_sql('数据库表名字',engine)

清除空值

数据介绍中说明只有user_geohash中存在空值(非空比例在35%左右)且经过加密处理，无法对该字段进行分析，因此，直接删除处理。

#查看user_geohash字段的非空比例
SELECT COUNT(u.`user_geohash`)/(SELECT COUNT(*) FROM USER) AS 'user_geohash字段的非空比例' 
FROM USER AS u
WHERE u.`user_geohash` IS NOT NULL;

#由于这列存在大量的空值且经过加密处理，故删除此列
ALTER TABLE USER DROP COLUMN user_geohash;

查找重复数据

这里的数据有重复值也是可以理解的，因为记录行为的最小粒度为小时，同一用户同一个行为在同一小时内是可能存在多次的，因此这里不去重处理。

SELECT *, COUNT(*)
FROM USER AS u
GROUP BY u.`user_id`, u.`item_id`, u.`behavior_type`, u.`time`
HAVING COUNT(*) > 1;

查看数据是否存在异常

检查数据的时间范围和行为数据的类别是否为4个即可。

# 检查时间是否异常
SELECT MIN(u.`time`) AS '时间起点', MAX(u.`time`) AS '时间终点'
FROM USER AS u;

# 检查用户行为类别是否异常
SELECT DISTINCT u.`behavior_type`
FROM USER AS u;

缺失值检查

# 检查缺失值
SELECT COUNT(u.`user_id`), 
       COUNT(u.`item_id`), 
       COUNT(u.`item_category`), 
       COUNT(u.`behavior_type`),
       COUNT(u.`time`)
FROM USER AS u

进一步处理

将日期和时间进行分离

#添加列：alter table 表名 add column 列名 varchar(30);
ALTER TABLE USER ADD COLUMN `date` VARCHAR(20) NOT NULL AFTER `time`;
#更新列：UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]
UPDATE USER
SET DATE = TIME;
#将date转为年月日
UPDATE USER
SET DATE = DATE_FORMAT(DATE, '%Y-%m-%d');
#将time转为小时
UPDATE USER
SET TIME = DATE_FORMAT(TIME, '%H');
#检查一下转化结果
SELECT *
FROM USER AS u

将用户行为数据进行替换： 1：pv 2：fav 3：cart 4：buy

UPDATE USER 
SET `behavior_type` = (CASE behavior_type
                 WHEN 1 THEN "pv"
                 WHEN 2 THEN "fav"
                 WHEN 3 THEN "cart"
                 WHEN 4 THEN "buy"
                 ELSE "other"
                 END);

感觉写的没问题但是一直报错，查了一下stackoverflow发现原来py导入，各字段的格式并不是我们想要的格式，需要进行修改。注意：字段的类型一般不要修改，一定要谨慎。

DESC USER;
#alter table 表名 modify column 字段名 类型;
ALTER TABLE USER MODIFY COLUMN behavior_type VARCHAR(20);

验证结果

SELECT *
FROM USER AS u;

指标的构建

1. 总体运营指标

① 流量指标

计算页面访客数(pv)、独立访客数(uv)、人均点击数(uv/pv)

# uv  pv pv\uv
SELECT COUNT(u.`user_id`) AS 'pv',
       COUNT(DISTINCT u.`user_id`) AS 'uv',
       COUNT(u.`user_id`)/COUNT(DISTINCT u.`user_id`) AS 'pv\uv',
       COUNT(u.`user_id`)/(COUNT(DISTINCT u.`user_id`)*30) AS '日人均点击次数'
FROM USER AS u
WHERE u.`behavior_type` = 'pv';

页面访客：942253次、独立访客数：9922位、人均点击次数95次。
95/30≈3.2次，日人均点击次数大概为3次/人/天

②每日流量指标变化趋势

# 计算每天的uv  pv pv\uv
SELECT u.date AS '日期',
       COUNT(u.`user_id`) AS 'pv',
       COUNT(DISTINCT u.`user_id`) AS 'uv',
       COUNT(u.`user_id`)/COUNT(DISTINCT u.`user_id`) AS '人均页面访问数'
FROM USER AS u
WHERE u.`behavior_type` = 'pv'
GROUP BY u.date
ORDER BY u.date ASC;

导出数据

# 后边加
INTO OUTFILE '/daily_uvpv.csv'
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"' 
LINES TERMINATED BY '\n';

需要更改配置什么的，一般情况下是不会存在导出数据的，因此，直接用sqluog的导出功能了。

可视化分析趋势

pv、uv指标呈正相关性；
三个指标在大部分时间走势是稳定的，从2014-12-11开始上升，到2014-12-12达到峰值，2014-12-13结束回到正常水平。
双十二活动的影响无疑是明显的。

AARRR漏斗转化率

①计算每个用户行为的pv

# 用户行为的pv
SELECT u.`behavior_type`, COUNT(u.`behavior_type`) AS 'behavior_count'
FROM USER AS u
GROUP BY u.`behavior_type`
ORDER BY behavior_count DESC;

由于在购物环节中，收藏和加入购物车是没有先后之分的，这两个环节可以放在一起，因此转化率只有浏览-加入购物车/收藏、收藏-购买。

从转化率来看：浏览-收藏/加入购物车的转化率仅为5%、收藏\加入购物车-购买的转化率也只有20%，也不是太高，说明有非常多的用户在收藏和加入购物车之后并没有真正的购买。
从占比来看：总体收藏的占比为2.11%，总体加入购物车的占比为2.98%，总体购买的占比仅有1%，说明有非常多的用户在浏览后没有进行下一步操作，有非常多的无效点击；

② 按照独立访客计算漏斗转化率

# 用户行为的uv
SELECT u.`behavior_type`, COUNT(DISTINCT u.`user_id`) AS 'behavior_count'
FROM USER AS u
GROUP BY u.`behavior_type`
ORDER BY behavior_count DESC;

用户从浏览到收藏/加入购物车的转化率很高，为94.15%，说明用户有浏览后收藏/加入购物车的习惯。但是从收藏/加入购物车到购买的转化率仅为38.45%，是用户流失发生的主要环节。

订单指标

① 成交量

总体成交量与人均购买次数

# 总体成交量与人均购买次数
SELECT COUNT(u.`behavior_type`) AS 成交总量
FROM USER AS u
WHERE u.`behavior_type` = 'buy';

每日成交量与人均购买次数走势

# 每日成交量与人均购买次数走势
SELECT u.`date`, 
       COUNT(u.`user_id`) AS '日成交量',
       COUNT(DISTINCT u.`user_id`) AS '日用户数',
       COUNT(u.`behavior_type`)/COUNT(DISTINCT u.`user_id`) AS '人均日购买次数'
FROM USER AS u
WHERE u.`behavior_type` = 'buy'
GROUP BY u.`date`
ORDER BY u.`date` ASC;

订单在大部分时间是保持平稳的，而在2014-12-12这天有爆发的增长，造成这一现象的原因是双十二电商大促活动，与前面的流量走势相结合进行分析，可以得到互相印证。
而且这一个月内，平均一个用户购买了2.1次，每天的订单量和流量指标是一致的。

② 复购率

总体的复购率

# 复购人数
SELECT COUNT(t.cnt) AS '复购人数'
FROM (SELECT u.`user_id`, COUNT(u.`user_id`) AS cnt
FROM USER AS u
WHERE u.`behavior_type` = 'buy'
GROUP BY u.`user_id`
HAVING cnt > 1) AS t;

#复购率
SELECT SUM(IF(t.cnt = 1, 0, 1))/SUM(IF(t.cnt IS NULL, 0, 1)) AS '复购率'
FROM (SELECT u.`user_id`, COUNT(u.`user_id`) AS cnt
FROM USER AS u
WHERE u.`behavior_type` = 'buy'
GROUP BY u.`user_id`) AS t;

复购人数为2268人，复购率为49.51%。

商品品类复购排行榜

SELECT u.`item_category`, COUNT(*) AS '购买次数'
FROM USER AS u
WHERE u.`behavior_type` = 'buy'
GROUP BY u.`item_category`
ORDER BY 购买次数 DESC
LIMIT 10;

可惜这里的商品类别是经过脱敏的，否则可以进行研究和下钻，优化商品结构。

用户复购排行

SELECT u.`user_id`, COUNT(*) AS '购买次数'
FROM USER AS u
WHERE u.`behavior_type` = 'buy'
GROUP BY u.`user_id`
ORDER BY 购买次数 DESC
LIMIT 10;

这些用户对于平台的忠诚度是比较高的，对于平台的价值也是比较高的，可以开发用户信息库、收集详实的用户资料，追踪记录顾客的交易情况，收集用户画像，或者线上组织客户VIP微信群，微信通知系统等，针对这些用户的购买偏好进行更精准的运营。

2. 用户行为特征分析

用户行为时间的特征

① 按日期粒度：

SELECT u.`date`,
       COUNT(*) AS '行为总数',
       SUM(IF(u.`behavior_type` = 'pv', 1, 0)) AS '点击次数',
       SUM(IF(u.`behavior_type` = 'fav', 1, 0)) AS '收藏次数',
       SUM(IF(u.`behavior_type` = 'fav', 1, 0)) AS '加入购物车次数',
       SUM(IF(u.`behavior_type` = 'buy', 1, 0)) AS '购买次数',
       COUNT(DISTINCT u.`user_id`) AS '用户总数',
       COUNT(*)/COUNT(DISTINCT u.`user_id`) AS '人均行为次数',
       CONCAT(ROUND(SUM(IF(u.`behavior_type` = 'pv', 1, 0))/COUNT(*)*100, 2), '%')  AS '点击数占比',
       CONCAT(ROUND(SUM(IF(u.`behavior_type` = 'buy', 1, 0))/COUNT(*)*100, 2), '%')  AS '成交数占比'
FROM USER AS u
GROUP BY u.`date`
ORDER BY u.`date` ASC;

按如期粒度来看，用户或活跃度与总体行为数是正相关的，走势平稳，双十二电商大促这天各项指标暴增，且当天点击数占比有所下降(用户的点击更有针对性), 成交数占比大幅上升。

② 按周的粒度
查询数据发现2014-11-18到2014-11-23,2014-12-15到2014-12-18均不满一周，因此，只取完整的三周进行分析：

SELECT DATE_FORMAT(u.`date`, '%W') AS '星期',
       COUNT(*) AS '行为总数',
       SUM(IF(u.`behavior_type` = 'pv', 1, 0)) AS '点击次数',
       SUM(IF(u.`behavior_type` = 'fav', 1, 0)) AS '收藏次数',
       SUM(IF(u.`behavior_type` = 'fav', 1, 0)) AS '加入购物车次数',
       SUM(IF(u.`behavior_type` = 'buy', 1, 0)) AS '购买次数',
       COUNT(DISTINCT u.`user_id`) AS '用户总数',
       COUNT(*)/COUNT(DISTINCT u.`user_id`) AS '人均行为次数',
       CONCAT(ROUND(SUM(IF(u.`behavior_type` = 'pv', 1, 0))/COUNT(*)*100, 2), '%')  AS '点击数占比',
       CONCAT(ROUND(SUM(IF(u.`behavior_type` = 'buy', 1, 0))/COUNT(*)*100, 2), '%')  AS '成交数占比'
FROM USER AS u
WHERE u.date BETWEEN '2014-11-24' AND '2014-12-14'
GROUP BY DATE_FORMAT(u.`date`, '%W')
ORDER BY DATE_FORMAT(u.`date`, '%W') ASC;

一周中的大部分时间用户活跃度都比较平稳，周五比较特殊，出现了增长。查看数据发现双十二正好是周五，因此是可以理解的。

③ 小时粒度

SELECT u.`time`,
       COUNT(*) AS '行为总数',
       SUM(IF(u.`behavior_type` = 'pv', 1, 0)) AS '点击次数',
       SUM(IF(u.`behavior_type` = 'fav', 1, 0)) AS '收藏次数',
       SUM(IF(u.`behavior_type` = 'fav', 1, 0)) AS '加入购物车次数',
       SUM(IF(u.`behavior_type` = 'buy', 1, 0)) AS '购买次数',
       COUNT(DISTINCT u.`user_id`) AS '用户总数',
       COUNT(*)/COUNT(DISTINCT u.`user_id`) AS '人均行为次数',
       CONCAT(ROUND(SUM(IF(u.`behavior_type` = 'pv', 1, 0))/COUNT(*)*100, 2), '%')  AS '点击数占比',
       CONCAT(ROUND(SUM(IF(u.`behavior_type` = 'buy', 1, 0))/COUNT(*)*100, 2), '%')  AS '成交数占比'
FROM USER AS u
GROUP BY u.`time`
ORDER BY u.`time` ASC;

各项指标呈正相关关系，每天0-5点用户的活跃率快速降低，讲到一天活跃量的最低值，6-10点用户活跃度快速上升，10-18点用户活跃度较为平稳，18-23点用户活跃度快速上升，达到一天的峰值。

结论：晚间用户最为活跃，但用户行为倾向于浏览；白天时段，用户的购买比率为一天内最高的，此时购买的目的性最强。

用户商品偏好特征

SELECT u.`item_category`,
       SUM(IF(u.`behavior_type` = 'pv', 1, 0)) AS '点击次数',
       SUM(IF(u.`behavior_type` = 'fav', 1, 0)) AS '收藏次数',
       SUM(IF(u.`behavior_type` = 'fav', 1, 0)) AS '加入购物车次数',
       SUM(IF(u.`behavior_type` = 'buy', 1, 0)) AS '购买次数',
       CONCAT(ROUND(SUM(IF(u.`behavior_type` = 'pv', 1, 0))/COUNT(u.`behavior_type`)*100, 2), '%')  AS '点击数占比',
       CONCAT(ROUND(SUM(IF(u.`behavior_type` = 'buy', 1, 0))/COUNT(u.`behavior_type`)*100, 2), '%')  AS '成交数占比'
FROM USER AS u
GROUP BY u.`item_category`
ORDER BY 点击次数 DESC;

可以建立点击数-购买数的二维图像选择合适的中心点，将产品分为四个类别：

点击数高，购买数高。说明此类产品刚需比较强，品牌多且种类丰富，用户在较高的需求下有很多的选择；
点击数低购买数高。用户的购买决策十分果断，且对于该类产品的需求量也是很大的，说明该类产品选择性比较小，可能形成几个品牌垄断的情况，或者产品的差异性较小，用户不愿花费过多的精力去挑选。
点击数低购买数低，绝大多数产品都集中在这个象限，这种产品存在很多的替代品，用户很难集中在某个子类进行大量购买，而是跳跃式选购。
点击数高购买数低，这类产品的需求弹性较大，用户购买存在随机性。

用户行为路径上的特征
用户购买商品分为以下几类过程：

浏览后购买
浏览后加入购物车购买
浏览后收藏购买
浏览后收藏并加入购物车购买

CREATE VIEW 用户行为 AS
SELECT u.`user_id`,
       SUM(IF(u.`behavior_type` = 'pv', 1, 0)) AS '点击次数',
       SUM(IF(u.`behavior_type` = 'fav', 1, 0)) AS '收藏次数',
       SUM(IF(u.`behavior_type` = 'fav', 1, 0)) AS '加入购物车次数',
       SUM(IF(u.`behavior_type` = 'buy', 1, 0)) AS '购买次数'
FROM USER AS u
GROUP BY u.`user_id`,
     u.`item_id`,
     u.`date`;

CREATE VIEW 标准化指标表 AS
SELECT uh.`user_id`,
       CONCAT(IF(uh.`点击次数`>=1,1,0),
       IF(uh.`收藏次数`>=1,1,0), 
       IF(uh.`加入购物车次数`>=1,1,0),
       IF(uh.`购买次数`>=1,1,0)) AS '行为路径'
FROM 用户行为 AS uh
WHERE uh.`购买次数` >= 1 AND uh.`点击次数` <> 0;

SELECT a.`行为路径`,
       COUNT(DISTINCT a.`user_id`) AS '用户数'
FROM 标准化指标表 AS a
GROUP BY a.`行为路径`

由于数据是抽取的，这个所得的结果误差较大。但这个结果显示，直接够买的用户远远多于浏览后加购或者收藏再购买的用户。说明，大部分购买者都是喜欢直接购买商品的，而非仔细挑选商品。

用户复购率特征

#先计算每个用户的购买次数
#然后对购买次数进行统计
SELECT tmp.购买次数, COUNT(tmp.购买次数) AS '用户数'
FROM (SELECT u.`user_id`, COUNT(u.`user_id`) AS '购买次数'
FROM USER AS u
WHERE u.`behavior_type` = 'buy'
GROUP BY u.`user_id`
ORDER BY 购买次数 DESC) AS tmp
GROUP BY tmp.购买次数
ORDER BY tmp.购买次数 ASC

这一个月内，用户的购买次数大部分集中在5次以内，开发空间较大。

3.基于RFM模型分析用户的价值

根据数据的情况，这里只能计算R和F
R(Recently): 最近一天的购买时间差(以2014-12-18为基准)
F(Frequency): 近期的购买频率

DROP VIEW IF EXISTS RF的统计视图;
# 创建RF的统计视图
CREATE VIEW RF的统计视图 AS 
SELECT u.`user_id`,
       DATEDIFF('2014-12-18', MAX(u.`date`)) AS R,
       COUNT(u.`user_id`) AS F
FROM USER AS u
WHERE u.`behavior_type` = 'buy'
GROUP BY u.`user_id`;

SELECT *
FROM RF的统计视图

根据直方图的情况：
将R/F分为四组：

0-5、5-12、12-23,、23-30分别对应4、3、2、1分
F值1-3、3-5、5-7、7以上分别对应1、2、3、4分

DROP VIEW IF EXISTS 用户价值打分;
CREATE VIEW 用户价值打分 AS 
SELECT rf.`user_id`,
       (CASE 
       WHEN rf.R BETWEEN 0 AND 5 THEN 4
       WHEN rf.R BETWEEN 5 AND 12 THEN 3
       WHEN rf.R BETWEEN 12 AND 23 THEN 2
       WHEN rf.R BETWEEN 23 AND 30 THEN 1
       END) AS R_score,
       (CASE 
       WHEN rf.F BETWEEN 1 AND 3 THEN 1
       WHEN rf.F BETWEEN 3 AND 5 THEN 2
       WHEN rf.F BETWEEN 5 AND 7 THEN 3
       WHEN rf.F BETWEEN 7 AND 81 THEN 4
       END) AS F_score
FROM RF的统计视图 AS rf;

SELECT *
FROM 用户价值打分;

计算R、F的平均值，确定评分标准

给用户贴上价值标签
重要价值客户：R、F得分都高
重要保持客户：R得分比较高，F得分比较低
重要发展客户：R得分比较低，F得分比较高
一般价值客户：R、F得分都比较低

DROP VIEW IF EXISTS 用户标签表;
CREATE VIEW 用户标签表 AS 
SELECT s.`user_id`,
       (CASE 
       WHEN s.R_score >= 2.78 AND s.F_score >= 1.21 THEN '重要价值客户'
       WHEN s.R_score >= 2.78 AND s.F_score <= 1.21 THEN '重要保持客户'
       WHEN s.R_score <= 2.78 AND s.F_score >= 1.21 THEN '重要发展客户'
       WHEN s.R_score <= 2.78 AND s.F_score <= 1.21 THEN '一般价值客户'
       END) AS 客户类型
FROM 用户价值打分 AS s;

SELECT *
FROM 用户标签表;

SELECT l.`客户类型`, COUNT(l.`客户类型`) AS 'cnt'
FROM 用户标签表 AS l
GROUP BY l.`客户类型`;