Build a Striking-Distance Keyword Finder in Python: Score GSC Opportunities by Position and CTR Gap

Open Google Search Console, sort by impressions, and stare for ten minutes. Every SEO team has done it. The problem is that “eyeballing the queries report” is not a process — it doesn’t scale past a few hundred rows, it ignores click-through-rate context, and it gives you a different answer depending on who’s looking and how much coffee they’ve had. The queries that move revenue are usually hiding in the middle of the table: ranking on page one or two, pulling real impressions, and leaking clicks because the position or the title isn’t quite good enough.

This post walks through a small, repeatable Python pipeline that pulls your Search Console data via the API, scores every query by how much recoverable traffic it represents, and hands you a ranked action queue instead of a 40,000-row spreadsheet. We’ll build the scoring model, explain why raw average position is a bad filter on its own, and wire the whole thing into an n8n schedule so a fresh opportunity list lands in your inbox every Monday.

What “striking distance” actually means

The phrase usually refers to keywords ranking in positions 11–20 — close enough to page one that a small push tips them over. That heuristic is fine as a starting point, but used alone it is misleading in two directions.

First, a query at position 14 with eight impressions a month is not an opportunity; it’s noise. Second, a query already sitting at position 6 with a 4% CTR when comparable positions earn 11% is a huge opportunity that the 11–20 filter throws away entirely. Position is a proxy. What you actually care about is the gap between the clicks a query earns today and the clicks it could earn if it performed like its peers. That gap is what we’ll score.

Pull the data from the Search Analytics API

The Search Console API returns the same dimensions as the UI, but without the 1,000-row export ceiling and with clean access to the query × page grain you need for triage. Authenticate with a service account that has been added as a user on the property, then request 90 days of data dimensioned by query and page.

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

SITE_URL = "sc-domain:example.com"
creds = service_account.Credentials.from_service_account_file(
    "service-account.json",
    scopes=["https://www.googleapis.com/auth/webmasters.readonly"],
)
gsc = build("searchconsole", "v1", credentials=creds)

def fetch(start, end, start_row=0, rows=25000):
    body = {
        "startDate": start,
        "endDate": end,
        "dimensions": ["query", "page"],
        "rowLimit": rows,
        "startRow": start_row,
        "dataState": "final",
    }
    return gsc.searchanalytics().query(siteUrl=SITE_URL, body=body).execute()

records, row = [], 0
while True:
    resp = fetch("2026-03-30", "2026-06-28", start_row=row)
    batch = resp.get("rows", [])
    if not batch:
        break
    for r in batch:
        q, page = r["keys"]
        records.append({
            "query": q, "page": page,
            "clicks": r["clicks"], "impressions": r["impressions"],
            "ctr": r["ctr"], "position": r["position"],
        })
    row += len(batch)

df = pd.DataFrame(records)

Paginate with startRow until a request returns no rows — that’s how you get past the UI’s export limit and capture the long tail where most striking-distance queries live.

Build an expected-CTR curve from your own data

Generic CTR-by-position benchmarks from a blog post in 2019 do not describe your SERPs. Brand queries, sitelinks, AI overviews, and shopping packs all distort the curve per site. So build the curve from the data you just pulled: bucket every row by rounded position and take the median CTR in each bucket. The median is deliberate — it resists the brand-term outliers that would otherwise inflate the top positions.

df["pos_bucket"] = df["position"].round().clip(1, 20).astype(int)
curve = df.groupby("pos_bucket")["ctr"].median().to_dict()

# Smooth gaps so every position 1-20 has a value
for p in range(1, 21):
    if p not in curve:
        neighbours = [curve[k] for k in (p - 1, p + 1) if k in curve]
        curve[p] = sum(neighbours) / len(neighbours) if neighbours else 0.0

def expected_ctr(pos):
    return curve.get(int(round(min(max(pos, 1), 20))), 0.0)

Score the opportunity

Now the core idea. For each query–page pair, estimate the clicks it would earn if its CTR matched the median for a realistic target position, then subtract the clicks it earns today. Two distinct opportunity types fall out of the same formula:

• CTR gap — the query already ranks where it ranks, but underperforms the curve. Fixable with a better title and meta description, structured data, or richer snippets. No new links required.
• Position gap — the query sits in striking distance (roughly positions 5–20) and would earn far more if pushed up a few spots. Fixable with on-page work, internal links, or content depth.

TARGET_POS = 5  # a realistic, not fantasy, target

def opportunity(row):
    imp = row["impressions"]
    current_clicks = row["clicks"]
    # potential if it performed like the median at the better of (current, target)
    aim = min(row["position"], TARGET_POS)
    potential_clicks = imp * expected_ctr(aim)
    return max(potential_clicks - current_clicks, 0)

df["opportunity_clicks"] = df.apply(opportunity, axis=1)
df["gap_type"] = df["position"].apply(
    lambda p: "ctr_gap" if p <= TARGET_POS else "position_gap"
)

actions = (
    df[df["impressions"] >= 50]            # kill the noise floor
      .sort_values("opportunity_clicks", ascending=False)
      .head(100)
      .round({"position": 1, "ctr": 3, "opportunity_clicks": 1})
)
actions.to_csv("striking_distance_actions.csv", index=False)

The impressions >= 50 floor is the single most important line in the script. Without it, the top of your list fills with positions-14 phantom queries that will never produce a click no matter what you do. Tune the floor to your traffic volume — small sites might drop it to 20, large ones raise it to 200.

Turn scores into an action queue, not a spreadsheet

A ranked CSV is better than eyeballing, but you can go one step further and route each row to the right fix. Group the top opportunities by gap_type: send the ctr_gap rows to your title and meta workflow, and the position_gap rows to whoever owns content and internal linking. If you’ve already built the title-rewrite pipeline from our guide on automating title and meta description rewrites with GSC and an LLM, the ctr_gap slice is its perfect input — you’re feeding it exactly the pages where a better snippet pays off most.

For the position-gap rows, hand the query and its URL to a brief generator. Our walkthrough on automating SEO content briefs with a SERP and LLM pipeline takes a target query and returns an outline, so the opportunity finder becomes the front door of your content refresh process rather than a standalone report.

Schedule it in n8n for a weekly digest

Running this by hand defeats the purpose. Wrap the script in a small Flask or FastAPI endpoint (or just an n8n Execute Command node calling the script directly), then build a four-node n8n workflow: a Schedule Trigger set to Monday 7am, an HTTP Request or Execute Command node that runs the pipeline, a Code node that formats the top 20 rows into a readable table, and a Slack or Gmail node that delivers it. The output is a weekly message that reads like “here are this week’s 20 highest-value, lowest-effort SEO fixes” — ranked, deduplicated, and tied to a specific URL.

If you’d rather visualize trends than receive a digest, pipe the scored table into the same warehouse you use for reporting. Our guide to building a self-updating SEO dashboard with GSC bulk export, BigQuery, and Looker Studio shows how to land the data and chart opportunity-clicks-over-time so you can watch the backlog shrink as you work it.

What to expect when you run it

The first run is almost always uncomfortable in a useful way. On most established sites, the top of the list is dominated by ctr_gap rows — pages already ranking on page one that are simply under-clicked. That’s good news: those are the cheapest wins, often a title rewrite away, with no link-building or new content required. The position_gap rows tend to cluster around a handful of templated or thin pages that need real content work, which is exactly the prioritization signal you want.

Because the curve is rebuilt from your own data every run, the model adapts as your SERP features change. When an AI overview starts eating clicks for a query cluster, the median CTR for those positions drops, the expected-CTR falls, and those queries quietly de-prioritize themselves — no manual benchmark maintenance required. Re-run it monthly, work the top 20, and let the next run resurface whatever you didn’t get to.

The broader point is the one we keep coming back to at SEO Automation Club: the value isn’t in the data export, it’s in the scoring and routing that turns raw GSC rows into a prioritized, owned action queue. Build the pipeline once and every future triage is a five-minute review instead of a Friday-afternoon staring contest.

Found this useful? Bookmark SEO Automation Club and check back each week — we publish a new working automation playbook (real code, real workflows, no fluff) every morning.

Frequently asked questions

What’s the difference between this and a rank tracker?

A rank tracker tells you where you rank for a fixed keyword list you chose in advance. This pipeline works backwards from your actual Search Console impressions to surface queries you may never have tracked — and it scores them by recoverable clicks, not just position, so it tells you which ones are worth acting on.

Why use the median CTR instead of standard industry benchmarks?

Industry CTR curves are averaged across millions of unrelated SERPs and don’t account for brand terms, sitelinks, AI overviews, or shopping packs on your specific queries. A curve built from your own data describes your reality and updates automatically as the SERP changes around you.

How often should I run the opportunity finder?

Monthly is a good cadence for most sites — frequent enough to catch new opportunities and decaying pages, infrequent enough that you can actually work the previous list before the next one lands. High-velocity publishers may want it weekly via the n8n schedule described above.

Do I need the GSC API, or can I use the CSV export?

You can prototype with the UI’s CSV export, but it caps at 1,000 rows and won’t give you the clean query×page grain at scale. The API paginates past that limit and lets you automate the whole thing, which is the entire point of building a repeatable pipeline.