李雨桐

# 分步聚合：避免同一订单多商品行导致金额/转化虚高
import pandas as pd
channel_metric = df.groupby("投放渠道").agg({
    "mql_id": "nunique", "投放成本": "first"
}).reset_index()

converting = df.dropna(subset=["order_id"]).groupby("投放渠道")["mql_id"].nunique().reset_index()

# 按order_id去重，避免同一订单多商品行重复累加
revenue = df.dropna(subset=["order_id"]).drop_duplicates(subset=["order_id"]) \
    .groupby("投放渠道")["payment_value"].sum().reset_index()

# 衍生核心指标
channel_metric["线索转化率"] = channel_metric["成交线索数"] / channel_metric["总线索数"]
channel_metric["CAC"] = channel_metric["渠道总投放成本"] / channel_metric["总线索数"]
channel_metric["成交CAC"] = channel_metric.apply(
    lambda r: r["渠道总投放成本"] / r["成交线索数"] if r["成交线索数"] > 0 else None, axis=1
)
channel_metric["ROI"] = channel_metric["渠道总收入"] / channel_metric["渠道总投放成本"]

# 计算漏斗各层级独立线索量
stage1_total_leads = df["mql_id"].nunique()
stage2_deal_seller = df.dropna(subset=["seller_id"])["mql_id"].nunique()
stage3_create_order = df.dropna(subset=["order_id"])["mql_id"].nunique()
stage4_pay_success = df.dropna(subset=["payment_value"])["mql_id"].nunique()

funnel_data = pd.DataFrame({
    "转化阶段": ["营销线索", "达成合作卖家", "生成订单", "支付完成"],
    "用户数量": [stage1_total_leads, stage2_deal_seller, stage3_create_order, stage4_pay_success]
})
funnel_data["阶段转化率"] = funnel_data["用户数量"].shift(-1) / funnel_data["用户数量"]

# 分渠道漏斗：分别统计每个渠道各级转化用户量
channel_funnel = df.groupby("投放渠道")["mql_id"].nunique().reset_index()
seller_funnel = df.dropna(subset=["seller_id"]).groupby("投放渠道")["mql_id"].nunique().reset_index()
order_funnel = df.dropna(subset=["order_id"]).groupby("投放渠道")["mql_id"].nunique().reset_index()
channel_funnel = channel_funnel.merge(seller_funnel, on="投放渠道", how="left") \
    .merge(order_funnel, on="投放渠道", how="left")

# 去重：同一客户同一订单仅保留一行，消除多商品重复行干扰
df_distinct = df.drop_duplicates(subset=["customer_id", "order_id"])

# 首购同期群 & 间隔月数（年月序号法：兼容所有pandas版本）
df_distinct["首购年月"] = df_distinct.groupby("customer_id")["order_purchase_timestamp"] \
    .transform("min").dt.to_period("M")
df_distinct["首购月序号"] = df_distinct["首购年月"].dt.year * 12 + df_distinct["首购年月"].dt.month
df_distinct["消费月序号"] = df_distinct["消费年月"].dt.year * 12 + df_distinct["消费年月"].dt.month
df_distinct["间隔月数"] = df_distinct["消费月序号"] - df_distinct["首购月序号"]

# pivot 透视表 + 留存率（以首月用户数为分母）
cohort_df = df_distinct.groupby(["首购年月", "间隔月数"])["customer_id"].nunique().reset_index()
cohort_pivot = cohort_df.pivot(index="首购年月", columns="间隔月数", values="customer_id")
retention_pivot = cohort_pivot.div(cohort_pivot.iloc[:, 0], axis=0)

snapshot_date = df["order_purchase_timestamp"].max() + pd.Timedelta(days=1)

rfm = df.groupby("customer_id").agg({
    "order_purchase_timestamp": lambda x: (snapshot_date - x.max()).days,  # R: 最近消费间隔
    "order_id": "nunique",                                       # F: 消费频次
    "payment_value": "sum"                                        # M: 总消费金额
}).reset_index()

# 5档分层打分（R反向打分：越小越近越好）
rfm["R分"] = pd.qcut(rfm["R最近消费间隔"], 5, labels=[5,4,3,2,1])
rfm["F分"] = pd.qcut(rfm["F消费频次"].rank(method="first"), 5, labels=[1,2,3,4,5])
rfm["M分"] = pd.qcut(rfm["M总消费金额"], 5, labels=[1,2,3,4,5])

# 用户价值分层规则引擎
def user_segment(code):
    r, f, m = int(code[0]), int(code[1]), int(code[2])
    if r >= 4 and f >= 4 and m >= 4:  return "高价值核心用户"
    elif r >= 3 and f >= 3 and m >= 3: return "潜力增长用户"
    elif r <= 2 and f >= 3:             return "沉睡高消费用户"
    elif r >= 4 and f <= 2:             return "新客一次性用户"
    else:                              return "低价值流失风险用户"
rfm["用户价值分层"] = rfm["RFM分层码"].apply(user_segment)

# 渠道对比：organic vs paid（观测数据，非严格随机实验）
group_a = df[df["投放渠道"]=="organic_search"]["payment_value"].dropna()
group_b = df[df["投放渠道"]=="paid_search"]["payment_value"].dropna()

compare_summary = pd.DataFrame({
    "组别": ["自然流量(organic)", "付费搜索(paid)"],
    "样本量": [len(group_a), len(group_b)],
    "客单价均值": [group_a.mean(), group_b.mean()],
    "客单价中位数": [group_a.median(), group_b.median()]
})

from scipy import stats
# Welch's t-test（不假设方差齐性）
t_stat, p_value = stats.ttest_ind(group_a, group_b, equal_var=False)
if p_value < 0.05:
    print("统计显著——但非随机实验，不能因果归因")

# 前置文件校验，缺失则明确提示
for path, name in [(path_raw, "全线索宽表"), (path_clean, "清洗宽表"), (path_tag, "用户标签表")]:
    if not os.path.exists(path):
        raise FileNotFoundError(f"缺文件: {name}")

# 看板1：渠道运营（去重防行爆炸 + 多表merge）
ch_raw = df_raw.dropna(subset=["order_id"]).drop_duplicates(subset=["order_id"])
channel_dashboard = df_raw.groupby("投放渠道")["mql_id"].nunique().reset_index()
ch_revenue = ch_raw.groupby("投放渠道")["payment_value"].sum().reset_index()
channel_dashboard = channel_dashboard.merge(ch_revenue, on="投放渠道", how="left")
channel_dashboard["平均客单价"] = channel_dashboard.apply(
    lambda r: r["总营收"] / r["订单量"] if r["订单量"] > 0 else 0, axis=1
)

# 看板2：商品品类（聚合 + 一键导出）
product_dashboard = df_clean.groupby("product_category_name_english").agg({
    "order_id": "nunique", "payment_value": "sum", "review_score": "mean"
}).reset_index()

# 批量导出4份PowerBI可直接导入的CSV
channel_dashboard.to_csv("PowerBI_渠道看板.csv", index=False, encoding="utf-8-sig")
product_dashboard.to_csv("PowerBI_商品品类看板.csv", index=False, encoding="utf-8-sig")