SEO Data Aggregation and Competitor Analysis Log - n8n Workflow

SEO Specialists and Strategists
Marketing Analysts requiring automated data aggregation
Users who need repeatable, detailed competitor research
Developers looking for advanced n8n templates utilizing complex API structures

• Anyone needing to leverage an n8n node for high-volume data logging.

This n8n workflow is engineered to streamline comprehensive SEO competitor analysis, moving beyond manual data exports. Instead of requiring interaction with the Semrush dashboard for various reports, this automation, triggered by a simple website submission, executes a complex API request to gather four critical datasets: Domain Overview, Organic Competitors, Organic Pages, and Detailed Organic Search Keywords.

The core value lies in its structured parallel processing. After fetching the data via an HTTP Request n8n node, the flow splits into four branches, each processing a specific part of the JSON response. The results are then automatically and cleanly logged across dedicated sheets in a single Google Sheet document. This advanced n8n workflow ensures accurate, consistent, and scalable data logging, providing a robust foundation for competitive tracking and reporting, far surpassing the capabilities of standard manual processes.

This powerful n8n workflow operates through a structured sequence of actions, ensuring every piece of competitor data is captured and logged correctly.

1. Trigger Initiation: The process starts with the “On form submission” n8n trigger. This acts as a gateway, capturing the target website URL that a user inputs into the automated form.


2. API Integration: The “Competitor Analysis” n8n node executes a critical HTTP POST request to a Semrush RapidAPI wrapper. It dynamically injects the captured website URL to initiate the large-scale competitor data extraction.


3. Data Parallelization and Cleanup: The single, large JSON response from the API is immediately distributed into four parallel branches. Each branch utilizes a dedicated Code n8n node (e.g., “Re format output,” “Reformat”) to specifically isolate and clean relevant data subsets, such as domainOverview or organicCompetitors, preparing them for structured database entry.


4. Structured Data Logging: Four separate Google Sheets n8n nodes work simultaneously to append the cleaned data. Each sheet node targets a distinct tab within the master “Seo n8n” Google Sheet document, logging metrics like domain traffic, organic keyword positions, and high-performing organic pages. This completes the fully automated n8n workflow cycle, delivering ready-to-use analysis data.

To deploy this powerful n8n workflow, follow these steps:

1. Import the n8n Workflow: Copy the provided JSON data and paste it into your n8n instance using the 'New' -> 'Import from JSON' option.


2. Set up the Trigger: Activate the "On form submission" n8n trigger node to generate the webhook URL. Share this URL or embed the form where you wish to initiate the competitor analysis.


3. Configure API Credentials: Open the "Competitor Analysis" (HTTP Request) n8n node. Update the x-rapidapi-key header parameter with your valid RapidAPI key for the Semrush integration service. Note: The specific URL https://competitor-analysis-semrush.p.rapidapi.com/competitor.php relies on a third-party RapidAPI service.


4. Google Sheets Setup:

This n8n workflow leverages multiple nodes for structured data handling:

On form submission (Form Trigger n8n trigger):
Function: Serves as the starting n8n trigger, collecting the URL input (website) necessary for the API query.
Key Configuration: Configured to require the website field input.
Competitor Analysis (HTTP Request n8n node):
Function: Executes the core data retrieval logic by calling the Semrush RapidAPI endpoint.
Key Configuration: Uses a POST method, sets Content-Type to multipart-form-data, and dynamically uses ={{ $json.website }} from the trigger node in the body parameters.
Re format output, Reformat, Reformat 2, Reformat2 (Code n8n node):
Function: These four separate n8n node instances are essential for parsing the single massive API response, ensuring only the necessary array or object structure is passed forward for logging.
Key Configuration: Each node uses JavaScript to isolate a specific path, e.g., return $input.first().json.data.semrushAPI.domainOverview.
Domain overview, Organic Competitor, Organic Pages, organic keywords (Google Sheets n8n node):
Function: Handles the final step of the n8n workflow, securely logging the processed SEO data into the specified Google Sheet.
Key Configuration: Operation set to Append. Each node is mapped to automatically match input fields (e.g., organicKeywords, searchVolume) to distinct columns in a designated sheet (e.g., domainOverview, organic keywords). Uses Service Account authentication.