SEO Keyword Rank Tracker With Google Sheets, BigQuery, And n8n: A Story Of One Overwhelmed Marketer

Introduction: When SEO Reports Start To Hurt

On a rainy Tuesday morning, Lina, a growth marketer at a small SaaS startup, stared at her screen in quiet frustration. Her CEO had just asked a simple question:

“Are we actually improving for our main keywords across all our domains?”

Lina opened five browser tabs. One for a pricey SEO rank tracker, another for Google Search Console, a third for Google Sheets, and two more for various reports she barely trusted anymore. Each tool told a slightly different story. None of them gave her a clean, unified view of how their target keywords were performing over time across every domain they managed.

The rank tracking tool alone was eating a painful chunk of their monthly budget. Worse, it could not fully adapt to their custom keyword lists, device breakdowns, or the way they wanted to report data to the team.

That morning, Lina decided two things:

• She would stop relying on expensive rank trackers that felt like black boxes.
• She would finally put their Google Search Console data, Google Sheets, and BigQuery to work in a way that actually fit their needs.

That decision led her to an n8n workflow template that changed how she tracked SEO performance, permanently.

Discovering The n8n Rank Tracking Template

Lina had heard of n8n before. Some colleagues used it to automate marketing ops and reporting, but she had never tried setting up a workflow herself. While searching for a “Google Search Console BigQuery rank tracker,” she stumbled on an n8n template titled:

“SEO Keyword Rank Tracker with Google Sheets & BigQuery”

The promise sounded almost too good:

• Replace expensive SEO rank trackers.
• Use data she already had in Google Search Console.
• Store and analyze everything in Google Sheets and BigQuery.
• Scale to any number of domains and keywords.

Curious and slightly skeptical, Lina opened the template. Instead of a generic black box, she found a clear structure split into two core flows:

• Keyword tracking by keyword list
• Keywords by URL and top position

It was exactly what she had been trying to cobble together manually in spreadsheets.

Rising Action: Setting The Stage For Automation

Before Lina could run the workflow, she had to prepare the foundations. That was the first test. If setup was too painful, she knew she would abandon the idea and go back to screenshots and copy-pasted CSVs.

Getting The Data Sources Ready

Lina started with the basics the template required:

• Google Search Console bulk export
  She enabled bulk export for her properties so that Google Search Console would continuously send performance data into BigQuery. This gave her raw tables with queries, URLs, positions, impressions, and clicks – all the ingredients a real rank tracker needs.
• Google BigQuery
  She verified that her BigQuery project contained the Search Console export tables. These tables would power all the ranking queries in the workflow, letting her slice performance by keyword, URL, and date without hitting interface limits.
• Google Sheets
  The template required three specific spreadsheets, so Lina created them and noted their IDs:
  • Top Ranking Keywords – for queries that already rank well and for spotting low hanging fruits.
  • Rank Tracking – for daily keyword performance by URL and device over time.
  • Tracked Keywords – a master list of targeted keywords per domain.
• Credentials for n8n
  She set up secure credentials in n8n so the workflow could talk to both Google Sheets and BigQuery without manual exports. Once done, she never had to log in to download CSVs again.

To her surprise, this part went faster than expected. The real magic, she suspected, would be in how the workflow handled the daily rank tracking logic.

The Turning Point: Running The Workflow For The First Time

With everything connected, Lina took a breath and triggered the n8n workflow manually. The template came alive in two intertwined stories of data: one focused on her keyword lists, the other on uncovering top ranking and opportunity keywords.

Storyline One: Tracking Keywords From Her Own Lists

Lina had always maintained messy spreadsheets of target keywords per domain. The first section of the workflow, “Keyword Tracking by Keyword List”, finally gave that chaos a structure.

This is how it unfolded inside n8n, step by step, while she watched the nodes light up:

1. Trigger
   The workflow began with her manual test. Later, she planned to schedule it to run automatically, but for now she wanted to see it in action.
2. Domains setup
   She defined each domain they were tracking, along with the associated BigQuery tables and Google Sheets. The workflow was designed to handle multiple domains, so she no longer had to duplicate anything.
3. Loop and split per domain
   n8n split the process for each domain, handling them in parallel. For someone managing several brands, this parallelism felt like a superpower.
4. Google Sheets keyword retrieval
   For each domain, the workflow pulled the list of tracked keywords from her dedicated Google Sheet. These were the exact phrases she cared about, not a generic set defined by a tool.
5. If node to check history
   The workflow checked whether there was any existing historical data in the Rank Tracking sheet. If there was a last tracked date, it used that as the starting point.
6. Defaulting to the last 7 days
   For domains or keywords that had never been tracked before, the workflow automatically set the start date to 7 days ago. Lina did not have to guess where to begin.
7. BigQuery query for rankings
   Using those dates and keyword lists, the workflow queried BigQuery for ranking data by URL. It pulled positions, clicks, impressions, and other metrics for each tracked keyword since the last run.
8. Merge and insert into Google Sheets
   Finally, the workflow merged the new daily ranking data with what was already in the Rank Tracking sheet and appended the fresh rows. Lina watched as her once static spreadsheet turned into a living, time series dataset.

Instead of manually exporting Search Console data and filtering for each keyword, she now had an automated rank tracker built on top of her own keywords, her own sheets, and her own BigQuery data.

Storyline Two: Finding Top Ranking And Opportunity Keywords

That alone would have been enough to impress her CEO, but the second section of the workflow was where Lina really started to smile. The template also included a flow called “Keywords by URL and Top Position”, designed to surface both top performers and opportunity keywords.

Inside n8n, this second storyline played out like this:

1. Loop through domains
   Again, the workflow iterated through each domain separately, so Lina could compare performance across all their properties.
2. Retrieve latest top keyword date
   For each domain, the workflow checked the Top Ranking Keywords sheet to find the most recent date for which data had already been saved. This ensured that only new data would be fetched.
3. Set date with 7 day default
   If no previous entries existed, the workflow once more defaulted to a start date 7 days in the past. Lina did not have to manually adjust anything when adding a new domain.
4. BigQuery query for opportunities
   Using BigQuery, the workflow searched for keyword opportunities and top ranking keywords for each URL. It applied criteria like impressions and click through rate to identify which queries were already performing well and which had the potential to grow.
5. Insert results into Google Sheets
   Finally, it appended these opportunity keywords and their metrics into the Top Ranking Keywords sheet, giving Lina a clear list of what to prioritize in her next content sprint.

Within a single run, she had two powerful outputs:

• A detailed rank tracking log for her chosen keywords by URL and device.
• A curated list of high potential and high performing keywords by URL.

Resolution: From Chaos To A Scalable SEO Tracking System

By the end of that first test run, Lina’s relationship with SEO reporting had changed. The tension she felt each time a stakeholder asked for “just a quick update” on rankings started to fade.

What Lina Gained From The n8n Workflow

As she explored the new sheets and dashboards, the benefits became obvious:

• Cost savings
  She could now rely on Google Search Console, BigQuery, and Google Sheets combined with n8n instead of paying for multiple rank tracking tools. The workflow became a cost effective alternative that still delivered all the insights she needed.
• Full customization
  Every keyword list, every domain, every filter was under her control. She could add new tracked keywords per domain simply by updating the Tracked Keywords sheet.
• Long term historical tracking
  Each daily run appended fresh data. Over time, this built a rich history that allowed her to compare performance across weeks and months without worrying about data retention limits in external tools.
• Deeper keyword insights
  The Top Ranking Keywords sheet highlighted both strong performers and low hanging fruits. Instead of guessing which queries to optimize next, she could point to real impressions, CTR, and position data.
• Scalability across domains
  Whether her team added one new site or ten, the workflow could scale to any number of domains and keywords. She simply updated the configuration and sheets, and the automation handled the rest.

How Her Day To Day Workflow Changed

A few weeks later, the pattern was clear:

• The workflow ran on a schedule inside n8n, pulling fresh data from BigQuery and updating her Google Sheets daily.
• Weekly SEO reviews no longer meant frantic last minute exports. She opened the sheets and filtered by date to see trends instantly.
• Her content team used the Top Ranking Keywords sheet to choose which pages to improve or which topics deserved new content.
• The CEO stopped asking if rankings were “really improving” because the answer was now visible in a simple, shareable spreadsheet.

Bring This n8n SEO Rank Tracker Into Your Own Story

If you recognize yourself in Lina’s struggle, juggling tools and spreadsheets just to answer basic ranking questions, you do not have to keep doing it the hard way.

This n8n workflow template lets you:

• Leverage your existing Google Search Console data with BigQuery.
• Centralize keyword rank tracking in Google Sheets.
• Automate daily updates for any number of domains and keywords.
• Uncover top ranking and opportunity keywords without manual digging.

Once your bulk export, BigQuery, and Google Sheets are set up, the workflow becomes a quiet background process that powers your SEO decisions with reliable data.

Start Your Own Automation Chapter

Ready to turn your scattered SEO reports into a coherent, automated rank tracking system with n8n, Google Sheets, and BigQuery?

Use this template as the foundation of your workflow, then adapt it to your own domains and keyword strategy.

If you need help implementing or customizing this automation for your specific setup, you can always reach out for expert guidance. Your next SEO report could be the easiest one you have ever prepared.