Tag: marketing analytics

This week, I decided to take my exploration of Snowflake external interface to pump data from Facebook Ads to Snowflake. I took a template that I previously used for Hubspot meta data analysis and rolled those learnings into building a Facebook data tool. A little ChatGPT guidance had me up and running within 20 minutes.

Security and Permissions Guidance and Requirements

Before you jump in and start clicking through this tutorial there are some considerations you need to run this end to end.

1 Access to a real Facebook ads account– When you complete the setup, you preferably want to test and valdiate against a real Facebook ads account. The same developer account you use, will need direct access to a Facebook ads account.

2. Snowflake Enterprise – You will need a full, registered version of Snowflake enterprise to utilize external interface to run this stored procedure.

3 Access to Snowflake AccountAdmin– This process requires external interface to be enabled and a network rule grating access to Facebook graph API. To do that you need AccountAdmin access or request your Snowflake admin to enable this feature. This tutorial explains all of the components, so you may want to pass this along to your admin along with a formal request to enable this feature.

Setup your Facebook App and Acquire a Token

Login to Facebook Developer Console and view “My Apps”

Create a new App in Facebook developer console

Select “Setup” for “Marketing API” so you can access APIs that will ultimately deliver your data into Snowflake.

To pull Facebook Ads insights (like impressions, spend, clicks). In my case, I am reading and analyzing data so I just checked the “read” permissions.

• ✅ ads_read → Required for reading ad data
• ✅ read_insights → Required to access ad performance metrics
• ads_management → (Optional) Only needed if you’re modifying campaigns or fetching extra account metadata (not required for read-only insights)

Click “Get Token” and store it in a secure key vault or key management tool.

Setup your Snowflake for Facebook Ads data

Login to your Snowflake org

Create a table that will store your staged data. I called my table “AD_INSIGHTS” that resides within a FACEBOOKADS schema.

-- CREATE TABLE TO HOLD

  CREATE OR REPLACE TABLE <<YOURDB>>.FACEBOOKADS.AD_INSIGHTS (
  METADATA VARIANT,
  LAST_UPDATED TIMESTAMP,
  TABLENAME STRING
);

Create a secret to hold your Facebook access token securely. This is the same token you acquired in the steps outlined earlier in this tutorial.

CREATE OR REPLACE SECRET facebook_access_token
  TYPE = GENERIC_STRING
  SECRET_STRING = 'PASTE_YOUR_ACCESS_TOKEN_HERE';

Create a network rule that will allow Snowflake to connect to Facebook graph API. This requires elevated permissions in your Snowflake org.

CREATE OR REPLACE NETWORK RULE facebook_api_rule
  MODE = EGRESS
  TYPE = HOST_PORT
  VALUE_LIST = ('graph.facebook.com');

Create external access integration. This requires elevated permissions in your Snowflake org.

  CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION facebook_integration
  ALLOWED_NETWORK_RULES = (facebook_api_rule)
  ALLOWED_AUTHENTICATION_SECRETS = (facebook_access_token)
  ENABLED = TRUE;

Create a stored procedure to connect and return raw JSON data to Snowflake

CREATE OR REPLACE PROCEDURE <<YOURDB>>.FACEBOOKADS.FETCH_AD_INSIGHTS(ad_account_id STRING, date_preset STRING)
  RETURNS STRING
  LANGUAGE PYTHON
  RUNTIME_VERSION = 3.10
  PACKAGES = ('snowflake-snowpark-python', 'requests')
  HANDLER = 'main'
  EXTERNAL_ACCESS_INTEGRATIONS = (facebook_integration)
  SECRETS = (
    'facebook_access_token' = <<YOURDB>>.FACEBOOKADS.FACEBOOK_ACCESS_TOKEN
  )
  EXECUTE AS OWNER
AS
$$
import _snowflake
import requests
import datetime
import json
from snowflake.snowpark.types import VariantType, TimestampType, StringType, StructType, StructField

def main(session, ad_account_id, date_preset):
    token = _snowflake.get_generic_secret_string('facebook_access_token')
    
    url = (
        f"https://graph.facebook.com/v19.0/act_{ad_account_id}/insights"
        f"?fields=campaign_name,ad_name,impressions,clicks,spend"
        f"&date_preset={date_preset}&access_token={token}"
    )
    
    response = requests.get(url)
    if response.status_code != 200:
        return f"Error: {response.status_code} - {response.text}"
    
    raw_json = json.loads(response.text)
    now = datetime.datetime.utcnow()

    schema = StructType([
        StructField("METADATA", VariantType()),
        StructField("LAST_UPDATED", TimestampType()),
        StructField("TABLENAME", StringType())
    ])

    df = session.create_dataframe([[raw_json, now, ad_account_id]], schema=schema)
    df.write.mode("append").save_as_table("<<YOURDB>>.FACEBOOKADS.AD_INSIGHTS")

    return f"Success: ad insights for account '{ad_account_id}' inserted."
$$;

Obtain a campaign ID that you have access and permissions to:

Execute and request your performance data.

CALL <<YOURDB>>.FACEBOOKADS.FETCH_AD_INSIGHTS('<<YOURADACCOUNTID>>', 'last_7d');

Note: Your should NOT prefix your account ID with any values like “act_“. The stored procedure pre-filles that prefix.

Your results should load into <<YOURDB>>.FACEBOOKADS.AD_INSIGHTS as JSON.

In the next tutorial on this topic, I will share the SQL I use to parse ads data, analyze and weave in performance with my CRM data.