January 22, 2026

SerpAPI to Google Sheets, SEO gaps logged clean

Lisa Granqvist Partner Workflow Automation Expert

Get a free AI assessment → ⬇️ Use template

Keyword research sounds simple until you’re 40 tabs deep, copying snippets into a sheet, and still not sure what “intent” Google is rewarding today. Then the client asks for “the gaps” and suddenly you’re rewriting the same report you wrote last week.

This SerpAPI Sheets automation hits SEO strategists first, but agency owners and in-house content leads feel it too. You want one keyword in, a clear opportunity brief out, and a clean log you can reuse later.

This workflow pulls live Google SERP data (India market), has AI summarize the competitive reality, emails a client-ready report via Gmail, and appends the structured results to Google Sheets for tracking and audits.

How This Automation Works

See how this solves the problem:

n8n Workflow Template: SerpAPI to Google Sheets, SEO gaps logged clean

Click to explore

flowchart LR

    subgraph sg0["Parse Market Analysi Flow"]
        direction LR
        n0@{ icon: "mdi:brain", form: "rounded", label: "LLM Engine for Market Intell..", pos: "b", h: 48 }
        n1@{ icon: "mdi:robot", form: "rounded", label: "Parse Market Analysis Output..", pos: "b", h: 48 }
        n4@{ icon: "mdi:swap-vertical", form: "rounded", label: "Extract Keyword from Request..", pos: "b", h: 48 }
        n5@{ icon: "mdi:web", form: "rounded", label: "Run Google SERP Search for K..", pos: "b", h: 48 }
        n6["<div style='background:#f5f5f5;padding:10px;border-radius:8px;display:inline-block;border:1px solid #e0e0e0'><img src='https://flowpast.com/wp-content/uploads/n8n-workflow-icons/code.svg' width='40' height='40' /></div><br/>Normalize SERP Results into .."]
        n7@{ icon: "mdi:robot", form: "rounded", label: "Analyze Keyword SEO Opportun..", pos: "b", h: 48 }
        n8["<div style='background:#f5f5f5;padding:10px;border-radius:8px;display:inline-block;border:1px solid #e0e0e0'><img src='https://flowpast.com/wp-content/uploads/n8n-workflow-icons/webhook.dark.svg' width='40' height='40' /></div><br/>Receive SEO Keyword Analysis.."]
        n9["<div style='background:#f5f5f5;padding:10px;border-radius:8px;display:inline-block;border:1px solid #e0e0e0'><img src='https://flowpast.com/wp-content/uploads/n8n-workflow-icons/code.svg' width='40' height='40' /></div><br/>Flatten AI Output for Downst.."]
        n10["<div style='background:#f5f5f5;padding:10px;border-radius:8px;display:inline-block;border:1px solid #e0e0e0'><img src='https://flowpast.com/wp-content/uploads/n8n-workflow-icons/code.svg' width='40' height='40' /></div><br/>Map SEO Fields for UI & Repo.."]
        n11@{ icon: "mdi:robot", form: "rounded", label: "Generate Client-Ready SEO In..", pos: "b", h: 48 }
        n12@{ icon: "mdi:brain", form: "rounded", label: "LLM Engine for Insight Forma..", pos: "b", h: 48 }
        n13@{ icon: "mdi:message-outline", form: "rounded", label: "Send SEO Opportunity Report ..", pos: "b", h: 48 }
        n14@{ icon: "mdi:database", form: "rounded", label: "Log SEO Analysis Result to G..", pos: "b", h: 48 }
        n12 -.-> n11
        n10 --> n11
        n10 --> n14
        n1 -.-> n7
        n4 --> n5
        n9 --> n10
        n8 --> n4
        n7 --> n9
        n6 --> n7
        n5 --> n6
        n0 -.-> n7
        n11 --> n13
    end

    subgraph sg1["Error Handler Flow"]
        direction LR
        n2@{ icon: "mdi:play-circle", form: "rounded", label: "Error Handler Trigger", pos: "b", h: 48 }
        n3["<div style='background:#f5f5f5;padding:10px;border-radius:8px;display:inline-block;border:1px solid #e0e0e0'><img src='https://flowpast.com/wp-content/uploads/n8n-workflow-icons/slack.svg' width='40' height='40' /></div><br/>Slack: Send Error Alert"]
        n2 --> n3
    end

    %% Styling
    classDef trigger fill:#e8f5e9,stroke:#388e3c,stroke-width:2px
    classDef ai fill:#e3f2fd,stroke:#1976d2,stroke-width:2px
    classDef aiModel fill:#e8eaf6,stroke:#3f51b5,stroke-width:2px
    classDef decision fill:#fff8e1,stroke:#f9a825,stroke-width:2px
    classDef database fill:#fce4ec,stroke:#c2185b,stroke-width:2px
    classDef api fill:#fff3e0,stroke:#e65100,stroke-width:2px
    classDef code fill:#f3e5f5,stroke:#7b1fa2,stroke-width:2px
    classDef disabled stroke-dasharray: 5 5,opacity: 0.5
    class n2 trigger
    class n1,n7,n11 ai
    class n0,n12 aiModel
    class n14 database
    class n5,n8 api
    class n6,n9,n10 code
    classDef customIcon fill:none,stroke:none
    class n6,n8,n9,n10,n3 customIcon

The Challenge: Turning SERPs Into Client-Ready SEO Insights

Live SERPs are the truth, but they’re messy. A single keyword can trigger organic listings, videos, “related searches,” and patterns that only show up when you zoom out. Manually, you’re stuck doing three jobs at once: collecting the data, interpreting it, and packaging it into something a client (or your boss) will actually trust. And if you don’t log what you found, you’ll re-research the same keyword in a month and pretend you’re not annoyed about it. Honestly, that’s the worst part.

It adds up fast. Here’s where it breaks down in real life.

You spend about an hour per keyword just collecting titles, snippets, and competitor angles from the top results.
Intent gets guessed instead of proven, so content briefs drift and revisions multiply.
Reporting becomes a copy-paste ritual, which means formatting errors and missing context are common.
No historical log exists, so your “strategy” is hard to audit when priorities change.

The Fix: Live SERP Pull + AI Gap Analysis + Sheets Logging

This workflow gives you a repeatable pipeline for keyword-level SEO research. It starts when a keyword is submitted via a webhook (a simple POST request from a form, tool, or internal system). The workflow cleans the payload, sends the keyword to SerpAPI to pull live Google results for India, and then normalizes the response into a structured dataset you can actually analyze. From there, GPT-4o reviews the SERP patterns and returns strict JSON: intent, competition level, content gaps, and high-impact opportunities. Finally, it drafts a polished HTML email for a client-ready report, sends it through Gmail, and appends the same structured fields into Google Sheets for tracking and audits. If anything fails, Slack gets an error alert with the node and message.

The workflow begins with “keyword in.” In the middle, SerpAPI provides real-world search behavior while AI turns that into decisions you can act on. At the end, you get two deliverables: an email you can forward today, and a row in Sheets you can reference later.

What Changes: Before vs. After

What This Eliminates

Impact You’ll See

Manually opening SERPs, copying results, and pasting snippets into a spreadsheet.
Re-checking “related searches” and video features by hand for every keyword.
Turning rough notes into a client-ready narrative email each time.
Wondering when a workflow failed because nothing alerts you.

Most teams take a keyword from request to report in about 10 minutes.
Search intent is documented consistently, so briefs stop drifting.
Content gaps are easier to defend because they’re grounded in live SERP evidence.
Your Google Sheets log becomes a lightweight SEO audit trail over time.
When a credential or API call breaks, Slack tells you what failed and where.

Real-World Impact

Say you research 10 keywords for a new content cluster. Manually, assume about an hour per keyword between SERP scanning, organizing notes, and drafting a presentable summary, so that’s basically a full day. With this workflow, you send 10 webhook requests (call it 2 minutes each), then wait for processing and emails to land while you do other work. You still review before sending to a client, but you’re reviewing a structured report instead of building one from scratch, which usually gives you about 6 hours back.

Requirements

n8n instance (try n8n Cloud free)
Self-hosting option if you prefer (Hostinger works well)
SerpAPI to pull live Google SERP results.
Google Sheets to store keyword insights historically.
OpenAI API key (get it from the OpenAI API dashboard).

Skill level: Intermediate. You’ll connect accounts, set credentials, and paste a webhook URL into the system that submits keywords.

Need help implementing this? Talk to an automation expert (free 15-minute consultation).

The Workflow Flow

A keyword request hits your webhook. You POST a payload that includes the target keyword. This can come from a form, an internal tool, a client intake, or a quick manual request when you’re exploring ideas.

The keyword is cleaned and used to fetch live SERPs. The workflow extracts the keyword field, then SerpAPI runs a Google search for the India market and returns organic results, related searches, video features, and metadata like result counts.

AI turns SERP noise into a structured opportunity brief. Raw SERP output is normalized into a consistent dataset, then GPT-4o analyzes intent, competition, gaps, and recommended formats. The workflow enforces a strict JSON schema and flattens it so downstream steps don’t get fragile.

You get deliverables in two places. Gmail sends an HTML report you can forward (or copy into your own template), and Google Sheets receives a new row with the key fields for tracking, audits, and future planning.

You can easily modify the target country, the reporting format, or where the request originates based on your needs. See the full implementation guide below for customization options.

Step-by-Step Implementation Guide

Step 1: Configure the Webhook Trigger

Set up the inbound request that starts the workflow and extracts the keyword payload.

Add the Incoming SEO Keyword Webhook node and set HTTP Method to POST.
Set the Path to c8996569-9459-4976-bbdd-b800179b3dc7.
Connect Incoming SEO Keyword Webhook to Isolate Keyword From Payload.
In Isolate Keyword From Payload, set the assignment body.keyword to ={{ $json.body.keyword }}.

Step 2: Connect SERP Data Source

Pull live SERP data for the provided keyword and normalize it for analysis.

Add Execute SERP Search India and set q to ={{ $json.body.keyword }}.
Set Additional Fields → num to 10 and Additional Fields → uule to India.
Credential Required: Connect your serpApi credentials in Execute SERP Search India.
Connect Execute SERP Search India → Standardize SERP Data Set and keep the provided jsCode as-is to normalize the response.

Step 3: Set Up the SEO Insight AI Analysis

Analyze standardized SERP data with AI and enforce a structured JSON response.

Add Assess SEO Opportunity AI and keep Prompt Type as define.
Set the Text field in Assess SEO Opportunity AI to the full template including expressions like {{ JSON.stringify($json.organic_results, null, 2) }} and the JSON output format.
Attach Market Insight LLM Core as the language model for Assess SEO Opportunity AI.
Credential Required: Connect your openAiApi credentials in Market Insight LLM Core.
Attach Structure Market JSON Output as the output parser for Assess SEO Opportunity AI and keep the provided jsonSchemaExample.
Note that Structure Market JSON Output is an AI sub-node; add credentials to the parent Market Insight LLM Core, not the parser.

Step 4: Transform and Route the AI Output

Flatten the AI result and align fields for downstream email and logging.

Connect Assess SEO Opportunity AI → Flatten AI Result Payload and keep jsCode as provided.
Connect Flatten AI Result Payload → Align SEO Fields for UI and keep the mapping logic that outputs keyword, country, search_intent, competition, content_gaps, content_opportunities, recommended_content_types, and confidence.
Align SEO Fields for UI outputs to both Draft Client SEO Email AI and Append SEO Log to Sheets in parallel.

Step 5: Configure Output Actions (Email + Sheets)

Generate a client-ready email and log the analysis to Google Sheets.

In Draft Client SEO Email AI, keep the HTML email prompt template and ensure it receives the structured data.
Attach Insight Formatting LLM as the language model for Draft Client SEO Email AI.
Credential Required: Connect your openAiApi credentials in Insight Formatting LLM.
Configure Dispatch SEO Report Email with Message and Subject set to =SEO Content Opportunity Analyzer, and set HTML Message to ={{ $json.output }}.
Credential Required: Connect your gmailOAuth2 credentials in Dispatch SEO Report Email.
Configure Append SEO Log to Sheets with Operation set to append and map columns like Keyword to ={{ $json.keyword }}, Timestamp to ={{$now}}, and Confidence to ={{ $json.confidence }}.
Credential Required: Connect your googleSheetsOAuth2Api credentials in Append SEO Log to Sheets.

Step 6: Add Error Handling

Ensure failures are captured and sent to Slack for visibility.

Enable Failure Catch Trigger to catch workflow errors.
Connect Failure Catch Trigger → Post Slack Error Notice.
Set the Text field in Post Slack Error Notice to =❌ *Error in API Error Catalog Workflow* *Node:* {{ $json.node.name }} *Message:* {{ $json.error.message }} *Time:* {{ $json.timestamp }}.
Credential Required: Connect your slackApi credentials in Post Slack Error Notice.

⚠️ Common Pitfall: If Slack alerts aren’t sent, verify the channelId in Post Slack Error Notice is a valid channel your bot can access.

Step 7: Test and Activate Your Workflow

Validate each branch and confirm that email and logging run in parallel.

Click Execute Workflow and send a POST request to Incoming SEO Keyword Webhook with a JSON body like {"keyword":"ai automation for ecommerce"}.
Confirm Execute SERP Search India returns results and Assess SEO Opportunity AI produces structured JSON output.
Verify that Align SEO Fields for UI triggers both Draft Client SEO Email AI and Append SEO Log to Sheets in parallel.
Check that Dispatch SEO Report Email sends a formatted HTML email and Append SEO Log to Sheets appends a new row.
Once verified, toggle the workflow to Active for production use.

🔒

Unlock Full Step-by-Step Guide

Get the complete implementation guide + downloadable template

Watch Out For

SerpAPI credentials can expire or hit plan limits. If results suddenly come back empty, check your SerpAPI dashboard usage and the API key stored in n8n first.
If you’re using Wait nodes or external rendering, processing times vary. Bump up the wait duration if downstream nodes fail on empty responses.
Default prompts in AI nodes are generic. Add your brand voice early or you’ll be editing outputs forever.

Common Questions

How quickly can I implement this SerpAPI Sheets automation automation?

Usually within an hour once your accounts are ready.

Can non-technical teams implement this SERP-to-Sheets?

Yes, but you’ll want someone comfortable with connecting OAuth accounts in n8n. No coding is required for day-to-day use once it’s set up.

Is n8n free to use for this SerpAPI Sheets automation workflow?

Yes. n8n has a free self-hosted option and a free trial on n8n Cloud. Cloud plans start at $20/month for higher volume. You’ll also need to factor in OpenAI API usage and your SerpAPI plan.

Where can I host n8n to run this automation?

Two options: n8n Cloud (managed, easiest setup) or self-hosting on a VPS. For self-hosting, Hostinger VPS is affordable and handles n8n well. Self-hosting gives you unlimited executions but requires basic server management.

How do I adapt this SerpAPI Sheets automation solution to my specific challenges?

You can swap the “Execute SERP Search India” settings to target a different country or language, then adjust the AI prompts that generate the structured JSON and the HTML email. Common tweaks include changing the competition scale, adding fields like “primary content type,” and writing the final output into a different Google Sheets tab per client.

Why is my SerpAPI connection failing in this workflow?

Most of the time it’s an invalid API key or you’ve hit SerpAPI usage limits. Check the SerpAPI dashboard, then re-save the credential in n8n. If the keyword has unusual characters, also look at the “Isolate Keyword From Payload” step to confirm the cleaned keyword is what you expect.

What’s the capacity of this SerpAPI Sheets automation solution?

On n8n Cloud, capacity depends on your plan’s monthly executions; self-hosting removes the execution cap and shifts the limit to your server. Practically, most teams run this comfortably for dozens of keywords a day, because each run is one keyword in and a few API calls out.

Is this SerpAPI Sheets automation automation better than using Zapier or Make?

Often, yes. n8n is better when you need structured parsing, error handling, and multi-step logic without paying extra for branching. It’s also easier to keep an audit trail because you control the whole flow, including Slack error alerts and how fields are normalized before Sheets. Zapier and Make can work, but complex JSON shaping and AI schema enforcement tends to get clunky. The real deciding factor is volume and complexity. Talk to an automation expert if you want a quick recommendation based on your exact workflow.

You end up with the same thing every time: live SERP evidence, clear gaps, and a report you don’t dread sending. Set it up once, then let the workflow do the repetitive work while you focus on the decisions.

Need Help Setting This Up?

Our automation experts can build and customize this workflow for your specific needs. Free 15-minute consultation—no commitment required.

Lisa Granqvist

Workflow Automation Expert

Expert in workflow automation and no-code tools.

{
"id": "",
"meta": {
"instanceId": "0433b7ccaadf1174ee601e0f020f6d5e34bc692ee5050d1bb29b38a2bb1458d3"
},
"name": "Automated SEO Insight Analysis System",
"tags": [],
"nodes": [
{
"id": "flowpast-topbar-11893",
"name": "Flowpast Branding",
"type": "n8n-nodes-base.stickyNote",
"position": [
220,
20
],
"parameters": {
"color": 7,
"width": 1265,
"height": 80,
"content": "## Flowpast.com | Automation Workflow Library\n**\ud83d\udcd6 Full tutorial & setup guide:** flowpast.com"
},
"typeVersion": 1
},
{
"id": "667f9b9f-9a96-4d73-8aa0-fb7f0cfda8b2",
"name": "Market Insight LLM Core",
"type": "@n8n/n8n-nodes-langchain.lmChatOpenAi",
"position": [
420,
120
],
"parameters": {
"model": {
"__rl": true,
"mode": "list",
"value": "gpt-4o",
"cachedResultName": "gpt-4o"
},
"options": [],
"responsesApiEnabled": false
},
"credentials": {
"openAiApi": {
"id": "credential-id",
"name": ""
}
},
"typeVersion": 1.3
},
{
"id": "ecfbcb1b-ec88-49df-a19e-983fc6d0f6e2",
"name": "Structure Market JSON Output",
"type": "@n8n/n8n-nodes-langchain.outputParserStructured",
"position": [
560,
120
],
"parameters": {
"jsonSchemaExample": "{\n  \"keyword\": \"AI automation for ecommerce\",\n  \"country\": \"India\",\n\n  \"seo_summary\": {\n    \"search_intent\": \"Informational + Commercial\",\n    \"competition_level\": \"High\",\n    \"serp_features\": [\"Videos\", \"Authority Blogs\", \"Reddit\"]\n  },\n\n  \"content_gaps\": [\n    \"Very few step-by-step ecommerce automation workflows\",\n    \"Lack of real implementation case studies\",\n    \"Most content is tool-list based and generic\"\n  ],\n\n  \"content_opportunities\": [\n    {\n      \"title\": \"How to Build AI Automation Workflows for Shopify Stores\",\n      \"angle\": \"Practical implementation\",\n      \"difficulty\": \"Medium\"\n    },\n    {\n      \"title\": \"Real Ecommerce AI Automation Case Studies (2025)\",\n      \"angle\": \"Experience-based\",\n      \"difficulty\": \"Medium\"\n    },\n    {\n      \"title\": \"Free vs Paid AI Automation Tools for Ecommerce\",\n      \"angle\": \"Comparison\",\n      \"difficulty\": \"Low\"\n    }\n  ],\n\n  \"recommended_content_types\": [\n    \"Long-form blog\",\n    \"YouTube explainer\",\n    \"Workflow playbook\"\n  ],\n\n  \"confidence_score\": \"High\"\n}\n"
},
"typeVersion": 1.3
},
{
"id": "fbc0d971-e377-4ee8-85f7-4cb2e1b6e040",
"name": "Failure Catch Trigger",
"type": "n8n-nodes-base.errorTrigger",
"position": [
220,
620
],
"parameters": [],
"typeVersion": 1
},
{
"id": "778714d3-e3a4-4a66-873e-219642cc429e",
"name": "Post Slack Error Notice",
"type": "n8n-nodes-base.slack",
"position": [
460,
635
],
"webhookId": "d5eff2c3-ff6e-4116-bdda-59455acd861b",
"parameters": {
"text": "=\u274c *Error in API Error Catalog Workflow* *Node:* {{ $json.node.name }} *Message:* {{ $json.error.message }} *Time:* {{ $json.timestamp }}",
"select": "channel",
"channelId": {
"__rl": true,
"mode": "list",
"value": "[YOUR_ID]",
"cachedResultName": "general-information"
},
"otherOptions": []
},
"credentials": {
"slackApi": {
"id": "credential-id",
"name": ""
}
},
"typeVersion": 2.3
},
{
"id": "27c67566-d2b0-46ac-a276-8ed5359149c9",
"name": "Isolate Keyword From Payload",
"type": "n8n-nodes-base.set",
"position": [
860,
190
],
"parameters": {
"options": [],
"assignments": {
"assignments": [
{
"id": "520ab8b7-b3ba-4f51-9568-6871888a79f0",
"name": "body.keyword",
"type": "string",
"value": "={{ $json.body.keyword }}"
}
]
}
},
"typeVersion": 3.4
},
{
"id": "b5fe5a52-0412-4811-a2a8-c2142a3677ab",
"name": "Execute SERP Search India",
"type": "n8n-nodes-serpapi.serpApi",
"position": [
610,
220
],
"parameters": {
"q": "={{ $json.body.keyword }}",
"requestOptions": [],
"additionalFields": {
"num": "10",
"uule": "India"
}
},
"credentials": {
"serpApi": {
"id": "credential-id",
"name": ""
}
},
"typeVersion": 1
},
{
"id": "05153e8d-efce-47bf-b91f-b0affd8e2b19",
"name": "Standardize SERP Data Set",
"type": "n8n-nodes-base.code",
"position": [
360,
195
],
"parameters": {
"jsCode": "const data = $input.first().json;\n\nreturn [\n  {\n    json: {\n      keyword: data.search_parameters?.q || \"\",\n      country: data.search_parameters?.uule || \"\",\n      total_results: data.search_information?.total_results || null,\n\n      organic_results: (data.organic_results || []).map(r => ({\n        position: r.position,\n        title: r.title,\n        snippet: r.snippet,\n        link: r.link,\n        source: r.source || null,\n        date: r.date || null\n      })),\n\n      videos: (data.inline_videos || []).map(v => ({\n        title: v.title,\n        platform: v.platform,\n        duration: v.duration,\n        link: v.link\n      })),\n\n      related_searches: (data.related_searches || []).map(rs => rs.query)\n    }\n  }\n];\n"
},
"typeVersion": 2
},
{
"id": "d28b0e9c-fb98-40cd-96da-24e6a866854d",
"name": "Assess SEO Opportunity AI",
"type": "@n8n/n8n-nodes-langchain.agent",
"position": [
380,
390
],
"parameters": {
"text": "=Keyword: {{ $json.keyword }}\nCountry: {{ $json.country }}\nTotal Results: {{ $json.total_results }}\n\nTop Organic Results:\n{{ JSON.stringify($json.organic_results, null, 2) }}\n\nRelated Searches:\n{{ JSON.stringify($json.related_searches, null, 2) }}\n\nVideos Present:\n{{ JSON.stringify($json.videos, null, 2) }}\n\nReturn the output in this JSON format:\n{\n  \"search_intent\": \"\",\n  \"competition_level\": \"\",\n  \"content_gaps\": [],\n  \"top_opportunities\": [],\n  \"recommended_content_types\": []\n}\n",
"options": {
"systemMessage": "=You are an SEO strategist working for an SEO agency.\n\nYour job is to analyze Google search results and identify:\n- Search intent\n- Competition level\n- Content gaps\n- High-impact content opportunities\n\nFocus on gaps and opportunities, not summaries.\nBe practical and concise.\n"
},
"promptType": "define",
"hasOutputParser": true
},
"typeVersion": 2
},
{
"id": "2ff3fef3-67ff-4a1a-9869-80bc137eacdb",
"name": "Incoming SEO Keyword Webhook",
"type": "n8n-nodes-base.webhook",
"position": [
1110,
210
],
"webhookId": "c8996569-9459-4976-bbdd-b800179b3dc7",
"parameters": {
"path": "c8996569-9459-4976-bbdd-b800179b3dc7",
"options": [],
"httpMethod": "POST"
},
"typeVersion": 2.1
},
{
"id": "f9a62170-9da2-494a-8465-d2adee785f01",
"name": "Flatten AI Result Payload",
"type": "n8n-nodes-base.code",
"position": [
630,
370
],
"parameters": {
"jsCode": "return [\n  {\n    json: {\n      ...$json.output\n    }\n  }\n];\n"
},
"typeVersion": 2
},
{
"id": "083b65e8-f773-41c6-800b-3a11aaf3db4a",
"name": "Align SEO Fields for UI",
"type": "n8n-nodes-base.code",
"position": [
885,
395
],
"parameters": {
"jsCode": "return [\n  {\n    json: {\n      // Core identifiers\n      keyword: $json.keyword,\n      country: $json.country,\n\n      // Fields UI is clearly expecting\n      search_intent: $json.seo_summary?.search_intent || \"\",\n      competition: $json.seo_summary?.competition_level || \"\",\n      serp_features: $json.seo_summary?.serp_features || [],\n\n      // Main sections\n      content_gaps: $json.content_gaps || [],\n      content_opportunities: $json.content_opportunities || [],\n      recommended_content_types: $json.recommended_content_types || [],\n\n      // CRITICAL: UI confidence binding\n      confidence: $json.confidence_score || \"Not determined\"\n    }\n  }\n];\n"
},
"typeVersion": 2
},
{
"id": "e1f82952-7f6a-4ad2-b11f-eece2be80f42",
"name": "Draft Client SEO Email AI",
"type": "@n8n/n8n-nodes-langchain.agent",
"position": [
1135,
365
],
"parameters": {
"text": "=Create a professional SEO Content Opportunity email summary using the following data.\n\nInclude these sections:\n1. Keyword & Country\n2. Search Intent (short paragraph)\n3. Competition Level (clear and direct)\n4. Key Content Gaps (bullet list)\n5. Top Content Opportunities (title + short description + difficulty)\n6. Recommended Content Formats\n7. Overall Confidence Score\n\nFormat the output as clean HTML with headings, bullet points, and spacing suitable for email.\n\nSEO Analysis Data:\n{{ JSON.stringify($json, null, 2) }}\n",
"options": {
"systemMessage": "=You are a senior SEO consultant writing professional client-ready email reports.\n\nYour task:\n- Summarize the provided SEO analysis clearly and concisely\n- Use a clean, visually structured HTML format\n- Keep the tone professional, clear, and business-friendly\n- Do NOT include raw JSON\n- Do NOT mention AI, prompts, or internal processes\n\nThe output must be valid HTML suitable for email.\n"
},
"promptType": "define"
},
"typeVersion": 2
},
{
"id": "26b1d7e3-8802-420f-9ba2-e3c44f2ebc80",
"name": "Insight Formatting LLM",
"type": "@n8n/n8n-nodes-langchain.lmChatOpenAi",
"position": [
1090,
120
],
"parameters": {
"model": {
"__rl": true,
"mode": "list",
"value": "gpt-4o",
"cachedResultName": "gpt-4o"
},
"options": [],
"responsesApiEnabled": false
},
"credentials": {
"openAiApi": {
"id": "credential-id",
"name": ""
}
},
"typeVersion": 1.3
},
{
"id": "efb57a55-2f4e-4dca-bbca-928419c5c8ad",
"name": "Dispatch SEO Report Email",
"type": "n8n-nodes-base.gmail",
"position": [
1385,
385
],
"parameters": {
"toList": [
"[YOUR_EMAIL]"
],
"message": "=SEO Content Opportunity Analyzer",
"subject": "=SEO Content Opportunity Analyzer",
"resource": "message",
"htmlMessage": "={{ $json.output }}",
"includeHtml": true,
"additionalFields": {
"ccList": []
}
},
"credentials": {
"gmailOAuth2": {
"id": "credential-id",
"name": ""
}
},
"typeVersion": 1
},
{
"id": "f2837049-ecb2-4cc3-81ae-3a98abedbbb0",
"name": "Append SEO Log to Sheets",
"type": "n8n-nodes-base.googleSheets",
"position": [
910,
555
],
"parameters": {
"columns": {
"value": {
"Country": "={{ $json.country }}",
"Keyword": "={{ $json.keyword }}",
"Timestamp": "={{$now}}",
"Confidence": "={{ $json.confidence }}",
"Competition": "={{ $json.competition }}",
"Content Gaps": "={{ $json.content_gaps }}",
"Opportunities": "={{ $json.content_opportunities }}",
"Search Intent": "={{ $json.search_intent }}"
},
"schema": [
{
"id": "Timestamp",
"type": "string",
"display": true,
"required": false,
"displayName": "Timestamp",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Keyword",
"type": "string",
"display": true,
"required": false,
"displayName": "Keyword",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Country",
"type": "string",
"display": true,
"required": false,
"displayName": "Country",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Search Intent",
"type": "string",
"display": true,
"required": false,
"displayName": "Search Intent",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Competition",
"type": "string",
"display": true,
"required": false,
"displayName": "Competition",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Confidence",
"type": "string",
"display": true,
"required": false,
"displayName": "Confidence",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Content Gaps",
"type": "string",
"display": true,
"required": false,
"displayName": "Content Gaps",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Opportunities",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "Opportunities",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Trigger Source",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Trigger Source",
"defaultMatch": false,
"canBeUsedToMatch": true
}
],
"mappingMode": "defineBelow",
"matchingColumns": [],
"attemptToConvertTypes": false,
"convertFieldsToString": false
},
"options": [],
"operation": "append",
"sheetName": {
"__rl": true,
"mode": "list",
"value": "[YOUR_ID]",
"cachedResultUrl": "",
"cachedResultName": "SEO Content Opportunity log"
},
"documentId": {
"__rl": true,
"mode": "list",
"value": "[YOUR_ID]",
"cachedResultUrl": "",
"cachedResultName": "sample_leads_50"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "credential-id",
"name": ""
}
},
"typeVersion": 4.7
}
],
"active": false,
"pinData": [],
"settings": {
"executionOrder": "v1"
},
"versionId": "",
"connections": {
"Failure Catch Trigger": {
"main": [
[
{
"node": "Post Slack Error Notice",
"type": "main",
"index": 0
}
]
]
},
"Insight Formatting LLM": {
"ai_languageModel": [
[
{
"node": "Draft Client SEO Email AI",
"type": "ai_languageModel",
"index": 0
}
]
]
},
"Align SEO Fields for UI": {
"main": [
[
{
"node": "Draft Client SEO Email AI",
"type": "main",
"index": 0
},
{
"node": "Append SEO Log to Sheets",
"type": "main",
"index": 0
}
]
]
},
"Structure Market JSON Output": {
"ai_outputParser": [
[
{
"node": "Assess SEO Opportunity AI",
"type": "ai_outputParser",
"index": 0
}
]
]
},
"Isolate Keyword From Payload": {
"main": [
[
{
"node": "Execute SERP Search India",
"type": "main",
"index": 0
}
]
]
},
"Flatten AI Result Payload": {
"main": [
[
{
"node": "Align SEO Fields for UI",
"type": "main",
"index": 0
}
]
]
},
"Incoming SEO Keyword Webhook": {
"main": [
[
{
"node": "Isolate Keyword From Payload",
"type": "main",
"index": 0
}
]
]
},
"Assess SEO Opportunity AI": {
"main": [
[
{
"node": "Flatten AI Result Payload",
"type": "main",
"index": 0
}
]
]
},
"Standardize SERP Data Set": {
"main": [
[
{
"node": "Assess SEO Opportunity AI",
"type": "main",
"index": 0
}
]
]
},
"Execute SERP Search India": {
"main": [
[
{
"node": "Standardize SERP Data Set",
"type": "main",
"index": 0
}
]
]
},
"Market Insight LLM Core": {
"ai_languageModel": [
[
{
"node": "Assess SEO Opportunity AI",
"type": "ai_languageModel",
"index": 0
}
]
]
},
"Draft Client SEO Email AI": {
"main": [
[
{
"node": "Dispatch SEO Report Email",
"type": "main",
"index": 0
}
]
]
}
}
}