业务问题：分析最近1周的用户行为转化

1.数据集

2.问题分析

数据部分截图样例：

其中，“行为类型”列中的值有4种，对应4种用户行为，分别是：用户对商品进行浏览、收藏、加购、购买行为。

业务场景：
地点是：公司淘宝店铺
时间范围是：这个实战项目当时分析的时间是最近1周时间（也就是11月25日-12月3日）
指标含义：通过沟通明确了衡量用户行为转化的指标是用户行为次数转化率。

问题分析：分析最近1周的用户行为转化其实是想看最近1周转化效果如何，如果转化率低，需要找到问题发生的原因，从而优化，提升转化率。

分析思路：对于数据异动问题，需要明确问题、分析原因并提出建议。

step1明确问题：
可以把业务流程和漏斗分析方法结合起来，就可以知道各个业务环节的转化率了。
这个漏斗模型中，
A（Awareness认知）：表示用户在平台上的浏览等行为
I（Interested兴趣）：表示用户对平台的兴趣，具体行为，对应这个实战项目里的“行为类型”数据是对商品的收藏，或者把商品加入购物车
P（Purchase 购买）：表示用户对商品进行了购买
L（ Loyalty 忠诚）：表示用户对商品进行复购等

环节转化率定义为，环节转化率=本环节行为次数／上一环节行为次数。可以通过环节转化率可以衡量相邻业务环节的转化情况。
要判断转化率好坏，需要一个比较对象。和去年同期的数据来做一些对比，看是不是有其他环节出了问题。
接下来，分析AIPL漏斗模型里的每个环节的转化率。

数据预处理（便于计算转化率）

create view 行列互换 as
select 用户ID , 行为时间,行为日期,行为小时,
	max(case 行为类型 when '浏览' then 1 else 0 end)as 浏览次数,
	max(case 行为类型 when '收藏' then 1 else 0 end)as 收藏次数,
	max(case 行为类型 when '加购' then 1 else 0 end)as 加购次数,
	max(case 行为类型 when '购买' then 1 else 0 end)as 购买次数
from 用户行为
where 行为日期>='2021-11-25' and 行为日期<='2021-12-03'
group by 用户ID,行为日期;

AIPL漏斗分析
A（认知）：浏览次数
I（兴趣）：收藏次数和加购次数
P（购买）: 购买次数
L（ 忠诚）：复购次数，属于复购分析，后面单独计算
写出SQL：如何计算A、I、P

select sum(浏览次数) as 浏览行为次数,
sum(收藏次数)+sum(加购次数) as 收藏和加购行为次数,
sum(购买次数) as 购买行为次数
from 行列互换;

接下来分析L：复购次数
写出SQL，L分析：复购分析
复购分析需要运用窗口函数计算消费次数，然后对所有消费次数大于1的购买行为进行求和，得出重复购买行为次数

create view 消费排名 as 
select *,
	(SELECT 
		count( DISTINCT 行为时间) 
	FROM 行列互换 s2 
	WHERE s2.用户ID = s.用户ID 
	AND s2.行为时间 <= s.行为时间 
	AND s2.购买次数=1)
	as  消费次数
FROM
    行列互换 s 
where s.购买次数=1
ORDER BY
    用户ID,
    行为时间 DESC;

select sum(购买次数) as 重复购买行为次数
from 消费排名
where 消费次数>=2;

将通过SQL计算出的AIPL数据整合到excle并计算转化率

和去年同期进行对比：

step2分析原因：
多维度拆解
从A环节来分析影响用户浏览次数的因素：发送推广内容的时间段是否正确
从I环节来分析影响用户加购次数的因素：用户浏览的商品是否会加购
方法：假设检验

假设1：推广内容的时间段不是用户浏览和转化高峰期
分析思路：
1）先分别计算出一天24小时里，对应的A 和 I 的用户行为次数
2）选择A浏览行为，A-I转化率都大于平均值的时间段，就是投放广告内容的最佳时间
根据上面分析思路，写出对应SQL

create view 行为时间分析 as
select 行为小时,sum(浏览次数) as 浏览行为次数,sum(收藏次数)+sum(加购次数) as 收藏和加购行为次数,
(sum(收藏次数)+sum(加购次数))/sum(浏览次数) as AI转化率
from 行列互换
group by 行为小时;

#比较对象：平均浏览行为次数
select avg(浏览行为次数)
from 行为时间分析;

#比较对象：平均AI转化率
select avg(AI转化率)
from 行为时间分析;

#进行对比分析：选出A浏览行为，A-I转化率都高于平均值的时间段，就是投放广告内容的最佳时间
select *
from 行为时间分析
where 浏览行为次数 > (select avg(浏览行为次数) from 行为时间分析) 
and AI转化率 > (select avg(AI转化率) from 行为时间分析)
order by AI转化率 desc;

拿着假设1得到的最佳投放时间，和推广部门开会讨论后，发现推广部门的投放时间并不是这些时间点，所以假设1成立。

验证假设2：用户浏览的商品和用户感兴趣的商品不一致。
分析思路：
用户浏览的商品TOP100和用户感兴趣的商品TOP100，看重合度是否高。
SQL如下：
写出SQL，用户浏览的商品TOP100
写出SQL，用户感兴趣的商品TOP100
写出SQL，看重合度是否高：多表查询，找到重复的数据

#用户浏览的商品TOP100
select 商品ID,count(*) as 浏览数量
from 用户行为
where 行为类型= '浏览'
group by 商品ID
order by 浏览数量 desc
limit 100;

#用户感兴趣的商品TOP100
select 商品ID,count(*) as 兴趣数量
from 用户行为
where 行为类型 in ('收藏','加购')
group by 商品ID
order by 兴趣数量 desc
limit 100;

#看重合度是否高：多表查询，找到重复的数据
select count(*) as 重复商品数量
from (select 商品ID,count(*) as 浏览数量
from 用户行为
where 行为类型= '浏览'
group by 商品ID
order by 浏览数量 desc
limit 100) as a
inner join (select 商品ID,count(*) as 兴趣数量
from 用户行为
where 行为类型 in ('收藏','加购')
group by 商品ID
order by 兴趣数量 desc
limit 100) as b
on a.商品ID=b.商品ID;

分析结果显示，100个商品里仅有11款款商品既是高浏览量，也是高收藏/加购的，故假设2成立。

step3:提出建议
1.挑选时段投放
2.商品陈列优化

总结：