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

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