How to blend Search Console data across multiple sites
By the Analistable team · Updated · 6 min read
Export (or fetch) the same Search Console report from each property for the same dates, add a site column to each, and stack them into one table. Sum clicks and impressions by site or query, recompute CTR and impression-weighted position, and pivot by site to see which sites compete for the same queries.
Part of our guide: How to answer questions across multiple spreadsheets
When you need a multi-site view
- A business runs country sites (example.co.uk, example.de) or brand sites that share topics.
- An agency or in-house team wants one SEO dashboard across a portfolio.
- A migration is under way and traffic is moving from an old domain to a new one.
Search Console reports each property separately, and the interface has no portfolio view. Blending means stacking the properties yourself with a column that says which site each row came from.
Choose properties carefully
Search Console has domain properties (all subdomains and protocols) and URL-prefix properties (one prefix). If you add both a domain property and a URL-prefix property for the same site, their data overlaps and stacking them double-counts. Pick one property per site and write the choice down.
| site | property | type |
|---|---|---|
| UK | example.co.uk | Domain |
| DE | example.de | Domain |
| Blog | https://blog.example.com/ | URL prefix |
If blog.example.com is a subdomain of a domain property you already include, drop it — it's already in there.
Stack with a site column
Export the same tab (Queries or Pages) for the same date range from each property, add the site label, and UNION ALL them:
SELECT 'UK' AS site, "Top queries" AS query, "Clicks" AS clicks,
"Impressions" AS impressions, "Position" AS position FROM gsc_uk
UNION ALL
SELECT 'DE', "Top queries", "Clicks", "Impressions", "Position" FROM gsc_de
UNION ALL
SELECT 'Blog', "Top queries", "Clicks", "Impressions", "Position" FROM gsc_blog;Each interface export is capped at 1,000 rows, so a large site's file is a sample of its top queries while a small site's is complete. Say so in any portfolio comparison, or fetch via the API to get more rows per property. Analistable's Search Console connection can pull each property you have access to and loads the rows into your browser for this kind of blend.
Worked example: three sites, September
| site | clicks | impressions | CTR | weighted position |
|---|---|---|---|---|
| UK | 18000 | 600000 | 3.0% | 14.2 |
| DE | 6000 | 300000 | 2.0% | 18.9 |
| Blog | 9000 | 450000 | 2.0% | 21.5 |
| Total | 33000 | 1350000 | 2.4% |
Portfolio CTR is 33,000 / 1,350,000 = 2.4%. Don't average the three CTRs (2.3%) or the positions — recompute from the summed clicks, impressions and position × impressions. A blended position across sites in different languages isn't meaningful anyway, which is why the total row leaves it blank.
Find queries where sites compete
Pivot by query with one column per site to find terms where two of your sites both appear — sometimes intended (country targeting), sometimes cannibalisation:
SELECT query,
SUM(clicks) FILTER (WHERE site = 'UK') AS uk_clicks,
SUM(clicks) FILTER (WHERE site = 'Blog') AS blog_clicks,
COUNT(DISTINCT site) AS sites
FROM gsc_all
GROUP BY query
HAVING COUNT(DISTINCT site) > 1
ORDER BY sites DESC, uk_clicks DESC;| query | UK clicks | Blog clicks | sites |
|---|---|---|---|
| invoice template | 1200 | 300 | 2 |
| how to write an invoice | 150 | 900 | 2 |
| vat invoice requirements | 400 | 380 | 2 |
“vat invoice requirements” is split almost evenly between the shop and the blog, which can mean neither page ranks as well as one consolidated page would. The two-site version of this question is covered in finding keywords ranking on both sites.
Keep the blend honest
- Same date range, search type and country filter for every property.
- One property per site, no overlapping domain and URL-prefix pairs.
- Sum clicks and impressions; recompute CTR and position.
- Note which sites are sampled by the 1,000-row cap.
For more ways to combine Search Console with other data, see combining Google Analytics and Search Console and the cross-spreadsheet guide.
Track a migration with the blend
During a domain migration, the old and new properties are two sites in the blend. Stack them by week with a site column and you can watch clicks move across:
| week | old domain | new domain | combined |
|---|---|---|---|
| Week 1 | 10000 | 0 | 10000 |
| Week 2 | 6000 | 3500 | 9500 |
| Week 3 | 2000 | 7600 | 9600 |
| Week 4 | 500 | 9700 | 10200 |
The combined column is the one to watch. A dip of a few per cent in the first weeks is common; a combined total that stays well below the pre-migration level after several weeks points to missing redirects or pages that didn't move. Join the old domain's page list to the new domain's to find URLs that had clicks before and have no counterpart now.
Use a page-level blend for that check: stack the Pages exports from both properties, strip the domain from each URL, and look for paths present on the old site with clicks and absent on the new one. Those are usually redirect mistakes.
Pages, not just queries
The same stacking works for the Pages tab. A page-level blend shows which site's content earns the most clicks per page, and whether a topic performs better on the shop domain or the blog. Keep page URLs complete, including the domain, so identical paths on two sites (/pricing on both) stay distinct rows.
Label sites consistently
Use short, stable site labels (UK, DE, Blog) rather than full property names, and keep them in a lookup table with the property, its type and the date you started including it. When a property is added or retired, the lookup table records it, and charts can show a gap rather than an unexplained drop in the portfolio total.
If you blend for several clients or business units, add a second label column for the group, so you can roll up by group as well as by site without editing the stacked data.
Compare sites fairly
Sites of different sizes need ratios, not raw totals. Clicks per indexed page, CTR and clicks share of the portfolio are fairer comparisons than clicks alone. A small blog earning 9,000 clicks from 200 pages (45 per page) is doing better per page than a shop earning 18,000 from 1,200 pages (15 per page), even though its total is half. Pull page counts from each property's Pages indexing report and keep them in the site lookup table.
Frequently asked questions
- Can Analistable blend properties from different Google accounts?
- It reads the properties the connected Google account can access. If sites sit in different accounts, give one account access to all of them in Search Console first.
- Do I need the same date range for every site?
- Yes. Different ranges make totals and shares meaningless. If a site launched mid-period, keep the range and note the launch date rather than shortening the period for that site.
- How many sites can I blend?
- There's no fixed limit — the stack just gets longer. Beyond a handful of properties, fetching through the API or a connected tool is more practical than manual exports.
- Can I combine sites I don't own in Search Console?
- No. You need at least restricted access to each property. For competitor sites, use a third-party rank tracker instead.
- Why do country sites show the same query?
- Searchers in one country can see another country's site, especially for languages shared across countries. Check hreflang and the country filter in each property.
- Should I filter by country before blending?
- If the sites target different countries, compare each site in its own target country. Blending unfiltered data mixes intended and unintended traffic.