SQL案例练习（2）：产品销售情况分析

案例来自书籍《SQL面试宝典：图解数据库求职题》

【背景】

云服务产品业务流程图

【问题】

主管发现2020年5月销售业绩下跌，急需找到业绩下降的原因，帮助业务员针对性改进。

【数据源】

表1：订单表

表2：任务下发表

1. 【业绩同比】

【sql思路】

（1）用date_format(字段,‘%Y-%m’)从订单表中的“支付时间中”提取月份
（2）group by分组汇总求每个月份的业绩总和

select date_format(支付时间,'%Y-%m') as 月份,sum(实付金额) as 业绩
from 订单表
group by date_format(支付时间,'%Y-%m');

考虑到转换二维表（case when语句）、求比率（需用窗口函数），故复制到excel中操作继续统计，生成同比图。

【结论】

可以看到2月份同比下跌52.9%（因疫情影响），3、4月份出现反弹，同比增长超20%，但5月不升反降，需进一步拆分看是否销售环节出现问题。

【延申】

如何用sql生成上期数&同比、同期数&环比率？
1.环比（与上月/上周/前一日等对比），求上期数&环比率
上期数 lag(当月业绩,1) over (order by 月份 asc)
环比率=（本期数-上期数）/上期数×100%
ps：此案例月份不连续，不适合用该代码（2020年1月上月业绩、环比出错）
当月份连续时求环比，可考虑用以下代码。

select 月份,当月业绩,
   lag(当月业绩,1) over (order by 月份 asc) as 上月业绩,
   -- (单月业绩-上月业绩)/上月业绩 as 环比
   round((当月业绩-lag(当月业绩,1) over (order by 月份 asc))/lag(当月业绩,1) over (order by 月份 asc),2) as 环比
from (
    select date_format(支付时间,'%Y-%m') as 月份,
    sum(实付金额) as 当月业绩
    from 订单表
    group by date_format(支付时间,'%Y-%m')) a;

2.同比（与去年同期比），求同期数&环比率（待补充）
同比=（本期数－同期数）/同期数×100%

2. 【客单价同比分析】

销售额=客单价*用户数，可以继续拆解5月客单价和用户数的同比变化，看销售额下跌的主要原因。

【sql思路】

（1）用date_format(支付时间,‘%Y-%m’)从订单表中的“支付时间中”提取月份
（2）筛选5月数据，where date_format(支付时间,‘%m’)=‘05’
（3）group by分组汇总，sum(实付金额)求销售额，count(distinct 用户id)求用户数
（4）客单价=销售额/用户数

select date_format(支付时间,'%Y-%m') as 月份,
    sum(实付金额) as 销售额,
    count(distinct 用户id) as 用户数,
    round(sum(实付金额)/count(distinct 用户id),2) as 客单价
from 订单表
where date_format(支付时间,'%m') = '05'
group by date_format(支付时间,'%Y-%m');

复制到excel中操作继续统计同比率，生成同比图。

【结论】

由图可以看到，用户数与去年同期持平，客单价则下跌11.45%，由此推断销售额下跌是由于客单价下跌导致。

【疑问】

哪些因素可能引起客单价变低呢？
1.产品侧：产品定价，如促销折扣、组合销售等导致交易价格贬低。——>产品分析
2.消费测：如新用户购买力偏低、老用户复购意愿变低——>用户分析
3.营销测：如老用户续费转化差——>流程转化率分析
也可能是市场环境变化、竞争对手策略变化等导致客单价贬低。

3. 【产品分析】

1）根据产品名称拆分，看5月用户数、客单价同比情况。

select date_format(支付时间,'%Y-%m') as 月份,产品名称,count(distinct 用户id) as 用户数,
        round(sum(实付金额)/count(distinct 用户id)) as 客单价
from 订单表
where date_format(支付时间,'%m') = '05'
group by date_format(支付时间,'%Y-%m'),产品名称;

【结论】

有图可看到，用户数同比基本持平，数据库产品同比下跌32.10%，整体客单价变低，问题主要是数据库产品客单价降低导致。后续需进一步分析下钻分析数据。

2）数据库子产品下钻分析

select date_format(支付时间,'%Y-%m') as 月份,产品名称,子产品名称,count(distinct 用户id) as 用户数,
        round(sum(实付金额)/count(distinct 用户id)) as 客单价
from 订单表
where date_format(支付时间,'%m') = '05' and 产品名称= '数据库' 
group by date_format(支付时间,'%Y-%m'),产品名称,子产品名称;

考虑可能是进阶版跟高级版付费用户数锐减，导致数据库产品客单价锐减。故导出去年跟今年所有月份的子产品数量，绘制百分比堆积面积图印证。

select date_format(支付时间,'%Y-%m') as 月份,子产品名称,count(distinct 用户id) as 用户数
from 订单表
where 产品名称= '数据库' 
group by date_format(支付时间,'%Y-%m'),子产品名称;

【结论】

可以看到，数据库子产品客单价均不降反涨，2020年5月进阶跟高级版用户占比明显下跌。数据库产品客单价减少的主要原因在于进阶版跟高级版的付费用户数锐减（分别降低33%、50%），高单价产品数量占比变低，导致数据库整体产品客单价骤降。

4. 【新老用户分析】

如何区分新老用户？
任务下发表中的“任务类型”字段，“新用户注册”是新用户，“产品即将到期提醒”是老用户。

1）数据库产品，新老用户分析

select date_format(支付时间,'%Y-%m') as 月份,b.任务类型,
        count(distinct a.用户id) as 客户数,
        round(sum(a.实付金额)/count(distinct a.用户id),2) as 客单价
from 订单表 a join 任务下发表 b on a.用户id=b.用户id
where a.产品名称= '数据库' and date_format(支付时间,'%m') = '05'
group by date_format(支付时间,'%Y-%m'),b.任务类型;

【结论】

由图可看到，老客户数量有些许下跌，客单价下跌超32%；新用户数量有所上涨，但客单价下跌超30%。

5. 【转化率分析】

两个表数据涉及到的转化流程：
新用户：注册—>下单。转化率=下单客户数/注册客户数
老用户：产品即将到期提醒—>复购下单。转化率=复购下单客户数/到期提醒客户数

select a.任务类型,
    date_format(a.任务下发时间,'%Y-%m') as 月份,
                count(b.用户id) as 支付用户数,
    concat(round(count(b.用户id)/count(a.用户id)*100,2),'%') as 转化率
from 任务下发表 a left join 订单表 b on a.用户id=b.用户id
where a.产品名称='数据库'
group by a.任务类型,date_format(a.任务下发时间,'%Y-%m')
order by a.任务类型,date_format(a.任务下发时间,'%Y-%m');

【结论】

新用户转化率与之前持平，老用户续费率在2020年5月出现大幅下跌（下跌超9%）。

总结与建议

2020年5月销售业绩下跌主要集中在两个原因：
（1）数据库产品订单主要集中在低客单价的标准版，进阶版跟高级版的付费用户数锐减。
（2）数据库产品老用户续费率下降。

针对以上问题给出建议：
（1）产品优化：通过市场、未成功下单用户调研，了解用户对进阶版、高阶版的需求，针对性改进产品功能和用户体验，提高进阶、高阶版产品的吸引力。
（2）产品定价调整：1）重新评估产品定价，考虑与产品实际价值匹配、竞品定价策略等，以确保价格竞争力。2）增加现时折扣等优惠方案，吸引用户下单。
（3）营销策略改进：1）适度调整进阶版、高阶版的营销推广，提高产品的知名度。2）
（4）客户关系维护：1）老客户付费失败、成功案例复盘总结原因，提升客户服务质量，增加客户满意度、忠诚度。2）对长期续费的老用户增加优惠，提高用户粘性。
（5）员工管理：1）加强销售流程监控，制定并拆解每月销售目标，定时定期汇报进度；2）设立合理的销售激励机制，制定中高端产品销售目标，对超越目标的进行额外奖励。 3）开展产品只是、销售技巧、客户关系维护等培训活动，提升团队专业性。