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

on google search console, Snowflake • July 14th, 2022 • Write for Hevo

Google Search Console to Snowflake_FI

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. 

Table of Contents

What is Google Search Console?

Google Search Console to Snowflake: GSC logo
Image Source

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?

Google Search Console to Snowflake: Snowflake logo
Image Source

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.

Reliably integrate data with Hevo’s Fully Automated No Code Data Pipeline

If yours is anything like the 1000+ data-driven companies that use Hevo more than 70% of the business apps you use are SaaS applications. Integrating the data from these sources in a timely way is crucial to fuel analytics and the decisions that are taken from it. But given how fast API endpoints etc can change, creating and managing these pipelines can be a soul-sucking exercise.

Hevo’s no-code data pipeline platform lets you connect over 150+ sources in a matter of minutes to deliver data in near real-time to your warehouse. What’s more, the in-built transformation capabilities and the intuitive UI means even non-engineers can set up pipelines and achieve analytics-ready data in minutes. 

All of this combined with transparent pricing and 24×7 support makes us the most loved data pipeline software in terms of user reviews.

Take our 14-day free trial to experience a better way to manage data pipelines.

Get started for Free with Hevo!

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.
    GSC 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:
    Google Search Console Settings
  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.

facebook page insights to snowflake: configure snowflake as destination

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

Deliver smarter, faster insights with your unified data

Using manual scripts and custom code to move data into the warehouse is cumbersome. Changing API endpoints and limits, ad-hoc data preparation and inconsistent schema makes maintaining such a system a nightmare. Hevo’s reliable no-code data pipeline platform enables you to set up zero-maintenance data pipelines that just work.

  • Wide Range of Connectors: Instantly connect and read data from 150+ sources including SaaS apps and databases, and precisely control pipeline schedules down to the minute.
  • In-built Transformations:  Format your data on the fly with Hevo’s preload transformations using either the drag-and-drop interface, or our nifty python interface. Generate analysis-ready data in your warehouse using Hevo’s Postload Transformation 
  • Near Real-Time Replication:  Get access to near real-time replication for all database sources with log based replication. For SaaS applications, near real time replication is subject to API limits.   
  • Auto-Schema Management:  Correcting improper schema after the data is loaded into your warehouse is challenging. Hevo automatically maps source schema with destination warehouse so that you don’t face the pain of schema errors.
  • Transparent Pricing:  Say goodbye to complex and hidden pricing models. Hevo’s Transparent Pricing brings complete visibility to your ELT spend. Choose a plan based on your business needs. Stay in control with spend alerts and configurable credit limits for unforeseen spikes in data flow.
  • 24×7 Customer Support: With Hevo you get more than just a platform, you get a partner for your pipelines. Discover peace with round the clock “Live Chat” within the platform. What’s more, you get 24×7 support even during the 14-dayfree trial.
  • Security: Discover peace with end-to-end encryption and compliance with all major security certifications including HIPAA, GDPR, SOC-2.
Get started for Free with Hevo!

Get Started for Free with Hevo’s 14-day Free Trial.

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.
Google Search Console to Snowflake: Selecting Property
Image Source
  • Step C: In the left menu, select Performance view.
Google Search Console to Snowflake: Performance Review
Image Source
  • Step D: Choose the time period you want to examine.
Google Search Console to Snowflake: Performance tab
Image Source
  • Step E: Click the Export icon in the top left corner and choose the format that you like (Google Sheets, CSV).
Google Search Console to Snowflake: Download CSV
Image Source

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:

Google Search Console to Snowflake: O/P 1
Image Source
  • 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:

Google Search Console to Snowflake: O/P 2
Image Source
  • 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:

Google Search Console to Snowflake: O/P 3
Image Source
  • 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:

Google Search Console to Snowflake: O/P 4
Image Source
  • 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:

Google Search Console to Snowflake: O/P 5
Image Source
  • 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:

Google Search Console to Snowflake: O/P 6
Image Source

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!

No-code Data Pipeline For Snowflake