Efficient Content Optimization with Google Search Console & Apps Script

In June 2025 I spoke about Efficient Content Optimization with Google Search Console & Apps Script at WTS FEST Berlin. Find my speaker slides, ‘How to video’ and my Google Apps Scripts that I use for content optimization with Google Search Console data. In this practical talk, you’ll learn how to use Google Search Console data together with Google App Script to re-optimize content, uncover new ideas, and automate some SEO tasks to save time.

https://speakerdeck.com/katarinadahlin/wtsfest-berlin-25

Katarina is a Senior Growth Hacker & SEO Consultant at Genero. With over 6 years of experience in SEO and digital marketing, Katarina efficiently drives organic growth for her clients.

Read also my interview by Women in Tech SEO a while ago.

In this practical talk, you’ll learn how to use Google Search Console data together with Google App Script to re-optimize content, uncover new ideas, and automate some SEO tasks to save time.

Why optimize existing content?
→ Improve the winning pages
If Google already likes your page and is ranking it high, it is easier to get the same page to rank on long tail keywords.
→ More pages does not equal more organic traffic.
You don’t always need new content. 20% of the pages might bring in 80% of the traffic/leads/revenue.
→ Less effort to optimize existing pages than creating new and more.
Content quality matters today more than ever.

I wanted a good way to optimize old content.

Get all Search Console data to a sheet
→ Combine keyword & landing page
→ Use tools like ‘Search Analytics for Sheets’ or ‘Supermetrics’ to get the raw data.

In Google Sheets Use Google Apps Script to get more data
→ Check if words are found in the content
→ Import titles

Start analyzing the data here or create a visual Looker studio for the optimization work.

Optimize!
→ Find keywords that are ranking well but missing from the content. Add them to the content.
→ Optimize titles by adding keywords
to the content
→ Spot cannibalizations

Google Search Console data Google Search Console is amazing to find new keywords and optimizing old content, but there are a few problems with using only the interface…
→ Filtering leaves out data (search queries, clicks, impressions), because of privacy reasons. As an example 35% of queries on my flower blog were hidden.
→ It’s a bit clumsy to work in.
→ You can only export 1000 search queries at a time.

Solution: Get the raw Google Search Console data to a Google Sheet Search.
Use tools like ‘Search Analytics for Sheets’ or ‘Supermetrics’.
→ Search Analytics for Sheets is free up to 25.000 rows.
→ Supermetrics is 29-399€ / month, but with this tool you can extract other data easily into Google Sheets as well, like Analytics data or ads data.

Both tools work fine and in similar ways! See settings in the image below.

Take the raw data.
Don’t filter yet here, because filtering leaves out info.
Combine Query & Landing page.
Dimensions: Page, Query
Metrics: Clicks, Impressions, CTR & Position

→ Now you have the biggest data set that you can get with API.
You can now already:
→ See all keywords a page is ranking on
→ Filter easily without data getting lost
→ See clicks, impressions and positions per search query and landing page

What is Google Apps Script?
→ Google Apps Script is a scripting language that can automate some tasks in Google Sheets, to save you time on manual tasks.

The ways I use it with Google Search Console data is:

1. Spot cannibalizations

Spot cannibalizations by combining keyword & landing page.
→ Sort by queries
→ Look at the Page, Impressions & Position
→ Look at what page is performing best?

Consider:
→ combining page content
→ remove the less performing pages
→ if you can’t remove pages if e.g. products, add internal links to the best performing page.

2. Optimize titles

The easiest way to improve rankings is to optimize meta titles.

→ Scrape and import the meta titles with Google Apps Script or Screaming Frog.
→ Sort by landing page to find keywords to add to your titles.

Meta title checker Apps Script part 1.

This meta title Apps Scripts fetches the meta title to column I from the URL in column A in the Google Sheet.

function importMetaTitle() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const lastRow = sheet.getLastRow();
  const startRow = Number(PropertiesService.getScriptProperties().getProperty('lastProcessedRowMetaTitle')) || 2; // Start at saved row or row 2
  const chunkSize = 100; // Process 100 rows per run

  Logger.log(`Starting from row: ${startRow}`);

  for (let i = startRow; i <= lastRow && i < startRow + chunkSize; i++) {
    const url = sheet.getRange(i, 1).getValue();
    if (!url) continue; // Skip empty URL cells

    try {
      // Fetch the URL and allow non-200 responses without throwing an exception
      const response = UrlFetchApp.fetch(url, {muteHttpExceptions: true});
      const responseCode = response.getResponseCode();
      
      // If the response code is not 200, record the error code in column I and continue to the next row
      if (responseCode !== 200) {
        sheet.getRange(i, 9).setValue("Error: " + responseCode);
        continue;
      }
      
      // Get the HTML content and extract the <title> tag
      const htmlContent = response.getContentText();
      const titleMatch = htmlContent.match(/<title[^>]*>([^<]+)<\/title>/i);
      
      if (titleMatch && titleMatch[1]) {
        const metaTitle = titleMatch[1].trim();
        sheet.getRange(i, 9).setValue(metaTitle);
      } else {
        sheet.getRange(i, 9).setValue("No title found");
      }
    } catch (e) {
      sheet.getRange(i, 9).setValue("Error: " + e.message);
    }
  }

  // Save progress: update the next starting row or clear progress if complete
  const nextRow = startRow + chunkSize;
  if (nextRow <= lastRow) {
    PropertiesService.getScriptProperties().setProperty('lastProcessedRowMetaTitle', nextRow);
    Logger.log(`Saved progress. Next start row: ${nextRow}`);
  } else {
    PropertiesService.getScriptProperties().deleteProperty('lastProcessedRowMetaTitle');
    Logger.log('Processing complete or reached row limit!');
  }
}

Meta title checker Apps Script part 2.

This Apps script resets the script 1 to start fetching the titles again from row 2 and not where it last time left off.

// Reset the meta title progress to start the script from row 2
function resetMetaTitleProgress() {
  PropertiesService.getScriptProperties().deleteProperty('lastProcessedRowMetaTitle');
  Logger.log('Meta title progress has been reset. The script will start from row 2.');
}

// Check the saved meta title progress to see where the script left off
function checkMetaTitleProgress() {
  const progress = PropertiesService.getScriptProperties().getProperty('lastProcessedRowMetaTitle');
  Logger.log('Meta title - Last processed row: ' + (progress || 'None (reset or not started yet)'));
}

3. Find questions for your FAQ section

Find FAQs to optimize for helpful content.
→ Add the question as a H2 to you content, try to get visible in a featured snippet or people also ask section.

4. Check if you are using the keyword in your content

To rank well in search results, it’s important to include keywords in your content that match the search terms people use.

→ Is the keyword ranking on pos 5-30 found on the page?
If not, add to page! It’s a low hanging fruit 🍎

Adding well-ranking but missing keywords to the content will help them ranker better.
→ works on lower competition words
→ you get more long tail keywords to rank better

How to build the Google Apps Script
→ Ask ChatGPT to create you the Google App Script for you, just explain what you want to do.
→ Test the Google Apps Script and modify and improve it to fit your needs – ask ChatGPT to do edits to you script!

Example prompt to start with:
Create a Google App Script to check if the keyword in column B is found in column A on the same row, and add the result back in column G as not found or found.

You can also use my Google Apps Script for checking if the keyword is found on the site or not. Modify it with ChatGPT if you are checking different columns or if you want the result back to a different column.

This script is also two folded. The first script that checking if the keyword is found on the page or not. This script continues from the next row where it left off. The other script is for resetting from which row to start with, and running the script makes it start checking from row 2 again.

Google Apps Script for checking if the keyword is found on the page or not part 1.

Run this script to check if the keyword in column B is found or not found on the page in column A. The results will be added back to column G as Found or Not found. It checks 100 rows at one run. It continues from the next row when running the script again.

function checkKeywordInPage() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const lastRow = sheet.getLastRow();
  const maxRows = 8000; // Limit to 8000 rows
  const startRow = Number(PropertiesService.getScriptProperties().getProperty('lastProcessedRow')) || 2; // Start at saved row or row 2
  const chunkSize = 100; // Process 100 rows per run

  Logger.log(`Starting from row: ${startRow}`);

  for (let i = startRow; i <= lastRow && i < startRow + chunkSize && i <= maxRows; i++) {
    const url = sheet.getRange(i, 1).getValue();
    const keyword = sheet.getRange(i, 2).getValue().toLowerCase(); // Convert keyword to lowercase

    try {
      // Use muteHttpExceptions to handle non-200 responses without throwing an error
      const response = UrlFetchApp.fetch(url, {muteHttpExceptions: true});
      const responseCode = response.getResponseCode();
      
      // If URL returns a 404 error, mark it and skip keyword checking.
      if (responseCode === 404) {
        sheet.getRange(i, 7).setValue("404");
        continue;
      }
      
      const content = response.getContentText().toLowerCase(); // Convert page content to lowercase

      // Replace spaces in the keyword with optional hyphen regex pattern
      const modifiedKeyword = keyword.replace(/\s+/g, '[-\\s]*');
      const keywordRegex = new RegExp(modifiedKeyword, 'i'); // Case-insensitive regex for the modified keyword

      if (keywordRegex.test(content)) {
        sheet.getRange(i, 7).setValue("Found");
      } else {
        sheet.getRange(i, 7).setValue("Not Found");
      }
    } catch (e) {
      sheet.getRange(i, 7).setValue("Error: " + e.message);
    }
  }

  // Save progress
  const nextRow = startRow + chunkSize;
  if (nextRow <= lastRow && nextRow <= maxRows) {
    PropertiesService.getScriptProperties().setProperty('lastProcessedRow', nextRow);
    Logger.log(`Saved progress. Next start row: ${nextRow}`);
  } else {
    PropertiesService.getScriptProperties().deleteProperty('lastProcessedRow'); // Reset when done
    Logger.log('Processing complete or reached row limit!');
  }
}

function resetProgress() {
  // Manually reset the progress
  PropertiesService.getScriptProperties().deleteProperty('lastProcessedRow');
  Logger.log('Progress has been reset.');
}

Google Apps Script for checking if the keyword is found on the page or not part 2.

This script resets the first script to start again from row 2.

// Reset the progress to start the script from row 2
function resetProgress() {
  PropertiesService.getScriptProperties().deleteProperty('lastProcessedRow');
  Logger.log('Progress has been reset. The script will start from row 2.');
}

// Check the saved progress to see where the script left off
function checkProgress() {
  const progress = PropertiesService.getScriptProperties().getProperty('lastProcessedRow');
  Logger.log('Last processed row: ' + (progress || 'None (reset or not started yet)'));
}

→ Find Apps Script in Google Sheet Extensions.
→ Create a new script.
→ Start from blank and add the Script.
→ Run the Script!
→ Get the result if the keyword is found or not found on the page.
→ Take the not found words and add them to the page that is ranking 📈

Own page per filter. Example of filter views:
→ Branded queries only
→ Brand queries excluded
→ Product pages
→ Category pages
→ Blog pages
→ FAQ searches
→ Keywords or topics

Add possibility to filter inside the Lookerstudio view.
Follow clicks & impressions (data directly from Google Search Console).
Have your Keyword + Landing page data with Clicks, Impressions, CTR, Position and Found/Not found data.
Update data as needed, every 28 days, or every 3 months, or every 6 months.

Example of one of my filter pages.

Improve the winning pages by re-optimizing with Google Search Console data.

To get all data possible export all Search Console data to a Google sheet.
Combine search query + landing page.

Tools:
→ Supermetrics
→ Search Analytics for Sheets.

Get more data with Apps Scripts & optimize!
→ meta titles
→ check if a keyword is found on page

Build a Looker Studio for content optimization.
→ filter views for different content clusters or page types

Thank you for reading this far!

/ Katarina