How to Connect SEO Data to Google BigQuery

Connecting your SEO data to Google BigQuery lets you analyze organic search performance alongside your paid media data. You see impressions, clicks, keyword rankings, and CTR from Google Search Console in the same database as your ad spend, CRM data, and revenue.

Here is how to do it.

Why SEO Data Belongs in BigQuery

Google Search Console keeps your data for 16 months. After that, it is gone. BigQuery keeps your data indefinitely. By connecting Search Console to BigQuery, you build a permanent historical record of your organic search performance.

You also get the ability to combine organic and paid data in one analysis. You see which keywords you are paying for in Google Ads while also ranking for organically. You identify keywords where you are spending money on ads but already ranking in the top three organically, which is often a signal to reduce paid spend.

What Data Google Search Console Sends to BigQuery

Google Search Console provides these metrics and dimensions for BigQuery analysis:

• Impressions: how many times your pages appeared in search results.
• Clicks: how many times users clicked through to your site.

CTR (click-through rate): clicks divided by impressions.

Average position: your average ranking position for each query.

Queries (keywords): the search terms that triggered your pages.

• Pages: which URLs received impressions and clicks.
• Countries: where the searches came from.
• Devices: desktop, mobile, or tablet.
• Dates: when the data was recorded.

Step 1: Connect Search Console to BigQuery With Porter Metrics

In Porter Metrics, create a new query and select Google Search Console as your data source. Authenticate with the Google account that has access to your Search Console properties.

Select your property (your website URL) and choose the metrics and dimensions you want to include. For a standard SEO report, select:

• Date
• Query (keyword)
• Page
• Country
• Device
• Impressions
• Clicks
• CTR
• Average position

Set your write mode to “update” and schedule daily syncs. Porter loads your Search Console data into BigQuery automatically every day.

Step 2: Query Your SEO Data in BigQuery

Once your data is in BigQuery, run queries to analyze your organic search performance.

Top keywords by clicks for the last 30 days:

SELECT
query,
SUM(impressions) AS total_impressions,
SUM(clicks) AS total_clicks,
ROUND(AVG(average_position), 1) AS avg_position,
ROUND(SUM(clicks) / NULLIF(SUM(impressions), 0) * 100, 2) AS ctr_percent
FROM your_dataset.search_console
WHERE date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) AND CURRENT_DATE()
GROUP BY query
ORDER BY total_clicks DESC
LIMIT 50

This returns your top 50 keywords by clicks for the last 30 days, with average position and CTR.

Step 3: Combine SEO and Paid Data

The most valuable analysis you get from having SEO data in BigQuery is the ability to compare organic and paid performance for the same keywords.

SELECT
sc.query AS keyword,
SUM(sc.impressions) AS organic_impressions,
SUM(sc.clicks) AS organic_clicks,
ROUND(AVG(sc.average_position), 1) AS organic_position,
SUM(gads.cost) AS paid_spend,
SUM(gads.conversions) AS paid_conversions
FROM your_dataset.search_console AS sc
LEFT JOIN your_dataset.google_ads_keywords AS gads

ON LOWER(sc.query) = LOWER(gads.keyword)

AND sc.date = gads.date

WHERE sc.date BETWEEN ‘2026-01-01’ AND ‘2026-01-31’
GROUP BY keyword
ORDER BY paid_spend DESC

This query joins Search Console data with Google Ads keyword data. You see every keyword where you are paying for ads alongside your organic ranking for that same keyword. Keywords where you rank in position 1-3 organically and also spend on paid are candidates for budget reallocation.

Step 4: Track SEO Performance Over Time

Track how your organic traffic changes month over month:

SELECT
FORMAT_DATE(‘%Y-%m’, date) AS month,
SUM(impressions) AS total_impressions,
SUM(clicks) AS total_clicks,
ROUND(AVG(average_position), 1) AS avg_position
FROM your_dataset.search_console
GROUP BY month
ORDER BY month ASC

This gives you a monthly summary of your organic search performance, which you visualize as a trend line in Looker Studio.

Building Your SEO Dashboard in Looker Studio

Connect Looker Studio to your BigQuery Search Console table. Build a report with:

Scorecards for total clicks, total impressions, average CTR, and average position.

A line chart showing clicks and impressions over time.

A table of top keywords by clicks with position and CTR columns.

A filter for country and device to segment the data.

Because the data comes from BigQuery, your dashboard loads fast and covers as much historical data as you have stored.