Automated SEO Optimization: Automate the Export, Not the Edit

Advice about automating SEO almost always opens with a purchase. Here is the belief worth breaking first: the slow part of this job was never the judgement. It is the copying. The Search Console interface hands back a trimmed view of data you already own, and somebody loses a Monday morning moving it into a spreadsheet. A scheduled script deletes that Monday permanently. What you finish holding is a short weekly list of pages that have earned a person's attention.

Who this suits: anyone who can run a small script or adapt a template, with a verified property in Search Console. What it costs: nothing. What to skip: any product promising to tune your pages unsupervised. The reason is at the bottom.

Step 1: Page through the API until it runs out of rows

The web report caps what it will hand over. The API is considerably more generous. Google's reference on retrieving your full dataset sets two boundaries. A single response carries at most 25,000 rows, inside an overall ceiling that the documentation puts at "a maximum of 50K rows of data per day per search type", ordered by clicks. Collecting past the first response means sending the identical request again with startRow advanced, until something comes back empty.

curl -s -X POST \
  "https://www.googleapis.com/webmasters/v3/sites/[PROPERTY_ENCODED]/searchAnalytics/query" \
  -H "Authorization: Bearer [TOKEN]" \
  -H "Content-Type: application/json" \
  -d '{
        "dimensions": ["query", "page"],
        "startRow": [OFFSET],
        "rowLimit": 25000,
        "startDate": "[WINDOW_OPENS]",
        "endDate": "[WINDOW_CLOSES]"
      }'

What comes back:

• One row per pairing, with the query and the URL carried in keys
• Four measures on every row: clicks, impressions, ctr, position
• Nothing at all, once startRow has passed the final available row

What to do next:

• Append every response to one file before reading any of it. Analysis mid-collection produces half-answers.
• Close the window three days before today and open it 28 days before that, because the most recent days arrive incomplete and read artificially low.
• Save a dated file each week. Comparison later costs nothing; reconstructing a lost week costs everything.
• Use a service account to authenticate, and keep that credential off any shared drive.

Nothing in this step ranks anything. You are holding raw material, and no more than that.

Step 2: Cut the export down to the rows a person should read

Nobody reads forty thousand rows. Three conditions do the triage. The first hunts for queries where you already surface but sit too far down to collect the click, a situation practitioners call striking distance. A position band running from roughly eight to twenty works as a starting point. It is a convention, not something Google published, so adjust it once you see what your own site returns.

KEEP the row when
    [Impressions]  is at least [FLOOR]
    [Position]     falls inside [LOWER] .. [UPPER]
    [CTR]          sits under [YOUR_SITE_AVERAGE]
THEN sort descending by [Impressions]
THEN drop every row containing [BRAND_TERMS]

What comes back:

• Pairings that carry demonstrated demand and visible headroom at the same time
• A per-URL tally of qualifying queries, which quietly ranks your update candidates for you

What to do next:

• Collapse everything by URL. A single page carrying five qualifying queries beats five pages carrying one apiece.
• Read each query against the page attached to it. A genuine mismatch calls for a new page, not for tuning an old one.
• Delete brand terms before you look. You already win those, and they will crowd out everything useful.
• Cut the list at twenty rows. Longer queues become queues nobody opens on a Tuesday.

What stays hidden here is diagnosis. Knowing a page is stuck tells you nothing about the cause, and only reading the page alongside the live results will.

Step 3: Convert each surviving row into a one-screen brief

Now automate the tedious typing. Every URL in your twenty gets a brief in one fixed shape, assembled from fields you have already collected. A spreadsheet formula handles it. So does twenty lines of script. A model can draft the proposed-edit sentence, as long as you restrict it to the fields you hand over and nothing else.

URL ................ [ADDRESS]
Earning impressions  [QUERY] at position [N], [COUNT] impressions
                     [QUERY] at position [N], [COUNT] impressions
Title today ........ [EXISTING TITLE]
Open question ...... [Is each query answered above the fold?]
Proposed edit ...... [ONE SENTENCE, UNAPPROVED]
Accountable ........ [PERSON], by [DATE]

What comes back:

• Twenty briefs in one identical shape, which makes the whole stack readable in minutes
• A person and a date attached to each, so no item quietly becomes everybody's problem

What to do next:

• Require a human read of every proposed edit before anyone opens the page. These systems estimate likely wording, and a confident-sounding rationale is precisely what they generate best.
• Discard any proposal that smuggles in a claim you cannot trace back to something real.
• Limit the week's queue to what the team will genuinely finish. An abandoned queue trains people to ignore queues.
• Write the ship date beside each change, so there is something to read back in two months.

No brief makes an edit. It prepares one. Where a model drafts yours, our guide to the fields a brief has to carry before anyone writes is the companion piece.

Step 4: Put it on a timer and keep approval in human hands

Run the first three steps weekly on whatever scheduler your stack already runs. One destination only, so nobody has to ask where this week's queue went.

RUNS ....... [DAY] at [TIME], weekly
LANDS IN ... [ONE destination: sheet, channel or tracker]
SHOUTS WHEN  authentication fails
             the response is empty
             the week's clicks fall off a cliff
NEVER ...... publishes. Approval is a person, with a name.

What comes back:

• A queue already waiting when the week starts
• An alarm the moment the job breaks, which stops breakage from impersonating a quiet week

What to do next:

• Leave the gate shut. Discovery and preparation belong on a timer. Publication belongs to someone who can be asked why.
• Audit the job itself every quarter and delete any column nobody has looked at since it was added.
• Log shipped changes in one place, so the next argument about what works has evidence underneath it.

Priorities stay outside this step. A queue sorted by impressions is not sorted by what the business needs this quarter, and no schedule will notice the difference.

The loop end to end, and how often to run it

1. Collect every row from the API, advancing the offset to exhaustion.
2. Reduce to striking-distance pairings that have real impression counts behind them.
3. Emit one fixed-shape brief per URL, unapproved by definition.
4. Schedule it weekly, alarm on failure, and let nothing publish itself.

Cadence: the job runs weekly, the results get read monthly, and the job's own design gets questioned quarterly. For what else deserves a timer, see our starting point for automating marketing work and our notes on keeping these workflows upright as volume climbs.

Check the quotas before you point a scheduler at this

A paging job raises an obvious worry: how many requests is too many. Google publishes the answer, and the numbers are reassuring once you read them. The usage limits page gives Search Analytics a per-site allowance of 1,200 queries per minute and a per-user allowance of the same, with the per-project ceiling at 40,000 queries per minute and 30,000,000 per day. A weekly job that sends a few dozen paged requests is not in the same universe as those figures.

The constraint that actually bites is the one that is not a request count. Alongside the query limits, the same page describes a load quota, measured in ten-minute chunks for the short term and one-day chunks for the long term, where a wider date range and finer grouping or filtering consume more of your allowance. So the expensive call is not a frequent one. It is a greedy one: sixteen months of history grouped by query and page and filtered three ways, requested repeatedly while somebody debugs a script.

One figure is worth writing down separately. URL Inspection is capped at 2,000 queries per day per site, with 600 per minute, per the published limits. If a later version of this loop checks indexing status as well as performance, that ceiling decides your approach: a site with tens of thousands of URLs cannot inspect them all in one night, so the inspection step has to work through a rotating sample rather than the whole set.

There is also a second route to the same data, and it is worth knowing where the fork is. Google's bulk data export streams raw Search Console data into BigQuery with no row ceiling at all. The price of that is setup and ownership: a Cloud project with billing enabled, the BigQuery and BigQuery Storage APIs switched on, and Google's export service account granted the BigQuery Job User and Data Editor roles. Search Console charges nothing for the export; Google Cloud charges its normal storage and query rates, above a free usage level. The first delivery lands up to 48 hours after configuration, nothing from before setup is ever exported, and changing the table schema causes the export to fail.

Page the API first, and switch only when it stops answering

The common recommendation now is to skip straight to the bulk export, on the grounds that the API is fiddly and row-limited. We would not. For a site with a few thousand URLs, the paging loop above answers the question this playbook exists to answer, it needs no Cloud project, no billing relationship and no service account with write access to a dataset, and a marketer who can edit a script can own the whole thing without filing a ticket. That independence is worth more than the elegance of a warehouse table.

The cost nobody mentions about the warehouse route is organisational rather than technical. The export lives inside somebody's Cloud project, under a service account that somebody granted roles to, and its table schema cannot be casually edited without breaking the feed. That is three dependencies on people outside marketing, in exchange for data you are not yet using. Teams that build the warehouse before they have read one week's queue end up with a very tidy dataset and the same unopened spreadsheet.

The call changes in two situations, and one of them is urgent. If you need query-level history reaching further back than the interface will give you, set the export up now rather than when you need it, because it does not backfill and the day you configure it is the earliest day you will ever be able to compare against. And if your filtered queue is routinely longer than the team can read, you have outgrown the triage, not the API: fix the filter before you buy a warehouse.

Four ways this playbook fails

• The data is a mirror, not a map. The API reports queries where you already appeared. Demand you never surfaced for is simply absent from the file, and no filter can reveal what was never recorded.
• Unsupervised editing crosses a line. Google's spam policies treat producing pages in bulk that add nothing for a reader as scaled content abuse, explicitly including mechanical transformations such as substituting synonyms or machine translating. Letting a job rewrite pages without review walks toward that pattern one harmless-looking edit at a time.
• Small sites get coincidence. At a few hundred impressions weekly, these conditions return noise dressed as opportunity. Lengthen the window before loosening the filter.
• Silent breakage. An expired token yields an empty spreadsheet, and an empty spreadsheet reads exactly like a calm week. The alarm is the only part of this you cannot skip.

Collecting, filtering and formatting are the parts a script does better than any person. Deciding which page matters to the business this month is not, and delegating that decision to a schedule is how teams end up carefully optimising pages that nobody has ever bought from.