用 n8n 自动分析 Facebook 广告并推送 AI 洞察到 Google 表格

输入 Facebook 广告账户数据 → 经 Gemini AI 按基准分类评估 → 输出带建议的 Google Sheets 报告

给谁

电商运营、广告投放代理、Meta 广告买手

你会得到

自动拉取最近 28 天广告数据，按账户基准 KPI 用 Gemini 为每条创意打分（HELL YES / YES / MAYBE / NOT REALLY / WE WASTED MONEY / INSUFFICIENT DATA），并写入 Google Sheets

你需要

n8n · google-sheets · nocodb

步骤

1. 配置凭证与令牌管理

   在 n8n 中配置所需凭证：Google Gemini（googlePalmApi）、Google Sheets（googleSheetsOAuth2Api）。模板默认使用 NocoDB 存储 Facebook 长期访问令牌；在「Getting Long-Term Token」和「Updating Token」节点中填入你的 NocoDB 项目 ID、表名和 API Token，或整体替换为你偏好的凭证管理方式。

2. 获取 Facebook 广告数据

   在「Getting Data For the Past 28 Days…」HTTP Request 节点中，将 URL 里的 act_XXXXXX 替换为你的 Facebook 广告账户 ID。该节点会调用 Facebook Graph API v22.0，拉取最近 28 天、广告级别（level=ad）的数据，包含花费、展示、点击、actions、action_values 等字段。

3. 处理、清洗与过滤数据

   「Splitting Out In Table Format」代码节点解析 Facebook 返回的原始数据，按优先顺序提取加购、发起结账、购买次数与购买金额；「Filtering Only For Sales Campaigns」过滤出 objective=OUTCOME_SALES 的销售类广告；「Aggregate Metrics by Ad Creative」按广告创意聚合并计算 CTR、CPC、CPM、ROAS、单次购买成本等衍生指标；最后「Sorting Based on Spends」按总花费降序排列。

4. 计算广告与账户基准 KPI

   工作流在此处并行分叉：上方路径继续处理单个创意数据；下方路径由「Calculate Account Benchmarks」代码节点汇总全部销售广告，计算账户整体基准（如平均 ROAS、平均单次购买成本）。随后「Stringify Everything」和「Stringify Benchmark Data」将两边数据转为 JSON 字符串，经「Combining Ad Data and Benchmarking Data」合并，再经「Combine Ad & Benchmark Data for LLM」聚合成单一输入项，为 AI 分析做准备。

5. AI 分析与分类

   「Senior Facebook Ads Media Buyer」LLM 链节点将聚合后的广告数据与账户基准一起发送给 Google Gemini。模型按花费阈值和基准对比，为每条创意给出分类、理由与建议。输出经「Structured Output Parser」约束为固定 JSON 数组格式，再经「Split Out」拆分为单行记录。

   运行提示词 · 来源原文。n8n 节点 'Senior Facebook Ads Media Buyer' 的 text 参数；作为 LLM 链的系统/用户指令

   复制提示词

   =**Role:** You are a Senior Facebook Ads Media Buyer with extensive experience in e-commerce campaign optimization. Your primary goal is to maximize Return on Ad Spend (ROAS) and overall profitability by identifying high-impact ad creatives and categorizing their performance.
   
   **Context:**
   You are analyzing ad creative performance for an e-commerce client. You have been provided with two sets of data for the past 14 days:
   1.  **"Ad Creative Performance Data"**: A JSON string representing an array of objects. Each object details a unique ad creative and its performance metrics (including `total_spend`, `total_purchases`, `total_purchase_value`).
   2.  **"Overall Account Benchmark Data"**: A JSON string representing a single object. This object contains the aggregated performance and KPIs for all ads in the account over the same period, serving as crucial benchmark values.
   
   **Task:**
   Your task is to rigorously evaluate each ad creative from the "Ad Creative Performance Data" against the "Overall Account Benchmark Data." **For every ad creative you evaluate, you MUST compare its key calculated metrics (CTR, CPC, CPM, Cost Per Add To Cart, Cost Per Checkout, Cost Per Purchase, Conversion Rate, ROAS, Average Order Value) directly against the corresponding values in the benchmark data.**
   
   Based on this comparison and the spend rules below, categorize the performance of **each ad creative**.
   
   **Performance Categorization Rules & Spend Thresholds:**
   
   1.  **Spend Significance:**
       *   Any ad creative with a `total_spend` of **less than $50** CANNOT be categorized as "HELL YES", "YES", or even "MAYBE", regardless of its other metrics. At best, it can be "NOT REALLY" if ratios are poor, or "INSUFFICIENT DATA/SPEND" if ratios look promising but spend is too low for a definitive positive categorization.
       *   To be considered for "HELL YES" or "YES" categories, an ad creative **MUST have a `total_spend` of $100 or more.**
   
   2.  **Performance Categories (evaluate against benchmarks AFTER spend rules):**
       *   **"HELL YES":** Significant spend (>= $100) AND dramatically outperforms benchmarks across ROAS, CVR, and Cost Per Purchase. Clear 80/20 driver.
       *   **"YES":** Significant spend (>= $100) AND clearly outperforms benchmarks on ROAS, CVR, and Cost Per Purchase. Reliably contributing.
       *   **"MAYBE":** Shows promise or mixed results. Spend >= $50. Marginally outperforms benchmarks, or strong on some key metrics but not others. Or, spend < $100 but > $50 with very strong ratios. Requires observation/optimization.
       *   **"NOT REALLY":** Underperforming. Metrics generally at/below benchmarks. Or, spend < $50 with unpromising ratios. Not a clear winner.
       *   **"WE WASTED MONEY":** Performing very poorly. Likely spent > $30-$50 (use judgment) with significantly worse metrics than benchmarks (very low ROAS, very high CPP). Pause candidate.
       *   **"INSUFFICIENT DATA/SPEND":** Spend < $50. Ratios might look okay/good, but not enough data for confident positive categorization ("MAYBE", "YES", "HELL YES").
   
   **Input Data:**
   
   **1. Ad Creative Performance Data (JSON String - LLM needs to parse this):**
   
   {{ $json.data[0].all_ads_data_string }}
   
   2. Overall Account Benchmark Data (JSON String - LLM needs to parse this):
   {{ $json.data[1].benchmarkData }}

6. 输出到 Google Sheets

   「Sending Raw Data To A Google Sheet」将清洗后的原始指标追加到 Google Sheets；「Updating Ad Insights Into Google Sheets」则按 ad_id 匹配，把 AI 生成的分类（Best Performing Ad）、理由（Justification）和建议（Recommendation）更新到对应行。两个节点都需要替换 Document ID 为你的 Google Sheet ID，并选择正确的工作表名称。

你正在搭建一个 n8n 工作流，用于自动分析 Facebook 广告表现并推送 AI 洞察到 Google Sheets。请先确认以下前置条件：1）n8n 实例已运行；2）已配置 googlePalmApi、googleSheetsOAuth2Api、nocoDbApiToken 三类凭证（或准备好自己的凭证替代方案）；3）拥有 Facebook 广告账户 ID 和目标 Google Sheet ID。然后按步骤导入模板，替换 act_XXXXXX 和 XXXX 等占位符，最后点击「Test workflow」验证。