Set Up Your SEO Analysis Workspace (Python + SQLite + Claude Code)

Full transcript

1,045 words · script draft, under review.

Intro

Three months into any SEO project, the folder looks like this. Exports with numbers in brackets. A spreadsheet called final final. And no way to know which file the chart in last month's report came from.

Analysis is only as good as the place it lives. If you can't rerun it, you can't trust it, and you can't re-measure later.

In this episode we set up a workspace that stays clean: a folder layout, Python, one SQLite database, a cache for every paid response, secrets that never enter the project, and Claude Code as an assistant that writes and runs the scripts, behind a cost gate.

The folder layout

Here's the layout I use. Five things at the top level.

Scripts: every pull and every analysis is a script, not a click. If I did it twice, it becomes a script.

Cache: the raw responses from every API, untouched, in one subfolder per source. This is the most valuable folder in the project. Paid data lives here once, and is never fetched again by accident.

Data: one SQLite database with the clean tables from episode five.

Reports: the outputs. Dated files, written by scripts, so every number in a report can be traced back to a script and a cache file.

And a short instruction file at the top for Claude Code, which we'll get to in a minute.

The rule behind the layout is one direction of flow. Cache feeds the database, the database feeds reports. Nothing is edited by hand in the middle.

Python and a virtual environment

Python is the glue. You don't need much: an HTTP library for the APIs, Google's client library for Search Console and Analytics, and pandas if you like tables. SQLite support is already built in.

Create a virtual environment inside the project, so the packages belong to this project and not to your whole machine. And write the packages into a requirements file. When you come back in six months, or move to another computer, one command gets you back to exactly the same setup.

The SQLite schema

Why SQLite? It's one file. No server, no password, nothing to keep running. It handles millions of rows on a laptop. And you can copy the whole database as easily as a spreadsheet.

The tables are the ones from the last episode: one raw table per source, with a primary key on the date and dimensions, so reruns overwrite instead of doubling.

I add one table that people usually forget: an API log. Every paid call writes a row. When, which endpoint, how many tasks, and what it cost according to the response. At the end of the month you can answer "what did this analysis cost" with one query, instead of a guess.

Secrets live outside

Keys and passwords never go in the project. Not in a script, not in a config file, not in the instruction file for the assistant.

They live in small files in your home folder, readable only by your user, and scripts read them as environment variables. The project can then be synced, shared or put in version control safely.

This matters even more with an AI assistant. Anything you paste into a chat ends up in a log. So you never type a key into the chat. If a key needs to be set, you run a small command yourself that reads it without showing it.

Claude Code as the analyst's assistant

Claude Code is an AI assistant that runs in your terminal, inside your project. It can read your files, write scripts and run them, and it asks before it does things.

For SEO analysis, that changes the speed of the work. I describe the question in plain English, and it writes the pull script, the SQL, or the chart. I review it, it runs it, and the output lands in reports.

The key is the instruction file in the project root. Claude Code reads it at the start of every session. That's where your rules live. Where the cache is. That every script must check the cache before calling an API. That keys are never read or printed. And the most important rule of all, which gets its own section.

The cost gate

The cost gate. Free calls, like Search Console and Analytics, can run freely. Paid calls cannot. Before any paid call, the assistant has to show what it will call, how many requests, and the estimated cost. Then it stops and waits for an explicit go from me.

An example with made-up numbers: two hundred keywords at depth ten, at about three tenths of a cent each. That's around sixty cents. Small. But the gate isn't about one run. It's about a loop that retries, or a script that runs over ten thousand keywords instead of a hundred. Without a gate, you find out on the invoice.

I build the gate in two layers. The rule in the instruction file, so the assistant asks. And a check in the code itself, a function that refuses to make a paid call without a confirmation flag. The rule handles intent. The code handles mistakes.

And after the call, the cost goes into the API log. Gate before, log after.

A day in the workspace

Here's how it fits together in practice.

I start with a question, in a sentence. Which pages lost clicks in the last four weeks, and did their position change? I give that to Claude Code. It reads the instruction file, finds the tables, and writes a short script.

I read the script before it runs. That's the human review step, and I never skip it. Not because the code is usually wrong, but because I want to know exactly what the number in the report means.

It runs. Free data flows from the cache and the database. If a paid call is needed, the gate stops it and asks. The result lands in reports as a dated file, with the script next to it. Next month, I run the same script again, and the comparison is honest.

Wrap-up

One folder, one database, a cache for everything, secrets outside, and a gate in front of every paid call. The workspace template, with the instruction file, is linked in the description. Next episode: we put this workspace to work, and let Claude Code run your first analysis with DataForSEO in about ten minutes. See you there.