def add_sellersku_column(df, limit_num=1000):
"""
报表中添加sellersku列
用4个线程去请求
通过接口,请求sellersku
:param df:
:return:
"""
global request_erpsku_queue, response_erpsku_queue
public_function.detect_df(df)
# 采用4线程去请求
# 需要查询的sku列表
request_sku = list(set(df['sku']))
request_erpsku_queue = public_function.Queue(request_sku)
# 返回结果列表
response_erpsku_queue = public_function.Queue()
# 多线程请求
thread_query_seller_sku()
# 请求结果
response_erpsku = response_erpsku_queue.items
if not response_erpsku:
return
response_erpsku_df = pd.concat(response_erpsku)
# seller-erpsku字典
response_erpsku_df['seller_name'] = response_erpsku_df['seller_name'].apply(
lambda x: x.upper() if x is not None else None)
response_erpsku_df = pd.merge(df, response_erpsku_df, left_on=['账号', 'sku'], right_on=['seller_name', 'sku'],
how='left')
response_erpsku_df.drop_duplicates(inplace=True)
return response_erpsku_df
def add_erpsku_column(df, limit_num=1000):
"""
报表中添加erpsku列
用4个线程去请求
通过接口,请求erpsku
:param df:
:return:
"""
global request_sku_queue, response_sku_queue
public_function.detect_df(df)
# 采用4线程去请求
# 需要查询的sku列表
request_sku = list(set(df['Seller SKU']))
request_sku_queue = public_function.Queue(request_sku)
# 返回结果列表
response_sku_queue = public_function.Queue()
# 多线程请求
thread_query()
# 请求结果
response_sku_result = response_sku_queue.items
if not response_sku_result:
return
response_sku_result = pd.concat(response_sku_result)
# seller-erpsku字典
sellersku_erpsku_dict = {sellsku: erpsku for sellsku, erpsku in
zip(response_sku_result['seller_sku'], response_sku_result['sku'])}
df['erpsku'] = [sellersku_erpsku_dict.get(seller_sku, '') for seller_sku in df['Seller SKU']]
def seller_sale_rank(station_saler):
"""
:param station_saler:
:return:
"""
public_function.detect_df(station_saler)
if station_saler.empty:
return
# 账号
station_name = station_saler['账号'].values[0]
station_saler_last_31_days = station_saler[station_saler['付款时间'] >= thirty_day_before]
station_saler_last_7_days = station_saler[station_saler['付款时间'] >= seven_day_before]
station_saler_last_days = station_saler[station_saler['付款时间'] == last_day]
# 将最近1天,最近7天,最近31天sku的销售额汇总,排序
station_saler_different_days_sales = []
days = [31, 7, 1]
for day, range_day_data in zip(days, [station_saler_last_31_days, station_saler_last_7_days,
station_saler_last_days]):
sku_saler_rank = range_day_data.groupby(['seller_sku']).agg(
{'销售额': 'sum', '数量': 'sum', 'sku': 'first'}).reset_index()
sku_saler_rank.sort_values(by=['销售额'], ascending=False, inplace=True)
sku_saler_rank['排名'] = range(1, len(sku_saler_rank) + 1)
sku_saler_rank.rename(columns={'销售额': f'{day}天销售额', '排名': f'{day}天排名'}, inplace=True)
station_saler_different_days_sales.append(sku_saler_rank)
station_saler_different_days_sales_rank = pd.merge(station_saler_different_days_sales[0],
station_saler_different_days_sales[1], how='left',
on='seller_sku')
station_saler_different_days_sales_rank = pd.merge(station_saler_different_days_sales_rank,
station_saler_different_days_sales[2], how='left',
on='seller_sku')
# 将排名的格式中为np.float64(列中有nan值)转换为int
for col in ['31天排名', '7天排名', '1天排名']:
if station_saler_different_days_sales_rank[col].dtype not in (np.int32, np.int64):
station_saler_different_days_sales_rank[col] = station_saler_different_days_sales_rank[col].apply(
lambda x: str(x).replace('.0','') if ~pd.isna(x) else ' ')
station_saler_different_days_sales_rank['账号'] = station_name
del station_saler_different_days_sales_rank['sku']
station_saler_different_days_sales_rank.rename(columns={'31天销售额': '总销售额', 'sku_x': 'sku', '数量_x': '总销售数量'},
inplace=True)
station_saler_rank_export_columns = ['账号', 'seller_sku', 'sku', '总销售额', '31天排名', '7天销售额', '7天排名', '1天销售额',
'1天排名', '总销售数量']
return station_saler_different_days_sales_rank[station_saler_rank_export_columns]
def get_top_10(station_name, station_df, top_num=10):
"""
获取店铺销售额top10sku的数据
:type station_name:
:param df:
:return:
"""
public_function.detect_df(df)
if df.empty:
return
# 店铺最近一天的top10
station_last_day_top10 = station_df[station_df['付款时间'] == last_day].nlargest(top_num, '销售额').groupby(
['seller_sku']).agg(
{'销售额': 'sum'}).reset_index().nlargest(top_num, '销售额')
station_last_day_top10_sku = list(station_last_day_top10['seller_sku'])
# 店铺最近七天的top10
station_last_seven_day_top10 = station_df[station_df['付款时间'] >= seven_day_before].groupby(['seller_sku']).agg(
{'销售额': 'sum'}).reset_index().nlargest(top_num, '销售额')
station_last_seven_top10_sku = list(station_last_seven_day_top10['seller_sku'])
# 店铺最近30天的top10
station_last_month_day_top10 = station_df[station_df['付款时间'] >= thirty_day_before].groupby(['seller_sku']).agg(
{'销售额': 'sum'}).reset_index().nlargest(top_num, '销售额')
station_last_month_top10_sku = list(station_last_month_day_top10['seller_sku'])
station_top10 = pd.DataFrame(
[station_last_day_top10_sku, station_last_seven_top10_sku, station_last_month_top10_sku]).T
station_top10.columns = export_columns_name[3:-1]
account = station_name[:-3]
site = station_name[-2:]
station_top10['账号'] = station_name
station_top10['account'] = account
station_top10['site'] = site
station_top10['sales_rank'] = range(1, len(station_top10) + 1)
return station_top10
def process_stations_perf():
"""
处理站点的表现主函数:
1.广告有订单的表现
2.加入购物车广告的表现
3.站点有销售却广告没有销售的表现
4.将店铺销售额排名(前10:昨日,最近7天,最近30天)
:return: xlsx
得到excel
"""
global site_exchange_rate, station_name_corresponding_dict
# 得到汇率
site_exchange_rate = exchange_rate.rate_exchange()
# 调整输出汇率格式
site_exchange_rate_df = pd.DataFrame(site_exchange_rate, index=[0]).T
site_exchange_rate_df['国家简称'] = site_exchange_rate_df.index
site_exchange_rate_df['国家'] = [static_param.SITE_EN_ZH[account] if account != 'updatetime' else '' for account
in site_exchange_rate_df['国家简称']]
site_exchange_rate_df.rename(columns={0: '汇率'}, inplace=True)
site_exchange_rate_df = site_exchange_rate_df.applymap(lambda x: '更新时间' if x == 'updatetime' else x)
site_exchange_rate_df = site_exchange_rate_df.reindex(columns=['国家', '国家简称', '汇率'])
# 输入folder_path
# 对文件夹进行判断
# 获取输入框中内容
folder_path = address.get()
folder_path = folder_path.strip('" ')
base_path = os.path.dirname(folder_path)
# 显示输入内容
showtext = f'开始处理" {folder_path} "文件夹,请耐心等待.'
lab3.insert('insert', showtext + '\n')
# lab3.update()
lab3.update()
public_function.detect_folder(folder_path)
# 导入店铺名对应关系表
# 加载店铺销售数据
station_name_corresponding_file_name = ['店铺名统一化.xls', '店铺名统一化.xlsx']
station_name_corresponding_file_exist = 0
for file in station_name_corresponding_file_name:
station_name_corresponding_file_path = os.path.join(base_path, file)
if os.path.exists(station_name_corresponding_file_path):
station_name_corresponding_file_exist = 1
break
if station_name_corresponding_file_exist == 0:
messagebox.showinfo('文件不存在', f'店铺对应关系表不存在,请核查{base_path}下是否有 {station_name_corresponding_file_name} 文件.')
station_name_corresponding_data = load_station_name_corresponding(station_name_corresponding_file_path)
station_name_corresponding_data.drop_duplicates(subset=['ERP account'], inplace=True)
station_name_corresponding_dict = {lazada_account.upper(): erp_account.upper() for erp_account, lazada_account in
zip(station_name_corresponding_data['ERP account'],
station_name_corresponding_data['lazada account'])}
station_name_reverse_corresponding_dict = {erp_account.upper(): lazada_account.upper() for
erp_account, lazada_account in
zip(station_name_corresponding_data['ERP account'],
station_name_corresponding_data['lazada account'])}
# 得到全部站点的表现
[stations_perf, sku_have_ordered_perf, sku_no_ordered_perf] = get_all_station_perf(folder_path)
# 广告信息中添加一列erpsku列
add_erpsku_column(sku_have_ordered_perf)
add_erpsku_column(sku_no_ordered_perf)
# 将站点表现中的账号名(lazada平台上的账号名)转换为erpsku上的账号名
stations_perf['account'] = stations_perf['account'].apply(
lambda x: station_name_corresponding_dict.get(x.upper(), f'{x}找不到对应的erp 账号'))
sku_have_ordered_perf['account'] = sku_have_ordered_perf['account'].apply(
lambda x: station_name_corresponding_dict.get(x.upper(), f'{x}找不到对应的erp 账号'))
sku_no_ordered_perf['account'] = sku_no_ordered_perf['account'].apply(
lambda x: station_name_corresponding_dict.get(x.upper(), f'{x}找不到对应的erp 账号'))
for df in [stations_perf, sku_have_ordered_perf, sku_no_ordered_perf]:
df['station'] = df['account'] + '-' + df['site']
"""
添加店铺sku销售情况:
1.店铺有广告表现:
有订单sku部分,无订单sku表部分均中添加两列:
1. sku广告销售额占整个店铺销售额占比,
2. sku广告销售数量占整个店铺销售数量占比
2.店铺无广告表现:
新建一个表格,包含列为:
日期、账号、站点、sku、sku销售数量、sku销售额、sku销售数量占比、sku销售额占比
"""
# 加载店铺销售数据
shop_sales_file_name = ['lazada总销.xls', 'lazada总销.xlsx', 'lazada总销.csv']
shop_sale_exist = 0
for file in shop_sales_file_name:
shop_sales_file_path = os.path.join(base_path, file)
if os.path.exists(shop_sales_file_path):
shop_sale_exist = 1
break
if shop_sale_exist == 0:
messagebox.showinfo('文件不存在', f'lazada总销售数据不存在,请核查{base_path}下是否有{shop_sales_file_name}文件.')
shop_sales_file_type = os.path.splitext(shop_sales_file_path)[1]
if shop_sales_file_type.lower() == '.csv':
try:
shop_sales_file_data = pd.read_csv(shop_sales_file_path, encoding='gb2312')
except:
shop_sales_file_data = pd.read_csv(shop_sales_file_path)
elif shop_sales_file_type.lower() in ['.xls', '.xlsx']:
try:
shop_sales_file_data = pd.read_excel(shop_sales_file_path, encoding='gb2312')
except:
shop_sales_file_data = pd.read_csv(shop_sales_file_path)
else:
raise ImportError(f'{shop_sales_file_path} is not a valid file.File type Must input .csv .xls or .xlsx')
# 初始化店铺数据
shop_data = process_station.init_shop_file_data(shop_sales_file_data)
# 将店铺销售额转换成人民币
shop_data['销售额'] = [round(site_exchange_rate[site] * sale, 2) for sale, site in
zip(shop_data['销售额'], shop_data['site'])]
# 店铺数据按照日期,店铺,sku来汇总
shop_data = shop_data.groupby(['付款时间', '账号', 'sku']).agg(
{'数量': 'sum', '销售额': 'sum', '平台': 'first', 'account': 'first', 'site': 'first', '订单号': 'first',
'币种': 'first'}).reset_index()
# 添加seller_sku列
shop_data_columns = list(shop_data.columns)
shop_data_columns.append('seller_sku')
shop_data = add_sellersku_column(shop_data)
shop_data = shop_data[shop_data_columns]
# 店铺按照日期,账号,sku分类
shop_data_sorted_by_sku = shop_data[['付款时间', '账号', 'sku', '数量', '销售额']]
# 广告每天销售的sku的集合
need_columns = ['Date', 'station', 'erpsku']
ad_station_sku_info = pd.concat([sku_have_ordered_perf[need_columns], sku_no_ordered_perf[need_columns]])
ad_station_sku_set = ad_station_sku_info.groupby(['Date', 'station']).agg(
{'erpsku': lambda x: set(x)}).reset_index()
# 店铺有订单,广告没有订单集合
shop_data_merge_ad_sku_set = pd.merge(shop_data, ad_station_sku_set, left_on=['付款时间', '账号'],
right_on=['Date', 'station'], how='left')
shop_data['sku_status'] = [None if pd.isna(ad_sku_set) else 1 if shop_sku in ad_sku_set else 0 for
shop_sku, ad_sku_set
in zip(shop_data_merge_ad_sku_set['sku'], shop_data_merge_ad_sku_set['erpsku'])]
shop_sales_ad_no_sales = shop_data[shop_data['sku_status'] != 0]
"""
1. 有订单sku表现/无订单sku表现 表中添加两列:
sku销售额占比以及销售数量占比
2. 店铺无广告的sku情况
"""
# 添加销售数量占比,销售额占比
sku_have_ordered_perf = pd.merge(sku_have_ordered_perf, shop_data_sorted_by_sku,
left_on=['Date', 'station', 'erpsku'],
right_on=['付款时间', '账号', 'sku'], how='left')
sku_have_ordered_perf['销售数量占比'] = [
'0%' if ((shop_sku_num == 0) or (
pd.isna(shop_sku_num))) else str(round(ad_sku_num * 100 / shop_sku_num, 2)) + '%' for
ad_sku_num, shop_sku_num
in zip(sku_have_ordered_perf['Units Sold'], sku_have_ordered_perf['数量'])]
sku_have_ordered_perf['销售额占比'] = [
'0%' if ((shop_sku_sale == 0) or (
pd.isna(shop_sku_sale))) else str(round(ad_sku_sale * 100 / shop_sku_sale, 2)) + '%' for
ad_sku_sale, shop_sku_sale
in zip(sku_have_ordered_perf['Revenue'], sku_have_ordered_perf['销售额'])]
sku_have_ordered_perf.rename(columns={'数量': '店铺销售数量', '销售额': '店铺销售额'}, inplace=True)
# sku_no_ordered_perf = pd.merge(sku_no_ordered_perf, shop_data_sorted_by_sku,
# left_on=['Date', 'station', 'erpsku'],
# right_on=['付款时间', '账号', 'sku'], how='left')
#
# sku_no_ordered_perf['销售数量占比'] = [
# '0%' if (shop_sku_num == 0) or (
# pd.isna(shop_sku_num)) else str(round(ad_sku_num * 100 / shop_sku_num, 2)) + '%' for ad_sku_num, shop_sku_num
# in zip(sku_no_ordered_perf['次数'], sku_no_ordered_perf['数量'])]
#
# sku_no_ordered_perf.rename(columns={'数量': '店铺销售数量'}, inplace=True)
del sku_have_ordered_perf['付款时间']
"""
调整输出格式:
1.将站点名修改为lzdaza命名规则
2.将日期格式调整为yyyymmdd格式
3.百分比输出格式调整
4.调整列名位置
5.调整列输出项
"""
# 将站点名命名为lazada命名规则
def trans_erp_account_into_lazada_account(df):
df['lazada_account'] = df['account'].apply(lambda x: station_name_reverse_corresponding_dict.get(x, None))
# 添加一列station列:
df['lazada_station'] = df['lazada_account'] + '-' + df['site']
# 将日期格式调整为yyyymmdd
def adjust_date_format(df):
columns = df.columns
if 'Date' in columns:
df['Date'] = df['Date'].apply(lambda x: datetime.strftime(x, '%Y%m%d'))
if '付款时间' in columns:
df['付款时间'] = df['付款时间'].apply(lambda x: datetime.strftime(x, '%Y%m%d'))
# 将店铺销售额按照前10排序
# 计算店铺最近1天,最近7天,最近30天
last_day = max(shop_data['付款时间'])
seven_day_before = last_day - timedelta(days=7)
thirty_day_before = last_day - timedelta(days=31)
# 获取店铺前十的数据
def get_top_10(station_name, station_df, top_num=10):
"""
获取店铺销售额top10sku的数据
:type station_name:
:param df:
:return:
"""
public_function.detect_df(df)
if df.empty:
return
# 店铺最近一天的top10
station_last_day_top10 = station_df[station_df['付款时间'] == last_day].nlargest(top_num, '销售额').groupby(
['seller_sku']).agg(
{'销售额': 'sum'}).reset_index().nlargest(top_num, '销售额')
station_last_day_top10_sku = list(station_last_day_top10['seller_sku'])
# 店铺最近七天的top10
station_last_seven_day_top10 = station_df[station_df['付款时间'] >= seven_day_before].groupby(['seller_sku']).agg(
{'销售额': 'sum'}).reset_index().nlargest(top_num, '销售额')
station_last_seven_top10_sku = list(station_last_seven_day_top10['seller_sku'])
# 店铺最近30天的top10
station_last_month_day_top10 = station_df[station_df['付款时间'] >= thirty_day_before].groupby(['seller_sku']).agg(
{'销售额': 'sum'}).reset_index().nlargest(top_num, '销售额')
station_last_month_top10_sku = list(station_last_month_day_top10['seller_sku'])
station_top10 = pd.DataFrame(
[station_last_day_top10_sku, station_last_seven_top10_sku, station_last_month_top10_sku]).T
station_top10.columns = export_columns_name[3:-1]
account = station_name[:-3]
site = station_name[-2:]
station_top10['账号'] = station_name
station_top10['account'] = account
station_top10['site'] = site
station_top10['sales_rank'] = range(1, len(station_top10) + 1)
return station_top10
# 店铺seller sku销售排名
def seller_sale_rank(station_saler):
"""
:param station_saler:
:return:
"""
public_function.detect_df(station_saler)
if station_saler.empty:
return
# 账号
station_name = station_saler['账号'].values[0]
station_saler_last_31_days = station_saler[station_saler['付款时间'] >= thirty_day_before]
station_saler_last_7_days = station_saler[station_saler['付款时间'] >= seven_day_before]
station_saler_last_days = station_saler[station_saler['付款时间'] == last_day]
# 将最近1天,最近7天,最近31天sku的销售额汇总,排序
station_saler_different_days_sales = []
days = [31, 7, 1]
for day, range_day_data in zip(days, [station_saler_last_31_days, station_saler_last_7_days,
station_saler_last_days]):
sku_saler_rank = range_day_data.groupby(['seller_sku']).agg(
{'销售额': 'sum', '数量': 'sum', 'sku': 'first'}).reset_index()
sku_saler_rank.sort_values(by=['销售额'], ascending=False, inplace=True)
sku_saler_rank['排名'] = range(1, len(sku_saler_rank) + 1)
sku_saler_rank.rename(columns={'销售额': f'{day}天销售额', '排名': f'{day}天排名'}, inplace=True)
station_saler_different_days_sales.append(sku_saler_rank)
station_saler_different_days_sales_rank = pd.merge(station_saler_different_days_sales[0],
station_saler_different_days_sales[1], how='left',
on='seller_sku')
station_saler_different_days_sales_rank = pd.merge(station_saler_different_days_sales_rank,
station_saler_different_days_sales[2], how='left',
on='seller_sku')
# 将排名的格式中为np.float64(列中有nan值)转换为int
for col in ['31天排名', '7天排名', '1天排名']:
if station_saler_different_days_sales_rank[col].dtype not in (np.int32, np.int64):
station_saler_different_days_sales_rank[col] = station_saler_different_days_sales_rank[col].apply(
lambda x: str(x).replace('.0','') if ~pd.isna(x) else ' ')
station_saler_different_days_sales_rank['账号'] = station_name
del station_saler_different_days_sales_rank['sku']
station_saler_different_days_sales_rank.rename(columns={'31天销售额': '总销售额', 'sku_x': 'sku', '数量_x': '总销售数量'},
inplace=True)
station_saler_rank_export_columns = ['账号', 'seller_sku', 'sku', '总销售额', '31天排名', '7天销售额', '7天排名', '1天销售额',
'1天排名', '总销售数量']
return station_saler_different_days_sales_rank[station_saler_rank_export_columns]
shop_sale_top10 = []
shop_sale_rank_info = []
shop_data_have_seller_sku = shop_data[~pd.isna(shop_data['seller_sku'])]
station_shop_grouped_data = shop_data_have_seller_sku.groupby(['账号'])
export_columns_name = ['账号', 'account', 'site', '当日_top10_seller_sku', '近7天_top10_seller_sku',
'近31天_day_top10_seller_sku', 'sales_rank']
for station_name, station_df in station_shop_grouped_data:
account = station_name[:-3]
site = station_name[-2:]
lazada_account = station_name_reverse_corresponding_dict.get(account.upper(), f'{account} error')
station_name = lazada_account + '-' + site
#
station_shop_sale_top10 = get_top_10(station_name, station_df)
# 店铺排名
station_df['账号'] = station_name
station_df = seller_sale_rank(station_df)
if not station_shop_sale_top10.empty:
shop_sale_top10.append(station_shop_sale_top10)
if not station_df.empty:
shop_sale_rank_info.append(station_df)
shop_sale_top10 = pd.concat(shop_sale_top10)
shop_sale_top10 = shop_sale_top10[export_columns_name]
# 店铺排名
shop_sale_rank_info = pd.concat(shop_sale_rank_info)
export_columns_dict = {'站点联盟表现': {'value': stations_perf,
'export_columns': ['Date', 'lazada_station', 'lazada_account', 'site',
'Est. Spend',
'Revenue', 'ROI']},
'联盟出单sku表现': {'value': sku_have_ordered_perf,
'export_columns': ['Date', 'lazada_station', 'lazada_account', 'site',
'Seller SKU', 'erpsku', 'Est. Spend', 'Revenue', 'Orders',
'Units Sold', 'ROI', '店铺销售数量', '店铺销售额', '销售数量占比', '销售额占比']},
'联盟未成交sku表现': {'value': sku_no_ordered_perf,
'export_columns': ['Date', 'lazada_station', 'lazada_account', 'site',
'Seller SKU', 'erpsku', '次数']},
'站点有销售广告没销售': {'value': shop_sales_ad_no_sales,
'export_columns': ['付款时间', '平台', 'lazada_station', 'lazada_account', 'site',
'币种', 'seller_sku', 'sku', '数量', '销售额']}}
for _, export_items_dict in export_columns_dict.items():
public_function.detect_df(export_items_dict['value'])
if not export_items_dict['value'].empty:
trans_erp_account_into_lazada_account(export_items_dict['value'])
adjust_date_format(export_items_dict['value'])
export_items_dict['value'] = export_items_dict['value'].reindex(columns=export_items_dict['export_columns'])
# 输出到同文件夹下
datetime_now = datetime.now().strftime('%Y-%m-%d_%H-%M')
export_basename = f'lazada站点表现_{datetime_now}.xlsx'
export_path = os.path.join(base_path, export_basename)
writer = pd.ExcelWriter(export_path)
for sheet_name, perf in export_columns_dict.items():
perf_data = perf['value']
if len(perf_data) > 0:
perf_data.to_excel(writer, sheet_name=sheet_name, index=False)
# 输出top10 sku
if not shop_sale_top10.empty:
shop_sale_top10.to_excel(writer, sheet_name='站点总销售sku top10', index=False)
# 输出店铺销售排名
if not shop_sale_rank_info.empty:
shop_sale_rank_info.to_excel(writer, sheet_name='站点总销售sku排名', index=False)
# 文件件站点情况
if empty_file_stations:
empty_file_stations.sort(reverse=False)
empty_file_stations_df = pd.DataFrame([empty_file_stations]).T
empty_file_stations_df.columns = ['站点无文件']
empty_file_stations_df.to_excel(writer, sheet_name='站点无文件', index=False)
# 输出汇率
if not site_exchange_rate_df.empty:
site_exchange_rate_df.to_excel(writer, sheet_name='汇率', index=False)
writer.save()
# 输出
showtext = f'处理完毕,结果输出在文件: {export_path} 中.请关闭此窗口.或此窗口将在3秒钟后自动关闭'
lab3.insert('insert', '\n')
lab3.insert('insert', '\n')
lab3.insert('insert', showtext)
lab3.update()
time.sleep(3)
win.destroy()