Import your Google Search Console data into your data warehouse - Blendo

How to Setup Google Search Console Integration

This integration will allow you to connect with Google Search Console and start collecting your data. It is a matter of minutes to send your shipments data to feed your BI, your data warehouse, or your custom analytics stack.

Here are the steps involved:

  1. Connecting Google Search Console
  2. Set the Replication Frequency
  3. Google Search Console expected Data Model and schema

Getting Started – Setup Google Search Console integration at Blendo

In order to connect Google Search Console with Blendo, you should verify that you are verified owner of the sites and have Owner or Full User permissions for your Google Search Console account.

1. To add this integration, go to Blendo dashboard. Click Add a Source and click on Google Search Console.

2. Click on the Google Search Console icon.

Import your Google Search Console data into your data warehouse - Blendo3. Next, you will be prompted to choose a Google account to log into and to approve Blendo’s access to your Google Search Console data. Blendo will only ever read your data.

4. Click Allow to continue.

5. In the new Setup your Google Search Console source screen, type the URL of the page you want to the info from.

Import your Google Search Console data into your data warehouse - Blendo

Site URL

Please make sure to use the exact URL as found inside Google Search Console.

Select Dimensions to Sync

6. Now we need to select the Dimensions for the report we want to replicate to your destination database. In the Dimensions field, you can see the list of dimensions available for selection. You may use the autocomplete search or use the drop-down to explore it.

Before you start some things to note:

  • Valid Combinations: Not all Dimensions can be queried together. The available dimensions are QueriesPagesCountriesDevices and Search Type. Please note that currently is not possible to combine the Search Type dimension with other dimensions and therefore should be on its own in a report.
  • Metrics: The metrics will always be the following: clicksimpressionsctrposition.
  • From Google Search Console API we can pull data up to 90 days in the past and as recent as 3 days ago (Google Search console documentation here) because of the retention policy of Google. So it is important to have that in mind along with the fact that the most recent data that you will get will be 3 days old.
  • In the Site URL please make sure to use the exact URL of the site as depicted inside Google Search Console.
  • Each site URL corresponds to one pipeline and one table with a report.
  • The Dimensions and Metrics combination can’t change after the pipeline integration is saved. The primary key Blendo creates depends on your choices during the original setup.

Start from date

7. As written above Google Search Console API we can pull data up to 90 days in the past and as recent as 3 days ago because of the retention policy of Google. So the default address will be that maximum.

8. Add a Table Prefix if you want.

9. Choose your Database Schema (if applicable)

10. Review again your selections. Once saved, you cannot add or remove dimensions or metrics. Click Validate & Save.

Initial sync and Historical data

For a new integration, Blendo will schedule an initial sync job. That will include the historical data and may take some time to complete.

Set the Replication Frequency

This configuration controls how often Blendo will pull data from your Google Search Console integration. Loading to your data warehouse could take a little bit longer.

11. If you want to change the replication schedule go to the Schedule section and click on Reschedule.

8. In the next pop-up select one of the options available and click Apply.

To help prevent overages, consider setting the integration to replicate less frequently. Ask us for help or recommendations.

Google Seach Console Data Model & Schema Information

When Blendo first connects to Google Search Console Data source, will schedule an initial sync job. That will include the historical data and may take some time to complete.

Due to Google’s retention policy, Google Search Console API allows us to pull data up to 90 days in the past and as recent as 3 days ago.

Sync Schedule

Blendo syncs Google Search Console data based on the schedule and frequency you choose. The default setting is every 1 hour but is configurable from inside the app.

Due to Google’s retention policy, and the way Google Search Console API allows us to pull data you will be able to see daily data.

Replication Process

The first time Blendo syncs your Google Search Console data performs a full replication. Then your Google Search Console data is replicated incrementally. Note that Blendo will only read your data.

After you finish the integration with Google Search Console, you might see its status as Importing from sourceExporting to the destination, or Completed.

What Google Search Console data do I have available?

Blendo creates a table for each Google Search Console integration you create. The schema of the table will have the DimensionsMetrics, and date as columns.

Let’s see an example. Selecting:

  • Country, Device, Page, Query as Dimensions
  • Metrics will default to Impressions, Clicks, Position, CTR

the table will have countrypagedevicequery, impressions, clicks, position, ctr, date as columns.

Here is an example of such a table

Column
Type
blendo_idPrimary Key
blendo_imported_atDate
DateDate
impressionsNumber
pageString
queryString
countryString
clicksNumber
positionNumber
ctrNumber
deviceString

Primary Keys

As Google Search Console does not provide a primary key that can be used for deduplication, the primary key is blendo_id. This is composed by using the dimension columns plus the date.

Table Rows

Google Search Console data fetched on a daily basis. Every sync will pull the report for the current day.

Google Search Console Integration Troubleshooting FAQ

I get Import error message "Sorry, Please enable order management site setting to use this API"

If ….

For more details please check the documentation from XXX.

Questions? Contact us!

in Connecting SaaS Data Sources