There are a number of ways that you can query FacebookAds to get ad insights data, and the AdInsights and AdInsightsActions views facilitate all of these querying methods. For a simplified datamodel and querying experience, we also offer alternatives: you can use pre-built, simplified reports that correspond to queries that you would write for AdInsights or AdInsightsActions, or you can define your own ad insights report with the CreateAdInsightsReport stored procedure.

Using the Pre-Built Reports

The driver contains the following pre-built reports:

• AdInsights_ByActionReaction_90DayIncr
• AdInsights_ByAge_90DayIncr
• AdInsights_ByAgeAndGender_90DayIncr
• AdInsights_ByCarousel_90DayIncr
• AdInsights_ByCountry_90DayIncr
• AdInsights_ByDevicePlatform_90DayIncr
• AdInsights_ByDMA_90DayIncr
• AdInsights_ByGender_90DayIncr
• AdInsights_ByProductId_90DayIncr
• AdInsights_ByPublisherPlatform_90DayIncr
• AdInsights_ByRegion_90DayIncr
• AdInsights_ByVideoSound_90DayIncr
• AdInsights_ByVideoType_90DayIncr
• AdInsights_TargetedLevel_90DayIncr

Queries to these views are simpler than queries to AdInsights and AdInsightsActions in that these views already define the breakdowns and action collections to query. Queries to these views still require the specification of a Target ad object, however, and can also still use the Level and ActionAttributionWindows filters.

SELECT * FROM AdInsights_ByActionReaction_90DayIncr WHERE Target='act_12345' AND Level='adset'

The naming scheme for the views defines the breakdowns queried by the view, and the default time increment used by the view. The views will attempt to query ads data as far back in time as possible. The time span and time increment for the default views can be changed by query inputs.

Defining Custom Reports with CreateAdInsightsReport

If you routinely need to request fields that are not available on the pre-built reports that ship with the adapter, or you want to create a simpler ad report for your users, you may want to define your own custom ads report so that you can more easily query for the data you need. The CreateAdInsightsReport stored procedure can be used for this purpose.

The stored procedure outputs schema files, depdending on the output options supplied. Set the Location connection property to the output path of the generated reports to expose them as queryable tables in the adapter.

The example stored procedure call below creates a custom ad insights report with the specified fields, breakdowns, action breakdowns, and action collections:

EXEC CreateAdInsightsReport @ReportName = 'AdInsights_CreatedReport', @Fields = 'AccountCurrency,AdAccountId', @Breakdowns = 'Country', @ActionBreakdowns='ActionType,ActionDestination',@ActionCollection='Actions,UniqueActions', @OutputDirectory='LOCATION'"

In this example, the @OutputDirectory parameter is set to the location specified for the Location connection property.