The Sisense Facebook Ads connector is a standalone connector that allows you to import data quickly from your Facebook Ads account into the ElastiCube Manager without custom SQL. This connector includes several features to enable you to quickly and easily import data from Facebook Ads:

  • Date range selector from with the ElastiCube Manager
  • Support for accumulative builds
  • Retry mechanism to improve build success

After you have downloaded and installed the connector, you can connect to Facebook Ads through a connection string you provide Sisense in the ElastiCube Manager. The connection string is used to authenticate users who connect to Facebook’s APIs.

This page describes how to install the Facebook Ads connector, how to connect to Facebook Ads with a connection string, and what tables you can import into the ElastiCube Manager.

Breaking Changes for the Marketing API Version 2.8

As of July 25, 2017, Facebook no longer supports their Marketing API version 2.8. Currently, all users whose Facebook app is v2.8 will receive an error. To overcome this issue, add the parameter ‘Version=2.10’ to the connection string.

For more information about the latest version of the Marketing API, see Facebook’s blog posts.

Prerequisites

  • The Facebook Ads connector requires Sisense V6.6.1 and later.

Installing the Facebook Ads Connector

Sisense provides the Facebook Ads connector as a standalone connector that you can download and add to your list of default Sisense connectors. After you install the Facebook Ads connector, it is added to your list of Web Service connectors.

To install the Facebook Ads connector:

  1. Download the Facebook Ads installation file.
  2. Open the installation file and click Install.
  3. After the installation process is complete, click Close.

The Facebook Ads connector is displayed in the ElastiCube Manager under Add Data > Web Services.

Connecting to the Facebook Ads

To access Facebook Ads’ data from Sisense, you must provide valid Facebook Ads credentials in a connection string.

This section describes how to create a Facebook app to retrieve the necessary authentication parameters for the connections string and then how to construct the connection string.

Note: If you have already created a connection string for the Facebook connector, you can use the same credentials in the Facebook Ads connection string.

Creating an App

The credentials you need to connect to your data in Facebook are provided by Facebook when you create a Facebook app through the Facebook Developer site.

For more information, see Creating an App in Facebook.

Creating a Connection String

Sisense uses connection strings to connect to Facebook Ads and import data into the ElastiCube Manager. Each connection string contains authentication parameters that the data source uses to verify your identity and what information you can export to Sisense.

Note: When switching accounts, you must sign out of your account and then sign it with the new account.

After you have obtained the relevant credentials, you can create the connection used to connect to your Facebook account. The Facebook connection string has the following structure:

jdbc:DataSourceName:Property1=Value1;Property2=Value2;

The following is an example of Facebook connection string:

jdbc:facebook:OAuthClientId=112345383774486;OAuthClientSecret=064c524478d712534b7e7e4224fad;
InitiateOAuth=GETANDREFRESH;Version=2.8;CallbackURL=http://localhost/;Timeout=0;ThrottleSeconds=15;
ThrottleMaxPercent=90;Other=RetryWaitTime=60000;MaximumRequestRetries=5;

The example above includes both mandatory and optional parameters you can provide in the connection. The required parameters are emphasized in bold.

Mandatory Parameters

  • OAuthClientId: Set this to the App ID in your app settings.
  • OAuthClientSecret: Set this to the App Secret in your app settings.
  • CallbackURL: Set this to the Return URL in your app settings.
  • InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken connection property.

Optional Parameters

  • Aggregate Format: The format aggregate or collection columns should return in.
  • Authenticate As Page: The name or Id of a page to authenticate as when making requests to Facebook.
  • Auto Cache: The AutoCache property controls automatic caching of data.
  • Cache Connection: The connection string for the cache database.
  • Cache Driver: The database driver to be used to cache data.
  • Cache Location: Specifies the path to the cache when caching to a file.
  • Cache Metadata: Whether or not to cache the table metadata. The cache will be stored in memory unless CacheConnection or CacheLocation are set.
  • Callback URL: The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings.
  • Firewall Password: A password, if authentication is required to connect through a firewall.
  • Firewall Port: The TCP port for the firewall FirewallServer — see the description of the FirewallServer option for details.
  • Firewall Server: Specify a firewall name or IP address to authenticate requested connections, if necessary.
  • Firewall Type: The type of firewall to connect through.
  • Firewall User: The user name to authenticate with the firewall.
  • Initiate OAuth: Set this property to initiate the process to obtain or refresh the OAuth access token when you connect.
  • Location: A path to the directory that contains the schema files defining tables, views, and stored procedures.
  • Logfile: A path to the log file.
  • Max Log File Size: A string specifying the maximum size in bytes for a log file (ex: 10MB). When the limit is hit, a new log is created in the same folder with the date and time appended to the end.
  • MaximumRequestRetries: The maximum amount of request retries.
  • OAuth Access Token: The access token for connecting using OAuth.
  • OAuth Client Id: The client Id assigned when you register your application with an OAuth authorization server.
  • OAuth Client Secret: The client secret assigned when you register your application with an OAuth authorization server.
  • OAuth Settings Location: The location of the settings file where OAuth values are saved when InitiateOAuth is set to GETANDREFRESH or REFRESH.
  • Offline:Use offline mode to get the data from the cache instead of the live source.
  • Other: The other parameters necessary to connect to a data source, such as username and password, when applicable.
  • Pagesize: The maximum number of results to return per page from Facebook.
  • Proxy Auth: Scheme The proxy server authorization scheme (default: BASIC).
  • Proxy Auto Detect: This indicates whether to use the default system proxy settings or not. Set ProxyAutoDetect to FALSE to use custom proxy settings. This takes precedence over other proxy settings.
  • Proxy Password:A password, if authenticating with a proxy server.
  • Proxy Port: The TCP port for the proxy ProxyServer (default: 80).
  • Proxy Server: If a proxy server is given, then the HTTP request is sent to the proxy instead of the specified server.
  • Proxy SSL Type: The SSL type to use when connecting to the proxy server (default: AUTO).
  • Proxy User: A user name, if authentication is to be used for the proxy.
  • Pseudo Columns: Indicates whether or not to include pseudo columns as columns to the table.
  • Query Cache: The time duration, in seconds, for which the in-memory cached data is reused.
  • Readonly: You can use this property to enforce read-only access to Facebook from the provider.
  • RetryWaitTime: The amount of time in milliseconds to wait before retrying to query the Facebook API.
  • RTK: The runtime key used for licensing.
  • Search Terms: Default SearchTerms if none are specified. Used for some tables, such as Users, where SearchTerms may be specified.
  • SSL Server Cert: The certificate to be accepted from the server when connecting using TLS/SSL.
  • Support Enhanced SQL: If set to true, the provider will cache the entire table in memory and issue the current query against the memory cache. Allows queries with aggregate functions that are otherwise unsupported. The entire memory cache is discarded after the query is executed, unlike QueryCache.
  • Tables: Comma-separated list of tables to be listed. For example: Tables=TableA,TableB,TableC.
  • Target: A default target if none is specified. Used for some tables, such as Comments, where a target may be specified.
  • ThrottleSeconds: Indicates how many seconds to wait until sending another insight request when the ThrottleMaxPercent has been met.
  • ThrottleMaxPercent: The threshold set for throttling further insight requests. The defaults for ThrottleSeconds and ThrottleMaxPercent are 10 and 95.
  • Timeout:The value in seconds until the timeout error is thrown, canceling the operation.
  • Verbosity: The verbosity level that determines the amount of detail included in the log file.
  • Version: The Facebook Graph API version to use.
    You can see your version in the Facebook Developer dashboard. See the Facebook Platform Changelog to see if your version is still supported.

Adding Facebook Ads Tables to your ElastiCube

After you have changed the connectivity settings of the predefined ElastiCube, or if you want to create your own ElastiCube from scratch, you can add Facebook Ads tables exposed by the Facebook API.

Facebook provides access to data associated with the Facebook account you defined in the connection string. Once you have connected, Sisense displays a list of Facebook Ads tables you can import. For a list of supported tables, see Facebook Ads Tables.

To add Facebook Ads data:

  1. In ElastiCube Manager, click Add Data and then, Facebook Ads. The Connect to Facebook window is displayed.
  2. In the Date Range fields, select the date range for the relevant Facebook Ads data you want to import.
  3. Note: When performing an accumulative build, if you change the date range from your previous build, select Delete existing Data to prevent duplication.
  4. In Datasource Connection String, enter your connection string.
  5. Click Connect to Server. Facebook is displayed in the Select Database list.
  6. Click OK. Sisense connects to Facebook and displays a list of tables available for you to import. For a list of tables you can import, see Facebook Ads Tables.
  7. Select the relevant tables and click Add. The tables are displayed in the ElastiCube Manager.

Tip: Facebook Ads leverages the Facebook Marketing API, however, this API includes user request limitations. To overcome this limitation, try to reduce the date range or query fewer Ad accounts by removing them from your developers profile (Settings > Advanced > Advanced Ad Account IDs). For more information, see Facebook’s Marketing Rate limitations.

Facebook Ads Tables

The table below describes the tables you can import from various Facebook APIs. Facebook limits the number of calls and the amount of data that you can retrieve from their APIs. These limitations depend on which Facebook API you are using and your account type. When you reach a limit, Sisense returns the error provided by Facebook. For example, if you receive any of the following error codes, this usually indicates that you have reached one of Facebook’s limitations:

  • error code = 4, CodedException
  • error code = 32, CodedException
  • error code = 17,  User request limit reached

If you encounter one of these errors, refer to the Facebook API that exposed the table for more details regarding that API’s limitations.

Available Tables

TableDescription
AdAccountsThe Ad Accounts available for a user. AdAccounts in Facebook are the accounts you have available in Facebook for creating Ads on.


Accessing Ad Account information requires the ads_read permission.
AdCreativesInformation about an Ad Creative or the Ad Creatives on a specific Ad Account, Ad Set, or Ad. AdCreatives in Facebook represent a collection of the creatives for specific Ads in Facebook.


Accessing Ad Creative information requires the ads_read permission.
AdInsightsAds Insights provides access for reporting and analytics purposes.

Accessing AdInsights information requires the ads_read permission.
AdInsights_Age_GenderThe following are breakdowns of the AdInsights table. Due to storage constraints, Facebook has made only some permutations of breakdowns available.


For more information, see Facebook Breakdowns.
AdInsights_Country
AdInsights_FrequencyValue
AdInsights_Placement_Device
AdInsights_HStatsByAdvertiserTZ
AdInsights_HStatsByAudienceTZ
AdInsights_Placement_Device
AdInsights_Placement_ImpressionDevice
AdInsights_ProductId
AdInsights_PublisherPlatform
_ImpressionDevice_PlatformPosition
AdInsights_Region
AdInsightsActionsQuery an Ad Report. Accessing Ad Report information requires the ads_read permission.


AdInsightsActions represents a breakdown of the Actions column from AdInsights.
AdInsightsActions_Age_GenderThe following are breakdowns of the AdInsightsActions table. Due to storage constraints, Facebook has made only some permutations of breakdowns available.

For more information, see Facebook Breakdowns.
AdInsightsActions_Country
AdInsightsActions_FrequencyValue
AdInsightsActions_HStatsByAdvertiserTZ
AdInsightsActions_HStatsByAudienceTZ
AdInsightsActions_Placement_Device
AdInsightsActions_Placement_ImpressionDevice
AdInsightsActions_ProductId
AdInsightsActions_PublisherPlatform
_ImpressionDevice_PlatformPosition
AdInsightsActions_Region
AdsQuery information about an Ad or the Ads in a specific Ad Set, Campaign, or Ad Account. Accessing Ad Information requires the ads_read permission.


Ads in Facebook represent individual ads that have been created.
AdScheduledReportsQuery the Scheduled Reports for a given Ad Account. Accessing Scheduled Report information requires the ads_read permission.


The scheduled reports are a list of reports scheduled to run every day for your account. These can be maintained in the Facebook Ads Manager. Daily runs of these reports are available from ScheduledReportRuns.
AdSetsQuery information about an Ad Set or the Ad Sets on a specific Campaign or Ad Account. Accessing Ad Set information requires the ads_read permission.


AdSets in Facebook represent a collection of Ads in Facebook.
CampaignsQuery information about a Campaign or the Campaigns on a specific Ad Account. Accessing Campaign information requires the ads_read permission.

Campaigns in Facebook represent advertising campaigns that individual Ads or AdSets may be a part of.

Versions

VersionRelease DateImprovements
6.6.1.50June 12, 2017New version of Graph API v2.9 Changelog
New objects: SimpleVideoInsights, VideoInsightsByActionType, VideoInsightsByDistributionType, VideoInsightsByReactionType
6.6.1.45May 17. 2017General Release

Version 6.6.1.45

Breaking Changes

In the latest version of the Facebook Graph API, permissions have been largely reworked. All of the friends* permissions have been removed. In addition, most permissions require Facebook approval if you wish to request information about other user accounts who are not an administrator of the Facebook App. Sisense recommends reviewing the new permissions listed by Facebook here:

https://developers.facebook.com/docs/facebook-login/permissions/v2.1

In addition, users may reject requested permissions in the Authorization dialog on Facebook. If this happens, the specific Permissions requested will state that they are declined under the Status column. The permission may be rerequested by executing GetOAuthAccessToken and setting AuthType to rerequest.

If you want to keep using an old version of the API, you can by simply setting the Version connection property in the connection string to the specific version you were using and also setting the Location to a folder where you have saved an older copy of the schema files (.rsd and .rsb files).

If you want to update your app to 2.9, then you don’t have to do anything

Note: Sisense removed few metrics from the AdInsights / AdInsightsActions due to their removal by Facebook in 2.9.

A number of various changes have been made across tables due to changes in Facebook. These changes are listed below:

  • Facebook no longer allows public posts to be searched. We have had to remove SearchTerms from the Posts table because of this.
  • Checkins have been removed from the Graph API. Due to this, we have removed the Checkins table.
  • Events may no longer be inserted, updated, or deleted via the Graph API. We have had to change the Events table to a view.
  • Usernames for Facebook Users may no longer be used as a target and are no longer returned. The Facebook username for Pages is unaffected.
  • User ID’s for Facebook Users will be different across different Facebook Apps. This is due to the new policy of App-scoped User ID’s.