substrakt

Welcome to part two of my discussion about Google’s latest page title update.

If you’ve missed out on part one (which covers what the page update is and how it differs from previous updates) go back and read that first, then join me back here to learn how you can find out exactly which of your page titles have been affected.

As you’ll see from part one, a lot of the discussion so far seems to be a case of “this element could be used as your page title, or it could be that element instead”, and it’s hard to know in what ways your website is actually affected.

Luckily, I came across a wonderful do-it-yourself resource (thanks Women in Tech SEO) that lets you see page titles and meta descriptions you have set vs what appears in Google for selected keywords and phrases. It might seem a little scary to set up yourself as it involves Google Sheets Script Editor and APIs, but I promise it’s an easy one and worth its weight in gold! All credit for this solution goes to Andrew Charlton at Keywords in Sheets.

Google Sheets Title (& meta description) Checker setup

To start, you need to create your own copy of the title & meta description checker spreadsheet. Once opened, click File > Make a Copy, and you’re all set with your own.
Next, you want to sign up to rapidAPI – it’s free, and you can even use your Google or Facebook account to sign up.

In rapidAPI you want to subscribe to the Google Search API. All you need is the free basic plan (you’ll get 600 hard requests / month: more than enough for what we’re doing here).
Next, you’ll need to find your API Key. To get this, head to your rapidAPI dashboard, then on the left-hand side menu, head to My Apps > default-application_xxxxxx > Security. Here you will see your API Key.

Now head to the Google Sheet you made a copy of in step one, then click Tools > Script Editor. This will open up a code editor in a new window.
I now have a bit of code for you. In short, this code uses the Google Search API to look for your site (set by domain) in the search results for the specified keywords and language, looks at the page titles & descriptions you’ve set vs what appears in Google, and lets you know whether they are different or not.In the code editor box, copy and paste the following code:

function onOpen() {
    const ui = SpreadsheetApp.getUi();
    ui.createMenu('Title Checker')
        .addItem('Check Titles', 'titlechecker')
        .addToUi();
}

function titlechecker() {

  // Add Moment.js library
  eval(UrlFetchApp.fetch('https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.29.1/moment.min.js').getContentText());

    // add API key from https://rapidapi.com/apigeek/api/google-search3
    const apiKey = "";

    // get active spreadsheet
    const ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

    // function to get last row of single column
    const getLastRowCol = (range) => {
        let rowNum = 0;
        let blank = false;
        for (let row = 0; row < range.length; row++) {

            if (range[row][0] === "" && !blank) {
                rowNum = row;
                blank = true;

            } else if (range[row][0] !== "") {
                blank = false;
            };
        };
        return rowNum;
    }

    // range variables to make sure data can always be appended
    const titleRange = ss.getRange('B:B').getValues();
    const titleLastRow = getLastRowCol(titleRange) + 1;
    const lastRowDiff = ss.getLastRow() - titleLastRow + 1;
    const queryRange = ss.getRange(titleLastRow, 1, lastRowDiff).getValues();

    // domain and language variables
    const domain = ss.getRange('J2').getValue().replace(/^(?:https?:\/\/)?(?:www\.)?/i, "").split('/')[0];
    const country = ss.getRange('K2').getValue();
    const language = ss.getRange('L2').getValue();

    // function to fetch SERPs from rapidapi
    const fetchSerps = (keyword, language, country) => {
        try {
            const options = {
                'method': 'GET',
                'contentType': 'application/json',
                'headers': {
                    'x-rapidapi-key': apiKey,
                    'x-rapidapi-host': 'google-search3.p.rapidapi.com'
                }
            };

            const serpResponse = UrlFetchApp.fetch(`https://google-search3.p.rapidapi.com/api/v1/search/q=${keyword}&gl=${country}&hl=lang_${language}&num=100`, options);

            const content = JSON.parse(serpResponse.getContentText());

            const organicResults = content.results;

            let row = [],
                data;
            for (i = 0; i < organicResults.length; i++) {

                let data = organicResults[i];

                const link = data.link;

                // if any of the top 100 ranking URLs include the domain, return data
                if (link.includes(domain)) {

                    // scrape the ranking URL
                    const urlResponse = UrlFetchApp.fetch(data.link).getContentText();

                    const $ = Cheerio.load(urlResponse);

                    // extract the title
                    const title = $('title').first().text().trim();
          const description = $('meta[name="description"]').attr('content').trim();

                    // check whether ranking title is different to page title
                    const changed = title !== data.title ? "Changed" : "Unchanged";
          const changed2 = description !== data.description? "Changed" : "Unchanged";

                    row.push(title, data.title, changed, description,data.description,changed2);

                    return row;

                }
            }


        } catch (e) {
            return false;
        }
    }

    // loop over remaining URLs and set values while the loop is running
    queryRange.forEach(function(row, i) {
        row.forEach(function(col) {

            // visually display a fetch status. Script inspired by https://script.gs visually-display-status-when-looping-through-google-sheets-data/
            ss.getRange(titleLastRow + i, 9).setValue("Loading...");

            SpreadsheetApp.flush();

            const check = fetchSerps(col, language, country);

            // if the SERPs check is successful, return row
            check ? ss.getRange(titleLastRow + i, 2, 1, 6).setValues([check]) : ss.getRange(titleLastRow + i, 2).setValue("No data");
            ss.getRange(titleLastRow + i, 9).setValue("Done");

            // Add timestamp of extraction
      ss.getRange(titleLastRow + i, 8).setValue(moment().format('llll'))

            SpreadsheetApp.flush();

        });
    });

}

In line 14 of the code, paste your API Key (found in step 4) in between the quotation marks. Then press ▶ Run to run and save the code.
You now need to add a library to your Script Editor app. The library is called Cheerio, and it makes it easier for the script editor to understand the HTML of the pages on your website. To add Cheerio to your Script Editor, press the + icon next to Libraries in the left-hand menu of the script editor, and search for it using this id:

1ReeQ6WO8kKNxoaA_O0XEQ589cIrRvEBA9qcWpNqdOP17i47u6N9M5Xh0

Once you’ve added Cheerio, you can close the Script Editor window, and head back to your Google Sheet. Once here, head to Sheet 2 (‘Script’), and make sure you have the headings:
- Query
- Page title
- Google Search title
- Changed?
- Meta description
- Google Search description
- Changed?
- Timestamp
- Domain
- Country
- Language
Under the Query header, delete the current keywords and input as many keywords and phrases you’d like to check for your website: I suggest heading to Google Search Console, and copying your top 10, 20, 50, or 100 keywords to check against.
Under the domain heading, change from shopify.com to your own site’s domain. (Option here to also edit the country and language you’d like the run the script against).

Look in the toolbar; you should now have an item called Title Checker. Click this, and then Check Titles.
Now sit back and watch as your spreadsheet fills with the titles and meta descriptions you have set for your site vs what appears in Google for the given keywords, domain, country and language*.

*the titles and descriptions pulled from Google refer to the first result. More pages on your site may appear for that keyword or phrase, this script just gives you the information about the first one that appears in the search results.

I’ve done it! What now?

You’re amazing! You now have a concrete list of your page titles, and under which circumstances Google might be changing them. This gives you invaluable insight into how Google and your website users expect to view and use your site.

Even if you think some of the changes made by Google aren’t as good as what you’ve written, you can use this to your advantage to update your content, title tags, and descriptions to something you would like to appear in Google instead. Just don’t forget to keep the user at the centre of everything you do.

It’s worth mentioning here that even though Google may change your page title to something different from what appears in your title tag, it still uses whatever is in your title tag in its ranking algorithm. Meaning, even if Google doesn’t show what you’ve written in your page title tag, you aren’t getting penalised, and whatever is in there counts towards your overall performance when it comes to SEO.

Hopefully, now that you’ve finished these two articles, you’re a little more familiar with the latest Google update around page titles, how it differs from previous updates, and how this has actually affected your own website and therefore your users. Even though there appears to be a few tweaks for Google to iron out, this update will see a better search experience for content creators and users alike.

Let me know how you’ve got on using this method to identify your changed page titles, and as always, if you have any questions, please feel free to send me an email at kate.arnold@substrakt.com.

Google Sheets Title (& meta description) Checker setup

I’ve done it! What now?

Get all the good stuff