106 lines
4.5 KiB
Python
106 lines
4.5 KiB
Python
#!/usr/bin/env python3
|
||
# 从本机 Excel 补齐「数据库没有表」的渠道数据（Adsterra / TrafficStars / Eagllwin 等）。
|
||
# 数据库只有 mintegral/bigo/mediago，其余渠道由运营手工维护到 Excel，本脚本读取后
|
||
# 输出成与 db_fetch.py 相同结构的 JSON（stdout），供 daily_sync 合并进 mtg_data.json。
|
||
#
|
||
# ── Excel 规范 ──────────────────────────────────────────────
|
||
# 路径：<运营>/Voypace/Voypace补充数据.xlsx （可用环境变量 VOYPACE_XLSX 覆盖）
|
||
# 第一个 sheet，首行为表头，需包含以下列（列名大小写/顺序不限）：
|
||
# date 日期，yyyy-MM-dd（或 Excel 日期）
|
||
# campid 对应 campaigns.json 里的 campid
|
||
# impression 展示
|
||
# clicks 点击
|
||
# conversions 转化/安装
|
||
# spend 花费(USD)
|
||
# 可选列：location（国家，缺省用 campaigns.json 里的 geo）
|
||
# 渠道/产品/包名 一律由 campid 反查 campaigns.json，Excel 无需再填。
|
||
# 只读取 campid 命中 campaigns.json 的行；其它行忽略。
|
||
# ────────────────────────────────────────────────────────────
|
||
import json, sys, os, datetime
|
||
|
||
HERE = os.path.dirname(os.path.abspath(__file__))
|
||
_camps = json.load(open(os.path.join(HERE, "campaigns.json")))
|
||
CAMPS = {str(r["campid"]): r for r in _camps}
|
||
|
||
DEFAULT_XLSX = os.environ.get(
|
||
"VOYPACE_XLSX",
|
||
"/Users/wangyuan/Documents/SHAREit/运营/Voypace/Voypace补充数据.xlsx",
|
||
)
|
||
|
||
def norm_date(v):
|
||
if v is None: return ""
|
||
if isinstance(v, (datetime.datetime, datetime.date)):
|
||
return v.strftime("%Y-%m-%d")
|
||
s = str(v).strip()
|
||
return s[:10] if s else ""
|
||
|
||
def to_int(v):
|
||
try: return int(float(str(v).replace(",", "").strip() or 0))
|
||
except: return 0
|
||
|
||
def to_float(v):
|
||
try: return float(str(v).replace(",", "").replace("$", "").strip() or 0)
|
||
except: return 0.0
|
||
|
||
def main(path):
|
||
if not os.path.exists(path):
|
||
# 没有补充表就输出空数组，daily_sync 照常跑
|
||
print("[]"); return
|
||
try:
|
||
import openpyxl
|
||
except ImportError:
|
||
sys.stderr.write("openpyxl 未安装，跳过 Excel 补数\n")
|
||
print("[]"); return
|
||
|
||
wb = openpyxl.load_workbook(path, data_only=True)
|
||
ws = wb.worksheets[0]
|
||
rows = list(ws.iter_rows(values_only=True))
|
||
if not rows:
|
||
print("[]"); return
|
||
|
||
header = [str(c).strip().lower() if c is not None else "" for c in rows[0]]
|
||
def col(*names):
|
||
for n in names:
|
||
if n in header: return header.index(n)
|
||
return None
|
||
ci = {
|
||
"date": col("date", "时间", "日期"),
|
||
"campid": col("campid", "adping id", "adping_id"),
|
||
"loc": col("location", "地区", "国家"),
|
||
"imp": col("impression", "impressions", "展示"),
|
||
"clk": col("clicks", "click", "点击"),
|
||
"cv": col("conversions", "install", "installs", "转化", "安装"),
|
||
"spend": col("spend", "花费", "cost"),
|
||
}
|
||
|
||
out = []
|
||
for r in rows[1:]:
|
||
if ci["campid"] is None: break
|
||
cid = str(r[ci["campid"]]).strip() if r[ci["campid"]] is not None else ""
|
||
cid = cid.split(".")[0] # 去掉可能的 .0
|
||
if cid not in CAMPS: continue
|
||
info = CAMPS[cid]
|
||
loc = ""
|
||
if ci["loc"] is not None and r[ci["loc"]] is not None:
|
||
loc = str(r[ci["loc"]]).strip().upper()
|
||
if not loc: loc = str(info.get("geo", "")).upper()
|
||
imp = to_int(r[ci["imp"]]) if ci["imp"] is not None else 0
|
||
clk = to_int(r[ci["clk"]]) if ci["clk"] is not None else 0
|
||
cv = to_int(r[ci["cv"]]) if ci["cv"] is not None else 0
|
||
sp = to_float(r[ci["spend"]]) if ci["spend"] is not None else 0.0
|
||
dt = norm_date(r[ci["date"]]) if ci["date"] is not None else ""
|
||
if not dt: continue
|
||
out.append({
|
||
"date": dt, "channel": info["channel"], "campid": cid,
|
||
"offer_name": f'{cid}_{info["product"]}', "product": info["product"],
|
||
"pkg": info["pkg"], "location": loc, "bid_type": "",
|
||
"impression": imp, "clicks": clk, "conversions": cv,
|
||
"ctr": round(clk/imp, 5) if imp else 0.0,
|
||
"cvr": round(cv/clk, 5) if clk else 0.0,
|
||
"spend": round(sp, 2),
|
||
})
|
||
print(json.dumps(out, ensure_ascii=False))
|
||
|
||
if __name__ == "__main__":
|
||
main(sys.argv[1] if len(sys.argv) > 1 else DEFAULT_XLSX)
|