Semrush to Google Sheets, competitor data logged clean
Competitor research sounds simple until you’re staring at three Semrush exports, five tabs, and a “quick” report that somehow ate your afternoon.
This Semrush Sheets automation hits SEO leads first, but marketing managers and agency owners feel it too. You get clean, consistent competitor data logged into the right Google Sheets tabs automatically, so your tracking doesn’t fall apart the moment Semrush changes a column name.
Below you’ll see how the workflow captures a domain, pulls competitor insights via the Semrush API, reformats the results, then appends domain, competitors, pages, and keyword metrics into tidy sheets.
How This Automation Works
See how this solves the problem:
n8n Workflow Template: Semrush to Google Sheets, competitor data logged clean
flowchart LR
subgraph sg0["Form Submission Flow"]
direction LR
n0["<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/form.svg' width='40' height='40' /></div><br/>Form Submission Trigger"]
n1["<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/>Format Domain Summary"]
n2["<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/>Format Competitor List"]
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/httprequest.dark.svg' width='40' height='40' /></div><br/>Competitor API Request"]
n4@{ icon: "mdi:database", form: "rounded", label: "Append Domain Overview", pos: "b", h: 48 }
n5@{ icon: "mdi:database", form: "rounded", label: "Append Competitor Metrics", 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/>Format Organic Pages"]
n7@{ icon: "mdi:database", form: "rounded", label: "Append Organic Pages", 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/code.svg' width='40' height='40' /></div><br/>Format Keyword Metrics"]
n9@{ icon: "mdi:database", form: "rounded", label: "Append Organic Keywords", pos: "b", h: 48 }
n2 --> n5
n8 --> n9
n6 --> n7
n1 --> n4
n0 --> n3
n3 --> n1
n3 --> n2
n3 --> n6
n3 --> n8
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 n0 trigger
class n4,n5,n7,n9 database
class n3 api
class n1,n2,n6,n8 code
classDef customIcon fill:none,stroke:none
class n0,n1,n2,n3,n6,n8 customIcon
The Challenge: Competitor data that won’t stay consistent
Manual competitor tracking usually starts with good intentions. Then the “monthly check-in” turns into downloading exports, copying columns into a spreadsheet, fixing broken formatting, and trying to remember which tab holds which metric. It gets worse when you need multiple views: domain overview, organic competitors, organic pages, and keyword-level details. One missed step and your report is wrong, your trend line jumps for no reason, and you spend more time cleaning data than making decisions.
It adds up fast. Here’s where it breaks down in real life.
- You keep re-building the same spreadsheet structure because exports rarely match your tabs perfectly.
- Small copy-paste mistakes slip in, and those mistakes always show up right before a client call.
- “Competitors” ends up meaning different things across different reports, so your team debates definitions instead of acting.
- Keyword metrics like CPC, volume, and difficulty get tracked inconsistently, which makes prioritization feel like guesswork.
The Fix: Semrush competitor insights logged into tidy Google Sheets tabs
This workflow turns competitor analysis into a repeatable input-output system. It starts when someone submits a domain (your site or a competitor) through a simple form. n8n sends that domain to the Competitor Analysis Semrush API using an HTTP request, then pulls back the useful parts you actually report on: domain overview stats, organic competitors, top organic pages, and keyword-level metrics. Instead of dumping raw JSON into a sheet, it reformats each dataset so the fields line up cleanly. Finally, it appends each formatted dataset into its own Google Sheets tab, so your “Domain Overview” doesn’t get mixed with “Organic Pages,” and keyword rows don’t end up smashed into a competitor list.
The workflow begins with form submission. From there, one API request branches into four formatting paths (domain summary, competitor list, organic pages, and keyword metrics). Each path appends to a dedicated Google Sheet, which keeps reporting stable even as you track more domains over time.
What Changes: Before vs. After
| What This Eliminates | Impact You’ll See |
|---|---|
|
|
Real-World Impact
Say you track 10 competitor domains each week and you want four views per domain (overview, competitors, pages, keywords). Manually, you’ll spend maybe 10 minutes per view to export, clean, and paste, which is about 7 hours weekly. With this workflow, you submit the domain once and let it run. Even if you budget 5 minutes to review the new rows and sanity-check trends, you’re still getting back roughly 6 hours every week.
Requirements
- n8n instance (try n8n Cloud free)
- Self-hosting option if you prefer (Hostinger works well)
- Semrush API access to pull competitor analysis data
- Google Sheets to store results in structured tabs
- Semrush API key (get it from your Semrush account/API area)
Skill level: Intermediate. You’ll connect accounts, paste an API key, and confirm your sheet/tab names match.
Need help implementing this? Talk to an automation expert (free 15-minute consultation).
The Workflow Flow
A domain gets submitted. Someone enters a website URL in a form, so you have a clean, repeatable starting point instead of “send me the domain in Slack.”
Semrush competitor data is requested. n8n sends that domain to the Competitor Analysis Semrush API via an HTTP request and receives the full response back in one go.
The response is split into four usable datasets. Separate formatting steps extract and tidy the domain overview, organic competitor list, organic pages, and organic keyword metrics so they match your spreadsheet columns.
Everything is appended to the right Google Sheets tab. Each dataset goes into its own sheet (overview, competitors, pages, keywords), which keeps your reporting stable as you add more domains over time.
You can easily modify which metrics you keep (or rename columns) to match your reporting template. See the full implementation guide below for customization options.
Step-by-Step Implementation Guide
Step 1: Configure the Form Submission Trigger
Set up the form that captures a target website and initiates the competitor analysis request.
- Add the Form Submission Trigger node as your workflow trigger.
- Set Form Title to
Competitor Analysis. - Set Form Description to
Competitor Analysis. - Add a required form field labeled website.
Step 2: Configure the Competitor API Request
Send the submitted website to the SEMrush competitor analysis endpoint via RapidAPI.
- Add the Competitor API Request node and connect it to Form Submission Trigger.
- Set URL to
https://competitor-analysis-semrush.p.rapidapi.com/competitor.php. - Set Method to
POSTand enable Send Body. - Set Content Type to
multipart-form-data. - Add a body parameter named website with value
{{ $json.website }}. - Enable Send Headers and add header x-rapidapi-host set to
competitor-analysis-semrush.p.rapidapi.com. - Add header x-rapidapi-key and replace
[CONFIGURE_YOUR_API_KEY]with your RapidAPI key.
Competitor API Request outputs to Format Domain Summary, Format Competitor List, Format Organic Pages, and Format Keyword Metrics in parallel.
[CONFIGURE_YOUR_API_KEY] unchanged, the workflow will fail at Competitor API Request.Step 3: Set Up the Data Formatting Nodes
Parse the API response into the four datasets used for logging.
- In Format Domain Summary, set JavaScript Code to
return $input.first().json.data.semrushAPI.domainOverview. - In Format Competitor List, set JavaScript Code to
return $input.first().json.data.semrushAPI.organicCompetitors;. - In Format Organic Pages, set JavaScript Code to
return $input.first().json.data.semrushAPI.organicPages;. - In Format Keyword Metrics, set JavaScript Code to
return $input.first().json.data.semrushAPI.domainOrganicSearchKeywords;.
Step 4: Configure Google Sheets Outputs
Append each formatted dataset to its corresponding worksheet.
- For Append Domain Overview, set Operation to
appendand Authentication toserviceAccount, then choose the domainOverview sheet in the Seo n8n document. - For Append Competitor Metrics, set Operation to
appendand Authentication toserviceAccount, then choose the organicCompetitors sheet in the Seo n8n document. - For Append Organic Pages, set Operation to
appendand Authentication toserviceAccount, then choose the organicPages sheet in the Seo n8n document. - For Append Organic Keywords, set Operation to
appendand Authentication toserviceAccount, then choose the domainOrganicSearchKeywords sheet in the Seo n8n document. - Credential Required: Connect your googleApi credentials in Append Domain Overview, Append Competitor Metrics, Append Organic Pages, and Append Organic Keywords.
Step 5: Test and Activate Your Workflow
Run a full test to confirm the API response is parsed and logged into all four Google Sheets tabs.
- Click Execute Workflow and submit the form with a valid domain in the website field.
- Verify that Competitor API Request returns data and that all four formatting nodes produce items.
- Check the Google Sheets file to confirm new rows are appended in domainOverview, organicCompetitors, organicPages, and domainOrganicSearchKeywords.
- When successful, toggle the workflow to Active to accept live form submissions.
Watch Out For
- Semrush API credentials can expire or be scoped to the wrong plan. If things break, check your Semrush API key status and quota limits 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.
- Google Sheets permissions trip people up. If appends fail, confirm the connected Google account can edit the target spreadsheet and that the tab names match exactly.
Common Questions
About an hour if your Semrush API and Google Sheets access are ready.
Yes. You will mostly be pasting an API key and choosing the right Google Sheet tabs.
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 Semrush API costs based on your plan and usage.
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.
You can customize it by changing what the formatting steps output before data is appended to Google Sheets. For example, if you don’t care about CPC, remove it from the “Format Keyword Metrics” mapping so your keyword tab stays lean. If you want extra domain stats, add those fields in “Format Domain Summary” and make sure the “Append Domain Overview” sheet has matching columns. Common tweaks include tracking only the top 10 competitors, filtering pages by traffic, and standardizing naming for client workspaces.
Usually it’s an invalid or expired Semrush API key, or you’ve hit a quota limit on your Semrush plan. Update the key in your HTTP Request credentials, then re-run one domain to confirm the response looks normal. If the API returns data but your sheet is empty, the issue is often field names that no longer match your formatting logic.
If you self-host n8n, there’s no execution limit, and the real constraint becomes your Semrush API quota and Google Sheets write limits.
Often, yes, because this workflow benefits from branching into multiple datasets and formatting them before writing to separate tabs. n8n handles that kind of “one trigger, four outputs” pattern without getting awkward or expensive. Self-hosting is also a big deal if you run lots of domains and don’t want every run treated like a premium task. Zapier or Make can still be fine for a simple “call API then add one row” job. If you’re unsure, Talk to an automation expert and we’ll map it to your volume and reporting needs.
Once this is set up, competitor research becomes a button-click, not a mini project. Your sheets stay clean, your reports stay consistent, and you get your week back.
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.
