RFM模型应用：淘宝用户行为分析（SQL）

一、目的

1.基于AARRR分析用户行为，各环节的流失率
2.参照RFM模型，找到最具价值的核心付费用户群进行分析

二、分析数据涉及到指标

数据集字段
用户ID，商品ID，商品类目ID，行为类型，时间戳
行为类型（PV-详情页点击，UV-购买，CART-加购，FAV-收藏）

三、数据清洗

数据清理--导入，缺失值处理，异常值处理--部分数据（2017.11.25-2.17.12.3）

四、构建模型

4.1 浏览量--2027221

SELECT COUNT(behavitor) AS 浏览量
FROM `userbehavior`
WHERE behavitor='pv';

4.2 用户数--22099

SELECT COUNT(DISTINCT userId) AS 用户数
FROM userbehavitor;

4.3 平均访问量--91

4.4 不同时段成交量

SELECT hours,COUNT(behavitor) AS 成交量
FROM User_Behavior
WHERE behavitor_type='buy'
GROUP BY hours
ORDER BY hours;

4.5购买次数最多的商品

SELECT item_id,COUNT(behavitor_type) 被购买次数
FROM User_Behavior
WHERE behavitor='buy'
GROUP BY item_id
ORDER BY COUNT(behavitor_type) DESC
LIMIT 10;

4.6 用户行为转化漏斗分析

SELECT behavitor_type,COUNT(behavitor_type)
FROM User_Behavior
GROUP BY behavitor_type
ORDER BY COUNT(behavitor_type) DESC;

1）在获取用户环节：用户使用淘宝的频率很高，没有问题。
2）在用户激活环节：收藏和添加到购物车次数对比pv的结果分别为6%和3%，将二者归为一类，占比为9%。
3）在收入环节：占比进一步下降，购买次数对比pv的结果为2%

也就是说，用户平均浏览10个网页会产生一个收藏或者添加到购物车行为；平均浏览50个网页才会产生一个实际购买行为。

4.7 RFM分析

Recency：最近一次消费距离上一次的长短

SELECT user_id,COUNT(user_id) AS 购买次数
FROM User_Behavior
WHERE behavitor_type='buy'
GROUP BY user_id
ORDER BY COUNT(user_id) DESC;

SELECT user_id,
    (
        CASE WHEN 购买次数 BETWEEN 1 AND 18 THEN 1
        WHEN 购买次数 BETWEEN 19 AND 36 THEN 2
        WHEN 购买次数 BETWEEN 37 AND 54 THEN 3
        WHEN 购买次数 BETWEEN 55 AND 72 THEN 4
        ELSE 0
        END
    ) AS 购买频率得分
FROM (
    SELECT user_id
           ,COUNT(behavitor_type) AS 购买次数
    FROM User_Behavior
    WHERE behavitor_type='buy'
    GROUP BY user_id
) b
ORDER BY 购买频率得分 DESC;

Frequency：特定时间内消费的次数

SELECT user_id
       ,(
           CASE WHEN 购买天数 BETWEEN 0 AND 2 THEN 1
           WHEN 购买天数 BETWEEN 3 AND 4 THEN 2
           WHEN 购买天数 BETWEEN 5 AND 6 THEN 3
           WHEN 购买天数 BETWEEN 7 AND 7 THEN 4
           ELSE 0
           END
       ) AS 购买得分
FROM(
    SELECT user_id,datediff(max(dates)),'2017-11-25') AS 购买天数
    FROM User_Behavior
    WHERE behavitor_type='buy'
    GROUP BY user_id
) AS a
WHERE behavitor_type='buy'
ORDER BY 购买得分;

分成四类用户

• 重要价值用户--重点关注并保持
• 重要保持客户--通过活动等提高购买率
• 重要发展客户--防止流失
• 一般价值用户--赠送优惠券

五、结论

1.用户活跃时间按18～21时，21～22时是一天中最活跃的时段。--这个时间进行更频繁的推广运营。
2.被重复购买最多的商品是×××，对于这些产品可以建立子的忠实粉丝群，进行更精准的人群管理。
3.用户行为转化率只有2%，在浏览详情页后出现了大量流失，建议通过活动，优惠券，产品详情页的改进提高转化。
4.通过RFM模型对用户分析后，重要价值用户较少，可以根据用户类型，精准营销。

SQL下载

回复可见：

作者丨简生
来源丨知乎