Facebook Ads to Snowflake: 2 Hassle-Free Ways to Connect

Unlock the full potential of your Facebook Ads data by integrating it seamlessly with Snowflake. With Hevo’s automated pipeline, get data flowing effortlessly—watch our 1-minute demo below to see it in action!

This article will provide you two major pathways for setting up your Facebook Ads to Snowflake integration. Your Facebook Ads generate a lot of transactional data about the performance of the Marketing campaigns by the minute. Pushing this data to a centralized Data Warehouse like Snowflake increases your ability to analyze the data in-depth, compare campaigns, run forecasts, and make predictions.

This article will first introduce you to Facebook Ads and Snowflake and then will elaborate on the two approaches of their integration. Furthermore, it will discuss the challenges that you may face when implementing the manual ETL approach. Read along to know the step-by-step integration of Facebook Ads to Snowflake.

Prerequisites

• Working knowledge of Databases and Data Warehouses.
• A Snowflake account.
• A Facebook account.
• Clear idea regarding what data is to be transferred.
• Working Knowledge of SQL.

Methods to Set up Facebook Ads to Snowflake Integration

Method 1: Transfer Facebook Ad Data to Snowflake with Hevo

You can implement the following two methods to set up your Facebook Ads to Snowflake Integration:

Hevo helps replicate your Facebook Ads data into Snowflake using the Facebook Marketing API. This is one of the best and fastest ways to connect Facebook ads to Snowflake and conduct deep data analysis.

However, for this, you must authorize Hevo to access data from your Facebook Ads account.

And you need to meet the following prerequisites to transfer data from Facebook ads to Snowflake:

1. An active Facebook (FB) account from which to ingest data to Snowflake.
2. Adequate access to the Facebook Ads console and related statistics.
3. Assigned roles, such as Team Administrator, Team Collaborator, or Pipeline Administrator, in Hevo to create the Pipeline.

Step #1: Configure Facebook Ads as a Source

Follow the steps below to configure Facebook ads as the Source in your Pipeline to ingest data from Facebook ads to Snowflake:

1. From the Navigation Bar, click PIPELINES.

2. Now, click + CREATE PIPELINE in the Pipelines List View.

3. From the Select Source Type page, choose Facebook Ads

Now, you will be taken to the Configure your Facebook Ads account page. 

4. On the page, do any of the following:

• If you have already configured a Facebook account, click CONTINUE.
• If now, click Add Facebook Ads Account and do the following:

A. Sign in to your Facebook account and click Continue as <Company Name> (Hevo Data, here) in the pop-up dialog.

B. Choose Access your Facebook Ads and related statistics and click Save to authorize Hevo for the same.

C. Now, another pop-up will open, and click Got it to confirm the same.

5. Now, on the Configure your Facebook Ads Source, add the following details:

• Pipeline Name: Give a recognizable and unique name for your Pipeline, under 255 characters.
  
• Select Ad accounts: Choose the Facebook Ads account that you want to ingest data from. If you have multiple ad accounts under one Facebook account, pick the one for data transfer.
  
• Report Type: Choose a report type from the following to ingest data from your Facebook Ads:
  • Predefined Reports: Here, Hevo automatically selects all the reports and their respective fields and columns for ingestion.
  • Custom Reports: Hevo allows you to select the aggregation level manually, the aggregation time, and the fields for the Facebook Ads report you want to replicate.
    
• Ad Action Report Time: This is the reported time of an action by a Facebook user, and you have the following options:
  • Impression: The time at which a user watched your ad.
  • Conversion: The time at which a user took action after watching your ad.
  • Mixed (Default): The time when an impression or link click occurred on your ad and the time when a conversion action, such as a purchase on your website, occurred for it.
    
• Historical Sync Duration: The duration for which you want to ingest the existing data from the Source. The default duration is one year.
  
• Attribution Setting:
  
• Custom Attribution Window Setting: Specify one or both of the following:

• Click Attribution Window: The number of days between a person clicking your ad and taking an action. 
• View Attribution Window: The number of days between a person viewing your ad and taking an action.
• Use Account Attribution Setting: The attribution setting you have defined in your Facebook Ads account.
• Use Unified Attribution Setting: The attribution settings that have been defined for individual ad sets.
• Advanced Settings
  • Thumbnail Height: The rendered height of the thumbnails provided in the thumbnail URL.
  • Thumbnail Width: The rendered width of the thumbnails provided in the thumbnail URL.

6. Click CONTINUE.

Now, you can proceed to configure the data ingestion and set up the Destination.

Step #2: Configure Snowflake as the Destination

Before you can configure Snowflake as the Destination to move Facebook Ad data, you need to have the following prerequisites:

1. An active Snowflake account.
2. The ACCOUNTADMIN or SECURITYADMIN role in Snowflake is used to create a new role for Hevo.
3. ACCOUNTADMIN or SYSADMIN role in Snowflake to create a warehouse.
4. Hevo has the USAGE permission on data warehouses and the USAGE and CREATE SCHEMA permissions on databases.
5. Hevo has the USAGE, MONITOR, CREATE TABLE, CREATE EXTERNAL TABLE, and MODIFY permissions on the current and future schemas.
6. The Team Collaborator or any administrator role except the Billing Administrator role in Hevo to create the Destination.

Follow the steps below to configure Snowflake as a Destination in Hevo:

1. From the Navigation Bar, pick DESTINATIONS.

2. Click + CREATE DESTINATION in the Destinations List View.

3. Now, on the Add Destination page, choose Snowflake as the Destination type.

4. Add the following on the Configure your Snowflake Destination page:

Destination Name: Pick a recognizable, unique name for your Destination within 255 characters.

Select one of the methods to connect to your Snowflake data warehouse:

Connect using Key Pair Authentication

• Snowflake Account URL: Get the URL following the steps here.
• Database User: A user with a non-administrative role created in Snowflake, and whom the public key is assigned.
• Private Key: A cryptographic password used along with a public key to generate digital signatures. Get the key by following the steps here.
• Passphrase: The password given while generating the encrypted private key.
• Warehouse: The Snowflake warehouse associated with your database, where the SQL queries and DML operations are performed.
• Database Name: The name of the Destination database where the data is to be loaded.
• Database Schema: The name of the schema in the Destination database to which the specified database user has access.
  Note: The schema name is case-sensitive.

Connect using Access Credentials

• Snowflake Account URL: Get the URL following the steps here.
• Database User: A user with a non-administrative role created in the Snowflake database.
• Database Password: The password of the database user.
• Warehouse: The Snowflake warehouse associated with your database, where the SQL queries and DML operations are performed.
• Database Name: The name of the Destination database where the data is to be loaded.
• Database Schema: The name of the schema in the Destination database to which the specified database user has access.

Advanced Settings

• Populate Loaded Timestamp: Enable this option to append the __hevo_loaded_at column to the Destination table to indicate the time when the Event was loaded to the Destination.
• Create Transient Tables: Enable this option to create transient tables. Transient tables have the same features as permanent tables, minus the Fail-safe period.

5. Now, click TEST CONNECTION. 

6. Click SAVE & CONTINUE.

8 Reasons to Choose Hevo Data to Connect Facebook Ads to Snowflake

• Faster Implementation: A very quick 2-stage process to get your pipeline set up. After that, everything’s automated while you watch data sync to Snowflake or any other destination in real-time. 
  
• 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 the source schema with the 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 ETL spend. Choose a plan based on your business needs. Stay in control with spend alerts and configurable credit limits for unforeseen spikes in the 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-day free trial.
  
• Security – Discover peace with end-to-end encryption and compliance with all major security certifications, including HIPAA, GDPR, and  SOC-2.

Method 2: Migrate from Facebook ads to Snowflake via Manual ETL Method

The best way to migrate data from Facebook ads to Snowflake for analytical purposes is to create a CSV file with the data and manually upload the same to the Snowflake data warehouse.

Here is the step-by-step that you can follow:

• Go to Facebook Ads Manager.
• Choose your desired campaigns, date ranges, columns/metrics, performance data, and more
• Export the report as a CSV file.
• Now, open the CSV and clean the data to ensure the column and row match as per the fields you want to ingest.
• In Snowflake, create the destination table with matching columns and types. This is vital to ensure that the columns, types, and rows in the destination table match the ones in the source file.

You can use the following SQL to create the table:

CREATE OR REPLACE TABLE facebook_ads (
    campaign_id VARCHAR,
    impressions INTEGER,
    clicks INTEGER,
    spend FLOAT,
    -- add additional columns as needed
);

Now, create a staging area within Snowflake to load the data from the CSV file and use the code given below:

CREATE OR REPLACE STAGE my_csv_stage;

Upload the file using the SnowSQL CLI or web UI with the following code:

snowsql -q "PUT file://path/to/facebook_ads.csv @my_csv_stage"

Load the CSV file into the Table you created in Snowflake, using the following COPY INTO command:

COPY INTO facebook_ads
FROM @my_csv_stage/facebook_ads.csv
FILE_FORMAT = (TYPE = 'CSV' FIELD_OPTIONALLY_ENCLOSED_BY = '\"' SKIP_HEADER = 1);

After you have done the transfer, check the data in Snowflake for accuracy and consistency.

Introduction to Facebook Ads

Facebook is arguably one of the most popular platforms for marketers to advertise on. Facebook ads generate a lot of data about campaigns across Impressions, Clicks, Cost Spread, and so on. The Facebook Marketing API makes this treasure trove of data available for authorized users. 

Facebook offers a variety of advertising formats, and the Facebook advertising manager will help you choose a format based on your company’s marketing goals. Whether you want to Increase visibility, Quickly check your business, or Increase your conversion rates, Facebook provides services for all of these tasks.

Both businesses and consumers benefit from paid advertising. Companies can reach audiences in ways that conventional advertising cannot achieve, and can easily track the effects of online and offline advertising. Offline tracking data shows companies the true value of their Facebook payments. The more visible your post is to your audience, the more likely it is to bring in sales. Also, customers will be able to easily access company information by viewing relevant advertisements.

Introduction to Snowflake

Snowflake is a leading cloud data warehouse platform that has enjoyed steady growth and increasing popularity in recent times. Snowflake provides a scalable cloud platform that is both business and developer-friendly and supports advanced data analytics. The Robustness, Cost-effectiveness, and Scalability of Snowflake make it very attractive for businesses to adopt.

There are multiple Data Warehouses available, however, what makes Snowflake unique is its architecture skills and its data-sharing abilities. Snowflake’s architecture enables storage and computing to scale independently, so customers can use storage and computing separately and pay for it. Also, users can protect their data in real-time.

The best feature of the snowflake is that it offers data storage and data computation capabilities separately. Snowflake is designed to ensure that minimum effort and interaction are required from the users for any performance or maintenance-related activity. Snowflake’s auto concurrency property provides users the facility to set a minimum and maximum cluster size and the scaling will then happen automatically over this range at a very rapid speed.

Challenges with the Manual ETL Process

The manual approach of setting up an ETL process that connects Facebook Ads to Snowflake is effective but it comes with its own set of challenges:

• A lot of technical knowledge is required to set up this manual ETL process, and it may place additional skill/personnel requirements on your business.
• Most standalone ETL scripts do not scale well — this means that, depending on the size of data to be transferred from Facebook Ads to Snowflake in each run, as well as the number of runs per day, the single ETL script may be insufficient to handle all your organization’s data transfer needs.
• The above steps would not be enough if you are looking to bring data from Facebook Ads to Snowflake in real-time. This would need to write additional code and set up cron jobs to move data in a timely fashion
• In case your use case involves cleaning or transforming data in any way, the above process would need to be tweaked to handle that. This is an additional overhead.
• A lot of the tasks involved in setting up and maintaining a manual process like this may be touch-and-go, and things will inevitably break. Without formal support and assistance, mission-critical data migration processes from Facebook Ads to Snowflake will fail and that can be costly to the business.

Check out how you can connect Facebook Ads to other data warehouses like Google BigQuery to manage and store your ads data seamlessly.

Conclusion

The article provided you an introduction to Snowflake and Facebook Ads, explaining their individual importance in the current business context. It also explained the step-by-step process of setting up Facebook Ads to Snowflake integration. The two methods discussed here are both equally effective in terms of final results, but as mentioned above, there are numerous challenges that you will face while implementing the manual method. If you have no qualms about investing an excessive amount of time and resources, go for the first method. Otherwise, you can opt for Hevo to seamlessly transfer your data from Facebook Ads to Snowflake for free.

Visit our Website to Explore Hevo

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand.

Share your experience of setting up Facebook Ads to Snowflake Integration in the comments section below.

FAQs