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

One thing I have clearly realized while being a full-time blogger and editor is that most writers (no matter how much we love them) are terrible at formatting.

I mean, I get it.

Writing is a creative process, and things like formatting, interlinking and proofreading may keep you from letting your creativity flourish.

One workaround is hiring a VA but if you have control issues (like I do) and still want to do everything on your own, here’s a free blog checklist that’s easy to follow and help you publish a perfectly formatted article each time!

A good formatting checklist should be:

• As short as possible (The longer it is, the more chances there are that none of your contributors will follow all the steps);
• As concise as possible (Avoid explaining the reason why you want it to be formatted that way. The more you explain, the less obvious your point becomes. Just list your requirements)
• As easy to understand as possible (Add a few screenshots, bold most important points, etc).

Feel free to make yourself a copy of this checklist, edit minor details (like the preferred width of images) and use it to edit your blog. It will be suitable and (hopefully) useful for.

• A single-author blog for consistency
• A (business) blog with multiple co-authors;
• A business managing multiple paid contributors;
• A business owner outsourcing content to ghost writers, etc.

Attention: This guide requires that your blog contributor has a wp-admin (Contributor) access to your blog. Otherwise, you may ask to send you the articles in HTML.

*Make a free copy of the checklist here. Read some explanations below*

Structuring Standards

We know that breaking the article into sections is very important: blog readers tend to scan through the articles jumping from a subheading to a subheading (and probably reading the sections that seem most relevant to them).

One of the most efficient ways to capture your readers’ attention is to provide catchy subheadings that would summarize your article content nicely. Ideally, the reader of the article should understand what it is about by just scanning through the subheadings which should effectively summarize the article content while still encouraging the reader to go more indepth.

Other important guidelines that (may) go in this section:

• Make your sentences and paragraphs short (that makes the whole article easier to read)
• Introduce your article effectively in the opening paragraphs and encourage a discussion in the conclusion (by asking some questions)

Adding Links

Links are always great (unless links are brutally self-serving and anchor-text-dirty). I always encourage all my authors include links to any app, business or person they are mentioning. Links are user-friendly, so in my guidelines I always prompt contributors to:

• Link to relevant articles on my blog;
• Link to others relevant articles elsewhere (as well as sources of information, apps, etc)
• Normally link words (not image files or subheadings)
• Always make sure they are using the full URL and the original “clean” link (now a shortened version, stripping all tracking parameters, etc).

Lists

Including lists is also highly appreciated. A well-formatted list is likely to draw readers’ attention and make them stay.

Sadly, contributors tend to format lists in all possible ways using -, *, or any other inappropriate symbols instead proper <ul><li> coding. Therefore it’s a good idea to remind them of proper formatting here.

Images

Another very important element of any blog post. Images will never be added unless you clearly ask for them.

Here I specify the proper style and size of the images as well as encourage authors to include a relevant, catchy and properly attributed Creative Commons image.

Here are more checklists you may find useful for blogging:

FAQ: Blog Post Checklist: Check All Prior to Hitting “Publish”

What is the purpose of the blog post checklist?

The blog post checklist is designed to help bloggers and editors publish well-formatted articles consistently, without having to compromise on the creativity of the writing process.

Who can benefit from using this checklist?

The checklist can be beneficial for a range of people including single-author blogs, multi-author business blogs, business owners outsourcing content to ghost writers, and businesses managing multiple paid contributors.

What are the key components of the checklist?

The blog post checklist focuses on:

• Structuring standards (including catchy subheadings and succinct paragraphs)
• Proper linking to relevant articles and sources
• Creating well-formatted lists
• Adding appropriately styled and attributed images.

How should the article checklist be crafted?

The checklist should be as short, concise, and easy to understand as possible, with important points highlighted, possibly with the use of screenshots.

How can readers access the checklist?

Make a free copy of the checklist from the link and modify it according to their preferences and needs.

If you find the guidelines useful or have any improvements to suggest, please comment below!

The following two tabs change content below.

I am Ann Smarty, owner of SEOsmarty.com. I’ve been in the SEO industry for two decades. I am the former Editor-in-Chief of Search Engine Journal and a contributor to Mashable. These days I am running Viral Content Bee and writing for Moz, Buzzsumo, Wix and many others!

Advertisement

Focus is mission-critical, but most companies don’t lower the gravitational forces pulling on attention.

As a result, large companies with too much mass have a hard time navigating and adapting to the quickly changing organic growth landscape:

• Google’s algorithm updates have struck hard and left casualties.
• Even small rank changes in top positions have an outsized impact.
• Consumer behavior on the internet is messy and hard to track
• Teams are shrinking: 80,000 tech workers have been laid off in 2024 so far.
• Marketing budgets were slashed on average by 10 to 20% over the last two years and only slowly started to recover.
• Tech workers spend two out of five days per week on meetings and email. Only 43% of the time is spent on actual tasks.

Boost your skills with Growth Memo’s weekly expert insights. Subscribe for free!

No Win Without Focus

Sir Isaac Newton realized that the sun’s gravity causes planets to orbit it in an elliptical path. Gravity in the workplace is a distraction from the focus of individuals and teams:

• Meetings.
• Fire drills.
• Red tape.
• Strategy pivots.
• Too many goals.
• Alignment overhead.
• Too many cooks in the kitchen.
• Procurement and legal rabbit holes.
• Non-critical emails and Slack messages.

The larger a company gets, the stronger its gravity.

Taken to an extreme, it takes companies forever to launch even a single feature, and they fall behind the competition.

For individuals, gravity is even more consequential: Scattered attention means getting nothing done, having no impact, and likely being fired. Worse, people get exhausted and burned out in the process.

“Tranquility comes from doing less,” (Ryan Holiday), but a lot of teams execute scatter-brained like a teenager multitasking between Netflix, TikTok, and texting.

Individual and team focus are connected at the hip. When a team is distracted, it transfers to individuals. Two-thirds of people struggle to find the energy to do their job.

Whenever I get overwhelmed, my brain tells me to open my email inbox and look for a quick dopamine hit. But finding quick tasks and busy work is no achievement.

Real impact comes from working through tedious, complex problems.

Unclear communication is one of the biggest attention drainers. We waste a lot of time deciphering what other people mean.

At Shopify, we had a very high bar for what internal communications went out to the Growth org and how they would be framed.

It’s easy to @ your whole team on Slack, but what people really need is key information:

• What’s going on?
• How is it relevant to me?
• What do I need to know/do?

Lazy communication has massive speed cost. In the book “Smart Brevity,” the authors provide a simple framework for writing clear statements:

1. Start with a muscular tease that grabs attention with six or fewer strong words.
2. Explain what recipients need to know in the first sentence.
3. Explain why it matters.
4. Offer a choice to go deeper by providing more optional context.

Most important: Think about one thing you want people to remember – not more. Nobody has time to read a Slack novel.

Better Prioritization

At PayPal, Peter Thiel established a culture of hardcore focus. He would only discuss their No. 1 priority with managers and hold them accountable for just their one main contribution to the company.

Focus is a forcing function to separate the wheat from the chaff when it comes to what to spend time on.

No one could be a better example of hardcore prioritization than engineers. If you want to get something on the ENG roadmap, something else has to give.

An effective roadmap operating system has only a few lines of code:

• What’s the goal?
• What are the top 3 things that get us there?
• For those three things, what do we need in terms of people, assets, time, support from other teams, and tools?
• For those three things, who does what, by when?
• Defend yourself and your team as much as possible from anything else.

You never look back at your time at a company and say, “Man, my fourth, fifth, and sixth priority back then really hit home,” but you might remember the impact of priorities one, two, and three.

Cal Newport’s new book, “Slow Productivity,” mentions doing less as one of the top ways to do better work.

But the advice I like the most is doubling the time you think a project takes.

Doubling automatically trims your roadmap by probably 50% but makes it more likely that you deliver on time and deliver well.

A big part of moving ourselves into an overcommitment corner is underestimating how long projects take (I think I wrote the last sentence at least as much for myself as for you).

Better Strategies

Poor strategies are hard to follow and confuse the team.

In my experience, managers want to get fancy, but they miss the most important point: A good strategy means doing something different from the competition.

Instead of outworking contenders, you want to do something that’s unique and leans to your competitive advantage.

Pairing differentiation with prioritization, your three most important projects should underline how you achieve the same goal in a different way as your competitors.

For example, instead of writing 100 blog articles, can you build a programmatic play or a list of tools? Or can you leverage contributors who write the content instead of a large in-house team?

I also found that most strategies simply aren’t clear. A simple test for clarity is to up or downsize the surface you have to explain it: Can you express your strategy in one paragraph (TL;DR), one page (high-level), and one doc (in-depth)?

Less Red Tape

Red tape in the form of excessive bureaucracy kills execution. I’ve seen many companies that take many weeks and endless alignment meetings before being able to sign up for a simple SaaS tool.

Procurement and legal teams can slow companies down and frustrate teams beyond means.

The key to having speed and a good evaluation process is clear guidelines when legal or procurement steps in.

With one of my former clients, the fastest-growing fintech startup in history, we sat down with the legal team and got a full download on guardrails. What can we say and what not? When do we have to get legal approval, and when can we move forward without it?

This is a task for the team manager or org leader. While tedious, the good news is that once the borders have been established, teams can move forward faster and focus on execution.

Fewer Meetings

Tobi Lütke, founder and CEO of Shopify, called meetings a “bug.” The leadership team regularly deployed the “Chaos Monkey,” a script that deletes all recurring meetings with more than two participants. Other companies set guardrails around time. Calendly restricts meetings to noon until 5 p.m.

Most meetings are poorly run, unnecessary, or simply a way for people to socialize.

Besides an agenda, every meeting should have a clear purpose. There really are only three types of meetings: socializing, information sharing, and decision-making.

Building relationships in the workplace is important, and there is nothing wrong with socializing. It’s important to be explicit and avoid meeting to “talk about Project X” while really wanting to socialize.

Information-sharing meetings are best done async. Instead of getting a large group of people together, record your message in a video or write a memo.

Decision-making meetings should be led by the decision maker and come with a pre-read.

The problem with many large organizations is that decisions are poorly framed; it’s unclear who makes the decision, and the decision-maker doesn’t have explicit criteria for how to make the decision.

Outlook: Can AI Help Us Regain Focus?

Show me how focused your team is, and I’ll show you a team that will win.

High gravity in large organizations, on the other hand, is an ask to be disrupted by a smaller, more agile player. The good news is that technology is working against gravity – at least in the workplace.

AI has the potential to help us find fragmented information, force clarity, and take over bland admin tasks that drain time so we can focus on things that matter.

Microsoft’s Future of Work report concludes:

“Organizational knowledge is fragmented across documents, conversations, apps and devices, but LLMs hold the potential to gather and synthesize this information in ways that were previously impossible”.

Layoffs.fyi

Marketing spending shows signs of growth, but AI adoption is slow: report; Beyond belt-tightening: How marketing can drive resiliency during uncertain times

Will AI Fix Work?

Workers Now Spend Two Full Days a Week on Email and in Meetings

Microsoft New Future of Work Report 2023

Featured Image: Paulo Bobita/Search Engine Journal