三、分析原因

根據(jù)“明確問題” 得出的分析目標(biāo)，指定細(xì)分的分析策略：

• 【P0: A→I 】部分是轉(zhuǎn)化率低的問題，在零售行業(yè)中，【人貨場】分析是最常用且有效的分析方法，故此案例也將應(yīng)用該方法進行分析

• 【P1: P→L】部分是用戶復(fù)購表現(xiàn)很好，所以可以通過【用戶復(fù)購分析】，挖掘、總結(jié)復(fù)購特征

• 【P2: I→P】部分目的在與進一步透析用戶購買行為，可以采用【AIPL+RFM】分析方法進行分析

【P0: A→I 】轉(zhuǎn)化率低，結(jié)合【人貨場】分析找到原因

1.人

【人】的部分主要在于觀察用戶行為特點，以總結(jié)經(jīng)驗。當(dāng)前數(shù)據(jù)維度有限，用戶自身行為相關(guān)的數(shù)據(jù)除了商品就是時間，故此部分主要分析A到I高效轉(zhuǎn)化的時間特征，以小時為基準(zhǔn)進行分析。

select  a.`小時`, `A-瀏覽行為`, `I-興趣行為`,
    concat(round((`I-興趣行為`/`A-瀏覽行為`),3)*100,'%') as 'A→I轉(zhuǎn)化率'
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.`小時`;

結(jié)果如圖：

以“平均值”作為對比的基準(zhǔn)

select  
    avg(`A-瀏覽行為`) as 'A-瀏覽行為 平均值', 
    avg(`I-興趣行為`) as 'I-興趣行為 平均值',
    concat(round(avg(`I-興趣行為`)/avg(`A-瀏覽行為`),3)*100,'%') as 'A→I轉(zhuǎn)化率 平均值'
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.`小時`;

得到結(jié)果如圖所示：

因每個時段的瀏覽人數(shù)不同，為確保分析的結(jié)果有效，故需選擇 瀏覽值大于平均值3733的時段來觀察：在這些時段中， 我們認(rèn)為轉(zhuǎn)化率大于 平均值9.1%的時段，

select * 
from
(select  a.`小時`, `A-瀏覽行為`, `I-興趣行為`,
    concat(round((`I-興趣行為`/`A-瀏覽行為`),3)*100,'%') as 'A→I轉(zhuǎn)化率'
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轉(zhuǎn)化率`>=9.1
order by `小時`;

結(jié)論：可選擇11點，15-17點，19點，22-23點作為投放選擇時間，其中23點的轉(zhuǎn)化率為最大值，可作為最佳投放時間

2.貨

針對【人貨匹配】的情況進行分析之前，先了解AI階段涉及到的總商品和總商品分類：

select  count(distinct item_id) as 'A' 
from userbehavior
where behavior_type ='pv';

結(jié)果如圖，共有60592款商品被有效瀏覽。

接下來，計算用戶感興趣的商品：

select count(distinct item_id) as 'I'
from userbehavior
where behavior_type in ('favor','cart');

結(jié)果如圖，用戶共對5206款商品產(chǎn)生興趣。

結(jié)合【假設(shè)驗證分析方法】，基于有效瀏覽機產(chǎn)生興趣的商品數(shù)差異較大的情況，提出假設(shè)：用戶偏好的商品主要集中在少數(shù)商品，而大部分長尾商品則是被錯誤地推薦到用戶。

驗證思路：

• 1. 將被瀏覽最多的前100款商品 以及 用戶最感興趣(即加購、收藏)的前100款商品 進行交叉查詢
• 2. 如果交叉出的商品數(shù)較少，則假設(shè)成立
• 3. 反之，則假設(shè)不成立

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款單品交叉，查看交叉數(shù)量：

select count(*) as 'a&i交叉商品數(shù)'
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;

結(jié)果顯示，僅有7款商品既是高瀏覽量，也是高收藏/加購的，故假設(shè)成立：用戶偏好的商品主要集中在少數(shù)商品，而大部分長尾商品則是被錯誤地推薦到用戶。進而可得出【人貨匹配】效率低的結(jié)論。

3.場

【場】廣義來說指的是與用戶的觸點，例如在線下零售業(yè)，則是門店; 在線上電商，則是在線店鋪、平臺、投放渠道等。

從這個角度，分析此段時間淘寶平臺的活動情況：

• 1. 結(jié)合業(yè)務(wù)經(jīng)驗，數(shù)據(jù)時間段在雙十一之后，雙十二之前，即是兩個大促的中間節(jié)點

• 2. 查詢2017年雙十二的相關(guān)信息如下：

• • l 活動：天貓雙12年終品牌盛典
• • l 預(yù)熱時間：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. 由活動時間可知，分析時間段處于雙十二預(yù)熱時間前夕的低潮期，客觀上營銷效果整體比較平淡，進而影響AI轉(zhuǎn)化率。

4.總結(jié)

人：為提高轉(zhuǎn)化率，可選擇在 瀏覽值大于3733 且 轉(zhuǎn)化率大于 9.1%的時間段加大投放力度；其中，23點為轉(zhuǎn)化率大于10%，為最佳投放時間

貨：人貨匹配效率低，平臺大部分商品為長尾商品，但它們并不能很好地吸引用戶興趣，需要進一步優(yōu)化商品信息以提升轉(zhuǎn)化率。

場：從平臺活動周期角度來說，分析時間段正處于大促活動預(yù)熱前的低潮期，此客觀因素也在一定程度上導(dǎo)致轉(zhuǎn)化率低。

【P1: P→L】用戶復(fù)購表現(xiàn)很好，通過【用戶復(fù)購分析】，挖掘、總結(jié)復(fù)購特征

用戶復(fù)購分析是為了優(yōu)化用戶觸達策略，即何時何地想哪些已購用戶推送什么商品可提升復(fù)購率。本案例中，因商品和受眾特征數(shù)據(jù)不足，不進行商品推薦分析，將通過分析用戶【復(fù)購周期】以解決業(yè)務(wù)中觸達時間的問題，主要包括何時觸達、觸達多久。

1.何時觸達

可通過計算用戶平均回購周期，即可在用戶發(fā)生購買行為后，在平均回購周期內(nèi)對其進行營銷觸達。

1） 通過以下語句先計算出每個用戶每次消費的回購周期

-- 創(chuàng)建消費次數(shù)視圖
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;

查詢結(jié)果

結(jié)果說明：如圖，user_id 為100的用戶 n_consume=2，回購周期=2天 即為期第二次消費時，與第一次消費間隔了2天，也就是說該用戶在2天前進行了第一次消費。

2）基于以上查詢結(jié)果，可以將每人次的回購周期進行平均，取得最終的 平均回購周期

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

查詢結(jié)果：

結(jié)論：即在用戶購買行為后的2-3天內(nèi)對其進行觸達。

2.觸達多久：即觸達多久能覆蓋用戶在生命周期內(nèi)的全部付費

1）統(tǒng)計每個客戶“首次消費日期”、“最后消費日期”后，相減即可算得每個客戶的消費周期，命名為“最長消費間隔”。

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
-- 僅針對復(fù)購人群
where a.user_id in 
  (select distinct user_id 
  from consume 
  where n_consume>1);

查詢結(jié)果：

2）基于上面的查詢結(jié)果，按日期進行分組統(tǒng)計，計算得每天消費的客戶平均最長消費間隔。

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
-- 僅針對復(fù)購人群
    where b.user_id in 
        (select distinct user_id 
        from consume 
        where n_consume>1)
        ) as t
group by `首次消費日期`
order by `首次消費日期`;

優(yōu)化一下

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 `首次消費日期`;

結(jié)果如圖，在周六(11-25) 首購的客戶，最長消費間隔最長為5.9天。

【P2:I→P】挖掘用戶購買轉(zhuǎn)化路徑，采用【AIPL+RFM】進行分析

1.用戶首購特征

有此前漏斗分析可知，平臺用戶復(fù)購率高達63%，也就是說用戶只要突破0到1的消費，接下來就有63%的機會復(fù)購，所以此部分重點應(yīng)放在如何提升AI→P。

為了提高AI客戶的購買轉(zhuǎn)化率，可以通過高購買率客戶與低購買率客戶對比，挖掘高購買率客戶特征，進而指導(dǎo)業(yè)務(wù)運營策略。

先創(chuàng)建視圖，以統(tǒng)計每個客戶的購買率及其他數(shù)據(jù)。語句如下：

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;

結(jié)果如圖：

1)購買率高 vs 購買率低

根據(jù)二八原則，定位購買率前20%和后20%分別為高購買率和低購買率用戶。根據(jù)視圖統(tǒng)計，共387個購買率分層。即得到兩個群組用戶的特點

select  avg(`瀏覽`) as '平均瀏覽數(shù)',
    avg(`收藏`+`加購`) as '平均興趣數(shù)',
    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 '平均瀏覽數(shù)',
    avg(`收藏`+`加購`) as '平均興趣數(shù)',
    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)總結(jié)

• 高購買率用戶瀏覽商品數(shù)36遠(yuǎn)小于低購買率用戶瀏覽商品數(shù)194
• 高購買率用戶加購率11.68%大于低購買率用戶加購率9.92%
• 高購買率用戶購買品類集中度399遠(yuǎn)大于低購買率用戶瀏覽商品數(shù)97

綜上所述可得到不同人群畫像特征：高購買率人群瀏覽商品數(shù)雖然少，但加購率高和購買率更高，大概率為決策型顧客，因個人需求看對商品就下手購買；相對比下，低購買率人群更多是糾結(jié)型顧客，需要反復(fù)瀏覽商品和收藏加購來確認(rèn)最終想要的商品。同時低購買率人群關(guān)注品類集中度較高，可針對這些品類優(yōu)化品類信息，減少用戶瀏覽跳失率。

2. AIPL + RFM分析

為進一步促進用戶購買，需進行精細(xì)化用戶運營，可通過RFM分析實現(xiàn)。根據(jù)實際業(yè)務(wù)和數(shù)據(jù)，重新定義RFM：

• R：最近一次購買離分析日期的距離，用以判斷購買用戶活躍狀態(tài)
• F：客戶收藏、加購行為次數(shù)
• M：客戶購買行為次數(shù)

即可得出詳細(xì)用戶分群及其人群特征：

• 1.重要價值用戶（R低F高M高）：用戶處于購買活躍期，感興趣的商品多，購買次數(shù)也多
• 2.一般價值用戶（R低F高M低）：用戶處于購買活躍期，感興趣的商品多，購買次數(shù)少
• 3.重要發(fā)展用戶（R低F低M高）：用戶處于購買活躍期，感興趣的商品少，購買次數(shù)多
• 4.一般發(fā)展用戶（R低F低M低）：用戶處于購買活躍期，感興趣的商品少，購買次數(shù)少
• 5.重要喚回用戶（R高F高M高）：用戶已不活躍，感興趣的商品多，購買次數(shù)也多
• 6.一般喚回用戶（R高F高M低）：用戶已不活躍，感興趣的商品多，購買次數(shù)少
• 7.重要挽留用戶（R高F低M高）：用戶已不活躍，感興趣的商品少，購買次數(shù)多
• 8.一般挽留用戶（R高F低M低）：用戶已不活躍，感興趣的商品少，購買次數(shù)少

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)根據(jù)以上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 '重要發(fā)展用戶'
        when R < 2.4501 and F<9.3115 and M<2.8018 then '一般發(fā)展用戶'
        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;

結(jié)果如圖：

4)統(tǒng)計各個組的用戶數(shù)及用戶占比，用于對用戶運營現(xiàn)狀進行分析，并給出運營策略方向。

select `用戶價值族群`, count(user_id) '用戶數(shù)',
    concat(round(count(user_id)/983*100,3),'%') as '用戶數(shù)占比'
from user_RFM
group by `用戶價值族群`
order by `用戶價值族群`

如上圖為RFM模型分組結(jié)果，對比分析需要對照組進行比照分析，因數(shù)據(jù)限制，此處結(jié)合業(yè)務(wù)經(jīng)驗進行判斷：

• 1. “重要”型用戶累計占比26.8%（3.1%+3.5%+7.4%+12.8%），符合二八規(guī)律
• 2. 從【用戶RFM轉(zhuǎn)化路徑】角度來看：重要價值用戶占比較少，而主要由一般價值用戶、重要發(fā)展用戶、重要喚回用戶轉(zhuǎn)化而來
• • l 一般價值、重要喚回用戶基數(shù)較多，可優(yōu)先對兩個人群進行“升級”
• • l 其次，重要發(fā)展用戶數(shù)量少，需要從基數(shù)較多的一般發(fā)展用戶轉(zhuǎn)化，之后再考慮將重要發(fā)展轉(zhuǎn)為重要價值