Docs

Google Search Console Data Export

What is Google Search Console ?

Google Search Console is the place to get your SEO information on your website directly from Google. Search analytics report within Google Search Console provides helpful information about how your site performs in Google Search. It will break all these sections down for you:

a. Queries: The keywords that users searched for in Google Search.
b. Clicks: The count of clicks from a Google search results page that landed the user on your property.
c. Impressions: The amount of links to your site a user saw on Google search results.
d. Click-through-rate (CTR): The click count divided by the impression count.
e. Position: The average position of the topmost result from your site.

How to add a Google Search Console Source in Electrik.AI?

Follow these steps to connect your Google Search Console account with Electrik.AI

Step 1: Ensure you have following prerequisites to create a Google Search Console Connection in Electrik.AI

a.Google Search Console Account UserId and Password.

b.Google Search Console Property

Step 2: Sign-in into your Electrik.AI account

Step 3: Select IO in Electrik.AI dashboard

Select IO in Electrik.AI dashboard

Step 4: Select the Connections Icon in the left most panel and Click on “Add” button the top.

New-Select-the-Connections-tab-in-the-left-most-navigation-bar-click-on-Add-action-button

Step 5: Click on “Google Search Console” icon to create a Google Search Console connection.

Conn Step 5 Click on “Google Search Console” icon to create a Google Search Console connection ElectrikAI

Step 6: Enter your Google Search Console User Id and Password.

Conn Step 6 Enter your Google Search Console User Id and Password ElectrikAI
Conn Step 6b Enter your Google Search Console User Id and Password ElectrikAI

Step 7: Allow Electrik.AI to view Google Search Console Data for your verified sites.

Conn Step 7 Allow Electrik.AI to view Google Search Console Data for your verified sites ElectrikAI

Step 8: Choose your Site URL, for which you want to Extract Search Analytics Report.
Please note that you will see only secured URLs (https://) in the dropdown list.

Conn Step 8 Choose your Site URL, for which you want to Extract Search Analytics Report ElectrikAI

Step 9: Click on Save button on the top.

Conn Step 9 Click on Save button on the top ElectrikAI

Congratulations, you have successfully created a Google Search Console Connection in Electrik.AI

Congratulations, you have successfully created a Google Search Console Connection in ElectrikAI

What are the supported Data Destinations?

Electrik.AI supports the following databases to export Google Search Console Data. It is important to note that Electrik.AI itself is not a destination. A destination is required to export data from various marketing data sources using Electrik.AI

PostgreSQL Data Destination-Eletrik.AI
MySQL Data Destination-Electrik.AI
AzureSQL Data Destination-Eletrik.AI
Google Big Query Data Destination-Elecrik.AI

How to export Google Search Console Data ?

Electrik.AI creates a data flow (or ETL process) that exports Google Search Console reports of your choice to a target database or data warehouse. This process runs daily at scheduled time and exports data for the previous day.

Steps to create a Data Flow from Google Search Console to Database

Step 1: Ensure you have the following prerequisites.

  1. Access to Google Search Console
  2. Google Search Console User Id and Passwords
  3. Access to the Database of your choice (PostgreSQL, MySQL, Azure SQL).
  4. Database Connection Details such as Host, Port, DB Name, DB User, DB Password

Step 2: Sign-in into your Electrik.AI account

Step 3: Select IO in Electrik.AI dashboard

Select IO in Electrik.AI dashboard

Step 4: Click on Add button to create a Google Search Console – Search Analytics Report Extractor Flow

Click on Add button to create a Flow

Step 5: Select Google Search Console Icon present in the list of available sources

Flow-Step 5 Select Google Search Console Icon present in the list of available sources ElectrikAI

Step 6: Select your existing Google Search Console connection in Electrik.AI.
To Configure a new connection, refer to this link.

Flow-Step 6 Select your existing Google Search Console connection in ElectrikAI

Step 7: Click on a database of your choice from list of available destinations.
In this example we are choosing Azure SQL.

Flow-Step 7 Click on a database of your choice from list of available destinations ElectrikAI

Step 8: Select your existing Azure SQL connection in Electrik.AI.
To Configure a new connection, refer to this link.

Flow-Step 8 Select your existing Azure SQL connection in ElectrikAI

Step 9: a. Electrik.AI supports 6 reports, you can choose all or any number of reports as per your requirement. In this example we are chosen all.

Google Search Console Flow 9a ElectrikAI

b. Review the Data Source, Data Destination, Database Schema, Provide Success Notification Email, Failure Notification Email. Click “Save and Proceed” to continue.
Success Notification Email: Enter a valid Email ID to receive the Flow Success Email Notification.
Failure Notification Email: Enter a valid Email ID to receive the Flow Failure Email Notification.
Note: By Default, Electrik.AI uses the signed-in user’s Email Id.

Google Search Console Flow 9b ElectrikAI

Note:  By Default, Electrik.AI chooses an existing Database Schema to load your Google Search Console Report. You can create a new schema in your database by selecting the “Create New Schema” radio button.

Congratulations, Flow is created. Please wait while you are redirected to Flow Details Screen.

Congratulations, Flow is created. Please wait while you are redirected to Flow Details Screen ElectrikAI

Congratulations, you have now successfully setup Google Search Console Data Export to Azure SQL Database.

you have now successfully setup Google Search Console Data Export to Azure SQL Database ElectrikAI

Your Google Search Console data will start loading after 24 hours in your Database.

Your Google Search Console data will start loading-Electrik.AI

Google Search Console Data Schema

The Page Performance report includes all statistics aggregated at the web page level.

Index Category Electrik.AI Column Name Description Sample Value
1 Search Console pagepath Website Page URL https://electrik.ai/how-to-extract-google-analytics-data-to-microsoft-azure-sql-database
2 Search Console snapshot_dt Date in PST 2020-06-01
3 Search Console siteurl Website Property setup on Google Search Console https://electrik.ai/
4 Search Console clicks Number of Clicks on your website URL from a Google Search Results Page 1
5 Search Console impressions Number of times your website URL appeared in search results viewed by a user. 25
6 Search Console ctr Click Through Rate = Clicks / Impressions . Values range from 0 to 1.0, inclusive. 0.04
7 Search Console position Average Ranking of your website URLs for the query or queries. For example , if your site's URL appears at position 3 for one query and position 7 for another query, the average position would be 5 (3+7/2) 14.2
8 Electrik.AI etl_org Organization Id electr_1
9 Electrik.AI etl_project Project Id myfirs_132
10 Electrik.AI etl_start_utc_dttm EAI Flow Start Timestamp in UTC 2020-06-01 07:01:51.743
11 Electrik.AI etl_create_utc_dttm Row Create Timestamp in UTC 2020-06-01 07:03:54.687
12 Electrik.AI etl_update_utc_dttm Row Update Timestamp in UTC 2020-06-01 07:03:54.687
13 Electrik.AI etl_flow_nm EAI Flow Code FL000910
14 Electrik.AI etl_flow_instance_id EAI Flow Execution Instance Id FL000910-20200601070113070565

The Page Performance report includes all statistics aggregated at the web page level.

Index Category Electrik.AI Column Name Description Sample Value
1 Search Console pagepath Website Page URL https://electrik.ai/how-to-extract-google-analytics-data-to-microsoft-azure-sql-database
2 Search Console query The actual Search Query that triggered impressions azure google analytics
3 Search Console snapshot_dt Date in PST 2020-06-01
4 Search Console siteurl Website Property setup on Google Search Console https://electrik.ai/
5 Search Console clicks Number of Clicks on your website URL from a Google Search Results Page 1
6 Search Console impressions Number of times your website URL appeared in search results viewed by a user. 2
7 Search Console ctr Click Through Rate = Clicks / Impressions . Values range from 0 to 1.0, inclusive. 0.5
8 Search Console position Average Ranking of your website URLs for the query or queries. For example , if your site's URL appears at position 3 for one query and position 7 for another query, the average position would be 5 (3+7/2) 7
9 Electrik.AI etl_org Organization Id electr_1
10 Electrik.AI etl_project Project Id myfirs_132
11 Electrik.AI etl_start_utc_dttm EAI Flow Start Timestamp in UTC 2020-06-03 07:01:51.743
12 Electrik.AI etl_create_utc_dttm Row Create Timestamp in UTC 2020-06-03 07:03:54.687
13 Electrik.AI etl_update_utc_dttm Row Update Timestamp in UTC 2020-06-03 07:03:54.687
14 Electrik.AI etl_flow_nm EAI Flow Code FL000910
15 Electrik.AI etl_flow_instance_id EAI Flow Execution Instance Id FL000910-20200603070113070565

The Page Performance report includes all statistics aggregated at the web page level.

Index Category Electrik.AI Column Name Description Sample Value
1 Search Console snapshot_dt Date in PST 2020-06-08
2 Search Console siteurl Website Property setup on Google Search Console https://electrik.ai/
3 Search Console clicks Number of Clicks on your website URL from a Google Search Results Page 5
4 Search Console impressions Number of times your website URL appeared in search results viewed by a user. 204
5 Search Console ctr Click Through Rate = Clicks / Impressions . Values range from 0 to 1.0, inclusive. 0.024509804
6 Search Console position Average Ranking of your website URLs for the query or queries. For example , if your site's URL appears at position 3 for one query and position 7 for another query, the average position would be 5 (3+7/2) 39.181374
7 Electrik.AI etl_org Organization Id electr_1
8 Electrik.AI etl_project Project Id myfirs_132
9 Electrik.AI etl_start_utc_dttm EAI Flow Start Timestamp in UTC 2020-06-10 07:01:51.743
10 Electrik.AI etl_create_utc_dttm Row Create Timestamp in UTC 2020-06-10 07:03:54.687
11 Electrik.AI etl_update_utc_dttm Row Update Timestamp in UTC 2020-06-10 07:03:54.687
12 Electrik.AI etl_flow_nm EAI Flow Code FL000910
13 Electrik.AI etl_flow_instance_id EAI Flow Execution Instance Id FL000910-20200610070113070565

The Website Performance report includes all statistics aggregated at the site level.

Index Category Electrik.AI Column Name Description Sample Value
1 Search Console devicecategory The type of Device: Desktop,Mobile or Tablet desktop
2 Search Console country_iso3 The Country from which the Session Originated. ISO Format 3 USA
3 Electrik.AI country_iso2 The Country from which the Session Originated. ISO Format 2 US
4 Search Console snapshot_dt Date in PST 2020-06-05
5 Search Console siteurl Website Property setup on Google Search Console https://electrik.ai/
6 Search Console clicks Number of Clicks on your website URL from a Google Search Results Page 4
7 Search Console impressions Number of times your website URL appeared in search results viewed by a user. 43
8 Search Console ctr Click Through Rate = Clicks / Impressions . Values range from 0 to 1.0, inclusive. 0.093023255
9 Search Console position Average Ranking of your website URLs for the query or queries. For example , if your site's URL appears at position 3 for one query and position 7 for another query, the average position would be 5 (3+7/2) 30.418604
10 Electrik.AI etl_org Organization Id electr_1
11 Electrik.AI etl_project Project Id myfirs_132
12 Electrik.AI etl_start_utc_dttm EAI Flow Start Timestamp in UTC 2020-06-07 07:01:51.743
13 Electrik.AI etl_create_utc_dttm Row Create Timestamp in UTC 2020-06-07 07:03:54.687
14 Electrik.AI etl_update_utc_dttm Row Update Timestamp in UTC 2020-06-07 07:03:54.687
15 Electrik.AI etl_flow_nm EAI Flow Code FL000910
16 Electrik.AI etl_flow_instance_id EAI Flow Execution Instance Id FL000910-20200607070113070565