--- title: The Search Console UI Samples Your Data. The BigQuery Bulk Export Does Not, and It Is Free. description: "Search Console's UI and API cap what you can see, but the free BigQuery Bulk Export does not. Here is how to enable it correctly, what the tables contain, and three queries to write on day one." author: LETSGROW Dev Team date: 2026-09-30 category: Analytics tags: ["Search Console", "BigQuery", "SEO Analytics", "Data Export", "SQL"] url: "https://letsgrow.dev/blog/search-console-bulk-export-bigquery-seo-analytics" --- # The Search Console UI Samples Your Data. The BigQuery Bulk Export Does Not, and It Is Free. If your SEO reporting still runs on screenshots from the Search Console interface, you are making decisions with a truncated dataset. The UI caps table exports at 1,000 rows and the API caps you at 50,000 rows per day per search type per property. Meanwhile, Google will hand you the full, unthrottled feed through the Bulk Data Export to BigQuery, at no charge beyond your normal BigQuery storage and query costs. Most marketing teams have not switched it on. That is the cheapest technical upgrade in SEO analytics, and it has a hard deadline: the export is not backfilled, so every day you wait is history you never get back. ## What you actually get in the export Once enabled, Search Console writes daily into a dataset in your Google Cloud project. Three objects matter. The `searchdata_site_impression` table holds data aggregated by property, so it is the right source for totals. The `searchdata_url_impression` table holds data aggregated by URL and query, which is where all the interesting work happens. The `ExportLog` table tells you which days landed successfully, which you should check before trusting any trend line. Two details separate people who use this well from people who get burned. First, anonymized queries, the rare long-tail searches Google withholds for privacy, appear as rows with a null query and the `is_anonymized_query` flag set. They are still counted in clicks and impressions, so if you sum only rows with a query string you will undercount. Second, the site-level and URL-level tables will not match exactly, because of how each is aggregated. Pick one table per report and stay consistent. ::compare-table columns: ["Dimension", "Search Console UI", "Search Console API", "BigQuery Bulk Export"] rows: - ["Row limit", "1,000 rows per export", "50,000 rows per day per search type per property", "No row cap on the daily export"] - ["History", "16 months, rolling", "16 months, rolling", "Starts the day you enable it, kept as long as you keep it"] - ["Joins with other data", "Manual, in spreadsheets", "Custom code required", "Native SQL joins with GA4, CRM, and revenue tables"] - ["Anonymized queries", "Hidden in totals", "Hidden in totals", "Exposed as flagged rows so totals reconcile"] - ["Setup effort", "None", "Auth, pagination, scheduling", "One settings screen plus IAM grants"] :: ## The setup, in the order that avoids failures Most failed setups die on permissions, not on configuration. Follow this order and it works the first time. ::checklist title: Enable the export without the usual failures items: - Create or pick a Google Cloud project with billing enabled. The export will not run on a project without it. - Enable the BigQuery API in that project. - Grant the Search Console service account (search-console-data-export@system.gserviceaccount.com) the BigQuery Job User and BigQuery Data Editor roles on the project. - In Search Console, open Settings, then Bulk data export, and enter the project ID and a dataset location. Choose the location deliberately, because it cannot be changed later. - Confirm you are a verified owner of the property. Restricted users cannot configure the export. - Check ExportLog after 48 hours. The first export typically arrives within a day or two. - Set a partition expiration only after you decide how much history you want. The default keeps everything. :: ## Three queries worth writing on day one The export earns its keep when you stop treating rankings as the unit of analysis and start joining search behavior to outcomes. Start with these. **Striking-distance pages.** Filter `searchdata_url_impression` to queries where average position sits between 8 and 20 with meaningful impressions over the trailing 28 days. Position in this table is zero-indexed, so add one before you compare it to what you see in the UI. Sort by impressions times the gap between actual CTR and expected CTR at position 3. That is your refresh backlog, ranked by upside instead of gut feel. **CTR outliers by page template.** Group URLs by template using a regex on the path, then compare CTR at equal positions across templates. If your comparison pages earn half the CTR of your guides at the same rank, the title and snippet pattern is the problem, and you have found it without running a single test. **Search demand joined to revenue.** Join URL-level clicks to your GA4 BigQuery export on landing page and date, then to CRM opportunities on session identifiers. Now you can say which queries feed pipeline, not which ones feed sessions. This is the report that gets SEO a seat in revenue conversations, and it is impossible in the UI. ## Where teams still get this wrong The failure mode after setup is treating the dataset as a data lake nobody queries. Schedule three things. A daily job that checks ExportLog and alerts when a day is missing. A weekly materialized view that pre-aggregates the striking-distance list so analysts are not scanning raw tables and paying for it. A monthly review where someone owns the outcome of the backlog the queries produce. Also cost-control your own queries. Always filter on the `data_date` partition column and select only the columns you need. An unfiltered scan of a large property's URL table is the fastest way to turn a free export into a surprise invoice. The point is not that BigQuery is fashionable. The point is that Search Console is the only first-party source of what people searched before they found you, and Google is offering you the unsampled version of it for free. Turn it on this week, verify the first export lands, and write the striking-distance query before the end of the month. Every day of delay is a day of your own search history you will never be able to analyze.