Efficient Content Optimization with Google Search Console & Apps Script

by Katarina Dahlin

Slide 1

Slide 1 text

No content

Slide 2

Slide 2 text

Katarina Dahlin | #WTSFest It all started 2.5 years ago when I started my ﬂower blog…

Slide 3

Slide 3 text

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)

Slide 4

Slide 4 text

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

Slide 5

Slide 5 text

Why optimize existing content?

Slide 6

Slide 6 text

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.

Slide 7

Slide 7 text

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.

Slide 8

Slide 8 text

Katarina Dahlin | #WTSFest → Less effort to optimize existing pages than creating new pages Quality over quantity matters in 2025.

Slide 9

Slide 9 text

Google Search Console

Slide 10

Slide 10 text

Katarina Dahlin | #WTSFest Google Search Console is amazing to ﬁnd new keywords and optimizing old content, but there are a few problems when using only the interface…

Slide 11

Slide 11 text

Katarina Dahlin | #WTSFest Filtering leaves out data, because of privacy reasons. You don’t see all search queries, clicks, impressions.

Slide 12

Slide 12 text

Katarina Dahlin | #WTSFest You can’t combine keyword and landing page to see which keywords are ranking on which pages.

Slide 13

Slide 13 text

Solution: Get the raw Google Search Console data to Google Sheets

Slide 14

Slide 14 text

Search Analytics for Sheets —-- OR ---- Supermetrics Tools Google Search Console

Slide 15

Slide 15 text

Free up to 25.000 rows searchanalyticsforsheets.com

Slide 16

Slide 16 text

29-399€ / month supermetrics.com

Slide 17

Slide 17 text

Katarina Dahlin | #WTSFest Take the raw data. Don’t ﬁlter yet here, because ﬁltering leaves out info. Combine Query & Landing page. Katarina Dahlin | #WTSFest

Slide 18

Slide 18 text

Katarina Dahlin | #WTSFest Dimensions: Page, Query Metrics: Clicks, Impressions, CTR & Position Katarina Dahlin | #WTSFest

Slide 19

Slide 19 text

Now you have the biggest data set you can get from Search Console Katarina Dahlin | #WTSFest

Slide 20

Slide 20 text

Katarina Dahlin | #WTSFest You can now already: - see all keywords a page is ranking on - see clicks, impr. & pos. per query & landing page - ﬁlter without data getting lost

Slide 21

Slide 21 text

Get more data with Google Apps Script for better analysis

Slide 22

Slide 22 text

Check if you are using the keyword in your content

Slide 23

Slide 23 text

Katarina Dahlin | #WTSFest To rank well, it’s important to include search terms that people use.

Slide 24

Slide 24 text

Katarina Dahlin | #WTSFest Is the keyword ranking on position 5-30 found on the page?

Slide 25

Slide 25 text

Katarina Dahlin | #WTSFest If not, add to page → low hanging fruit 🍎 → you get more long tail keywords to rank better.

Slide 26

Slide 26 text

Let’s check if the keyword is found on the page with Google Apps Script

Slide 27

Slide 27 text

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.

Slide 28

Slide 28 text

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

Slide 29

Slide 29 text

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.

Slide 30

Slide 30 text

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

Slide 31

Slide 31 text

Katarina Dahlin | #WTSFest → Test the Google Apps Script and modify and improve it to ﬁt your needs – ask ChatGPT to do edits to you script!

Slide 32

Slide 32 text

Katarina Dahlin | #WTSFest Find Apps Script in Google Sheet Extensions

Slide 33

Slide 33 text

Katarina Dahlin | #WTSFest Create a new script

Slide 34

Slide 34 text

Katarina Dahlin | #WTSFest @katarinadahlin.bsky.social | #WTSFest Start from blank and add the Script

Slide 35

Slide 35 text

Katarina Dahlin | #WTSFest @katarinadahlin.bsky.social | #WTSFest Get the Google Apps script: katarinadahlin.com/content-opti mization Can look like this

Slide 36

Slide 36 text

Katarina Dahlin | #WTSFest @katarinadahlin.bsky.social | #WTSFest Run the Script!

Slide 37

Slide 37 text

Katarina Dahlin | #WTSFest @katarinadahlin.bsky.social | #WTSFest

Slide 38

Slide 38 text

Get the result if the keyword is found or not found on the page Katarina Dahlin | #WTSFest

Slide 39

Slide 39 text

Katarina Dahlin | #WTSFest Take the not found words ranking on pos 5-30 and add them to the page that is ranking 📈

Slide 40

Slide 40 text

Create a visual Looker Studio view for your common used Search Console ﬁlters & optimization work

Slide 41

Slide 41 text

Add ﬁlters Follow clicks & impressions

Slide 42

Slide 42 text

URL + Keyword + GSC data + Found/Not found Update data as needed, every 28 days, or every 3 months, or every 6 months

Slide 43

Slide 43 text

Katarina Dahlin | #WTSFest Marimekko view Iittala view Branded queries excluded Own page per ﬁlter

Slide 44

Slide 44 text

Katarina Dahlin | #WTSFest Filter views by: - branded searches - non-branded searches - product pages - category pages - Blog pages - FAQ searches - Keyword or topic clusters

Slide 45

Slide 45 text

In summary

Slide 46

Slide 46 text

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

Slide 47

Slide 47 text

Katarina Dahlin | #WTSFest Get more data with Google Apps Scripts → check if a keyword is found on page → if not, optimize!

Slide 48

Slide 48 text

Katarina Dahlin | #WTSFest Build a visual Looker Studio for content optimization with commonly used ﬁlters

Slide 49

Slide 49 text

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

Slide 50

Slide 50 text

Katarina Dahlin | #WTSFest Thank you !

Slide 51

Slide 51 text

Katarina Dahlin katarinadahlin.com linkedin.com/in/katarina-dahlin youtube.com/@KatarinaDahlinSEO Get my slides: katarinadahlin.com/content-optimization