The Search Console API sorts by clicks. That's why your keyword script misses the good stuff

#python #seo #webdev #tutorial

A while back I was rebuilding a keyword ranking report for an ecommerce client. The tool pulled the "top 20 queries by impressions" for each month. Looked fine.

Except one of the category's biggest head terms was blank in two separate months. 22,589 impressions, 6 clicks. It was ranking the whole time. Across the full grid, 55 of 132 cells read as empty, and every one of them was actually ranking.

The cause is one line in Google's docs that's easy to skim past: Search Analytics results come back sorted by clicks, descending. There's no parameter to change it.

So if you ask for 20 rows and then sort by impressions, you're re-sorting the top 20 by clicks. Anything with lots of impressions and almost no clicks never makes it into the set.

And that's exactly the keyword you were looking for. High impressions, position 8 to 20, barely any clicks. Striking distance.

The fix is boring

Pull everything, then filter and sort on your side.

The API gives you up to 25,000 rows per request, and you page with startRow. Keep going until a page comes back short.

def fetch_all(gsc, site, body, page_size=25000):
    """Page through startRow until the API runs out of rows."""
    rows, start_row = [], 0
    while True:
        req = dict(body, rowLimit=page_size, startRow=start_row)
        batch = gsc.searchanalytics().query(siteUrl=site, body=req).execute().get("rows", [])
        rows.extend(batch)
        if len(batch) < page_size:
            return rows
        start_row += page_size

Setup (5 minutes, mostly clicking)

In Google Cloud, create a project, enable the Google Search Console API, and create a service account. Download its JSON key.
In Search Console, go to Settings > Users and permissions and add the service account's email. Restricted access is enough.
pip install google-api-python-client google-auth

Watch the property name. A domain property is sc-domain:example.com. A URL-prefix property is the full URL with the trailing slash, https://www.example.com/. Get it wrong and you'll get a 403 that looks like a permissions problem.

The filter

Once you've got every query + page row, the rest is plain Python:

from collections import defaultdict

def striking_distance(rows, brand_terms, min_impr, min_pos, max_pos):
    pages_per_query = defaultdict(set)
    for r in rows:
        pages_per_query[r["keys"][0]].add(r["keys"][1])

    out = []
    for r in rows:
        query, page = r["keys"]
        if any(b in query.lower() for b in brand_terms):
            continue
        if r["impressions"] < min_impr or not (min_pos <= r["position"] <= max_pos):
            continue
        out.append({
            "query": query,
            "page": page,
            "impressions": r["impressions"],
            "clicks": r["clicks"],
            "ctr": round(r["ctr"] * 100, 2),
            "position": round(r["position"], 1),
            "pages_ranking": len(pages_per_query[query]),
        })
    # the sort the API won't do for you
    return sorted(out, key=lambda x: x["impressions"], reverse=True)

A few choices in there, so you can change them:

Brand terms out. People searching your brand name will find you anyway. They also inflate everything.
Positions 8 to 20. Bottom of page one and all of page two. Some people use 4 to 15. Depends how ambitious you're feeling.
100 impressions minimum over 28 days. Below that, the average position jumps around too much to trust.
pages_ranking. If it's above 1, two of your URLs are splitting the same query. That's cannibalisation, and it's usually a better fix than rewriting either page. I wrote up how to diagnose and fix it if you hit that.

Putting it together

import csv
import datetime as dt
from google.oauth2 import service_account
from googleapiclient.discovery import build

SITE = "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)

end = dt.date.today() - dt.timedelta(days=3)  # final data lags 2 to 3 days
start = end - dt.timedelta(days=27)
rows = fetch_all(gsc, SITE, {
    "startDate": start.isoformat(),
    "endDate": end.isoformat(),
    "dimensions": ["query", "page"],
})

hits = striking_distance(rows, ["example"], 100, 8, 20)
with open("striking_distance.csv", "w", newline="") as f:
    if hits:
        w = csv.DictWriter(f, fieldnames=hits[0].keys())
        w.writeheader()
        w.writerows(hits)

Open the CSV. The top rows are your list.

Two more things that'll confuse you

Your totals won't match the dashboard. Google hides rare queries for privacy, so the sum of your query rows is always lower than the property total. It's not a bug in your code. Don't try to reconcile it to the click.

Tracking a fixed list of keywords? Don't use a listing at all. Make one request per keyword with an exact-match filter and rowLimit: 1:

{"dimensionFilterGroups": [{"filters": [
    {"dimension": "query", "operator": "equals", "expression": "your keyword"}
]}]}

More calls, sure. But the row comes back whatever its click count, which is the whole point. That's how I fixed the report in the end, and I added a check that each keyword's monthly impressions add up to its total for the full period. If they don't, something's missing.

What to do with the list

Start with the rows where you're sitting at 8 to 12 with a CTR under 1%. Usually the page answers the query, just not in the title or the first paragraph. Rewrite those two things, wait three or four weeks, re-run the script and compare.

The rows at 15 to 20 usually need more than a title change. Leave them for later.

I drafted this with help from an AI assistant. The client story and its numbers are from my own work.

DEV Community