线上推广优化：从零到一搭建自动化投放与效果追踪系统

一、系统架构与核心组件

本系统旨在通过自动化工具替代人工重复操作，并建立全链路数据追踪，核心由三个部分组成：自动化投放脚本、效果追踪数据管道、可视化分析看板。

系统架构图逻辑： 广告平台API -> 自动化脚本 -> 原始数据落库 -> ETL清洗 -> 聚合数据表 -> BI看板。所有组件均部署在可免费试用的云服务器上。

1.1 环境准备与必要账号

你需要准备以下账号与工具，这是后续所有操作的基础：

• 云服务器： 注册阿里云或腾讯云，选择最低配置的按量付费ECS（如1核1G），系统选择Ubuntu 20.04。创建后获取公网IP地址。
• 广告平台开发者权限： 以巨量引擎为例，进入巨量引擎开放平台，创建“开发者”身份，并获取已授权广告主账户的Access Token。
• 数据库： 注册一个Aiven的免费PostgreSQL数据库实例，获取连接字符串。

二、自动化投放脚本部署

此脚本负责定时从广告平台拉取数据，并执行简单的规则化调整。

2.1 服务器初始化与脚本部署

使用终端连接你的云服务器。

步骤1：连接服务器并安装基础环境。

在终端中执行以下命令：

``` ssh root@你的服务器公网IP apt-get update && apt-get install -y python3-pip cron git pip3 install requests pandas schedule psycopg2-binary ```

步骤2：克隆脚本仓库并配置。

我们使用一个开源的、维护良好的脚本作为基础。

``` cd /root git clone https://github.com/nick3499/ads_auto_optimizer.git cd ads_auto_optimizer ```

步骤3：编辑配置文件 `config.yaml`。

使用 `nano config.yaml` 命令打开文件，将其内容完全替换为以下配置，并填入你的真实信息：

``` database: host: “你的Aiven数据库主机地址” port: “你的Aiven数据库端口，通常为5432” name: “你的数据库名” user: “你的数据库用户名” password: “你的数据库密码” platforms: ocean_engine: access_token: “你的巨量引擎Access Token” ad_account_id: “你的广告主ID” rules: - name: “pause_low_ctr” enabled: true condition: “click_cnt > 100 AND ctr < 0.005” action: “update_ad_status” params: {"status": “DISABLE”} ```

按`Ctrl+O`保存，按`Ctrl+X`退出编辑器。

2.2 设置定时任务与日志

步骤1：测试脚本能否正常运行。

执行以下命令，观察是否报错，并检查数据库是否出现`ads_raw_data`表。

``` python3 main.py --task fetch_data ```

步骤2：配置系统级定时任务Crontab。

执行 `crontab -e`，在文件末尾添加以下两行，表示每天9点和21点各执行一次数据拉取与规则应用：

``` 0 9 cd /root/ads_auto_optimizer && /usr/bin/python3 main.py --task fetch_data >> /var/log/ads_fetch.log 2>&1 0 21 cd /root/ads_auto_optimizer && /usr/bin/python3 main.py --task apply_rules >> /var/log/ads_rule.log 2>&1 ```

三、构建效果追踪数据管道

原始数据需要清洗、关联才能用于分析。我们在数据库内完成这一步。

3.1 创建数据清洗与聚合SQL

使用任何能连接PostgreSQL的工具（如DBeaver免费版），执行以下SQL语句，创建清洗后的中间表。

步骤1：创建清洗视图。

``` CREATE VIEW ads_cleaned AS SELECT ad_id, campaign_id, DATE(stat_time) as stat_date, SUM(impression) as impressions, SUM(click) as clicks, SUM(spend) as spend, SUM(conversion) as conversions FROM ads_raw_data WHERE stat_time >= CURRENT_DATE - INTERVAL '30 days' GROUP BY ad_id, campaign_id, DATE(stat_time); ```

步骤2：创建核心聚合表。

此表包含关键绩效指标，是看板的数据源。

``` CREATE TABLE ads_daily_kpi AS SELECT stat_date, campaign_id, ad_id, impressions, clicks, spend, conversions, ROUND(clicks 1.0 / NULLIF(impressions, 0), 4) as ctr, ROUND(spend 1.0 / NULLIF(clicks, 0), 2) as cpc, ROUND(spend 1.0 / NULLIF(conversions, 0), 2) as cpa FROM ads_cleaned; ```

3.2 设置ETL自动更新

在数据库中创建一个存储过程，并设置定时触发器，让`ads_daily_kpi`表每日自动更新。

步骤1：创建更新存储过程。

``` CREATE OR REPLACE PROCEDURE refresh_ads_kpi() LANGUAGE plpgsql AS $$ BEGIN DELETE FROM ads_daily_kpi WHERE stat_date >= CURRENT_DATE - INTERVAL '3 days'; INSERT INTO ads_daily_kpi SELECT stat_date, campaign_id, ad_id, impressions, clicks, spend, conversions, ROUND(clicks 1.0 / NULLIF(impressions, 0), 4) as ctr, ROUND(spend 1.0 / NULLIF(clicks, 0), 2) as cpc, ROUND(spend 1.0 / NULLIF(conversions, 0), 2) as cpa FROM ads_cleaned WHERE stat_date >= CURRENT_DATE - INTERVAL '3 days'; END; $$; ```

步骤2：创建定时作业。

如果你使用的Aiven数据库，在控制台找到“Cron Jobs”或“PGBouncer”类似功能，添加一个新任务：

• 调度表达式：`0 2 ` （表示每天凌晨2点执行）
• 执行命令：`CALL refresh_ads_kpi();`

四、配置可视化分析看板

使用Metabase开源BI工具，快速将数据图表化。

4.1 安装并连接Metabase

步骤1：在云服务器上使用Docker安装Metabase。

回到服务器终端，执行：

``` docker pull metabase/metabase:latest docker run -d -p 3000:3000 \ -e “MB_DB_TYPE=postgres” \ -e “MB_DB_DBNAME=你的数据库名” \ -e “MB_DB_PORT=你的数据库端口” \ -e “MB_DB_USER=你的数据库用户名” \ -e “MB_DB_PASS=你的数据库密码” \ -e “MB_DB_HOST=你的数据库主机地址” \ --name metabase metabase/metabase ```

步骤2：完成初始配置。

在浏览器中访问 `http://你的服务器公网IP:3000`，按照界面提示创建管理员账户。在“添加数据库”环节，系统会自动填入之前Docker命令中设置的环境变量，直接测试连接即可。

4.2 创建核心监控看板

在Metabase中，依次创建以下问题（图表），并将其添加到一个名为“推广效果监控”的仪表盘中。

图表1：每日消耗与转化趋势（双轴折线图）

• 数据源：选择 `ads_daily_kpi` 表。
• 选择“可视化” -> “折线图”。
• X轴：`stat_date`。
• Y轴：添加“系列1”为 `SUM(spend)`， “系列2”为 `SUM(conversions)`。
• 在“Y轴”设置中，为“系列2”选择“右侧Y轴”。

图表2：计划层级CPA排名（条形图）

• 新建问题，选择“原生查询”。
• 在SQL编辑器中输入：

``` SELECT campaign_id, SUM(spend) as total_spend, SUM(conversions) as total_conv, ROUND(SUM(spend) 1.0 / NULLIF(SUM(conversions), 0), 2) as cpa FROM ads_daily_kpi WHERE stat_date >= CURRENT_DATE - INTERVAL '7 days' GROUP BY campaign_id HAVING SUM(spend) > 0 ORDER BY cpa DESC LIMIT 10; ```

• 点击“可视化”，选择“条形图”。X轴选择 `cpa`，Y轴选择 `campaign_id`。

图表3：实时关键指标摘要（仪表盘数字）

• 新建三个“标量”问题，分别查询昨日数据：
• 问题1 SQL：`SELECT SUM(spend) FROM ads_daily_kpi WHERE stat_date = CURRENT_DATE - 1`， 重命名为“昨日消耗”。
• 问题2 SQL：`SELECT ROUND(SUM(spend)1.0/NULLIF(SUM(conversions),0),2) FROM ads_daily_kpi WHERE stat_date = CURRENT_DATE - 1`， 重命名为“昨日CPA”。
• 问题3 SQL：`SELECT ROUND(SUM(clicks)1.0/NULLIF(SUM(impressions),0),4) FROM ads_daily_kpi WHERE stat_date = CURRENT_DATE - 1`， 重命名为“昨日CTR”。

将以上三个问题添加到仪表盘，并排显示。

五、系统验证与日常使用

完成以上所有步骤后，进行最终验证。

验证清单：

• 自动化脚本： 执行 `cat /var/log/ads_fetch.log` 查看最近一次拉取日志，确认无报错。
• 数据管道： 在数据库客户端执行 `SELECT FROM ads_daily_kpi ORDER BY stat_date DESC LIMIT 5;`，确认有最近日期的数据。
• 可视化看板： 访问Metabase仪表盘，确认所有图表数据正常更新，且日期为最新。

日常操作流程：

每日上午，打开Metabase“推广效果监控”仪表盘，重点关注：

1. “昨日CPA”是否超过阈值。
2. “计划层级CPA排名”中，排名第一的高成本计划，考虑是否需在`config.yaml`的`rules`部分添加针对该计划的暂停规则。
3. 观察“每日消耗与转化趋势”，判断整体走势是否健康。

如需调整自动化规则，只需修改服务器上 `/root/ads_auto_optimizer/config.yaml` 文件中的 `rules` 部分，保存即可，脚本会在下次定时任务时生效。