Discussion
  • Enterprise SEO
  • Search Console

Has the Search Console bulk export to BigQuery changed real decisions for you, or become an expensive data lake nobody queries?

Published 1 reply

Discussion starter: an enterprise SEO question, posted with an answer from Niraj Raut to open the thread. If you have dealt with this on a site, reply with what you saw, especially where it differs.

Disclosure: Niraj Raut, who posted this answer, runs the SEO consultancy linked at the end of it.

1 reply

  • admin
    Admin
    Original poster

    The export changes decisions when a question needs something the Search Console interface and API cannot supply: the full long tail of query and URL pairs, history older than 16 months, or a join with your own data such as templates, stock, revenue or publish dates. When nobody owns questions like those, it turns into the data lake you describe.

    The Cloud bill is the visible cost, and the easiest one to control. The costs that make an export feel expensive are harder to see: dashboards reading raw tables, analysts reconciling numbers that were never designed to match the interface, and nobody deciding what the data is for. So the test is not site size on its own. It is whether someone owns a handful of recurring decisions that need export-only data. Size matters because the long tail the interface hides grows with the site.

    What the export gives you that the interface and API do not

    Feature Performance report Search Analytics API Bulk export to BigQuery
    Rows Top 1,000 rows per table More rows, but a daily cap trims the long tail on large sites Full daily performance data; anonymised queries appear as flagged rows with the query text withheld
    History 16 months 16 months From setup onwards, kept for as long as you retain it; no backfill
    Joins None Only through your own pipeline SQL joins with crawl, log, CMS, product or GA4 data in the same location
    Cost Free Free, within quotas Cloud storage and query charges above a free usage level

    Google’s setup guide is explicit on two points that bear directly on the “is it worth it” question. For data from before your setup date it points you to the API or the reports, so the export has no backfill. And the data is subject to Google Cloud storage and query costs, with a free usage level and charges above it. The same page notes that you cannot easily change the dataset’s location once exports have begun. That matters more than it sounds, because BigQuery joins need datasets in the same location, so pick the location your GA4 export or other SEO data already uses.

    Decisions where the export can change the answer

    These are the decision types where export-only data can reorder a priority list, not just add detail to it.

    Decision Why the interface or API falls short What to build in the export
    Which overlapping pages to consolidate Overlap between two pages can be spread across many low-volume queries that fall below row limits A table of queries where two or more of your URLs earned impressions, summed over 90 days
    Whether a release hurt one template Interface filters work on URL patterns but cannot join to a template map or release log Daily clicks and impressions by template, joined to a URL-to-template mapping and release dates
    How this core update or season compares with two years ago The interface and API stop at 16 months Partitions retained for as long as your longest real comparison window
    Which pages to improve first Search Console has no revenue, margin or stock data Landing-page performance joined to GA4 or product data
    How much query-level reporting is missing Query reports omit anonymised queries without saying how much they hold Share of clicks and impressions on anonymised rows, by page or section

    Hypothetical example:

    A retailer with 300,000 indexable URLs builds its quarterly consolidation list from the API. Two category pages look distinct there because each ranks for different head terms. In the export, the same two URLs share impressions on several thousand long-tail queries, none large enough to appear in the API’s top rows, and both sit on page two for most of them. That overlap is the evidence for a merge test. If the export had shown little shared long tail, the API list would have been good enough, and that is worth knowing too.

    Where the export adds little

    On a site where the interface already shows almost every query, the export mainly buys longer history. Some questions that once needed SQL are now answered in the interface: Search Console has added a branded queries filter and query groups in Insights. The export has its own schema, so check whether a given interface feature exists there before planning a report around it. And if nobody on the team writes SQL or maintains data models, the export’s value is capped at whatever a contractor builds once and nobody updates.

    Why export numbers do not match the interface

    This is where an export can lose the team’s trust. An analyst sums a column, gets a different total from the Performance report, and the team goes back to the interface. The differences are documented in Google’s table guide:

    • Two tables, two aggregations. searchdata_site_impression aggregates by property and searchdata_url_impression aggregates by URL, and Google notes that their totals differ. Use the property table for site totals and the URL table for page analysis, and do not compare one with the other.
    • Anonymised rows are included, without query text. Rows with is_anonymized_query set to true have a null query field. A sum across named queries comes out below the total whenever anonymised rows carry clicks or impressions, by design.
    • Position is zero-based. The stored sums start at zero for the top position. Average position is SUM(sum_position) / SUM(impressions) + 1 in the URL table, using sum_top_position in the property table. Averaging the raw column gives a wrong number.
    • Rows repeat keys. Google advises aggregating metrics rather than reading individual rows.
    • Search types are separate. Web, image, video, news, Discover and Google News sit side by side, so filter the search type explicitly.

    Fix this once, centrally:

    Write these rules into a small set of shared views (site totals, page by day, query by page) and point every dashboard at the views. When each analyst writes their own raw-table SQL, the mismatches return and trust goes with them.

    Keeping the bill small and the data intact

    The setup guide recommends partition expiration to manage storage costs. On the query side, BigQuery’s on-demand pricing charges for the data a query processes, so most of the cost control is ordinary BigQuery discipline:

    • Filter on data_date in every query and select only the columns you need.
    • Materialise daily aggregate tables, and point Looker Studio or other dashboards at those instead of the raw URL table, which a busy dashboard can query many times a day.
    • Set a Cloud billing budget alert and per-user or per-project query quotas, so one careless query cannot surprise anyone.
    • Set retention to your longest real comparison window. Once you delete partitions older than 16 months, no other Search Console source can replace them.
    • Check each morning that the previous day’s data arrived. Google says Search Console retries the next day after a non-persistent error, but a removed permission or a billing problem needs a person to notice. A gap within the last 16 months can be partly patched from the API; an older one cannot.

    Before you switch it off:

    Because the export cannot be backfilled, turning it off and restarting later leaves a permanent hole in your long-tail history. On a large site, a quiet export with partition expiration and no dashboards costs mainly storage, and it keeps the option open. Decide on the value of that option, not on how often the tables were queried last month.

    A one-quarter test: does it change the decision?

    A quick, honest test is a shadow decision. Take one decision you already make on a schedule, such as a monthly consolidation shortlist or a refresh priority list, and for three cycles build it twice: once from the interface or API, and once from the export joined to your own data. Three monthly cycles give you three comparisons without waiting half a year.

    Each cycle, build the same shortlist twice: from API data, and from the export joined to your own data
    ↓
    After three cycles, do the top 20 items differ materially?

    If they do not

    The export is not changing this decision. Move it to storage-only (retention set, no raw-table dashboards) and revisit when a new question needs it.

    If they do

    Act on the export-only items, record what happened to them, and give the export a named owner and a modelled layer.

    The metric that matters is not the number of queries run against the dataset. It is how many items on the export-based list were missing from the API-based list, were acted on, and moved the outcome the list exists for (clicks to consolidated pages, revenue from improved pages). Usage counts reward busy dashboards; this rewards decisions.

    My practical position

    If I inherited a quiet export on a large site, I would not switch it off first. I would set partition expiration, stop any dashboard that reads raw URL tables, publish the shared views, and give it one quarter with a named owner and two or three decisions to feed. On a small site where the interface already shows nearly every query, I would keep it only if someone needs more than 16 months of history, and say so explicitly rather than letting it run as an unowned cost.

    Need help with this on your own site?

    Niraj Raut works with businesses in Nepal, Australia, the UK, Europe and the US on technical SEO, ecommerce SEO, local SEO and AI search.

    See SEO services

Add a reply

Replies are open to approved members. Log in or apply to join.