Connect Snowflake to Google Ads: 3 Easy Methods

on Activate, Data Integration, Data Warehouses, Google Ads, Hevo Activate, Reverse ETL, Snowflake, Snowflake • June 15th, 2022 • Write for Hevo

snowflake to google ads: FI

Google Ads provides paid advertisements that appear in search results on google.com, as well as advertisements that appear on other websites via the Display Network and Google’s AdSense program.

Snowflake’s Data Cloud is built on a cutting-edge data platform that is delivered as Software-as-a-Service (SaaS). Snowflake provides Data Storage, Processing, and Analytic Solutions that are Faster, Easier to Use, and more Flexible than traditional options.

This article discusses the different methods to Connect Snowflake to Google Ads(Coming Soon For Hevo Activate!). In addition to that, it gives a brief introduction to Google Ads and Snowflake.

Table of Contents

What is Snowflake?

snowflake to google ads: snowflake logo
Image Source

Snowflake is a fully managed SaaS (Software as a Service) that combines Data Warehousing, Data Lakes, Data Engineering, Data Science, Data Application Development, and Secure Sharing and Consumption of Real-time / Shared Data into a single platform. To meet the demanding needs of growing businesses, Snowflake includes out-of-the-box features such as Storage and Compute Separation, On-the-fly Scalable Compute, Data Sharing, Data Cloning, and third-party Tool Support.

Snowflake isn’t based on any existing database technology or “Big Data” software platforms like Hadoop. Snowflake, on the other hand, combines a brand-new SQL query engine with cutting-edge Cloud Architecture. Snowflake gives users all of the features and capabilities of an enterprise analytic database, plus a lot more.

Key Features of Snowflake

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

  • Snowflake enables you to enhance your Analytics Pipeline by transitioning from nightly Batch Loads to Real-time Data Streams, allowing you to improve the quality and speed of your analytics.
  • Snowflake uses the Caching Paradigm to swiftly deliver the results from the cache.
  • Snowflake allows you to get rid of silos and ensure access to meaningful insights across the enterprise, resulting in better Data-driven Decision-Making. This is a crucial first step toward bettering partner relationships, optimizing pricing, lowering operational expenses, increasing sales effectiveness, and more.
  • Snowflake allows you to better analyze Customer Behaviour and Product Usage.
  • Snowflake allows you to create your own Data Exchange, which allows you to securely Communicate Live, Controlled Data.

What is Google Ads?

snowflake to google ads: google ads logo
Image Source

Google Ads is a Google-developed online advertising platform where advertisers can bid to have brief Advertisements, Service Offerings, Product Listings, or Videos displayed to web users. It can place ads in Search Engine Results, such as Google Search, as well as on Non-search Websites, Mobile Apps, and Videos. The services are charged on a pay-per-click (PPC) basis.

Part of the Google Ads system is based on cookies, and part is based on keywords chosen by advertisers. These characteristics are used by Google to place ad copy on pages that they believe are relevant. Advertisers are compensated when users divert their browsing habits to click on the ad copy. Advertisements can be run on a local, national, or global scale.

Key Features of Google Ads

The fact is that Google generates millions of search queries in a day. It means it has comprehensive resources and can drive more traffic. Using Google Ads for advertising has multiple benefits. A few features of Google Ads are listed below:

  • Adjustable Bidding: As per your budget, you can decide on bidding as well as adjusting the Ads. Based on the previous users, you can analyze which products show high User Interaction and Conversion Rates. Accordingly, you can change the Ads spend to winning products. 
  • Flexible Campaign Settings: In Google Ads, you can set your Ads Campaigns as per your convenience. Several types of Ads Campaigns are available that you can use for advertising your products or services.
  • Ads Targeting: Google Ads features Ads targeting where you can run Ads Campaigns as per your audience’s demographics. You can select the target device option while running the Ads.
  • To learn more about Google Ads, click here

Explore Methods to Connect Snowflake to Google Ads

Snowflake to Google Ads: Manual CSV Upload

This method involves manually converting your Snowflake data into CSV Files using certain SQL commands. The data present in the CSV Files then has to be modified according to the format specified by Google Ads. Afterward, the data can be imported and mapped directly into Google Ads. It is a 12-step process that will also require troubleshooting certain errors manually.

Snowflake to Google Ads: Using APIs

This method allows you to connect Snowflake to Google ads using APIs. It is suitable for someone having good knowledge of API Integration.

Snowflake to Google Ads: Using Hevo Activate

Hevo Activate provides a hassle-free, one-stop solution to Connect Snowflake to Google Ads(Coming Soon For Hevo Activate!) in an effortless manner. Hevo Activate syncs customer /product data in the warehouse to your Business Application. With Hevo Activate you can bring the data to fingertips of your business teams, where they need it the most – Business Applications. Teams can now make faster, smarter actions by analyzing the user journeys, and creating personalized experiences with Hevo Activate.

GET STARTED WITH HEVO ACTIVATE FOR FREE

Ways to Connect Snowflake to Google Ads

Snowflake to Google Ads: Manual CSV Upload

This method is used to manually connect Snowflake to Google ads. When it comes to uploading data to Google Ads, you must follow a very specific format. The process is as follows:

  • Step 1: Log in to your account on Google Ads.
  • Step 2: In the Navigation bar, click the Tools and Settings button.
  • Step 3: Select Audience Manager from the dropdown menu.
  • Step 4: Select Segments from the left-hand menu.
  • Step 5: Create a Remarketing List by clicking the + button.
  • Step 6: Select the Customer List from the dropdown menu.
  • Step 7: You can select which type of data to upload in the Data to Upload section. You’ll choose the first option, Upload Emails, Phone Numbers, and/or Addresses. You can then download a template that will show you exactly how to format your upload file. If you want to upload email addresses, for example, you’ll need a CSV file with only one column called Email and nothing else.
  • Step 8: You can export the data from Snowflake now that you know exactly what you need. Go to the Snowflake Web Interface and create a query that meets the template’s requirements. It’s as simple as the query below in your case.
SELECT C_EMAIL_ADDRESS AS "Email"
FROM "SNOWFLAKE_SAMPLE_DATA"."TPCDS_SF100TCL"."CUSTOMER";
  • Step 9: Once your query has been completed, click the Download button. Then return to Google Ads and upload your file there. A confirmation screen will appear if everything went well.

Snowflake to Google Ads: Using APIs

Snowflake and Google Ads APIs are used in this automated method. You’ll demonstrate how to set up the export/import using Python, but you can use any language you want (e.g. Java). To use the Snowflake API, all you need are your “normal” Snowflake Credentials that you use to log into the Web UI.

Following are the steps to follow to Connect Snowflake to Google Ads using APIs:

  • Step 1: You’ll begin by exporting the data from Snowflake once you’ve completed the API Setup. Install the libraries now if you’re following along with Python and haven’t already.
pip install snowflake-connector-python google-ads
  • Step 2: Next, you’ll need to import the Snowflake Connector.
import snowflake.connector
  • Step 3: You’ll need the same Username, Password, and Account you used to log into the Web UI to export data from Snowflake. Your account name can be found in the URL https://your-account>.snowflakecomputing.com if you use the classic Snowflake Web UI. Create a Connection Object with the following code using these:
conn = snowflake.connector.connect(
    user = "your-username",
    password = "your-password",
    account = "your-account"
    )
  • Step 4: Your data is now ready to be queried. You only need to retrieve email addresses, so your query is simple.
cursor = conn.cursor().execute("""
    SELECT C_EMAIL_ADDRESS AS "Email"
    FROM "SNOWFLAKE_SAMPLE_DATA"."TPCDS_SF100TCL"."CUSTOMER";
""")
  • Step 5: Your data is stored in the cursor as a list of Tuples, which we can retrieve with fetchall. It also contains the column names, which you don’t need for your particular use case. 
data = [i[0] for i in cursor.fetchall()]
columns = [i[0] for i in cursor.description]
  • Step 6: If you’ve gotten all of the information you need, you can Disconnect from Snowflake.
conn.close()
  • Step 7: Unfortunately, using the Python Client Library to import our data into Google Ads is just as difficult as getting access to the API. In the end, you modified this example to fit our needs.
from google.ads.googleads.client import GoogleAdsClient
from google.ads.googleads.errors import GoogleAdsException
import hashlib
  • Step 8: You’ll require a Customer. There are several ways to create one, but you can use the following method to get started:
client = GoogleAdsClient.load_from_storage()
  • Step 9: Now is the time to create a segment list if you haven’t already. You tweaked this function a little bit to make it more useful.
def _create_customer_match_user_list(client, customer_id, list_name, list_description, membership_life_span):
    """Creates a Customer Match user list.
    Args:
        client: The Google Ads client.
        customer_id: The Google Ads customer ID for which to add the user list.
        list_name: The name to give to the user list.
        list_description: The description of the user list.
        membership_life_span: Number of days a user should remain on the list.
    Returns:
        The string resource name of the newly created user list.
    """
    # Creates the UserListService client.
    user_list_service_client = client.get_service("UserListService")

    # Creates the user list operation.
    user_list_operation = client.get_type("UserListOperation")

    # Creates the new user list.
    user_list = user_list_operation.create
    user_list.name = list_name
    user_list.description = list_description
    user_list.crm_based_user_list.upload_key_type = (client.enums.CustomerMatchUploadKeyTypeEnum.CONTACT_INFO)
    user_list.membership_life_span = membership_life_span

    response = user_list_service_client.mutate_user_lists(customer_id=customer_id, operations=[user_list_operation])
    user_list_resource_name = response.results[0].resource_name
    print(f"User list with resource name '{user_list_resource_name}' was created.")

    return user_list_resource_name
    
    
user_list_resource_name = _create_customer_match_user_list(client, 'your-google-ads-customer-id', 'Census - demo', 'A demo by Census', 30)
  • Step 10: If everything went well, the Variable User List Resource name would be customers/123456789/userLists/987654321, where 123456789 is your Google Ads Customer ID and 987654321 is the ID of your Segment. The Web UI is the quickest way to find the ID if you already have a Segment List. In the Navigation bar, next to the account’s name, you’ll find your Google Ads Customer ID. Navigate to the segment to which you want to add users and look for the query parameter userListId in the URL. Create the User List Resource Name string variable by manually entering these values.
  • Step 11: Now it’s time to start adding users. You’ve made some minor changes to the example code once more. You’ve passed emails as a parameter to _add users to the Customer Match User List and looped through them to add operations to request.operations. In _build offline User Data Job Operations, you’ve also added Email as a parameter.
  • Step 12: Before uploading, Google Ads requires that you hash your customers’ email addresses using SHA-256. This function handles it, as well as cleaning the email addresses. Even a single character change will result in a completely different hash, making it impossible for Google Ads to match the email address of one of its users. If you have reason to believe that your data requires more cleaning than what this function provides, now is the time to take action.
def _normalize_and_hash(s):
    """Normalises and hashes a string with SHA-256.
    Args:
        s: The string to perform this operation on.
    Returns:
        A normalised (lowercase, remove whitespace) and SHA-256 hashed string.
    """
    return hashlib.sha256(s.strip().lower().encode()).hexdigest()
  • Step 13: It’s ideal if you can comprehend the following two functions and modify them to suit your needs, but don’t worry if you can’t. To put it another way, it creates the operations that will add the hashed email addresses to your segment.
def _build_offline_user_data_job_operations(client, email):
    """Builds and returns offline user data job operations.
    Args:
        client: The Google Ads client.
        email: A single, unhashed email address.
    Returns:
        A list containing the operations.
    """
    # Creates the first user data based on an email address.
    user_data_with_email_address_operation = client.get_type(
        "OfflineUserDataJobOperation"
    )
    user_data_with_email_address = user_data_with_email_address_operation.create
    user_identifier_with_hashed_email = client.get_type("UserIdentifier")
    # Hash normalised email addresses based on SHA-256 hashing algorithm.
    user_identifier_with_hashed_email.hashed_email = _normalize_and_hash(
        email
    )
    user_data_with_email_address.user_identifiers.append(
        user_identifier_with_hashed_email
    )

    return [user_data_with_email_address_operation]
    
def _add_users_to_customer_match_user_list(
client, customer_id, user_list_resource_name, skip_polling, emails
):
    """Uses Customer Match to create and add users to a new user list.
    Args:
        client: The Google Ads client.
        customer_id: The customer ID for which to add the user list.
        user_list_resource_name: The resource name of the user list to which to
            add users.
        skip_polling: A bool dictating whether to poll the API for completion.
    """
    # Creates the OfflineUserDataJobService client.
    offline_user_data_job_service_client = client.get_service(
        "OfflineUserDataJobService"
    )

    # Creates a new offline user data job.
    offline_user_data_job = client.get_type("OfflineUserDataJob")
    offline_user_data_job.type_ = (
        client.enums.OfflineUserDataJobTypeEnum.CUSTOMER_MATCH_USER_LIST
    )
    offline_user_data_job.customer_match_user_list_metadata.user_list = (
        user_list_resource_name
    )

    # Issues a request to create an offline user data job.
    create_offline_user_data_job_response = (
        offline_user_data_job_service_client.create_offline_user_data_job(
            customer_id=customer_id, job=offline_user_data_job
        )
    )
    offline_user_data_job_resource_name = (
        create_offline_user_data_job_response.resource_name
    )
    print(
        "Created an offline user data job with resource name: "
        f"'{offline_user_data_job_resource_name}'."
    )

    request = client.get_type("AddOfflineUserDataJobOperationsRequest")
    request.resource_name = offline_user_data_job_resource_name
    request.operations = []
    
    for email in emails:
        operation = _build_offline_user_data_job_operations(client, email)
        request.operations.extend(operation)
    
    request.enable_partial_failure = True

    # Issues a request to add the operations to the offline user data job.
    response = offline_user_data_job_service_client.add_offline_user_data_job_operations(
        request=request
    )

    # Prints the status message if any partial failure error is returned.
    # Note: the details of each partial failure error are not printed here.
    # Refer to the error_handling/handle_partial_failure.py example to learn
    # more.
    # Extracts the partial failure from the response status.
    partial_failure = getattr(response, "partial_failure_error", None)
    if getattr(partial_failure, "code", None) != 0:
        error_details = getattr(partial_failure, "details", [])
        for error_detail in error_details:
            failure_message = client.get_type("GoogleAdsFailure")
            # Retrieve the class definition of the GoogleAdsFailure instance
            # in order to use the "deserialize" class method to parse the
            # error_detail string into a protobuf message object.
            failure_object = type(failure_message).deserialize(
                error_detail.value
            )

            for error in failure_object.errors:
                print(
                    "A partial failure at index "
                    f"{error.location.field_path_elements[0].index} occurred.n"
                    f"Error message: {error.message}n"
                    f"Error code: {error.error_code}"
                )

    print("The operations are added to the offline user data job.")

    # Issues an request to run the offline user data job for executing all
    # added operations.
    operation_response = (
        offline_user_data_job_service_client.run_offline_user_data_job(
            resource_name=offline_user_data_job_resource_name
        )
    )

    if skip_polling:
        _check_job_status(
            client,
            customer_id,
            offline_user_data_job_resource_name,
            user_list_resource_name,
        )
    else:
        # Wait until the operation has finished.
        print("Request to execute the added operations started.")
        print("Waiting until operation completes...")
        operation_response.result()
        _print_customer_match_user_list_info(
            client, customer_id, user_list_resource_name
        )
        # [END add_customer_match_user_list]
  • Step 14: After that, the code below activates the Proverbial Red button, causing your Customers’ Email Addresses to be uploaded.
upload = _add_users_to_customer_match_user_list(client, 'your-google-ads-id', user_list_resource_name, False, data)

The output should show that the operation is currently in progress, which will take some time.

Snowflake to Google Ads: Using Hevo Activate

All the above mentioned drawbacks can be overcome using an Automated Reverse-ETL Solution like Hevo Activate. 

With Hevo Activate, you can empower business teams with 360° customer profiling. Sync customer and product usage data that are available in Data Warehouses, such as Snowflake to CRMs like Hubspot and Business Analytics Solutions like Google Ads (Coming Soon for Hevo Activate!). in a hassle-free & effortless manner.

GET STARTED WITH HEVO ACTIVATE FOR FREE

Instead of juggling multiple BI tools for insight, Hevo Activate enables the seamless transfer of data to all the business applications. Teams can use Hevo Activate to generate faster and more accurate results. Hevo Activate enables you to create personalized experiences for your customers. 

Try our 14-day full access free trial today to experience an entirely automated hassle-free Data Replication!

Here’s how Hevo Activate is different:

  • Real-Time Data Sync: Hevo Activate, with its strong integration with various data sources, allows you to transfer data quickly & efficiently. This ensures efficient utilization of bandwidth on both ends.
  • No APIs, CSVs, Just SQL!: Use the powerful and smart SQL Query Editor to filter and transform data from your data warehouse table to a Target object. The in-built validation checks ensure each query is validated before you save it.
  • Secure: Hevo Activate has a fault-tolerant architecture that ensures that the data is handled safely and consistently with zero data loss.
  • On-Demand Sync: Hevo Activate gives users the option to resume the sync or run sync now to execute data sync as per their demand.
  • Intelligent Data Type Conversion: During the mapping action, Hevo Activate transforms the field types of the synced data automatically. 
  • Data Transformation: Hevo Activate provides a simple interface to perfect, modify, and enrich the data you want to transfer. 
  • Live Support: The Hevo Activate team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.

Conclusion

This article describes the ways to Connect Snowflake to Google Ads(Coming Soon For Hevo Activate!) in a few easy steps. It also gives an overview of Snowflake and Google Ads.

VISIT OUR WEBSITE TO EXPLORE HEVO ACTIVATE

With Hevo Activate all these processes can be eliminated and seamlessly integrated within the Hevo dashboard. It allows you to immediately transfer data from a source of your choosing, such as Snowflake, Amazon Redshift, and so on, to any SaaS application, CRM like HubSpot, Google Ads(Coming Soon For Hevo Activate!), and so on, in a fully automated and safe manner for free.

Want to take Hevo Activate for a spin?

SIGN UP 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.

Sync your data to Google Ads Seamlessly