Facebook Offline Events

Through Alli Audiences, we are able to send offline conversion data to FB via their Offline Events API. There are some data requirements that should be managed in order to send valid data over - these parameters can be found in the documentation linked above under the parameters and description section.

File delivery cadence should be at least weekly to get conversion data processed more immediately. From FB: You should upload transaction within 62 days of the conversion so for the first set that’s as far back that will be matched/processed on FB’s end. Once the client drops offline data to the audience SFTP (see: delivering files), AMP will need to be setup to ingest the file (see: setting up a CRM).

Once the data is brought into Alli Audiences, a Power Audience may need to be created if the original file columns don’t exactly match what is required by Facebook. See the example below of how this is done. Note: the column names decided within the SQL must match with the Facebook requirements (event_time, value, event_name, currency, purchase_date).

SELECT 
    attr_FIRST_NM as attr_fn, 
    attr_LAST_NM as attr_ln, 
    attr_BILLING_CITY as attr_ct, 
    attr_BILLING_STATE as attr_st, 
    attr_BILLING_ZIP as attr_zip,
    attr_EMAIL_ADDR as attr_email, 
    EXTRACT(EPOCH FROM attr_PURCHASE_DATE )  as attr_event_time, 
    attr_purchase_date,
    attr_store_id,
    case when attr_TRANSACTION_TOTAL IS NULL
        then 0
        else attr_TRANSACTION_TOTAL end 
    as attr_value, 
    'AddToCart'::TEXT AS attr_event_name,
    'USD' as attr_currency,
    sort_order,
    created_at,
    CURRENT_DATE AS updated_at
    FROM {crm-8eb3c5b5-008a-414e-92ad-9b1aa106647f}
    where attr_purchase_date > current_date - 45 AND attr_purchase_date < current_date + 10
    AND attr_store_id::text != '-1501' AND attr_store_id::text != '-1502'