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

App Dashboard-Electrik.AI

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

Select the Connections Icon in the left most panel and Click on “Add” button the top-Electrik.AI
Source connections-Electrik.AI

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

Click on “Google Search Console” icon to create a Google Search Console connection-Electrik.AI

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

Enter your Google Search Console User Id and Password-Electrik.AI
Enter-your-password-Electrik.AI

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

Allow Electrik.AI to view Google Search Console Data for your verified sites-Electrik.AI

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.

Choose your Site URL, for which you want to Extract Search Analytics Report-Electrik.AI

Step 9: Click on Save button on the top.

Click on Save button on the top-Electrik.AI

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

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

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

App Dashboard-Electrik.AI

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

Search Analytics Report Extractor Flow-Electrik.AI

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

Select Google Search Console Icon-Electrik.AI

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

Select your existing Google Search Console connection-Electrik.AI

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

Choosing Azure SQL-Electrik.AI

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

Select your existing Azure SQL connection-Electrik.AI

Step 9: Review the Data Source, Data Destination, Database Schema, Select Report Name, Provide Success Notification Email, Failure Notification Email and Click “Save” to continue.
Report Name: Electrik.AI now supports 5 reports, choose any one of them to get the appropriate data.
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.

Use Existing Schema-Electrik.AI

Database Schema : Electrik.AI creates a new Database Schema to load your Search Analytics Report.
You can also choose an existing schema in your database by selecting the “Use Existing Schema” radio button.

Review the Data Source, Data Destination, Database Schema-Electrik.AI

Step 10: Please wait while you are redirected to Flow Details Screen.
Step 11: Congratulations, you have now successfully setup Google Search Console Data Export to Azure SQL Database.

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

Page Performance

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

IndexCategoryElectrik.AI Column Name Description        Sample Value
1Search ConsolepagepathWebsite Page URLhttps://electrik.ai/how-to-extract-google-analytics-data-to-microsoft-azure-sql-database
2Search Consolesnapshot_dtDate in PST2020-06-01
3Search ConsolesiteurlWebsite Property setup on Google Search Consolehttps://electrik.ai/
4Search ConsoleclicksNumber of Clicks on your website URL from a Google Search Results Page1
5Search ConsoleimpressionsNumber of times your website URL appeared in search results viewed by a user.25
6Search ConsolectrClick Through Rate = Clicks / Impressions . Values range from 0 to 1.0, inclusive.0.04
7Search ConsolepositionAverage 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
8Electrik.AIetl_orgOrganization Idelectr_1
9Electrik.AIetl_projectProject Idmyfirs_132
10Electrik.AIetl_start_utc_dttmEAI Flow Start Timestamp in UTC2020-06-01 07:01:51.743
11Electrik.AIetl_create_utc_dttmRow Create Timestamp in UTC2020-06-01 07:03:54.687
12Electrik.AIetl_update_utc_dttmRow Update Timestamp in UTC2020-06-01 07:03:54.687
13Electrik.AIetl_flow_nmEAI Flow CodeFL000910
14Electrik.AIetl_flow_instance_idEAI Flow Execution Instance IdFL000910-20200601070113070565

Query Performance by Page

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

IndexCategoryElectrik.AI Column Name Description        Sample Value
1Search ConsolepagepathWebsite Page URLhttps://electrik.ai/how-to-extract-google-analytics-data-to-microsoft-azure-sql-database
2Search ConsolequeryThe actual Search Query that triggered impressionsazure google analytics
3Search Consolesnapshot_dtDate in PST2020-0601
4Search ConsolesiteurlWebsite Property setup on Google Search Consolehttps://electrik.ai/
5Search ConsoleclicksNumber of Clicks on your website URL from a Google Search Results Page1
6Search ConsoleimpressionsNumber of times your website URL appeared in search results viewed by a user.2
7Search ConsolectrClick Through Rate = Clicks / Impressions . Values range from 0 to 1.0, inclusive.0.5
8Search ConsolepositionAverage 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
9Electrik.AIetl_orgOrganization Idelectr_1
10Electrik.AIetl_projectProject Idmyfirs_132
11Electrik.AIetl_start_utc_dttmEAI Flow Start Timestamp in UTC2020-0603 07:01:51.743
12Electrik.AIetl_create_utc_dttmRow Create Timestamp in UTC2020-0603 07:03:54.687
13Electrik.AIetl_update_utc_dttmRow Update Timestamp in UTC2020-0603 07:03:54.687
14Electrik.AIetl_flow_nmEAI Flow CodeFL000910
15Electrik.AIetl_flow_instance_idEAI Flow Execution Instance IdFL000910-20200603070113070565

Website Performance

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

IndexCategoryElectrik.AI Column Name Description        Sample Value
1Search Consolesnapshot_dtDate in PST2020-06-08
2Search ConsolesiteurlWebsite Property setup on Google Search Consolehttps://electrik.ai/
3Search ConsoleclicksNumber of Clicks on your website URL from a Google Search Results Page5
4Search ConsoleimpressionsNumber of times your website URL appeared in search results viewed by a user.204
5Search ConsolectrClick Through Rate = Clicks / Impressions . Values range from 0 to 1.0, inclusive.0.024509804
6Search ConsolepositionAverage 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
7Electrik.AIetl_orgOrganization Idelectr_1
8Electrik.AIetl_projectProject Idmyfirs_132
9Electrik.AIetl_start_utc_dttmEAI Flow Start Timestamp in UTC2020-06-10 07:01:51.743
10Electrik.AIetl_create_utc_dttmRow Create Timestamp in UTC2020-06-10 07:03:54.687
11Electrik.AIetl_update_utc_dttmRow Update Timestamp in UTC2020-06-10 07:03:54.687
12Electrik.AIetl_flow_nmEAI Flow CodeFL000910
13Electrik.AIetl_flow_instance_idEAI Flow Execution Instance IdFL000910-20200610070113070565

Query Performance

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

IndexCategoryElectrik.AI Column Name Description        Sample Value
1Search ConsolequeryThe actual Search Query that triggered impressionsazure google analytics
2Search Consolesnapshot_dtDate in PST2020-06-01
3Search ConsolesiteurlWebsite Property setup on Google Search Consolehttps://electrik.ai/
4Search ConsoleclicksNumber of Clicks on your website URL from a Google Search Results Page1
5Search ConsoleimpressionsNumber of times your website URL appeared in search results viewed by a user.2
6Search ConsolectrClick Through Rate = Clicks / Impressions . Values range from 0 to 1.0, inclusive.0.5
7Search ConsolepositionAverage 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
8Electrik.AIetl_orgOrganization Idelectr_1
9Electrik.AIetl_projectProject Idmyfirs_132
10Electrik.AIetl_start_utc_dttmEAI Flow Start Timestamp in UTC2020-06-03 07:01:51.743
11Electrik.AIetl_create_utc_dttmRow Create Timestamp in UTC2020-06-03 07:03:54.687
12Electrik.AIetl_update_utc_dttmRow Update Timestamp in UTC2020-06-03 07:03:54.687
13Electrik.AIetl_flow_nmEAI Flow CodeFL000910
14Electrik.AIetl_flow_instance_idEAI Flow Execution Instance IdFL000910-20200603070113070565

Device Performance

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

IndexCategoryElectrik.AI Column Name Description        Sample Value
1Search ConsoledevicecategoryThe type of Device: Desktop,Mobile or Tabletdesktop
2Search Consolecountry_iso3The Country from which the Session Originated. ISO Format 3USA
3Electrik.AIcountry_iso2The Country from which the Session Originated. ISO Format 2US
4Search Consolesnapshot_dtDate in PST2020-06-05
5Search ConsolesiteurlWebsite Property setup on Google Search Consolehttps://electrik.ai/
6Search ConsoleclicksNumber of Clicks on your website URL from a Google Search Results Page4
7Search ConsoleimpressionsNumber of times your website URL appeared in search results viewed by a user.43
8Search ConsolectrClick Through Rate = Clicks / Impressions . Values range from 0 to 1.0, inclusive.0.093023255
9Search ConsolepositionAverage 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
10Electrik.AIetl_orgOrganization Idelectr_1
11Electrik.AIetl_projectProject Idmyfirs_132
12Electrik.AIetl_start_utc_dttmEAI Flow Start Timestamp in UTC2020-06-07 07:01:51.743
13Electrik.AIetl_create_utc_dttmRow Create Timestamp in UTC2020-06-07 07:03:54.687
14Electrik.AIetl_update_utc_dttmRow Update Timestamp in UTC2020-06-07 07:03:54.687
15Electrik.AIetl_flow_nmEAI Flow CodeFL000910
16Electrik.AIetl_flow_instance_idEAI Flow Execution Instance IdFL000910-20200607070113070565