September 21, 2021

Python Tutorial 19: Automate Refreshing SEO Google Sheets Dashboard with New and Existing Keywords Performance from Google Search Console

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.

python tutorial

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. Long time ago, Google analytics stopped showing SEO insight and changed it to “not provided”. But the 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 Python Tutorial, 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!

Python Tutorial – 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 it 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.

import pandas as pd
import gspread
from googleapiclient.discovery import build
from google.oauth2 import service_account
from oauth2client.service_account import ServiceAccountCredentials

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 at your backend of the Google API library.

python tutorial

python tutorial

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.

scope = ['https://www.googleapis.com/auth/webmasters']
api_key = 'yourJSONfilepath.json'
credentials = service_account.Credentials.from_service_account_file(api_key,scopes=scope)
service = build('webmasters', 'v3', credentials=credentials)

We need to add the gspread section codings as well. For more details, please check out the other article I released before.

Python Tutorial 17: Get the Up-to-date Amazon Product Market Value by Amazon Price Tracker Using ASIN, gspread, Oauth2Client, and Google Sheet

Python Tutorial – Available JSON Data Feed

In Google search console API, you can grab the 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, 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.

request = {
'startDate': '2021-07-01',
'endDate': '2021-07-31',
'dimensions': ['query'],
'rowLimit': '2000'
}

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='http://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.

Python Tutorial – Create the Loop to Fetch and Save Data on the Google Sheets

As well as youtube video performance and Shopify product data I talked 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

Python Tutorial 18: Crontab Automates Running Amazon Competitor Price Tracker for Updating P&L Calculator and Product Market Value

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 Python Tutorial 19. We would send you the script immediately to your mailbox.

Contact us

I hope you enjoy reading Python Tutorial 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 my 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 to 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 code on Buyfromlo products and digital software
  • Subscribe to our monthly membership through Patreon to enjoy exclusive benefits (www.patreon.com/louisludigital)

Leave a Reply

Your email address will not be published. Required fields are marked *