189 lines
8.4 KiB
Plaintext
189 lines
8.4 KiB
Plaintext
{
|
|
"cells": [
|
|
{
|
|
"metadata": {},
|
|
"cell_type": "markdown",
|
|
"source": "宜搭导出文件字段对应",
|
|
"id": "ccd76344cc98d6e6"
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"id": "initial_id",
|
|
"metadata": {
|
|
"collapsed": true,
|
|
"ExecuteTime": {
|
|
"end_time": "2026-03-07T02:04:58.912156600Z",
|
|
"start_time": "2026-03-07T02:04:55.474219900Z"
|
|
}
|
|
},
|
|
"source": [
|
|
"import pandas as pd\n",
|
|
"import numpy as np\n",
|
|
"from datetime import datetime\n",
|
|
"\n",
|
|
"\n",
|
|
"# 读取旧数据,记得排除下有订单的\n",
|
|
"old_df = pd.read_excel(fr\"C:\\Users\\hp_z66\\OneDrive\\Desktop\\续约服务数据合并报告_20260307.xlsx\",sheet_name = \"今天前30及后120\") # 或 read_csv\n",
|
|
"\n",
|
|
"# 初始化新 DataFrame（列按新表头）\n",
|
|
"new_columns = [\n",
|
|
" \"关联数据\", \"公司名称\", \"门店名称\", \"门店编码\", \"加盟商\", \"过期日\", \"Saas版本\",\n",
|
|
" \"上次购买价格\", \"联系人\", \"联系手机号\", \"运营顾问\", \"订单商品名称\",\n",
|
|
" \"120天是否跟进\", \"120天处理人\", \"120天跟进时间\",\n",
|
|
" \"60天是否跟进\", \"60天处理人\", \"60天跟进时间\",\n",
|
|
" \"30天是否跟进\", \"30天处理人\", \"30天跟进时间\",\n",
|
|
" \"是否联系上\", \"现阶段问题\", \"未联系原因\", \"联系情况及问题说明\", \"回访方式\",\n",
|
|
" \"潜在商机\", \"商机归属\", \"商机详情\", \"续约意愿\", \"不续约原因\",\n",
|
|
" \"产品问题\", \"服务问题\", \"门店问题\", \"价格问题\", \"不续约具体情况说明\",\n",
|
|
" \"周期性增购\", \"连锁门店待办同步处理\", \"选择需要同步的门店名称\",\n",
|
|
" \"订单编码\", \"订单支付日期\", \"本次-实付金额（元）\", \"业务类型（续约、升级）\",\n",
|
|
" \"120天自动流转时间\", \"60天自动流转时间\", \"30天自动流转时间\", \"0天自动流转时间\",\n",
|
|
" \"当前所处节点\", \"流程状态\", \"经营模式\", \"程序关停\", \"区域客服\", \"公司等级\",\n",
|
|
" \"运营专家\", \"流水号\", \"是否已同步宜搭\", \"连锁店数据同步辅助：门店列表\",\n",
|
|
" \"公司id\", \"提交人\", \"提交时间\", \"更新时间\", \"流程状态\", \"当前节点\", \"当前负责人\",\n",
|
|
" \"商品名称\", \"购买数量\", \"购买金额\", \"应续约日\", \"是否愿意续约\", \"不续约原因\", \"续约后订单编码\"\n",
|
|
"]\n",
|
|
"\n",
|
|
"new_df = pd.DataFrame(columns=new_columns)\n",
|
|
"\n",
|
|
"# 字段映射\n",
|
|
"mapping = {\n",
|
|
" \"门店名称\": \"门店名称\",\n",
|
|
" \"门店编码\": \"门店编码\",\n",
|
|
" \"加盟商\": \"加盟商\",\n",
|
|
" \"过期日\": \"过期日\",\n",
|
|
" \"Saas版本\": \"Saas版本\",\n",
|
|
" \"上次购买价格\": \"上次购买价格\",\n",
|
|
" \"联系人\": \"联系人\",\n",
|
|
" \"联系手机号\": \"联系手机号\",\n",
|
|
" \"专属运营顾问\": \"运营顾问\",\n",
|
|
" \"订单编码\": \"订单编码\",\n",
|
|
" \"订单支付日期\": \"订单支付日期\",\n",
|
|
" \"客户等级\": \"公司等级\",\n",
|
|
" \"联系情况及问题说明\": \"联系情况及问题说明\",\n",
|
|
" \"产品原因\": \"产品问题\",\n",
|
|
" \"服务问题\": \"服务问题\",\n",
|
|
" \"门店原因\": \"门店问题\",\n",
|
|
" \"价格原因\": \"价格问题\",\n",
|
|
" \"不续约具体情况说明\": \"不续约具体情况说明\",\n",
|
|
" \"公司名称\": \"公司名称\",\n",
|
|
" \"经营模式\": \"经营模式\",\n",
|
|
" \"区域客服\": \"区域客服\",\n",
|
|
" \"提交人\": \"提交人\",\n",
|
|
" \"创建时间\": \"提交时间\",\n",
|
|
" \"最近修改时间\": \"更新时间\",\n",
|
|
" \"应续约日\": \"应续约日\",\n",
|
|
" \"区域经理\": \"运营专家\", # 假设区域经理作为运营专家\n",
|
|
" \"120天处理人\":\"120天是否跟进\",\n",
|
|
" \"60天处理人\":\"60天是否跟进\",\n",
|
|
" \"30天处理人\":\"30天是否跟进\",\n",
|
|
" \"到期前120天联系情况_提交人\":\"120天处理人\",\n",
|
|
" \"到期前60天联系情况_提交人\":\"60天处理人\",\n",
|
|
" \"到期前30天联系情况_提交人\":\"30天处理人\",\n",
|
|
" \"到期前120天联系情况_日期\":\"120天跟进时间\",\n",
|
|
" \"到期前60天联系情况_日期\":\"60天跟进时间\",\n",
|
|
" \"到期前30天联系情况_日期\":\"30天跟进时间\",\n",
|
|
"}\n",
|
|
"\n",
|
|
"# 处理每一行\n",
|
|
"def map_row(row):\n",
|
|
" new_row = {col: None for col in new_columns}\n",
|
|
"\n",
|
|
" # 基础映射\n",
|
|
" for old_col, new_col in mapping.items():\n",
|
|
" if old_col in row.index:\n",
|
|
" new_row[new_col] = row[old_col]\n",
|
|
"\n",
|
|
" # 自动流转时间\n",
|
|
" new_row['120天自动流转时间'] = new_row['过期日'] - pd.Timedelta(days=60)\n",
|
|
" new_row['60天自动流转时间'] = new_row['过期日'] - pd.Timedelta(days=30)\n",
|
|
" new_row['30天自动流转时间'] = new_row['过期日'] - pd.Timedelta(days=0)\n",
|
|
" new_row['0天自动流转时间'] = new_row['过期日'] + pd.Timedelta(days=90)\n",
|
|
"\n",
|
|
"\n",
|
|
" # 是否联系上：旧表有“是否联系上客户”\n",
|
|
" contacted = row.get(\"是否联系上客户\", None)\n",
|
|
" if pd.notna(contacted):\n",
|
|
" new_row[\"是否联系上\"] = contacted\n",
|
|
" else:\n",
|
|
" # 或根据联系情况推断\n",
|
|
" new_row[\"是否联系上\"] = \"是\" if pd.notna(row.get(\"联系情况及问题说明\")) else \"否\"\n",
|
|
"\n",
|
|
" # 续约意愿\n",
|
|
" willing = row.get(\"门店是否愿意续约\", None)\n",
|
|
" new_row[\"是否愿意续约\"] = willing\n",
|
|
" new_row[\"续约意愿\"] = \"愿意\" if willing == \"是\" else (\"不愿意\" if willing == \"否\" else willing)\n",
|
|
"\n",
|
|
" # 不续约原因（合并分类）\n",
|
|
" reasons = []\n",
|
|
" if row.get(\"产品原因\") == \"是\": reasons.append(\"产品问题\")\n",
|
|
" if row.get(\"服务问题\") == \"是\": reasons.append(\"服务问题\")\n",
|
|
" if row.get(\"门店原因\") == \"是\": reasons.append(\"门店问题\")\n",
|
|
" if row.get(\"价格原因\") == \"是\": reasons.append(\"价格问题\")\n",
|
|
" new_row[\"不续约原因\"] = \"; \".join(reasons) if reasons else None\n",
|
|
"\n",
|
|
" # 流程状态：根据实例状态或审批结果\n",
|
|
" status = row.get(\"实例状态\", \"\")\n",
|
|
" if \"完成\" in str(status) or \"通过\" in str(status):\n",
|
|
" new_row[\"流程状态\"] = \"已完成\"\n",
|
|
" elif \"终止\" in str(status) or \"拒绝\" in str(status):\n",
|
|
" new_row[\"流程状态\"] = \"已终止\"\n",
|
|
" else:\n",
|
|
" new_row[\"流程状态\"] = \"进行中\"\n",
|
|
"\n",
|
|
" new_row[\"当前所处节点\"] = row.get(\"当前审批节点\", None)\n",
|
|
" new_row[\"当前节点\"] = new_row[\"当前所处节点\"]\n",
|
|
"\n",
|
|
" # 默认值填充\n",
|
|
" new_row[\"业务类型（续约、升级）\"] = \"续约\"\n",
|
|
" new_row[\"回访方式\"] = \"电话\"\n",
|
|
" new_row[\"程序关停\"] = False\n",
|
|
" new_row[\"是否已同步宜搭\"] = False\n",
|
|
" new_row[\"商品名称\"] = \"SaaS服务\"\n",
|
|
" new_row[\"购买数量\"] = 1\n",
|
|
" new_row[\"购买金额\"] = row.get(\"上次购买价格\", None)\n",
|
|
"\n",
|
|
" return pd.Series(new_row)\n",
|
|
"\n",
|
|
"# 应用映射\n",
|
|
"new_df = old_df.apply(map_row, axis=1)\n",
|
|
"\n",
|
|
"# 保存\n",
|
|
"new_df.to_excel(\"new_table_migrated.xlsx\", index=False)\n",
|
|
"print(\"迁移完成！\")"
|
|
],
|
|
"outputs": [
|
|
{
|
|
"name": "stdout",
|
|
"output_type": "stream",
|
|
"text": [
|
|
"迁移完成！\n"
|
|
]
|
|
}
|
|
],
|
|
"execution_count": 4
|
|
}
|
|
],
|
|
"metadata": {
|
|
"kernelspec": {
|
|
"display_name": "Python 3",
|
|
"language": "python",
|
|
"name": "python3"
|
|
},
|
|
"language_info": {
|
|
"codemirror_mode": {
|
|
"name": "ipython",
|
|
"version": 2
|
|
},
|
|
"file_extension": ".py",
|
|
"mimetype": "text/x-python",
|
|
"name": "python",
|
|
"nbconvert_exporter": "python",
|
|
"pygments_lexer": "ipython2",
|
|
"version": "2.7.6"
|
|
}
|
|
},
|
|
"nbformat": 4,
|
|
"nbformat_minor": 5
|
|
}
|