基于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;

select * from UserBehavior limit 10;
+-----------+-----------+---------------+---------------+--------------+---------------------+--------+
| user_id   | item_id   | item_category | behavior_type | user_geohash | times               | amount |
+-----------+-----------+---------------+---------------+--------------+---------------------+--------+
|  98047837 | 232431562 |          4245 | pv            | NULL         | 2014-12-06 02:32:00 |  45.24 |
|  97726136 | 383583590 |          5894 | pv            | NULL         | 2014-12-09 20:41:00 |  95.31 |
|  98607707 |  64749712 |          2883 | pv            | NULL         | 2014-12-18 11:12:00 | 114.45 |
|  98662432 | 320593836 |          6562 | pv            | 96nn52n      | 2014-12-06 10:27:00 |  74.85 |
|  98145908 | 290208520 |         13926 | pv            | NULL         | 2014-12-16 21:53:00 |  82.92 |
|  93784494 | 337869048 |          3979 | pv            | NULL         | 2014-12-03 20:35:00 |  75.53 |
|  94832743 | 105749725 |          9559 | pv            | NULL         | 2014-12-13 20:08:00 | 112.86 |
|  95290487 |  76866650 |         10875 | pv            | NULL         | 2014-11-27 16:31:00 |  35.62 |
|  96610296 | 161166643 |          3064 | pv            | NULL         | 2014-12-11 23:51:00 | 113.02 |
| 100684618 |  21751142 |          2158 | cart          | NULL         | 2014-12-05 23:52:00 |  68.85 |
+-----------+-----------+---------------+---------------+--------------+---------------------+--------+

• 缺失值处理：

select count(user_id),count(item_id),count(item_category),count(behavior_type),count(user_geohash),count(times),count(amount) from UserBehavior;
+----------------+----------------+----------------------+----------------------+---------------------+--------------+---------------+
| count(user_id) | count(item_id) | count(item_category) | count(behavior_type) | count(user_geohash) | count(times) | count(amount) |
+----------------+----------------+----------------------+----------------------+---------------------+--------------+---------------+
|        1048575 |        1048575 |              1048575 |              1048575 |              330790 |      1048575 |       1048575 |
+----------------+----------------+----------------------+----------------------+---------------------+--------------+---------------+

查询结果中发现user_geohash字段存在缺失值，缺失比例超过50%。缺失严重的数据在后续分析中意义不大，可做删除处理。

• 异常值检查：

select max(times),min(times),max(amount),min(amount) from UserBehavior;
+---------------------+---------------------+-------------+-------------+
| max(times)          | min(times)          | max(amount) | min(amount) |
+---------------------+---------------------+-------------+-------------+
| 2014-12-18 23:59:00 | 2014-11-18 00:00:00 |      130.00 |       30.00 |
+---------------------+---------------------+-------------+-------------+

查询结果中发现该数据集中不存在异常值。

• 重复记录处理：

select count(*) from (select distinct * from UserBehavior) as t;
+----------+
| count(*) |
+----------+
|   962097 |
+----------+

查询结果中发现该数据集有86478条重复记录，筛选不重复的记录共962097行。

• 字段处理：
  根据times字段增加计算字段用户行为日期、周和小时，排除后续分析不需要的user_geohash字段，并将筛选后的结果保存到新表。

create table UserBehavior_new as
select 
	user_id,
	item_id,
	item_category,
	behavior_type,
	times,
	date(times) as 日期,
	date_format(times,'%a') as 周,
	hour(times) as 小时,amount 
from 
	(select distinct * from UserBehavior) as t;

select * from UserBehavior_new limit 10;
+-----------+-----------+---------------+---------------+---------------------+------------+------+------+--------+
| user_id   | item_id   | item_category | behavior_type | times               | 日期       | 周   | 小时 | amount |
+-----------+-----------+---------------+---------------+---------------------+------------+------+------+--------+
|  98047837 | 232431562 |          4245 | pv            | 2014-12-06 02:32:00 | 2014-12-06 | Sat  |    2 |  45.24 |
|  97726136 | 383583590 |          5894 | pv            | 2014-12-09 20:41:00 | 2014-12-09 | Tue  |   20 |  95.31 |
|  98607707 |  64749712 |          2883 | pv            | 2014-12-18 11:12:00 | 2014-12-18 | Thu  |   11 | 114.45 |
|  98662432 | 320593836 |          6562 | pv            | 2014-12-06 10:27:00 | 2014-12-06 | Sat  |   10 |  74.85 |
|  98145908 | 290208520 |         13926 | pv            | 2014-12-16 21:53:00 | 2014-12-16 | Tue  |   21 |  82.92 |
|  93784494 | 337869048 |          3979 | pv            | 2014-12-03 20:35:00 | 2014-12-03 | Wed  |   20 |  75.53 |
|  94832743 | 105749725 |          9559 | pv            | 2014-12-13 20:08:00 | 2014-12-13 | Sat  |   20 | 112.86 |
|  95290487 |  76866650 |         10875 | pv            | 2014-11-27 16:31:00 | 2014-11-27 | Thu  |   16 |  35.62 |
|  96610296 | 161166643 |          3064 | pv            | 2014-12-11 23:51:00 | 2014-12-11 | Thu  |   23 | 113.02 |
| 100684618 |  21751142 |          2158 | cart          | 2014-12-05 23:52:00 | 2014-12-05 | Fri  |   23 |  68.85 |
+-----------+-----------+---------------+---------------+---------------------+------------+------+------+--------+

-- 数据预览
select 
	count(distinct user_id) as 用户总数,
	count(distinct item_id) as 商品总数,
	count(distinct item_category) as 商品类目数,
	count(distinct 日期) as 总天数  
from UserBehavior_new;
+----------+----------+------------+--------+
| 用户总数  | 商品总数 | 商品类目数  | 总天数  |
+----------+----------+------------+--------+
|     8477 |   701609 |       7095 |     31 |
+----------+----------+------------+--------+

select behavior_type,count(*) 
from UserBehavior_new 
group by behavior_type;
+---------------+----------+
| behavior_type | count(*) |
+---------------+----------+
| pv            |   902050 |
| cart          |    29016 |
| fav           |    20930 |
| buy           |    10101 |
+---------------+----------+

清洗后的数据集共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、人均浏览量、成交量、销售额
select 
	日期,
	sum(behavior_type='pv') as 浏览量,
	count(distinct user_id) as 访客量,
	sum(behavior_type='pv')/count(distinct user_id) as 人均浏览量,
	sum(behavior_type='buy') as 成交量,
	sum((behavior_type='buy')*amount) as 销售额 
from UserBehavior_new 
group by 日期;
+------------+--------+--------+------------+--------+----------+
| 日期       | 浏览量  | 访客量  | 人均浏览量  | 成交量 | 销售额    |
+------------+--------+--------+------------+--------+----------+
| 2014-11-18 |  26974 |   6089 |     4.4300 |    314 | 24488.37 |
| 2014-11-19 |  26280 |   6036 |     4.3539 |    302 | 24274.52 |
| 2014-11-20 |  26416 |   6030 |     4.3808 |    296 | 23971.45 |
| 2014-11-21 |  24289 |   5872 |     4.1364 |    259 | 20401.00 |
| 2014-11-22 |  27053 |   6057 |     4.4664 |    308 | 24085.09 |
| 2014-11-23 |  27820 |   6135 |     4.5346 |    328 | 26384.42 |
| 2014-11-24 |  27571 |   6099 |     4.5206 |    336 | 27517.63 |
| 2014