Data Model

The FacebookAds Adapter models FacebookAds APIs as relational Tables, Views, and Stored Procedures. API limitations and requirements are documented in this section; you can use the SupportEnhancedSQL feature, set by default, to circumvent most of these limitations.

Views

Views describes the available views. Two types of views are made available:

Base Views are statically defined to model Ad Accounts, Campaigns, Businesses, Lead Values, and more. They are used to create your custom reports instead of being used standalone. By default. these views return data from all time aggregated into a single row.
Predefined Report Views are a set of standard reports that mimic exactly what you see in the Facebook UI. All Predefined Report Views have "report" appended to their name. By default, these views return data from all time with a daily breakdown.

Stored Procedures

Stored Procedures are function-like interfaces to the data source. They can be used to search, update, and modify information in the data source.

Date Ranges and Aggregation

All tables and views support date ranges and aggregation.

Date Ranges

Date ranges can be defined in the WHERE clause using the Date field using =, <,>, between delimiters.

Additional predefined date fields are available, specifically:

week
month
quarter

When filtering on these, you can use the = operator with the date set to the first day of the time period. If you specify a different date, an error is returned.

For example, to specify the month of May in the year 2022, use the following condition, specifying the first day of that month:

month = '2022-05-01'

Aggregation

Aggregation can be applied at multiple levels:

“Date” returns daily data in the query results. For example,

SELECT ID, Target, Name, Status, "Date" FROM CData.FacebookAds.Campaigns WHERE "Date" BETWEEN '2022-01-01' and '2023-01-31' and Id = '15526475270_410830705612736_5193593'

“Hour” returns data aggregated by hour across the date range selected. For example, querying two years of data and selecting “Hour” returns 24 rows of data (one for each hour) with two years of data aggregated for each hour.

“DayofWeek” returns data aggregated by week across the date range selected. For example, querying two years of data and selecting “DayOfWeek” returns seven rows of data (one for each day) with two years of data aggregated for each day.

“Week” returns data aggregated by week across the date range selected. For example, querying two years of data and selecting “Week” returns 104 rows of data (one for each week) with data aggregated for each week.

“Month” returns data aggregated by month across the date range selected. For example, querying two years of data and selecting “Month” returns 24 rows of data (one for each month) with data aggregated for each month.

“MonthofYear” returns data aggregated by month across the date range selected. For example, querying two years of data and selecting “MonthofYear” returns 12 rows of data (one for each month) with two years of data aggregated for each month.

“Quarter” returns data aggregated by quarter across the date range selected. For example. querying two years of data and selecting “Quarter” return eight rows of data (one for each quarter). Note that a Quarter is defined as starting on the 1st of the month of January, April, July, and October.

“Year” returns data aggregated by year across the date range selected. For example, querying two years of data and selecting “Year” returns two rows of data (one for each year) with data aggregated by year.