Schema · Entity relationship diagram
Meta Ads tables and relationships
Facebook and Instagram campaigns, ad sets, ads, creatives and daily ad performance with conversions.
ConnectApprove an access request
Default schema
meta_adsDestinationsAmazon Redshift, Google BigQuery, Snowflake, PostgreSQL, Amazon S3
- Tables mirror the Marketing API objects (ad account, campaigns, ad sets, ads, ad creatives): one row per object with its latest settings;
updated_timesays when it last changed. ad_insights_dailyis the ad-level Insights report, one row per ad per day (date_start) in the ad account's timezone. The last 28 days are re-pulled on every sync while Meta's attribution settles.ad_insights_actions_dailyandad_insights_action_values_dailysplit the Insightsactionsandaction_valueslists into one row peraction_type(purchase, add_to_cart, link_click ...), with the 1-day view, 1-day click and 7-day click windows as columns.
Primary keyForeign keyReferencesJoins on (not a key)
Drag to pan, scroll or pinch to zoom, click a table to see what it joins to.
Table reference
8 tables · click a table for its columnsad_accounts12 columns · Account & ad structureshow in diagram
One row per ad account (GET act_<id>).
Primary key: account_id
| Column | Type | Key | Description |
|---|---|---|---|
| account_idPrimary key. | bigint | PK | Primary key. |
| name | varchar(512) | ||
| account_status | varchar(32) | ||
| currency | varchar(8) | ||
| timezone_name | varchar(64) | ||
| timezone_offset_hours_utc | decimal(6,2) | ||
| business_name | varchar(512) | ||
| business_country_code | varchar(8) | ||
| amount_spent | bigint | ||
| spend_cap | bigint | ||
| created_time | timestamp | ||
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
campaigns19 columns · Account & ad structureshow in diagram
One row per campaign (GET act_<id>/campaigns), latest settings.
Primary key: id
| Column | Type | Key | Description |
|---|---|---|---|
| idPrimary key. | bigint | PK | Primary key. |
| account_idReferences ad_accounts.account_id. | bigint | FK | References ad_accounts.account_id. |
| name | varchar(1024) | ||
| objective | varchar(64) | ||
| status | varchar(32) | ||
| effective_status | varchar(32) | ||
| configured_status | varchar(32) | ||
| buying_type | varchar(32) | ||
| bid_strategy | varchar(64) | ||
| daily_budgetMinor units of the account currency (cents for USD), as the API returns it. | bigint | Minor units of the account currency (cents for USD), as the API returns it. | |
| lifetime_budget | bigint | ||
| budget_remaining | decimal(18,2) | ||
| spend_cap | bigint | ||
| special_ad_categories | varchar(256) | ||
| start_time | timestamp | ||
| stop_time | timestamp | ||
| created_time | timestamp | ||
| updated_time | timestamp | ||
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
ad_sets25 columns · Account & ad structureshow in diagram
One row per ad set (GET act_<id>/adsets), latest settings.
Primary key: id
| Column | Type | Key | Description |
|---|---|---|---|
| idPrimary key. | bigint | PK | Primary key. |
| campaign_idReferences campaigns.id. | bigint | FK | References campaigns.id. |
| account_idReferences ad_accounts.account_id. | bigint | FK | References ad_accounts.account_id. |
| name | varchar(1024) | ||
| status | varchar(32) | ||
| effective_status | varchar(32) | ||
| configured_status | varchar(32) | ||
| optimization_goal | varchar(64) | ||
| billing_event | varchar(64) | ||
| bid_strategy | varchar(64) | ||
| bid_amount | bigint | ||
| daily_budget | bigint | ||
| lifetime_budget | bigint | ||
| budget_remaining | bigint | ||
| destination_type | varchar(64) | ||
| promoted_object_pixel_id | bigint | ||
| promoted_object_custom_event_type | varchar(64) | ||
| targeting_age_min | bigint | ||
| targeting_age_max | bigint | ||
| targeting_geo_locations_countries | varchar(2048) | ||
| start_time | timestamp | ||
| end_time | timestamp | ||
| created_time | timestamp | ||
| updated_time | timestamp | ||
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
ads13 columns · Account & ad structureshow in diagram
One row per ad (GET act_<id>/ads), latest settings.
Primary key: id
| Column | Type | Key | Description |
|---|---|---|---|
| idPrimary key. | bigint | PK | Primary key. |
| adset_idReferences ad_sets.id. | bigint | FK | References ad_sets.id. |
| campaign_idReferences campaigns.id. | bigint | FK | References campaigns.id. |
| account_idReferences ad_accounts.account_id. | bigint | FK | References ad_accounts.account_id. |
| creative_idReferences ad_creatives.id. | bigint | FK | References ad_creatives.id. |
| name | varchar(1024) | ||
| status | varchar(32) | ||
| effective_status | varchar(32) | ||
| configured_status | varchar(32) | ||
| preview_shareable_link | varchar(1024) | ||
| created_time | timestamp | ||
| updated_time | timestamp | ||
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
ad_creatives16 columns · Account & ad structureshow in diagram
One row per ad creative (GET act_<id>/adcreatives).
Primary key: id
| Column | Type | Key | Description |
|---|---|---|---|
| idPrimary key. | bigint | PK | Primary key. |
| account_idReferences ad_accounts.account_id. | bigint | FK | References ad_accounts.account_id. |
| name | varchar(1024) | ||
| status | varchar(32) | ||
| object_type | varchar(64) | ||
| title | varchar(1024) | ||
| body | varchar(8192) | ||
| call_to_action_type | varchar(64) | ||
| link_url | varchar(2048) | ||
| url_tags | varchar(2048) | ||
| image_url | varchar(2048) | ||
| thumbnail_url | varchar(2048) | ||
| video_id | bigint | ||
| effective_object_story_id | varchar(128) | ||
| instagram_permalink_url | varchar(1024) | ||
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
ad_insights_daily20 columns · Daily insightsshow in diagram
Insights, level=ad, one row per ad per day (date_start, the ad account's timezone).
Primary key: ad_id, date_start
| Column | Type | Key | Description |
|---|---|---|---|
| ad_idPrimary key (with date_start). References ads.id. | bigint | PK FK | Primary key (with date_start). References ads.id. |
| date_startPrimary key (with ad_id). | date | PK | Primary key (with ad_id). |
| adset_idReferences ad_sets.id. | bigint | FK | References ad_sets.id. |
| campaign_idReferences campaigns.id. | bigint | FK | References campaigns.id. |
| account_idReferences ad_accounts.account_id. | bigint | FK | References ad_accounts.account_id. |
| ad_name | varchar(1024) | ||
| adset_name | varchar(1024) | ||
| campaign_name | varchar(1024) | ||
| account_currency | varchar(8) | ||
| impressions | bigint | ||
| reach | bigint | ||
| frequency | decimal(18,6) | ||
| clicks | bigint | ||
| inline_link_clicks | bigint | ||
| spend | decimal(18,4) | ||
| cpm | decimal(18,6) | ||
| cpc | decimal(18,6) | ||
| ctr | decimal(18,6) | ||
| _jsdata_keyUpsert key: md5 of the natural key. Upsert key: md5 of ad_id | date_start. | varchar(64) | Upsert key: md5 of the natural key. Upsert key: md5 of ad_id | date_start. | |
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
ad_insights_actions_daily9 columns · Daily insightsshow in diagram
Insights `actions` per ad, day and action_type (counts). value = default attribution setting; value_1d_view / value_1d_click / value_7d_click = action_attribution_windows.
Primary key: ad_id, date_start, action_type
| Column | Type | Key | Description |
|---|---|---|---|
| ad_idPrimary key (with date_start, action_type). References ad_insights_daily.ad_id,date_start. | bigint | PK FK | Primary key (with date_start, action_type). References ad_insights_daily.ad_id,date_start. |
| date_startPrimary key (with ad_id, action_type). References ad_insights_daily.ad_id,date_start. | date | PK FK | Primary key (with ad_id, action_type). References ad_insights_daily.ad_id,date_start. |
| action_typePrimary key (with ad_id, date_start). | varchar(256) | PK | Primary key (with ad_id, date_start). |
| valueCount under the ad set's attribution setting. | decimal(24,6) | Count under the ad set's attribution setting. | |
| value_1d_viewCount attributed within 1 day of an impression without a click. | decimal(24,6) | Count attributed within 1 day of an impression without a click. | |
| value_1d_click | decimal(24,6) | ||
| value_7d_clickCount attributed within 7 days of a click. | decimal(24,6) | Count attributed within 7 days of a click. | |
| _jsdata_keyUpsert key: md5 of the natural key. | varchar(64) | Upsert key: md5 of the natural key. | |
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
ad_insights_action_values_daily9 columns · Daily insightsshow in diagram
Insights `action_values` per ad, day and action_type (conversion value, account currency).
Primary key: ad_id, date_start, action_type
| Column | Type | Key | Description |
|---|---|---|---|
| ad_idPrimary key (with date_start, action_type). References ad_insights_daily.ad_id,date_start. | bigint | PK FK | Primary key (with date_start, action_type). References ad_insights_daily.ad_id,date_start. |
| date_startPrimary key (with ad_id, action_type). References ad_insights_daily.ad_id,date_start. | date | PK FK | Primary key (with ad_id, action_type). References ad_insights_daily.ad_id,date_start. |
| action_typePrimary key (with ad_id, date_start). | varchar(256) | PK | Primary key (with ad_id, date_start). |
| valueConversion value (account currency) under the ad set's attribution setting. | decimal(24,6) | Conversion value (account currency) under the ad set's attribution setting. | |
| value_1d_view | decimal(24,6) | ||
| value_1d_click | decimal(24,6) | ||
| value_7d_click | decimal(24,6) | ||
| _jsdata_keyUpsert key: md5 of the natural key. | varchar(64) | Upsert key: md5 of the natural key. | |
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |