Analistable

How to combine Search Console exports from multiple date ranges

By the Analistable team · Updated · 6 min read

Export the same Search Console report (queries or pages) once per period — a month, say — add a period column to each file, and stack them into one long table. Because each export holds its own top 1,000 rows, the stack covers more queries than one long-range export. Save exports regularly to keep history beyond Search Console's 16 months.

Part of our guide: How to answer questions across multiple spreadsheets

The two limits you're working around

  • 1,000 rows per interface export: the Performance report exports at most 1,000 rows per table. Over a year, the long tail falls off the bottom.
  • 16 months of history: Search Console keeps about 16 months of performance data. Anything older is gone unless you saved it.

Splitting a year into twelve monthly exports means each month contributes its own top 1,000 queries. Queries that rank briefly in one month — seasonal or newly ranking terms — appear in that month's file even if they'd never reach the top 1,000 across the full year. The stack is still not complete data; for that you need the Search Console API, which returns far more rows per request.

Export each period the same way

  1. Open Performance → Search results and set the date range to the first period (for example 1–31 July).
  2. Keep the same search type (web), filters and tab (Queries or Pages) for every period.
  3. Export to Google Sheets, Excel or CSV and name the file with the period: gsc_queries_2026-07.csv.
  4. Repeat for each period.

Don't export the comparison view (two date ranges side by side) and then try to stack it; its columns differ from a single-period export.

Add a period column and stack

Each file needs a column saying which period it covers, because the export itself doesn't include dates. In Power Query, combine from a folder and derive the period from Source.Name (combining files from a folder). In SQL:

SELECT DATE '2026-07-01' AS period, * FROM gsc_2026_07
UNION ALL
SELECT DATE '2026-08-01', * FROM gsc_2026_08
UNION ALL
SELECT DATE '2026-09-01', * FROM gsc_2026_09;

If your SQL engine can read many CSVs at once with the file name as a column (DuckDB's read_csv with filename=true does this), you can parse the period from the file name instead of listing every file.

Aggregate correctly

Clicks and impressions add up across periods. Click-through rate and position do not — you must recompute them:

  • CTR = total clicks ÷ total impressions, not the average of monthly CTRs.
  • Position = impression-weighted average: SUM(position × impressions) ÷ SUM(impressions).
SELECT "Top queries" AS query,
       SUM("Clicks") AS clicks,
       SUM("Impressions") AS impressions,
       SUM("Clicks") * 1.0 / SUM("Impressions") AS ctr,
       SUM("Position" * "Impressions") / SUM("Impressions") AS position,
       COUNT(*) AS months_present
FROM gsc_stacked
GROUP BY 1
ORDER BY clicks DESC;

Worked example: one query, three months

Query “invoice template uk” across stacked exports
periodclicksimpressionsCTRposition
2026-0712040003.0%8
2026-0830060005.0%5
2026-0948080006.0%3.5
Combined900180005.0%5

Combined CTR is 900 / 18,000 = 5.0%. The simple average of the three CTRs would be 4.7%. Weighted position is (8.0 × 4,000 + 5.0 × 6,000 + 3.5 × 8,000) / 18,000 = 90,000 / 18,000 = 5.0. The simple average of positions would be 5.5, overstating how low it ranked.

A query missing from a month's file doesn't mean it had zero clicks — it may simply have fallen below that month's top 1,000. Treat gaps as “unknown”, not zero, when charting trends.

Questions the stack answers

  • Which queries appeared for the first time this quarter?
  • Which pages lost the most clicks month on month? (Pair with GA4 as in finding pages losing clicks.)
  • Which queries are seasonal — present in only two or three months?

Analistable's Search Console connection fetches query data through the API on the server and loads it into the browser, so you can skip the 1,000-row interface exports and ask these questions directly; your existing CSV history can sit alongside it. See the cross-spreadsheet guide for related question types.

Build a long-term archive

The stack is also how you keep history beyond 16 months. A light routine is enough:

  1. On the first working day of each month, export the previous month's Queries and Pages tabs.
  2. Save them to one folder per table with the month in the file name.
  3. Never edit the raw files; do the stacking and cleaning in a query that reads the folder.
  4. Every January, check that the previous year's twelve files are all present.

After a year you can compare October with last October even when Search Console itself only reaches back 16 months. After two years you have history Search Console no longer holds at all.

Store the export settings in a short readme in the folder: property, search type, country filter and whether the export was from the interface or the API. When someone changes one of those, the trend breaks, and the readme tells you why.

Compare two periods side by side

With a period column, a period-over-period comparison is a pivot. Put query in rows, period in columns and clicks as values, then add a difference column. In SQL:

SELECT query,
             SUM(clicks) FILTER (WHERE period = DATE '2026-08-01') AS aug,
             SUM(clicks) FILTER (WHERE period = DATE '2026-09-01') AS sep,
             sep - aug AS change
      FROM gsc_stacked
      GROUP BY query
      ORDER BY change;

Sorting by change ascending puts the biggest losers first. Remember the caveat about gaps: a query present in August and missing in September shows a NULL for September, which you should read as “below the export cut-off”, not as zero.

Watch for country and device filters

If any export in the stack had a country or device filter applied, its numbers won't be comparable with the rest. The export file doesn't record which filters were active, so name files with the filter (gsc_queries_2026-07_uk.csv) and add a filter column when stacking. Mixing filtered and unfiltered months is the most common reason a stacked trend shows a sudden step that never happened.

Pages tab: watch for URL changes

When stacking Pages exports, a URL that changed between periods — after a redesign or a slug edit — appears as two different pages, one ending and one starting. Keep a small redirect map (old URL → new URL) and apply it to the stacked table before grouping, so the page's history stays on one row. Without it, every migration looks like a page that lost all its clicks and another that appeared from nowhere.

Frequently asked questions

Should I store exports as CSV or Google Sheets?
CSV is simplest to stack with Power Query, SQL or Analistable, and it doesn't depend on a Google account. Sheets is fine if your team works there; just keep one tab per period.
Why don't monthly clicks add up to the yearly export's total?
The yearly export only lists its top 1,000 queries, and anonymised queries are excluded from query tables. Totals in the chart above the table include all queries, so compare stacked totals with the chart, not with the table.
Does exporting by week get even more rows?
Yes — more periods means more top-1,000 slices. Weekly exports are tedious by hand, though, and at that point the API is usually easier.
Can I recover data older than 16 months?
Not from Search Console. Start saving monthly exports now, or connect it to a tool that stores history, so next year you have it.
Should I stack query and page exports together?
No. They're different tables with different keys. Stack queries with queries and pages with pages; a query-plus-page view needs the API, which can return both dimensions per row.

Related guides