SEO dashboard: build one your agent maintains, in one command

An SEO dashboard is only worth having if it answers your actual Monday questions and stays current without you touching it. Most of what ranks for this term is template galleries — fifteen screenshots, forty KPIs, no opinion about which numbers matter. This guide goes the other way: define the five questions an SEO dashboard exists to answer, write five small SQL queries against raw Search Console data, and publish them as one live, self-refreshing URL with a single command. And the maintenance doesn't fall on you or on a BI seat — it falls on an AI agent that can query your search data directly.

The five questions an SEO dashboard should answer

Strip away the template clutter and a dashboard is just a set of standing questions. For SEO, five cover nearly everything that changes a decision:

1. Which queries are rising, and which are falling? Not a wall of every keyword — the movers. A query that gained or lost meaningful clicks week over week is a signal; the other three thousand flat rows are noise.
2. Which pages earn less CTR than their position should deliver? A page sitting at position 3 with a 1% CTR has a title or snippet problem. Fixing it is the cheapest win in SEO — no new content, no links, just better packaging for rankings you already own.
3. How fresh is the data I'm looking at? Search Console finalizes data two to three days late. A dashboard that doesn't say how current it is turns every end-of-chart dip into a false alarm.
4. How is the portfolio doing as a whole? If you run more than one site, you need the one-row-per-site view before you need any drill-down.
5. What actually changed this week? Page-level week-over-week deltas — the difference between reporting and rereading the same totals every Monday.

Notice what's not on the list: gauges, word clouds, an "SEO health score." A dashboard earns its screen space by changing what you do next, and the five questions above are the ones that do.

Why static SEO dashboards rot

If you've ever assembled one of these in a spreadsheet or a template tool, you know how it ends. Two failure modes do the killing.

Data hauling. Every static dashboard runs on exports: download the CSV, paste it into the sheet, fix the columns that shifted, screenshot it for the deck. The export is stale the moment it lands, and because the hauling is manual it happens weekly at best — right up until the week you're busy, and then the dashboard is quietly three weeks old.

Definition drift. One chart excludes brand queries, another doesn't. One tab uses a 28-day window, another uses "this month." CTR gets recomputed slightly differently in two places. A month in, two numbers on the same page disagree and nobody can say why — and the moment a dashboard needs an asterisk, people stop trusting all of it.

The fix isn't a prettier template. It's structural: data that syncs itself, every number defined in SQL you can actually read, and upkeep owned by something with infinite patience for chores. That last part is where an agent comes in.

Connect Search Console once

The build below uses TableBI, a hosted data backend designed to be driven by Claude Code — Anthropic's terminal-based coding agent — or any agent that can run a CLI. The shape is three verbs: pipe sources in, ask with read-only SQL, pin the result to a live URL. Install the CLI and plant the skill so the agent knows the commands exist:

npm i -g @tablebi/cli && tablebi install

Then connect Search Console — one OAuth round-trip in the browser:

tablebi connect gsc --site sc-domain:example.com

Data syncs into two altitudes. facts holds cross-channel unified definitions — useful the day you put ads data next to search. search_console_raw is the one this dashboard runs on: lossless rows straight from GSC, with site, query, page, clicks, impressions and position columns. From then on, an agent session opens with tablebi context --json, which returns connected sources, data freshness and the command cheatsheet in one JSON blob it can hold in context.

The five widgets, in SQL

Each question becomes one query. Question 3 — freshness — needs no SQL at all: every answer ships with a trust block that states per-source freshness, so five widgets cover the list. One convention first: every window is anchored to MAX(date), never current_date.

1 — The pulse. Daily clicks over 28 days, as a line. It's the chart everyone looks at first, so give it to them:

tablebi ask "SELECT date, SUM(clicks) AS clicks
             FROM search_console_raw
             WHERE date >= (SELECT MAX(date) FROM search_console_raw) - 28
             GROUP BY date ORDER BY date"

2 — Query movers. Clicks this week versus the week before, ranked by the swing. Flip the sort and the same widget shows what's bleeding:

tablebi ask "SELECT query,
             SUM(CASE WHEN date >= (SELECT MAX(date) FROM search_console_raw) - 7
                 THEN clicks ELSE 0 END) AS last_7d,
             SUM(CASE WHEN date < (SELECT MAX(date) FROM search_console_raw) - 7
                 THEN clicks ELSE 0 END) AS prior_7d
             FROM search_console_raw
             WHERE date >= (SELECT MAX(date) FROM search_console_raw) - 14
             GROUP BY query
             ORDER BY last_7d - prior_7d DESC LIMIT 15"

3 — CTR laggards. The highest-leverage query in the set: pages ranking in the top five positions whose CTR doesn't match the real estate. Note the ctr() macro — the definition lives in the backend, not re-derived by hand in every chart:

tablebi ask "SELECT query, page, AVG(position) AS avg_position,
             SUM(impressions) AS impressions,
             ctr(SUM(clicks), SUM(impressions)) AS ctr
             FROM search_console_raw
             WHERE date >= (SELECT MAX(date) FROM search_console_raw) - 28
             GROUP BY query, page
             HAVING AVG(position) <= 5 AND SUM(impressions) >= 500
             ORDER BY ctr ASC LIMIT 15"

Everything this returns is a rewrite candidate: you already rank; the snippet just isn't earning the click.

4 — Portfolio overview. One row per site — the thirty-second answer to "how are we doing overall":

tablebi ask "SELECT site, SUM(clicks) AS clicks,
             SUM(impressions) AS impressions,
             ctr(SUM(clicks), SUM(impressions)) AS ctr
             FROM search_console_raw
             WHERE date >= (SELECT MAX(date) FROM search_console_raw) - 28
             GROUP BY site ORDER BY clicks DESC"

5 — Pages, week over week. The same CASE pattern as the movers, grouped by page instead of query — which URLs gained ground and which gave it up:

tablebi ask "SELECT page,
             SUM(CASE WHEN date >= (SELECT MAX(date) FROM search_console_raw) - 7
                 THEN clicks ELSE 0 END) AS last_7d,
             SUM(CASE WHEN date < (SELECT MAX(date) FROM search_console_raw) - 7
                 THEN clicks ELSE 0 END) AS prior_7d
             FROM search_console_raw
             WHERE date >= (SELECT MAX(date) FROM search_console_raw) - 14
             GROUP BY page ORDER BY last_7d DESC LIMIT 15"

You don't have to write any of this yourself. Describe the widget in plain English inside a Claude Code session — "show me top-5 rankings with weak CTR" — and the agent writes the SQL, runs it read-only, and shows you the table to sanity-check. The SQL stays visible either way, which is exactly what keeps definitions from drifting.

Pin it: one command, one live URL

Once the five queries look right, freeze them into a dashboard. ::line renders a widget as a line chart; the default is a table:

tablebi pin --title "SEO overview" \
  --widget "Daily clicks (28d)::line=SELECT date, SUM(clicks) AS clicks FROM search_console_raw …" \
  --widget "Query movers, 7d vs prior=SELECT query, … FROM search_console_raw …" \
  --widget "CTR laggards (top-5 positions)=SELECT query, page, … FROM search_console_raw …" \
  --widget "Portfolio by site=SELECT site, … FROM search_console_raw …" \
  --widget "Pages, week over week=SELECT page, … FROM search_console_raw …"
✓ published → https://<ws>.tablebi.com/d/dsh_…  (public, read-only, self-refreshing)

What comes back is a public, read-only URL. It refreshes itself — syncs are triggered by visits and run on a schedule — so the link you drop in a Slack channel in January is still current in June. Viewers see a "Powered by TableBI" footer, and next to each widget an ask button: anyone looking at the dashboard can copy a one-sentence question to send back to whoever owns it.

Maintenance becomes a conversation. "Filter brand queries out of the movers widget" is one sentence to your agent; it edits the SQL and re-pins. The dashboard never turns into the thing nobody dares touch — which is how the static ones die.

Here's a real one — live Search Console data across a portfolio of sites, pinned exactly this way. It's not a mockup; it refreshes as the source syncs: