Getting Search Console data out: API, BigQuery and Looker Studio

Four ways to get the data out

The Performance report is a view on top of one dataset. When you need more rows than the table shows, a history you control, a scheduled job or a dashboard for people who never open Search Console, you need to move that data somewhere else. Google offers four routes, and they are not interchangeable.

• UI export. The export button in the Performance report. Manual, quick, capped at 1,000 rows.
• Search Console API (the Search Analytics method, searchAnalytics.query). Programmatic requests with your own dimensions and filters. Free, but subject to row limits and quotas.
• Looker Studio connector. A native data source for dashboards. No code, the same ceilings as the API.
• Bulk data export. A daily dump into a BigQuery dataset in your Google Cloud project. No daily row limit, raw rows, SQL access, real costs.

This lesson builds on the Performance report lesson: the metrics, filters and the difference between property and page aggregation are explained there. Here the focus is on getting the data out correctly.

The table below compares the four routes side by side. Figures come from Google's documentation; the history window is the standard Performance report window and is approximate.

What Google officially says

Google also states who the bulk export is for: “Small and medium sites already have access to all their data through the user interface, the Looker Studio connector (formerly known as Data Studio) or the Search Analytics API.” If your site is not large, the free routes are usually enough.

UI exports and their limits

Most Search Console reports have an export button that downloads the chart and table data as Google Sheets, Excel or CSV. For the Performance report this is the fastest way to get numbers into a spreadsheet, and it has three properties you must know before you trust the file.

• The table is capped. “The table can display a maximum of 1,000 rows, so some rare or long-tail rows might be omitted from the table, but included in the chart total.” The download contains the same 1,000 rows, not more.
• Missing values become zeros. “Values shown as either ~ or - in the report (not available/not a number) will be zeros in the downloaded data.” A zero position in the file means no value, not first place.
• Rows do not add up to the totals. Anonymized queries are in the chart totals but have no row in the table. Summing the exported rows always gives less than the chart.

Two details make the cap less painful. First, it applies per export, so a filter (one folder, one country, one device) gives you a different set of 1,000 rows. Second, Google notes that “For requests that don't involve query or URL dimensions, such as countries, devices, and Search Appearances, Search Console will display and export all the data.” The cap only bites on queries and pages.

The UI export is enough for a one-off look at the top queries or pages of a small site. It is the wrong tool for tracking change over time, for long-tail analysis or for anything you need to repeat every week.

The Search Console API: what you can query

The Search Console API has several resources (sites, sitemaps, URL Inspection), but for performance data you use one method. In Google's words, it lets you “Query your search traffic data with filters and parameters that you define.” It returns rows grouped by the dimensions you choose, with clicks, impressions, CTR and position for each row.

Endpoint, property and scope

POST https://www.googleapis.com/webmasters/v3/sites/{siteUrl}/searchAnalytics/query

The siteUrl must match the property exactly as it exists in Search Console, URL-encoded: Google's examples are “http://www.example.com/ (for a URL-prefix property) or sc-domain:example.com (for a Domain property)”. A Domain property therefore becomes sc-domain%3Aexample.com in the path. Requests need OAuth with one of two scopes: https://www.googleapis.com/auth/webmasters.readonly or https://www.googleapis.com/auth/webmasters. For reporting, always use the read-only one.

Request body fields

An example request

Top queries and pages for the blog folder on mobile, for one day, from a Domain property:

POST https://www.googleapis.com/webmasters/v3/sites/sc-domain%3Aexample.com/searchAnalytics/query
Authorization: Bearer ACCESS_TOKEN

{
  "startDate": "2026-10-26",
  "endDate": "2026-10-26",
  "type": "web",
  "dimensions": ["query", "page"],
  "dimensionFilterGroups": [{
    "groupType": "and",
    "filters": [
      { "dimension": "page", "operator": "includingRegex", "expression": "^https://example\\.com/blog/" },
      { "dimension": "device", "operator": "equals", "expression": "MOBILE" }
    ]
  }],
  "rowLimit": 25000,
  "startRow": 0
}

The response is a list of rows. Each row has keys in the order of your dimensions, then clicks, impressions, ctr (from 0 to 1, not a percentage) and position, plus responseAggregationType for the whole response. The numbers below are illustrative:

{
  "rows": [
    { "keys": ["how to clean suede shoes", "https://example.com/blog/suede-care/"],
      "clicks": 41, "impressions": 980, "ctr": 0.0418, "position": 3.2 }
  ],
  "responseAggregationType": "byPage"
}

Three behaviours to plan for. “Results are sorted by click count descending”, except when you group by date, which sorts oldest first. “When date is one of the dimensions, any days without data are omitted from the result list”, so a missing day is a gap, not a zero row. And search appearance works in two steps: “Search appearance is not available as a column along with any other dimensions.” First query with searchAppearance as the only dimension to list the types, then filter by one type and add other dimensions.

API limits: rows, pagination, quotas and data delay

Rows and pagination

One request returns at most 25,000 rows. To get more, repeat the same request and move the offset: “Page through results by re-running the same query, increasing the startRow value by 25,000 in the request until you reach the last page (a response with 0 rows).” An empty page is the normal end of the loop, not an error: “If startRow exceeds the number of results for the query, the response will be a successful response with zero rows.”

Pagination does not remove the ceiling. “In addition to API usage quota, the Search Analytics method exposes a maximum of 50K rows of data per day per search type (web, image, and so on--sorted by clicks).” Detail also costs completeness: “When you group by page and/or query, our system may drop some data in order to be able to calculate results in a reasonable time using a reasonable amount of computing resources.” For exact totals, run a second query without page and query, grouped only by date, country or device.

Quotas

Search Analytics has two kinds of quota. Load quota measures how expensive your queries are, in 10-minute and 1-day windows. “Most users will not exceed load limits”; if you do, the API returns a quota exceeded error, the same error for every kind of quota. Google's fixes:

• For the short-term quota: “If you exceed your quota, wait 15 minutes and try again.” Then spread queries through the day.
• Cut the expensive parts: “Queries grouped/filtered by page AND query string are the most expensive.”
• Shorten the range: “Query load increases with the date range queried.”
• Store what you pulled: “Avoid requerying the same data (for example, querying all data for last month over and over).”

Request quota limits how many calls you make. The numbers are in the cheat sheet below, together with every other documented limit.

Data delay and the daily pull

“Data is typically available after 2-3 days”, and the help center says the same: “Collected data is usually available in 2–3 days.” If you need fresher numbers, set dataState to all; the response then includes recent, still-changing rows, and the metadata object tells you where incomplete data starts (first_incomplete_date, or first_incomplete_hour with hourly_all).

Google's recommended pattern for complete data is simple: run a query each day for a single day, about three days back, for each search type you care about, and page through until you get zero rows. One day per request keeps load low and stays inside the 50,000-row ceiling per day rather than per range.

Bulk data export to BigQuery

The bulk export removes the row ceiling. “You can configure an export in Search Console to get a daily data dump into your BigQuery project.” The data then lives in your Google Cloud project, where you can query it with SQL, join it with other data and keep it as long as you like.

Setup

1. Prepare a Google Cloud project with billing. Enable the BigQuery API and the BigQuery Storage API.
2. Grant access to Google's export account. In IAM, add the principal [email protected] with two roles: BigQuery Job User and BigQuery Data Editor. You do not create this account; it belongs to Search Console.
3. Configure the export in Search Console. Settings > Bulk data export, paste the project ID (not the project number), choose a dataset name (it always starts with searchconsole; use a different one per property if several properties share a project) and a dataset location, which you cannot easily change later.
4. Wait. “The first export will happen up to 48 hours after your successful configuration in Search Console.”

Only owners can do step 3: “Note that only property owners can set up a bulk data export.” And there is no backfill: “If you want to see historical data that precedes your initial setup, use the Search Console API or the reports.” Set up the export early, and pull the older months through the API once if you need them.

The three tables

All dates are Pacific Time and the data tables are partitioned by data_date. “Search Console exports bulk data once per day, though not necessarily at the same time for each table.” If a day fails for a reason you must fix, such as lost permissions, Search Console retries that day for about a week and then gives up on it. When Google corrects old data, the whole day is replaced and epoch_version goes up by one.

How anonymized data looks

The export is complete except for privacy filtering. Anonymized queries are, in Google's definition, “Anonymized queries are those that aren't issued by more than a few dozen users over a two-to-three month period.” Their traffic is still in the tables, but without the query text: “When is_anonymized_query is true, this will be a zero-length string.” For Discover, rows below the privacy threshold carry is_anonymized_discover, described as “Whether the data row is under the Discover anonymization threshold.” Such rows lack fields like URL and country.

This is a real difference from the API, which simply has no row for anonymized queries. In BigQuery you can measure exactly how much of your traffic is anonymized, which the other routes cannot show you per day and URL.

Cost and retention

BigQuery bills the Google Cloud project, which means you, not Google, pay. “Remember that when you run a query on BigQuery you will be charged for it, and your tables can become quite large.” Storage grows every day: “Data will be accumulated forever for your project, unless you set an expiration time for your data.” If you set a partition expiration, Google's rule is: “Make sure the expiration is 14 days or longer, and avoid changing the table schema (for example, adding a column).” A changed schema makes the export fail. Never set an expiration on the whole table, which would delete everything.

Querying the export: SQL that gives correct numbers

The tables are not pre-summed. The same query, date and search type can appear in several rows, so “you should almost always aggregate all your metrics.” Position is stored as a zero-based sum, so the average needs a division and + 1. And every query is billed by the bytes it scans, so filter on the partition column first.

-- Top non-brand queries per URL, last 28 complete days, web search
SELECT
  url,
  query,
  SUM(clicks)      AS clicks,          -- ① always aggregate: keys repeat across rows
  SUM(impressions) AS impressions,
  SAFE_DIVIDE(SUM(clicks), SUM(impressions)) AS ctr,   -- ② recompute CTR, never average it
  SAFE_DIVIDE(SUM(sum_position), SUM(impressions)) + 1 AS avg_position  -- ③ zero-based, so +1
FROM `my-project.searchconsole.searchdata_url_impression`
WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
                    AND DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY)  -- ④ partition filter = lower cost
  AND search_type = 'WEB'
  AND NOT is_anonymized_query                           -- ⑤ drop the empty-query rows
  AND NOT REGEXP_CONTAINS(query, r'example|exmple')        -- ⑥ your brand variants
GROUP BY url, query
HAVING SUM(impressions) >= 50
ORDER BY clicks DESC
LIMIT 1000;

The notes in detail:

1. Aggregate. Google's own example shows why: a plain SELECT query, impressions returns one fragment of a query's traffic, while SUM(impressions) … GROUP BY query returns the total.
2. Recompute ratios. CTR is SUM(clicks) / SUM(impressions) over the rows you group, never an average of per-row CTRs.
3. Position. Use SUM(sum_position)/SUM(impressions) + 1 on the URL table and SUM(sum_top_position)/SUM(impressions) + 1 on the site table.
4. Date filter. Google advises using “a WHERE clause to limit the date range in the date partitioned table”. A query without it scans the full history every time.
5. Anonymized rows. “Anonymized queries often make up the single most common query for a site.” Leave them out of top-query lists, but keep them in totals when you compare with the Performance chart.
6. Search type values. Google's sample queries use upper-case values such as 'WEB' and 'DISCOVER', and lower-case country codes such as 'usa'.

Choose the table by question. Sitewide totals, query trends and country or device splits come from searchdata_site_impression. Anything per URL, per search appearance or for Discover comes from searchdata_url_impression. Never add the two together: they count the same impressions differently.

Looker Studio connector: site vs URL impression

Looker Studio has a free, native Search Console connector. Google's current documentation calls the product Data Studio again, so you will see both names. The connector is the shortest route from Search Console to a shareable dashboard, and it can sit next to Google Analytics, Google Ads or BigQuery data in the same report.

To connect: create a data source, choose the Search Console connector, authorize, pick the property, then choose the table and default search type. You must be signed in with an account that has at least view permission on the property.

Two tables, one per data source

The connector exposes the same two aggregations as the bulk export. “The Search Console connector gives you access to both these methods however a single data source can only use one of these.”

• Site Impression: aggregated by property. Search types web, image, video and news. Use it for totals, query trends, countries and devices.
• URL Impression: aggregated by page. Adds Discover and Google News. Use it for landing-page reports and anything with a page dimension.

For both views in one dashboard, Google's advice is: “To see your site impressions and URL impressions side by side, create two data sources and add both to a single report.” Label each chart with its table, because a Site Impression total and a URL Impression total for the same period will not match.

Blank queries and row limits

Privacy filtering shows up as an empty query: “In this case, Data Studio may aggregate these queries into a single row with a blank value.” Filter out null values of the Query dimension in tables of top queries. The connector also uses the same data access as the API, so very large sites hit the same daily row ceiling. When that happens, point Looker Studio at the BigQuery tables instead of the connector.

Credentials

Every data source runs on one of three credential types. “OWNER'S CREDENTIALS let other people view or create reports that use this data without requiring them to have their own access to the dataset.” Viewer's credentials require each viewer to have their own access; service account credentials use a non-human account. With owner's credentials, sharing the report is sharing the data, so treat the share list as an access list.

Which option to use when

A practical signal: if your daily API pull for one search type regularly reaches tens of thousands of rows, you are near the ceiling and the bulk export starts to pay for itself. If it returns a few thousand rows, the API already gives you everything that is not anonymized.

Automation ideas

• Daily archiveOne API request per day and search type, three days back, paged to zero rows, written to a database or BigQuery table you own.
• Export health checkQuery ExportLog each morning; alert when a data_date is missing for either table.
• Scheduled summariesBigQuery scheduled queries that build weekly page and query tables, so dashboards read small tables, not raw data.
• Drop alertsCompare each page's clicks with the same weekday a week earlier and flag large falls on important URLs.
• JoinsMatch url with crawl data (status, canonical, indexability) or CMS data (author, publish date, template).
• Brand splitA maintained regex of brand variants applied in SQL or as a Looker Studio calculated field.

A health check on the export log can be this short:

SELECT data_date, namespace, MAX(epoch_version) AS version, MAX(publish_time) AS published
FROM `my-project.searchconsole.ExportLog`
WHERE data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 10 DAY)
GROUP BY data_date, namespace
ORDER BY data_date DESC;  -- expect two namespaces per day

Failed exports are not written to ExportLog, so a missing day in this result is your alert. The settings page in Search Console shows the error itself.

Access and privacy hygiene

Search data shows what people type to find you and which pages earn money. Treat every export route as an access decision.

1. Use a dedicated service account for scripts. Create it in Google Cloud, add its e-mail as a user in Search Console, and give it Restricted permission when it only reads performance data. Do not run scheduled jobs on a person's account.
2. Request the read-only scope. webmasters.readonly is enough for the Search Analytics method. The full webmasters scope can also change sitemaps and properties.
3. Keep keys out of code. Store service account keys in a secret manager, not in a repository, a shared drive or a spreadsheet. Rotate them and delete unused ones; where your platform allows keyless authentication, prefer it.
4. Grant only the documented roles. Search Console's export account needs BigQuery Job User and BigQuery Data Editor, nothing more. Analysts usually need read access to the dataset and permission to run jobs, not project ownership.
5. Choose Looker Studio credentials deliberately. Owner's credentials expose the data to everyone the report is shared with. For sensitive properties, use viewer's credentials.
6. Review users regularly. Remove former employees, agencies and old service accounts from Search Console, the Cloud project and shared reports.

Common mistakes

• ✕Summing table rows and expecting the chart totalAnonymized queries and the 1,000-row cap mean the rows always add up to less.
• ✕Averaging CTR or positionRecompute CTR from summed clicks and impressions; compute position from sum_position plus one.
• ✕Reading a zero position as first placeIn UI exports, ~ and - become zeros.
• ✕One long date range in one API callLoad grows with the range and the row ceiling is per day. Query day by day.
• ✕Treating an empty page as an errorA zero-row response is how pagination ends.
• ✕Mixing site and URL aggregationSite Impression and URL Impression count differently. Do not add or compare them without saying so.
• ✕BigQuery queries without a date filterEvery run scans and bills the full history.
• ✕Expecting a backfillThe bulk export starts on setup day. Earlier months come only from the API or the reports.
• ✕Editing the export tablesAn added column breaks the export. Build your own tables from the export instead.
• ✕Owner rights for every scriptOne leaked key with full rights exposes every property it can reach.

FAQ

Sources

• Search Console API — Search Analytics: query
• Search Console API — Get all your search traffic data
• Search Console API — Usage limits
• Google Search Central Blog — A deep dive into Search Console performance data filtering and limits
• Google Search Central Blog — Bulk data export: a new and powerful way to access your Search Console data
• Search Console Help — About bulk data export of Search Console data to BigQuery
• Search Console Help — Start a new bulk data export
• Search Console Help — Table guidelines and reference
• Search Console Help — Query guidelines and sample queries
• Search Console Help — Performance report: Troubleshooting data discrepancies
• Search Console Help — Performance report: About the data
• Google Cloud — Data Studio (Looker Studio): Connect to Search Console

Quotes checked against the official pages on 30 October 2026.