数据分析：商业分析方法进阶(三)

三、分析原因

根据“明确问题” 得出的分析目标，指定细分的分析策略：

• 【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模型分组结果，对比分析需要对照组进行比照分析，因数据限制，此处结合业务经验进行判断：

• 1. “重要”型用户累计占比26.8%（3.1%+3.5%+7.4%+12.8%），符合二八规律
• 2. 从【用户RFM转化路径】角度来看：重要价值用户占比较少，而主要由一般价值用户、重要发展用户、重要唤回用户转化而来
• • l 一般价值、重要唤回用户基数较多，可优先对两个人群进行“升级”
• • l 其次，重要发展用户数量少，需要从基数较多的一般发展用户转化，之后再考虑将重要发展转为重要价值