Python and Pandas for SEO Reporting: Automate Dashboards in 6 Steps

Copying numbers from Search Console into a slide deck every Monday morning is not reporting, it is data entry. Python and pandas for SEO reporting turns that same weekly task into a script that runs in minutes and never mistypes a cell.

An agency managing a dozen client accounts spends a real chunk of every month on reporting. Someone exports Search Console data, exports analytics data, and pastes both into a spreadsheet by hand.

Then the same person builds a chart, writes a summary, and repeats the whole process for the next client. Nothing about that process changes month to month except the numbers.

A script replaces the copying step entirely. It pulls the same two data sources, merges them, calculates the same comparisons, and writes the same report shape every time.

The person who used to spend a day on reporting spends that day on the parts a script cannot do, reading the numbers and deciding what they mean for the client.

Four libraries cover the whole pipeline in this guide, from pulling data to writing the finished report.

# create an isolated environment so package versions don't clash
python -m venv venv
source venv/bin/activate      # Windows: venv\Scripts\activate

pip install pandas google-api-python-client google-analytics-data openpyxl matplotlib

The pandas library does the merging and math. google-api-python-client talks to Search Console, and google-analytics-data is Google's official client for pulling Google Analytics 4 data. openpyxl writes a formatted spreadsheet, and matplotlib draws the trend chart.

All four are documented in full on the Python Package Index, which is worth bookmarking before the first run in case a version pin needs checking later.

The Search Console API returns clicks, impressions, and average position for a date range, one row per query or page depending on the dimension requested.

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

SCOPES = ['https://www.googleapis.com/auth/webmasters.readonly']
creds = service_account.Credentials.from_service_account_file(
    'service_account.json', scopes=SCOPES
)
service = build('searchconsole', 'v1', credentials=creds)

def get_gsc_data(site_url, start_date, end_date):
    request = {
        'startDate': start_date,
        'endDate': end_date,
        'dimensions': ['query'],
        'rowLimit': 5000
    }
    response = service.searchanalytics().query(
        siteUrl=site_url, body=request
    ).execute()
    rows = response.get('rows', [])
    return pd.DataFrame([{
        'query': r['keys'][0],
        'clicks': r['clicks'],
        'impressions': r['impressions'],
        'position': round(r['position'], 1)
    } for r in rows])

Running this once for the current month and again for the prior month gives two data frames ready to compare, which is exactly what the report needs.

Google's official google-analytics-data client wraps the GA4 Data API and returns sessions, users, and conversions in a similar shape to the Search Console pull.

from google.analytics.data_v1beta import BetaAnalyticsDataClient
from google.analytics.data_v1beta.types import RunReportRequest, DateRange, Dimension, Metric
import pandas as pd

client = BetaAnalyticsDataClient()

def get_ga4_data(property_id, start_date, end_date):
    request = RunReportRequest(
        property=f'properties/{property_id}',
        dimensions=[Dimension(name='landingPage')],
        metrics=[Metric(name='sessions'), Metric(name='conversions')],
        date_ranges=[DateRange(start_date=start_date, end_date=end_date)]
    )
    response = client.run_report(request)
    rows = []
    for row in response.rows:
        rows.append({
            'landing_page': row.dimension_values[0].value,
            'sessions': int(row.metric_values[0].value),
            'conversions': float(row.metric_values[1].value)
        })
    return pd.DataFrame(rows)

The property ID and a service account with API access are the only setup pieces here, since the request shape stays the same across every client property.

Search Console rows and GA4 rows rarely line up on the same key out of the box, so a short cleaning step makes the merge reliable.

import pandas as pd

def build_report_frame(gsc_df, ga4_df):
    gsc_df['query'] = gsc_df['query'].str.strip().str.lower()
    combined = pd.concat([gsc_df], ignore_index=True)
    combined['clicks'] = combined['clicks'].fillna(0).astype(int)
    combined['impressions'] = combined['impressions'].fillna(0).astype(int)
    return combined.sort_values('clicks', ascending=False)

fillna matters here because a query that had impressions but zero clicks sometimes comes back missing entirely rather than as a literal zero, and an uncaught blank cell breaks every calculation after it.

Clients care less about the raw numbers than about the direction they moved, so the report needs a comparison column, not just two tables side by side.

import pandas as pd

def compare_periods(current_df, previous_df, key, metric):
    merged = current_df.merge(
        previous_df, on=key, suffixes=('_now', '_prev'), how='outer'
    ).fillna(0)
    merged[f'{metric}_change'] = merged[f'{metric}_now'] - merged[f'{metric}_prev']
    merged[f'{metric}_pct_change'] = (
        merged[f'{metric}_change'] / merged[f'{metric}_prev'].replace(0, 1) * 100
    ).round(1)
    return merged.sort_values(f'{metric}_change', ascending=False)

The replace(0, 1) guard stops a division by zero from crashing the script the first time a query shows up with no prior month history at all.

A finished pandas data frame can become a formatted spreadsheet in a few lines, complete with a chart the client can open without any setup on their end. Search Engine Journal's guide to SEO reporting covers what most clients actually want to see in a report, which is worth checking against the layout a script produces.

import pandas as pd
import matplotlib.pyplot as plt

def write_excel_report(report_df, client_name, output_path):
    with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
        report_df.to_excel(writer, sheet_name='Query Performance', index=False)

def save_trend_chart(monthly_totals, output_path):
    fig, ax = plt.subplots(figsize=(8, 4))
    ax.plot(monthly_totals['month'], monthly_totals['clicks'], marker='o', color='#4a9e24')
    ax.set_title('Organic Clicks by Month')
    fig.tight_layout()
    fig.savefig(output_path, dpi=150)

Every client gets the exact same file structure, so opening last month's report and this month's report side by side always feels familiar rather than like a new document to learn.

A script that still needs someone to remember to run it every month has only solved half the problem, so scheduling through GitHub Actions closes the loop.

name: Monthly SEO Report
on:
  schedule:
    - cron: '0 6 1 * *'
jobs:
  build_report:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - run: pip install -r requirements.txt
      - run: python build_report.py

The cron entry runs on the first day of every month, so each client report is already sitting in the output folder before anyone thinks to ask for it.

• Store credentials as secrets: the service account JSON and property IDs belong in GitHub Actions secrets, never committed to the repository.
• Keep one config file per client: a small YAML or JSON file per client keeps the same script reusable across every account.
• Log failures loudly: a report that silently fails to generate is worse than one that never existed, since nobody notices until a client asks.
• Version the output folder: keeping a dated copy of every report makes it trivial to compare against any past month, not just the previous one.
• Review before sending: automation builds the report, but a quick human glance before it reaches a client catches the occasional odd number an API returned.

JC Chouinard, whose Search Console API examples this series has referenced before, has also spoken publicly about applying Python across everyday SEO reporting work.

Video credit: JC Chouinard.

The same merge and compare pattern shown in this guide extends easily to a third data source, like a rank tracking export, once the two API pulls above feel familiar.