---
title: "Hands-on: Search Console data with Python and Google Sheets"
description: "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…"
url: https://optimizeall.com/learn/seo-and-content-strategy/search-console-data-with-python-and-sheets
updated: 2026-10-05
---

SEO & Content Strategy · AI search and measuring SEO · lesson 17 of 17 · 14 min

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

## 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

```python
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

```python
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:

```text
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.

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

Lecture coming soon · 13 chapters · about 8 minutes. Read the full transcript below.

1. Search Console + Python + Sheets
2. Why the API
3. One-time setup
4. The extractor
5. Four analyses
6. Own benchmarks
7. Example 1 (simple)
8. Example 2 (illustrative)
9. Into Sheets / Looker Studio
10. Read cannibalisation carefully
11. Watch me do it: the monthly API routine
12. Work responsibly
13. Recap

## Lecture transcript

### Search Console + Python + Sheets

The Search Console interface is great, until you need more than a thousand rows, or want to see query and page together for three months, every month, without clicking for an hour. That's when a little Python pays for itself. In this lecture you'll set up the Search Console API, write a reusable extractor with pagination, run four practical analyses, and get the results into Google Sheets or Looker Studio.

### Why the API

Why go beyond the interface? It shows up to a thousand rows per table and one comparison at a time. The API returns far more rows, combines dimensions like query, page and date, and makes monthly analyses repeatable. Data is available for roughly the last sixteen months, so export regularly for longer history, or use Search Console's bulk export to BigQuery on large sites.

### One-time setup

Setup is a one time job. In Google Cloud, create a project and enable the Search Console API. Create a service account and download its key, stored outside your code folder, with an environment variable pointing to it. Then, in Search Console settings, add the service account's email as a user. Restricted access is enough to read data. Finally install the Google API client, Google auth and pandas.

### The extractor

Here's the key idea behind the extractor. Think of it like a librarian fetching records in boxes. Each request can return at most twenty five thousand rows, so the script asks for the first box, then the next, using a start row parameter, until a box comes back less than full. It also retries politely with a short backoff if it hits a quota error. The result is one tidy table with your dimensions plus clicks, impressions, CTR and position.

### Four analyses

Now four analyses. One: striking distance, non brand queries ranking eight to twenty with real demand. Two: low CTR for position. Here's a smart trick. Instead of generic industry CTR curves, which vary wildly by query type and SERP features, compare each row with your own site's median CTR at that position. Three: cannibalisation, queries where two or more of your pages each get meaningful impressions. And four: question queries, raw material for FAQs and cluster pages.

### Own benchmarks

Why your own benchmark matters. Imagine two queries at position three. One has an AI Overview, a video carousel and shopping ads above the results. The other has plain blue links. Their normal CTR will be very different. Generic curves would flag the first as underperforming when it's actually typical. Comparing against your own median at each position is still rough, but it's grounded in your real results pages.

### Example 1 (simple)

Worked example one, simple. A solo blogger with a cooking site runs just the question analysis once. It surfaces fifty questions she ranks for between positions five and fifteen, like can you freeze biryani and why is my naan dough sticky. She adds short, answer first sections to the relevant recipes, and writes two new posts for the biggest gaps. One script, one afternoon, a month's worth of content ideas grounded in real demand.

### Example 2 (illustrative)

Worked example two, realistic and illustrative. A Karachi edtech company's content lead runs the script on the first working day of each month. Striking distance surfaces forty non brand queries, and the team refreshes six pages covering them. The low CTR check finds a guide whose title promises free courses the site no longer offers. Fixing the title restores CTR. Cannibalisation shows two near identical posts on freelancing skills, which get consolidated. The routine takes about two hours and replaces guesswork with a short, prioritised list.

### Into Sheets / Looker Studio

Getting results into Sheets is easy. Save each table as a CSV and import it. For no code users, Sheets add ons and the Looker Studio Search Console connector can pull data directly, and Looker Studio is a good way to share dashboards. In Sheets, a few formulas go a long way: a brand flag with REGEXMATCH, CTR versus your median with XLOOKUP, and a top pages summary with QUERY.

### Read cannibalisation carefully

Let's pause and read the cannibalisation output properly, because it's easy to over react. Suppose the query UAE VAT registration shows three of your URLs. One is your registration guide, one is your VAT service page, and one is an old news post about a threshold change. Is that a problem? The guide and the service page serve different intents, informational and transactional, so both can be right. The old news post is the one to look at. If it's outdated and competes with the guide, merge its useful bits and redirect it.

### Watch me do it: the monthly API routine

Watch me do it. I'll run the monthly Search Console routine for a Karachi edtech company. First, in the terminal, I check the two environment variables are set: the site property and the path to the service account key, which lives outside the project folder. Next, I run the extractor for the last full quarter with query and page as dimensions. It paginates through three pages of results and prints forty eight thousand rows. Then I open a notebook and run the four analyses. Striking distance: forty non brand queries between positions eight and twenty with real demand. Low CTR for position: I build the site's own median CTR by rounded position, and a guide on free courses comes out at less than half its benchmark. I check the title. It still promises free courses the site no longer offers. That explains it. Cannibalisation: two near identical posts on freelancing skills swap for the same queries. Questions: sixty question queries, useful for FAQs. Now I export each result to CSV and import them into the team's Google Sheet, where a QUERY formula summarises the top pages. Finally, I write the top ten actions with evidence beside each, and annotate the date in the shared log. About two hours, start to finish.

### Work responsibly

Work responsibly. Keep the service account key out of code and version control, and restrict who can read it. Remember that some rare queries are anonymised, so query level totals won't match page totals. And annotate site releases, updates and campaigns in a shared log, so trends can be interpreted. Common mistakes: pulling only a thousand rows and assuming it's everything, comparing query and page totals, using generic CTR curves, and hard coding credentials.

### Recap

Recap. The Search Console API gives you more rows, combined dimensions and repeatable analyses. Use a service account with the key path in an environment variable, and paginate. Run striking distance, low CTR for position, cannibalisation and question analyses, benchmark against your own site, and share results through Sheets or Looker Studio. Try this now. Run the extractor for last quarter, or use an export, and produce a one page list of your top ten actions with the evidence for each.

## 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.

## Try it

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.

- [Previous: Measuring SEO with Google Search Console](https://optimizeall.com/learn/seo-and-content-strategy/search-console-measurement)
- [All lessons of SEO & Content Strategy](https://optimizeall.com/learn/seo-and-content-strategy)