Google Search Console to ClickHouse: How to Move Your Data

Moving Google Search Console into ClickHouse gives you fast analysis over search performance that Google itself only keeps for about sixteen months. Anything older exists only in what you collected, which makes this pipeline more valuable the earlier you start it.

This guide covers the managed path with Airbyte. Two things shape the build: Search Console revises recent figures after publishing them, and the dimensions you choose define your streams rather than filtering them.

Google Search Console to ClickHouse at a glance:

Why move data from Google Search Console to ClickHouse?

Two situations account for most of these pipelines.

The first is interactive analysis across a long history. Search data is granular and grows quickly, and questions about how a page or a query group has performed across two years are exactly the aggregations a column store answers in a second rather than a minute.

The second is joining search performance to what happened next, such as conversion or revenue. If you also want to run language models over query text or page content, Google Search Console to Databricks is the better home for that kind of work.

What do you need before you start?

Four things, and the first is an access level people often discover they lack:

Owner or Full User access to each property. Restricted or view-level access is not enough to read the API, so this may be a request to whoever administers your properties. The Google Search Console source documentation covers authentication and the report options.

Your site URLs in the right form. The field takes a list, and a domain property is written with the sc-domain prefix rather than as an ordinary address, which is the most common configuration mistake here.

A decision about data state. Final gives verified figures that lag, and all gives fresher numbers that Google may revise. The same day can show different values under each.

An agreed set of dimensions per report. Each combination you define becomes its own stream, so this decides how many tables you end up with and what each one can answer.

If your database restricts inbound traffic by IP, add the Airbyte Cloud IP addresses to the allow list before you begin.

How do you build a Google Search Console to ClickHouse pipeline in Airbyte?

Step 1: Choose your data state and write it down

Decide whether you want verified figures or fresh ones, then record the choice somewhere beside the tables, because nothing in the data says which you picked. A dashboard built on fresh figures and a report built on verified ones will disagree about the same week, and without that note the disagreement looks like a pipeline fault rather than a deliberate setting.

Step 2: Configure the Google Search Console source

Click Sources in the left navigation, then New Source, and select Google Search Console, following adding a source. Authenticate, list your site URLs, set a start date and data state, then define any custom reports. One source can cover several properties, so a business with a few sites usually needs only one.

Step 3: Configure the ClickHouse destination

Click Destinations, then New Destination, and select ClickHouse, following adding a destination. Supply the host, port, database and credentials. Records land in typed columns over the native protocol, which is what makes aggregating two years of search data quick.

Step 4: Create the connection and sort for your queries

Click Connections, then New connection, select your streams and a sync mode. Daily is right for search data. Then set a sorting key beginning with the date, because almost every question against this data bounds a period and that is what lets ClickHouse skip rather than scan.

Then apply FINAL in the views anybody reports from, for the reason covered next.

Why do yesterday's numbers change?

Because Search Console publishes early figures and corrects them later. Choosing the fresher data state gets you numbers quickly and accepts that Google may revise them; choosing the verified state waits for figures that will not move. Neither is wrong, and the same day genuinely shows different values depending on which you asked for.

In a column store that produces a specific and slightly awkward consequence. A revised figure arrives as another row for the same combination of date, dimensions and property rather than as an update, and ClickHouse resolves that during background merges rather than on write. Between the sync and the merge, both versions are present.

So use FINAL in any view somebody reports from, and accept the modest cost for the confidence it buys. Counting rows directly from the underlying table will inflate clicks and impressions in a way that looks like a good week rather than an error, which is the kind of wrong number that survives review because everybody wants to believe it.

How should you shape the tables?

Around the dimension combinations you chose, because those are the tables. A custom report is defined by its set of dimensions, and each set becomes a separate stream, so asking for query-level and page-level breakdowns produces two tables rather than one table you can group differently. That is worth understanding before anybody promises a dashboard.

Date is always part of the result whether you listed it or not, which is convenient because it is also what you should sort by. A record is identified by its dimensions together with the date, the site URL and the search type, so a table covering several properties needs the site URL in any grouping you write.

Choose dimensions for the questions rather than for completeness, since a report broken down by query, page, country and device is enormous and mostly empty. And start collecting sooner than you need to, because Search Console keeps roughly sixteen months and the history beyond that is whatever your tables happen to hold.

Frequently asked questions

Why do two reports disagree about the same week?

Probably different data states, since verified and fresh figures differ for recent days. Record which one your pipeline uses beside the tables.

My clicks look too high.

Revised figures arrive as additional rows and ClickHouse deduplicates during background merges, so report through views applying FINAL rather than from the raw table.

The connection fails on a property.

Check the access level, since Owner or Full User is required, and check that domain properties are written with the sc-domain prefix rather than as a normal URL.

Can I group one table by different dimensions?

Not across combinations you did not request. Each dimension set is its own stream and table, so decide them from the questions you intend to ask.

Can I do this without writing code?

The pipeline, yes. The views applying FINAL and the sorting keys on your tables are SQL, and both are what make this fast and accurate rather than merely populated.

Get your Google Search Console data into ClickHouse

Confirm Owner or Full User access, write domain properties with the sc-domain prefix, and choose your data state deliberately, recording it beside the tables. Define dimension sets from the questions people will ask, since each set is a separate table. Then sort by date, apply FINAL wherever a figure leaves the building, and start collecting early because Google keeps only about sixteen months.

Airbyte's connector catalog includes 600+ pre-built connectors, so search performance can outlive Google's own retention. For the same source into an operational database, see Google Search Console to MySQL, and for attribution data into the same destination, Adjust to ClickHouse.

Start syncing now →