为什么进行用户行为数据分析?
1.大环境:电商红利期已去,新客流量成本增大
2.品牌:存量老用户维护成本低且转化率高,需进行精细化运营
3.提出可落地、结合业务运营的,提高运营效率、赋能业务增长的建议
数据来源:
注:本次数据的清洗、筛选等处理用 SQL进行实现
一.数据清洗
处理(一般指删除)重复数据、空值、不需要分析的数据。目的是获得有效数据,精准有效的对各个业务流程进行数据分析。
1.重复值处理
①查找重复数据
select
user_id, item_id, time_stamp
from userbehavior
group by
user_id , item_id, time_stamp
having count(*) > 1;
②删除重复数据
alter table userbehavior
add id int primary key auto_increment
delete
from userbehavior
where id in
(select id from
(select id, user_id, item_id, time_stamp, row_number() over(partition by user_id, item_i
d, time_stamp) '重复值'
from userbehavior) as temp
where 重复值 >1
2.空值处理
#查找是否有空值
select
*
from userbehavior
where user_id is null
or item_id is null
or category_id is null
or behavior_type is null
or time_stamp is null
发现无空值
3.不需要数据的处理
主要:查找是否存在超出分析时间的记录
①时间戳处理
将时间戳转换成四个字段:weekday,dates,datetime,hours。后续需要利用这些字段进行分析。
alter table userbehavior
add dates varchar(255)
update userbehavior set dates = from_unixtime(time_stamp,'%Y-%m-%d')
alter table userbehavior
add datetime varchar(255)
update userbehavior set datetime = from_unixtime(time_stamp,'%Y-%m-%d %H-%i-%s')
alter table userbehavior
add hours varchar(255)
update userbehavior set hours = from_unixtime(time_stamp,'%H')
alter table userbehavior
add weekday varchar(255)
update userbehavior set weekday = weekday(datetime)+1
②除去超出分析时间的数据
需要分析11月25日-12月3日之间的用户行为,在此时间段之外的行为都不应该被纳入分析。
delete
#select *(查询)
from userbehavior
where
dates < '2021-11-25'
or dates > '2021-12-03'
经查询,已经将超出分析时间之外的数据删除。
自此,用SQL清洗数据已完成,可以将这份“干净”的数据用于接下来的分析。
接下是数据分析的主体部分,主要有三大步:
1.明确问题:通过AIPL模型分析,挖掘出可进行落地分析的目标
2.分析原因:结合业目标,和分析经验来分析原因
3.给出建议:结合原因给出具体可落地的建议
明确问题
1.确定会员总数
select
count(distinct user_id) 会员总人数
from userbehavior
2.通过AIPL模型进行整体分析
A - Awareness 认知:浏览
I - Interested 兴趣:收藏、加购
P - Purchase 购买:购买
L - Loyalty 忠诚:复购
现在需要计算A(浏览view)、I(收藏favor加购cart)、P(buy)、L(n*buy)四个过程
①计算前需对表结构进行行列互换(之后仅展示筛选结果)
②计算A(浏览view)、I(收藏favor加购cart)、P(buy)的值
CREATE VIEW behavior AS
SELECT user_id,
datetime,
dates,
hours,
CASE behavior_type WHEN 'pv' THEN 1 ELSE 0 END AS 'view',
CASE behavior_type WHEN 'fav' THEN 1 ELSE 0 END AS 'favor',
CASE behavior_type WHEN 'cart' THEN 1 ELSE 0 END AS 'cart',
CASE behavior_type WHEN 'buy' THEN 1 ELSE 0 END AS 'buy'
FROM userbehavior
select sum(view) 'A',
sum(favor)+sum(cart) 'I', sum(buy) 'P'
from behavior
DROP VIEW