AI SEO · GEO · AEO agency – India & worldwideanup@anupsrivastava.com+91-8840146999
Get Free Proposal

How to Generate SEO Reports From Search Console Exports Using Python

Generating SEO reports from Search Console by hand is slow and error-prone. The web interface limits how many rows you can see, filters must be rebuilt every time, and combining query and page data in spreadsheets quickly becomes unmanageable on larger sites. Python solves these problems with a few dozen lines of code. This guide walks through a practical workflow: getting the data, cleaning it, grouping queries into meaningful clusters and producing page-level reports your team can act on.

What You Need

  • Python 3 with the pandas library installed
  • Search Console data, either exported as CSV files or pulled through the Search Console API
  • A spreadsheet or dashboard tool to share the results

The interface export is limited to 1,000 rows per report, which is fine for small sites. For larger sites, use the Search Console API, which returns up to 25,000 rows per request and supports paging, or the bulk data export to BigQuery for very large properties.

Step 1: Load the Data

If you export through the API, request the dimensions query, page and date, with metrics clicks, impressions, CTR and position. Save each period as a CSV, then load it:

import pandas as pd

df = pd.read_csv("gsc_query_page.csv")
df.columns = [c.lower() for c in df.columns]
df["date"] = pd.to_datetime(df["date"])

Keep raw exports unchanged and save processed versions separately. That way you can always rebuild reports if your logic changes.

Step 2: Clean the Data

Small cleaning steps prevent misleading reports:

  • Convert queries to lowercase and remove extra spaces
  • Normalise page URLs by removing tracking parameters and fragments
  • Separate branded and non-branded queries using a list of brand terms
  • Remove test or staging URLs if they appear
brand_terms = ["seo plus geo", "seoplusgeo"]
df["query"] = df["query"].str.lower().str.strip()
df["page"] = df["page"].str.split("?").str[0].str.split("#").str[0]
df["branded"] = df["query"].str.contains("|".join(brand_terms))

Step 3: Group Queries Into Clusters

Individual queries are too granular for reporting. Grouping them into topics makes trends visible. Start with simple rules based on keywords:

rules = {
    "ai visibility": ["ai visibility", "ai search", "chatgpt", "gemini"],
    "local seo": ["local seo", "near me", "google business"],
    "technical seo": ["crawl", "index", "core web vitals", "robots"],
}

def assign_cluster(q):
    for cluster, terms in rules.items():
        if any(t in q for t in terms):
            return cluster
    return "other"

df["cluster"] = df["query"].apply(assign_cluster)

Review the “other” group regularly and add rules. For large sites, text embeddings can cluster queries by meaning, but rules are transparent and easy for colleagues to understand. This mirrors the thinking behind keyword cluster SEO.

Step 4: Build SEO Reports From Search Console Data at Page Level

Page-level reporting shows which URLs gain or lose and why. Compare two periods:

cur = df[df["date"] >= "2026-07-01"]
prev = df[(df["date"] >= "2025-07-01") & (df["date"] < "2025-10-01")]

def summarise(d):
    g = d.groupby("page").agg(clicks=("clicks", "sum"),
                              impressions=("impressions", "sum"))
    g["ctr"] = g["clicks"] / g["impressions"]
    return g

report = summarise(cur).join(summarise(prev), lsuffix="_cur", rsuffix="_prev")
report["clicks_change"] = report["clicks_cur"] - report["clicks_prev"]
report.sort_values("clicks_change").head(20)

Note that average position should be weighted by impressions when aggregated, not simply averaged, otherwise low-volume queries distort the result.

Useful Page-Level Views

ReportQuestion it answers
Biggest losersWhich pages lost the most clicks year on year?
CTR outliersWhich pages have high impressions but low CTR?
Cluster ownershipWhich page receives most clicks for each cluster?
CannibalisationWhich clusters have several pages splitting clicks?
Branded shareHow dependent is each page on branded searches?

The CTR outlier view is especially useful now that AI Overviews reduce clicks for some queries. Pages with stable impressions but falling CTR may need a different strategy, as explained in our guide to zero-click search optimization.

Step 5: Export and Share

Write results to Excel or Google Sheets, or load them into Looker Studio:

report.to_csv("page_report.csv")

Add short written notes for each section. Numbers alone rarely lead to action. Explain what changed, why it probably changed and what the team should do next.

Step 6: Automate the Workflow

Once the script works, schedule it to run weekly or monthly. Common options include a scheduled job on a server, a cloud function, or a notebook run on a schedule. Store credentials securely, log each run and alert the team if data is missing. Our SEO automation services team builds these pipelines so reports arrive automatically.

A Monthly Report Structure

A practical monthly report built from this script usually has five short sections. First, a summary of non-branded clicks and impressions compared with the same month last year. Second, cluster performance, showing which topics grew or declined. Third, the top 20 losing pages with a probable cause for each. Fourth, CTR outliers that may be affected by AI Overviews or weak titles. Fifth, recommended actions with owners. Keep the same structure every month so readers know where to look, and keep the raw tables in an appendix for analysts.

Common Pitfalls

  • Comparing periods with different lengths or seasonal patterns
  • Averaging positions without weighting by impressions
  • Forgetting that Search Console anonymises some rare queries, so query totals are lower than page totals
  • Mixing data from different properties, such as http and https versions
  • Treating small changes on low-volume pages as meaningful

Extending the Reports

Join Search Console data with analytics conversions, CRM revenue or AI citation tracking to see the full picture. A page losing clicks but gaining AI citations and conversions may be performing better than the click data suggests. That combined view is the basis of a strong data-driven SEO strategy.

Frequently Asked Questions

Do I Need to Be a Programmer to Do This?

Basic Python knowledge is enough. The examples above can be adapted with a little practice.

Is the API Better Than CSV Exports?

For larger sites, yes. It returns far more rows and can be automated.

How Far Back Does Search Console Data Go?

Sixteen months. Save exports regularly if you want longer history.

Python turns Search Console data into fast, repeatable insight. For help building reporting pipelines, explore our technical SEO audit, review programmatic SEO services, learn about API content generation, read about long-tail keyword SEO, see our AI SEO traffic growth case study, or explore our SEO services.

Free Proposal