Blog

Build Your Own Rank Tracker With Search Console Data

Borja Obeso
Borja Obeso Co-founder, Distribb
Last updated
Title card: build your own rank tracker with Search Console data

Edward Sturm's team stopped trusting third-party rank trackers and built their own. He says anyone can do it in a couple of hours. If you want to build your own rank tracker too, this is his full method: Google Search Console data backed up to BigQuery, a small dashboard on top and a short list of things to leave out. I added the exact setup steps from Google's documentation.

Why build a rank tracker at all

Sturm's reason is data quality. He says third-party rank trackers are less reliable than they have ever been, because Google is making it harder for them to collect results and is feeding them bad data. He calls it poisoned SERPs.

The symptom is easy to recognize. Your tracker shows your rankings falling off a cliff week over week. You open Google Search Console and everything is stable, or going up.

That gap is dangerous when you are actively changing pages. You make an edit, the tracker shows a drop the next week, and you roll the edit back. If Search Console was flat the whole time, you just undid a change that might have helped.

Search Console is data straight from Google, so it is the best source you can get. It has one big limit: it keeps 16 months of history. A tracker built on it needs its own backup, and that is where BigQuery comes in.

If you only want to check positions by hand or compare paid tools, our guide on how to track keyword rankings covers that. This article is about building the tracker yourself.

Step 1: List only the keywords you target on purpose

Sturm's tracker does not follow every keyword his pages show up for. It follows the keywords the team is actively targeting, and nothing else.

A keyword counts as targeted if you did something deliberate for it. Maybe a whole page is built around it. Maybe you only added it to an H2 because you think an existing page could rank better for it. Either way, you chose it. Keywords you rank for by accident stay out, because they add noise and distort the averages.

Pick keywords because they are valuable to you, and volume is only one reason. His examples: the revenue a keyword would bring if you ranked well, a low-volume keyword that important people search, or a keyword that leads people to embed or share your product and link to you.

Put the list in a spreadsheet with one row per keyword and the page that targets it.

Milestone: a sheet with every keyword you are deliberately targeting and its target URL.

Step 2: Send Search Console data to BigQuery

Search Console has a built-in bulk data export to BigQuery, Google's data warehouse. Sturm says it is extremely inexpensive and pretty much free for most businesses. Google's own page says there is a free usage level and that storage and queries above it are charged.

The setup, from Google's Search Console Help:

  1. Create a Google Cloud project with billing set up.
  2. In the Cloud Console, go to APIs & Services and enable the BigQuery API and the BigQuery Storage API.
  3. Go to IAM and Admin, click Grant Access and add this principal: search-console-data-export@system.gserviceaccount.com. Give it two roles, BigQuery Job User and BigQuery Data Editor.
  4. In Search Console, open Settings, then Bulk data export.
  5. Paste your Cloud project ID. Use the ID, not the project number.
  6. Keep the default dataset name, searchconsole, and pick a dataset location. You can't easily change the location later.
  7. Click Continue to start the daily exports.

The first export arrives up to 48 hours later. The export does not backfill. Your history starts on the day you set it up, so this is worth doing today even if you build the dashboard next month. For older data, Google points you to the Search Console API or the reports.

One more line from the documentation: data piles up forever unless you set a partition expiration on the tables. If you set one, it has to be 14 days or longer, and you should not change the table schema.

Milestone: a searchconsole dataset in BigQuery with new rows landing every day.

Step 3: Query positions for your tracked keywords

The export creates a table called searchdata_url_impression, with one set of rows per day, query and URL. A query like this returns the weekly average position and clicks for each keyword and page over the last 90 days:

SELECT DATE_TRUNC(data_date, WEEK(MONDAY)) AS week, query, url, SUM(clicks) AS clicks, SUM(impressions) AS impressions, (SUM(sum_position) / SUM(impressions)) + 1.0 AS avg_position
FROM searchconsole.searchdata_url_impression
WHERE search_type = 'WEB' AND data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY) AND query IN ('your first keyword', 'your second keyword')
GROUP BY 1, 2, 3
ORDER BY 1 DESC, 2

Three rules from Google's query guidelines explain why it looks like that:

  • Always aggregate. The same query can appear on several rows for one day, so use SUM and GROUP BY every time.
  • Add one to the position. Position is stored zero-based, which is why the formula ends in + 1.0.
  • Limit by date. The tables are partitioned by date, and a date filter keeps the query cheap.

In practice you load your keyword sheet as a small table and join on it, so nobody has to edit the query when the list changes. The URL with the best average position for a keyword is the "top page" column Sturm shows in his tracker.

Milestone: one query that returns week, keyword, top URL, clicks and average position for your whole list.

Step 4: Add search volume

Sturm's team pulls average monthly search volume for each tracked keyword from DataForSEO's clickstream data API. He describes it as very cheap.

The volume column has one job. It lets you sort keywords by how much traffic they could bring if you ranked first, so you know which pages deserve work next.

The tracker also has a clicks column from Search Console. He debated including it and says they may still remove it. It is there to check that the volume estimates are roughly right.

If you don't want to pay for an API, type the volumes into your keyword sheet by hand from whatever keyword tool you already use. They change slowly.

Milestone: a monthly volume next to every tracked keyword.

Step 5: Label every keyword

Sturm calls labels extremely important. A label groups keywords by subfolder, hub page, intent or purpose, so you can watch a whole group move.

His example: all service pages sit under a services hub, and all the People Also Ask keywords sit under an FAQ hub. One label for services and one for PAA, and you can see how each hub is doing as a unit.

This only works if the site itself is organized. If your pages are grouped into hubs, the labels write themselves. If you can't decide which label a keyword belongs to, that usually points to a structure problem on the site. Our guide to topic cluster strategy covers how to set that structure up.

Milestone: a label column in the keyword sheet, with no keyword left unlabeled.

Step 6: Build the dashboard with an AI coding assistant

Sturm's team built the interface by describing it to an AI coding assistant, and he says the whole thing can be done in a couple of hours. Theirs runs on an analytics subdomain of the company site, and you sign in with an email address.

What his version shows:

  • Date ranges: all time, last 12 months, 6 months, 3 months, last month, last 30 days, last 12 weeks, last 28 days and last 7 days. Movements can be viewed by day, week or month.
  • Default view: three months, week by week.
  • Top graph: the average movement across all tracked keywords.
  • Keyword table: sortable by most moved, top ranks and average monthly volume, with the clicks column, the top URL and the label.
  • Keyword detail: the keyword, its label, its volume, the current ranking, the movement from the start of the period to the end, the average for the period and a graph over time.

To build yours, give the assistant three things: read access to the BigQuery dataset, the query from step 3 and a description of the screens above. Sturm says he will link screenshots of his dashboard in the episode description so you can hand them to an LLM as a reference.

Put a login in front of it. Ranking data for your money keywords is not something to leave on a public URL.

Milestone: a private page where you can open any tracked keyword and see its weekly graph.

Step 7: Keep an SEO change journal

The graph is only useful if you know what you changed and when. Sturm keeps a journal of every change made to SEO pages and posts.

His example goes like this. You revisit a page months after writing it and see it does not give the searcher what they want above the fold. You fix it, and the graph goes up. Then you keep improving it, use the keyword a few times too many, and the graph turns down. You open the journal, find what you changed around that date and undo it. He gives a second cause for a drop like that: a JavaScript element above the fold that Google can't read or that is broken.

A journal can be a spreadsheet with four columns: date, URL, what changed and why.

Milestone: every page edit from now on has a dated line in the journal.

What to leave out

Sturm is as firm about what the tracker should not show. More data means more noise, and noise makes it harder to decide.

  • Revenue. He disagrees with the advice to track only revenue each week for SEO. Revenue depends on your offer, pricing, brand, support, response speed, bugs, conversion rate work and trust. Rankings for your target keywords are the result an SEO can control most directly, even with links and off-site brand in the mix.
  • Keywords you don't target. They ruin the averages and pull attention away from the keywords that matter.
  • Extra metrics. His advice is to show the minimum. Even the clicks column is on probation.

One limit he doesn't dwell on: Search Console only reports on your own site. If you need competitor positions, positions in one specific city or a number for today, you still need a SERP-based tool. We compared them in our list of the best SEO rank trackers.

Automate it with Distribb

I'll be straight about this: Distribb is not a rank tracker, and it will not replace the BigQuery backup. Set up the export in step 2 either way, because history you never saved can't be recovered.

What Distribb automates is everything around the dashboard, and the step after it.

  • Step 1, choosing keywords: Distribb does the keyword research for your market, with search volume and difficulty, and plans each keyword as an article on a calendar date. Your target list exists from day one.
  • Steps 3 and 6, reading the data: Distribb reads your Search Console clicks and rankings for every page. From Claude or ChatGPT you can ask which pages lost clicks this month, and it answers from your own data and names the page.
  • Step 7, acting on a drop: Optimizations fixes pages that slip. You can also ask your assistant to rewrite the worst one, and it rebuilds the page on the same URL with its internal links kept.
  • The work that moves the graph: articles written and published to your site, and a backlink exchange that adds 3 to 5 contextual links a month from real businesses.

A tracker tells you the number. Distribb does the work that changes it, on autopilot, once your site is connected. Pro is $97 a month with 30 articles. Accelerator is $495 a month with 90 articles and done-for-you link outreach campaigns. Both start with a free trial.

Start your Free Trial

Watch the video

The episode is about 12 minutes. The YouTube version shows the dashboard screens with the company's keywords and URLs replaced.

FAQ

Can I build my own rank tracker for free?

Mostly, yes. Google Search Console is free, and its bulk export to BigQuery has a free usage level, with charges only for storage and queries above it. The optional cost is search volume data from an API. Edward Sturm says his team built theirs in a couple of hours with an AI coding assistant.

Why does my rank tracker show a drop when Search Console doesn't?

Third-party trackers collect Google results from the outside, and Sturm says Google is making that harder and returning unreliable results to them. Search Console reports what real searchers saw for your site. When the two disagree for more than a few days, trust Search Console.

How long does Google Search Console keep data?

Search Console keeps 16 months of performance data. To keep more, turn on the bulk data export to BigQuery in Settings. The export runs daily and does not backfill, so your stored history starts on the day you enable it. Older data has to come from the Search Console API or the reports.

What data should a rank tracker show?

As little as possible. Sturm's tracker shows the average position of each targeted keyword over time, its monthly search volume, the top ranking URL, a label for grouping and a clicks column. He leaves out revenue and every keyword the team is not actively targeting.

Is average position in Search Console accurate?

It is accurate for what it measures: the average position across every impression in the period, for all searchers and locations. It will not match what you see when you search from your own browser. Read the movement over weeks, and compare periods, more than the single number.

Start the export today

The dashboard can wait a week. The export can't, because every day without it is a day of history you will not get back. Turn it on, write your target keyword list and start the journal. When you want the pages that slip to be found and rewritten for you, see how Distribb's AI SEO agent works from Claude or ChatGPT.

Your blog, on autopilot. Written and published for you.

Distribb researches keywords, writes SEO articles and publishes them to your site, then builds backlinks from real businesses.

Free for 3 daysCancel anytimeSet up in 5 minutes