How to create monthly Google Search Console API reports with EcommerceTools

Google Search Console is a really useful tool for search marketers since it shows what is happening data-wise before organic search visitors reach your website. Google Analytics only shows you what results you’re getting after someone has clicked a search engine result, so you miss out on seeing how many impressions you’re getting, what sort of click through rate (CTR) you’re obtaining, and what your average position in the Search Engine Results Pages (SERPS) is.

By monitoring the number of pages and queries that are generating impressions and clicks, and by optimising your content using search engine optimisation (SEO) techniques, you can increase the number of people who see your pages in the SERPs, increase the number of people who click them, and generate more traffic for your site, which should help increase conversions.

While this is useful, and while the Google Analytics API is much more feature-rich, the Google Search Console API is much more basic, so getting these results out in a format you can use for long-term monitoring and reporting is harder. In this project, I’ll show you how you can use the EcommerceTools Python package to query Google Search Console for these monthly metrics and create a single Pandas dataframe containing the historic performance for each month of a 13-month period.

Import the packages

First, open up a Jupyter notebook and install my EcommerceTools Python package. This can be used to perform a range of ecommerce, marketing, and SEO related tasks, including querying the Google Search Console API.

You can install it using the Pip package manager by entering the command below in a code cell and then executing it. Then, import the Pandas, Numpy, and Datetime packages, including timedelta, as well as the seo module from ecommercetools.

!pip3 install ecommercetools

import pandas as pd
import numpy as np
import datetime as dt
from datetime import timedelta
from ecommercetools import seo

Set up EcommerceTools

First, you will need to create a client secrets JSON key using the Google API Console to allow EcommerceTools to authenticate and retrieve your data. To save putting hard-coded values in the rest of the project, we’ll create some variables to store these values.

The key variable holds the path to your JSON keyfile, the site_url contains the URL of the Google Search Console property you want to query, and the start_date and end_date define the time period you wish to examine. Importantly, the end date must refer to the last day of the calendar month. I’ve selected a 13 month period, to allow me to observe the year-on-year change for the site.

key = "client_secrets.json"
site_url = "http://example.com"
start_date = "2020-05-01"
end_date = "2021-06-30" # This must be the last day

Calculate the start date and end date of each month

Since Google Search Console doesn’t provide a way to group data by month, we have to do this manually. The easiest way to do this is to assemble multiple API queries and then join the output. However, to do this, we need to know the start date and end date for each month that fell within the larger time period we defined in the variables above.

Since this is the sort of code snippet I might want to re-use in another project, I’ve wrapped things up in a little function called get_month_start_and_end_dates(). This takes the start_date (i.e. 2020-05-01) and the end_date (i.e. 2021-06-30) and identifies all of the calendar months within, returning a Pandas dataframe containing the month_start and end_date for each month, as well as a period which shows the year-month value (i.e. 2020-05 for May 2020).

Since the Google Search Console API requires dates to be passed in as strings, not datetimes, we need to do a bit of data munging and use the datetime strftime() function to ensure that the final dates we return in the dataframe are strings, which will show as the object data type when you run info() on the dataframe.

def get_month_start_and_end_dates(start_date, end_date):
    """Return a Pandas dataframe containing the month start and end dates between a start and end date"""
    
    start_date_object = dt.datetime.strptime(start_date, '%Y-%m-%d')
    end_date_object = dt.datetime.strptime(end_date, '%Y-%m-%d')

    month_start = pd.date_range(start_date_object, end_date_object, freq='MS')
    month_end = pd.date_range(start_date_object, end_date_object, freq='M')

    df = pd.DataFrame({'period': 'period', 'month_start': month_start, 'month_end': month_end})
    df['period'] = df['month_start'].dt.strftime('%Y-%m')    
    df['month_start'] = df['month_start'].dt.strftime('%Y-%m-%d')
    df['month_end'] = df['month_end'].dt.strftime('%Y-%m-%d')
    return df

Running the get_month_start_and_end_dates() with our start_date and end_date variables returns a dataframe I’ve called df_dates which contains the period, month_start, and month_end for each month detected between the two dates defined earlier. Running df_dates.info() confirms that these are in the object data type, so should work correctly when passed to the Google Search Console API.

df_dates = get_month_start_and_end_dates(start_date, end_date)
df_dates.head()

df_dates.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 14 entries, 0 to 13
Data columns (total 3 columns):
 #   Column       Non-Null Count  Dtype 
---  ------       --------------  ----- 
 0   period       14 non-null     object
 1   month_start  14 non-null     object
 2   month_end    14 non-null     object
dtypes: object(3)
memory usage: 464.0+ bytes

Fetch Google Search Console data for each month

Next, we can create another function to fetch our data. This takes four values: key is a string holding the path to your Google API Console client secrets JSON keyfle, which is used to authenticate; site_url is the URL of the property in GSC you want to query, and start_date and end_date refer to the long period of 13 months we defined at the beginning.

The first step of the function is to run the get_month_start_and_end_dates() function to create a dataframe called df_dates which will be used internally to create each API query. We then create an empty Pandas dataframe called df_metrics, which contains columns for the metrics we’re going to fetch from Search Console.

The next bit is technically somewhat inefficient, so will be slow, but there’s no other way to do this in Search Console. It iterates over each row in df_dates using the iterrows() function and runs a separate GSC query for each calendar month by passing in the month_start and month_end from the given month to a standard payload, and then passing this to the query_google_search_console() function of EcommerceTools.

When the data are returned by query_google_search_console(), we extract the bits we need, calculate the metrics, and add them to a dictionary called row. In the first column we’ll store the year-month in period, then we’ll count the number of unique pages and queries using nunique(), then calculate the total number of impressions and clicks using sum(), and calculate the mean of the average click through rate and position using mean(). (You can, of course, make this more accurate by calculating the CTR manually, instead of averaging an average.)

Finally, we add the row dictionary to the df_metrics dataframe using the append() function with the important ignore_index=True argument, and return the final dataframe when we’re done.

def get_monthly_google_search_console_data(key, site_url, start_date, end_date): 
    """Return a Pandas dataframe containing monthly data from Google Search Console.
    
    Args:
        key (string): Path to client secrets JSON keyfile. 
        site_url (string): URL of site to query in Google Search Console. 
        start_date (string): Date string in YYYY-MM-DD format. 
        end_date (string): Date string in YYYY-MM-DD format. 
    
    Returns: 
        df (dataframe): Pandas dataframe of monthly data. 
    """
    
    df_dates = get_month_start_and_end_dates(start_date, end_date)
    df_metrics = pd.DataFrame(columns=['period', 'pages', 'queries', 'impressions', 'clicks', 'avg_ctr', 'avg_position'])

    for index, row in df_dates.iterrows():
    
        payload = {
            'startDate': row['month_start'], 
            'endDate': row['month_end'],
            'dimensions': ["date", "page", "query"],  
            'rowLimit': 25000,
            'startRow': 0
        }

        df = seo.query_google_search_console(key, site_url, payload)

        row = {
            'period': row['period'],
            'pages': df['page'].nunique(),
            'queries': df['query'].nunique(),
            'impressions': df['impressions'].sum(),
            'clicks': df['clicks'].sum(),
            'avg_ctr': round(df['ctr'].mean(), 1),
            'avg_position': round(df['position'].mean(), 1),
        }

        df_metrics = df_metrics.append(row, ignore_index=True)
    
    return df_metrics

Run the code

Finally, we can run our function. By passing in the key, site_url, and start_date and end_date, we can run a load of separate Google Search Console API queries and join the results together to create a single dataframe which shows the site’s organic search performance over time. Viewing data in this format is handy for reporting, because you can see at a glance what you did the previous month and the previous year.

df = get_monthly_google_search_console_data(key, site_url, start_date, end_date)
df.sort_values(by='period', ascending=False)

Matt Clarke, Thursday, July 08, 2021