Send Facebook Ads data to AWS Redshift
Facebook and Instagram ad performance as Redshift tables you own: ads, creatives and insights, each its own table, refreshed on a schedule. Join them to web analytics and CRM to see which ads bring customers and revenue.
AWS RedshiftDateCostClicksDaily 09:00Facebook says what its ads delivered, and your AWS Redshift tables can't confirm it because the spend isn't there.
Paste this into your AI assistant. It creates a Facebook Ads Data Mart on your AWS Redshift storage and loads ads, creatives and insights as tables you own, refreshed on a schedule.
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-aws-redshift.md. The task: load Facebook Ads insights into AWS Redshift daily and build a Data Mart that joins spend by ad to website sessions and CRM revenue, so I can rank ads by revenue, not by purchase ROAS. 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 join Facebook Ads with web analytics and CRM in AWS Redshift
Ads Manager reports delivery using Meta's own attribution. Which ads produced customers and revenue is a question for your data: spend has to sit beside website touch points and CRM deals in one warehouse.
- Place Facebook spend beside Google, TikTok and Microsoft in one Redshift database
- Link clicks and sign-ups to the orders and deals they became
- Store spend, reach and ROAS in your AWS account, outside any ad account
Your data warehouseAdTouch pointDealJoined by idAd seen or clickedDeal months onOne query joins ad to deal.Step 1: Pick Facebook Ads and the fields to load
Create a Data Mart on your Redshift storage, choose Facebook Ads as the source, sign in with Facebook or paste an access token, add your ad account ids and select the fields.
- 15 endpoints and 1172 fields to choose from
- Ad Insights has 100 fields: spend, reach, clicks, conversions, ROAS
- Each breakdown gets its own Data Mart and table
Step 2: Load Facebook Ads into a Redshift schema and table
Schema and table in your Redshift database are filled in for you and can be edited. The first load covers the start of the previous month through today.
- Insights land in their own table, apart from ads and creatives
- Run History shows every load
DestinationSchemafacebook_adsTablead_account_insightsRun
Tablefacebook_ads.ad_account_insig…Step 3: Read the output schema without an API guide
After the first run the output schema gives each field a description. Fields like actions and action_values are columns of their own. Feed it to your quality checks.
- No Marketing API reference needed to read a column
- Analysts and marketers read the same 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: Link the Facebook Ads tables in Redshift
Ad accounts, ads, creatives and insights are separate tables that join on ids, which makes one Facebook Ads model.
- ad_id in Ad Insights is the ad, and creative_id on the ad leads to its creative
- Campaign and ad set ids ride on each row, so spend sums up at either level
Step 5: Schedule the refresh and backfill the past
Schedule once with a scheduled refresh: daily, weekly, monthly or on an interval. Backfill any earlier period when you need it.
- Backfills past a month are cut into segments of up to 31 days automatically
- No orchestration to run in AWS
Step 6: Point every report at the same Redshift tables
One set of Redshift tables feeds your model, , , assistants such as Claude and ChatGPT, and scheduled AI insights.
- Combine Facebook with Google and TikTok spend once
- Dashboards and sheets cannot drift apart
- 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 AWS Redshift
API scripts, generic ETL and CSV exports all move Facebook numbers. OWOX puts them in Redshift tables that are readable and ready to join.
- Your Redshift, your tables. Data sits in your own database behind your AWS credentials, outside the ad account.
- One table per breakdown. Country, region, age and gender, device, placement and product breakdowns load separately, so you keep only the cuts you use.
- Joins built in. Ad, ad set and campaign ids link the tables to each other and to CRM.
- No upkeep on the API. Changes to the Marketing API are handled for you.
- Free to self-host. The open-source OWOX Data Marts runs in your own infrastructure.
You are booked. The invitation is on its way to , with the calendar invite and the meeting link.