「MySQL」电商“人货场”指标体系

本文你将学到：

1. 如何搭建电商“人货场”指标体系
2. MySQL：留存率分析
3. MySQL：RFM模型分析
4. MySQL：用户行为路径分析

细心的同学可能会发现，这个符号的「标题」都是实战项目，因为学习的目的不是学习本身，而是为了解决实际问题。本文通过案例来讲述MySQL在公司里是如何应用的。

本文适用于有一定MySQL基础，想进阶练习的同学。（真的都是干货，请大家多多练习） 思维导图附在文章末尾，配合文章一起使用效果更佳。

一、项目背景

电商行业从早些年的粗放式经营，逐步转化为精细化运营。随着平台数据量的不断增加，通过数据分析挖掘消费者的潜在需求、消费偏好成为平台运营过程中的重要环节。

本项目目的如下：

• 探索用户行为规律，寻找高价值用户
• 分析商品特征，寻找高贡献商品
• 分析产品功能，优化产品路径

二、使用“人货场”拆解方式建立指标体系

「人」是整个运营的核心，所有举动都围绕着怎么让更多的人有购买行为，且买的更多买的更贵。

「货」对应着供给，涉及到了商品分层，哪些是红海，哪些是蓝海，如何进行动态调整。

「场」就是消费者在什么场景下，以什么样的方式接触到了这个商品。（哇这句话太精彩了）

建立指标体系如下：

人货场对应的指标（部分）

三、问题确认

1、基于RFM模型找出核心付费用户群，对这部分用户进行精准营销。「人」

2、商品分析：找出热销商品，研究热销商品特点。「货」

3、基于漏斗模型的用户购买流程各环节分析指标，确定各环节的转化率，便于找到需要改进的环节。「场」

四、准备数据

CREATE TABLE temp_trade(
user_id int(9) COMMENT '⽤户ID',
item_id int(9) COMMENT '商品ID',
behavior_type int(1) COMMENT '⽤户⾏为类型（1-曝光；2-购买；3-加⼊购物⻋；4-加⼊收藏夹。）',
user_geohash varchar(14) COMMENT '地理位置',
item_category int(5) COMMENT '品类ID',
date_time datetime COMMENT '用户行为发生时间',
dates date COMMENT '用户行为发生日期'
);

从navicat导入以下数据源：

temp_trade数据源：https://gitee.com/hu-weiqing/datasource/blob/master/temp_trade.xlsx

数据源前10条展示如下：

五、SQL实现

1、用户指标体系

1.1 基础数据：统计每日PV、UV、PV/UV

-- UV：统计distinct user_id数量
-- PV：统计behavior_type=1的记录数

SELECT
a.dates,
count(distinct a.user_id) as 'UV',
count(if(a.behavior_type=1,user_id,null)) as 'PV',
count(if(a.behavior_type=1,user_id,null)) / count(distinct a.user_id) as 'PV/UV'
FROM temp_trade a 
GROUP BY a.dates ;