Efficient Content Optimization with Google Search Console & Apps Script

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.

Transcript

2. Katarina Dahlin | #WTSFest It all started 2.5 years ago when I started my flower blog…
3. Katarina Dahlin | #WTSFest I started to have a good amount of organic traffic, so I wanted a good way to optimize old content (instead of just creating new and more)
4. Katarina Dahlin | #WTSFest I looked into Google Search Console to: 1. Find keywords that are ranking well but missing from the content, to add them to the content. 2. Optimize titles by adding keywords
5. Why optimize existing content?
6. Katarina Dahlin | #WTSFest → 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 more long tail keywords.
7. Katarina Dahlin | #WTSFest → 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. So look to optimize for your winning content.
8. Katarina Dahlin | #WTSFest → Less effort to optimize existing pages than creating new pages Quality over quantity matters in 2025.
9. Google Search Console
10. Katarina Dahlin | #WTSFest Google Search Console is amazing to find new keywords and optimizing old content, but there are a few problems when using only the interface…
11. Katarina Dahlin | #WTSFest Filtering leaves out data, because of privacy reasons. You don’t see all search queries, clicks, impressions.
12. Katarina Dahlin | #WTSFest You can’t combine keyword and landing page to see which keywords are ranking on which pages.
13. Solution: Get the raw Google Search Console data to Google Sheets
14. Search Analytics for Sheets —-- OR ---- Supermetrics Tools Google Search Console
15. Free up to 25.000 rows searchanalyticsforsheets.com
16. 29-399€ / month supermetrics.com
17. Katarina Dahlin | #WTSFest Take the raw data. Don’t filter yet here, because filtering leaves out info. Combine Query & Landing page. Katarina Dahlin | #WTSFest
18. Katarina Dahlin | #WTSFest Dimensions: Page, Query Metrics: Clicks, Impressions, CTR & Position Katarina Dahlin | #WTSFest
19. Now you have the biggest data set you can get from Search Console Katarina Dahlin | #WTSFest
20. Katarina Dahlin | #WTSFest You can now already: - see all keywords a page is ranking on - see clicks, impr. & pos. per query & landing page - filter without data getting lost
21. Get more data with Google Apps Script for better analysis
22. Check if you are using the keyword in your content
23. Katarina Dahlin | #WTSFest To rank well, it’s important to include search terms that people use.
24. Katarina Dahlin | #WTSFest Is the keyword ranking on position 5-30 found on the page?
25. Katarina Dahlin | #WTSFest If not, add to page → low hanging fruit 🍎 → you get more long tail keywords to rank better.
26. Let’s check if the keyword is found on the page with Google Apps Script
27. Katarina Dahlin | #WTSFest What is Google Apps Script? A scripting language that can automate some tasks in Google Sheets, to save you time on manual tasks.
28. Katarina Dahlin | #WTSFest Guide to Google Apps Script by Johanna Maier: https://www.oncrawl.com/wp-content/uploads/2024/06/Women-in-Tech-SEO-Festival -Berlin-Johanna-Maier-Google-Apps-Script.pdf
29. Katarina Dahlin | #WTSFest 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.
30. Katarina Dahlin | #WTSFest 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. Get the Google Apps script: katarinadahlin.com/content-optimization
31. Katarina Dahlin | #WTSFest → Test the Google Apps Script and modify and improve it to fit your needs – ask ChatGPT to do edits to you script!
32. Katarina Dahlin | #WTSFest Find Apps Script in Google Sheet Extensions
33. Katarina Dahlin | #WTSFest Create a new script
34. Katarina Dahlin | #WTSFest @katarinadahlin.bsky.social | #WTSFest Start from blank and add the Script
35. Katarina Dahlin | #WTSFest @katarinadahlin.bsky.social | #WTSFest Get the Google Apps script: katarinadahlin.com/content-opti mization Can look like this
36. Katarina Dahlin | #WTSFest @katarinadahlin.bsky.social | #WTSFest Run the Script!
37. Katarina Dahlin | #WTSFest @katarinadahlin.bsky.social | #WTSFest
38. Get the result if the keyword is found or not found on the page Katarina Dahlin | #WTSFest
39. Katarina Dahlin | #WTSFest Take the not found words ranking on pos 5-30 and add them to the page that is ranking 📈
40. Create a visual Looker Studio view for your common used Search Console filters & optimization work
41. Add filters Follow clicks & impressions
42. URL + Keyword + GSC data + Found/Not found Update data as needed, every 28 days, or every 3 months, or every 6 months
43. Katarina Dahlin | #WTSFest Marimekko view Iittala view Branded queries excluded Own page per filter
44. Katarina Dahlin | #WTSFest Filter views by: - branded searches - non-branded searches - product pages - category pages - Blog pages - FAQ searches - Keyword or topic clusters
45. In summary
46. Katarina Dahlin | #WTSFest Improve the winning pages by re-optimizing content with Google Search Console data → Get the raw data combining Query + Landing page to Google Sheets
47. Katarina Dahlin | #WTSFest Get more data with Google Apps Scripts → check if a keyword is found on page → if not, optimize!
48. Katarina Dahlin | #WTSFest Build a visual Looker Studio for content optimization with commonly used filters
49. Katarina Dahlin | #WTSFest Resources 1. How to - video about working with content optimization using Search Console & Apps Scripts 2. Apps Script: Get titles 3. Apps Script: Word found on page or not https://katarinadahlin.com/content-optimization
50. Katarina Dahlin | #WTSFest Thank you !
51. Katarina Dahlin katarinadahlin.com linkedin.com/in/katarina-dahlin youtube.com/@KatarinaDahlinSEO Get my slides: katarinadahlin.com/content-optimization