推荐数据

RFM模型应用:淘宝用户行为分析(SQL)

RFM模型.jpg

一、目的

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

二、分析数据涉及到指标

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

行为类型.jpg

三、数据清洗

数据清理--导入,缺失值处理,异常值处理--部分数据(2017.11.25-2.17.12.3)

数据清洗.jpg

四、构建模型

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下载

回复可见:

此处内容需要评论回复后

作者丨简生
来源丨知乎

本文经授权后发布,本文观点不代表立场
-- 展开阅读全文 --
上瘾:让用户养成使用习惯的四大产品逻辑epub高清电子书
« 上一篇 07-05
合并存取款两张数据表(SQL)
下一篇 » 08-28

发表评论

作者信息

热门文章

标签TAG

热评文章