前言
本文是基于阿里云天池的飞猪平台用户行为分析,使用MySQL和Excel做数据分析,对输出结果使用Excel和PowerBI进行数据可视化。
一、数据分析步骤
明确问题、理解数据、数据清洗、数据分析、数据可视化
二、明确问题
1、数据来源
阿里云天池数据集(User Behavior Data from Fliggy Trip Platform for Recommendation):https://tianchi.aliyun.com/dataset/113649
2、分析目标
从用户的行为数据中挖掘当前用户的行为特征,通过分析这些特征能够发现平台当前所面临的一些问题并分析原因,从而能出台一些针对性的解决措施,扩大用户的使用转化率。
3、建立指标体系
运用多维度拆解分析方法,分为三个维度(用户、商品、行为)
(1)用户维度
用户画像:年龄、性别、职业、区域分布
留存分析(用户粘性):次日、三日、七日、十五日、三十日
用户价值分析(RFM)
(2)商品维度
商品画像:种类、地域、标签分布
热门商品分析:点击、收藏、加购、购买前十
(3)行为维度
运用AAARR模型分析
aquisition用户获取:访客量(UV)
activation用户激活:浏览量(PV)、成交量
retention用户留存:见用户维度留存分析
revenue用户收益:复购率、跳失率
refer用户推荐:各环节转化率(漏斗分析)
三、理解数据
1、数据描述
文件名 |
介绍 |
包含字段 |
user_item_behavior_history.csv |
所有用户行为数据 |
用户ID,产品ID,行为类型,时间戳 |
user_profile.csv |
所有用户基本属性画像 |
用户ID,年龄,性别,职业,常住城市,人群标签 |
item_profile.csv |
产品所有基本属性 |
产品ID,产品类别ID,产品城市,产品标签 |
2、数据展示
表1:User_profile.csv
每一行代表一个用户,共500万余条数据
A字段:用户ID B字段:年龄 C字段:性别(1-未知,2-男性,3-女性) D字段:职业 E字段:常住城市 F字段:人群标签
表2:item_profile.csv
每一行代表一个商品,共27万余条数据
A:产品ID B:产品类别ID C:产品城市 D:产品标签
表3:user_item_behavior_history.csv
每一行代表一个用户数据行为,共2亿余条数据
A:用户ID B:产品ID C:行为类型(clk, fav, cart, pay) D:时间戳
四、数据清洗
1、备份表
-- 备份表
CREATE TABLE userbehavior_beifen(
User_ID VARCHAR(255),
product_ID VARCHAR(255),
behavior_type VARCHAR(255),
timestamp VARCHAR(255)
);
-- 插入数据
INSERT into userbehavior_beifen SELECT * FROM user_item_behavior_history;
2、处理重复值
-- 处理重复值
ALTER TABLE userbehavior_beifen
ADD id int PRIMARY KEY auto_increment;
DELETE FROM userbehavior_beifen
WHERE id in (
SELECT id FROM(
SELECT *,
ROW_NUMBER() over(partition by User_ID,product_ID,behavior_type,`timestamp`) '重复值'
FROM userbehavior_beifen) temp
WHERE 重复值>1);
3、处理空值
-- 处理空值(数据比较干净,没有空值)
DELETE FROM userbehavior_beifen
WHERE User_ID is null or product_ID is null or behavior_type is null
or `timestamp` is null;
4、一致化处理
-- 时间格式转换
ALTER TABLE userbehavior_beifen
ADD date VARCHAR(255);
ALTER TABLE userbehavior_beifen
ADD time VARCHAR(255);
ALTER TABLE userbehavior_beifen
ADD weekday VARCHAR(255);
ALTER TABLE userbehavior_beifen
ADD `hour` VARCHAR(255);
UPDATE userbehavior_beifen
set date=FROM_UNIXTIME(timestamp,'%Y-%m-%d');
UPDATE userbehavior_beifen
set time=FROM_UNIXTIME(timestamp,'%H:%i:%s');
UPDATE userbehavior_beifen
set weekday=weekday(date)+1;
UPDATE userbehavior_beifen
set `hour`=FROM_UNIXTIME(`timestamp`,'%H');
5、选取分析时间
我们将随机选取的数据日期限制在2021-04-01到2021-04-30之间,其余删除。
-- 数据限制在2021-04-01到2021-04-30
DELETE FROM userbehavior_beifen
WHERE date<'2021-04-01' or date>'2021-04-30';
五、数据分析及可视化
1、行为维度
(1)浏览量(PV)、访客量(UV)、成交量
总量
SELECT COUNT(distinct User_ID) as UV
FROM userbehavior_beifen;
SELECT COUNT(behavior_type) as PV
FROM userbehavior_beifen
WHERE behavior_type = 'clk';
SELECT COUNT(behavior_type) as 支付量
FROM userbehavior_beifen
WHERE behavior_type = 'pay';
计算得
平均浏览量:PV/UV=7.59
人均成交量:支付量/UV=20.47%
在这一个月内,总体来看,在6566个用户中,平均每个人浏览7.59次,人均成交量为20.47%,说明用户活跃度不高且支付转化率不高。
日均
SELECT `date`,COUNT(DISTINCT User_ID) as 每日UV
FROM userbehavior_beifen
GROUP BY `date`
ORDER BY `date`;
SELECT date,COUNT(behavior_type) as 每日PV
FROM userbehavior_beifen
WHERE behavior_type = 'clk'
GROUP BY date
ORDER BY date;
SELECT date,COUNT(behavior_type) as 每日成交量
FROM userbehavior_beifen
WHERE behavior_type = 'pay'
GROUP BY date
ORDER BY date;
导出数据到Excel,并计算日人均PV和日人均成交量
每天的人均访问量起伏不大,月底稍有上升。
每天的人均成交量基本平稳,在6-9号、11-14和19-21号较低,4号最高,推测可能与清明假期有关。
时均
在中午人均成交量最高,凌晨4-7时成交量最低。
人均访问量在20-24时和11时最高。
周均
周四人均访问量最高,周六人均成交量最高。
(2)复购率、跳失率
复购率=购买次数>1的用户/所有购买用户
-- 建立一个表存放用户购买次数
CREATE TABLE count_pay
(SELECT User_ID,
ROW_NUMBER() over(PARTITION by User_ID) 购买次数
FROM userbehavior_beifen
WHERE behavior_type='pay');
-- 查询复购人数
SELECT COUNT(DISTINCT User_ID) 复购人数
FROM count_pay
WHERE 购买次数>1;
SELECT COUNT(DISTINCT User_ID) 总购买人数
FROM count_pay;
复购率为257/519=49.52%
跳失率=点击次数为1的用户/所有点击用户
-- 建立一个表存放用户点击次数
CREATE TABLE count_clk
(SELECT User_ID,
ROW_NUMBER() over(PARTITION by User_ID) 点击次数
FROM userbehavior_beifen
WHERE behavior_type='clk');
-- 计算点击一次的人数
SELECT COUNT(*) 点击一次人数
FROM(
SELECT User_ID
FROM count_clk
GROUP BY User_ID
HAVING max(点击次数)=1
) a;
-- 总人数
SELECT COUNT(DISTINCT User_ID) 总人数
FROM count_clk;
跳失率为1474/6388=23%
(3)转化率(漏斗分析法)
用户转化率可以用来直观的查看业务流程各环节的转化率,可以明确出转化率低的环节,并对其做出针对性的改善。
CREATE TABLE xingwei(
SELECT behavior_type as 行为,COUNT(User_ID) 人数
FROM userbehavior_beifen
WHERE behavior_type='clk');
INSERT INTO xingwei
SELECT behavior_type as 收藏,COUNT(User_ID) 人数
FROM userbehavior_beifen
WHERE behavior_type='fav';
INSERT INTO xingwei
SELECT behavior_type as 加购,COUNT(User_ID) 人数
FROM userbehavior_beifen
WHERE behavior_type='cart';
INSERT INTO xingwei
SELECT behavior_type as 支付,COUNT(User_ID) 人数
FROM userbehavior_beifen
WHERE behavior_type='pay';
从漏斗图来看,最终购买的人数相比于点击的人数转化率只有2.5%,流失率最大的环节处于点击—加购环节,大量用户在这一环节流失,而相较于上一环节的转化率,可以看出加购—支付的转化率比较高,达到了73.97%。分析点击—加购环节流失的主要原因有两个,一是商品类别不符合用户期待,二是商品价格超过了用户的心里预期,针对这一流失问题,需要在向用户推送商品时精准推送,按照用户标签进行推送,以提高点击—加购这一环节的转化率。
2、用户维度
(1)用户画像
只对支付用户进行分析。
星期
SELECT `weekday`,COUNT(DISTINCT User_ID) as 每日UV
FROM userbehavior_beifen
WHERE behavior_type='pay'
GROUP BY `weekday`
ORDER BY `weekday`;
周四成交人数最高。
时刻
11时支付人数最多,凌晨支付人数最少。
年龄
SELECT age,
COUNT(*) as 成交人次
FROM (
SELECT
CASE when b.age<18 then '18岁以下'
when b.age>=18 and b.age <=25 then '18-25'
when b.age>25 and b.age<=35 then '26-35'
when b.age>35 and b.age<=45 then '36-45'
when b.age>45 and b.age<=60 then '46-60'
when b.age>60 and b.age<=80 then '61-80'
when b.age>80 then '80以上' end as age
FROM userbehavior_beifen a
JOIN user_profile b
on a.User_ID=b.User_ID
WHERE a.behavior_type='pay') as nianling
GROUP BY age
ORDER BY age;
支付用户的年龄主要集中在46-60岁年龄段,这个年龄的用户相对年轻人和中年人来说,没有太大经济和生活压力,更懂得也有时间享受生活出去旅行。
性别
SELECT b.gender,COUNT(*) as 成交人次
FROM userbehavior_beifen a
join user_profile b
on a.User_ID=b.User_ID
where a.behavior_type='pay'
GROUP BY gender
ORDER BY gender
(1:未知,2:男性,3:女性)
女性占比最高,更喜欢享受生活喜欢旅行,男性经济方面压力大些,相对来说出行欲望较低。
职业
SELECT b.occupation,COUNT(*) as 成交人次
FROM userbehavior_beifen a
join user_profile b
on a.User_ID=b.User_ID
where a.behavior_type='pay'
GROUP BY occupation
ORDER BY occupation;
职业3 成交人次最多,说明这个职业的人比较喜欢出行或是职业原因(如需要出差)。
地区
SELECT habitual_city,成交人次,占比
FROM
(SELECT b.habitual_city,COUNT(*) as 成交人次,round(COUNT(*)/1344,4) as 占比
FROM userbehavior_beifen a
join user_profile b
on a.User_ID=b.User_ID
where a.behavior_type='pay'
GROUP BY b.habitual_city) as linshi
WHERE 占比>=0.1
只有城市224占比超过10%,可能这个城市比较发达,人民相对富裕,除生活开销外,愿意负担出行的费用享受生活。
(2)留存分析
N日留存率=(某日新增的用户中,在注册的第N天还进行登录的用户数)/ 该日新增用户数
-- 创建一个存放用户操作日期的表
CREATE table liucunlv(
SELECT User_ID,date
FROM userbehavior_beifen
GROUP BY User_ID,date
ORDER BY User_ID,date
);
-- 添加一个字段,记录最早操作日期
ALTER TABLE liucunlv
ADD 最早操作日期 VARCHAR(255);
UPDATE liucunlv SET 最早操作日期=(
SELECT 最早操作日期
FROM (
SELECT User_ID,min(date) 最早操作日期
FROM liucunlv
GROUP BY User_ID
) a
WHERE liucunlv.User_ID=a.User_ID
);
-- 添加一字段记录日期差值
ALTER TABLE liucunlv
add 日期差值 VARCHAR(255);
UPDATE liucunlv SET 日期差值=DATEDIFF(date,最早操作日期);
-- 新建一个表,记录次日,3日,7日,15日,30日留存人数
CREATE TABLE count_liuvunlv(
SELECT 最早操作日期,
sum(case when 日期差值='1' then 1 else 0 end) as 次日,
sum(case when 日期差值='3' then 1 else 0 end) as '3日',
sum(case when 日期差值='7' then 1 else 0 end) as '7日',
sum(case when 日期差值='15' then 1 else 0 end) as '15日',
sum(case when 日期差值='30' then 1 else 0 end) as '30日'
FROM liucunlv
GROUP BY 最早操作日期
ORDER BY 最早操作日期
);
-- 查询留存率
SELECT 最早操作日期,
CONCAT(cast((次日/当日)*100 as DECIMAL(18,2)),'%') as 次日留存率,
CONCAT(cast((`3日`/当日)*100 as DECIMAL(18,2)),'%') as '3日留存率',
CONCAT(cast((`7日`/当日)*100 as DECIMAL(18,2)),'%') as '7日留存率',
CONCAT(cast((`15日`/当日)*100 as DECIMAL(18,2)),'%') as '15日留存率',
CONCAT(cast((`30日`/当日)*100 as DECIMAL(18,2)),'%') as '30日留存率'
FROM count_liuvunlv;
部分数据如下所示
从不同的时间来看,留存率整体是比较低的。
(3)用户价值(RFM分析)
用户价值分析包括三个方面,最近消费日期(R),指定时间段消费频率(F),消费金额(M),源数据不包含用户消费,故从最近消费日期、指定时间段消费频率进行分析,此次分析将指定时间段设定为2021年4月1日至2021年4月30日,现对用户进行RFM分析。
R表
-- 建表存放R值
CREATE TABLE R_value (
SELECT User_ID,max(date) as 最近购买日期
FROM userbehavior_beifen
WHERE behavior_type='pay'
GROUP BY User_ID
);
-- 计算与2021-04-30时间间隔并赋R值
-- 间隔<=2 ->5, 2<间隔<=4 ->4, 4<间隔<=6 ->3,6<间隔<=8 ->2,间隔>8 ->1
ALTER TABLE R_value
ADD 日期间隔 VARCHAR(255);
ALTER TABLE R_value
ADD R值 VARCHAR(255);
UPDATE R_value SET
日期间隔 = DATEDIFF('2021-04-30',最近购买日期);
UPDATE R_value SET
R值 = (
case when 日期间隔<=2 then 5
when 日期间隔<=4 then 4
when 日期间隔<=6 then 3
when 日期间隔<=8 then 2
else 1 end
);
F表
-- 向F_value表中插入购买频率及计算F值
-- 购买次数<=2 ->1, 购买次数<=4 ->2, 购买次数<=6 ->3, 购买次数<=8 ->4, 购买次数>8 ->5
CREATE table F_value (
SELECT User_ID,count(*) 购买次数
FROM userbehavior_beifen
WHERE behavior_type='pay'
GROUP BY User_ID
);
ALTER TABLE f_value add F值 VARCHAR(255);
UPDATE f_value set F值=
(case when 购买次数<=2 then 1
when 购买次数<=4 then 2
when 购买次数<=6 then 3
when 购买次数<=8 then 4
else 5 end);
-- 计算R平均值 2.3
SELECT avg(R值) from r_value;
CREATE table R值高低 (
SELECT User_ID,
(case when R值>=2.3 then '高'
else '低' end) R值高低
from r_value
);
-- 计算F平均值 1.6
SELECT avg(F值) from f_value;
CREATE table F值高低 (
SELECT User_ID,
(case when F值>=1.6 then '高'
else '低' end) F值高低
from f_value
);
-- 将用户进行分类
SELECT r.User_ID,
(case when r.`R值高低`='高' and f.`F值高低`='高' then '重要高价值用户'
when r.`R值高低`='低' and f.`F值高低`='高' then '重要保留用户'
when r.`R值高低`='高' and f.`F值高低`='低' then '重要发展用户'
when r.`R值高低`='低' and f.`F值高低`='低' then '重要挽留用户'
end
) 用户分类
FROM r值高低 r
left join f值高低 f
on r.User_ID=f.User_ID;
-- 计算各分类用户数
SELECT 用户分类,COUNT(*) 计数
FROM (
SELECT r.User_ID,
(case when r.`R值高低`='高' and f.`F值高低`='高' then '重要高价值用户'
when r.`R值高低`='低' and f.`F值高低`='高' then '重要保留用户'
when r.`R值高低`='高' and f.`F值高低`='低' then '重要发展用户'
when r.`R值高低`='低' and f.`F值高低`='低' then '重要挽留用户'
end
) 用户分类
FROM r值高低 r
left join f值高低 f
on r.User_ID=f.User_ID
) user_category
GROUP BY 用户分类;
从用户RFM分析可以看出,重要挽留用户占比最多,其次是重要保留用户和重要发展用户,重要高价值用户占比最少,头部20%用户贡献了80%的GMV,针对这部分用户运营需要想办法保留住。
3、商品维度
(1)热门商品分析
Top10商品
-- 各类别前10商品
SELECT product_ID,COUNT(product_ID) 点击
from userbehavior_beifen
WHERE behavior_type='clk'
GROUP BY product_ID
ORDER BY 点击 desc
LIMIT 10;
SELECT product_ID,COUNT(product_ID) 收藏
from userbehavior_beifen
WHERE behavior_type='fav'
GROUP BY product_ID
ORDER BY 收藏 desc
LIMIT 10;
SELECT product_ID,COUNT(product_ID) 加购
from userbehavior_beifen
WHERE behavior_type='cart'
GROUP BY product_ID
ORDER BY 加购 desc
LIMIT 10;
SELECT product_ID,COUNT(product_ID) 支付
from userbehavior_beifen
WHERE behavior_type='pay'
GROUP BY product_ID
ORDER BY 支付 desc
LIMIT 10;
相关度分析
-- 浏览(点击)商品前10 与 支付商品前10 交叉商品
SELECT click.product_ID,click.`点击`,pay.`支付`
from (
SELECT product_ID,COUNT(product_ID) 点击
from userbehavior_beifen
WHERE behavior_type='clk'
GROUP BY product_ID
ORDER BY 点击 desc
LIMIT 10
) click
join
(
SELECT product_ID,COUNT(product_ID) 支付
from userbehavior_beifen
WHERE behavior_type='pay'
GROUP BY product_ID
ORDER BY 支付 desc
LIMIT 10
) pay
on click.product_ID=pay.product_ID;
仅有三个交叉商品,可能是人货不匹配,运营需提高广告精准投放。
(2)商品画像
-- 购买top10商品所属类别
SELECT productcategory_ID,count(a.product_ID) 数量
FROM (
SELECT product_ID,COUNT(product_ID) 支付
from userbehavior_beifen
WHERE behavior_type='pay'
GROUP BY product_ID
ORDER BY 支付 desc
LIMIT 10
) a
join item_profile b
on a.product_ID=b.`product _ID`
GROUP BY productcategory_ID;
支付量Top10的商品所属类别为种类13,4和38,可加大该三种类别商品推荐
支付量Top10的商品所属城市为城市224,383,373,248,344和386,其中,城市224与用户画像中所占比最高的区域匹配。
支付量Top10的商品所属标签为169;94、-1、94;343和94;169
六、总结
1、总体上看,用户活跃度不高且支付转化率不高,可能跟产品性质有关,一般使用频率不高,只有有需求时才会产生用户行为。
2、每天的人均成交量在4月4号最高,推测可能与清明假期有关,反乡或旅游等出行增多。
3、每日内,中午时刻人均成交量最高,凌晨4-7时成交量最低。人均访问量在20-24时和11时最高,可增加在这些时刻的广告投放。
4、用户的复购率较低约为50%,留存率整体较低,说明用户粘度不高,平台对用户的吸引力很低,平台可以从页面设计、精准推送、服务制度以及售后服务等几个方面寻找原因。
5、转化率分析中,流失率最大的环节处于点击—加购环节,大量用户在这一环节流失,猜测可能是商品类别不符合用户期待,或商品价格超过了用户的心里预期。
6、支付用户的年龄主要集中在46-60岁年龄段,性别集中在女性(60%以上),职业方面职业3 成交人次最多,可针对这些用户进行推广。
7、用户留存分析中,留存率整体比较低。
8、RFM分析可以看出,重要挽留用户占比最多,其次是重要保留用户和重要发展用户,重要高价值用户占比最少,头部20%用户贡献了80%的GMV,针对这部分用户运营需要想办法保留住。
9、点击商品和支付商品前10中仅有三个交叉商品,可能是人货不匹配,运营需提高广告精准投放。同时,还可针对支付占比高的品类商品增加推广。