In my work as an SEO, I often wished for an easier way to use Google Search Console’s data. I wanted to see keywords for a page across different countries. For example, when working on North America, it would be helpful to see total impressions and clicks for Canada and the USA, as well as the average position of queries in these countries.
When I learned python, I found new ways to use it for my SEO work. But, I still didn’t know how to get data from multiple countries. The main problem was I didn’t know how to connect to the Google Search Console API.
A few months ago, I saw a useful video by Mihir Naik. In this video, Mihir showed how to connect to the Google Search Console API. After watching the video, I found new ways to use the GSC data. Now, I can do many things with python and GSC API that used to take me a lot of time using spreadsheets.
Today, I want to share one useful way to use the GSC data. But, this is not easy for everyone. You need to be an SEO, know how to use data and Google Search Console, and how to use python and Google Colab. And of course, you should watch Mihir’s video.
A Quick Look at What the Script Does
If you can do all these things, you will like the python script I will share. Let me explain what it does.
This script uses Python to get data from the Google Search Console API. You can set specific details like date range, dimensions (country, query, page), row limit, and more. It also lets you set filters, like country and specific webpage. The script then gets the data from the API and puts it into a dataframe for each country. The data includes details like country, keyword, page, impressions, clicks, and positions. After this, the script merges the data from USA and Canada into one dataframe, calculates the click-through rate (CTR), and averages the position. Lastly, it groups the impressions, clicks, and average position by keyword.
Presenting the Complete Code
Now that you know what the script does, I’m going to share the complete code with you. It delivers a comprehensive dataset packed with insights on impressions, clicks, and the average position of keywords for a specific page, broken down by country. This information proves extremely useful when you need to examine such data across multiple nations. To help you better understand the functionality of each part of the code, I’ve added thorough comments. These annotations will guide you through each step, making it much clearer what’s happening at every stage.
# Define request parameters for US
request_body_usa = {
"startDate" : "<start_date>", # Starting date of the data retrieval. Replace <start_date> with actual date
"endDate" : "<end_date>", # Ending date of the data retrieval. Replace <end_date> with actual date
"dimensions" : ["country", "QUERY","PAGE"], # Data dimensions or the types of data we are interested in
"rowLimit" : 25000, # The maximum number of rows to return in the response
"dataState" : "final", # Data state, we are interested in the final state
"dimensionFilterGroups" : [ # Filter the data to only include USA and specific page
{
"filters" : [
{
"dimension" : "country", # Filter by country
"operator" : "equals",
"expression" : "usa" # Country equals USA
},
{
"dimension" : "page", # Filter by page
"operator" : "equals",
"expression" : "<page_url>" # Replace <page_url> with actual page URL
}
]
}
]
}
# Define request parameters for Canada
request_body_can = {
"startDate" : "<start_date>", # Replace <start_date> with actual date
"endDate" : "<end_date>", # Replace <end_date> with actual date
"dimensions" : ["country", "QUERY","PAGE"],
"rowLimit" : 25000,
"dataState" : "final",
"dimensionFilterGroups" : [
{
"filters" : [
{
"dimension" : "country",
"operator" : "equals",
"expression" : "can" # Country equals Canada
},
{
"dimension" : "page",
"operator" : "equals",
"expression" : "<page_url>" # Replace <page_url> with actual page URL
}
]
}
]
}
# Query the data for USA and prepare the dataframe
response_data_usa = webmasters_service.searchanalytics().query(siteUrl=website, body = request_body_usa).execute()
df_usa = pd.DataFrame(response_data_usa['rows']) # Convert response to DataFrame
df_usa_keywords_pages_country = pd.DataFrame(df_usa['keys'].tolist(), columns=['country', 'keyword', 'page'])
df_usa = df_usa.drop('keys', axis=1) # Drop the 'keys' column
df_usa = pd.concat([df_usa_keywords_pages_country, df_usa], axis=1) # Merge the DataFrame with country, keyword, page info
# Query the data for Canada and prepare the dataframe
response_data_can = webmasters_service.searchanalytics().query(siteUrl=website, body = request_body_can).execute()
df_can = pd.DataFrame(response_data_can['rows']) # Convert response to DataFrame
df_can_keywords_pages_country = pd.DataFrame(df_can['keys'].tolist(), columns=['country', 'keyword', 'page'])
df_can = df_can.drop('keys', axis=1) # Drop the 'keys' column
df_can = pd.concat([df_can_keywords_pages_country, df_can], axis=1) # Merge the DataFrame with country, keyword, page info
# Merge USA and Canada data, and calculate CTR and average position
df = pd.concat([df_usa, df_can], ignore_index=True) # Merge USA and Canada data
df['ctr'] = df['ctr'].apply(lambda x: round(x, 2)) # Round CTR to 2 decimal places
df['position'] = df['position'].apply(lambda x: round(x, 2)) # Round position to 2 decimal places
df = df.reset_index(drop=True)
df_total = df.groupby('keyword').agg({ # Group by keyword and calculate total impressions, total clicks, and average position
'impressions': 'sum',
'clicks': 'sum',
'position': 'mean'
}).reset_index()To end, this Python script is very useful for SEO professionals who want to get detailed SEO data across different countries. It saves a lot of time and makes our work easier. The world of SEO is always changing and there is always something new to learn. I hope you found this helpful and I would love to hear your thoughts. Please feel free to ask any questions or share your own experiences in the comments.