In this guide, I’ll cover in detail how to make the best use of keywords in three steps:

1. Finding good keywords: keywords that are rankable and bring value to your site.
2. Using keywords in content and meta tags: how to use the target keyword to structure and write content that will satisfy readers and send relevance signals to search engines.
3. Tracking keywords: monitoring your (and your competitors’) performance.

There’s really a lot you can do with just a single keyword, so at the end of the article, you’ll find a few advanced SEO tips.

1. Pick relevant seed keywords to generate keyword ideas

Seed keywords are words or phrases that you can use as the starting point in a keyword research process to unlock more keywords. For example, for our site, these could be general terms like “seo, organic traffic, digital marketing, keywords, backlinks”, etc.

There are many good sources of seed keywords, and it’s not a bad idea to try them all:

• Brainstorming. This involves gathering a team or working solo to think deeply about the terms your potential customers might use when searching for your products or services.
• Your competitors’ website navigation. The labels they use in their navigation menus, headers, and footers often highlight critical industry terms and popular products or services that you might also want to target.
• Your competitors’ keywords. Tools like Ahrefs can help you discover which keywords your competitors are targeting in their SEO and paid ad campaigns. I’ll cover that in a bit.
• Your website and promo materials. Review your website’s text, especially high-performing pages, as well as any promotional materials like brochures, ads, and press releases. These sources can reveal the terms that already resonate with your audience.
• Generative AI. AI tools can generate keyword ideas based on brief descriptions of your business, products, or industry (example below).

Here’s what you can ask any generative AI for, whether that’s Copilot, ChatGPT, Perplexity, etc.

Next, paste your seed keywords into a tool like Ahrefs Keywords Explorer to generate keyword ideas. If you’re using Ahrefs, you can go straight into Keywords Explorer, get AI suggestions there, and start researching right away.

Next, make sure you’ve set up the country in which you’ll want to rank and hit “Search”.

After hitting the “Search” button, go to the Matching terms report. You will see a big list of keywords.

The list you’ll get will be quite raw — not all keywords will be equally good and the list will likely be too big to manage. Next steps are all about refining the list because we’ll be looking for target keywords — the keywords that will become the topic of your content.

2. Refine the list and cluster

• KD (Keyword Difficulty): how difficult it would be to rank on the first page of Google for a given keyword.
• Traffic potential: traffic you can get for ranking #1 for that keyword and other relevant keywords (based on the page that currently ranks #1).
• Lowest DR (Domain Rating): plug in the DR of your site to see keywords where another site with the same DR already ranks in the top 10. In other words, it helps to find “rankable” keywords.
• Target: one of the main use cases is excluding keywords you already rank for.
• Include/Exclude: see keywords that contain specific words to increase relevancy/hide keywords with irrelevant words.

For example, here’s how to find potentially rankable keywords with traffic potential above 300 monthly visits. Go to the Matching terms report in Keywords Explorer and set filters: keyword difficulty filter (KD) to your site’s Domain Rating, Traffic potential, and Volume filters to a minimum of 300.

Clustering is another step to refine your list. It shows you if there is another keyword you could target to get more traffic (aka parent topic). At the same time, it shows which keywords most likely belong on the same page.

For example, here are some clusters distilled from low-competition topics about marketing.

Pro tip

Take keyword trends into account when choosing keywords.

For example, the keyword “is affiliate marketing legit” is at 8.8k monthly search volume right now, but based on our forecast in Keywords Explorer (the orange part of the chart), if it continues its current growth rate it should be more than triple next year.

Advertisement

The graphs will also show you if the search volume is affected by seasonality (fluctuations in search volume throughout different times of the year).

4. Identify search intent and determine value for your site

Before investing time in content, make sure you can give searchers what they want and that the keywords will attract the right kind of audience.

To identify the type of page you need to create to satisfy searchers, look at the top-ranking pages to see what purpose they serve (are they more informational or commercial), or simply use the Identify intents AI feature in Keywords Explorer.

So, for example, if the top-ranking pages are ecommerce pages and you’re not offering products on your site, it’s going to be very hard to rank.

Then, ask yourself if visitors attracted by a keyword will be valuable to your business — whether they’re likely to subscribe to your newsletter or make a purchase. At Ahrefs, we use a business potential score to evaluate this.

Finally, if a keyword checks all boxes, add it to a keyword list.

Now you’ve got a list of relevant, valuable target keywords with traffic potential ready for content creation. You can repeat the process as many times as you like with different seed keywords or different filters and find new ideas.

There’s one more great source of keywords — competitors.

5. Enrich the list with your competitors’ keywords

1. Enter your domain in Ahrefs’ Site Explorer and go to the Organic competitors report.
2. Select the most relevant competitors and click on Copy (this copies URLs — we’ll use them in another tool).

Next, we’ll see which keywords you’re missing.

1. Go to Ahrefs’ Competitive Analysis tool, paste the previously copied URLs, enter your domain on top and hit Show keyword opportunities.

1. In the Content gap report, use filters to refine the report.
2. Select keywords and add them to your list.

Pro tip

If you stumble across two similar keywords there’s an easy way to determine if they belong on the same page.

• Enter the keyword in Keywords Explorer.
• Scroll to SERP overview, click Compare with, and enter the keyword to compare with.

• Fewer common results and low SERP (Search Engine Result Page) similarity mean separate pages should target the two keywords.

Use the target keyword to determine the search intent

Search intent is the reason behind the search. Understanding it tells you what users are looking for and what you need to deliver in your content.

To identify search intent, look at the top-ranking results for your target keyword on Google and identify the three Cs of search intent:

• Content type – What is the dominating type of content? Is it a blog post, product page, video, or something else? If you’ve done that during the keyword research phase (highly recommended), only two elements to go.
• Content format – Some common formats include how-to guides, list posts, reviews, comparisons, etc.
• Content angle – The unique selling point of the top-ranking points, e.g., “best,” “cheapest,” “for beginners,” etc. Provides insight into what searchers value in a particular search.

For example, most top-ranking pages for “avocado seed” are blog posts serving as how-to guides for planting the seed. The use of easy and simple angles indicates that searchers are beginners looking for straightforward advice.

Use the target keyword in the URL slug

https://ahrefs.com/blog/how-to-use-keywords-seo

Google says to use words that are relevant to your content inside page URLs (source). Usually, the easiest way to do that is to set your target keyword in the slug part of the URL.

Use the target keyword in the title and match it with the H1 tag

A title tag is a bit of HTML code used to specify the title of a webpage.

<title>How to Use Keywords for SEO: A-Z Guide For Beginners</title>

The H1 tag is an HTML heading that’s most commonly used to mark up a web page title.

<h1>How to Use Keywords for SEO: A-Z Guide For Beginners</h1>

Both are very important to Google and searchers. Since they both indicate what the page is about, you can just match them, like I did in this article.

Titles help Google understand the context of a page. What’s more, even a slight improvement to your title can improve your rankings.

Google advises focusing on creating good titles, which should be “unique to the page, clear and concise, and accurately describe the contents of the page” (source). It’s hard to think of a better way to accurately describe the contents other than using the target keyword.

If it makes sense for the title, aim for an exact match of the keyword. But if you need to insert a preposition or break the phrase, this won’t make Google think your page is less relevant. Google understands close variations of the keyword really well, so there’s no need to stuff in similar keywords, misspellings, etc.

To illustrate, my old article on how to see keywords that Google Analytics won’t show ranks #1 for many variations of the phrase in the title.

Use the keyword in meta description

However, don’t write meta descriptions solely for Google; Google rewrites them more than half of the time (study) and doesn’t use them for ranking purposes. Instead, focus on crafting meta descriptions for searchers.

These descriptions appear in the SERPs, where users can read them. If your description is relevant and compelling, it can increase the likelihood of users clicking on your link.

Including your target keyword in the meta description is usually natural. For instance, consider the description of the article mentioned earlier. Incorporating the keyword into the sentences simply provides a comprehensive way to describe the issue.

Use the target keyword to find secondary keywords

Secondary keywords are any keywords closely related to the primary keyword that you’re targeting with your page.

You can find them through your primary keyword and use them as subheadings (H2 to H6 tags) and talking points throughout the content. Here’s how.

Go to Keywords Explorer and plug in your target keyword. From there, head on to the Related terms report and toggle between:

• Also rank for: keywords that the top 10 ranking pages also rank for.
• Also talk about: keywords frequently mention by top-ranking articles.

Now, to know how to use these keywords in your text, just manually look at the top ranking pages and see how and where they cover the keywords.

For example, looking at one of the top articles for “digital marketing”, we can see right away that some of the most important aspects are the definition, a template and importance. You can use the free Ahrefs SEO Toolbar to break down the structure of any page instantly.

Another place you can look for inspiration is the People Also Ask Box in the SERPs. Use it to find words and subtopics that may be worth adding to the article.

Pro tip

Optimizing an existing article?

Advertisement

Use the Content Gap tool to find subtopics you may be missing. The tool shows keywords that your competitors’ pages rank for, and your page doesn’t.

• Go to Keywords Explorer and enter your target keyword.
• Scroll down to the SERP overview, select a few top pages, and click Open in Content Gap.

• In Content Gap, click on Targets and add the page you’re optimizing in the last field.

Use primary and secondary keywords in the main content

To rank high on search engines, it’s important to include your keywords in your text. Even though Google is good at understanding similar words and variations, it still helps to use the specific keywords people might search for. Google explains that in their short guide to how search works:

The most basic signal that information is relevant is when content contains the same keywords as your search query. For example, with webpages, if those keywords appear on the page, or if they appear in the headings or body of the text, the information might be more relevant.

When writing, it’s important to incorporate keywords naturally. Start your content with the most relevant information that people are likely to search for. This ensures that key points are immediately visible to your readers and search engines.

If you have a secondary keyword that’s less critical but still relevant, consider giving it a dedicated section. This approach allows you to explore the topic in detail, rather than briefly mentioning it at the end of your content.

However, avoid overemphasizing the frequency of your keywords. Effective SEO involves more than just repeating keywords. If SEO were simply about keyword density, it would be straightforward, but such strategies don’t lead to long-term success and can make your content feel spammy.

For instance, if ‘content strategy’ is a central theme of your discussion and you mention it only once, Google might perceive your content as incomplete. On the other hand, stuffing your article with the term ‘content strategy’ more than necessary won’t outperform your competitors and could potentially lead to your site being flagged as spam.

Use the target keyword in link anchor text and/or surrounding text

Google uses anchors to understand the page’s context. There even seems to be a consensus that anchor text is a ranking factor, although, according to our study, it is likely a weak one.

In situations like these, it’s just best to stick with Google’s advice:

Good anchor text is descriptive, reasonably concise, and relevant to the page that it’s on and to the page it links to. It provides context for the link, and sets the expectation for your readers. (…)

Remember to give context to your links: the words before and after links matter, so pay attention to the sentence as a whole.

So use the target keyword in the anchor text and or surrounding text but keep it natural — add only on pages that are related to the page you’re linking to and use text that will help the readers understand where and why you’re linking.

It’s pretty much an automated process; everything can be handled by a tool like Ahrefs’ Rank Tracker. No need to check rankings manually and note them down in a spreadsheet.

If you have a keyword list ready, all you need to do is add that list to Rank Tracker.

The keywords will appear in Rank Tracker’s Overview report.

Another way to add keywords is to hit Add keywords in the top right corner (best for adding single keywords or importing a list from a document).

Now to compare your performance against competitors, just go to the Competitors report. The metric I recommend tracking is SOV (Share of Voice). It shows how many clicks go to your pages compared to competitors.

One of the key advantages of SOV is that it accounts for fluctuations in search volume trends. Therefore, if you notice a decrease in traffic but maintain a high SOV, it indicates that the drop is due to a decrease in the overall popularity of the keywords, not a decline in your SEO effectiveness.

But not only can you track your competitors’ keywords, you can also monitor them. Use a tool like Ahrefs Alerts to get notifications whenever your competitors started working for a new keyword.

Just to go Alerts tool in Ahrefs and fill in the details.

1. Use keywords to find guest blogging opportunities

It’s one of the most popular link building tactics with a few other benefits like exposure to a new, targeted audience.

Here’s how to find relevant, high-quality sites to pitch:

1. Go to Ahrefs’ Content Explorer.
2. Enter a broad keyword or phrase related to your niche.
3. Select In title from the drop-down menu.
4. Run the search.

Next, refine the list by applying these filters:

• Domain Rating (DR) from 30 to 60.
• Click the One page per domain filter.
• Click the Exclude homepages filter.
• Click the Exclude subdomains filter.

Finally, click on the Websites tab to see potential websites you could guest blog for.

2. Use keywords to find internal link opportunities

Internal links take visitors from one page to another on your website. Their main purpose is to help visitors easily navigate your website, but they can also help boost SEO by aiding the flow of link equity.

Finding new internal link opportunities is also a time-consuming process if done manually, but you can identify them in bulk using Ahrefs’ Site Audit. The tool takes the top 10 keywords (by traffic) for each crawled page, then looks for mentions of those on your other crawled pages.

Click on the Internal link opportunities report in Site Audit.

You’ll see a bunch of suggestions on how to improve your internal linking using new links. The tool even suggests exactly where to place the internal link.

3. Use keywords to find link building opportunities

Link building is the process of getting other websites to link to pages on your website. Its purpose is to boost the “authority” of your pages in the eyes of Google and help your pages rank.

A good place to start is to pull up the top-ranking pages for your target keyword and research where they got their links from.

Put your keyword into Keywords Explorer and scroll down to the SERP overview. You’ll see the top-ranking pages and their number of backlinks (and linking domains).

Once you click on any of the backlink numbers, you’ll be redirected to a list of backlinks of a given page in Site Explorer.

From that point, the typical process involves identifying sites with the highest potential to boost your SEO and contacting their owners if you think they’d be willing to link to your content. We’re covering the details of this process and everything else you need to know to start with link building in this guide.

4. Avoid common keyword pitfalls

• Don’t use the same keyword excessively on a page. Repeating a keyword too frequently within a single page can lead to keyword stuffing, which is treated as spam by Google.
• Don’t use the same focus keyword across multiple pages. Each page on your website should have a unique focus keyword. Using the same keyword across multiple pages can lead to keyword cannibalization, where pages compete against each other in search results.
• Don’t sacrifice quality content for keyword usage. While keywords are essential for SEO, prioritize high-quality, informative content above all else. Don’t make your content read unnatural or too long by cramming in keywords. This won’t help you rank and will decrease content quality.
• Don’t use keywords just for the sake of using them. This means two things. First, don’t target keywords not related to your website or business — this will only bring you useless traffic. Second, don’t try to hit some keyword frequency goal which is often suggested by content optimization tools by just mentioning the keyword without any substance — SEO doesn’t work that way anymore.

Final thoughts

Got questions or comments? Find me on X or LinkedIn.

Google announced a new tool for Analytics to streamline data comparisons.

The ‘saved comparisons’ feature allows you to save filtered user data segments for rapid side-by-side analysis.

Google states in an announcement:

“We’re launching saved comparisons to help you save time when comparing the user bases you care about.

Learn how you can do that without recreating the comparison every time!”

We’re launching saved comparisons to help you save time when comparing the user bases you care about. Learn how you can do that without recreating the comparison every time! → https://t.co/29nN2MiPtm pic.twitter.com/r8924rAT05

— Google Analytics (@googleanalytics) May 8, 2024

Advertisement

Google links to a help page that lists several benefits and use cases:

“Comparisons let you evaluate subsets of your data side by side. For example, you could compare data generated by Android devices to data generated by iOS devices.”

“In Google Analytics 4, comparisons take the place of segments in Universal Analytics.”

Saved Comparisons: How They Work

The new comparisons tool allows you to create customized filtered views of Google Analytics data based on dimensions like platform, country, traffic source, and custom audiences.

These dimensions can incorporate multiple conditions using logic operators.

For example, you could generate a comparison separating “Android OR iOS” traffic from web traffic. Or you could combine location data like “Country = Argentina OR Japan” with platform filters.

These customized comparison views can then be saved to the property level in Analytics.

Users with access can quickly apply saved comparisons to any report for efficient analysis without rebuilding filters.

Google’s documentation states:

“As an administrator or editor…you can save comparisons to your Google Analytics 4 property. Saved comparisons enable you and others with access to compare the user bases you care about without needing to recreate the comparisons each time.”

Rollout & Limitations

The saved comparisons feature is rolling out gradually. There’s a limit of 200 saved comparisons per property.

For more advanced filtering needs, such as sequences of user events, Google recommends creating a custom audience first and saving a comparison based on that audience definition.

Some reports may be incompatible if they don’t include the filtered dimensions used in a saved comparison. In that case, the documentation suggests choosing different dimensions or conditions for that report type.

Why SEJ Cares

The ability to create and apply saved comparisons addresses a time-consuming aspect of analytics work.

Analysts must view data through different lenses, segmenting by device, location, traffic source, etc. Manually recreating these filtered comparisons for each report can slow down production.

Any innovation streamlining common tasks is welcome in an arena where data teams are strapped for time.

How This Can Help You

Saved comparisons mean less time getting bogged down in filter recreation and more time for impactful analysis.

Here are a few key ways this could benefit your work:

• Save time by avoiding constant recreation of filters for common comparisons (e.g. mobile vs desktop, traffic sources, geo locations).
• Share saved comparisons with colleagues for consistent analysis views.
• Switch between comprehensive views and isolated comparisons with a single click.
• Break down conversions, engagement, audience origins, and more by your saved user segments.
• Use thoughtfully combined conditions to surface targeted segments (e.g. paid traffic for a certain product/location).

The new saved comparisons in Google Analytics may seem like an incremental change. However, simplifying workflows and reducing time spent on mundane tasks can boost productivity in a big way.

Featured Image: wan wei/Shutterstock

Working in SEO leads to interesting challenges that I’m sure you’ve all faced at one point.

You’re a master of flexibility and managing tedious tasks. I’ve recently found myself dealing with 100+ top-tier sites.

Working with global companies, it’s quite the puzzle to:

• Wrangle data for 100+ sites.
• Keep tabs on every site’s performance.

And, since some of these sites compete against each other on the first page of Google, it’s quite possible that Site 1’s traffic drops but Site 2 captures the loss.

Checking one site’s Google Search Console (GSC) is easy, but it’s intense with hundreds of sites at a global scale.

What Can You Do?

I devised a Google Sheets Apps Script that connects to GSC’s API to transform global reporting from an arduous task that can take days – or weeks – into one that takes a few minutes.

After creating the script, I can easily put in a date range and pull each site’s:

• Clicks and impressions.
• Keywords.
• Average rankings.
• Etc.

Since we manage hundreds of sites, it’s not uncommon for users to end up on one of our sites to make their purchase, as mentioned above.

In the grand scheme of things, the bigger picture is more important than an individual site’s performance.

What I’m going to show you is my 10-step process to create a script that pulls clicks and impressions and then compares it all year over year (YoY).

10-Step Process To Create A Google Sheets Apps Script For Reporting On Hundreds Of Sites

Step 1: Creating Your Google Sheets

Your first step is to create your original Google Sheets file. You can do this by following these steps:

• Go to Google Drive.
• Navigate to the folder where you want to place the files.
• Right-click on the background
• Select > Google Sheets > Blank Spreadsheet.

You’ll want to rename the file. I called mine “Global Search Console Reporting.”

Screenshot from author, April 2024

Your file is now set up, and you’re ready for the next step.

Step 2: Setting Up Your Google Sheet

A blank sheet isn’t useful and won’t make sense to users until you add some headers in Row 1. Headers that I recommend adding, in this order and bolding, are:

• Website.
• Niche.
• Clicks.
• Impressions.
• YoY Clicks.
• YoY Impressions.
• Clicks % Difference.
• Impressions % Difference.

Your file should now look something like this:

Screenshot from author, April 2024

Your next step is to create a Google Cloud Project, which is also fairly simple and straightforward.

Step 3: Create A Google Cloud Console Data Project

Creating your project should be free because Google provides a $300 credit to try out its platform. If you haven’t used Google Cloud, you can find it at https://console.cloud.google.com/.

You can now follow these steps:

• Tap Select Project > New Project.
• Enter Project Name (example: “My GSC Data Project”).
• Tap Create.

Screenshot from author, April 2024

• Click Select Project.
• Select your Project.

• Click the top Search bar.
• Type “Google Search Console API.”
• Select “Google Search Console API.”
• Click Enable.

Step 4: Create Apps Scripts In Google Sheets

In this step, we will work on integrating the Apps Script into the Google Sheet that you created previously. You’ll need to open the Sheet and follow these steps:

• Tap Extensions > Apps Script.

I’m not going to go into the details on how the script works, but you can copy this code:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Search Console')
    .addItem('Fetch Data', 'menuItem1')
    .addToUi();
}

function menuItem1() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow(); // Find the last row with data in column A

  // Clear cells C2:F151 before processing data
  sheet.getRange("C2:F151").clearContent();

  for (var i = 2; i <= lastRow; i++) { var siteProperty = sheet.getRange(i, 1).getValue(); var startDateValue = sheet.getRange('M1').getValue(); var endDateValue = sheet.getRange('M2').getValue(); var timeZone = SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone(); var format = "yyyy-MM-dd"; // Calculate dates for last year var lastYearStartDate = new Date(startDateValue); lastYearStartDate.setFullYear(lastYearStartDate.getFullYear() - 1); var lastYearEndDate = new Date(endDateValue); lastYearEndDate.setFullYear(lastYearEndDate.getFullYear() - 1); var startDate = Utilities.formatDate(lastYearStartDate, timeZone, format); var endDate = Utilities.formatDate(lastYearEndDate, timeZone, format); // Fetch data for the previous year var previousYearResponse = requestSearchConsoleAPI(siteProperty, startDate, endDate); // Fetch data for the current year (unchanged) startDate = Utilities.formatDate(new Date(startDateValue), timeZone, format); endDate = Utilities.formatDate(new Date(endDateValue), timeZone, format); var currentYearResponse = requestSearchConsoleAPI(siteProperty, startDate, endDate); // Process and write data for both years processAndWriteData(sheet, i, previousYearResponse, currentYearResponse); } } function processAndWriteData(sheet, row, previousYearResponse, currentYearResponse) { // Check if response is not defined or null and has at least one row if (previousYearResponse && previousYearResponse.length > 0) {
    var previousYearClicks = 0;
    var previousYearImpressions = 0;

    previousYearResponse.forEach(function(row) {
      previousYearClicks += row.clicks;
      previousYearImpressions += row.impressions;
    });

    sheet.getRange(row, 5).setValue(previousYearClicks); // Write to column D (index 5)
    sheet.getRange(row, 6).setValue(previousYearImpressions); // Write to column E (index 6)
  } else {
    Logger.log('No data found for previous year in row: ' + row);
  }

// Process and write data for the current year
  if (currentYearResponse && currentYearResponse.length > 0) {
    var currentYearClicks = 0;
    var currentYearImpressions = 0;

    currentYearResponse.forEach(function(row) {
      currentYearClicks += row.clicks;
      currentYearImpressions += row.impressions;
    });

    sheet.getRange(row, 3).setValue(currentYearClicks); // Write to column C (index 3)
    sheet.getRange(row, 4).setValue(currentYearImpressions); // Write to column D (index 4)
  } else {
    Logger.log('No data found for current year in row: ' + row);
  }
}



function requestSearchConsoleAPI(siteProperty, startDate, endDate) {

  try {
    const oauthToken = ScriptApp.getOAuthToken(); // Correctly call the method
    const siteUrl = siteProperty;
    const url="https://www.googleapis.com/webmasters/v3/sites/" + encodeURIComponent(siteUrl) + '/searchAnalytics/query';
    const payload = {
      startDate: startDate,
      endDate: endDate,
      type: 'web'
    };

    const headers = {
      'Authorization': 'Bearer ' + oauthToken,
      'Content-Type': 'application/json'
    };
    const options = {
      'method': 'post',
      'contentType': 'application/json', // Consistent content type
      'headers': headers,
      'payload': JSON.stringify(payload),
      'muteHttpExceptions': true
    };

    const response = UrlFetchApp.fetch(url, options);
    const responseCode = response.getResponseCode();
    const contentText = response.getContentText(); // Get response text for logging

  Logger.log('Response Code: ${responseCode}'); // Use backticks
  Logger.log('Response Content: ${contentText}'); // Use backticks


  if (responseCode === 200) {
    const json = JSON.parse(contentText);
    Logger.log(json); // This will log the actual JSON response
    return json.rows; // Adjust this line based on the actual structure of your API response
  } else {
    // Correctly use backticks here for template literals
    const errorMessage="Error fetching data: ${responseCode} - ${contentText}";
    Logger.log(errorMessage);
    throw new Error(errorMessage);
  }

  } catch (e) {
    Logger.log('Error: ${e.toString()}');
    return null;
  }
}

And then go back to your Apps Script project and do the following:

• Press CTRL + A to select all.
• Press CTRL + V to paste in the code you copied.
• Tap OK.
• Click Save project.
• Tap Run.

*Note: If you are receiving a Bad Request error from Google with too many redirects, this is because you have multiple accounts logged in. Try in a browser with only one Google account logged in.

Screenshot from author, April 2024

You’ll be requested to Review permissions and will need to select the Google Account associated with your Google Search Console.

Google will give you a warning because the app isn’t verified, so simply tap on the “Advanced” setting and then “Go to Untitled project (unsafe).”

Screenshot from author, April 2024

Finally, you can complete this step by tapping or clicking on the Allow button.

Step 5: Set Up The Access Credentials

I know there’s a lot of back-and-forth going on between Sheets and Google Cloud Console, but it’s an unfortunate necessity at this point. Now, we will be setting up Access Credentials, which will require you to go back to the Google Cloud Console.

Note: You must have enabled the Google Search Console API from the previous step.

Your screen should look something like this:

Screenshot from author, April 2024

You’ll need to:

• Tap Credentials > Create Credentials.
• Tap OAuth client ID > Configure Consent Screen.

• Click External.
• Tap Create.
• Enter “My GSC Data” as the App name.
• Add your Support email (your email used for GSC).
• Add your Developer contact information (the email you used for GSC).
• Tap Save and continue.
• Tap ADD OR REMOVE SCOPES.
• Check 2 of the Google Search Console API scopes (might be on page 2).

• Click Update.
• Click Save and Continue.
• Now click Add Users.

• You can add multiple users, preferably those that have access to GSC.
• Save and Continue.

Step 6: Set Up Google Cloud Project For GSC Data

While we’re still on the Google Cloud Project, you’ll want to click the hamburger icon and go to Cloud overview > Dashboard:

Screenshot from author, April 2024

You’ll notice that it says “Project number,” which you should select and Copy by pressing CTRL + C.

Switch back to your Apps Script tab and tap Project Settings:

Screenshot from author, April 2024

Go to the section titled Google Cloud Platform (GCP) Project, paste the project number (CTRL +  V) into the text box, and click Set project.

Step 7: Rename Your Google Apps Script

You’ll now want to rename your Apps Script by going to Project History like this:

You’ll then:

• Click Untitled project at the top of the screen.
• Enter “My GSC Data Project Script.”
• Click on Rename.

Step 8: Edit Google Apps Manifest File For Code.gs Script

You’re still staying inside of your script, and we’re going to go back to Project Settings just as we did before.

This time, you’ll want to click Show “appsscript.json” manifest file in editor to make sure there’s a checkmark next to it.

Next, click on Editor and navigate to the appsscript.json, which you can see below:

Screenshot from author, April 2024

You’ll want to delete everything in the appsscript.json file and paste in the following script:

{
  "timeZone": "America/New_York",
  "dependencies": {
  },
  "exceptionLogging": "STACKDRIVER",
  "oauthScopes": [
    "https://www.googleapis.com/auth/webmasters",
    "https://www.googleapis.com/auth/script.external_request",
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/spreadsheets.currentonly"
  ]
}

Once you’ve added the code, you can click on your Code.gs file and tap Save, and then Run. You’ll be prompted to review permissions, and you’ll need to select your appropriate account to continue using.

After a few prompts, you’ll be asked to allow your app “My GSC Data,” and execution will begin.

Step 9: Adjust The Dates For Website Data Analysis

In the Google Sheets file, you’ll want to add the following under:

• L1: Start Date.
• L2: End Date.

Note: The start and end dates should be specified in M1 and M2. For example, you can input:

Note: The date format may differ based on your system settings and location.

Step 10: Set Conditional Formatting For Non-Empty Cells Less Than Zero

Everything is set up, but you should add some conditional formatting to make it look better. We’re going to focus on the “Clicks % Difference” and “Impressions % Difference” columns:

Screenshot from author, April 2024

Select the rows under the headers “Clicks % Difference” and “Impressions % Difference” and click on Format > Conditional formatting. Under Format rules, you’ll want to select Less than.

In the “Value or formula” text area, you can add 0.

What this does is that if it’s less than 0, we’ll be changing the color to red since it’s in the negative and traffic has been lost. You can do this by clicking on the paint can and changing it to red before clicking done.

If you want to change a positive increase in traffic to green, you’ll add another rule for Greater than and add the 0 value.

Here are the formulas to use in G2 and H2 (you can replicate them for each row; just click and drag down for the other rows):

=IFERROR(IF(AND(C2<>"",E2<>""), (C2-E2)/E2, ""),"")

=IFERROR(IF(AND(D2<>"",F2<>""), (D2-F2)/F2, ""),"")

Now, you have an easy way to run reports on multiple sites at once.

That’s It, You Have Your Global Report

In column A, input your Google Search Console properties; if it is a domain property, add it as sc-domain:example.com or a URL property as https://example.com

To run or refresh the report, use the special menu Search Console > Fetch Data:

*Note: This script supports about 150 domains, but if you need more, you can adjust the row #14 in your AppScripts file:

sheet.getRange("C2:F151").clearContent();

Using this very tutorial, you’ll have an easy time turning days of gathering data and running reports into a few minutes. You can even expand the scripts to perform other calculations or gather more data for your report.

Check out my other tutorial on Integrating ChatGPT With Google Sheets.

Automating your reports is a great way to streamline tedious tasks, and I hope it makes your job a little easier.

More resources: 

Featured Image: 200dgr /Shutterstock