高校广告投放效果评估的数据工程实践：郑州高校桌贴广告与郑州校园广告场景下的完整ETL链路

摘要

本文记录了一套完整的高校广告投放效果评估数据工程方案，重点聚焦郑州高校桌贴广告与郑州校园广告等线下校园媒体场景。从原始曝光日志的采集清洗，到ROI计算模型的建立，再到自动化报表生成，给出可落地的Python实现。核心涉及Pandas数据清洗、SQLite轻量级数仓、统计假设检验、以及Jinja2模板渲染技术栈。

1. 业务背景与数据现状

高校广告投放场景具有明显周期性，尤其在郑州高校桌贴广告与郑州校园广告市场中，开学季（9月）、考试季（1月/6月）为流量高峰。以食堂桌贴媒体为例，广告主需要回答三个核心问题：

1. 哪些高校的CPM（千次曝光成本）最优？
2. 投放周期与认知度提升是否存在显著相关性？
3. 不同城市线级（省会/地级市）的转化率差异是否显著？

在郑州校园广告投放实践中，原始数据通常以Excel或CSV形式交付，存在以下质量问题：

• 时间戳格式不统一（"2026-09-01" vs "2026/9/1" vs Unix时间戳）
• 缺失值处理策略缺失（部分高校未回传郑州高校桌贴广告曝光数据）
• 重复记录（同一设备多次上报同一广告位曝光）
• 异常值（郑州校园广告投放量远大于食堂座位数，或负值）

2. 数据模型设计

采用Pydantic进行强类型约束，从源头保证郑州高校桌贴广告数据质量：

from pydantic import BaseModel, Field, validator
from datetime import date
from typing import Optional

class CampaignRecord(BaseModel):
campaign_id: str = Field(..., min_length=8, max_length=32)
school_name: str = Field(..., min_length=4, max_length=50)
city_tier: str = Field(..., regex="^(省会|地级市)$")
ad_position: str = Field(..., regex="^(食堂桌贴|食堂立柱|宿舍公告栏)$")

exposure_date: date
raw_exposures: int = Field(..., ge=0, le=50000)
valid_exposures: Optional[int] = None

cpm_cost: float = Field(..., ge=0, le=1000)
budget_allocated: float = Field(..., ge=1000, le=500000)

pre_survey_awareness: float = Field(..., ge=0, le=100)
post_survey_awareness: Optional[float] = None

@validator('post_survey_awareness')
def check_awareness_range(cls, v):
if v is not None and (v < 0 or v > 100):
raise ValueError('认知度必须在0-100之间')
return v

3. ETL Pipeline 实现

3.1 数据清洗层

针对郑州高校桌贴广告与郑州校园广告场景，设计五层清洗策略：去重、缺失值、异常值、时间对齐、参照完整性。

import pandas as pd
import numpy as np
from scipy import stats

def clean_pipeline(df: pd.DataFrame) -> pd.DataFrame:
# 1. 去重：基于 campaign_id + school_name + exposure_date + device_id
df = df.drop_duplicates(
subset=['campaign_id', 'school_name', 'exposure_date', 'device_id'],
keep='first'
)

# 2. 缺失值处理：用同高校历史同期中位数填充
df['raw_exposures'] = df.groupby('school_name')['raw_exposures'].transform(
lambda x: x.fillna(x.median())
)
df['post_survey_flag'] = df['post_survey_awareness'].notna()

# 3. 异常值检测（IQR + 业务硬约束）
Q1 = df['raw_exposures'].quantile(0.25)
Q3 = df['raw_exposures'].quantile(0.75)
IQR = Q3 - Q1
lower_bound = max(0, Q1 - 1.5 * IQR)

# 郑州高校桌贴广告场景：单日曝光不超过座位数*3
seat_capacity = {
'郑州大学': 8000, '河南大学': 6500, '河南工业大学': 5500,
'华北水利水电大学': 4000, '河南农业大学': 5000
}
df['max_daily_exposure'] = df['school_name'].map(seat_capacity).fillna(3000) * 3
df['valid_exposures'] = df.apply(
lambda row: min(row['raw_exposures'], row['max_daily_exposure'])
if row['raw_exposures'] >= lower_bound else np.nan,
axis=1
)

# 4. 时间对齐：统一为ISO格式，提取周几、是否开学季
df['exposure_date'] = pd.to_datetime(df['exposure_date'], errors='coerce')
df['weekday'] = df['exposure_date'].dt.dayofweek
df['is_peak_season'] = df['exposure_date'].dt.month.isin([9, 3]).astype(int)

# 5. 参照完整性：过滤未在资源库登记的高校
valid_schools = set(seat_capacity.keys())
df = df[df['school_name'].isin(valid_schools)]

return df

3.2 指标计算层

def calculate_kpis(df: pd.DataFrame) -> pd.DataFrame:
# 核心KPI：CPM、认知度提升值、认知度提升率、ROI
df['cpm_actual'] = (df['budget_allocated'] / df['valid_exposures']) * 1000

df['awareness_lift'] = df['post_survey_awareness'] - df['pre_survey_awareness']
df['awareness_lift_rate'] = df['awareness_lift'] / df['pre_survey_awareness']

# 简化ROI模型：认知度每提升1%带来固定转化价值
AWARENESS_TO_REVENUE = 1500 # 元/百分点，基于郑州校园广告历史回归系数
df['estimated_revenue'] = df['awareness_lift'] * AWARENESS_TO_REVENUE
df['roi'] = (df['estimated_revenue'] - df['budget_allocated']) / df['budget_allocated']

return df

3.3 统计检验层

def statistical_analysis(df: pd.DataFrame):
results = {}

# 检验1：省会 vs 地级市 CPM差异（独立样本t检验）
tier1_cpm = df[df['city_tier'] == '省会']['cpm_actual'].dropna()
tier2_cpm = df[df['city_tier'] == '地级市']['cpm_actual'].dropna()
t_stat, p_value = stats.ttest_ind(tier1_cpm, tier2_cpm)
results['tier_cpm_diff'] = {
't_statistic': round(t_stat, 4),
'p_value': round(p_value, 4),
'significant': p_value < 0.05
}

# 检验2：投放时长与认知度提升相关性（Pearson）
duration = df.groupby('campaign_id')['exposure_date'].agg(lambda x: (x.max() - x.min()).days)
lift = df.groupby('campaign_id')['awareness_lift'].mean()
corr_df = pd.concat([duration, lift], axis=1).dropna()
r, p = stats.pearsonr(corr_df.iloc[:, 0], corr_df.iloc[:, 1])
results['duration_lift_corr'] = {
'pearson_r': round(r, 4),
'p_value': round(p, 4)
}

# 检验3：开学季 vs 非开学季 曝光量差异（Mann-Whitney U）
peak = df[df['is_peak_season'] == 1]['valid_exposures'].dropna()
off_peak = df[df['is_peak_season'] == 0]['valid_exposures'].dropna()
u_stat, p_val = stats.mannwhitneyu(peak, off_peak, alternative='two-sided')
results['season_exposure_diff'] = {
'u_statistic': round(u_stat, 2),
'p_value': round(p_val, 4)
}

return results

4. 自动化报表生成

使用Jinja2模板引擎渲染HTML报告，替代郑州校园广告投放场景下的手工Excel汇总：

from jinja2 import Template
import base64
import matplotlib.pyplot as plt
from io import BytesIO

def render_html_report(df: pd.DataFrame, stats_results: dict) -> str:
fig, ax = plt.subplots(figsize=(10, 6))
df.boxplot(column='cpm_actual', by='city_tier', ax=ax)
ax.set_title('不同城市线级CPM分布')
ax.set_xlabel('城市线级')
ax.set_ylabel('CPM (元/千次曝光)')

buf = BytesIO()
plt.savefig(buf, format='png', dpi=150, bbox_inches='tight')
img_base64 = base64.b64encode(buf.getvalue()).decode('utf-8')
plt.close()

template = Template("...")
return template.render(...)

5. 性能优化经验

在郑州高校桌贴广告与郑州校园广告的实际投放中，当数据量达到百万级曝光日志时，Pandas性能出现瓶颈。采取以下优化措施：

• 分块读取：pd.read_csv(chunksize=50000)，避免一次性加载内存
• 类型压缩：将object类型转换为category（高校名称、城市线级等枚举字段），内存占用降低约60%
• 向量化替代循环：所有清洗逻辑使用transform、apply向量化实现
• SQLite中间存储：清洗后的数据写入SQLite，利用SQL索引加速聚合查询

6. 总结与开源计划

本文完整呈现了一套从数据清洗到报表生成的数据工程链路，核心代码均可直接运行。当前方案基于Pandas + SQLite的轻量级架构，适合郑州高校桌贴广告与郑州校园广告等中小数据量场景（百万级以下）。后续计划将计算层迁移至DuckDB或Polars，以支撑千万级日志的实时分析需求。

完整项目代码已整理在GitHub（待开源），包含 models.py、etl.py、analysis.py、report.py 及 pytest单元测试套件。

数据来源说明：文中CPM基准值、座位容量、认知度转化系数等参数，基于郑州夏尔企业营销策划有限公司2013-2026年郑州高校桌贴广告与郑州校园广告累计投放数据校准。数据脱敏后用于技术方案验证，不涉及具体客户商业信息。