Collect Facebook Ads data in Snowflake
Bring Facebook and Instagram ad performance into your Snowflake account with OWOX Data Marts: ads, creatives and insights as separate tables, reloaded on a schedule. Then join spend to your web analytics and CRM tables in SQL to see which ads brought customers.
SnowflakeDateCostClicksDaily 09:00Facebook Ads reports what Facebook says it delivered, and Snowflake holds no spend to check it against revenue.
Paste this into your AI assistant. It creates a Facebook Ads Data Mart on your Snowflake storage, reloads ads, creatives and insights on a schedule, and joins spend to your web analytics and CRM.
The prompt
Read https://www.owox.com/ai-agent-setup.md and follow it to set up OWOX Data Marts for me, starting from /connectors/free-facebook-ads-connector-to-snowflake.md. The task: load Facebook Ads ad insights into Snowflake on a daily schedule and build a Data Mart with spend, clicks and CRM revenue by campaign and week, so I can see cost per customer. Tag the links you give me with utm_campaign=ai-task and utm_term=data-foundation. Use what you know about my context and goals
Facebook Ads Endpoints
15 Facebook Ads endpoints you can import. Each lands in its own table in your data warehouse.
Ad
Individual ads with their creative, status, and links to their campaign and ad set.
32 fieldsAd Account
Your ad account settings, status, currency, and billing details.
61 fieldsAd Account User
Users with access to an ad account and their assigned roles.
2 fieldsAd Creatives
The images, videos, text, and other visual elements used in your ads.
65 fieldsAd Insights
Daily performance metrics for each ad — impressions, clicks, spend, and conversions.
100 fieldsTime seriesAd Insights by Ad Set
Daily ad set performance with reach deduplicated to match Meta Ads Manager.
92 fieldsTime seriesAd Insights by Age and Gender
Daily ad performance broken down by audience age range and gender.
102 fieldsTime seriesAd Insights by Campaign
Daily campaign performance with reach deduplicated to match Meta Ads Manager.
89 fieldsTime seriesAd Insights by Country
Daily ad performance broken down by the country where your audience is located.
101 fieldsTime seriesAd Insights by Device Platform
Daily ad performance broken down by the device type your audience used.
101 fieldsTime seriesAd Insights by Link URL Asset
Daily ad performance broken down by the link URL shown in your ads.
101 fieldsTime seriesAd Insights by Product ID
Daily ad performance broken down by individual product from your catalog.
101 fieldsTime seriesAd Insights by Publisher Platform and Position
Daily ad performance broken down by where your ads appeared — platform and placement position.
102 fieldsTime seriesAd Insights by Region
Daily ad performance broken down by the region where your audience is located.
101 fieldsTime seriesAds
Individual ads with their status, creative, and campaign hierarchy.
24 fields
Why Facebook Ads belongs next to your CRM in Snowflake
Ads Manager counts results with Meta's own attribution, so it reports what Facebook says it delivered. To check that against revenue, spend needs to sit beside website touch points and CRM deals as tables in one warehouse, where SQL can join them.
- Line Facebook up with Google, TikTok and Microsoft spend in one query
- Follow a sign-up on your site to the order or deal it became
- Query spend, reach and ROAS from your own account instead of the ad account
Your data warehouseAdVisitDealJoined by idAd seen or clickedDeal months laterA SQL join links the ad to the deal.Step 1: Connect Facebook Ads and choose the fields
Create a Data Mart on your Snowflake storage and pick Facebook Ads as the source. Sign in with Facebook or paste an access token, list the ad account ids and tick the fields you want.
- 15 endpoints and 1172 fields are available
- Ad Insights has 100 fields, from spend and reach to conversions and ROAS
- Give each breakdown its own Data Mart so it lands in its own table
Step 2: Load Facebook Ads into a Snowflake table
Choose the Snowflake database, then the schema and table; all three are pre-filled and editable. The first run loads the previous month through today into your account.
- Each endpoint lands in its own table, for example the ad account insights
- Each run is logged in Run History
DestinationDatabaseSchemafacebook_adsTablead_account_insightsRun
Tablefacebook_ads.ad_account_insig…Step 3: Read the output schema in plain words
Once the first run finishes, OWOX shows every column with an alias and a description a colleague can follow. Your quality checks can use it as well.
- actions, action_values and purchase_roas arrive as JSON text with documented meaning; parse them once in a view
- A stakeholder and an analyst see the same column names
facebook_ads.ad_account_insightsOutput schemaFieldDescriptiondate_startThe start date for your data…spendThe estimated total amount of…impressionsThe number of times your ads…clicksThe number of clicks on your ads.reachThe number of people who saw…purchase_roasThe total return on ad spend…Step 4: Join the Facebook Ads tables in SQL
Ad accounts, ads, creatives and insights stay separate tables that share ids. A few joins in Snowflake turn them into one Facebook Ads model.
- Ad Insights holds ad_id; Ads holds creative_id for the creative
- Campaign and ad set ids come on every insights row, so you can sum spend at either level
Step 5: Schedule the load and backfill history
Pick a cadence once and a scheduled refresh keeps the Snowflake tables current. A manual run fetches any earlier period.
- Daily, weekly, monthly or interval runs
- A long backfill is cut into pieces of up to 31 days for you
Step 6: Use the same Snowflake tables in every report
The same tables serve your model, , , assistants such as Claude and ChatGPT, and AI insights delivered to Slack or email.
- Blend Facebook with Google and TikTok spend in one place
- Reports stay consistent because they read the same tables
- 0.41.0Rotating a warehouse key is one change: Storages share reusable Credentials
- 0.41.0Shared SQL logic stays in one place: build a Data Mart on top of another
- 0.41.0Rotating a connector key is one change: Sources share reusable Credentials
- 0.39.0Reload a year of history in one backfill, not twelve
- 0.39.0Long Facebook Ads imports no longer fail on a corrupted response
Why OWOX for Facebook Ads in Snowflake
A Marketing API script or a manual export gets the rows out. OWOX leaves them in Snowflake tables that you own and that are ready to model.
- Inside your Snowflake account. Your roles and grants decide who can query the tables, not an ad account login.
- A table per breakdown. Country, region, age and gender, device, placement and product breakdowns each load separately, so you keep only the cuts you use.
- Ready to model. Tables share ad, ad set and campaign ids, so they join to web analytics and CRM.
- Looked after for you. When Meta changes the Marketing API, there is no script of yours to repair.
- Free when self-hosted. Run the open-source OWOX Data Marts on your own infrastructure at no licence cost.
You are booked. The invitation is on its way to , with the calendar invite and the meeting link.