店铺管理：从双十一销售数据发现收银小票欺诈
2019双十一轰轰烈烈的结束了，几家欢喜几家愁。作为线下店铺，有人愁的是移动互联网的业绩冲击，还有人发现了店铺管理问题：后台查到部分店铺双十一这天发生了大批量的退货，随机挑选几款商品对比发现，这些货品前期基本不打折，而现在都参加了1111低折扣大促。那么真的是顾客退货吗（这是另外一个管理问题），还是有小部分员工进行欺诈，顾客前期高价买走了商品，有些员工擅自系统退单，然后活动当天以低价录入收银台，差额装入自己腰包？
如果这是一个低频事件，可能是顾客退货，但如果凡是促销，某店都会发生这种情况，频次较高，金额较大，则有可能是店铺管理问题。那么如何从海量的销售数据中抓出这些疑点，方便进行重点核实，以减轻公司损失？在Excel中使用Power Query可以10分钟内搭建这样一个自动化检测工具进行疑点检索。
1.销售数据导入公司ERP系统一般有类似如下的销售数据，包含店铺名称、销售日期、小票编号、销售类型（如促销，正价销售、退货），销售的商品编码、数量、折扣、金额等。
如果数据量不大，可以直接通过“数据”选项卡导入Power Query（下图为Excel 2016界面，Excel 2013需安装Power Query插件，2016及以上内置）。如果店铺很多，数据量超过百万行，可使用文件夹的方式导入。
2.销售退货分离
将导入Power Query的数据查询复制一份，分别命名为“销售”和“退货”，“销售”查询做个筛选动作，仅保留“销售类型”为“正价”和“促销”的明细。类似的，“退货”查询仅保留退货的明细。
3.销售退货横向合并
通过合并查询的方式，将“退货”查询横向合并到“销售”查询中（类似vLookup)，匹配列按照店铺和商品编号，实际应用可能更加具体，比方到每个营业员。
合并完成后，展开“退货”数据，得到以下完整表。这个表意味着，某店某货品在某日有多少销售，销售折扣多少，这个货品对应的在哪几天有退货，退货折扣几折，一目了然。
4.提取疑似异常明细
将没有退货的商品明细筛选去掉，然后判断什么是疑似异常明细。假设极少数员工想要欺诈牟利，那么他需要现将顾客高价买的商品在收银系统退掉，然后再按照现在促销折扣低价录入。所以，可以确定可疑数据需要至少满足两个条件（现实操作可能有更多条件，此处罗列根本点）：
退货时间>=再次录入销售的时间
退货折扣>再次录入销售的折扣
所以，新建如下条件列进行判断（此处假设当天退当天重新录票）
if [日期]=[退货.日期] and [销售折扣]<[退货.销售折扣] then "疑似异常" else "其他"
然后将疑似异常的明细筛选出来，下图即是部分可疑清单。
可以进行汇总统计，某店如果数量较多、频繁发生，就需要重点排查。等到用到这个工具的时候，其实已经有点晚了，这是一个管理问题。整个过程除了最后一个公式，其余都是点鼠标，最后附上自动生成的源码：
let
源 = Excel.CurrentWorkbook(){[Name="数据源"]}[Content],
更改数据类型 = Table.TransformColumnTypes(源,{{"日期", type date}, {"销量", type number}, {"销售折扣", type number}, {"销售金额", type number}, {"商品编号", type text}, {"促销名称", type text}, {"销售类型", type text}, {"小票编号", type text}, {"店铺", type text}}),
去除退货明细 = Table.SelectRows(更改数据类型, each ([销售类型] <> "退货")),
合并退货查询 = Table.NestedJoin(去除退货明细, {"店铺", "商品编号"}, 退货, {"店铺", "商品编号"}, "退货", JoinKind.LeftOuter),
展开退货查询 = Table.ExpandTableColumn(合并退货查询, "退货", {"日期", "小票编号", "销售类型", "销量", "销售折扣", "销售金额"}, {"退货.日期", "退货.小票编号", "退货.销售类型", "退货.销量", "退货.销售折扣", "退货.销售金额"}),
去除退货空白 = Table.SelectRows(展开退货查询, each ([退货.日期] <> null)),
添加判断条件 = Table.AddColumn(去除退货空白, "是否异常", each if [日期]=[退货.日期] and [销售折扣]<[退货.销售折扣] then "疑似异常" else "其他"),
筛选异常明细 = Table.SelectRows(添加判断条件, each ([是否异常] = "疑似异常"))
in
筛选异常明细公众号：wujunmincn
