BigQuery SEO rank tracking: weekly ranks and the AI Overview as a table

Google's BigQuery connector is a remote MCP server at bigquery.googleapis.com/mcp that lets Claude list datasets and tables and run SQL under your own permissions. The top results for this query fall into three routes. A tutorial from Porter Metrics, ranking first for this query, covers pulling SEO data into BigQuery to analyze next to paid media. Supermetrics moves Semrush rankings into BigQuery on an hourly, daily or weekly transfer. Rank trackers such as AccuRanker write their own tables there.

AccuRanker's guide shows what that costs in setup. It needs a 20K keyword subscription or above, a request to support to enable BigQuery, billing on your Google Cloud account and a service account key. It then appends yesterday's ranks daily to tables including all_ranks and own_ranks.

LemRank states the limits plainly: no native BigQuery connector, so you write the loader, and API history covers 90 days per request. If your tracker has no connector, a pipeline you build is the only route.

Claude plus two connectors is a fourth route: the rank source is Linkeddit, and the pipeline is a prompt you approve each week.

Google's bulk data export sends Search Console performance data to BigQuery once a day. It creates the tables searchdata_site_impression and searchdata_url_impression, plus an ExportLog table. Google says it covers all the performance data Search Console has for the property, except anonymized queries.

Three facts decide how you use it. The first export arrives up to 48 hours after setup and is not retroactive; Google points to the Search Console API for older data. Rows repeat keys, so Google says to aggregate metrics almost always. And a table's schema must not change, because adding a column makes the export fail.

Google's position fields are impressions-weighted. In the URL table, average position is SUM(sum_position) / SUM(impressions) + 1. That is an average over everything people saw. Linkeddit's rank is different: the best organic position of your domain on the checked Google results page, from 1 to 10, or null.

Neither replaces the other. The export says what real searchers saw and clicked. Linkeddit says where a defined keyword sat on one weekly check, and what the AI Overview did. Join them on URL.

Rank alone misreads the result. On the test project, cookieless analytics returned a null rank, yet Google's AI Overview listed the domain as a source. For privacy friendly analytics the domain ranked 3 and no Overview was shown. For google analytics alternative it ranked 2, an Overview appeared and cited the domain too.

That is three keywords and three different situations. A table with only a position column would call the first a loss and could not tell the second from the third. Store ai_overview_shown and ai_overview_cites_us as booleans beside rank, and top_domain as a string so you can group by who holds position 1.

SQL then answers questions a dashboard rarely does: for keywords with an Overview, how often are you cited when you rank outside the top 10? Linkeddit supplies each check as a row, and Claude supplies the query.

A rank column alone can hide this. A SaaS marketer wrote on Reddit: "In all possible keywords where our page is ranking number 1 to 3 on Google, we are not showing up on Google AIO, as if we got shadow banned." A separate AI Overview column is what lets a query like that tell you whether Google cited you while you ranked, and when it stopped.

Use one row per keyword per check date and never update old rows. In Linkeddit, change is the previous rank minus the latest, so a positive number means the keyword moved up, and it is null on a first check or when either check was outside the top 10. Store it as a nullable column and do not turn it into zero. All 25 keywords on the test project returned a first check on 2026-10-28.

Google's BigQuery connector lists five tools: execute_sql, get_dataset_info, get_table_info, list_dataset_ids and list_table_ids. Google's documentation says execute_sql is the only tool that is not read-only, and that an IAM deny policy can restrict it. Its docs also describe an execute_sql_readonly tool, so check which tools your project exposes. Results are capped at 3,000 rows and queries at three minutes.

Writing the table is the one governed side effect in this workflow. Have Claude show each statement and wait for approval, and give the agent its own identity, which Google recommends so that access can be controlled and monitored.