Facebook Usage Scenarios

Marketing Scenarios

Main use case for the Marketing API is the retrieval of ad performance. This is achieved mainly by using the two procedures: MarketingInsights and MarketingActions. While MarketingInsights returns the most common metrics, MarketingActions is focused on more advanced and custom metrics, delivered via the arrays of key-value pairs.

MarketingInsights

SQL

CALL facebook.MarketingInsights (
    account_id => long_account_id/* Optional: Ad Account ID */,
    campaign_id => long_campaign_id/* Optional: Ad Campaign ID (overrides Account ID) */,
    start_date => date_start_date/* Optional: Start Date */,
    end_date => date_end_date/* Optional: End Date */,
    batchSize => integer_batchSize/* Optional: Download data in batches, duration in days */,
    level => 'string_level'/* Optional: Level: Campaign, Adset or Ad */,
    daily => boolean_daily/* Optional: Daily precision or summary data */,
    clean28days => boolean_clean28days/* Optional: Remove 28 last days each time */,
    breakdown_age => boolean_breakdown_age/* Optional: Breakdown results by country */,
    breakdown_country => boolean_breakdown_country/* Optional: Breakdown results by country */,
    breakdown_device_platform => boolean_breakdown_device_platform/* Optional: Breakdown results by country */,
    breakdown_gender => boolean_breakdown_gender/* Optional: Breakdown results by country */,
    breakdown_publisher_platform => boolean_breakdown_publisher_platform/* Optional: Breakdown results by country */,
    action_attributes_mask => 'string_action_attributes_mask'/* Optional: Override mask 101010 for 1d_click,1d_view,7d_click,7d_view,28d_click,28d_view */,
    syncMode => boolean_syncMode/* Optional: If true, data is requested synchronously */,
    fieldsOverride => 'string_fieldsOverride'/* Optional: Requests only the specified list of fields from the API */,
    no_ads_action_stats => boolean_no_ads_action_stats/* Optional: Do not request ad action stats, and only create the single table */,
    customFields => 'string_customFields'/* Optional: JSON object to define extra columns */,
    target_table => 'string_target_table'/* Optional: Table name to save the data to */,
    preview => boolean_preview/* Optional: Preview only, don't write into table */
);;

This procedure covers the majority of use-cases and returns the following response. Depending on the parameters, some fields may be returned as null values or as XML arrays.

Daily Data Flows

Downloading marketing insights is typically scheduled on a day-to-day basis. This can be achieved by making use of scheduling the following sample call:

SQL

CALL facebook.MarketingInsights (
    account_id => 1234567890 | null,
    start_date => '2018-01-01',
    batchSize => 1,
    level => 'ad',
    daily => true,
    breakdown_country => true,
    target_table => 'dwh.Facebook_daily_insights'
);;

You can also create a job to call this procedure once a day, using the Data Virtuality Studio or the following snippet:

SQL

BEGIN
	DECLARE biginteger facebookJobId;
	facebookJobId = CALL SYSADMIN.CreateSQLJob (
		script => 'CALL facebook.MarketingInsights (
	account_id => 1234567890 | null,
	start_date => ''2018-01-01'',
	batchSize => 1,
	level => ''ad'',
	daily => true,
	breakdown_country => true,
	target_table => ''dwh.Facebook_daily_insights''
	);', 
		parallelRunsAllowed => 1, 
		retryCounter => 1, 
		retryDelay => 30, 
		description => 'sourceSchema=facebook,targetTable=dwh.Facebook_daily_insights,description=Facebook_daily_insights,template=MarketingInsights'
	);

	CALL SYSADMIN.CreateSchedule (
		jobId => facebookJobId, 
		type => 'CRON', 
		intervl => 0, 
		startDelay => 1, 
		cronExpression => '0 0 5 * * ?', 
		enabled => true
	);
END;;

The procedure makes use of the following parameters:

account_id: when set to a value, only the specified ad account is queried. When omitted (or set to null), all accessible ad accounts are queried and saved in the same table;
start_date: the initial replication date. Please note that having this parameter in place does not harm daily operations, as the internal workflow only uses this parameter when no data has been previously loaded. When data is already in the table, the latest retrieved date overwrites the initial replication date;
batchSize: number of days requested in a batch. Setting this field to a larger value may increase performance, but when a lot of data comes from the account, Facebook API will fail, recommending you to decrease the batch size;
level: level to request the data. Can be account, campaign, adset, or ad. Each one provides higher data granularity;
daily: indicates that daily data is required. When set to false - the API will return the overall data per batch period. Unlikely to be used as false in autonomous jobs, but in some cases is handy to request performance at some of the higher aggregation levels (e.g. account or campaign) per extended periods, like a month or a year;
breakdown_country: instructs the API to return the data with the country breakdown. This will introduce a new field country in the destination table, with the data being broken down by the country;
target_table: the fully-qualified name of the destination table. Assumes that the destination schema (dwh in this case) exists. The destination table may or may not exist. In the latter case, it will be created. The destination table structure must be compatible with the procedure output, preferably the table is created by the procedure, and not manually. In some cases, incorrect structure will not produce any errors, but the data will come in incomplete, due to the built-in mechanism to adjust to the existing metadata.

The procedure allows several customizations described below.

The following breakdowns are supported, apart from the above-mentioned country breakdown:

breakdown_age
breakdown_country
breakdown_device_platform
breakdown_gender
breakdown_publisher_platform

Each one adds the respective breakdown to the API request, and the corresponding column in the destination table. Columns (tables) are created only on the first execution of the procedure, thus adding a breakdown to the job, which targets existing table, created without this breakdown, will result in having multiple rows, broken down by the specified dimension, without a way to identify one against another, thus changing breakdown parameters in daily operation is not advised.

Some breakdowns are incompatible. Please refer to Facebook documentation for more details. The procedure will throw an error, when incompatible breakdowns are requested. Remove one of the incompatible breakdowns to proceed.

Setting the preview parameter to TRUE is only used to preview the shape of the returned data. This parameter is not intended to be used in production jobs, as it only returns the topmost rows with a sole purpose to do it as quickly as possible. Consistency and completeness are not guaranteed and are expected to not be met when preview is set to TRUE.

Ad Hoc Calls

When the target_table is specified, and the preview parameter is not specified, the data is saved into the specified table, and no output from the procedure is provided. You may wish to define advanced jobs. In this case, omit the target_table parameter. Further customization might require the definition of views on top of the returned data and subsequent handling of the returned data. The below call provides the API data from "yesterday" by making use of the date handling functions:

CODE

CREATE VIEW views.Facebook_Data_From_Yesterday AS
	SELECT * FROM (
		CALL facebook.MarketingInsights (
			account_id => 1234567890,
			start_date => TimestampAdd (SQL_TSI_DAY, -1, CurDate()),
			end_date => TimestampAdd (SQL_TSI_DAY, -1, CurDate()),
			level => 'campaign',
			daily => false
		)
	)x;;

You can use the view in further steps. Depending on the execution date, the returned data will be different.