How to join Google Ads search terms with Search Console queries
By the Analistable team · Updated · 6 min read
Export the Google Ads search terms report and a Search Console performance export by query for the same dates. Lowercase and trim both query columns, then do a full outer join on the query text. Rows with paid cost and a strong organic position are candidates for testing lower bids; rows with paid conversions and no organic presence are content gaps.
Part of our guide: How to answer questions across multiple spreadsheets
What the overlap tells you
Search Console shows queries where your pages appeared in organic results. The Google Ads search terms report shows the searches that triggered your ads. Joined on the query text, they split into four groups:
| Group | Paid? | Organic? | What to consider |
|---|---|---|---|
| Overlap, strong organic | Yes | Average position ≤ 3 | Test lower bids or pausing; measure total clicks |
| Overlap, weak organic | Yes | Position > 10 | Paid is carrying it; organic content opportunity |
| Paid only | Yes | No | Content gap if the term converts |
| Organic only | No | Yes | Possible new keywords for ads |
Pausing ads on a term you rank first for doesn't always keep the clicks — brand terms with competitor ads above you are the usual exception. Treat overlaps as test candidates, not automatic cuts.
Exports and their limits
- Google Ads: Insights and reports → Search terms, with columns Search term, Match type, Clicks, Impr., Cost, Conversions. Search terms with low volume may be hidden for privacy, so totals won't equal campaign totals.
- Search Console: Performance → Search results → Queries tab → Export. The interface export is capped at 1,000 rows, and Google omits some rare (anonymised) queries. For more rows, use the API or Analistable's Search Console connection, which fetches query data server-side and loads it into your browser.
Use the same date range in both, and set Search Console to web search type. Long date ranges catch more queries; see combining Search Console exports from multiple date ranges if you're working around the row limit.
Normalise and join
Search Console queries are already lowercase; Google Ads search terms usually are too, but trim both anyway and collapse double spaces. Aggregate Ads terms first — the same term appears once per campaign and ad group.
WITH paid AS (
SELECT LOWER(TRIM("Search term")) AS query,
SUM("Clicks") AS paid_clicks, SUM("Cost") AS cost, SUM("Conversions") AS paid_conv
FROM ads_search_terms
GROUP BY 1
), organic AS (
SELECT LOWER(TRIM("Top queries")) AS query,
"Clicks" AS organic_clicks, "Impressions" AS impressions, "Position" AS position
FROM gsc_queries
)
SELECT COALESCE(p.query, o.query) AS query,
p.paid_clicks, p.cost, p.paid_conv,
o.organic_clicks, o.position
FROM paid p
FULL OUTER JOIN organic o ON o.query = p.query
ORDER BY p.cost DESC NULLS LAST;A full outer join keeps all four groups. An inner join would show only the overlap; a general guide to join types explains the difference.
Worked example: five queries, September
| query | paid clicks | cost | paid conv. | organic clicks | avg position |
|---|---|---|---|---|---|
| acme crm | 420 | 210 | 38 | 3100 | 1.1 |
| crm for small business | 260 | 1040 | 13 | 90 | 8.4 |
| crm pricing uk | 150 | 600 | 9 | ||
| free crm template | 540 | 2.3 | |||
| acme crm login | 80 | 24 | 0 | 2200 | 1 |
“acme crm login” cost £24 for 80 clicks and no conversions while ranking first organically — a clear test candidate. The brand term “acme crm” converts well on paid (38 conversions for £210) and may be defending against competitor ads, so test carefully. “crm pricing uk” is paid-only with 9 conversions: a pricing page aimed at UK searchers is worth considering. “free crm template” is organic-only. Total paid cost across the five rows is £1,874.
Search Console's position is an average across all impressions in the period and can mask large daily swings. Check the query's daily trend before acting on a single average.
Going further
Join on landing page as well as query to see whether the ad and the organic result point to the same URL. To add on-site behaviour, combine with GA4 — see combining Google Analytics and Search Console data and merging GA4 with Google Ads cost. In Analistable you can load the search terms CSV, connect Search Console, and ask “which queries do we pay for and rank top 3 organically?” without writing the join yourself. More examples: the cross-spreadsheet guide.
Run a pause test properly
The join tells you where to test, not what the result will be. A simple, fair test for an overlap query:
- Pick one term (or a small group) with a strong organic position and no strong competitor ads.
- Record two to four weeks of baseline: paid clicks, organic clicks, total clicks and conversions for that term.
- Pause or lower the bid, and leave everything else unchanged.
- Compare the same number of weeks after the change. Look at total clicks and conversions, not organic alone.
- Check seasonality by comparing with the same weeks last year, if you have them.
| period | paid clicks | organic clicks | total clicks | cost |
|---|---|---|---|---|
| Before | 80 | 2200 | 2280 | 24 |
| After | 0 | 2265 | 2265 | 0 |
Total clicks fell by 15 (0.7%) while cost fell by £24 — organic picked up 65 of the 80 paid clicks. For a login term with no conversions, that's a reasonable trade. Repeat the join monthly so new overlaps surface as rankings change.
Brand versus non-brand
Split the joined table into brand and non-brand queries with a simple contains-match on your brand name before drawing conclusions. Brand terms nearly always overlap, because you rank first for your own name and also bid on it. Non-brand overlaps are rarer and usually more interesting: they show generic terms where your content already competes and paid budget might be better spent elsewhere.
Keep a separate list of competitor brand terms too. Those usually appear as paid-only rows, and pausing them is a strategy decision rather than a reporting one.
Keep the cost of the overlap in view
Sum cost for overlap rows with an average organic position of 3 or better, and report it as a single figure each month. It's the budget at stake if every test went well, and it gives the rest of the analysis a sense of scale. In the five-row example, the two overlap rows ranking first organically account for £234 of the £1,874 spent — about 12% — and most of that is the brand term you may decide to keep.
Frequently asked questions
- How long a date range should I use?
- At least 28 days, and ideally 90, so low-volume queries appear in both exports often enough to match. Shorter ranges miss most of the long tail.
- Can I join on landing page as well?
- Yes, if you add Final URL from Google Ads and Page from Search Console's API data. Clean both URLs first. It tells you whether the paid and organic results send people to the same page.
- Does pausing ads hurt organic rankings?
- There's no evidence that Google Ads spend affects organic rankings. What changes is the results page: with your ad gone, a competitor's ad may take the top slot above your organic listing.
- Should I match on keywords or search terms?
- Search terms. Keywords are what you bid on and can be broad; search terms are what people typed, which is what Search Console reports.
- Why don't paid and organic queries match exactly?
- Close variants and plurals are counted as separate queries in Search Console. Consider a second pass that strips plurals or uses fuzzy matching for near-misses.
- Is there a built-in Google report for this?
- Google Ads has had a paid and organic report for linked Search Console accounts. Availability and layout have changed over time, so check your account; the manual join works regardless.