Facebook marketing

Prerequisites

• A Facebook Ad Account ID

Setting up the Facebook marketing connector

Basic Setup

1. Create a new data source

2. Select Facebook Marketing

3. Fill in the Name and Short name

4. Click on “Login with Facebook” and Authenticate your account

5. In another window, navigate to Facebook Ads manager https://www.facebook.com/adsmanager/manage/

   Your ad account ID number is shown above the search and filter bar in the account drop-down menu. Refer to the screen shot below:

   

   Fill in the account id

6. Enter the start date in the format YYYY-MM-DDT00:00:00Z

7. Click on Test connection and add the data source

8. Add the new data source

info

It might take a couple of minutes to provision a new data source. Once the data source is added you will be able to query it using plain SQL

At this point, you will be able to browse the data structure of the newly added data source. Once the data source is successfully added, and you have verified it in the data browser -- you can use 3rd party tools like DBeaver to connect to the newly created data source. Refer to Using DBeaver

Detailed configuration Information

No	Item	Required	Example	Notes
1	Account ID	Yes	111111111111111	The Facebook Ad account ID to use when pulling data from the Facebook Marketing API. Open your Meta Ads Manager. The Ad account ID number is in the account dropdown menu or in your browser's address bar. See the docs for more information.
2	Start Date	Yes	2017-01-25T00:00:00Z	The date from which you'd like to replicate data for all incremental streams, in the format YYYY-MM-DDT00:00:00Z. All data generated after this date will be replicated.
3	End Date	No	2017-01-26T00:00:00Z	The date until which you'd like to replicate data for all incremental streams, in the format YYYY-MM-DDT00:00:00Z. All data generated between the start date and this end date will be replicated. Not setting this option will result in always syncing the latest data.
4	Access Token	Yes		The value of the generated access token. From your App’s Dashboard, click on "Marketing API" then "Tools". Select permissions ads_management, ads_read, read_insights, business_management. Then click on "Get token". See the docs for more information.
5	Include Deleted Campaigns, Ads, and AdSets	No		Set to active if you want to include data from deleted Campaigns, Ads, and AdSets.
6	Fetch Thumbnail Images from Ad Creative	No		Set to active if you want to fetch the thumbnail_url and store the result in thumbnail_data_url for each Ad Creative.
7	Custom Insights	No		A list which contains ad statistics entries, each entry must have a name and can contains fields, breakdowns or action_breakdowns. Click on "add" to fill this field.
8	Page Size of Requests	No		Page size used when sending requests to Facebook API to specify number of records per page when response has pagination. Most users do not need to set this field unless they specifically need to tune the connector to address specific issues or use cases.
9	Insights Lookback Window	No		The attribution window. Facebook freezes insight data 28 days after it was generated, which means that all data from the past 28 days may have changed since we last emitted it, so you can retrieve refreshed insights from the past by setting this parameter. If you set a custom lookback window value in Facebook account, please provide the same value here.
10	Maximum size of Batched Requests	No		Maximum batch size used when sending batch requests to Facebook API. Most users do not need to set this field unless they specifically need to tune the connector to address specific issues or use cases.

Tables in the Facebook marketing connector

No	Table Name	Columns	Primary Keys	Read Mode	Inc. Sync Keys	API Info
1	custom_conversions	17	id	Full Refresh
2	ads	20	id	Full Refresh, Incremental	updated_time	API Reference
3	images	17	id	Full Refresh, Incremental	updated_time	API Reference
4	ad_account	55	id	Full Refresh
5	campaigns	22	id	Full Refresh, Incremental	updated_time	API Reference
6	ads_insights_action_breakdowns	10
7	ads_insights_breakdowns	24
8	activities	12				API Reference
9	ad_creatives	33	id	Full Refresh
10	videos	29	id	Full Refresh, Incremental	updated_time	API Reference
11	ads_insights	111	date_start, account_id, ad_id	Full Refresh, Incremental	date_start
12	ad_sets	19	id	Full Refresh, Incremental	updated_time

Table: custom_conversions

No	Column Name	Type	Notes
1	creation_time	VARCHAR
2	business	VARCHAR
3	custom_event_type	VARCHAR
4	event_source_type	VARCHAR
5	description	VARCHAR
6	rule	VARCHAR
7	data_sources	VARCHAR
8	is_unavailable	BOOLEAN
9	offline_conversion_data_set	VARCHAR
10	first_fired_time	VARCHAR
11	account_id	VARCHAR
12	is_archived	BOOLEAN
13	retention_days	DOUBLE
14	name	VARCHAR
15	id	VARCHAR	Primary Key
16	default_conversion_value	DOUBLE
17	last_fired_time	VARCHAR

Table: ads

No	Column Name	Type	Notes
1	conversion_specs	VARCHAR
2	updated_time	VARCHAR	Incremental sync key
3	created_time	VARCHAR
4	bid_info	VARCHAR
5	last_updated_by_app_id	VARCHAR
6	bid_type	VARCHAR
7	recommendations	VARCHAR
8	creative	VARCHAR
9	effective_status	VARCHAR
10	targeting	VARCHAR
11	account_id	VARCHAR
12	adlabels	VARCHAR
13	bid_amount	INTEGER
14	tracking_specs	VARCHAR
15	adset_id	VARCHAR
16	name	VARCHAR
17	id	VARCHAR	Primary Key
18	campaign_id	VARCHAR
19	status	VARCHAR
20	source_ad_id	VARCHAR

Table: images

No	Column Name	Type	Notes
1	original_width	INTEGER
2	created_time	VARCHAR
3	updated_time	VARCHAR	Incremental sync key
4	creatives	VARCHAR
5	url	VARCHAR
6	account_id	VARCHAR
7	filename	VARCHAR
8	name	VARCHAR
9	url_128	VARCHAR
10	width	INTEGER
11	original_height	INTEGER
12	is_associated_creatives_in_adgroups	BOOLEAN
13	id	VARCHAR	Primary Key
14	permalink_url	VARCHAR
15	hash	VARCHAR
16	status	VARCHAR
17	height	INTEGER

Table: ad_account

No	Column Name	Type	Notes
1	is_direct_deals_enabled	BOOLEAN
2	media_agency	DOUBLE
3	spend_cap	VARCHAR
4	account_status	INTEGER
5	business_city	VARCHAR
6	business_country_code	VARCHAR
7	balance	VARCHAR
8	user_tos_accepted	VARCHAR
9	funding_source	DOUBLE
10	timezone_name	VARCHAR
11	timezone_offset_hours_utc	DOUBLE
12	id	VARCHAR	Primary Key
13	is_tax_id_required	BOOLEAN
14	business_street	VARCHAR
15	has_migrated_permissions	BOOLEAN
16	business_name	VARCHAR
17	created_time	VARCHAR
18	funding_source_details	VARCHAR
19	disable_reason	DOUBLE
20	is_notifications_enabled	BOOLEAN
21	tax_id	VARCHAR
22	is_personal	DOUBLE
23	min_daily_budget	DOUBLE
24	tos_accepted	VARCHAR
25	name	VARCHAR
26	timezone_id	DOUBLE
27	business_street2	VARCHAR
28	is_attribution_spec_system_default	BOOLEAN
29	user_tasks	VARCHAR
30	fb_entity	DOUBLE
31	business_zip	VARCHAR
32	is_in_3ds_authorization_enabled_market	BOOLEAN
33	amount_spent	VARCHAR
34	extended_credit_invoice_group	VARCHAR
35	end_advertiser	DOUBLE
36	tax_id_type	VARCHAR
37	currency	VARCHAR
38	can_create_brand_lift_study	BOOLEAN
39	line_numbers	DOUBLE
40	owner	DOUBLE
41	capabilities	VARCHAR
42	failed_delivery_checks	VARCHAR
43	rf_spec	VARCHAR
44	business	VARCHAR
45	min_campaign_group_spend_cap	DOUBLE
46	tax_id_status	DOUBLE
47	account_id	VARCHAR
48	end_advertiser_name	VARCHAR
49	partner	DOUBLE
50	has_advertiser_opted_in_odax	BOOLEAN
51	offsite_pixels_tos_accepted	BOOLEAN
52	is_prepay_account	BOOLEAN
53	age	DOUBLE
54	io_number	DOUBLE
55	business_state	VARCHAR

Table: campaigns

No	Column Name	Type	Notes
1	source_campaign_id	DOUBLE
2	created_time	VARCHAR
3	updated_time	VARCHAR	Incremental sync key
4	issues_info	VARCHAR
5	special_ad_category_country	VARCHAR
6	lifetime_budget	DOUBLE
7	stop_time	VARCHAR
8	bid_strategy	VARCHAR
9	daily_budget	DOUBLE
10	spend_cap	DOUBLE
11	smart_promotion_type	VARCHAR
12	buying_type	VARCHAR
13	objective	VARCHAR
14	effective_status	VARCHAR
15	special_ad_category	VARCHAR
16	start_time	VARCHAR
17	account_id	VARCHAR
18	adlabels	VARCHAR
19	budget_remaining	DOUBLE
20	name	VARCHAR
21	budget_rebalance_flag	BOOLEAN
22	id	VARCHAR	Primary Key

Table: ads_insights_action_breakdowns

No	Column Name	Type	Notes
1	action_canvas_component_name	VARCHAR
2	action_carousel_card_id	VARCHAR
3	action_video_sound	VARCHAR
4	action_type	VARCHAR
5	action_video_type	VARCHAR
6	action_carousel_card_name	VARCHAR
7	action_target_id	VARCHAR
8	action_destination	VARCHAR
9	action_device	VARCHAR
10	action_reaction	VARCHAR

Table: ads_insights_breakdowns

No	Column Name	Type	Notes
1	impression_device	VARCHAR
2	country	VARCHAR
3	gender	VARCHAR
4	hourly_stats_aggregated_by_advertiser_time_zone	VARCHAR
5	skan_conversion_id	VARCHAR
6	body_asset	VARCHAR
7	link_url_asset	VARCHAR
8	title_asset	VARCHAR
9	frequency_value	VARCHAR
10	call_to_action_asset	VARCHAR
11	place_page_id	VARCHAR
12	image_asset	VARCHAR
13	video_asset	VARCHAR
14	hourly_stats_aggregated_by_audience_time_zone	VARCHAR
15	product_id	VARCHAR
16	ad_format_asset	VARCHAR
17	platform_position	VARCHAR
18	device_platform	VARCHAR
19	dma	VARCHAR
20	publisher_platform	VARCHAR
21	region	VARCHAR
22	app_id	VARCHAR
23	age	VARCHAR
24	description_asset	VARCHAR

Table: activities

No	Column Name	Type	Notes
1	date_time_in_timezone	VARCHAR
2	translated_event_type	VARCHAR
3	application_name	VARCHAR
4	event_type	VARCHAR
5	extra_data	VARCHAR
6	object_type	VARCHAR
7	object_name	VARCHAR
8	actor_name	VARCHAR
9	actor_id	VARCHAR
10	application_id	VARCHAR
11	object_id	VARCHAR
12	event_time	VARCHAR

Table: ad_creatives

No	Column Name	Type	Notes
1	thumbnail_data_url	VARCHAR
2	instagram_actor_id	VARCHAR
3	object_story_id	VARCHAR
4	instagram_story_id	VARCHAR
5	url_tags	VARCHAR
6	image_hash	VARCHAR
7	call_to_action_type	VARCHAR
8	template_url_spec	VARCHAR
9	body	VARCHAR
10	title	VARCHAR
11	image_crops	VARCHAR
12	thumbnail_url	VARCHAR
13	link_url	VARCHAR
14	id	VARCHAR	Primary Key
15	effective_object_story_id	VARCHAR
16	object_type	VARCHAR
17	image_url	VARCHAR
18	product_set_id	VARCHAR
19	effective_instagram_story_id	VARCHAR
20	instagram_permalink_url	VARCHAR
21	object_id	VARCHAR
22	link_og_id	VARCHAR
23	object_story_spec	VARCHAR
24	template_url	VARCHAR
25	account_id	VARCHAR
26	adlabels	VARCHAR
27	asset_feed_spec	VARCHAR
28	object_url	VARCHAR
29	applink_treatment	VARCHAR
30	name	VARCHAR
31	actor_id	VARCHAR
32	status	VARCHAR
33	video_id	VARCHAR

Table: videos

No	Column Name	Type	Notes
1	updated_time	VARCHAR	Incremental sync key
2	content_category	VARCHAR
3	icon	VARCHAR
4	description	VARCHAR
5	content_tags	VARCHAR
6	source	VARCHAR
7	title	VARCHAR
8	is_crosspost_video	BOOLEAN
9	scheduled_publish_time	VARCHAR
10	premiere_living_room_status	BOOLEAN
11	backdated_time_granularity	VARCHAR
12	is_instagram_eligible	BOOLEAN
13	is_crossposting_eligible	BOOLEAN
14	id	VARCHAR	Primary Key
15	post_views	INTEGER
16	views	INTEGER
17	live_status	VARCHAR
18	created_time	VARCHAR
19	custom_labels	VARCHAR
20	universal_video_id	VARCHAR
21	embed_html	VARCHAR
22	format	VARCHAR
23	length	DOUBLE
24	published	BOOLEAN
25	ad_breaks	VARCHAR
26	is_episode	BOOLEAN
27	backdated_time	VARCHAR
28	permalink_url	VARCHAR
29	embeddable	BOOLEAN

Table: ads_insights

No	Column Name	Type	Notes
1	cost_per_estimated_ad_recallers	DOUBLE
2	optimization_goal	VARCHAR
3	updated_time	VARCHAR
4	reach	INTEGER
5	cost_per_unique_action_type	VARCHAR
6	buying_type	VARCHAR
7	objective	VARCHAR
8	video_30_sec_watched_actions	VARCHAR
9	cost_per_thruplay	VARCHAR
10	instant_experience_outbound_clicks	VARCHAR
11	unique_clicks	INTEGER
12	unique_outbound_clicks	VARCHAR
13	estimated_ad_recall_rate_upper_bound	DOUBLE
14	video_p75_watched_actions	VARCHAR
15	cost_per_unique_inline_link_click	DOUBLE
16	canvas_avg_view_time	DOUBLE
17	inline_link_click_ctr	DOUBLE
18	ctr	DOUBLE
19	video_avg_time_watched_actions	VARCHAR
20	video_play_retention_20_to_60s_actions	VARCHAR
21	ad_click_actions	VARCHAR
22	cost_per_ad_click	VARCHAR
23	labels	VARCHAR
24	unique_link_clicks_ctr	DOUBLE
25	video_p95_watched_actions	VARCHAR
26	ad_id	VARCHAR	Primary Key
27	full_view_reach	DOUBLE
28	catalog_segment_value	VARCHAR
29	cost_per_unique_outbound_click	VARCHAR
30	inline_post_engagement	INTEGER
31	auction_competitiveness	DOUBLE
32	actions	VARCHAR
33	catalog_segment_value_omni_purchase_roas	VARCHAR
34	video_continuous_2_sec_watched_actions	VARCHAR
35	unique_outbound_clicks_ctr	VARCHAR
36	conversions	VARCHAR
37	estimated_ad_recall_rate	DOUBLE
38	attribution_setting	VARCHAR
39	video_play_retention_0_to_15s_actions	VARCHAR
40	cost_per_action_type	VARCHAR
41	outbound_clicks_ctr	VARCHAR
42	unique_inline_link_click_ctr	DOUBLE
43	catalog_segment_actions	VARCHAR
44	cost_per_15_sec_video_view	VARCHAR
45	estimated_ad_recallers_upper_bound	DOUBLE
46	account_name	VARCHAR
47	video_time_watched_actions	VARCHAR
48	wish_bid	DOUBLE
49	unique_inline_link_clicks	INTEGER
50	video_p100_watched_actions	VARCHAR
51	cost_per_conversion	VARCHAR
52	video_p50_watched_actions	VARCHAR
53	outbound_clicks	VARCHAR
54	converted_product_quantity	VARCHAR
55	qualifying_question_qualify_answer_rate	DOUBLE
56	ad_name	VARCHAR
57	impressions	INTEGER
58	full_view_impressions	DOUBLE
59	account_id	VARCHAR	Primary Key
60	website_purchase_roas	VARCHAR
61	estimated_ad_recall_rate_lower_bound	DOUBLE
62	instant_experience_clicks_to_open	DOUBLE
63	adset_name	VARCHAR
64	social_spend	DOUBLE
65	video_play_curve_actions	VARCHAR
66	age_targeting	VARCHAR
67	frequency	DOUBLE
68	action_values	VARCHAR
69	cpc	DOUBLE
70	campaign_id	VARCHAR
71	cpm	DOUBLE
72	created_time	VARCHAR
73	cpp	DOUBLE
74	conversion_rate_ranking	VARCHAR
75	cost_per_inline_link_click	DOUBLE
76	video_play_retention_graph_actions	VARCHAR
77	date_start	VARCHAR	Incremental sync key, Primary Key
78	conversion_values	VARCHAR
79	adset_id	VARCHAR
80	purchase_roas	VARCHAR
81	estimated_ad_recallers	DOUBLE
82	cost_per_unique_click	DOUBLE
83	account_currency	VARCHAR
84	inline_link_clicks	INTEGER
85	video_15_sec_watched_actions	VARCHAR
86	website_ctr	VARCHAR
87	auction_max_competitor_bid	DOUBLE
88	converted_product_value	VARCHAR
89	auction_bid	DOUBLE
90	catalog_segment_value_website_purchase_roas	VARCHAR
91	campaign_name	VARCHAR
92	cost_per_inline_post_engagement	DOUBLE
93	unique_actions	VARCHAR
94	spend	DOUBLE
95	catalog_segment_value_mobile_purchase_roas	VARCHAR
96	date_stop	VARCHAR
97	video_play_actions	VARCHAR
98	video_p25_watched_actions	VARCHAR
99	cost_per_outbound_click	VARCHAR
100	canvas_avg_view_percent	DOUBLE
101	quality_ranking	VARCHAR
102	unique_ctr	DOUBLE
103	instant_experience_clicks_to_start	DOUBLE
104	cost_per_2_sec_continuous_video_view	VARCHAR
105	estimated_ad_recallers_lower_bound	DOUBLE
106	clicks	INTEGER
107	engagement_rate_ranking	VARCHAR
108	location	VARCHAR
109	gender_targeting	VARCHAR
110	ad_impression_actions	VARCHAR
111	mobile_app_purchase_roas	VARCHAR

Table: ad_sets

No	Column Name	Type	Notes
1	updated_time	VARCHAR	Incremental sync key
2	created_time	VARCHAR
3	bid_info	VARCHAR
4	lifetime_budget	DOUBLE
5	bid_strategy	VARCHAR
6	end_time	VARCHAR
7	daily_budget	DOUBLE
8	effective_status	VARCHAR
9	start_time	VARCHAR
10	targeting	VARCHAR
11	account_id	VARCHAR
12	adlabels	VARCHAR
13	bid_amount	DOUBLE
14	budget_remaining	DOUBLE
15	promoted_object	VARCHAR
16	name	VARCHAR
17	bid_constraints	VARCHAR
18	id	VARCHAR	Primary Key
19	campaign_id	VARCHAR

External Resources

• Facebook Ad Account Id
• Facebook App
• Meta For Developers
• Facebook Ad Account Id Number
• Docs
• Ad Creative
• Documentation
• Create An App
• Setup The Marketing Api
• Heavily Throttles
• Documentation On Authorization
• Adaccount
• Adcreatives
• Adsets
• Adinsights
• Customconversions
• Facebook Insights Api Documentation.
• Facebook Article

Zipstack Cloud features a powerful SQL querying engine on top of many types of connectors, including those from Trino, some custom connectors and connectors from the open source Airbyte project. Some Zipstack cloud connectors are designed to utilize and expand upon Airbyte's connector protocol, but they do not use Airbyte's EL core. Additionally, some parts of the documentation for these connectors have been adapted from the connector documentation found in Airbyte's open source project.