How to Connect Google Search Console to Snowflake?: 2 Easy Methods

A free Google service called Google Search Console helps companies with search engine optimization. It gives you a lot of data, such as the keywords and positions for which your website is ranked, the frequency with which users click on your result after typing in particular queries, and the websites that have linked to your content.

To support your business choices with data, you need real-time high-quality data from all of your data sources in a single repository. Traditional On-premise Data Warehouse solutions require constant maintenance and have a limited range of scalability and performance. A more affordable and instantly scalable solution with best-in-class query performance is Snowflake. With full ANSI SQL support for Data Analysis and Transformations, it is a one-stop shop for Cloud Data Warehousing and Analytics.

In this article, you will learn how to transfer data from Google Search Console to Snowflake using CSV files format. 

What is Google Search Console?

Google Search Console is an essential tool for anyone who owns a website and wants to increase organic traffic from Google Search. It provides you with detailed tips on how to improve your site in addition to performance data. Many analytics reports, including Mobile Usability, Core Web Vitals, Links Reports, and others, show performance. It enables you to submit both a sitemap and a newer page on your website for indexing, allowing Google to read fresh pages on your website on a regular basis and making them appear faster in search results.

Key Features of Google Search Console

• The Search Analytics reports in Search Console offer a comprehensive array of crucial insights regarding the Google search performance of your website. Go to “Search Traffic” and then “Search Analytics” in the menu on the left of the Search Console screen to check it out.
• You may add structured data to your website using the Data Highlighter, a very user-friendly and easy tool that informs Google what your content is and how it should be handled.
• No matter how meticulous the webmaster, there will always be a few aspects of a website that can be enhanced from a search engine’s point of view. The HTML Improvements tool clearly identifies the problems with your website and offers advice on how to address them.

What is Snowflake?

The world’s first cloud data warehouse, called Snowflake, was created using the infrastructure of the customer’s preferred Cloud provider (AWS, Azure, or GCP). Snowflake SQL has common Analytics and windowing features and adheres to the ANSI standard. There are some differences in Snowflake’s syntax, but there are also some similarities.

The integrated development environment (IDE) for Snowflake is entirely web-based. Navigate to XXXXXXXX.us-east-1.snowflakecomputing.com to interact with your instance. You will be taken to the main Online GUI, which also serves as the main IDE, after logging in, where you may start interacting with your data assets. Additionally, for convenience, the Snowflake interface refers to each query tab as a “Worksheet“. Like the tab history feature, these “Worksheets” are automatically preserved and accessible at any moment.

Key Features of Snowflake

The following are some of the features of Snowflake as a Software as a Service (SaaS) solution:

• Query Optimization: Snowflake can independently improve a query by clustering and partitioning data. 
• Secure Data Sharing: Data can be transferred securely between accounts by using Snowflake Database Tables, Views, and UDFs.
• Support for File Formats: Semi-structured data can be imported into Snowflake in file formats like JSON, Avro, ORC, Parquet, and XML. For storing semi-structured data, it has a column type called VARIANT.

Methods to Connect Google Search Console to Snowflake

Method 1: Connect Google Search Console to Snowflake using Hevo

Hevo helps you directly transfer data from 150+ sources such as Google Search Console to Snowflake, Database, Data Warehouses, or a destination of your choice in a completely hassle-free & automated manner. Hevo is fully managed and completely automates the process of not only loading data from your desired source but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. Its fault-tolerant architecture ensures that the data is handled in a secure, consistent manner with zero data loss.

Sign up here for a 14-Day Free Trial!

The following steps can be implemented to connect Google Search Console to Snowflake using Hevo:

Step 1: Configure Google Search Console as your Source

• Step 1.1: Click PIPELINES in the Asset Palette.
• Step 1.2: Click + CREATE in the Pipelines List View.
• Step 1.3: In the Select Source Type page, select Google Search Console.
• Step 1.4: In the Configure your Google Search Console Account page, Select an existing Google Search Console account or click + ADD GOOGLE SEARCH CONSOLE ACCOUNT.
  
• Step 1.5: Select the Google account linked to the Google Search Console data, and click ALLOW to authorize Hevo to read and ingest the data.
• Step 1.6: In the Configure your Google Search Console Source, specify the following:
  

1. Pipeline Name: A unique name for your Pipeline.
2. Properties: Select the properties/sites whose performance you want to analyze.

• Step 1.7: Click TEST & CONTINUE.
• Step 1.8: Proceed to configuring the data ingestion and setting up the Destination.

Step 2: Configure Snowflake as your Destination

To set up Snowflake as a destination in Hevo, follow these steps:

• Step 2.1: In the Asset Palette, select DESTINATIONS.
• Step 2.2: In the Destinations List View, click + CREATE.
• Step 2.3: Select Snowflake from the Add Destination page.
• Step 2.4: Set the following parameters on the Configure your Snowflake Destination page:
  • Destination Name: A unique name for your Destination.
  • Snowflake Account URL: This is the account URL that you retrieved.
  • Database User: The Hevo user that you created in the database. In the Snowflake database, this user has a non-administrative role.
  • Database Password: The password of the user.
  • Database Name: The name of the Destination database where data will be loaded.
  • Database Schema: The name of the Destination database schema. Default value: public.
  • Warehouse: SQL queries and DML operations are performed in the Snowflake warehouse associated with your database.

• Step 2.5: Click Test Connection to test connectivity with the Snowflake warehouse.
• Step 2.6: Once the test is successful, click SAVE DESTINATION.

Method 2: Connect Google Search Console to Snowflake Manually using CSV Files

You cannot directly export the data from Google Search Console to Snowflake. To export data from Google Search Console to Snowflake, first you will have to export data from Google Search Console as CSV files and then load the CSV files into Snowflake.

Step 1: Export Data from Google Search Console as CSV

The first step in exporting data from Google Search Console to Snowflake is exporting data from Google Search Console as CSV files.

• Step A: Login into your Google Search Console account.
• Step B: Choose the property from which you wish to export data.

• Step C: In the left menu, select Performance view.

• Step D: Choose the time period you want to examine.

• Step E: Click the Export icon in the top left corner and choose the format that you like (Google Sheets, CSV).

Now you have your CSV Data with you. The first step in exporting data from Google Search Console to Snowflake is complete now.

Step 2: Load CSV Data into Snowflake 

The second step in exporting data from Google Search Console to Snowflake is importing CSV data into Snowflake. 

This section explains how to use the SnowSQL client to bulk load data into Snowflake. Any delimited plain-text file, including Comma-delimited CSV files, can have data loaded in bulk using SQL. Semi-structured data from JSON, AVRO, Parquet, or ORC files can also be bulk loaded. But the article’s major focus is loading from CSV files. In Snowflake, you can stage files on what are known as stages on the inside. Each customer and every table gets its own stage. Snowflake also makes it possible to create named levels, such as demo stages.

• Step A: Upload your data files in order for Snowflake to access them. This essentially amounts to file staging.
  • Internal stages provide convenient and secure data file storage without employing any external resources. If your data files are already staged in a compatible Cloud storage like GCS or S3, you can bypass staging and load directly from these external locations.
  • Furthermore, you may just upload CSV files from your computer.
• Step B:  After that, you import your data into your tables from these ready-to-use files. The previously constructed database will be picked using the “use” line.

Syntax: 

Use database [database-name];   

Example: 

use database dezyre_test;

Output:

• Step C: This step involves creating a named file format that can be read or loaded into Snowflake tables for a group of staged data.

Syntax: 

CREATE [ OR REPLACE ] FILE FORMAT [ IF NOT EXISTS ] 
                      TYPE = { CSV | JSON | AVRO | ORC | PARQUET | XML } [ formatTypeOptions ]
                      [ COMMENT = '' ]

Example: 

create or replace file format my_csv_format
  type = csv
  field_delimiter = '|'
  skip_header = 1
  null_if = ('NULL', 'null')
  empty_field_as_null = true
  compression = gzip;

Output:

• Step D: The Construct statement is now used to create a table, as shown below. In the current or provided schema, it either creates a new table or modifies an existing one.

Syntax: 

CREATE [ OR REPLACE ] TABLE  [ (  [  ] ,  [  ] , ... ) ] ;

Example: 

CREATE OR REPLACE TABLE dezyre_employees (
EMPLOYEE_ID number,
FIRST_NAME varchar(25),
LAST_NAME varchar(25),
EMAIL varchar(25),
PHONE_NUMBER varchar(15),
HIRE_DATE DATE,
JOB_ID varchar(15),
SALARY  number(12,2),
COMMISSION_PCT  real,
MANAGER_ID number,
DEPARTMENT_ID number
);

Output:

• Step E: As shown below, upload the CSV data file using your local computer to the Snowflake’s staging area. Along with the URLs for the locations of the staged CSV files, you may also specify the access credentials if the destination is secured. Additionally, you can make named stages that point to various places.

Syntax: 

put file://D:\dezyre_emp.csv @DEMO_DB.PUBLIC.%dezyre_employees;

Output:

• Step F: The CSV data is now loaded into the target Snowflake table that was previously established, as can be seen in the image below.

Example: 

copy into dezyre_employees
  from @%dezyre_employees
  file_format = (format_name = 'my_csv_format' , error_on_column_count_mismatch=false)
  pattern = '.*dezyre_emp.csv.gz'
  on_error = 'skip_file';

Output:

• Step G: By running the select query indicated below, you can check to see if the data that was put into the target database is accurate.

Example: 

select * from dezyre_employees;

Output:

You have successfully done Google Search Console to Snowflake data transfer.

Limitations of Connecting Google Search Console to Snowflake Manually

• Data may only be transmitted from Google Search Console to Snowflake in one direction. In order to maintain both tools up to date, two-way sync is required.
• The manual process takes time because the records need to be updated often. This is a waste of time and resources that could be used for more crucial company duties.
• Some customers may find the amount of engineering bandwidth needed to maintain workflows across numerous platforms and update current data bothersome.
• No transformation is possible during data transport. This could be a big problem for companies that wish to edit their data before moving it from Google Search Console to Snowflake.

Conclusion  

In this article, you got a glimpse of how to connect Google Search Console to Snowflake after a brief introduction to the salient features, and use cases. The methods talked about in this article are using automated solutions such as Hevo and CSV files. The second process can be a bit difficult for beginners. Moreover, you will have to update the data each and every time it is updated and this is where Hevo saves the day!

Visit our Website to Explore Hevo

Hevo Data provides its users with a simpler platform for integrating data from 150+ sources for Analysis. It is a No-code Data Pipeline that can help you combine data from multiple sources like Google Search Console. You can use it to transfer data from multiple data sources into your Data Warehouses, Database, or a destination of your choice such as Snowflake. It provides you with a consistent and reliable solution to managing data in real-time, ensuring that you always have Analysis-ready data in your desired destination.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.

Share your experience of learning about Google Search Console to Snowflake! Let us know in the comments section below!