How to merge keyword rankings with Search Console data

Two views of the same rankings

A rank tracker checks a fixed list of keywords from one location and device, usually once a day, and records the position and URL it saw. Search Console reports what real searchers saw across locations and devices, averaged over the period, with clicks and impressions. They disagree often, and the disagreements are useful:

• Tracker says position 3, Search Console says 9: the tracker's location or device differs from where most searchers are.
• Tracker URL differs from the page with most Search Console clicks: two pages compete for the term.
• High tracked search volume but few impressions: the keyword's volume estimate is optimistic, or you rank only in some regions.

What to export

The Search Console interface exports queries and pages as separate tabs, so it can't give you query + page per row. The API can. Analistable's Search Console connection fetches query and page dimensions server-side and loads them into the browser, which is one way to get that combined table without writing API code.

Normalise keys

• Keywords: lowercase, trim, collapse double spaces. Search Console queries are lowercase already.
• URLs: trackers sometimes store URLs without protocol or trailing slash. Strip protocol, “www.”, trailing slash, and anything after # or ? on both sides.

CREATE MACRO clean_url(u) AS
  regexp_replace(regexp_replace(lower(split_part(split_part(u, '#', 1), '?', 1)),
    '^https?://(www\.)?', ''), '/$', '');

That macro uses DuckDB syntax; other SQL dialects have equivalent regex functions. In Excel you can do the same with SUBSTITUTE and TEXTBEFORE in Microsoft 365.

Join on keyword, then compare URLs

WITH gsc AS (
  SELECT lower(trim(query)) AS kw, clean_url(page) AS url,
         clicks, impressions, position
  FROM gsc_query_page
), top_page AS (
  SELECT kw, arg_max(url, clicks) AS gsc_top_url,
         SUM(clicks) AS clicks, SUM(impressions) AS impressions
  FROM gsc GROUP BY kw
)
SELECT r.keyword, r.position AS tracked_pos, clean_url(r.url) AS tracked_url,
       t.gsc_top_url, t.clicks, t.impressions, r.search_volume,
       clean_url(r.url) = t.gsc_top_url AS same_url
FROM rankings r
LEFT JOIN top_page t ON t.kw = lower(trim(r.keyword))
ORDER BY r.search_volume DESC;

Joining on keyword first and comparing URLs second keeps keywords where the URLs differ — an inner join on both keyword and URL would silently drop them, and they're often the most interesting rows. For background on the join types involved, see SQL joins across multiple tables.

Worked example: five tracked keywords

“mileage log template” ranks fourth in the tracker, but Search Console's top-clicked page for it is a different URL — a cannibalisation candidate. “timesheet template uk” has no Search Console row at all: either it ranks too low to earn impressions in the period, or the tracker's location sees a result most searchers don't. Clicks for the four matched keywords total 1,220.

Search volume from trackers is a monthly estimate. Search Console impressions count only searches where your page appeared, so don't expect them to match volume even at position 1.

Find what you aren't tracking

Flip the join: start from Search Console and keep queries with no tracked keyword, ordered by impressions. Those are queries you already rank for that nobody is watching. Pair with the click trend from finding pages losing clicks to prioritise. Analistable can take the tracker CSV and the connected Search Console data together and answer “tracked keywords where the ranking URL differs from the page getting clicks” in plain English. The cross-spreadsheet guide has more join recipes.

Weight rankings by what they earn

Rank trackers often report an average position across all tracked keywords, which treats a 50-search keyword and a 6,600-search keyword as equal. After the join you can weight by Search Console impressions instead, so the summary reflects what searchers actually saw:

SELECT SUM(tracked_pos * impressions) / SUM(impressions) AS weighted_tracked_position,
             AVG(tracked_pos) AS simple_average
      FROM joined
      WHERE impressions > 0;

For the four matched keywords above, the simple average tracked position is (2 + 4 + 7 + 3) / 4 = 4.0. Weighted by impressions it is (2 × 21,000 + 4 × 9,000 + 7 × 5,200 + 3 × 3,400) / 38,600 = 124,600 / 38,600 = 3.2, because the best-ranked keyword also has the most impressions.

Report both. When the weighted figure moves and the simple one doesn't, a high-impression keyword changed position — usually the one to look at first.

Refresh on a schedule

Run the join monthly with the same tracker settings (location, device) and the same Search Console filters. Store each month's joined table with a month column so you can see when a ranking URL changed and whether clicks followed. A URL switch that coincides with a drop in clicks is the clearest sign of two pages competing for one keyword.

When the tracker and Search Console disagree on URL

Look at both pages before acting. If the tracked URL is the one you want ranking, strengthen it: internal links from the other page, clearer title and headings for the keyword, and possibly merging the weaker page into it with a redirect. If Search Console's top page is actually the better result, update the tracker's target URL so future reports compare against the right page.

Tag keywords by intent

Add an intent or topic column to the tracker list (pricing, how-to, template, brand). After the join, group by that column to see which kinds of keywords earn clicks relative to their tracked position. Template keywords often earn more clicks per impression than how-to keywords at the same position, because the results page has fewer answer boxes. That tells you where improving a ranking is likely to pay off.

Frequently asked questions

Does the tracker's search volume help after the join?

Yes, for prioritising. Keywords with high volume, a tracked position between 4 and 10, and modest clicks are often the quickest wins, because a small ranking gain moves them into the positions that earn most clicks.

Should I set the tracker to mobile or desktop?

Match where most of your Search Console impressions come from. Check the Devices tab in Search Console; for many sites mobile dominates.

Which position should I trust?

Use Search Console for what searchers actually saw on average, and the tracker for daily movement on a fixed setup. They answer different questions.

Why is a tracked keyword missing from Search Console?

Low impressions, anonymised queries, a different country or device, or a slightly different phrasing in Search Console. Try a contains-match before concluding it has no traffic.

Can I do this in Excel?

Yes, with XLOOKUP from the tracker sheet into a Search Console sheet on a cleaned keyword column. Getting query and page together still needs an API-based export.

Related guides

• How to find pages losing clicks, and which ones matter
• How to merge two tables and summarise them with a pivot
• How to merge UTM campaign data with CRM leads
• Microsoft Copilot in Excel vs Analistable
• How do sales reps rank across all regions?