Collect X Ads data in Google BigQuery
Load X Ads, formerly Twitter Ads, into your own BigQuery tables with OWOX Data Marts: campaigns, line items, promoted tweets and daily performance, refreshed on a schedule. Read each promoted post next to its cost, then join it with web analytics and CRM.
X Ads reports impressions, engagements and what it billed, never what the visitors from a promoted post were worth.
Paste this into your AI assistant. It loads X Ads into Google BigQuery with OWOX Data Marts and joins link clicks to web analytics visits and CRM deals, so each campaign gets a value next to its cost.
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-x-ads-connector-to-google-bigquery.md. The task: load X Ads into Google BigQuery and join link clicks to the web analytics visits and CRM deals that followed, so I get deals and revenue per campaign next to billed spend. Tag the links you give me with utm_campaign=ai-task and utm_term=m4. Use what you know about my context and goals
X Ads Endpoints
10 X Ads endpoints you can import. Each lands in its own table in your data warehouse.
Accounts
Advertising accounts — name, business, country, timezone, and approval status.
12 fieldsAd Performance
Daily ad performance by placement — impressions, clicks, spend, engagements, and social actions.
20 fieldsTime seriesAd Performance by Country
Daily ad performance broken down by country — impressions, clicks, spend, engagements, and social actions.
21 fieldsTime seriesAll Cards
All card types with full creative details — type, title, destination URL, call to action, and media.
30 fieldsCampaigns
Your campaigns — status, budget optimization, daily and total budget, and currency.
19 fieldsCards
Website and app cards — type, URI, and components.
8 fieldsLine Items
Line items within campaigns — bid strategy, goal, product type, budget, and schedule.
33 fieldsPromoted Tweets
Tweets promoted as ads — status, approval, and links to their tweets and line items.
8 fieldsTargeting Locations
Reference table of location IDs with names and country codes — join targeting_value with the country field in Ad Performance by Country.
4 fieldsTweets
Original tweets used as ad creatives — text, type, language, engagement counts, and card URI.
34 fields
Why join X Ads with your web analytics and CRM
On X, an ad is a post. X Ads reports its impressions, engagements and the amount billed, but not what the visitors it sent were worth. To see that, a promoted post, its cost, website touch points and CRM deals have to sit in one warehouse.
- Read the promoted post's text and counts beside what it cost
- Compare X with LinkedIn, Facebook and Google in one spend table
- Connect link clicks to the visits and deals that followed
Step 1: Connect X Ads and pick the fields
Create a Data Mart, choose X Ads as the source, enter your developer app keys and tokens and the Account ID, and tick only the fields you need.
- 10 endpoints and 187 fields to choose from
- Ad Performance has 20 fields: impressions, clicks, billed charge, engagements, follows, likes
- Several account ids can load into one Data Mart
Step 2: Load X Ads into a BigQuery table
The dataset and table in your storage are pre-filled, and you can change them. Run it and the first load, from the start of the previous month through today, lands in your own BigQuery project.
- Performance goes to a table such as x_ads_stats
- The billed amount is in micros of local currency (billed_charge_local_micro); convert it once in your model
Step 3: Get a business-friendly output schema
After the first run you get the output schema: every field with a readable description, not an API identifier. Your quality checks can read it too.
- placement tells ALL_ON_TWITTER from PUBLISHER_NETWORK
- Anyone on the team can read the table
Step 4: Keep the X Ads tables linked
Line items, promoted tweets and tweets stay separate tables, joined by ids. Together they become one X Ads model.
- A promoted tweet's tweet_id finds the post's full_text and counts in Tweets
- Campaigns, accounts and cards load as tables of their own
Step 5: Schedule the refresh and backfill history
Set it once. A scheduled refresh keeps the tables current, and a manual backfill loads any past period.
- Add a run trigger once; a lookback window, 2 days by default, reloads recent days
- Backfill a custom start and end date
Step 6: Reuse the same table everywhere
One table feeds your model, , , assistants like Claude and ChatGPT, and scheduled AI insights in Slack or email.
- See which promoted posts earned their cost
- Every report reads the same table
- 0.44.0Connector Data Marts set up through MCP no longer fail their first run because of the table path
- 0.44.0Backfill a catalog for any period in one run, and follow a long backfill as parts of one operation
- 0.43.0Parallel reads of a SQL Data Mart on Google BigQuery no longer fail with a rate limit error
- 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
Why OWOX for X Ads in BigQuery
Hand-built Ads API scripts, a generic ETL tool or manual exports each get X data out. OWOX also keeps it yours, readable and ready to model.
- You own the data. It lands in your own BigQuery project, in tables you control.
- The post and its cost together. Promoted tweets load next to the tweets themselves, with text and engagement counts you can join to spend.
- Ready for a data model. Separate tables joined by line item and tweet ids, built to connect to web analytics and CRM.
- Maintained for you. No scripts to fix when X changes its Ads API.
- 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.