数据分析：商业分析方法进阶(三)
三、分析原因
根据“明确问题” 得出的分析目标，指定细分的分析策略：
-
【P0: A→I 】部分是转化率低的问题，在零售行业中，【人货场】分析是最常用且有效的分析方法，故此案例也将应用该方法进行分析
-
【P1: P→L】部分是用户复购表现很好，所以可以通过【用户复购分析】，挖掘、总结复购特征
-
【P2: I→P】部分目的在与进一步透析用户购买行为，可以采用【AIPL+RFM】分析方法进行分析
【P0: A→I 】转化率低，结合【人货场】分析找到原因
1.人
【人】的部分主要在于观察用户行为特点，以总结经验。当前数据维度有限，用户自身行为相关的数据除了商品就是时间，故此部分主要分析A到I高效转化的时间特征，以小时为基准进行分析。
select a.`小时`, `A-浏览行为`, `I-兴趣行为`,
concat(round((`I-兴趣行为`/`A-浏览行为`),3)*100,'%') as 'A→I转化率'
from
(select hour(datetime) as '小时', count(*) as 'A-浏览行为'
from behavior
where view=1
group by hour(datetime)
) as a
left join
(select hour(datetime) as '小时', count(*) as 'I-兴趣行为'
from behavior
where favor = 1 or cart =1
group by hour(datetime)
) as i
on a.`小时` = i.`小时`
order by a.`小时`;
结果如图：
以“平均值”作为对比的基准
select
avg(`A-浏览行为`) as 'A-浏览行为 平均值',
avg(`I-兴趣行为`) as 'I-兴趣行为 平均值',
concat(round(avg(`I-兴趣行为`)/avg(`A-浏览行为`),3)*100,'%') as 'A→I转化率 平均值'
from
(select hour(datetime) as '小时', count(*) as 'A-浏览行为'
from behavior
where view=1
group by hour(datetime)
) as a
left join
(select hour(datetime) as '小时', count(*) as 'I-兴趣行为'
from behavior
where favor = 1 or cart =1
group by hour(datetime)
) as i
on a.`小时` = i.`小时`
order by a.`小时`;
得到结果如图所示：
因每个时段的浏览人数不同，为确保分析的结果有效，故需选择 浏览值大于平均值3733的时段来观察：在这些时段中， 我们认为转化率大于 平均值9.1%的时段，
select *
from
(select a.`小时`, `A-浏览行为`, `I-兴趣行为`,
concat(round((`I-兴趣行为`/`A-浏览行为`),3)*100,'%') as 'A→I转化率'
from
(select hour(datetime) as '小时', count(*) as 'A-浏览行为'
from behavior
where view=1
group by hour(datetime)
) as a
left join
(select hour(datetime) as '小时', count(*) as 'I-兴趣行为'
from behavior
where favor = 1 or cart =1
group by hour(datetime)
) as i
on a.`小时` = i.`小时`) as b
where `A-浏览行为`>3733 and `A→I转化率`>=9.1
order by `小时`;
结论：可选择11点，15-17点，19点，22-23点作为投放选择时间，其中23点的转化率为最大值，可作为最佳投放时间
2.货
针对【人货匹配】的情况进行分析之前，先了解AI阶段涉及到的总商品和总商品分类：
select count(distinct item_id) as 'A'
from userbehavior
where behavior_type ='pv';
结果如图，共有60592款商品被有效浏览。
接下来，计算用户感兴趣的商品：
select count(distinct item_id) as 'I'
from userbehavior
where behavior_type in ('favor','cart');
结果如图，用户共对5206款商品产生兴趣。
结合【假设验证分析方法】，基于有效浏览机产生兴趣的商品数差异较大的情况，提出假设：用户偏好的商品主要集中在少数商品，而大部分长尾商品则是被错误地推荐到用户。
验证思路：
- 1. 将被浏览最多的前100款商品 以及 用户最感兴趣(即加购、收藏)的前100款商品 进行交叉查询
- 2. 如果交叉出的商品数较少，则假设成立
- 3. 反之，则假设不成立
select item_id,count(*) as 'A'
from userbehavior
where behavior_type ='pv'
group by item_id
order by A desc
limit 100;
按验证思路里说的，分别A, I 热门的前100款单品交叉，查看交叉数量：
select count(*) as 'a&i交叉商品数'
from(
select item_id,count(distinct item_id) as a_item
from userbehavior
where behavior_type='pv'
group by item_id
order by a_item desc
limit 100)as m
inner join
(select item_id,count(distinct item_id) as i_item
from userbehavior
where behavior_type in('favor','cart')
group by item_id
order by i_item desc
limit 100)as n
on m.item_id=n.item_id;
结果显示，仅有7款商品既是高浏览量，也是高收藏/加购的，故假设成立：用户偏好的商品主要集中在少数商品，而大部分长尾商品则是被错误地推荐到用户。进而可得出【人货匹配】效率低的结论。
3.场
【场】广义来说指的是与用户的触点，例如在线下零售业，则是门店; 在线上电商，则是在线店铺、平台、投放渠道等。
从这个角度，分析此段时间淘宝平台的活动情况：
-
1. 结合业务经验，数据时间段在双十一之后，双十二之前，即是两个大促的中间节点
-
2. 查询2017年双十二的相关信息如下：
- l 活动：天猫双12年终品牌盛典
- l 预热时间：2017年12月7日00:00:00-2017年12月11日23:59:59；
- l 上线时间：2017年12月12日00:00:00-2017年12月12日23:59:59。
-
3. 由活动时间可知，分析时间段处于双十二预热时间前夕的低潮期，客观上营销效果整体比较平淡，进而影响AI转化率。
4.总结
人：为提高转化率，可选择在 浏览值大于3733 且 转化率大于 9.1%的时间段加大投放力度；其中，23点为转化率大于10%，为最佳投放时间
货：人货匹配效率低，平台大部分商品为长尾商品，但它们并不能很好地吸引用户兴趣，需要进一步优化商品信息以提升转化率。
场：从平台活动周期角度来说，分析时间段正处于大促活动预热前的低潮期，此客观因素也在一定程度上导致转化率低。
【P1: P→L】用户复购表现很好，通过【用户复购分析】，挖掘、总结复购特征
用户复购分析是为了优化用户触达策略，即何时何地想哪些已购用户推送什么商品可提升复购率。本案例中，因商品和受众特征数据不足，不进行商品推荐分析，将通过分析用户【复购周期】以解决业务中触达时间的问题，主要包括何时触达、触达多久。
1.何时触达
可通过计算用户平均回购周期，即可在用户发生购买行为后，在平均回购周期内对其进行营销触达。
1） 通过以下语句先计算出每个用户每次消费的回购周期
-- 创建消费次数视图
create view consume as
select distinct user_id, dates,
dense_rank() over(partition by user_id order by dates) 'n_consume'
from behavior
where buy=1
select a.user_id, a.dates, a.n_consume,
datediff(a.dates, b.dates) as '回购周期（天）'
from
(select * from consume) as a,
(select * from consume) as b
where a.user_id = b.user_id and
b.n_consume = a.n_consume-1;
查询结果
结果说明：如图，user_id 为100的用户 n_consume=2，回购周期=2天 即为期第二次消费时，与第一次消费间隔了2天，也就是说该用户在2天前进行了第一次消费。
2）基于以上查询结果，可以将每人次的回购周期进行平均，取得最终的 平均回购周期
select avg(datediff(a.dates, b.dates)) as '平均回购周期（天）'
from
(select * from consume) as a,
(select * from consume) as b
where a.user_id = b.user_id
and b.n_consume = a.n_consume-1
查询结果：
结论：即在用户购买行为后的2-3天内对其进行触达。
2.触达多久：即触达多久能覆盖用户在生命周期内的全部付费
1）统计每个客户“首次消费日期”、“最后消费日期”后，相减即可算得每个客户的消费周期，命名为“最长消费间隔”。
select a.user_id,
a.dates '首次消费日期',
`最后消费日期`,
datediff(`最后消费日期`,a.dates) as '最长消费间隔'
from
(select user_id, dates
from consume
where n_consume=1) as a
left join
(select user_id, max(dates) as '最后消费日期'
from consume
group by user_id) as b
on a.user_id = b.user_id
-- 仅针对复购人群
where a.user_id in
(select distinct user_id
from consume
where n_consume>1);
查询结果：
2）基于上面的查询结果，按日期进行分组统计，计算得每天消费的客户平均最长消费间隔。
select `首次消费日期`,
weekday(`首次消费日期`)+1 as '星期几',
avg(`最长消费间隔`) as '平均最长消费间隔'
from
(select a.user_id,
a.dates as '首次消费日期',
`最后消费日期`,
datediff(`最后消费日期`,a.dates) as '最长消费间隔'
from
(select user_id, dates
from consume
where n_consume=1) as a
left join
(select user_id, max(dates) as '最后消费日期'
from consume
group by user_id) b
on a.user_id = b.user_id
-- 仅针对复购人群
where b.user_id in
(select distinct user_id
from consume
where n_consume>1)
) as t
group by `首次消费日期`
order by `首次消费日期`;
优化一下
select `首次消费日期`,
weekday(`首次消费日期`)+1 as '星期几',
avg(`最长消费间隔`) as '平均最长消费间隔'
from
(select a.user_id,
a.dates as '首次消费日期',
`最后消费日期`,
datediff(`最后消费日期`,a.dates) as '最长消费间隔'
from
(seleperformance_schemact user_id, dates
from consume
where n_consume=1) as a
inner join
(select user_id, max(dates) as '最后消费日期'
from consumea
where n_consume>1
group by user_id) b
on a.user_id = b.user_id) as c
group by `首次消费日期`
order by `首次消费日期`;
结果如图，在周六(11-25) 首购的客户，最长消费间隔最长为5.9天。
【P2:I→P】挖掘用户购买转化路径，采用【AIPL+RFM】进行分析
1.用户首购特征
有此前漏斗分析可知，平台用户复购率高达63%，也就是说用户只要突破0到1的消费，接下来就有63%的机会复购，所以此部分重点应放在如何提升AI→P。
为了提高AI客户的购买转化率，可以通过高购买率客户与低购买率客户对比，挖掘高购买率客户特征，进而指导业务运营策略。
先创建视图，以统计每个客户的购买率及其他数据。语句如下：
create view user_feature as
select user_id,
sum(view) as '浏览',
sum(favor) as '收藏',
sum(cart) as '加购',
sum(buy) as '购买',
(sum(cart)+sum(favor))/sum(view) as '加购率',
sum(buy)/(sum(view)+sum(cart)+sum(favor)) as '购买率',
dense_rank() over(order by sum(buy)/(sum(view)+sum(cart)+sum(favor)) desc) as '购买率排序'
from behavior
group by user_id;
select *
from user_feature
order by `购买率` asc;
结果如图：
1)购买率高 vs 购买率低
根据二八原则，定位购买率前20%和后20%分别为高购买率和低购买率用户。根据视图统计，共387个购买率分层。即得到两个群组用户的特点
select avg(`浏览`) as '平均浏览数',
avg(`收藏`+`加购`) as '平均兴趣数',
avg(`加购率`) as '平均加购率'
from user_feature
where `购买率排序`<= 387*0.2;
高购买率客户品类集中度：
select count(distinct category_id) as '购买品类集中度'
from userbehavior
where behavior_type ='buy'
and user_id in (
select distinct user_id
from user_feature
where `购买率排序` <= 387*0.2);
低购买率客户 特征：
注意需剔除无购买人群影响
select avg(`浏览`) as '平均浏览数',
avg(`收藏`+`加购`) as '平均兴趣数',
avg(`加购率`) as '平均加购率'
from user_feature
where `购买率排序` > 387*0.8 and `购买率` > 0;
低购买率客户品类集中度：
select count(distinct category_id) as '购买品类集中度'
from userbehavior
where behavior_type ='buy'
and user_id in (
select distinct user_id
from user_feature
where `购买率排序` > 387*0.8 and `购买率` > 0);
2)总结
- 高购买率用户浏览商品数36远小于低购买率用户浏览商品数194
- 高购买率用户加购率11.68%大于低购买率用户加购率9.92%
- 高购买率用户购买品类集中度399远大于低购买率用户浏览商品数97
综上所述可得到不同人群画像特征：高购买率人群浏览商品数虽然少，但加购率高和购买率更高，大概率为决策型顾客，因个人需求看对商品就下手购买；相对比下，低购买率人群更多是纠结型顾客，需要反复浏览商品和收藏加购来确认最终想要的商品。同时低购买率人群关注品类集中度较高，可针对这些品类优化品类信息，减少用户浏览跳失率。
2. AIPL + RFM分析
为进一步促进用户购买，需进行精细化用户运营，可通过RFM分析实现。根据实际业务和数据，重新定义RFM：
- R：最近一次购买离分析日期的距离，用以判断购买用户活跃状态
- F：客户收藏、加购行为次数
- M：客户购买行为次数
即可得出详细用户分群及其人群特征：
- 1.重要价值用户（R低F高M高）：用户处于购买活跃期，感兴趣的商品多，购买次数也多
- 2.一般价值用户（R低F高M低）：用户处于购买活跃期，感兴趣的商品多，购买次数少
- 3.重要发展用户（R低F低M高）：用户处于购买活跃期，感兴趣的商品少，购买次数多
- 4.一般发展用户（R低F低M低）：用户处于购买活跃期，感兴趣的商品少，购买次数少
- 5.重要唤回用户（R高F高M高）：用户已不活跃，感兴趣的商品多，购买次数也多
- 6.一般唤回用户（R高F高M低）：用户已不活跃，感兴趣的商品多，购买次数少
- 7.重要挽留用户（R高F低M高）：用户已不活跃，感兴趣的商品少，购买次数多
- 8.一般挽留用户（R高F低M低）：用户已不活跃，感兴趣的商品少，购买次数少
1）按以上定义，计算出每个客户的R、F、M值
create view RFM as
select r.user_id, R, F, M
from
(select user_id, datediff('2017-12-03', dates) 'R'
from consume as a
where n_consume = (
select max(n_consume)
from consume b
where a.user_id = b.user_id )
) as r
left join
(select user_id, sum(favor)+sum(cart) 'F', sum(buy) as 'M'
from behavior
group by user_id) as fm
on r.user_id = fm.user_id;
2)为了划分R、F、M值的高 与低，这里采用常用的平均值法作为阈值
select avg(R), avg(F), avg(M)
from RFM;
3)根据以上RFM阈值，及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;
结果如图：
4)统计各个组的用户数及用户占比，用于对用户运营现状进行分析，并给出运营策略方向。
select `用户价值族群`, count(user_id) '用户数',
concat(round(count(user_id)/983*100,3),'%') as '用户数占比'
from user_RFM
group by `用户价值族群`
order by `用户价值族群`
如上图为RFM模型分组结果，对比分析需要对照组进行比照分析，因数据限制，此处结合业务经验进行判断：
- “重要”型用户累计占比26.8%（3.1%+3.5%+7.4%+12.8%），符合二八规律
- 从【用户RFM转化路径】角度来看：重要价值用户占比较少，而主要由一般价值用户、重要发展用户、重要唤回用户转化而来
- l 一般价值、重要唤回用户基数较多，可优先对两个人群进行“升级”
- l 其次，重要发展用户数量少，需要从基数较多的一般发展用户转化，之后再考虑将重要发展转为重要价值
