电商婴儿用品数据分析（SQL+Power Pivot）

项目说明：部分思路参考 电商婴儿用品数据分析(SQL)
数据来源：天池数据集
数据说明：数据来自淘宝和天猫上购买婴儿用品信息，本数据集包括2个文件：
表1：(sample)sam_tianchi_mum_baby_trade_history.csv——交易记录，后面简称为trade
表2：(sample)sam_tianchi_mum_baby.csv——婴儿信息，后面简称为babyinfo

一、分析框架

1. 描述性分析
2. 购买情况的季节因素（购买量和购买人数的时间趋势分析）
3. 商品种类之间的购买情况差异（热门小类、冷门小类）
4. 构建不同商品的用户画像
5. 复购情况分析（整体复购情况、首次复购情况、复购商品种类）

二、数据导入及清洗

/*数据导入*/
drop table if exists trade;
create table trade (
`user_id` char(100),
`auction_id` char(100),
`cat_id` bigint,
`cat1` bigint,
`buy_mount` bigint,
`day` date
);

drop table if exists babyinfo;
create table babyinfo (
`user_id` char(100),
`birthday` date,
`gender` bigint 
);

/*缺失值检查*/
select count(user_id),count(cat1),count(cat_id),count(buy_mount),count(day) from trade;
# 结果均为29971项数据，trade表中无缺失值

select count(user_id),count(birthday),count(gender) from babyinfo;
# 结果均为953项数据，babyinfo表中无缺失值

/*数据集描述性分析*/
/*trade表*/
# 数据集覆盖面
select count(distinct user_id) user_volume,
       count(distinct auction_id) behavior_types,
       count(distinct cat1) broad_types,
       count(distinct cat_id) sec_types,
       count(distinct day) dates
from trade;
# 1. trade数据集总共覆盖了29944位用户的购买行为
# 2. 总共定义了28422种不同的购买行为，即使是同一个user_id不同时间的购买记录对应的auction_id也不同，全部数据中也有少数重复值，没有规律，从数据集中我们难以了解其具体含义，对此字段不做分析
# 3. 总共包含6种商品大类和662种商品细分分类
# 4. 总共统计了949天里面消费者的购买情况

# 不同购买数量对应的消费者人数
select count(user_id) num,buy_mount
from trade
group by buy_mount
order by buy_mount asc;

# 每个商品大类包含多少个二级分类
select cat1 broad_type,count(distinct cat_id) sec_types
from trade
group by cat1
order by sec_types asc;

/*babyinfo表*/
# 数据集覆盖面
select count(distinct user_id) user_volume,
       count(distinct gender) gender_types
from babyinfo;
# 1. babyinfo数据集总共覆盖了953个婴儿的出生信息
# 2. gender列有三种类型：0 表示男孩，1 表示女孩，2表示性别不明

# 每个性别类型对应的婴儿数量
select gender,count(user_id) num
from babyinfo
group by gender
order by gender;

三、数据分析及可视化

（1）季节因素

# 消费者购买数量和消费者人数的日变化情况
select day,sum(buy_mount) 每天的销量,count(user_id) 每天的用户数
from trade
group by day
order by day asc;

# 对每天的销量降序排列，可以看到有五天的销量大于1000，呈现异常情况
select day,sum(buy_mount) 每天的销量,count(user_id) 每天的用户数
from trade
group by day
order by sum(buy_mount) desc;

# 对消费数量异常的五天进一步分析，认为单人单日购买量大于10属于异常情况
select day,user_id,sum(buy_mount) 每个用户购买量
from trade
where day in('2014-11-13','2013-12-20','2014-09-20','2015-01-12','2013-07-01')
group by user_id
having 每个用户购买量 > 10
order by day asc,每个用户购买量 desc;

# 进一步看具体的消费情况，发现大额订单主要集中在28大类、50008168大类和50014815大类
CREATE view abnormalorders as
select day,user_id,cat1,cat_id,sum(buy_mount) 每个用户购买量
from trade
where day in('2014-11-13','2013-12-20','2014-09-20','2015-01-12','2013-07-01')
group by cat_id
having 每个用户购买量 > 10
order by day asc,每个用户购买量 desc;

观察图表，每年的双11、双12日消费用户数都会大幅上升，每年2月的连续某些天用户数出现低谷；用户数整体缓慢增长趋势。

# 排除日销量大于1000的异常值进行分析
select day,sum(buy_mount) 每天的销量
from trade
group by day
having 每天的销量 <= 1000
order by day asc;

销量的波动比较大，同时也可看出跟购买用户数相对应时间的低谷。

如果是因为快递停运和商家过年休息，才出现春节期间购买情况低谷，建议商家可以在春节前或春节后，甚至春节前后都做促销，***货品也提高销量；或者可以利用春节以发券的形式或折扣价格做部分预售。

（2）种类因素

# 不同大类对应的销量情况
select cat1 broad_category,sum(buy_mount) 销售量
from trade
group by cat1;

# 不同大类对应的用户数情况
select cat1 broad_category,count(user_id) 购买人数
from trade
group by cat1;

6个大类中，不管是销量还是购买用户数，28大类，50014815大类和50008168大类都是前三。但销量最高是28大类，购买用户数最多的是50008168大类。

# 热门小类：销量和购买用户数都排在前10的小类。将这些热门小类筛选出来单独分析：

select g1.cat1 大类名称,g1.cat_id 小类名称,
       g1.销量,g1.销量排名,
       g2.人数,g2.人数排名
from (
      select cat1,cat_id,销量,t1.t1_rank 销量排名
      from (select cat1,cat_id,sum(buy_mount) 销量,
                   dense_rank()over(order by sum(buy_mount) desc) t1_rank
            from trade
            group by cat1,cat_id) t1
      where t1.t1_rank <= 10 
                            ) g1
inner join (
      select cat1,cat_id,人数,t2_rank 人数排名
      from (select cat1,cat_id,count(user_id) 人数,
                   dense_rank()over(order by count(user_id) desc) t2_rank
            from trade
            group by cat1,cat_id) t2
      where t2.t2_rank <= 10 
                            ) g2
on g1.cat_id = g2.cat_id
order by g1.cat1 asc,g1.cat_id asc

热门小类总共有6个，其中50008168大类有3个热门小类，28大类，38大类和50014815大类分别有一个热门小类。对这6个热门小类单独分析每月销量和购买用户数，看看有什么特点。

# 这里只展示28大类50011993小类的查询代码
select left(day,7) 年月,sum(buy_mount) 销量,count(user_id) 购买人数
from trade
where cat_id = '50011993'
group by 年月
having sum(buy_mount) < 1000 # 去除数据异常的月份
order by 年月 asc;

50011993小类：购买用户数呈增长趋势，但波动较大，不是很稳定。50011993小类销量排名第2，用户数排名第5，说明用户需求还是很大的，很有市场，主要就是要想办法稳定客户量，这样此商品才能持续热门。

211122小类：销量循环波动增长趋势，用户数缓慢增长，不同月份之间购买情况没有明显的差异。通过主次坐标轴差异可以看出一个用户购买多个商品。211122小类是潜力股，是全年都有市场的，并且已经在稳步增长，建议将此类放在首页较明显位置，也可以做些活动，以吸引更多客户购买，同时可以通过满减的策略鼓励客户一次买多个。

50006602小类：2013年7月销量突出，其他时候销量波动，有一定增长，但波动比较大，与之对应的用户数也频繁波动。对50006602小类，主要要稳定客户，购买量和用户数都不太稳定，需要从产品本身或者营销策略方面多找原因。

50010558小类：销量排名第10，但购买用户数排名第2，建议按月份需求做相应促销，同时考虑到每个用户可能的需求量有限，可以通过一些激励方案，鼓励客户推荐给朋友购买。

50013636小类：每年的11月和5月是购买高峰，而2月和7月是购买低谷，结合商品情况看是否是有明显季节适应性。销量和购买用户数的轨迹高度重合，说明每个用户的购买量不大，核实此商品是否是耐用品。

50018831小类：每年的11月是销量大高峰，4月也有个小高峰，同比增长率低，用户数增长不明显，较缓慢，但从2014年9月份开始用户数也在稳步增长。

50018831小类的销量排名第1，即使是去掉那个10000的偶然销量，其销量也还是会排在前10，说明这个商品的市场需求很大。双11可能是价格很实惠，销量很高，但这也能从这些高峰购买月份的销量与用户数之比就可以看出来，可能价格合适的情况下用户是会购买多件的。

对50018831小类的重心，首先应该要保持客户的稳步增长，然后就是适时的促销活动，保持该类的热度。

# 冷门小类：销量和用户数都为1的小类，统计每个大类的冷门小类数：

select cat1 大类,cat_id 小类,count(cat_id) 冷门小类数
from (select cat1,cat_id from trade
      group by cat_id
      having sum(buy_mount) = 1 and count(user_id) = 1) t
group by 大类
order by 冷门小类数 desc;

冷门小类数最多的是28大类，差不多占了一半。冷门小类总数有83个。

（3）种类与婴儿

# 构建trade表和babyinfo表的合并视图
# 年龄为负数的归为“0未出生”这类，0~1岁（包括1岁）归为“1岁”，1~2岁（包括2岁）归为“2岁”，依次类推，大于5岁的一并归为“5岁以上”。
# 将原来用0和1代表的性别，改为用中文“男”，“女”以及“不明”表示。
CREATE view tradeinfo as
select t.user_id,cat1,cat_id,buy_mount,day buy_day,birthday,
       (case when datediff(day,birthday) < 0 then '0未出生'
             when 0 < datediff(day,birthday) <= 365 then '1岁'
             when 365 < datediff(day,birthday) <= 365*2 then '2岁'
             when 365*2 < datediff(day,birthday) <= 365*3 then '3岁'
             when 365*3 < datediff(day,birthday) <= 365*4 then '4岁'
             when 365*4 < datediff(day,birthday) <= 365*5 then '5岁'
        else '5岁以上' end) 年龄分段,
       (case when round(datediff(day,birthday)/365,1) < 0 then 0
        else round(datediff(day,birthday)/365,1) end) 年龄,
       (case gender when 0 then '男'
                    when 1 then '女'
        else '性别不明' end) 性别
from trade t join babyinfo b on t.user_id = b.user_id;

# 每个年龄段的人数和购买量
select 年龄分段,count(user_id) 人数,sum(buy_mount) 购买量
from tradeinfo
group by 年龄分段
order by 购买量 desc;

1岁和2岁的人数最多，也是购买的主力军；观察到未出生的人数和购买量紧随其后，可能是准父母或亲友都喜欢提前为宝宝准备各类用品。

# 每个大类的购买量
select cat1 商品大类,sum(buy_mount) 购买量
from tradeinfo
group by 商品大类
order by 购买量 desc;

# 每个小类的购买量
CREATE view secbuymount as
select cat1 商品大类,cat_id 商品小类,sum(buy_mount) 购买量
from tradeinfo
group by 商品小类
order by 购买量 desc;

# 购买量排名前10的小类
select 商品大类,商品小类,购买量,t_rank
from (select 商品大类,商品小类,购买量,
             dense_rank()over(order by 购买量 desc) t_rank
      from secbuymount) t
where t.t_rank <= 10;

接下来对购买量排名前10的商品小类的用户画像进行分析，这里以TOP1-50018831小类为例。

# 分析不同性别和年龄分段的购买人数和购买量情况
select 性别,年龄分段,count(user_id) 用户数,sum(buy_mount) 购买量
from tradeinfo
where cat_id = '50018831'
group by 性别,年龄分段
order by 性别,年龄分段;

# 发现对应1岁男孩的购买量最高，对这一人群单独分析
select user_id,sum(buy_mount)
from tradeinfo
where cat_id = '50018831'
group by user_id;

# 发现用户359601689一次性购买了160件商品，不能排除刷单的可能性
# 去除这单情况，对50018831小类进行分析
select 性别,年龄分段,count(user_id) 用户数,sum(buy_mount) 购买量
from tradeinfo
where cat_id = '50018831'
and user_id <> '359601689'
group by 性别,年龄分段
order by 性别,年龄分段;

可以看到目标人群主要集中在1/2/3岁男孩和1岁女孩。

（4）复购情况

/*整体复购情况*/
select user_id,count(*) 购买次数
from trade
group by user_id
having 购买次数 > 1
order by 购买次数 desc;
# 共有24位用户复购了产品

# 构建复购用户明细视图进行进一步分析
CREATE view 复购用户明细 as
select * from trade
where user_id in (select user_id
                  from (select user_id,count(*) 购买次数 from trade
                        group by user_id
                        having 购买次数 > 1
                        order by 购买次数 desc) t)
order by user_id;

/*首次复购情况*/
# 分析用户首次复购的时间天数间隔和购买数量差异
# 首次复购的情况可以说明用户对产品的粘性和需求程度（还需要结合产品本身的特质分析）
# 如果首次复购的积极性就很低，长期来看就更加难以留住用户了
select 首购.user_id,
       datediff(第2次购买日期,第1次购买日期) 首次复购间隔天数,
       (第2次购买数量 - 第1次购买数量) 首次复购数量差
from(
     select user_id,day 第1次购买日期,buy_mount 第1次购买数量
     from(select user_id,day,
                 dense_rank()over(partition by user_id order by day asc) t_rank,buy_mount
          from 复购用户明细) t
     where t_rank = 1
     ) 首购
inner join (
            select user_id,day 第2次购买日期,buy_mount 第2次购买数量
            from(select user_id,day,
                          dense_rank()over(partition by user_id order by day asc) t_rank,buy_mount
                 from 复购用户明细) t
            where t_rank = 2
            ) 复购
on 首购.user_id = 复购.user_id
order by 首次复购间隔 asc;

可以从分析结果中看出，整体的复购情况并不理想，24位复购用户的平均首次复购时间天数间隔为281天，并且近一半用户的间隔天数集中在一年以上；平均复购数量增加0.75件，并且主要是由用户890739828拉动的：

select * from trade
where user_id = '890739828';

可以看到他216天后复购商品小类[211122] 24件，作为单一样本，更有可能和其自身的特殊需求有关。

可以算出：
复购率 = (24+3)/29944*100%=0.09%
平均购买次数 = 29971/29944=1.0009
商家要从多方面分析复购率低的原因，比如产品本身，价格，竞品，营销方案，市场环境，行业规律等。

# 分析复购用户选择复购产品的情况
select cat1 商品大类,count(cat_id) 小类数
from 复购用户明细
group by 商品大类
order by 小类数 desc;

饼状图数据标签含义为：商品大类，商品小类数，小类数占比。
可以看到复购用户更倾向于在相同大类里复购不同小类。