某电商在线奶粉销量数据分析-SQL+Tableau自动化分析

本文数据集来源：Baby Goods Info Data

数据库：MySQL

数据库管理软件：DataGrip

可视化分析软件:Tableau

本文sql文件、可视化分析源文件地址:DataScience

数据预处理与导入

导入数据到数据库

1. 创建Trade表

create table trade
(
	user_id varchar(20) not null comment '用户id',
	auction_id varchar(20) not null comment '购买行为编号',
	cat_id varchar(20) null comment '商品所属的大类下的子类',
	cat1 varchar(15) null comment '商品所属的大类',
	property text null comment '商品属性',
	buy_mount int null comment '购买数量',
	day varchar(8) null comment '购买日期'
);

2. 使用DataGrip将(sample)sam_tianchi_mum_baby_trade_history.csv导入trade表

3. 创建babyinfo表

create table babyinfo
(
	user_id varchar(20) not null,
	birthday varchar(8) null,
	gender char null
);

4. 使用DataGrip将(sample)sam_tianchi_mum_baby.csv导入babyinfo表

5. 然后再在DataGrip修改日期数据的数据格式为date格式

   • 定义表格时，直接使用date格式，导入会出现错误

数据探索：

Trade表

• buy_mount （购买数量/销量）
• user_id（用户id）
• auction_id（购买行为编号）
• cat1（商品所属的大类）
• cat_id（cat1的子类，是更细分的类别）
• property（商品属性）
• day（购买时间）

统计缺失值信息

select
       sum(user_id is null)
       ,sum(auction_id is null)
       ,sum(cat1 is null)
       ,sum(cat_id is null)
       ,sum(trade.property is null)
       ,sum(trade.buy_mount is null)
       ,sum(trade.day is null)
from trade;

• 只有144条记录的商品属性property有缺失

用户数量：

select
       count(user_id)
      ,count(distinct user_id)
from trade;

• 29971条购买记录中，却有29944个用户，说明只有不到30个用户购买超过两次，说明奶粉类产品的复购率不是很高。

统计不同购买数量的用户

select
buy_num
,count(user_id) as user_num
from
(
    select
    user_id
    ,count(user_id) as buy_num
    from trade
    group by user_id
    )as tem
group by buy_num
order by user_num;

• 99.92%的用户都只购买了一次

商品类别

select
       cat1,
       count(distinct cat_id) as 子类数量
from trade
group by cat1
order by 子类数量;

• 总共有六个大类，每个大类下面分别由数量不一的子类构成

每次购买数量

select
       buy_mount 每次购买数量
       ,count(user_id) 消费次数
from trade
group by buy_mount
order by 消费次数;

时间跨度

select max(day),
       min(day)
from trade;

• 可以看到销售数据从2012年7月开始，到2015年2月结束，其中2015年2月只有5天的数据，数据不完整，在下面的聚类运算中，统计量会比较小

babyinfo表

• user_id（用户id）
• birthday（出生日期）
• gender:性别（0 男孩，1 女孩，2性别不明）

缺失值统计

select sum(user_id is null),
       sum(birthday is null),
       sum(gender is null)
from babyinfo;

有信息的用户数量

select count(distinct user_id) -- 有信息的用户数量
from babyinfo;

• 虽然只有3.2%的用户有详细的婴儿年龄等信息，但是考虑到数据抽样的随机性，仍能反映出整体分布的一些规律信息

不同性别婴儿的数量

select gender, -- 不同性别的数量
       count(gender)
from babyinfo
group by gender;

探索分析

销量信息

按天统计每天的销量和活跃的用户数量

1. sql查询语句

select day,
       sum(buy_mount) as 销量,
       count(distinct user_id) as 用户数量
from mytest.trade
group by day
order by day

2. 使用tableau进行可视化

• 从图中可以看到，在部分日期的销量和用户数量是平时数据几千倍，可能会有异常，需要进行查明

3. 查询销量异常记录：

-- 查询单次购买超过100的记录数
select user_id,day,
       buy_mount
from trade
where buy_mount>100
order by buy_mount desc;

• 从结果中可以看到，有不少用户的一次购买量超过了100罐，甚至有一次购买10000罐的，很有可能是批发商购买或者是刷单行为，需要对这些记录进行筛选剔除

经调查：

婴幼儿在0-1岁时，理论上一共需要81罐400g奶粉，假设用户除“双十一”、“618”外其他时间每次只购买1罐，那么两个购物节平均需要承担27罐奶粉，向上取整后，以单笔销量超过30罐奶粉作异常值处理。

4. 更改连接sql语句，剔除异常销量数据，重新查询后，销量用户数量图如下：

select day,
       sum(buy_mount) as 销量,
       count(distinct user_id) as 用户数量
from mytest.trade
where buy_mount < 30
group by day
order by day

• 1 从销量图上可以看出，每年的销量高峰是双十一和双十二购物期间

• 2 在春节期间，销量出现了明显的低谷

• 3 购物节的销量呈现逐年增加的趋势

观察销量在一周内的变化

-- 分析按星期的销量，用户量
select dayname(day) as D,
       sum(buy_num) as 销量,
       sum(user_num) as 活跃用户量
from
    (
        select
            day,
            sum(buy_mount ) as buy_num,
            count(distinct user_id) as user_num
        from trade
        where buy_mount<30
        group by day
        ) as tem
group by D,dayofweek(day)
order by dayofweek(day) ;

• 可以看到周六日的销量和活跃用户数量都明显小于工作日，很有可能是部分父母在周末需要带孩子，而在工作日进行购买奶粉

分析按月购买的情况：

-- 分析按月购买的情况
select
       月份,
       max(if(年份 = 2012,buy_num,0)) as 2012年,
       max(if(年份 = 2013,buy_num,0)) as 2013年,
       max(if(年份 = 2014,buy_num,0)) as 2014年,
       max(if(年份 = 2015,buy_num,0)) as 2015年
from
    (
        select
        month(day) as 月份,
        year(day) as 年份,
        sum(buy_mount ) as buy_num,
        count(distinct user_id) as user_num
        from trade
        where buy_mount<30
        group by year(day), month(day)
        ) as tem
group by 月份
order by 月份;

• 在每年中，销量主要呈现出两个大的周期
  • 周期1：2-5月销量上升，5-7月销量下降
  • 周期2：7-11月销量上升，11-2月份销量下降
• 主要可能影响因素：
  • 1 不同月份新生儿数量不同
  • 2 不同月份的促销力度不同
• 每年的1，2月份的销量数据相对都比较低，可能是受到春节的影响，在后面需要对春节前后的销量进行细致分析，来采取2015年2月中后期的销售决策

2015年春节前后销量详细分析

• 2013年春节：2月9日-2月15日
• 2014年春节：1月30日-2月6日
• 2015年春节：2月19日-2月25日
• 当前的数据只截至到了2015年2月5日（腊月十七）
• 可以通过调用往年腊月的销售数据，来辅助2015年腊月的销售决策

通过筛选器，观察每年腊月初一到腊月十五的销售额

• 可以看到，2015年的腊月初一到十五的销量虽然仍然很大，但是相比于去年的同比增速，今年的增速有所下降

往年腊月的销售额与同比增速

• 截至目前2015年2月5日（据春节还有14天），已完成销量已经超过了去年的腊月销量，但是要想超过去年的增长率，仍然有1029✖（1.7741）-1129= 696罐的销售额需要完成，平均每天需要完成696/14=49.7罐的销售目标。

观察2014年和2015年腊月初一到腊月十五各产品大类的销量与同比增速

• 观察可知，只有28大类的销售同比增速超过了去年，38大类产品增速基本与去年平均增速持平，二其他大类的增速都比较低于预期，需要详细分析营销策略。

腊月每天的销量变化

• 可以看到，腊月十五过后，销量会逐渐下降，因此需要在接下来1-2周内，马上采取一定的促销活动，来保证本月的销售量达到或超过去年的同比增速。

对比每月新出生人数：

• 数据生成参考婴儿信息一节

• 通过与上图比较可以看出，5月份的销量高峰，很可能是受到8月份出生人数增加的影响，父母为了提前准备，从而提前购买了奶粉
• 11月的销量高峰，是受到打折促销的影响

销量与季度

• 每年都是第一季度的销量最少（春节影响），第二、第三季度销量中等，第四季度销量最高（打折促销，为过年囤货）。

产品类别分析

每个大类奶粉的购买情况

-- 每个大类奶粉的购买情况
select
       cat1 as 类别,
        sum(buy_mount) as 销量,
       count(distinct user_id) as 用户数
from trade
where buy_mount<30
group by cat1
order by cat1;

• 50008168、28、50014815都是销量比较大的产品类别，相比而言，其他类别的销量比较少

• 销量排名基本与用户数量相同，除了38产品，虽然销量排名第四，但是用户量却比较少，可能是用户单次购买量较大，或者店家的满减促销做的比较好，需要进一步调查。

找到热销的子类信息

-- 寻找热销子类（销量前十或用户数量前十）
select
tem1.*,
用户量排名, 用户量
from
         (select cat_id,
                 @j := @j + 1 as 销量排名,
                 销量
          from (select @j := 0) as t,
               (
                   select cat_id,
                          sum(buy_mount) as 销量
                   from trade
                   where buy_mount < 30
                   group by cat_id
                   order by sum(buy_mount) desc) as n
         ) as tem1

join
         (select cat_id,
                 @i := @i + 1 as 用户量排名,
                 用户量
          from (select @i := 0) as t,
               (
                   select cat_id,
                          count(distinct user_id ) as 用户量
                   from trade
                   where buy_mount < 30
                   group by cat_id
                   order by count(distinct user_id ) desc) as m
         ) as tem2
on tem1.cat_id = tem2.cat_id
where 销量排名<10 or 用户量排名<10;

• 可以看到，销量比较高的奶粉子类的销量，都基本上都在逐年增加，其中50010558的销量增长迅速，可以在今年进行优先推广。

• 通过tableau可以清晰地查看不同子类销售量的变化趋势，从而采取不同的措施，来提高销量

婴儿阶段分析

• 1段：0-6个月（4-6月已可食用辅食）
• 2段：6-12个月（6月大：可食用糊状或泥状的食物；9月大：可食用有硬度食物）
• 3段：1-3岁
• 4段：3-7岁（已经符合入读公办幼儿园的年龄，此阶段奶类流质食物已经不是主流）

-- 创建年龄段信息视图
create view mytest.age_info as
(
    select
    b.user_id,
       cat1,
       cat_id,
       buy_mount,
       day as buy_day,
       birthday,
       (
           case
            when datediff(day,birthday)/30<0 then '未出生'
            when datediff(day,birthday)/30<6 then '0-6个月'
            when datediff(day,birthday)/365<1 then '6-12个月'
           when datediff(day,birthday)/365<3 then '1-3岁'
           when datediff(day,birthday)/365<7 then '3-7岁'
           else '大于七岁'
           end
        ) as 年龄分段,
       if((floor(((to_days(`t`.`day`) - to_days(`b`.`birthday`)) / 365)) < 0), '未出生',
          floor(((to_days(`t`.`day`) - to_days(`b`.`birthday`)) / 365))) AS `年龄`,
       (
           case gender
           when 0 then '男'
           when 1 then '女'
           else '不明'
           end
        ) as 性别
from babyinfo b
join trade t
    on b.user_id = t.user_id
where t.buy_mount<30
    );

每个年龄段的婴儿数量和购买量：

-- 每个年龄段的人数和购买量：
select
        年龄分段,
       count(distinct user_id) as 人数,
       sum(buy_mount) as 购买总量
from age_info
group by 年龄分段
order by field(年龄分段,'未出生','0-6个月','6-12个月','1-3岁','3-7岁','大于七岁');

• 1.在整个食用奶粉的各个阶段中，1-3岁对奶粉的需求量最大
• 2.可以看到，有相当一部分家长，购买奶粉都是在孩子出生前购买的，可以考虑针对这种提前购买的行为，有针对性地展开销售

各年龄购买情况

• 可以看到，0-1岁的用户量和奶粉销量都最高，随着年龄的上升，婴儿逐渐能够食用其他食物，奶粉需求逐渐下降

产品用户画像

不同产品大类的用户群体分布

不同用户的产品购买比例

不同产品子类的用户群体分布

复购情况分析

创建复购用户的视图

create view mytest.multi_info
as
    (
select
       user_id,
       cat_id,
       cat1,
       buy_mount,
       day
from trade
where
      user_id in
(
    select
    user_id
    from trade
    where buy_mount<30
    group by user_id
    having count(auction_id)>1
)
order by  user_id,day);

计算复购率

-- 计算复购率
select
a.num1/count(distinct user_id) as 复购率
from
       (select count(distinct user_id) as num1 from multi_info) as a ,
     trade b;

查询有重复购买行为用户复购的是否是同一小类的奶粉

-- 查询有重复购买行为用户复购的是否是同一小类的奶粉
select
       t.num as 复购产品种类数,
       count(user_id) as 用户数
from
    (select
    user_id,count(distinct cat_id) as num
    from multi_info
    group by user_id) as t
group by t.num;

查询有重复购买行为用户复购的是否是同一大类的奶粉

-- 查询有重复购买行为用户复购的是否是同一大类的奶粉
select
       t.num as 复购产品种类数,
       count(user_id) as 用户数
from
    (select
    user_id,count(distinct cat1) as num
    from multi_info
    group by user_id) as t
group by t.num;

分析结果汇总

销售趋势分析

• 可以看到每年的销售高峰在双十一，双十二两个高峰，其他时间销售量变化比较平稳
• 销售量呈现逐年增加的趋势，并且增速逐年增加
• 每年的春节期间，会出现销量低谷

春节销量低谷分析

• 截至目前2015年2月5日（据春节还有14天），已完成销量已经超过了去年的腊月销量，但是要想超过去年的增长率，仍然有1029✖（1.7741）-1129= 696罐的销售额需要完成，平均每天需要完成696/14=49.7罐的销售目标。
• 只有28大类的销售同比增速超过了去年，38大类产品增速基本与去年平均增速持平，二其他大类的增速都比较低于预期，需要详细分析营销策略。
• 腊月十五之后，销量就会逐渐下降，因此需要在接下来一周内，采取营销措施，来刺激销量，从而达到去年的增长率

销售周期性分析

• 在每年中，销量主要呈现出两个大的周期
  • 周期1：2-5月销量上升，5-7月销量下降
  • 周期2：7-11月销量上升，11-2月份销量下降
• 可以看到周六日的销量和活跃用户数量都明显小于工作日，很有可能是部分父母在周末需要带孩子，而在工作日进行购买奶粉
• 5月份的销量高峰，很可能是受到8月份出生人数增加的影响，父母为了提前准备，从而提前购买了奶粉

不同类别产品销量占比分析

各年龄段销售差异

参考资料：

1只会环比下降3%的数据分析师还有救吗？

2电商婴儿用品数据分析(SQL)