93 lines
3.9 KiB
Python
93 lines
3.9 KiB
Python
#!/usr/bin/env python3
|
|
# 在 ads-center 上运行：从 report_api.db 取 mintegral/bigo/mediago 三渠道
|
|
# 按 campid 匹配 Voypace 产品，聚合(日期+campid+国家)，输出 JSON 到 stdout。
|
|
# 参数：start_date（含） end_date（含），格式 yyyy-MM-dd
|
|
import sqlite3, json, sys, os
|
|
from collections import defaultdict
|
|
|
|
DB = "/Users/ads-center/Documents/database/report_api.db"
|
|
START = sys.argv[1] if len(sys.argv) > 1 else "2026-07-15"
|
|
END = sys.argv[2] if len(sys.argv) > 2 else "2026-12-31"
|
|
|
|
# campid -> 产品信息（从同目录 campaigns.json 读取）
|
|
HERE = os.path.dirname(os.path.abspath(__file__))
|
|
_camps = json.load(open(os.path.join(HERE, "campaigns.json")))
|
|
CAMPS = {r["campid"]: {"product": r["product"], "pkg": r["pkg"]} for r in _camps}
|
|
|
|
conn = sqlite3.connect(DB)
|
|
conn.row_factory = sqlite3.Row
|
|
|
|
# 聚合容器：key=(date,channel,campid,location) -> 累加指标
|
|
agg = defaultdict(lambda: {"impression":0,"clicks":0,"conversions":0,"spend":0.0})
|
|
meta = {}
|
|
|
|
def campid_of(text):
|
|
t = str(text or "")
|
|
for cid in CAMPS:
|
|
if cid in t:
|
|
return cid
|
|
return None
|
|
|
|
# ---- Mintegral ----
|
|
# 广告单元名(如 91555309_Viu_ID)可能落在 Offer_Name 或 Campaign_Id，两字段都匹配
|
|
like = " OR ".join([f"Offer_Name LIKE '%{cid}%' OR Campaign_Id LIKE '%{cid}%'" for cid in CAMPS])
|
|
sql = f"""SELECT Date d, Offer_Name nm, Campaign_Id cmp, Location loc, Impression imp, Click clk,
|
|
Conversion cv, Spend sp
|
|
FROM mintegral WHERE Date>=? AND Date<=? AND ({like})"""
|
|
for r in conn.execute(sql, (START, END)):
|
|
cid = campid_of(r["nm"]) or campid_of(r["cmp"])
|
|
if not cid: continue
|
|
k = (r["d"], "Mintegral", cid, (r["loc"] or "").upper())
|
|
a = agg[k]
|
|
a["impression"] += int(r["imp"] or 0); a["clicks"] += int(r["clk"] or 0)
|
|
a["conversions"] += int(r["cv"] or 0); a["spend"] += float(r["sp"] or 0)
|
|
meta[k] = (r["nm"] or r["cmp"], CAMPS[cid])
|
|
|
|
# ---- Bigo ----
|
|
like = " OR ".join([f"campaignName LIKE '%{cid}%'" for cid in CAMPS])
|
|
sql = f"""SELECT aggregateTime d, campaignName nm, country loc, tgtPkgName pkg,
|
|
impression imp, click clk, real_result_conversion cv, totalCost cost
|
|
FROM bigo WHERE aggregateTime>=? AND aggregateTime<=? AND ({like})"""
|
|
for r in conn.execute(sql, (START, END)):
|
|
cid = campid_of(r["nm"])
|
|
if not cid: continue
|
|
k = (r["d"], "Bigo", cid, (r["loc"] or "").upper())
|
|
a = agg[k]
|
|
a["impression"] += int(r["imp"] or 0); a["clicks"] += int(r["clk"] or 0)
|
|
a["conversions"] += int(r["cv"] or 0); a["spend"] += float(r["cost"] or 0)/100.0
|
|
meta[k] = (r["nm"], CAMPS[cid])
|
|
|
|
# ---- MediaGo ---- (ad级明细，需聚合；spend = cpc*click 反算)
|
|
like = " OR ".join([f"campaign_name LIKE '%{cid}%'" for cid in CAMPS])
|
|
sql = f"""SELECT date d, campaign_name nm, adset_country loc, vimp imp, click clk,
|
|
cv cvn, cpc
|
|
FROM mediago WHERE date>=? AND date<=? AND ({like})"""
|
|
for r in conn.execute(sql, (START, END)):
|
|
cid = campid_of(r["nm"])
|
|
if not cid: continue
|
|
k = (r["d"], "MediaGo", cid, (r["loc"] or "").upper())
|
|
a = agg[k]
|
|
clk = int(r["clk"] or 0)
|
|
a["impression"] += int(r["imp"] or 0); a["clicks"] += clk
|
|
a["conversions"] += int(r["cvn"] or 0); a["spend"] += float(r["cpc"] or 0) * clk
|
|
meta[k] = (r["nm"], CAMPS[cid])
|
|
|
|
conn.close()
|
|
|
|
out = []
|
|
for k, a in agg.items():
|
|
date, channel, cid, loc = k
|
|
nm, info = meta[k]
|
|
imp, clk, cv = a["impression"], a["clicks"], a["conversions"]
|
|
out.append({
|
|
"date": date, "channel": channel, "campid": cid,
|
|
"offer_name": nm, "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(a["spend"], 2),
|
|
})
|
|
out.sort(key=lambda x: (x["date"], x["channel"], -x["impression"]))
|
|
print(json.dumps(out, ensure_ascii=False))
|