Getting Past Search Console’s 16-Month Wall.

The symptom showed up as a simple question from a client with a large site. Which pages lost search clicks compared with the same quarter two years earlier, and on which queries. Search Console could not answer it. The Performance report keeps 16 months and nothing more, so the earlier quarter had already been deleted. The export button gives you at most 1,000 rows, which for a site with tens of thousands of indexed pages is a rounding error. The Search Console API goes deeper on rows, up to 25,000 per request with paging [VERIFY: current rowLimit maximum], but it reads from the same 16-month store, so it cannot reach data Google has already discarded. The only route I know of that removes both limits is the bulk data export into BigQuery, and it only works forwards from the day you enable it.

Step 1. Create the Google Cloud project and enable billing

Open console.cloud.google.com with the Google account that will own the data. Create a new project and note the project ID, the lowercase identifier, which can differ from the display name. In the left menu open Billing and attach a billing account, even though the first months will likely cost nothing. A project without billing runs in the BigQuery sandbox, and sandbox tables expire after 60 days by default [VERIFY: sandbox expiry period], which defeats the entire purpose of the exercise.

Then open APIs and Services, click Enable APIs and Services, search for BigQuery API and confirm it is enabled.

Step 2. Give Search Console permission to write into the project

Search Console writes using its own service account. It needs two roles on your project before the export will run.

1. In the Cloud console open IAM and Admin, then IAM.
2. Click Grant Access.
3. In New principals paste search-console-data-export@system.gserviceaccount.com.
4. Add the role BigQuery Job User.
5. Add a second role, BigQuery Data Editor, and save.

If you prefer the command line, this does the same thing.

gcloud projects add-iam-policy-binding YOUR_PROJECT_ID \
  --member="serviceAccount:search-console-data-export@system.gserviceaccount.com" \
  --role="roles/bigquery.jobUser"

gcloud projects add-iam-policy-binding YOUR_PROJECT_ID \
  --member="serviceAccount:search-console-data-export@system.gserviceaccount.com" \
  --role="roles/bigquery.dataEditor"

You should see the service account listed on the IAM page with both roles against it. Skipping this is the most common reason the first export fails, and Search Console will only tell you about the failure after it has tried and given up.

Step 3. Switch on the bulk data export in Search Console

In Search Console pick the property, open Settings from the left menu, then click Bulk data export and fill the three fields.

Click Continue. Search Console runs a permission check against the service account you set up in Step 2. If it passes you will see a confirmation that the export is scheduled. Do not touch the dataset name, the location, or the table schemas afterwards. Google is explicit that editing the schema breaks the export.

Two things about timing matter more than anything else in this guide. The first export arrives up to 48 hours after you save, and it contains data for the day of the export, not for the days before, because nothing is backfilled. A property I enabled on a Tuesday had its first rows for that Tuesday, landing on the Thursday, and every day since. If you think you might want two years of history in 2028, the export has to be running by September 2026. Set it up for every property you manage before you need it, since the storage for a small site costs a few rupees.

What lands in the dataset

After the first run the dataset contains three tables.

Both data tables are partitioned on data_date, which is the Pacific Time day the data was generated. Always filter on data_date so BigQuery reads only the partitions you need. Positions are stored as sums and are zero-based, so average position in the familiar 1-based form is SUM(sum_position) / SUM(impressions) + 1 for the URL table, and the same with sum_top_position for the site table.

The anonymised query rows are the part people miss. Google withholds rare queries for privacy. In the interface those impressions silently disappear from the query list, which is why the query totals never add up to the chart totals. In the export they are kept as rows where is_anonymized_query is true and query is a zero-length string. That means the export can tell you what share of your impressions you cannot see, which is one of the queries below.

Step 4. Link GA4 to the same BigQuery project

Search data on its own tells you which pages Google sent people to. Linking GA4 into the same project lets you join that against what those people did afterwards, and it also lifts GA4 out of its own retention limits, which are 2 or 14 months for event-level data in the interface.

1. In GA4 open Admin, then under Product links click BigQuery links.
2. Click Link, then Choose a BigQuery project, and pick the project from Step 1.
3. Choose the same data location you chose for Search Console.
4. Pick the data streams to include, and do not exclude any events unless the property is near the daily export ceiling.
5. Under Frequency tick Daily, since Streaming is an extra export with its own costs and is not needed for this job.
6. Review the settings and submit.

GA4 creates a dataset named analytics_ followed by the property ID, with one table per day named events_YYYYMMDD. Data starts flowing within 24 hours of linking, and like Search Console it does not backfill. Standard (free) GA4 properties have a ceiling of 1 million events a day on the daily export; if the property goes over it consistently, Google pauses the export rather than trimming it, so a very busy site needs a filter on which events are sent.

Step 5. Run the queries the interface cannot

Replace YOUR_PROJECT_ID in each query. All three run in the BigQuery console under SQL workspace. Before running anything, hover over the green tick in the editor, which shows how many bytes the query will process, and that number is what you pay for.

Clicks by query, page and country across two years

This is the report the client asked for and the interface flatly cannot produce. It is one query over 24 months with no row cap other than the one you choose.

SELECT
  query,
  url,
  country,
  SUM(clicks) AS clicks,
  SUM(impressions) AS impressions,
  ROUND(SAFE_DIVIDE(SUM(sum_position), SUM(impressions)) + 1, 1) AS avg_position
FROM `YOUR_PROJECT_ID.searchconsole.searchdata_url_impression`
WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 24 MONTH)
                    AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
  AND search_type = 'web'
  AND is_anonymized_query = FALSE
GROUP BY query, url, country
HAVING clicks > 0
ORDER BY clicks DESC
LIMIT 50000;

Country codes are three-letter ISO 3166-1 alpha-3 values, so India is ind in lower case in the export [VERIFY: case of country values in your table]. Add AND country = 'ind' to narrow it. The 50,000 limit is arbitrary; remove it and click Save results to export the full set to Google Sheets or CSV in Drive.

Share of anonymised query impressions by month

If the anonymised share is climbing, your query-level reports are getting less representative over time, and this is the only way to measure it.

SELECT
  FORMAT_DATE('%Y-%m', data_date) AS month,
  SUM(IF(is_anonymized_query, impressions, 0)) AS anonymised_impressions,
  SUM(impressions) AS all_impressions,
  ROUND(100 * SAFE_DIVIDE(SUM(IF(is_anonymized_query, impressions, 0)),
                          SUM(impressions)), 1) AS anonymised_share_pct
FROM `YOUR_PROJECT_ID.searchconsole.searchdata_site_impression`
WHERE data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 24 MONTH)
  AND search_type = 'web'
GROUP BY month
ORDER BY month;

The site table is the right one here because the URL table double counts impressions when a query shows two of your URLs.

Pages whose clicks fell more than 30 percent year on year

The interface can do a year-on-year compare, but only on the top 1,000 rows, sorted by the current period. Pages that fell off entirely never appear. This version compares the last 90 days against the same 90 days a year earlier and lists every page that lost 30 percent or more, including pages that went to zero.

WITH this_year AS (
  SELECT url, SUM(clicks) AS clicks
  FROM `YOUR_PROJECT_ID.searchconsole.searchdata_url_impression`
  WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
                      AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
    AND search_type = 'web'
  GROUP BY url
),
last_year AS (
  SELECT url, SUM(clicks) AS clicks
  FROM `YOUR_PROJECT_ID.searchconsole.searchdata_url_impression`
  WHERE data_date BETWEEN DATE_SUB(DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR), INTERVAL 90 DAY)
                      AND DATE_SUB(DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR), INTERVAL 1 DAY)
    AND search_type = 'web'
  GROUP BY url
)
SELECT
  l.url,
  l.clicks AS clicks_last_year,
  IFNULL(t.clicks, 0) AS clicks_this_year,
  ROUND(100 * (IFNULL(t.clicks, 0) - l.clicks) / l.clicks, 1) AS change_pct
FROM last_year AS l
LEFT JOIN this_year AS t USING (url)
WHERE l.clicks >= 100
  AND IFNULL(t.clicks, 0) <= l.clicks * 0.7
ORDER BY clicks_last_year DESC;

The l.clicks >= 100 floor keeps out pages where a drop from 3 clicks to 2 would otherwise be reported as a 33 percent collapse. Raise it for a site with more traffic. Once the export has been running for more than 16 months this query keeps working on windows the interface has already forgotten.

Step 6. Work out what it costs

BigQuery bills two things, storage and query processing. Both have free allowances that cover a lot of SEO work.

Because both Search Console tables are partitioned by day, each day’s partition drops to the long-term rate 90 days after it was written, and reading a partition does not reset that clock. The query in Step 5 that scans 24 months of the URL table reads the whole table, so the bytes scanned per run equals the table size.

I will not guess your row volume, since it depends on how many URLs and queries the site earns impressions for. Measure it after a fortnight with this query, then multiply out.

SELECT
  table_name,
  ROUND(active_logical_bytes / POW(1024, 3), 2) AS active_gib,
  ROUND(long_term_logical_bytes / POW(1024, 3), 2) AS long_term_gib
FROM `YOUR_PROJECT_ID.searchconsole.INFORMATION_SCHEMA.TABLE_STORAGE`
WHERE table_schema = 'searchconsole';

Worked example with an assumed size, so you can see the arithmetic. Say the two Search Console tables together grow by 1 GiB a month [VERIFY: replace with your measured figure]. After two years you hold 24 GiB, of which roughly 21 GiB is long-term. Storage is (24 minus 10 free) times about $0.02, which is under $0.30 a month, or about 25 rupees at 85 rupees to the dollar [VERIFY: exchange rate]. If an analyst runs 40 full-table queries a month at 24 GiB each, that is 960 GiB scanned, which stays inside the 1 TiB free allowance and costs nothing. The GA4 export is the bigger line. A property near the 1 million events a day ceiling can write tens of GiB a month [VERIFY: measure with the same INFORMATION_SCHEMA query on the analytics dataset], and full scans of a year of event tables will cross the free query tier, so on GA4 queries always restrict _TABLE_SUFFIX to the dates you need.

Set a budget alert in Billing, then Budgets and alerts, at something like 2,000 rupees a month so a runaway query gets flagged rather than discovered on the invoice.

Check it worked

Give it 48 hours after saving the export settings, then run this in BigQuery.

SELECT agenda, namespace, data_date, epoch_version, publish_time
FROM `YOUR_PROJECT_ID.searchconsole.ExportLog`
ORDER BY publish_time DESC;

You should see rows with agenda equal to SEARCHDATA, two rows per data_date (one with namespace set to searchdata_site_impression, one to searchdata_url_impression), and publish_time values roughly 24 hours apart. The earliest data_date should be the day you saved the settings or the day after. If epoch_version later rises above 1 for a date, Google revised that day’s data and re-exported it, which is normal.

Cross-check one day against the interface. Pick a data_date at least three days old, sum clicks from the site table for search_type = 'web', and compare with the Performance report for that single date with Search type set to Web. The two should be within a few percent. A large gap usually means you are comparing the URL table (which double counts) with the interface, or the wrong search type.

Back in Search Console, Settings then Bulk data export now shows the last export status. Search Console only reports the most recent attempt, so a run that failed three days ago will not be visible there, and only ExportLog gives you the history.

For GA4, open the BigQuery console and confirm the analytics_PROPERTYID dataset exists with at least one events_YYYYMMDD table within 24 hours of linking.

Where I could be wrong

The pricing figures are the published US dollar rates at the time of writing, and the storage rate is derived from an hourly figure, so check the BigQuery pricing page before quoting a client. Prices in rupees on Google Cloud SKUs may not track the spot exchange rate.

I have assumed the export runs on a standard Google account with owner access to the Search Console property. For properties owned by an agency account that later leaves, the Cloud project ownership and the Search Console ownership must both be transferred, or the export will keep writing into a project nobody can bill or read.

The anonymised query behaviour described here is for the site table. Discover rows in the URL table are treated differently, with URL and country withheld under a threshold, so a Discover-heavy publisher should read Google’s table reference before trusting the shares.

The claim that the API stops at 16 months is true for the Search Analytics endpoint I use. If Google extends retention in future, the storage argument weakens but the row-cap and join arguments still hold.

Sources

• Google Search Console Help, Bulk data export, https://support.google.com/webmasters/answer/12917675
• Google Search Console Help, Table guidelines and reference, https://support.google.com/webmasters/answer/12917991
• Google Search Console Help, Manage bulk data exports, https://support.google.com/webmasters/answer/12919198
• Google Analytics Help, Set up BigQuery Export, https://support.google.com/analytics/answer/9823238
• Google Analytics Help, Data retention, https://support.google.com/analytics/answer/7667196
• Google Cloud, BigQuery pricing, https://cloud.google.com/bigquery/pricing
• Google Cloud, BigQuery INFORMATION_SCHEMA.TABLE_STORAGE view, https://cloud.google.com/bigquery/docs/information-schema-table-storage
• Google Search Console API, Search Analytics query reference, https://developers.google.com/webmaster-tools/v1/searchanalytics/query

How to cite this guide