Google Search Console API: Automate SEO Reporting and Monitoring With Python

pip install google-auth google-auth-oauthlib google-api-python-client gspread pandas

from google.oauth2.credentials import Credentials
from google_auth_oauthlib.flow import InstalledAppFlow
from googleapiclient.discovery import build

SCOPES = ['https://www.googleapis.com/auth/webmasters.readonly']

def authenticate():
    flow = InstalledAppFlow.from_client_secrets_file('client_secrets.json', SCOPES)
    credentials = flow.run_local_server(port=0)
    return build('searchconsole', 'v1', credentials=credentials)

service = authenticate()
site_url = 'sc-domain:yourdomain.com'  # or 'https://yourdomain.com/'

# Fetch top queries with impressions > 100, excluding branded terms
response = service.searchanalytics().query(
    siteUrl=site_url,
    body={
        'startDate': '2026-01-01',
        'endDate': '2026-05-01',
        'dimensions': ['query'],
        'dimensionFilterGroups': [{
            'filters': [{
                'dimension': 'query',
                'operator': 'notContains',
                'expression': 'brand-name'  # exclude branded queries
            }]
        }],
        'rowLimit': 25000,
        'startRow': 0
    }
).execute()

rows = response.get('rows', [])
for row in rows:
    if row['impressions'] > 100:
        print(f"Query: {row['keys'][0]}, Clicks: {row['clicks']}, Position: {row['position']:.1f}")

import gspread
from google.oauth2.service_account import Credentials
from datetime import datetime, timedelta

def get_weekly_data(service, site_url):
    end_date = datetime.now().strftime('%Y-%m-%d')
    start_date = (datetime.now() - timedelta(days=7)).strftime('%Y-%m-%d')

    response = service.searchanalytics().query(
        siteUrl=site_url,
        body={
            'startDate': start_date,
            'endDate': end_date,
            'dimensions': ['page', 'query'],
            'rowLimit': 5000
        }
    ).execute()
    return response.get('rows', [])

def write_to_sheets(rows, sheet_name='GSC Weekly Report'):
    gc = gspread.service_account(filename='service_account.json')
    sh = gc.open(sheet_name)
    ws = sh.get_worksheet(0)

    headers = ['Page', 'Query', 'Clicks', 'Impressions', 'CTR', 'Position', 'Date']
    data = [headers]
    today = datetime.now().strftime('%Y-%m-%d')

    for row in rows:
        data.append([
            row['keys'][0],
            row['keys'][1],
            row['clicks'],
            row['impressions'],
            f"{row['ctr']:.2%}",
            f"{row['position']:.1f}",
            today
        ])

    # Append rows (skip header if sheet already has it)
    ws.append_rows(data[1:])

def check_traffic_drop(service, site_url, threshold=0.20):
    """Alert if clicks dropped more than threshold% week over week."""
    def get_clicks(start, end):
        r = service.searchanalytics().query(
            siteUrl=site_url,
            body={'startDate': start, 'endDate': end, 'dimensions': ['date'], 'rowLimit': 7}
        ).execute()
        return sum(row['clicks'] for row in r.get('rows', []))

    this_week_end = datetime.now().strftime('%Y-%m-%d')
    this_week_start = (datetime.now() - timedelta(days=7)).strftime('%Y-%m-%d')
    last_week_end = (datetime.now() - timedelta(days=7)).strftime('%Y-%m-%d')
    last_week_start = (datetime.now() - timedelta(days=14)).strftime('%Y-%m-%d')

    this_week = get_clicks(this_week_start, this_week_end)
    last_week = get_clicks(last_week_start, last_week_end)

    if last_week > 0:
        change = (this_week - last_week) / last_week
        if change < -threshold:
            send_alert(f"Traffic dropped {abs(change):.0%} week over week ({last_week} -> {this_week} clicks)")

def fetch_all_rows(service, site_url, body):
    all_rows = []
    start_row = 0
    while True:
        body['startRow'] = start_row
        response = service.searchanalytics().query(
            siteUrl=site_url, body=body
        ).execute()
        rows = response.get('rows', [])
        if not rows:
            break
        all_rows.extend(rows)
        start_row += len(rows)
        if len(rows) < 25000:
            break
    return all_rows

# Pseudocode for joining GSC and GA4 data
import pandas as pd

gsc_df = pd.DataFrame(gsc_rows)
ga4_df = pd.read_csv('ga4_export.csv')

merged = pd.merge(gsc_df, ga4_df, left_on='keys', right_on='pagePath', how='left')
merged['conversion_rate'] = merged['conversions'] / merged['clicks']