SEO report example

Seven widgets that earn their slot — and the SQL behind each one.

A useful SEO report answers four questions: is search traffic growing, which queries and pages moved, what is close to page one, and what broke. Everything else is decoration that makes the report longer without making it more decidable.

The example below is seven widgets against Google Search Console, with the query for each. It is deliberately not a file to download: a static report is stale the day after it is generated, and the next client means rebuilding it. These queries run against your connected Search Console property and tablebi pin turns the set into one public URL that refreshes on its own — this one is live now, running on real portfolio data.

Connect the source

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

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

Source for this template: Google Search Console. One connected source and one live dashboard fit inside the free tier.

The widgets

1. Clicks and impressions trend

Is search traffic growing, and is it growing because of visibility or because of relevance?

WITH w AS (SELECT MAX(date) AS anchor FROM search_console_raw WHERE site = 'example.com')
SELECT date_trunc('week', date) AS week,
       SUM(clicks) AS clicks,
       SUM(impressions) AS impressions,
       ctr(SUM(clicks), SUM(impressions)) AS ctr
FROM search_console_raw, w
WHERE site = 'example.com' AND date > w.anchor - 90
GROUP BY 1 ORDER BY 1

2. Query movers

Which queries gained or lost the most impressions against the previous period?

WITH w AS (SELECT MAX(date) AS anchor FROM search_console_raw WHERE site = 'example.com')
SELECT query,
       SUM(impressions) FILTER (WHERE date > w.anchor - 28) AS now_impr,
       SUM(impressions) FILTER (WHERE date <= w.anchor - 28) AS prev_impr
FROM search_console_raw, w
WHERE site = 'example.com' AND date > w.anchor - 56
GROUP BY query
HAVING SUM(impressions) > 50
ORDER BY (now_impr - prev_impr) DESC
LIMIT 25

3. Striking distance

Which queries sit just off page one, where a small move is worth real clicks?

WITH w AS (SELECT MAX(date) AS anchor FROM search_console_raw WHERE site = 'example.com')
SELECT query,
       avg_position(SUM(position * impressions), SUM(impressions)) AS pos,
       SUM(impressions) AS impressions
FROM search_console_raw, w
WHERE site = 'example.com' AND date > w.anchor - 28
GROUP BY query
HAVING avg_position(SUM(position * impressions), SUM(impressions)) BETWEEN 8 AND 20
   AND SUM(impressions) > 100
ORDER BY impressions DESC
LIMIT 25

4. Page performance

Which pages carry the traffic, and which are indexed but earning nothing?

WITH w AS (SELECT MAX(date) AS anchor FROM search_console_raw WHERE site = 'example.com')
SELECT page,
       SUM(clicks) AS clicks,
       SUM(impressions) AS impressions,
       ctr(SUM(clicks), SUM(impressions)) AS ctr
FROM search_console_raw, w
WHERE site = 'example.com' AND date > w.anchor - 28
GROUP BY page ORDER BY impressions DESC LIMIT 30

5. Low-CTR at good position

Which pages rank well but are not being clicked — a title and snippet problem, not a ranking one?

WITH w AS (SELECT MAX(date) AS anchor FROM search_console_raw WHERE site = 'example.com')
SELECT page,
       avg_position(SUM(position * impressions), SUM(impressions)) AS pos,
       ctr(SUM(clicks), SUM(impressions)) AS ctr,
       SUM(impressions) AS impressions
FROM search_console_raw, w
WHERE site = 'example.com' AND date > w.anchor - 28
GROUP BY page
HAVING avg_position(SUM(position * impressions), SUM(impressions)) < 10
   AND ctr(SUM(clicks), SUM(impressions)) < 0.02
   AND SUM(impressions) > 200
ORDER BY impressions DESC

6. Branded against non-branded

Is growth coming from people who already knew you, or from demand you earned?

WITH w AS (SELECT MAX(date) AS anchor FROM search_console_raw WHERE site = 'example.com')
SELECT CASE WHEN lower(query) LIKE '%yourbrand%' THEN 'branded' ELSE 'non-branded' END AS kind,
       SUM(clicks) AS clicks,
       SUM(impressions) AS impressions,
       ctr(SUM(clicks), SUM(impressions)) AS ctr
FROM search_console_raw, w
WHERE site = 'example.com' AND date > w.anchor - 28
GROUP BY 1

7. Query and page pairs

Which query is actually carrying each page — the pairing that tells you what a page is really ranking for.

WITH w AS (SELECT MAX(date) AS anchor FROM search_console_raw WHERE site = 'example.com')
SELECT page, query,
       SUM(impressions) AS impressions,
       avg_position(SUM(position * impressions), SUM(impressions)) AS pos
FROM search_console_raw, w
WHERE site = 'example.com' AND date > w.anchor - 28
GROUP BY page, query
HAVING SUM(impressions) > 20
ORDER BY impressions DESC LIMIT 30

What to leave out

Three things appear in most SEO report templates and change no decision:

• A domain authority score. A third-party estimate of a Google signal that Google does not publish. It moves slowly, you cannot act on it directly, and it invites arguments about the vendor's model rather than about the site.
• Total keyword counts. "Ranking for 4,182 keywords" counts positions 90 through 100 the same as position 3.
• A backlink count without context. Referring domains that matter and spam that arrived on its own land in the same number.

If a widget cannot finish the sentence "if this moves, we will…", it is decoration.

The caveat that belongs on the report itself

Search Console runs two to three days behind, and it applies a privacy threshold that anonymises very low-frequency queries — which means the sum of your per-query clicks is smaller than your total clicks, always. That is not missing data and it is not a bug in the report.

Every answer TableBI returns carries that caveat with it, alongside per-source freshness, so a reader who notices the discrepancy finds the explanation attached rather than emailing you about it. Anything you hand to a client should carry the same note.

From seven queries to one URL

Once the widgets are right, pinning turns the set into a hosted dashboard that re-runs its own queries on a schedule. The client gets a link rather than an attachment, and next month is not a rebuild.

Pin the set as one live URL

tablebi pin --title "SEO report example"
→ https://you.tablebi.com/d/dsh_…  # public, read-only, refreshes itself

The URL needs no login from whoever you send it to, and it re-runs its own queries rather than freezing a moment. Here is one running on real data. It carries no branding of yours — there is no white-label or custom-domain option today, which is the trade against a reporting platform.

Questions people ask

Other templates

Want the arithmetic before the wiring? The free marketing calculators run these same formulas in the browser.