数据来源：https://www.kaggle.com/mkechinov/ecommerce-events-history-in-cosmetics-shop?select=2020-Jan.csv
分析工具：Excel、MySQL、PowerBI
1. 数据准备
该数据集包含北美地区化妆品网店的两个月的用户行为数据（2020.01-2020.02），共8421434行，9个特征变量；每一行对应一条用户行为数据，关联着商品和用户。包含的变量有：
-
空值处理
使用SQL语句查询数据中各字段的NULL值及空值：
SELECT
count(*),
count(case when event_time is null or event_time = '' then 1 else null end) NNevent_time,
count(case when event_type is null or event_type = '' then 1 else null end) NNevent_type,
count(case when product_id is null or product_id = '' then 1 else null end) NNproduct_id,
count(case when category_id is null or category_id = '' then 1 else null end) NNcategory_id,
count(case when category_code is null or category_code = '' then 1 else null end) NNcategory_code,
count(case when brand is null or brand = '' then 1 else null end) NNbrand,
count(case when price is null or price = '' then 1 else null end) NNprice,
count(case when user_id is null or user_id = '' then 1 else null end) NNuser_id,
count(case when user_session is null or user_session = '' then 1 else null end) NNuser_session
FROM data.user_behavior
结果如下：
可以发现，category_code字段的数据严重缺失，其次为brand，在这里只将category_code列（不做分析）删除：
alter table user_behavior drop column category_code;
alter table user_behavior drop column user_session;
- 时间字段拆分：日期/小时/星期
alter table user_behavior add column date_1 date;
update user_behavior set date_1 = date(event_time);
alter table user_behavior add column hour_1 char(2);
update user_behavior set hour_1 = hour(event_time);
alter table user_behavior add column weekday_1 char(1);
update user_behavior set weekday_1 = weekday(event_time);
2. 数据分析
-
流量分析
PV(页面浏览次数)、UV(独立访客数)、PV/UV(平均每位用户访问页面次数)
select count(distinct user_id) as 'UV',
(select count(*) from user_behavior
where event_type = 'view') as 'PV',
(select count(*) from user_behavior
where event_type = 'view')/(count(distinct user_id) ) as 'PV/UV'
from user_behavior
-
用户活跃/留存分析
日活跃用户(DAU)
select date_1 as 'Date',count(distinct user_id) as 'DAU' from user_behavior
group by date_1
在2020年1月1日处日活跃用户数极低，其他时间的DAU出现周期性波动。对于出现极低的现象，可能是由于新年的缘故，用户极少去浏览网站，手机粘度下降。周期性波动是以周为单位，波谷大多出现在周末。平台可根据DAU的分布特征采取相应的营销手段。
留存率
以2020-01-01作为首日，计算次日、3日、7日、30日和50日留存：
select count(distinct user_id) as 首日用户数
from user_behavior
where date_1 = '2020-01-01'；
-- 次日留存 --
select count(distinct user_id) as 次日留存 from user_behavior
where date_1 = '2020-01-02'
and user_id in (select distinct user_id from user_behavior where date_1 = '2020-01-01');
-- 3日留存 --
select count(distinct user_id) as 3日留存 from user_behavior
where date_1 = '2020-01-03'
and user_id in (select distinct user_id from user_behavior where date_1 = '2020-01-01');
-- 7日留存 --
select count(distinct user_id) as 7日留存 from user_behavior
where date_1 = '2020-01-07'
and user_id in (select distinct user_id from user_behavior where date_1 = '2020-01-01');
-- 30日留存 --
select count(distinct user_id) as 30日留存 from user_behavior
where date_1 = '2020-01-30'
and user_id in (select distinct user_id from user_behavior where date_1 = '2020-01-01');
-- 50日留存 --
select count(distinct user_id) as 50日留存 from user_behavior
where date_1 = '2020-02-19'
and user_id in (select distinct user_id from user_behavior where date_1 = '2020-01-01');
计算结果如下：
首日用户数：12296
以2020.01.01为首日，可以发现用户流失严重，平台需引以重视。建议采取会员制度、优惠券发放活动等方式提高用户粘性。
-
产品分析
商品销量前五名
select product_id,count(*) as 数量 from user_behavior
where event_type = 'purchase'
group by product_id
order by 数量 desc
limit 5;
编号为5809910销量排名第一，占比35.08%。
view、cart及purchase前五名
select product_id,count(*) as 数量 from user_behavior
where event_type = 'view'
group by product_id
order by 数量 desc
limit 5;
select product_id,count(*) as 数量 from user_behavior
where event_type = 'cart'
group by product_id
order by 数量 desc
limit 5;
select product_id,count(*) as 数量 from user_behavior
where event_type = 'purchase'
group by product_id
order by 数量 desc
limit 5;
分别从用户的三个行为可视化分析可知，与销量成类似，编号为5809910的商品不论是浏览人数、加购人数还是购买人数都是占比最大的，该商品非常受欢迎。
品牌（brand）购买量前五
select brand,count(user_id) as 数量 from user_behavior
where brand <> '' and event_type = 'purchase'
group by brand
order by 数量 desc
limit 5;
Runail品牌的购买量最多。
品牌（brand）销售额前五
select brand,sum(price) as 销售额 from user_behavior
where brand <> '' and event_type = 'purchase'
group by brand
order by 销售额 desc
limit 5;
销售额还是Runail品牌的最多，而其他品牌的销售额和销售量不尽相同。并且排名前三的都是美甲行业，为避免平台商品往单一化发展，商家需要加大其他品牌产品的宣传力度，或者将其和美甲商品捆绑销售，促进平台商品多样化。
用户行为转化分析
select event_type,count(*) as 数量 from user_behavior
group by event_type
从漏斗图中可以看出，该化妆品平台的页面转化率高达57.55%，购买转化率为12.67%，购物车移除率为40.78%，整体表现较好。
复购率分析(60天)
select
count(user_id) 购买人数,
count(if(a.times>1,1,null)) as 复购人数,
count(if(a.times>1,1,null))/count(user_id) as 复购率
from(select user_id, count(distinct user_session) as times from user_behavior2
where event_type = 'purchase'
group by user_id) a;
60天复购率高于15%，达到16.33%，表现良好。
- RFM用户价值分析
select R.user_id,R.Recency,F.Frequency,M.Monetary
from (
select user_id, datediff(date('2020-03-01'),date(a.R_date)) as Recency
from (
select user_id , max(date_1) as R_date
from user_behavior
where event_type= 'purchase' and price <> ''
group by user_id
) a
) R
left join (
select user_id, count(*) as Frequency
from user_behavior
where event_type='purchase' and price <> ''
group by user_id
) F
on R.user_id = F.user_id
left join (
select user_id, sum(price) as Monetary
from user_behavior
where event_type = 'purchase' and price <> ''
group by user_id
) M
on R.user_id=M.user_id
where M.Monetary > 0
从RFM分类模型可以看出，32%的客户属于新客户，29%的客户为流失客户，只有8%的客户属于重要价值用户（这里RFM分类方法不准确，只是为了实践SQL代码）。
重要价值客户
最近有购买行为，累计交易次数多，累计交易金额高
重要唤回客户
交易次数和交易金额高，但是最近没有交易
重要深耕客户
交易金额大，而且最近有交易
重要挽留客户
交易金额大，但是最近没有交易而且交易次数比较少
潜力客户
交易次数多，且最近有交易
新客户
最近刚开始交易的用户
一般维持客户
交易次数多，但是都是小额消费的，且最近没有交易
流失客户
RFM 值都较低，基本流失了
3. 总结
2020年1月1日，DAU处于极低值，可能是新年期间用户对手机粘性下降造成的。
从留存率来看，用户留存表现很差，30日留存跌至3.5%。
5809910号商品在前五中占的购买量和销售额最高，可以捆绑带动其他产品销售；并且美甲行业产品销售最好，平台应加大其他品类化妆品的宣传，促进平台多元化发展。
此化妆品电商的页面转化率为57.55%，购买转化率为12.67%，整体表现良好。
从RFM分类模型可以看出，32%的客户属于新客户，29%的客户为流失客户，只有8%的客户属于重要价值用户。通过优惠券和会员制等方式提高新用户粘性、挽回流失用户。
