Auto-Analyze Facebook Ads with n8n and Push AI Insights to Google Sheets

Input Facebook ad account data → evaluate against benchmarks with Gemini AI → output categorized report with recommendations to Google Sheets

Who

E-commerce operators, ad agencies, Meta media buyers

You get

Automatically pull last-28-day ad data, score each creative against account KPI benchmarks using Gemini (HELL YES / YES / MAYBE / NOT REALLY / WE WASTED MONEY / INSUFFICIENT DATA), and write results to Google Sheets

You need

n8n · google-sheets · nocodb

Steps

1. Configure credentials and token management

   Set up required credentials in n8n: Google Gemini (googlePalmApi), Google Sheets (googleSheetsOAuth2Api). The template defaults to NocoDB for storing the Facebook long-term access token; enter your NocoDB project ID, table name, and API token in the 'Getting Long-Term Token' and 'Updating Token' nodes, or replace the entire section with your preferred credential management.

2. Fetch Facebook ad data

   In the 'Getting Data For the Past 28 Days…' HTTP Request node, replace act_XXXXXX in the URL with your Facebook Ad Account ID. This node calls Facebook Graph API v22.0 to pull the last 28 days of ad-level data, including spend, impressions, clicks, actions, and action_values.

3. Process, clean, and filter data

   The 'Splitting Out In Table Format' code node parses raw Facebook response data, extracting add-to-cart, checkout-initiated, purchase counts and purchase values using a preferred action_type priority order. 'Filtering Only For Sales Campaigns' keeps only objective=OUTCOME_SALES. 'Aggregate Metrics by Ad Creative' aggregates by ad name (with ad_id) and calculates derived KPIs such as CTR, CPC, CPM, ROAS, cost per purchase. Finally, 'Sorting Based on Spends' sorts descending by total_spend.

4. Calculate ad and account benchmark KPIs

   The workflow splits in parallel here: the top path continues with individual creative data; the bottom path uses the 'Calculate Account Benchmarks' code node to aggregate all sales ads and compute account-wide benchmarks (e.g., average ROAS, average cost per purchase). Then 'Stringify Everything' and 'Stringify Benchmark Data' convert both sides to JSON strings, merge via 'Combining Ad Data and Benchmarking Data', and aggregate via 'Combine Ad & Benchmark Data for LLM' into a single input item ready for AI analysis.

5. AI analysis and categorization

   The 'Senior Facebook Ads Media Buyer' LLM chain node sends the aggregated ad data and account benchmark to Google Gemini. The model categorizes each creative against spend thresholds and benchmarks, returning a classification, justification, and recommendation. Output is constrained by the 'Structured Output Parser' to a fixed JSON array schema, then split into individual rows via 'Split Out'.

   Execution prompt · Source text。n8n node 'Senior Facebook Ads Media Buyer' text parameter; used as LLM chain system/user instruction

   Copy prompt

   =**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. Output to Google Sheets

   'Sending Raw Data To A Google Sheet' appends cleaned raw metrics to Google Sheets. 'Updating Ad Insights Into Google Sheets' matches rows by ad_id and updates with AI-generated classification (Best Performing Ad), justification (Justification), and recommendation (Recommendation). Both nodes require replacing the Document ID with your Google Sheet ID and selecting the correct sheet name.

You are setting up an n8n workflow to automatically analyze Facebook ad performance and push AI insights to Google Sheets. Please confirm the following prerequisites first: 1) n8n instance is running; 2) credentials googlePalmApi, googleSheetsOAuth2Api, and nocoDbApiToken are configured (or you have an alternative credential store ready); 3) you have your Facebook Ad Account ID and target Google Sheet ID. Then import the template, replace placeholders like act_XXXXXX and XXXX, and click 'Test workflow' to verify.