- 业务场景:
公司A为了推广自家产品,增加订单量,招聘了大量的地推人员,规定地推人员需到终端门店去拜访,介绍产品特性,以自己的三寸不烂之舌,让店家心潮澎湃,并现场下单。然而,现实是残酷的,仅仅通过一次拜访店主就下单的情形相当之少。那有什么办法呢?公司A规定,地推人员必须经常去拜访,和店家建立好关系,相信总有一天店家会被地推人员的热情所感动,同时为了激励地推人员,只要去拜访,即使没能成功下单,也能获得补贴,按次计算。
地推人员小a灵机一动,想到可以不去现场拜访,虚假填报一些拜访记录,就能混到补贴了。然后,道高一尺,魔高一丈,公司A推出了一款定位打卡APP,规定拜访时必须打卡,APP会自动定位打卡位置。
- 审计场景:
既然APP会自动定位打卡位置,那么是否可以通过比对地推人员的打卡位置与终端门店的位置,判断地推人员是否在真实的位置打卡呢?
- 可行性分析:
- 终端门店位置信息分析:查看数据可以发现,终端门店的位置信息表示为:XXX省XXX市XXX县XXX路XXX号;
- 地推打卡记录信息分析:查看数据可以发现,打卡记录信息表示为:经纬度;
- 那么问题来了,如何将两种不同类型的数据进行对比呢?考虑是否可以将终端门店位置信息转换为经纬度后,再与打卡记录进行比较呢?
- 通过查询发现,高德地图提供了API,可以将地理位置地址转换为经纬度,貌似可行。
- 那么问题又来了,终端门店经纬度与打卡经纬度相差多少才算正常呢?那要测试一下咯,于是小编用手机,在不同地方记录经纬度,总结了如下规律:
考虑到高德地图定位精度,那就选择小数点后三位的经纬度差异进行比较;
- 实现方式(利用Python进行数据分析):
步骤一:将终端客户的地理地址转换为经纬度坐标
import requests
import pandas as pd
import openpyxl
import re
import numpy as np
def Modifysyntax(path):
customeraddress = pd.read_excel(path)
for i in range(len(customeraddress)):
if customeraddress.loc[i, '地址'] is not None:
s = str(customeraddress.loc[i, '地址'])
customeraddress.loc[i, '新地址'] = re.sub('[|/,()#]', '', s) # 将客户地址中的[|/,()#]'去掉
else:
continue
customeraddress.to_excel(path, index=False)
def ExcuteSingleQuery(locationList, currentkey):
# 1-将locationList中的地址连接成高德地图API能够识别的样子
locationList_abstract =locationList['新地址']
resultList = [] # 用来存放地理编码结果的空序列
k = 0
while k < len(locationList_abstract):
locationString = "" # 当前locationList组成的string
for j in range(0, 10):
locationString += locationList_abstract[k+j] + '|'
# 2-地理编码查询需要的Url
output = 'json' # 查询返回的形式
batch = 'true' # 是否支持多个查询
base = 'https://restapi.amap.com/v3/geocode/geo?' # 地理编码查询Url的头
currentUrl = base + "output=" + output + "&batch=" + batch + "&address=" + locationString + "&key=" + currentkey
# 3-提交请求
response = requests.get(currentUrl) # 提交请求
answer = response.json() # 接收返回
# 4-解析Json的内容
if answer['status'] == '1' and answer['info'] == 'OK':
# 4.1-请求和返回都成功,则进行解析
tmpList = answer['geocodes'] # 获取所有结果坐标点
for i in range(0, len(tmpList)):
try:
# 解析','分隔的经纬度
coordString = tmpList[i]['location']
coordList = coordString.split(',')
# 放入结果序列
resultList.append([float(coordList[0]), float(coordList[1])])
except:
# 如果发生错误则存入None
resultList.append(None)
# return resultList
elif answer['info'] == 'DAILY_QUERY_OVER_LIMIT':
# 4.2-当前账号的余额用完了,返回-1
return -1
else:
# 4.3-如果发生其他错误则返回-2
return -2
print(resultList)
k += 10
print(k)
return resultList
def write_to_excel(file_path, latlng): # 将转换的经纬度写入EXCEL中
wb = openpyxl.load_workbook(file_path) # 获取文件
ws = wb.active # 获取活跃的工作表
i = 14
for j in range(len(latlng)):
ws.cell(j + 2, i, latlng[j][0])
ws.cell(j + 2, i + 1, latlng[j][1])
wb.save(file_path) # 保存文件
if __name__ == '__main__':
path_one = '请输入文档存储路径'
Modifysyntax(path_one)
data = pd.read_excel(path_one)
frame = ExcuteSingleQuery(locationList=data,
currentkey="高德API调用时的秘钥")
write_to_excel(path_one, frame)
步骤二:将终端客户经纬度坐标与打卡记录经纬度坐标进行比较,筛选出异常数据:
import pandas as pd
path_one = '请输入地推人员打卡记录经纬度存储路径'
path_two = '请输入终端客户经纬度信息存储路径'
path = '请输入比对结果存储路径'
df1 = pd.read_excel(path_one)
df3 = pd.read_excel(path_two)
df5 = pd.merge(df1, df3, left_on='df1的主键', right_on='df3相对于df1的外键', how='left') # 实现vlookup功能
result = []
for i in range(len(df5)):
if abs(df5.loc[i, '经度']-df5.loc[i, '经度(转换)']) >= 0.001 and abs(df5.loc[i, '纬度']-df5.loc[i, '维度(转换)']) >= 0.001: # 小数点后三位,筛选出不满足条件的数据
result.append(df5.loc[i, :])
data = pd.DataFrame(result) # 将列表转为dataframe
data.to_excel(path, index=False)
至此,我们就可以找出那些地推人员打卡记录不真实的数据,表明他们没有真正去终端客户拜访,很可能在家或在其他地方打卡。
以上就是通过数据分析的方式,结合业务场景,进行审计的全部过程,如果大家喜欢我的文章,请添加关注微信公众号(二帮主做IT审计),你们的关注和赞赏,是我不断更新的动力。
http://weixin.qq.com/r/_zl5YfbEe6sErbIC92zp (二维码自动识别)