AdInsights

Queries an Ad Report. Accessing Ad Report information requires the ads_read permission.

Table Specific Information

Facebook AdInsights can be requested with a great amount of detail, to simulate the same sort of information that can be retrieved in a report.

Select

When requesting AdInsights, always include a Target. The Target specifies the element from which to retrieve the insights. Legal Targets are AdAccount, Campaign, AdSet, or an Ad. For example:

SELECT * FROM AdInsights WHERE Target = 'act_123456'

Columns that are always fetched by default during a SELECT * query against Adinsights:

Target
DateStart
DateEnd
AccountCurrency
AdAccountId
AdAccountName
Impressions
Spend

Metrics Columns

When running a SELECT * query on an Adinsights report, not all of the metrics columns are retrieved from Facebook. This means that certain columns may return “0” or null values if column metadata is retrieved in a prior request. To fetch this data, these columns must be included in the SELECT portion of the query, rather than using SELECT *.

Metrics columns that may display this behavior in Adinsights include:

BuyingType
Clicks
ConversionRateRanking
CostPerEstimatedAdRecallers
CostPerInlineLinkClick
CostPerInlinePostEngagement
CostPerUniqueClick
CostPerUniqueInlineLinkClick
CPC
CPM
CPP
CTR
EstimatedAdRecallRate
EstimatedAdRecallers
Frequency
Impressions
InlineLinkClicks
InlineLinkClicksCounter
InlinePostEngagement
InstantExperienceClicksToOpen
InstantExperienceClicksToStart
InstantExperienceOutboundClicks
Objective
QualityRanking
Reach
Spend
UniqueClicks
UniqueCTR
UniqueInLineLinkClicks
UniqueInLineLinkClickCounter
UniqueLinkClicksCounter
Checkins
EventResponses
LinkClicks
OfferSaves
OutboundClicks
PageEngagements
PageLikes
PageMentions
PagePhotoViews
PostComments
PostEngagements
PostShares
PostReactions
PageTabViews
Video3SecondViews

Date Presets

To specify a data range, use either DateStart and DateEnd, or DatePreset. Available values for DatePreset:

maximum
today
yesterday
this_week_sun_today
this_week_mon_today
last_week_sun_sat
last_week_mon_sun
last_3d
last_7d
last_14d
last_28d
last_30d
last_90d
this_month
last_month

For example:

SELECT DateStart, DateEnd, AdAccountId, Spend, Impressions FROM AdInsights WHERE Target = 'act_123456' AND DateStart >= '01/01/2015' AND DateEnd <= '03/31/2015'

SELECT DateStart, DateEnd, AdAccountId, Spend, Impressions FROM AdInsights WHERE Target = 'act_123456' AND DatePreset = 'last_90d'

To specify how many days should be included in each report row, use TimeIncrement. For example:

SELECT DateStart, DateEnd, AdAccountId, Age, Spend, Impressions FROM AdInsights WHERE Target = 'act_123456' AND DatePreset = 'last_90d' AND TimeIncrement = '7'

SELECT DateStart, DateEnd, AdAccountId, Age, Spend, Impressions FROM AdInsights WHERE Target = 'act_123456' AND DatePreset = 'last_90d' AND TimeIncrement = 'monthly'

To specify the level at which insights should be retrieved, use the Level column. Legal values are ad, adset, campaign, or account. For example:

SELECT DateStart, DateEnd, AdAccountId, Age, Spend, Impressions FROM AdInsights WHERE Target = 'act_123456' AND Level = 'campaign'

Note: Both the DatePreset and the breakdowns are subject to frequent changes by Facebook. The lists above may be outdated due to Facebook changes. To see the most currently available breakdowns and date presets, see the documentation on Facebook for parameters and breakdowns at https://developers.facebook.com/docs/marketing-api/insights/.

Breakdown Columns

There are a number of breakdown columns. In general, only one breakdown column can be selected at a time. If you use SELECT *, no breakdown columns will be used. The available breakdown columns are:

Age: Can also be selected with Gender
Country
FrequencyValue
Gender: Can also be selected with Age
HStatsByAdvertiserTZ (Hourly Stats by Advertizer Timezone)
HStatsByAudienceTZ (Hourly Stats by Audience Timezone)
ImpressionDevice: Can also be selected with PublisherPlatform or both PublisherPlatform and PlatformPosition.
PlatformPosition: Must be selected with PublisherPlatform. Can also be selected with ImpressionDevice.
PublisherPlatform: Can be selected with PlatformPosition, ImpressionDevice, or both PlatformPosition and ImpressionDevice.
ProductId
Region

Most other columns not already mentioned can be used with standard SQL WHERE clause modifiers. For instance:

SELECT DateStart, DateEnd, AdAccountId, Spend, Impressions FROM AdInsights WHERE Target = 'act_123456' WHERE Impressions > 10000 AND Spend < 1000

Facebook Ads enforces restrictions on what breakdowns can be selected together, so by default the connector also enforces similar restrictions on breakdowns in queries supplied to it. If desired, you can bypass internal enforcement of breakdown restrictions by setting IgnoreValidationException to True. This passes any supplied breakdown combination to Facebook as-is.

Managing Query Size

Sometimes if a query is too large for Facebook to process on its end, you might receive this error: Please reduce the amount of data you're asking for, then retry your request. The error appears to be triggered by individually dense fields, and not the page size.

If the connector encounters this error when first executing certain types of queries, the driver attempts to retry the query at the next lowest level of ad object, down to the level defined in RetryLevel, as long as the query does not include the Level parameter. (If the Level parameter is specified in a query, the driver does not attempt to retry beyond the ad object level specified by Level. For further information, see RetryLevel.) Note that since spreading queries across more granular ad objects increases the number of requests executed for the query, it can decrease query performance.

If you continue to receive this error, try either reducing the date range of the query, or removing expensive columns.

A good method for finding and removing expensive columns is to use a binary search by removing half of the columns you are selecting and retrying the query. If you get the same error, reduce the remaining columns by half and try again. If you do not get an error, add back half of the columns you just removed, and try again.

Columns


Name	Type	Description
Target	String	The Id of the Account, Campaign, Ad Group, or Ad to get insights for.
DatePreset	String	An alternative to specifying the DateStart and DateEnd. A date range is automatically calculated based on the specified preset value. The allowed values are maximum, today, yesterday, this_week_sun_today, this_week_mon_today, last_week_sun_sat, last_week_mon_sun, last_3d, last_7d, last_14d, last_28d, last_30d, last_90d, this_month, last_month.
DateStart	Date	The starting date to retrieve insights. In the Facebook UI, this is the Report Start field. In the Facebook UI, this is the Report Start field.
DateEnd	Date	The ending date to retrieve insights. In the Facebook UI, this is the Report End field. In the Facebook UI, this is the Report End field.
TimeIncrement	String	The number of days of data aggregation. An int (1-90) or one of monthly or all_days. This value splits the range or preset date into smaller increments. The default value is 1.
Level	String	The level to represent the results at. The allowed values are ad, adset, campaign, account.
AccountCurrency	String	The currency that is being used by the ad account.
ActionAttributionWindows	String	A comma separated list that determines what is the attribution window for the actions. For example, 28d_click means the API returns all actions that happened 28 days after someone clicked on the ad. The default option means [1d_view,28d_click]. Possible values include 1d_view, 7d_view, 28d_view, 1d_click, 7d_click, 28d_click, default.
AdAccountId	String	The Id of the Ad Account that is associated with the report row.
AdAccountName	String	The name of the Ad Account that is associated with the report row.
CampaignId	String	The Id of the Campaign that is associated with the report row.
CampaignName	String	The name of the Campaign that is associated with the report row.
AdSetId	String	The Id of the Ad Set that is associated with the report row.
AdSetName	String	The name of the Ad Set that is associated with the report row.
AdId	String	The Id of the Ad that is associated with the report row.
AdName	String	The name of the Ad that is associated with the report row.
BuyingType	String	The method by which target ads are paid for in your campaigns.
Clicks	Long	The total number of clicks on your ad. Depending on what you're promoting, this can include Page likes, event responses or app installs. In the Facebook UI, this is the Clicks (All) field.
ConversionRateRanking	String	The conversion rate ranking.
CostPerEstimatedAdRecallers	Decimal	The average cost per additional person that we estimate will recall seeing your ad if asked within 2 days.
CostPerInlineLinkClick	Decimal	The average cost per click on links in the ad.
CostPerInlinePostEngagement	Decimal	The average cost per engagement on the post.
CostPerUniqueClick	Decimal	The average cost per unique click for these ads, calculated as the amount spent divided by the number of unique clicks received.
CostPerUniqueInlineLinkClick	Decimal	The average you paid for each unique inline link click.
CPC	Decimal	The average cost per click for these ads, calculated as the amount spent divided by the number of clicks received.
CPM	Decimal	The average cost that you've paid to have 1,000 impressions on your ad.
CPP	Decimal	The average cost that you've paid to have your ad served to 1,000 unique people.
CTR	Double	The number of clicks you received divided by the number of impressions. In the Facebook UI, this is the CTR (All) % field.
EstimatedAdRecallRate	Double	The estimated number of people who recall your ad divided by the number of people your ad reached.
EstimatedAdRecallers	Double	The additional number of people that we estimate will remember seeing your ads if asked within 2 days.
Frequency	Double	The average number of times that your ad was served to each person.
Impressions	Long	The number of times that your ad was served. On our mobile apps an ad is counted as served the first time it's viewed. On all other Facebook interfaces, an ad is served the first time it's placed in a person's News Feed or each time it's placed in the right column.
InlineLinkClicks	Long	Total number of clicks on links in the ad.
InlineLinkClicksCounter	Double	The click-through rate for inline clicks to link.
InlinePostEngagement	Long	The total number of engagements on the post.
InstantExperienceClicksToOpen	Long	instant_experience_clicks_to_open
InstantExperienceClicksToStart	Long	instant_experience_clicks_to_start
InstantExperienceOutboundClicks	Long	instant_experience_outbound_clicks
Objective	String	The objective you selected for your campaign. Your objective reflects the goal you want to achieve with your advertising.
QualityRanking	String	The quality ranking.
Reach	Long	The number of people your ad was served to.
Spend	Decimal	The total amount you've spent so far.
UniqueClicks	Long	The total number of unique people who have clicked on your ad. For example, if 3 people click on the same ad 5 times, it counts as 3 unique clicks.
UniqueCTR	Double	The number of people who clicked on your ad divided by the number of people you reached. For example, if you received 20 unique clicks and your ad was served to 1,000 unique people, your unique click-through rate would be 2%.
UniqueInlineLinkClicks	Long	The number of unique inline link clicks that your ad got. In the Facebook UI, this is the Unique Clicks to Link field.
UniqueInlineLinkClickCounter	Double	The click-through rate for unique inline clicks to link.
UniqueLinkClicksCounter	Double	The unique click-through rate for clicks to link. The number of people who clicked on the link in your ad that directs people off Facebook divided by the number of people you reached. Example: if you received 20 unique clicks to link and your ad was shown to 1,000 unique people, your unique click-through rate would be 2%.
Checkins	Int	The number of checkins attributed to the Ad.
EventResponses	Int	The number of event responses attributed to the Ad.
LinkClicks	Int	The number of link clicks attributed to the Ad.
OfferSaves	Int	The number of receive offers attributed to the Ad.
OutboundClicks	Int	The number of outbound clicks attributed to the Ad.
PageEngagements	Int	The number of page enagements attributed to the Ad.
PageLikes	Int	The number of page likes attributed to the Ad.
PageMentions	Int	The number of page mentions attributed to the Ad.
PagePhotoViews	Int	The number of photo views attributed to the Ad.
PostComments	Int	The number of post comments attributed to the Ad.
PostEngagements	Int	The number of post engagements attributed to the Ad.
PostShares	Int	The number of post shares attributed to the Ad.
PostReactions	Int	The number of post reactions attributed to the Ad.
PageTabViews	Int	The number of tab views attributed to the Ad.
Video3SecondViews	Int	The number of video views attributed to the Ad. Views count if at least 3 seconds or the entire video (if the video is less than 3 seconds) were played.
PixelAddToCart	Int	Pixel adds to cart.
PixelPurchase	Int	Pixel purchases.
Age	String	The age range for the metrics in this row. This is a breakdown column and selecting this column causes results to be further broken down by this metric.
Country	String	The country for the metrics in this row. This is a breakdown column and selecting this column causes results to be further broken down by this metric.
DevicePlatform	String	The device or platform used for viewing the ad. This is a breakdown column that may not be selected with other breakdown columns.
DMA	String	The designated marketing area. This is a breakdown column that may not be selected with other breakdown columns.
FrequencyValue	String	The number of times an ad in your Reach and Frequency campaign was served to each person. This is a breakdown column and selecting this column causes results to be further broken down by this metric.
Gender	String	The gender for the metrics in this row. This is a breakdown column and selecting this column causes results to be further broken down by this metric.
HStatsByAdvertiserTZ	String	Time period over which the stats were taken for the advertiser. This is a breakdown column and selecting this column causes results to be further broken down by this metric.
HStatsByAudienceTZ	String	Time period over which the stats were taken for the audience. This is a breakdown column and selecting this column causes results to be further broken down by this metric.
ImpressionDevice	String	The devices used to view the Ad. This is a breakdown column and selecting this column causes results to be further broken down by this metric.
PlacePageId	String	The place page used if applicable. This is a breakdown column that may not be selected with other breakdown columns.
PlatformPosition	String	The position on the platform.
ProductId	String	The product Id advertised in the Ad. This is a breakdown column and selecting this column causes results to be further broken down by this metric.
PublisherPlatform	String	The platforms the ads were published on.
Region	String	The region someone viewed the Ad from. This is a breakdown column and selecting this column causes results to be further broken down by this metric.
AdEffectiveStatus	String	An input only list of supported statuses when retrieving insights at a level lower than the Ad Account. See the values listed on AdStatus for an example of valid values.
UseAsync	Boolean	A boolean indicating if an asynchronous call should be used for retrieving the insights.
DefaultSummary	Boolean	A boolean indicating if we should get the default summary.

Tableau Connector for Facebook Ads