Working in SEO results in fascinating challenges that I’m positive you’ve all confronted at one level.
You’re a grasp of flexibility and managing tedious duties. I’ve not too long ago discovered myself coping with 100+ top-tier websites.
Working with international firms, it’s fairly the puzzle to:
- Wrangle knowledge for 100+ websites.
- Maintain tabs on each web site’s efficiency.
And, since a few of these websites compete in opposition to one another on the primary web page of Google, it’s fairly potential that Website 1’s site visitors drops however Website 2 captures the loss.
Checking one web site’s Google Search Console (GSC) is straightforward, however it’s intense with a whole lot of web sites at a world scale.
What Can You Do?
I devised a Google Sheets Apps Script that connects to GSC’s API to rework international reporting from an arduous job that may take days – or weeks – into one which takes a couple of minutes.
After creating the script, I can simply put in a date vary and pull every web site’s:
- Clicks and impressions.
- Key phrases.
- Common rankings.
- And so forth.
Since we handle a whole lot of web sites, it’s not unusual for customers to finish up on certainly one of our websites to make their buy, as talked about above.
Within the grand scheme of issues, the larger image is extra vital than a person web site’s efficiency.
What I’m going to point out you is my 10-step course of to create a script that pulls clicks and impressions after which compares all of it 12 months over 12 months (YoY).
10-Step Course of To Create A Google Sheets Apps Script For Reporting On Lots of Of Websites
Step 1: Creating Your Google Sheets
Your first step is to create your unique Google Sheets file. You are able to do this by following these steps:
- Go to Google Drive.
- Navigate to the folder the place you need to place the information.
- Proper-click on the background
- Choose > Google Sheets > Clean Spreadsheet.
You’ll need to rename the file. I referred to as mine “International Search Console Reporting.”
Your file is now arrange, and also you’re prepared for the subsequent step.
Step 2: Setting Up Your Google Sheet
A clean sheet isn’t helpful and gained’t make sense to customers till you add some headers in Row 1. Headers that I like to recommend including, on this order and bolding, are:
- Web site.
- Area of interest.
- Clicks.
- Impressions.
- YoY Clicks.
- YoY Impressions.
- Clicks % Distinction.
- Impressions % Distinction.
Your file ought to now look one thing like this:
The next move is to create a Google Cloud Venture, which can be pretty easy and simple.
Step 3: Create A Google Cloud Console Information Venture
Creating your undertaking ought to be free as a result of Google offers a $300 credit score to check out its platform. In case you haven’t used Google Cloud, you could find it at https://console.cloud.google.com/.
Now you can observe these steps:
- Faucet Choose Venture > New Venture.
- Enter Venture Identify (instance: “My GSC Information Venture”).
- Faucet Create.
- Click on Choose Venture.
- Choose your Venture.
- Click on the highest Search bar.
- Sort “Google Search Console API.”
- Choose “Google Search Console API.”
- Click on Allow.
Step 4: Create Apps Scripts In Google Sheets
On this step, we’ll work on integrating the Apps Script into the Google Sheet that you just created beforehand. You’ll have to open the Sheet and observe these steps:
- Faucet Extensions > Apps Script.
I’m not going to enter the main points on how the script works, however you may copy this code:
perform onOpen() {
var ui = SpreadsheetApp.getUi();
// Or DocumentApp or FormApp.
ui.createMenu('Search Console')
.addItem('Fetch Information', 'menuItem1')
.addToUi();
}
perform menuItem1() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var lastRow = sheet.getLastRow(); // Discover the final row with knowledge in column A
// Clear cells C2:F151 earlier than processing knowledge
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 final 12 months 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 knowledge for the earlier 12 months var previousYearResponse = requestSearchConsoleAPI(siteProperty, startDate, endDate); // Fetch knowledge for the present 12 months (unchanged) startDate = Utilities.formatDate(new Date(startDateValue), timeZone, format); endDate = Utilities.formatDate(new Date(endDateValue), timeZone, format); var currentYearResponse = requestSearchConsoleAPI(siteProperty, startDate, endDate); // Course of and write knowledge for each years processAndWriteData(sheet, i, previousYearResponse, currentYearResponse); } } perform processAndWriteData(sheet, row, previousYearResponse, currentYearResponse) { // Test if response is just not outlined or null and has not less than one row if (previousYearResponse && previousYearResponse.size > 0) {
var previousYearClicks = 0;
var previousYearImpressions = 0;
previousYearResponse.forEach(perform(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 knowledge discovered for earlier 12 months in row: ' + row);
}
// Course of and write knowledge for the present 12 months
if (currentYearResponse && currentYearResponse.size > 0) {
var currentYearClicks = 0;
var currentYearImpressions = 0;
currentYearResponse.forEach(perform(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 knowledge discovered for present 12 months in row: ' + row);
}
}
perform requestSearchConsoleAPI(siteProperty, startDate, endDate) {
attempt {
const oauthToken = ScriptApp.getOAuthToken(); // Appropriately name the strategy
const siteUrl = siteProperty;
const url="https://www.googleapis.com/site owners/v3/websites/" + encodeURIComponent(siteUrl) + '/searchAnalytics/question';
const payload = {
startDate: startDate,
endDate: endDate,
sort: 'internet'
};
const headers = {
'Authorization': 'Bearer ' + oauthToken,
'Content material-Sort': 'software/json'
};
const choices = {
'methodology': 'publish',
'contentType': 'software/json', // Constant content material sort
'headers': headers,
'payload': JSON.stringify(payload),
'muteHttpExceptions': true
};
const response = UrlFetchApp.fetch(url, choices);
const responseCode = response.getResponseCode();
const contentText = response.getContentText(); // Get response textual content for logging
Logger.log('Response Code: ${responseCode}'); // Use backticks
Logger.log('Response Content material: ${contentText}'); // Use backticks
if (responseCode === 200) {
const json = JSON.parse(contentText);
Logger.log(json); // It will log the precise JSON response
return json.rows; // Regulate this line primarily based on the precise construction of your API response
} else {
// Appropriately use backticks right here for template literals
const errorMessage="Error fetching knowledge: ${responseCode} - ${contentText}";
Logger.log(errorMessage);
throw new Error(errorMessage);
}
} catch (e) {
Logger.log('Error: ${e.toString()}');
return null;
}
}After which return to your Apps Script undertaking and do the next:
- Press CTRL + A to pick out all.
- Press CTRL + V to stick within the code you copied.
- Faucet OK.
- Click on Save undertaking.
- Faucet Run.
*Observe: If you’re receiving a Unhealthy Request error from Google with too many redirects, it’s because you will have a number of accounts logged in. Attempt in a browser with just one Google account logged in.
You’ll be requested to Assessment permissions and might want to choose the Google Account related together with your Google Search Console.
Google offers you a warning as a result of the app isn’t verified, so merely faucet on the “Superior” setting after which “Go to Untitled undertaking (unsafe).”
Lastly, you may full this step by tapping or clicking on the Permit button.
Step 5: Set Up The Entry Credentials
I do know there’s quite a lot of back-and-forth occurring between Sheets and Google Cloud Console, however it’s an unlucky necessity at this level. Now, we shall be establishing Entry Credentials, which would require you to return to the Google Cloud Console.
Observe: You will need to have enabled the Google Search Console API from the earlier step.
Your display screen ought to look one thing like this:
You’ll have to:
- Faucet Credentials > Create Credentials.
- Faucet OAuth consumer ID > Configure Consent Display.
- Click on Exterior.
- Faucet Create.
- Enter “My GSC Information” because the App identify.
- Add your Help e mail (your e mail used for GSC).
- Add your Developer contact data (the e-mail you used for GSC).
- Faucet Save and proceed.
- Faucet ADD OR REMOVE SCOPES.
- Test 2 of the Google Search Console API scopes (is perhaps on web page 2).
- Click on Replace.
- Click on Save and Proceed.
- Now click on Add Customers.
- You may add a number of customers, ideally those who have entry to GSC.
- Save and Proceed.
Step 6: Set Up Google Cloud Venture For GSC Information
Whereas we’re nonetheless on the Google Cloud Venture, you’ll need to click on the hamburger icon and go to Cloud overview > Dashboard:
You’ll discover that it says “Venture quantity,” which you must choose and Copy by urgent CTRL + C.
Swap again to your Apps Script tab and faucet Venture Settings:
Go to the part titled Google Cloud Platform (GCP) Venture, paste the undertaking quantity (CTRL + V) into the textual content field, and click on Set undertaking.
Step 7: Rename Your Google Apps Script
You’ll now need to rename your Apps Script by going to Venture Historical past like this:
You’ll then:
- Click on Untitled undertaking on the high of the display screen.
- Enter “My GSC Information Venture Script.”
- Click on on Rename.
Step 8: Edit Google Apps Manifest File For Code.gs Script
You’re nonetheless staying within your script, and we’re going to return to Venture Settings simply as we did earlier than.
This time, you’ll need to click on Present “appsscript.json” manifest file in editor to verify there’s a checkmark subsequent to it.
Subsequent, click on on Editor and navigate to the appsscript.json, which you’ll be able to see under:
You’ll need to delete all the things within the appsscript.json file and paste within 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"
]
}When you’ve added the code, you may click on in your Code.gs file and faucet Save, after which Run. You’ll be prompted to evaluate permissions, and also you’ll want to pick out your applicable account to proceed utilizing.
After just a few prompts, you’ll be requested to permit your app “My GSC Information,” and execution will start.
Step 9: Regulate The Dates For Web site Information Evaluation
Within the Google Sheets file, you’ll need to add the next beneath:
- L1: Begin Date.
- L2: Finish Date.
Observe: The beginning and finish dates ought to be laid out in M1 and M2. For instance, you may enter:
Observe: The date format might differ primarily based in your system settings and site.
Step 10: Set Conditional Formatting For Non-Empty Cells Much less Than Zero
Every thing is about up, however you must add some conditional formatting to make it look higher. We’re going to deal with the “Clicks % Distinction” and “Impressions % Distinction” columns:
Choose the rows beneath the headers “Clicks % Distinction” and “Impressions % Distinction” and click on on Format > Conditional formatting. Beneath Format guidelines, you’ll need to choose Lower than.
Within the “Worth or formulation” textual content space, you may add 0.
What this does is that if it’s lower than 0, we’ll be altering the colour to purple because it’s within the unfavorable and site visitors has been misplaced. You are able to do this by clicking on the paint can and altering it to purple earlier than clicking executed.
If you wish to change a constructive enhance in site visitors to inexperienced, you’ll add one other rule for Larger than and add the 0 worth.
Listed below are the formulation to make use of in G2 and H2 (you may replicate them for every row; simply click on and drag down for the opposite rows):
=IFERROR(IF(AND(C2<>"",E2<>""), (C2-E2)/E2, ""),"")=IFERROR(IF(AND(D2<>"",F2<>""), (D2-F2)/F2, ""),"")Now, you will have a straightforward solution to run stories on a number of websites directly.
That’s It, You Have Your International Report
In column A, enter your Google Search Console properties; if it’s a area property, add it as sc-domain:instance.com or a URL property as https://instance.com
To run or refresh the report, use the particular menu Search Console > Fetch Information:
*Observe: This script helps about 150 domains, however should you want extra, you may regulate the row #14 in your AppScripts file:
sheet.getRange("C2:F151").clearContent();
Utilizing this very tutorial, you’ll have a straightforward time turning days of gathering knowledge and operating stories into a couple of minutes. You may even develop the scripts to carry out different calculations or collect extra knowledge to your report.
Try my different tutorial on Integrating ChatGPT With Google Sheets.
Automating your stories is a good way to streamline tedious duties, and I hope it makes your job a bit of simpler.
Extra assets:
Featured Picture: 200dgr /Shutterstock
