Chapter 19: Automate Refreshing SEO Keywords Performance in Google Sheets Using Google Search Console and Easy2Digital APIs
SEO keyword insight is pretty valuable for any webmasters. The existing ranked keywords’ avg position performance month by month let you understand how’s going of your content marketing strategy. What is more, new keywords popping up in the search console inspire you with the new content perspective and long-tail keywords to utilize. It’s kind of a no-brainer, but the thing is how to organize the process and automate the process which grabs the SEO insight. This is the value you can gain from this piece.
Google SEO keyword query insight currently is only available in the Google search console, unless you pay for a SaaS, such as SEMrush, Moz, etc. A long time ago, Google analytics stopped showing SEO insight and changed it to “not provided”. But keyword insight is hugely important for content marketing, and website optimization. You and I absolutely know that, which is just like bullshit as I said. But the thing is how to automatically update these keyword queries to your Google Sheets Dashboard you created and customized it as you like.
In this chapter, I would talk to you about how to leverage Python codes and Google Search Console API with Crontab. It’s for automatically updating the latest keyword queries and refreshing the dashboard. By the end of this article, you can learn the techniques to create your favorite application. Then, you can sit and open the dashboard and enjoy!
Table of Contents: Google Search Console API Integration Using Python
- Required Modules in Python Script
- Google Search Console API
- Available JSON Data Feed
- Search Console API Methods and Parameters
- Create the Loop to Fetch and Save Data on Google Sheets
- Automate the update using Crontab
- Full Python Script of Google SEO Console Keyword Query Scraper
Required Modules in Python Script
We would use Google APIs, which connect with Google search console and Google Drive. So oauth2 is necessary. Then, it’s necessary to have pandas of course, because we need to use them to frame the fetched data. Last but not least, as it would upload to Google Sheets, so for making it more simple, I recommend using gspread and oauth2client as well.
Google Search Console API
First thing first, we need to go to the Google developer console to enable Google search console API and Google Drive API. Many people would forget to activate Google Drive API though. So please remember to check your backend of the Google API library.
Then, we need to create a new credential for this project in the Google cloud platform. And we also need to create a new key within this credential and download the JSON file. You would use this file in a moment.
Last but not least, we set the scope in the codings and configure the JSON file with building the service functions.
We need to add the gspread section codings as well. For more details, please check out the other article I released before.
Available JSON Data Feed
In Google search console API, you can grab data more than you download the data from the platform report. But also it can be an automatic process and you have more keyword insight.
In terms of the data available via API, you can select startDate, enddate, dimension of query, page, and device. What’s more, it’s because Google API restricts the data generation volume. You can set the rowLimit. For example, you can generate 10 keywords, or you fetch 2000 keywords as well.
Search Console API Methods and Parameters
Obviously, there are three types of API connection, which are search analytics, sitemap, and site setting. To us, we would utilize search analytics in this Python Script.
Frankly, it doesn’t have many methods to utilize. It’s just the searchanalytics() and query(). In the query, we need to fill in our website URL in the siteUrl parameter. Then the data we aim to fetch is necessary to add to the body parameter.
response = service.searchanalytics().query(siteUrl='https://www.easy2digital.com/', body=request).execute()
After command B, you can see the JSON format data already has come up in front of us. From the JSON data, you can find out what metric data we can fetch. They can be clicks, impressions, ctr, position, and so on. Create the Loop to Fetch and Save Data on the Google Sheets
As well as the youtube video performance and Shopify product data I talked about previously, we need to create a loop to grab all the keyword data we set the amount in rowLimit above.
for row in response['rows']:
seoData = {}
for i in range(len(request['dimensions'])):
seoData[request['dimensions'][i]] = row['keys'][i]
seoData['clicks'] = row['clicks']
seoData['impressions'] = row['impressions']
seoData['ctr'] = round(row['ctr'], 2)
seoData['position'] = round(row['position'], 2)
results.append(seoData)
Like grabbing Amazon product data, we would use pandas to append the fetched data and upload it to Google Sheets using gspread. For more details, please check out Python Tutorial 17.
Automate the update using Crontab
In Python Tutorial 18, I talked about how to use crontab to automate refreshing the Amazon price tracker. Regarding SEO keyword queries and position performance refreshing, it’s the same script by just modifying the schedule and script path.
Then, you can just open your SEO performance tracker and check the up-to-date performance. I would talk about how to create a Google Sheets dashboard if you are interested in. Please comment below and let me know
For more details, please check out Python Tutorial 18
Full Python Script of Google Search Console API Scraper
If you would like to have the full version of the Python Script of Amazon Product Price Tracker, please subscribe to our newsletter by adding the message “Chapter 19”. We would send you the script immediately to your mailbox.
I hope you enjoy reading Chapter 19: Automate Refreshing SEO Google Sheets Dashboard with New and Existing Keywords Performance from Google Search Console. If you did, please support us by doing one of the things listed below, because it always helps out our channel.
- Support and donate to our channel through PayPal (paypal.me/Easy2digital)
- Subscribe to my channel and turn on the notification bell Easy2Digital Youtube channel.
- Follow and like my page Easy2Digital Facebook page
- Share the article on your social network with the hashtag #easy2digital
- Buy products with Easy2Digital 10% OFF Discount code (Easy2DigitalNewBuyers2021)
- You sign up for our weekly newsletter to receive Easy2Digital latest articles, videos, and discount codes
- Subscribe to our monthly membership through Patreon to enjoy exclusive benefits (www.patreon.com/louisludigital)
