数据背景
- 来源：阿里移动推荐算法
- 背景：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.`客户类型`;
