一、背景

以终为始，此项目分析的目的就是希望能提高销售收入，给业务部门提出合理的改进建议。其实，现在国内的电商行业已经进入到了一个白热化的竞争阶段，增量用户基本已经达到了一个瓶颈，所以现在提高销售收入的方法，更多的是通过对存量用户的精细化运营来提高销售转化率的。

为了让大家对这个项目有更好的理解，这里再简单介绍一下为了让对电商数据指标不是特别熟悉的朋友更好的理解后面的数据，这里先简单介绍一下几个关键的指标，下表是我们拿到的用户行为数据，这里已经做了脱敏处理。可以看到，一共有5个字段，包括了用户ID、商品ID、商品品类ID、行为类型、时间戳。其中，行为类型包括，pv-浏览行为、buy-购买行为、cart-加购行为、fav-收藏行为。

下面，我将从数据分析的4个步骤，分享这个项目的一些实操和思维。

二、数据清洗

我们拿到一份数据，不能马上就开始做数据分析，数据往往可能因为录入等原因，导致数据出现异常，通过需要对数据进行清洗。比如：重复值、空值、超出分析范围、异常值、离群值等。

1、删除重复值

-- 查找重复值
SELECT user_id,item_id,time_stamp
FROM userbehavior
GROUP BY user_id,item_id,time_stamp
HAVING COUNT(*) >1;

-- 新建行唯一id
ALTER TABLE userbehavior ADD id INT PRIMARY KEY auto_increment;

-- 找出重复行的id，重复值>1即为重复值
SELECT user_id,item_id,time_stamp,ROW_NUMBER() over(PARTITION by user_id,item_id,time_stamp) AS 重复值
FROM userbehavior;

-- 删除重复行id所在的记录
DELETE FROM userbehavior WHERE id in
(
SELECT b.id FROM(select max(id) as id from userbehavior group by user_id,item_id,time_stamp having count(*)>1)as b
);

2、处理空值

-- 查询是否存在空值，将每个字段进行遍历查询
SELECT *
FROM userbehavior
WHERE user_id is null or category_id is null or item_id is null or behavior_type is null or time_stamp is null;


本项目中不存在空值

3、时间戳处理

先对时间戳处理，转成我们日常常见的格式，然后筛选出不在我们此次分析范围内的数据，并删除。

-- 时间处理，增加字段，用于接受指定的日期格式
ALTER TABLE userbehavior ADD dates varchar(255);
UPDATE userbehavior SET dates = FROM_UNIXTIME(time_stamp,'%Y-%m-%d');

ALTER TABLE userbehavior ADD hours varchar(255);
UPDATE userbehavior SET hours = FROM_UNIXTIME(time_stamp,'%H');

ALTER TABLE userbehavior ADD datetime varchar(255);
UPDATE userbehavior SET datetime = FROM_UNIXTIME(time_stamp,'%Y-%m-%d %H:%i:%s');

-- 补充星期几的数据，(索引从0开始，代表星期一)
ALTER TABLE userbehavior ADD weekday varchar(255);
UPDATE userbehavior SET weekday = WEEKDAY(datetime)+1;

-- 查找是否存在超出分析时间的记录
SELECT *
FROM userbehavior
WHERE dates < '2021-11-25' or dates > '2021-12-03';

-- 删除超出分析时间的记录
DELETE
FROM userbehavior
WHERE dates < '2021-11-25' or dates > '2021-12-03';

三、明确问题

通过AIPL模型，计算浏览、收藏、购买、复购各个环节转化率，定位哪个环节出了问题。为方便后续统计分析，针对用户的行为，先进行行列互换，并创建一个视图

-- 行列互换，创建视图
CREATE VIEW behavior as
SELECT user_id,datetime,dates,hours,max(case behavior_type when 'pv' then 1 else 0 end)'pv',
max(case behavior_type when 'fav' then 1 else 0 end)'favor',
max(case behavior_type when 'cart' then 1 else 0 end)'cart',
max(case behavior_type when 'buy' then 1 else 0 end)'buy'
FROM userbehavior
GROUP BY user_id,datetime

1、计算AIP

-- 计算AIP
CREATE VIEW jieguo as
SELECT sum(pv)'A',sum(favor)+sum(cart)'I',sum(buy)'P'
FROM behavior;

2、计算L复购行为

-- 第一步：通过窗口函数，记录会员的第n次消费
CREATE VIEW consume as
SELECT user_id,datetime,buy,dense_rank() over(partition by user_id ORDER BY datetime)as 'n_consume'
FROM behavior WHERE buy=1
ORDER BY user_id,datetime

-- 第二步：筛选第二次及以上消费，皆为复购消费
SELECT sum(buy)'L'
FROM consume WHERE n_consume>1;

3、计算AIPL转化率

SELECT jieguo.I/jieguo.A,jieguo.P/jieguo.I
FROM jieguo;''

4、小结

至此，我们已经完成了AIPL的建模计算，我们做成漏洞进行分析，如下图所示：A→I，拉新环节比较低，需要优化拉新策略；I→P效果还可以，分析用户的首购特征，需要深入挖掘优化策略；P→L转化率很不错，需要总结经验，复制到其他业务单元。

四、分析原因

通过对AIPL模型的结果分析，我们分别对A→I拉新环节，进行人货场分析；P→L针对用户的复购行为进行分析；I→P首购环节，对高购买率的人群的特征进行分析，找出规律应用到促进购买，同时也可以进行RFM分析，对用户进行精细化的运营。

1、人货场分析

人的部分：观察不同用户的行为特点，总结经验。此项目中，用户行为数据有限，用户相关数据行为除了商品就是时间，因此对于人的分析，就转化成了A到I高效转化的时间特征，从而找出转化效率最高的时间段对用户进行触达动作。人和时间交叉的维度，主要在用户触达侧落地，比如广告投放时间，发短信时间。

-- 找出用户从A到I高效转化的时间特征
-- 先分别计算出每一小时对应的A和I的用户行为　
CREATE VIEW A as
SELECT hours,count(*)'A浏览行为'
from behavior
WHERE pv=1
GROUP BY hours;

CREATE VIEW I as
SELECT hours,count(*)'I兴趣行为'
from behavior
WHERE favor=1 or cart=1
GROUP BY hours;

-- 基于A表和I表，计算用户从A到I每个小时对应的转化率
CREATE VIEW zhuanhualv as
SELECT A.hours,A浏览行为,I兴趣行为,concat(round((I兴趣行为/A浏览行为),3)*100,'%') as 'A→I转化率'
from A left join I on A.hours=I.hours
ORDER BY A.hours;

-- 为了有一个对比的阈值，计算转化效率的评估标准(平均值)，然后把每个时间点与平均值进行比较
SELECT hours,avg(A浏览行为),avg(I兴趣行为),avg(A→I转化率)
from zhuanhualv;

货的部分：计算人货匹配效率，总结商品推荐是否有效

-- 计算被浏览的总产品数
SELECT count(distinct item_id)'A'
FROM userbehavior
WHERE behavior_type='pv';

-- 计算用户感兴趣的产品数
SELECT COUNT(DISTINCT item_id)'I'
FROM userbehavior
WHERE behavior_type in ('favor','cart');

-- 从前面结果可以看出，用户感兴趣的产品数较浏览数差异较大，因此这里可以假设大部分产品被错误的地推荐到用户；
-- 将用户对用户最感兴趣的商品和浏览最多的前100款产品进行交叉查询，如果交叉查询的商品数较少，则假设成立．
-- 计算每个商品的浏览数据并排序
CREATE VIEW L as
SELECT item_id,count(*)'L'
FROM userbehavior
WHERE behavior_type = 'pv'
GROUP BY item_id ORDER BY L DESC limit 100;

-- 计算加购商品的数据并排序
CREATE VIEW G as
SELECT item_id,count(*)'G'
FROM userbehavior
WHERE behavior_type in ('favor','cart')
GROUP BY item_id ORDER BY G DESC limit 100;

-- 交叉查询，仅有６款产品，因此假设成立．
SELECT count(*) as 'L&G | 交叉商品数'
FROM L INNER join G ON L.item_id=G.item_id;

场的部分：场是除了用户行为及产品之外的内容，广义上指与用户的触点，比如电商页面设计、实体店场地布置、促销活动方面分析；在本项目中，结合我们的数据，可以从分析时间范围内，是否存在销售促销，对分析期间的销售产生了影响。

结合业务经验，本次分析时间范围内是11月25-12月3日。在双十一之后，双十二之前，因此可以知道，分析时间处于两个大促活动的中间的低潮期，客观上营销效果整体比较平淡，进而影响了AI转化率。

人货场小结：人，为了提高转化率，可以选择在浏览值大于3735且转化率大于9.1%的时间段，加大投放力度，其中23点为转化率大于10%，为最佳转化时间。货，目前人货匹配效率低，平台大部分商品为长尾商品，不能很好的吸引客户，需要进一步优化商品信息，以提高转化率。场，从平台活动周期角度来说，分析时间段出于低潮期，客观上一定程度上导致了转化率低。

2、复购分析

复购分析的目的是为了优化用户触达策略，即何时何地向哪些已购用户推送什么商品，可以提升复购率。因此把复购分析的问题转成通过分析用户复购周期以解决业务中何时触达、触达多久的问题。

何时触达？

-- 为了计算方便，新建一个用户第n次购买的视图
CREATE VIEW consume_dates as
SELECT DISTINCT user_id,dates,DENSE_RANK() over(PARTITION by user_id ORDER BY dates)as n_consume
FROM behavior WHERE buy=1;

-- 先解决何时触达的问题，先计算用户的回购周期
CREATE VIEW zhouqi AS
SELECT a.user_id,a.dates,a.n_consume,DATEDIFF(a.dates,b.dates) '回购周期（天）'
FROM consume_dates as a,consume_dates as b
WHERE a.user_id=b.user_id and b.n_consume=a.n_consume-1;

-- 计算平均回购周期
SELECT avg(zhouqi.回购周期（天）)
FROM zhouqi;

因此，以上可以得到结论，可以在用户购买之后的两到三天内对用户进行触达。

触达多久？

前面已经知道，在用户购买之后的两到三天内可以对用户进行触达，但是往往发一次是不够的。在发完短信后，用户还没回来进行消费，那接下来就要进行第二次触达。因为顾客消费后，会有一个黄金触达周期，即顾客最长消费间隔，在这个间隔范围内顾客都有可能会回购。因此，触达多久的问题就转化成了，计算顾客最长消费间隔。

-- 计算顾客最长消费间隔，先计算每个顾客的最长消费间隔
CREATE VIEW jiange as
SELECT a.user_id,a.dates AS '首次消费日期',最后消费日期,DATEDIFF(最后消费日期,a.dates)'最长消费间隔'
FROM (SELECT user_id,dates FROM consume_dates WHERE n_consume=1)a
join (SELECT user_id,max(dates)'最后消费日期' FROM consume_dates WHERE n_consume > 1 GROUP BY user_id) b
on a.user_id=b.user_id;

-- 计算平均值消费间隔
SELECT avg(最长消费间隔)'平均值消费间隔'
FROM jiange;

因此，对于复购环节的分析，我们得出，顾客的平均回购周期是2.3天，平均消费间隔是4.4天。因此我们可以向业务部门提建议，在顾客购买后的2-3天进行触达动作，在4-5天内重复一到两次触达动作。

3、购买率特征分析

通过前面的分析已经知道，只要顾客进行了首次购买，则复购率为64%，因此该部分的重点是提升用户首购的概率。这里通过对比分析思维，通过高购买率顾客与低购买率顾客做对比，挖掘高购买率客户特征，进而指导业务运营策略。

购买特征分析：从前20%和后20%人群的浏览数、兴趣数、购买品类集中度进行对比。

-- 用户购买特征分析,统计用户浏览、收藏、购买的数据
CREATE VIEW user_feature as
SELECT user_id,sum(pv)'浏览',sum(favor)+sum(cart)'收藏加购',sum(buy)'购买',(sum(cart)+sum(favor))/sum(pv)'加购率',sum(buy)/(sum(pv)+sum(cart)+sum(favor))'购买率',DENSE_RANK() over(ORDER BY sum(buy)/(sum(pv)+sum(cart)+sum(favor))desc)'购买率排序'
FROM behavior GROUP BY user_id;

-- 基于二八原则，定义前20%的人群为高购买率人群
SELECT max(购买率排序)*0.2 FROM user_feature -- 前77人为高购买率人群

-- 查看前20%人群的购买特征
SELECT avg(浏览),avg(收藏加购)'平均兴趣数',avg(加购率)'平均架构率'
FROM user_feature WHERE 购买率排序<=77;

-- 查看前20%人群的品类集中度特征,共买了多少种商品
SELECT count(DISTINCT category_id)'品类集中度'
FROM userbehavior WHERE behavior_type='buy' AND user_id in (SELECT DISTINCT user_id from user_feature WHERE 购买率排序<=77);

-- 查看尾部20%人群的消费特征，同时注意剔除购买率为0的人群；
SELECT avg(浏览),avg(收藏加购)'平均兴趣数',avg(加购率)'平均架构率'
FROM user_feature WHERE 购买率排序>=312 and 购买率>0;

-- 查看尾部20%人群的品类集中度特征,共买了多少种商品
SELECT count(DISTINCT category_id)'品类集中度'
FROM userbehavior WHERE behavior_type='buy' AND user_id in (SELECT DISTINCT user_id from user_feature WHERE 购买率排序>=312 and 购买率>0);

总结上面的特征分析，可以得出大概的人群画像，高购买率人群商品浏览数少，但加购率高，大概率为决策性顾客；低购买率人群大概率为纠结型顾客，需要反复浏览商品来确认。低购买率人群品类集中度高，可以让运营有目的地去优化品类信息，从而减少用户浏览跳失率。

RFM模型分析：对用户进行多指标的聚类，R为客户最近一次购买离分析日期的距离；F为客户消费频次；M为客户消费金额。因次项目没有客户消费金额，因此对该模型进行优化，把F改成客户收藏加购行为的次数，对应AIPL模型的AI；M改成客户购买行为的次数，对应AIPL模型的PL。下图得出RFM分值效果：

-- 计算R值
CREATE view r as
SELECT user_id,dates'最后消费日期',datediff('2021-12-03',dates)'R'
FROM consume_dates a
WHERE n_consume=(SELECT MAX(n_consume) FROM consume_dates b WHERE a.user_id = b.user_id)

-- 计算F和M值
CREATE view fm as
SELECT user_id,sum(favor)+sum(cart)'F',sum(buy)'M'
FROM behavior GROUP BY user_id;

-- 表连接,得到RFM表
CREATE view RFM as
SELECT r.user_id,R,F,M
FROM r left join fm on r.user_id =fm.user_id;

-- 计算RFM阈值（可以利用平均值、业务经验、聚类算法，这里简单一点，采用平均值的方法）
SELECT avg(R),avg(F),avg(M) FROM RFM;

-- 对人群通过阈值进行划分族群
CREATE VIEW user_rfm as
SELECT user_id,case WHEN R<2.4501 and F>9.3115 and M>2.8018 then '重要价值用户'
WHEN R<2.4501 and F>9.3115 and M<2.8018 then '一般价值用户'
WHEN R<2.4501 and F<9.3115 and M>2.8018 then '重要发展用户'
WHEN R<2.4501 and F<9.3115 and M<2.8018 then '一般发展用户'
WHEN R>2.4501 and F>9.3115 and M>2.8018 then '重要唤回用户'
WHEN R>2.4501 and F>9.3115 and M<2.8018 then '一般唤回用户'
WHEN R>2.4501 and F<9.3115 and M>2.8018 then '重要挽留用户'
WHEN R>2.4501 and F<9.3115 and M<2.8018 then '一般挽留用户'
end as '用户价值族群'
FROM RFM;

-- 对人群进行结构分析,看看每个族群的占比（round精确位数）
SELECT 用户价值族群,count(user_id)'用户数',concat(round(count(user_id)/983*100,3),'%')'用户数比'
FROM user_rfm
GROUP BY 用户价值族群 ORDER BY 用户价值族群;

小结：重要价值用户，累计占比约26%，符合二八原则，证明我们这个分层的逻辑基础是合理的，把最重要的前20%的人群划分出来了。重要价值用户占比较少，而重要价值用户一般从一般价值、重要发展、重要唤回用户转化而来，所以为了提高重要价值人群的占比， 可以从这三个人群着手，重要发展用户人群基础比较多，可以优先对该类人群进行升级，即对该类用户进行营销，促使他们消费更多升级成重要价值用户。其次，一般价值和重要唤回的梳理较少，还需补充人数，可以从一般发展和一般挽留用户中转化而来。

五、提出建议

1、对于A→I的拉新环节：

推广部门：优化投放策略，从A到I转化率高的时间点，尤其是每天23点，加大投放力度，提高AI转化率

产品部门：针对人货匹配效率低的问题，及时处理低转化率的商品；总结高转化率高的产品特点，开发新品；

运营部门：针对人货匹配效率低的问题，参考高转化产品的描述，优化产品标题内容；针对大促前的低潮期，提出吸引性促销策略，来提高该阶段的销售；

2、对于I→P的购买环节

用户运营部门：针对高购买率客户，可以配合精准商品推荐模型， 触达优质长尾商品，提高购买率；针对低购买率客户，触达热门商品，进而提高购买率； 结合RFM转化路径，针对重要价值用户，保持一定频率的触达，持续维护；重要发展用户，与兴趣商品关联高的商品进行推荐触达，提供用户收藏加购，进而转化为重要价值用户；一般发展用户，商品组合推荐提升连带，转化为一般价值用户；

3、对于P→L的复购环节：

用户运营部门：针对用户复购周期的特性，顾客消费2-3天内，结合回馈手段及时进行触达；在4-5天内重复1-2次以确保顾客能接收到足够的营销刺激；