Academy
Optimize All
Sign in
Start learning free

SEO & Content StrategyAI search and measuring SEO · Lesson 17 of 17

Hands-on: Search Console data with Python and Google Sheets

Article · 14 min · 8 min lecture

Video lecture

Hands-on: Search Console data with Python and Google Sheets

13 chapters · about 8 min · full transcript

Coming soon

Chapter 1 of 13

Search Console + Python + Sheets

  • API setup
  • A reusable extractor
  • Four analyses
  • Into Sheets / Looker Studio

The narrated lecture is in production

Every chapter is scripted and ready. Browse the chapters and read the full transcript now — the video will appear here when it’s published.

Chapters

Why go beyond the interface

The Search Console interface shows up to 1,000 rows per table and one comparison at a time. The Search Console API returns far more rows, lets you combine dimensions (query × page × date), and makes monthly analyses repeatable. Data is available for roughly the last 16 months, so export regularly if you want longer history — or use Search Console's bulk data export to BigQuery for large sites.

Setup (once)

  1. In Google Cloud, create a project and enable the Google Search Console API.
  2. Create a service account and download its JSON key. Store the key outside your code folder and point an environment variable at it.
  3. In Search Console → Settings → Users and permissions, add the service account's email as a user (restricted access is enough to read data).
  4. Install libraries: pip install google-api-python-client google-auth pandas.

A reusable extractor with pagination

import os
import time
import pandas as pd
from google.oauth2 import service_account
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError

SITE = os.environ.get("GSC_SITE", "sc-domain:example.com")
creds = service_account.Credentials.from_service_account_file(
    os.environ["GSC_SERVICE_ACCOUNT_JSON"],
    scopes=["https://www.googleapis.com/auth/webmasters.readonly"])
gsc = build("searchconsole", "v1", credentials=creds)

def fetch(start: str, end: str, dimensions: list[str], filters: list[dict] | None = None) -> pd.DataFrame:
    rows, start_row = [], 0
    while True:
        body = {"startDate": start, "endDate": end, "dimensions": dimensions,
                "rowLimit": 25000, "startRow": start_row}
        if filters:
            body["dimensionFilterGroups"] = [{"filters": filters}]
        for attempt in range(3):
            try:
                resp = gsc.searchanalytics().query(siteUrl=SITE, body=body).execute()
                break
            except HttpError as e:
                if attempt == 2:
                    raise
                time.sleep(2 ** attempt)          # simple backoff for quota errors
        batch = resp.get("rows", [])
        rows += [{**dict(zip(dimensions, r["keys"])), "clicks": r["clicks"],
                  "impressions": r["impressions"], "ctr": r["ctr"], "position": r["position"]}
                 for r in batch]
        if len(batch) < 25000:
            return pd.DataFrame(rows)
        start_row += 25000

if __name__ == "__main__":
    df = fetch("2026-06-01", "2026-08-31", ["query", "page"])
    df.to_csv("gsc_query_page_2026Q3.csv", index=False)
    print(len(df), "rows")

Four analyses in a few lines each

import re
BRAND = re.compile(r"yourbrand|your brand", re.I)
df["brand"] = df["query"].str.contains(BRAND)

# 1) Striking distance: non-brand queries ranking 8-20 with real demand
striking = df[(~df["brand"]) & df["position"].between(8, 20) & (df["impressions"] >= 200)]

# 2) Low CTR for position: compare each row with the site's median CTR at that rounded position
df["pos_bucket"] = df["position"].round().clip(1, 20)
bench = df.groupby("pos_bucket")["ctr"].median().rename("median_ctr")
df = df.join(bench, on="pos_bucket")
low_ctr = df[(df["impressions"] >= 500) & (df["ctr"] < 0.5 * df["median_ctr"])]

# 3) Cannibalisation: queries where 2+ pages each get meaningful impressions
multi = df[df["impressions"] >= 50].groupby("query")["page"].nunique()
cannibal = df[df["query"].isin(multi[multi >= 2].index)].sort_values(["query", "clicks"], ascending=[True, False])

# 4) Question queries for FAQ/cluster ideas
questions = df[df["query"].str.match(r"^(how|what|why|when|which|can|does|is|are)\b", case=False)]

Using your own site's median CTR by position as the benchmark avoids relying on generic industry CTR curves, which vary widely by query type and SERP features.

Getting results into Google Sheets

The simplest route is CSV: striking.to_csv("striking.csv", index=False), then File → Import in Sheets. For no-code users, Google Sheets add-ons and the Looker Studio Search Console connector can pull data directly; Looker Studio is a good way to share dashboards without sharing raw exports. In Sheets, useful formulas on an imported export:

Brand flag:      =REGEXMATCH(LOWER(A2), "yourbrand|your brand")
CTR vs median:   =D2 / XLOOKUP(ROUND(E2), Bench!A:A, Bench!B:B)
Top pages:       =QUERY(Data!A:E, "select B, sum(C) group by B order by sum(C) desc limit 20", 1)

Working responsibly with the data

  • Keep the service-account key out of code and version control; restrict who can read it.
  • Remember that some rare queries are anonymised: query-level totals won't match page totals.
  • Annotate changes (site releases, updates, campaigns) in a shared log so trends can be interpreted.

Worked example (illustrative)

A Karachi edtech company's content lead runs the script on the first working day of each month. Striking-distance analysis surfaces 40 non-brand queries on positions 8–20; the team refreshes six pages that cover them. The low-CTR-for-position check finds a guide whose title promises "free" courses the site no longer offers — fixing the title restores CTR. Cannibalisation output shows two near-identical posts on "freelancing skills"; they're consolidated. The monthly routine takes about two hours and replaces guesswork with a short, prioritised list.

Common mistakes

  • Pulling only 1,000 rows from the interface and assuming it's everything.
  • Comparing query-level totals with page-level totals.
  • Using generic CTR curves instead of your own benchmarks.
  • Hard-coding credentials in scripts or notebooks.

Key takeaways

  • The Search Console API returns far more rows than the interface and makes monthly analyses repeatable.
  • Use a service account added as a Search Console user, with the key path in an environment variable, and paginate with startRow.
  • Run striking-distance, low-CTR-for-position, cannibalisation and question analyses with pandas.
  • Move results to Sheets or Looker Studio, benchmark against your own CTR by position, and keep credentials secure.

Check your understanding

Quick questions to lock in the lesson. They don’t count towards your certificate.

  1. Why paginate API requests with startRow?
  2. Why benchmark CTR against your own site's median CTR by position?

Put it into practice

Set up API access for a site you manage (or use an export), run the four analyses, and produce a one-page list of the top ten actions with the evidence for each.

Enrol for free to save your progress

Reading is always free. Enrol to keep your place, take the final assessment and earn a verifiable certificate.