基于MySQL的电商用户、商品、平台价值分析

1.项目背景

随着电商行业近几年的迅猛发展，电子商务从早些年的粗放式经营，逐步转化为精细化运营。随着平台数据量的不断积累，通过数据分析挖掘消费者的潜在需求，消费偏好成为平台运营过程中的重要环节。本项目基于某电商平台用户行为数据，在MySQL关系型数据库，探索用户行为规律，寻找高价值用户；分析商品特征，寻找高贡献商品；分析产品功能，优化产品路径。

2.分析流程

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

• 人（用户）是整个运营的核心。所有举动都围绕着，如何让更多的人有购买行为，让他们买的更多，买的更贵。所以对人的洞察是一切行为的基础。目前平台上的主力消费人群有哪些特征，他们对货品有哪些需求，他们活跃在哪些场，还有哪些有消费者目前不在平台上，对这些问题的回答指向了接下来的行动。
• 货就对应供给，涉及到了货品分层，哪些是红海，哪些是蓝海，如何进行动态调整，是要做运营还是换平台，以满足消费者的需求。
• 场就是消费者在什么场景下，以什么样的方式接触到了这个商品。早期的导购做的比较简单，当前的场就比较丰富，但也暴露了淘宝和京东在导购方面的一些问题。比如内容营销，目前最好的可能是微信的 KOL 生态和小红书，甚至微博，都不在电商自己的场。如何做一个全域的打通，和消费者进行多触点的接触，比如社交和电商联动，来完成销售转化，这就是腾讯和阿里一直都在讲的「全域营销」。

3.确认问题

本次分析的目的是想通过对用户行为数据进行分析，为以下问题提供解释和改进建议：
1）基于漏斗模型的用户购买流程各环节分析指标，确定各个环节的转换率，便于找到需要改进的环节；
2）商品分析：找出热销商品，研究热销商品特点；
3）基于RFM模型找出核心付费用户群，对这部分用户进行精准营销。

4.数据说明

5.指标体系建设

5.1用户指标体系

根据“人货场”体系建立基础指标（UV/PV/留存率）和RFM模型分析。

• 基础指标
  uv、 pv、留存率统计
• RFM模型分析
  R(Recency)：最近一次消费
  F(Frequency)：消费频率
  M(Monetary)：消费金额
  根据以上三个指标，我们可以从三个维度将用户分成八类，但是本案例中没有M维度的值，故我们将用户分成四类。我们按照最近一次消费的均值和消费频率的均值定最低界限。

1. 重要高价值客户：指最近一次消费较近且消费频率较高的客户
2. 重要唤回客户：指最近一次消费较远且消费频率较高的客户
3. 重要深耕客户：指最近一次消费较近且消费频率较低的客户
4. 重要挽留客户：指最近一次消费较远且消费频率较低的客户

5.2 商品指标体系

商品指标主要有：

• 商品的点击量、收藏量、加购量、购买次数、购买转化（该商品的所有用户中有购买转化的用户）等；
• 对应品类的点击量 收藏量 加购量 购买次数 购买转化（该商品品类的所有用户中有购买转化的用户）等。

5.3 平台指标体系

行为指标：
点击次数 收藏次数 加购物车次数 购买次数 购买转化（该平台当中的所有用户中有购买转化的用户）

6. 结论

6.1 用户分析

UV异常分析：每日 UV数据中，明显异常点为双十一活动造成，该影响为已知影响。

对于UV周环比的分析：日常周环比数据大多大于0，说明用户呈一定上升趋势，其中如11月 26日、 12月2日、 12月 7日等的数据为下降数据，需要结合其他数据做进一步的下降原因分析。双十一活动后用户周环比会相应下降，为正常原因。

猜测可能的问题有：

• 内部问题：产品BUG、策略问题（周年庆活动结束了）、营销问题（代理商换了）等；

• 外部问题：竞品活动问题（其他平台大酬宾），政治环境问题（进口商品限制），舆情口碑问题（平台商品爆出质量问题）等；

6.2 用户精细化运营

通过RFM模型中的用户最近一次购买时间、用户消费频次分析，分拆得到以下重要用户。可以在后续精细化运营场景中直接使用细分用户，做差异化运营：

• 对高价值客户做VIP服务设计，增加用户粘性同时通过设计优惠券提升客户消费；
• 对深耕客户做广告推送，刺激用户提升消费频次；
• 对挽留客户做优惠券、签到送礼策略，增加挽留用户粘性；
• 对唤回客户做定向广告、短信召回策略，尝试召回用户。

6.3 商品分析

热销商品品类如下所示。
其中’5027‘、 ’5399‘品类购买转化率较其余商品品类偏低，需要结合更多数据做进一步解读。

可能的原因：品类独有特性导致用户购买较低，比如必需品、奢侈品等等。

6.4 产品功能路径分析

以下为主要购买路径。可以发现用户多以直接购买为主；添加购物车的购买在主要购买路径中数量较少。后续的产品加购功能和产品收藏功能还需要结合更多数据做改进方案。

附录代码

-- 表处理
create table o_retailers_trade_user
(
user_id int (9),
item_id int (9),
behavior_type int (1),
user_geohash varchar (14),
item_category int (5),
time varchar (13)
);

-- 增加新列date_time、 dates，
UPDATE o_retailers_trade_user 
SET dates = date( date_time );
DESC o_retailers_trade_user;

ALTER TABLE o_retailers_trade_user ADD COLUMN date_time datetime NULL;
UPDATE o_retailers_trade_user 
SET date_time = str_to_date( time, '%Y-%m-%d %H' );-- %H可以表示0-23；⽽%h表示0-12

ALTER TABLE o_retailers_trade_user ADD COLUMN dates CHAR ( 10 ) NULL;
UPDATE o_retailers_trade_user 
SET dates = date( date_time );
DESC o_retailers_trade_user;
	
	
-- 重复值处理：创建新表a，并插入5W条数据。
create table temp_trade like o_retailers_trade_user;
insert into temp_trade select distinct * from o_retailers_trade_user limit 50000;	

-- 用户指标
-- 基础指标：uv、 pv、留存率（按日）统计
SELECT
	dates,
	count( DISTINCT user_id ) AS 'uv',
	count( IF ( behavior_type = 1, user_id, NULL ) ) AS 'pv',
	count( IF ( behavior_type = 1, user_id, NULL ) ) / count( DISTINCT user_id ) AS 'pv/uv' 
FROM
	temp_trade 
GROUP BY
	dates;
	
	
-- 用户留存
WITH temp_table_trades AS 
(
	SELECT
		a.dates,
		count( DISTINCT b.user_id ) AS device_v,
		count( DISTINCT IF ( datediff( b.dates, a.dates ) = 0, b.user_id, NULL ) ) AS device_v_remain0,
		count( DISTINCT