目录
（8）每月新客，老客复购率分析（老用户即新客第一个月购买，次月复购）
一、字段处理
1. 字段说明
2. 增加新列
新增时间、月份、小时、周几列。根据数据分列得到这几列数据，其中周日-周六为1-7。删除原日期列event_time。
部分数据如上图所示。
二、数据清洗-excel
1.查看数据缺失情况
category_code、brand即类别和品牌列存在数据缺失的情况，对于category_code选择用R来代替缺失值而不是选择删除缺失值。brand缺失值数量较少，所以将缺失值直接删除。
2.查看重复值
order_id订单编号存在重复值，就是一个订单有包含多个商品。所以不删除重复值。
3.查看数据是否有异常
（1）将几个id的格式转为文本类型
直接通过数据-分列-选定文本格式，完成分列即可将数据转化为文本类型。
（2）查看年龄和价格是否存在异常值
通过查看，年龄列并无异常值，price和amount最小值为0，这类商品应该就是免费类的商品，所以也不属于异常值。应该进一步分析，购买了0元商品的用户，后续是否还有购买了其他的商品。
（3）查看其他字段有无异常值
通过观察，日期列中有1970年的数据，将这些异常值删除。
三、进行数据分析
1.总指标
（1）GMV
通过计算得到，总的GMV约为1.15亿元。
（2）每月的GMV
通过每月GMV的折线图，可以清晰地知道从1月到四月趋于平缓，且每月GMV不到5000000元。从4月到4月快速上升，且在8月达到了最高点。8月到11月也在快速下降。
（3）客单价
得到客单价1240，订单均价296.
2.用户行为分析
（1）用户总数量
select count(distinct user_id) from `电子产品销售分析`得到用户总数量为6906人。
（2）各地区用户数量
select local,count(distinct user_id) from `电子产品销售分析`
group by local
由上述图标可知广东的用户最多，其次是北京和上海，其余城市基本都在500左右。
a 2020年人口普查数据
通过2020年人口普查数据，计算各个城市的人口占比，可以据此进行拉新。截取部分数据如下表所示：
| 地区 | 人口数 | 2020年人口比重 | 2010年人口比重 |
| 全国 | 1411778724 | 100 | 100 |
| 广东 | 126012510 | 8.93 | 7.79 |
| 山东 | 101527453 | 7.19 | 7.15 |
| 河南 | 99365519 | 7.04 | 7.02 |
| 江苏 | 84748016 | 6 | 5.87 |
将各城市人口数匹配到各城市用户数中，具体如下：
由上图可得，广东的人口数最多，其次是江苏、四川、浙江、湖南，且广东的用户数也最多，所以在广东进行拉新活动的性价比是最高的。
（2）用户性别的分布
select sex,count(user_id) from
(select user_id,sex from `电子产品销售分析`
group by user_id) a
group by sex
男女各占一半。
（3）年龄分布
select age,count(user_id) from
(select user_id,age from `电子产品销售分析`
group by user_id) a
group by age
其中，最小年龄为16，最大年龄为50。将年龄段分成7等份，步长为5，可分为(15,20]岁、(20,25]岁、(25,30]岁、(30,35]岁、(35,40]岁、(40,45]岁、(45,50]岁。
由上述结果可知，(15,20]岁年龄段用户数量最少，(20,25]岁年龄段用户数量最多，其他都相对比较平均，在1000左右。
（4）各年龄段的消费金额及下单数量
select age,count(order_id),sum(price) from
(select order_id,age,sum(price) price
from `电子产品销售分析`
group by order_id) aa
group by age
由上述结果可知，销售数量越多，销售金额就越多。其中(15,20]岁最高。
（5）男性女性的下单数量和销售金额
select sex,count(order_id),round(sum(price),2) from
(select order_id,sex,sum(price) price
from `电子产品销售分析`
group by order_id) aa
group by sex
发现男性女性的消费金额与下单次数均比较平均。
（6）分析购买了0元商品的用户
select user_id,sum(price)
from `电子产品销售分析`
where user_id in
(select distinct user_id
from `电子产品销售分析`
where price = 0)
group by user_id
从产品类别可知29个购买0元商品的用户，0元商品是中奖得到的，并且中奖的用户只有一个用户没有下过单。而且这29个用户的客单价高达34832元(总的客单价为1240元)。
（7）二八定律-找出累计贡献销售额80%的那批用户
数据太多，就不一一列举了。由上表计算可知前27%的顾客占了80%的销售额，所以要维护好这27%的用户。
（8）客户消费金额的分位数
用户平均消费金额大于75%分位数,即存在着高消费的客户。
3.产品分析
（1）销量前十的产品
select product_id,count(1) aaa
from `电子产品销售分析`
group by product_id
order by count(1) desc
limit 10
对于热销产品，应该时刻关注他们的库存量，避免发生缺货情况.。
（2）销量少于10的商品
select product_id,count(1) aaa
from `电子产品销售分析`
group by product_id
having count(1) < 10
共有2003个产品销量低于10，上述数据仅截取部分数据。对于销量低于10的产品，可以考虑促销进行清仓处理。
（3）销售额前十的商品
select product_id,round(sum(price),2) price1
from `电子产品销售分析`
group by product_id
order by sum(price) desc
limit 10
销售额并不代表利润，而是要结合成本来看。
（4）销量前十的产品类别 category_code
select category_code,count(1) aaa
from `电子产品销售分析`
group by category_code
order by count(1) desc
limit 10
除去异常填充的类别，最受欢迎的智能手机和笔记本电脑，且智能手机销量是笔记本电脑的4倍。
（5）对于手机,销量前五的品牌-brand
select brand,count(1) aaa
from `电子产品销售分析`
where category_code = 'electronics.smartphone'
group by brand
order by count(1) desc
limit 5
销售数量最高的牌子是samsung，其次是apple，其中samsung销量占了一半，apple销量占了四分之一。
（6）分析销量前五的人群特征
a、samsung牌子
年龄分布
SELECT case when age <= 20 then '(15,20]岁'
when age <= 25 then '(20,25]岁'
when age <= 30 then '(25,30]岁'
when age <= 35 then '(30,35]岁'
when age <= 40 then '(35,40]岁'
when age <= 45 then '(40,45]岁'
else '(45,50]岁'
end age1
,count(1) FROM `电子产品销售分析`
where brand = 'samsung'
group by age1
性别分布
SELECT sex,count(1) FROM `电子产品销售分析`
where brand = 'samsung'
group by sex
地域分布
SELECT local,count(1) FROM `电子产品销售分析`
where brand = 'samsung'
group by local
由上述结果可知，年龄分布比较平均，性别分布也比较平均，地域分布占比最多的是广东、北京、上海。
b、apple牌子
年龄分布
SELECT case when age <= 20 then '(15,20]岁'
when age <= 25 then '(20,25]岁'
when age <= 30 then '(25,30]岁'
when age <= 35 then '(30,35]岁'
when age <= 40 then '(35,40]岁'
when age <= 45 then '(40,45]岁'
else '(45,50]岁'
end age1
,count(1) FROM `电子产品销售分析`
where brand = 'apple'
group by age1
性别分布
SELECT sex,count(1) FROM `电子产品销售分析`
where brand = 'apple'
group by sex 
地域分布
SELECT local,count(1) FROM `电子产品销售分析`
where brand = 'apple'
group by local
人群特征基本跟Samsung一致。
4.销售情况分析
（1）各省销量，销售额分析
SELECT local,count(1),round(sum(price),2) FROM `电子产品销售分析`
group by local
由上述结果可知，广东的销量和销售额都是最高的，其次是上海和北京。
（2）按日期分析销售额与销量的总体走势
select date(date),sum(price),count(1)
from `电子产品销售分析`
group by date(date)部分结果如下所示：
从数据显示，618和双11当天，销量和销售额并没有很高，反而很低。
假如数据真实，那么618和双11搞的活动效果是非常差的，要进行复盘。
（3）按月份对销量和销售额进行分析
select month,round(sum(price),2),count(1)
from `电子产品销售分析`
group by month
由上述结果可知，销售额1-8月呈上升趋势，8-11月销售额在呈下降趋势。
销量和销售额基本上一致。
a、对新老顾客销量跟销售额的分析
计算每月新客的销售额和销量
SELECT month,round(sum(price),2),count(1) from
(select case when month = month_min then '新客户' else '老客户' end aa,month,price
from `电子产品销售分析` a join
(select user_id,min(month) month_min
from `电子产品销售分析`
group by user_id) b on a.user_id = b.user_id) d
where aa = '新客户'
GROUP BY month
计算老顾客的销量和销售额
SELECT month,round(sum(price),2),count(1) from
(select case when month = month_min then '新客户' else '老客户' end aa,month,price
from `电子产品销售分析` a join
(select user_id,min(month) month_min
from `电子产品销售分析`
group by user_id) b on a.user_id = b.user_id) d
where aa = '老客户'
GROUP BY month
b、新老用户销售额对比
除了11月份，老用户的销售额都是比新用户的高，说明客户留存不错，老顾客回购多。
c、新老客户销售额对比分析
情况跟销售额一致。
（4）按周分析
select weekday,round(sum(price),2),count(1)
from `电子产品销售分析`
group by weekday
周日-周六为1-7。
由上述结果可知，周六日的销售量和销量都比平常的要高，客户比较喜欢在周末购物，可以在周末推送商品给顾客，也可以适当选一两件商品进行清仓。
（5）按小时分析
select hour,round(sum(price),2),count(1)
from `电子产品销售分析`
group by hour
销量和销售额在凌晨一点就开始上升，升到9点就达到了峰值。
用户喜欢在6-12点下单，可以在此时间段推送商品。
（6）每月新增用户数：第一次购买的用户
select aa.month1,aa.a,bb.b,(a-b)/b from
(select month1,count(1) a from
(select user_id,min(month) month1
from `电子产品销售分析`
group by user_id) d
group by month1) aa
left join (select month1,count(1) b from
(select user_id,min(month) month1
from `电子产品销售分析`
group by user_id) d
group by month1) bb
on aa.month1 = bb.month1 + 1
2月比1月的下降了将近一倍，5月比4月增长了2两倍，而10比9月增长了将近134倍（如果不是数据错误），说明拉新活动效果显著，可以多搞几次这样的拉新活动。
（7）每月复购率
每月复购率 = 每月购买两次以上的用户 / 总购买次数
select bb.month, bb.复购人数,cc.总购买人数,复购人数/总购买人数 '复购率'from (select month,count(user_id) '复购人数' from
(select month,user_id,count(distinct order_id) a
from `电子产品销售分析`
group by month,user_id) aa
where a >= 2
group by month) bb
left join
(select month,count(distinct user_id) '总购买人数' from `电子产品销售分析` group by month) cc
on bb.month = cc.month 
（8）每月新客，老客复购率分析（老用户即新客第一个月购买，次月复购）
每月的复购人数：
新用户复购数
select month,count(user_id) from
(SELECT month,user_id,count(distinct order_id) '复购次数' from
(select case when month = month_min then '新客户' else '老客户' end aa,month,a.user_id,a.order_id
from `电子产品销售分析` a join
(select user_id,min(month) month_min
from `电子产品销售分析`
group by user_id) b
on a.user_id = b.user_id) d
where aa = '新客户'
GROUP BY month,user_id) aaaa
where 复购次数 >= 2
group by month
老用户复购人数：
select month,count(user_id) from
(SELECT month,user_id,count(distinct order_id) '复购次数' from
(select case when month = month_min then '新客户' else '老客户' end aa,month,a.user_id,a.order_id
from `电子产品销售分析` a join
(select user_id,min(month) month_min
from `电子产品销售分析`
group by user_id) b
on a.user_id = b.user_id) d
where aa = '老客户'
GROUP BY month,user_id) aaaa
where 复购次数 >= 2
group by month
除了10月是新客户复购率高，其余月份都是老用户复购率高。
5.RFM模型
R：最近一次消费（Recency）：
当前时间—最近一次购买的日期（当前时间选取文件中的最新日期：2020-11-21）
F：消费频率（Frequency）
M：消费金额（Monetary）
注：同一个用户一天内购买多次视为一次
select user_id,max(date(date)) date,round(sum(price),2) '销售金额'
from `电子产品销售分析`
group by user_id
select user_id,max(date(date)) date,round(sum(price),2) 'M',COUNT(distinct order_id) 'F'
from `电子产品销售分析`
group by user_id 
select user_id,DATEDIFF('2020-11-21',max(date(date))) 'R',round(sum(price),2) 'M',COUNT(distinct date(date)) 'F'
from `电子产品销售分析`
group by user_id 
R的打分：
[0-20]:5分
(20-40]:4分
(40-60]:3分
(60-80]:2分
80以上:1分
F的打分:
[0-5]:1分
(5-10]:2分
(10-15]:3分
(15-20]:4分
20以上:5分
M的打分:
[0-1000]:1分
(1000-5000]:2分
(5000-10000]:3分
(10000-15000]:4分
15000以上:5分
select user_id,case when R <= 20 then 5 when R <= 40 then 4 when R <= 60 then 3 when R <= 80 then 2 else 1 end 'R',case when F <= 10 then 1 when F <= 20 then 2 when F <= 30 then 3 when F <= 40 then 4 else 5 end 'F',case when M <= 1000 then 1 when M <= 3000 then 2 when M <= 9000 then 3 when M <= 15000 then 4 else 5 end 'M'
from
(select user_id,DATEDIFF('2020-11-21',max(date(date))) 'R',round(sum(price),2) 'M',COUNT(distinct order_id) 'F'
from `电子产品销售分析`
group by user_id) aa
select user_id,if(R>3.8,1,0) 'R',if(F>3.6,1,0) 'F',if(M>3.5,1,0) 'M',concat(if(R>3.8,1,0),if(F>3.6,1,0),if(M>3.5,1,0)) result from
(select user_id,case when R <= 20 then 5 when R <= 40 then 4 when R <= 60 then 3 when R <= 80 then 2 else 1 end 'R',case when F <= 5 then 1 when F <= 10 then 2 when F <= 15 then 3 when F <= 20 then 4 else 5 end 'F',case when M <= 1000 then 1 when M <= 3000 then 2 when M <= 9000 then 3 when M <= 15000 then 4 else 5 end 'M'
from
(select user_id,DATEDIFF('2020-11-21',max(date(date))) 'R',round(sum(price),2) 'M',COUNT(distinct date(date)) 'F'
from `电子产品销售分析`
group by user_id) aa) bb
group by user_id
select case when result = 111 then '重要价值客户' when result = 101 then '重要发展客户' when result = 011 then '重要保持客户' when result = 001 then '重要挽留客户' when result = 110 then '一般价值客户' when result = 100 then '一般发展客户' when result = 010 then '一般保持客户' else '一般挽留客户' end '客户类型',COUNT(1) from
(select user_id,if(R>3.8,1,0) 'R',if(F>3.6,1,0) 'F',if(M>3.5,1,0) 'M',concat(if(R>3.8,1,0),if(F>3.6,1,0),if(M>3.5,1,0)) result from
(select user_id,case when R <= 20 then 5 when R <= 40 then 4 when R <= 60 then 3 when R <= 80 then 2 else 1 end 'R',case when F <= 5 then 1 when F <= 10 then 2 when F <= 15 then 3 when F <= 20 then 4 else 5 end 'F',case when M <= 1000 then 1 when M <= 3000 then 2 when M <= 9000 then 3 when M <= 15000 then 4 else 5 end 'M'
from
(select user_id,DATEDIFF('2020-11-21',max(date(date))) 'R',round(sum(price),2) 'M',COUNT(distinct date(date)) 'F'
from `电子产品销售分析`
group by user_id) aa) bb
group by user_id) cc
group by 客户类型 
重要客户人数为3478。
其中重要价值客户人数为2481，约占重要顾客的70%，对于重要价值顾客，要给予他们VIP的服务，同时也要留意他们的购买反馈。
重要发展顾客人数为438，约占重要顾客的12.6%，这类顾客的购买次数不是很高，要提高他们的购买率，可以通过发放优惠券等方式。
重要保持客户的人数为370，约占重要顾客的10.6%，这类顾客的最近一次购买时间相隔太久，应该推一些商品信息或者打电话给他们，也可以通过发放优惠券的方式来提高他们的复购率。
重要挽留顾客的人数为189，约占重要顾客的5.4%，对于这类顾客，我们要想办法挽回他们，可以通过发信息，推送优惠券等方式。
一般客户人数为3428。
对于一般价值，一般发展，一般保持的顾客，在维护好重要顾客的情况下，可以利用一些资源维护这批顾客。但是对于一般挽留的顾客，人数高达1503人，这批顾客注定要流失的，在没有资源的情况下，我们需要放弃这批顾客。