作者：繁花落尽显清秋
链接：https://zhuanlan.zhihu.com/p/94784115
来源：知乎
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

在10月份的时候入职了一家社交电商平台，对我来说，这是第一次从事电商行业的数据分析师工作，可惜由于种种原因，公司效益不佳，难以维持原先的团队开支，开始精简人员，处于试用期的我自然难免被裁，在此，也还是需要感谢公司跟部门领导，在这段时间给予的机会与照顾。
工作期间，在部门老大的带领下，做了一个商品熔断机制，在此分享一下，希望在以后的工作中能不断改善自己的认知，提高自己的技能
一、背景介绍：
由于公司成立不久，许多规则都在慢慢形成并完善，比如商品熔断。
平台商品较多，每日的销售、退单也不少。当然，我们都希望消除退单，不过这是不可能的，但是我们还是需要知道每个商品退单的原因是什么，针对这个原因，我们是否能采取有效的方法，去避免下次再次出现，减少退单。对于非常多的退款原因，当然是有方法避免的，但有的原因属于原则问题，采取的措施就比较暴力（直接下架）。因此，就需要一套商品熔断机制，来给这些问题商品贴上几个比较粗犷的标签。
与业务部门确定的熔断的逻辑是：
①、行业平均售后率：数据时间周期内 一级别类目的“ 退款单量”/“支付单量”
②、平台平均售后率：数据时间周期内 “ 退款单量”/“支付单量”
③、三段熔断值：”行业平均售后率 5 “与 “平台平均售后率 2”取较大值，若较大值小于0.5，则熔断值定位0.5
④、二段熔断值为三段熔断值得1.25倍，一段熔断值为三段熔断值得1.5倍，因其他原因，没有采用一、二段熔断值，最终仅以三段熔断值作为熔断条件
⑤、只对数据周期内 支付单量>=50，退款单量>=5 的商品做熔断预警，该范围外数据暂不做预警，该部分商品若退单率>熔断值，则“下架”处理；若 该部分商品若退单率>熔断值0.75，则做“警告”处理；若 该部分商品若退单率>熔断值0.5，则做“提醒”处理
⑥、做每日熔断日报，数据时间周期为15天，若有“下架”标签的商品，查看其退款原因，若为质量问题，缺货等，则做立即下架处理，其余因尺寸、款式等可以通过改善运营或物流、客服等解决的，做申述上架处理；警告状态的商品，若退款单量较多，也需查看下退款原因，若质量问题、缺货较多，也做立即下架处理

二、模型制作

1、采集数据，由于公司使用阿里云数据库（mysql)，我们部门只有读取的权限，所以将数据导出，导入自己建立的数据库

获取一级类目下的各个商品的在该时间周期内的支付单量、退货单量等汇总数据

  1. /*汇总数据*/
  2. select  一级类目,
  3.     商品id,
  4.     max(商品名称) as 商品名称,
  5.     max(供应商ID) as 供应商ID,
  6.     max(供应商) as 供应商,
  7.     count(distinct 子订单号) 支付单量,
  8.     count(distinct 熔断退款子订单) as 退款单量,
  9.     sum(支付金额) as 支付金额,
  10.     sum(if(熔断退款子订单 is null,0,退款金额 )) as 退款金额,
  11.     sum(购买数量) as 购买数量,
  12.     sum(if(熔断退款子订单 is null,0,退款数量 )) as 退款数量,
  13.     CONCAT(cast(date_format(adddate(date_format(adddate(now(),0),'%Y-%m-%d'),-15),'%Y/%m/%d') as char),'-',cast(date_format(adddate(date_format(adddate(now(),0),'%Y-%m-%d'),-1),'%Y/%m/%d') as char)) as 数据时间周期
  14.      from 
  15.      (  /*周报_售后汇总数据+售后及供应链周报数据源+发货合规数据_初始数据*/
  16.     select  a.主订单号,
  17.         a.子订单号,
  18.         a.支付时间,
  19.         a.发货时间,
  20.         d.create_time as 退款申请时间,
  21.         a.商品id,
  22.             b.商品名称,
  23.         a.sku_id,
  24.         b.商品类型,
  25.            if(b.cat_id1=1,'礼包商品',一级类目) 一级类目,
  26.         二级类目,
  27.         三级类目,
  28.         供应商ID,
  29.         f.name as 供应商,
  30.         a.支付金额,a.购买数量,退款金额,退款数量,
  31.         if(d.return_status in (3,15) and a.发货时间 is not null, a.子订单号,NULL) as 熔断退款子订单
  32.         FROM
  33.         (/*订单数据取近15天数据*/
  34.         select   *******省略****** )a
  35.         left join
  36.         (/*类目信息情况*/
  37.           select *******省略****** )b
  38.                on  a.商品id=b.id
  39.         left join
  40.         (/*订单退款情况，需要保留所有退款订单，在计算供应链数据中有作用，此外，数据取截止周报数据截止日期的即可，子订单号+skuid为唯一值，可做索引值;return_status为3、15为成功退款的*/
  41.          select  *******省略******  )d
  42.                on  a.子订单号=d.子订单号
  43.                and  a.sku_id=d.sku_id
  44.         left join
  45.         (/*由于目前无法区分出子订单的退款原因，暂时以主订单的退款原因替代，主订单号为唯一值，数据取截止周报数据截止日期的即可*/
  46.           select *******省略****** )e
  47.             on  a.主订单号=e.主订单号
  48.         left join
  49.                    (select *******省略****** ) f
  50.             on  a.供应商ID=f.id                        
  51.         )t0
  52.         group by 一级类目,商品id;

获取 各商品退款原因明细

  1. /*退款原因明细*/
  2. select  distinct
  3.     a.子订单号,
  4.     a.商品id,
  5.     b.商品名称,
  6.     if(b.cat_id1=1,'礼包商品',一级类目) 一级类目,
  7.     供应商ID,
  8.     f.name as 供应商,
  9.     if(d.create_time is null,'',e.退款原因) as 退款原因,
  10.     原因描述,
  11.     concat(cast(date_format(adddate(date_format(adddate(now(),0),'%Y-%m-%d'),-15),'%Y/%m/%d') as char),'-',cast(date_format(adddate(date_format(adddate(now(),0),'%Y-%m-%d'),-1),'%Y/%m/%d') as char)) as 数据时间周期
  12.     FROM
  13.     (/*订单数据取近15的数据*/
  14.       select  DISTINCT
  15.           order_no  as  主订单号,
  16.           sub_order_no  as  子订单号,
  17.           confirm_receive_time  as  确认收货时间,
  18.           if(delivery_time>date_format(adddate(now(),0),'%Y-%m-%d'),NULL,delivery_time)  as  发货时间,
  19.           pay_time  as  支付时间,
  20.           item_id as 商品id,
  21.           item_name as 商品名称,
  22.           sku_id,
  23.           supplier_id  as  供应商ID,
  24.           order_return_status,
  25.           order_status
  26.        from  se_order_item_sku
  27.         WHERE  order_status >=3
  28.           and supplier_id not in (64)
  29.           and pay_time>=date_format(adddate(date_format(adddate(now(),0),'%Y-%m-%d'),-15),'%Y-%m-%d')
  30.               and pay_time<=date_format(adddate(date_format(adddate(now(),0),'%Y-%m-%d'),0),'%Y-%m-%d')
  31.      )a
  32.      left join
  33.      (/*类目信息情况*/
  34.         select  *******省略***** )b
  35.             ON  a.商品id=b.id
  36.       left join
  37.        (/*订单退款情况，需要保留所有退款订单，在计算供应链数据中有作用，此外，数据取截止周报数据截止日期的即可，子订单号+skuid为唯一值，可做索引值;return_status为3、15为成功退款的*/
  38.          select  *******省略****** )d
  39.         ON a.子订单号=d.子订单号
  40.         and  a.sku_id=d.sku_id
  41.        left join
  42.         (/*由于目前无法区分出子订单的退款原因，暂时以主订单的退款原因替代，主订单号为唯一值，数据取截止周报数据截止日期的即可*/
  43.         select *******省略******)e
  44.         ON  a.主订单号=e.主订单号
  45.        left join
  46.             (select *******省略****** ) f
  47.         ON  a.供应商ID=f.id        
  48.         where  d.return_status in (3,15) 
  49.           and  a.发货时间 is not null

2、导入本地数据库、并制作模型

将数据导入本地数据库，由于部门里用的是sql server 数据库，所以以下均用sql server 数据库
导入数据后
19.12.03 繁花落尽-商品熔断 - 图119.12.03 繁花落尽-商品熔断 - 图2汇总数据19.12.03 繁花落尽-商品熔断 - 图319.12.03 繁花落尽-商品熔断 - 图4退款原因
上面作为数据源，需要对其进行使用熔断规则，因为最后要做成日报呈现，将熔断后的数据做为视图，方便每日更新
根据熔断规则，先制作行业熔断值：

  1. SELECT  a.[一级类目], 
  2.         行业平均售后率, 
  3.     b.平台平均售后率, 
  4.     iif(iif(行业平均售后率 * 5 < 平台平均售后率 * 2, 平台平均售后率 * 2, 行业平均售后率 * 5) > 0.5, 
  5.          0.5, iif(行业平均售后率 * 5 < 平台平均售后率 * 2, 平台平均售后率 * 2, 行业平均售后率 * 5)) AS 三段熔断值, 
  6.     iif(iif(行业平均售后率 * 5 * 1.25 < 平台平均售后率 * 2 * 1.25, 平台平均售后率 * 2 * 1.25, 行业平均售后率 * 5 * 1.25) > 0.5 * 1.25,
  7.          0.5 * 1.25, iif(行业平均售后率 * 5 * 1.25 < 平台平均售后率 * 2 * 1.25, 平台平均售后率 * 2 * 1.25, 行业平均售后率 * 5 * 1.25)) AS  二段熔断值, 
  8.         iif(iif(行业平均售后率 * 5 * 1.50 < 平台平均售后率 * 2 * 1.50, 平台平均售后率 * 2 * 1.50, 行业平均售后率 * 5 * 1.50) > 0.5 * 1.50, 
  9.           0.5 * 1.50, iif(行业平均售后率 * 5 * 1.50 < 平台平均售后率 * 2 * 1.50, 平台平均售后率 * 2 * 1.50, 行业平均售后率 * 5 * 1.50)) AS 一段熔断值, 
  10.     1 AS 零阶段熔断值, 
  11.     a.数据时间周期
  12.      FROM 
  13.       (SELECT  [一级类目],
  14.            [数据时间周期], 
  15.            sum([支付单量]) AS [支付单量], 
  16.            sum([退款单量]) AS [退款单量], 
  17.            sum([退款单量]) / sum([支付单量]) 行业平均售后率, 
  18.            1 AS id
  19.            FROM  [dbo].[商品熔断日报数据源_商品明细]
  20.            GROUP BY [一级类目], [数据时间周期]) a 
  21.     LEFT JOIN
  22.           (SELECT  sum([退款单量]) / sum([支付单量]) 平台平均售后率, 
  23.            [数据时间周期], 
  24.            1 AS id
  25.               FROM  [dbo].[商品熔断日报数据源_商品明细]
  26.               GROUP BY [数据时间周期]) b 
  27.        ON  a.id = b.id AND a.数据时间周期 = b.数据时间周期;

制作熔断处理后视图

  1. select  一级类目,t0.商品id,t0.商品名称,t0.供应商ID,供应商,支付单量,退款单量,支付金额,退款金额,购买数量,退款数量,退单率,熔断值,
  2.     iif(熔断值=1,'未预警',(case when 退单率>熔断值 then '下架' when 退单率>熔断值*0.75 then '警告' when 退单率>熔断值*0.5 then '提醒'  else '未预警' end)) as 预警状态,
  3.     iif(t1.商品状态 is null,'历史未操作',t1.商品状态) as 商品历史状态,t1.处理日期 as 历史处理日期,
  4.     数据时间周期
  5.    from
  6.    (
  7.      select  a.[一级类目],
  8.         [商品id],
  9.         [商品名称],
  10.         [供应商ID],
  11.         [供应商],
  12.         [支付单量],
  13.         [退款单量],
  14.         [支付金额],
  15.         [退款金额],
  16.         [购买数量],
  17.         [退款数量],
  18.           CAST([退款单量] AS FLOAT)/[支付单量] as 退单率,
  19.              iif(退款单量>=5 and 支付单量>=50,三段熔断值,1) as 熔断值,
  20.           a.[数据时间周期]
  21.      from   [dbo].[商品熔断日报数据源_商品明细] a
  22.        left join  [dbo].[view_商品行业熔断值] b
  23.               on  a.一级类目=b.一级类目
  24.          and  a.数据时间周期=b.数据时间周期
  25.     )t0
  26.        left join 
  27.        [dbo].[商品熔断_历史已处理商品] t1  #历史已处理商品为手动登记后导入数据库
  28.          on  t0.商品id=t1.商品ID

根据熔断处理后的数据，分别制作每日视图报表
19.12.03 繁花落尽-商品熔断 - 图519.12.03 繁花落尽-商品熔断 - 图6

3、报表展现

使用excel 报表展现每日熔断情况：
19.12.03 繁花落尽-商品熔断 - 图719.12.03 繁花落尽-商品熔断 - 图819.12.03 繁花落尽-商品熔断 - 图919.12.03 繁花落尽-商品熔断 - 图1019.12.03 繁花落尽-商品熔断 - 图1119.12.03 繁花落尽-商品熔断 - 图1219.12.03 繁花落尽-商品熔断 - 图1319.12.03 繁花落尽-商品熔断 - 图14熔断报表
建立好链接后，设置excel条件格式,以最醒目的方式呈现熔断情况，熔断下架的商品可以用商品ID 在退款原因中观看退款原因，判别是否需要下架。
总结：制作这样的一份熔断报表，只需要每日从云数据中根据设定好的sql代码，获取数据，然后导入本地数据库，打开excel熔断表格，刷新数据，便可以自动生成每日熔断报表，根据熔断数据对商品进行处理；熔断的逻辑需要在后面的业务场景变化、完善中不断加入新的规则，慢慢完善，比如还可以加入商品的每日咨询情况，判定他的质量是否存在问题，加入熔断体系。