Send query results to Meta Ads Manager

You can use queries and orchestrations to build custom audiences in Meta Ads Manager. Send customer information, such as email addresses, phone numbers, names, birthdates, gender, city, state, postal code, and mobile advertising IDs, from Amperity to find customer matches.

Use custom audiences in Meta Ads Manager to advertise to customers on Facebook, Instagram, and Messenger, along with using the Meta Audience Network to extend your advertising beyond Facebook and reach new audiences on apps and mobile devices, such as WhatsApp.

You may configure Amperity to send offline events to Meta Ads Manager. Offline events should be a set of transaction events that occurred within the previous 7 days. Offline events sent to Meta Ads Manager using the Conversions API for offline events are matched with audiences in Facebook, Facebook Messenger, Instagram, and WhatsApp and ca help your brand track offline conversions for your marketing campaigns.

Build custom audiences on Meta Ads Manager using data from Amperity.

A Meta Ads Manager destination works like this:

  1. Build a custom audience using a query

  2. Add orchestration

  3. Run orchestration

    Important

    The custom audience terms of service must be signed by each business user that is associated with your Facebook Ads account. If the terms of service are not signed, a permissions error will prevent Amperity from sending data to Facebook Ads.

    Meta Ads Manager uses OAuth to grant access to Amperity. You will need to reauthorize OAuth if the token expires or is removed.

    Ask your DataGrid Operator to help resolve these issues if they occur.

    Note

    Changes to audiences are not immediately available in Meta Ads Manager. Allow for up to 24 hours after the point at which Amperity has finished sending audience updates for them to be available.

  4. Build ads in Meta Ads Manager.

    What is Meta Ads Manager?

    Audiences sent to Meta Ads Manager have access to Facebook, Messenger, Instagram and the Meta Audience Network.

    Facebook Ads

    Use Meta Ads Manager to configure a variety of ad placements across Facebook .

    Instagram

    Use Meta Ads Manager to configure objectives that place ads on Instagram .

    Facebook Messenger

    Use Meta Ads Manager to configure objectives that place ads on Messenger .

    WhatsApp

    Use Audience Manager to reach users who are not on Facebook or Instagram, but are on mobile apps that are within the audience network. For example, creating ads that open conversation threads in WhatsApp .

  5. Place ads on Facebook, Instagram, and Messenger; use Audience Network to extend advertising beyond Facebook to reach audiences on apps and mobile devices, such as WhatsApp.

Build audience

You will need to build an audience using a query that shapes the data so that fields in the output are mapped to the Facebook Marketing API keys.

Data mapping for Meta Ads Manager is only required one time. You can do this in the following ways:

  1. Add a Facebook table to the customer 360 database that selects PII fields in the Customer 360 table, and then outputs them to a table with columns that map to the Meta Ads Manager naming patterns.

  2. Add a SQL query that does the filtering within the query, and then outputs results that do not require a data template.

The correct approach here depends on the data and the desired use case (or cases) for downstream workflows.

Use a Facebook table

A Facebook table collects PII data from the Customer 360 table, and then creates a table with columns that match the naming pattern for fields in the Facebook Marketing API. This approach allows the Query Editor to use the Facebook table to filter by matching records. In the customer 360 database, add a SQL table with a SELECT statement similar to:

SELECT
  amperity_id AS EXTERN_ID
  ,LOWER(email) AS EMAIL
  ,CONCAT('+1','',REGEXP_REPLACE(phone,'[$\D\s]','')) AS PHONE
  ,REGEXP_REPLACE(given_name, '[^\w\s]', '') AS FN
  ,REGEXP_REPLACE(surname, '[*@/.,-_]', '') AS LN
  ,REGEXP_EXTRACT(postal,'^(\d{5})') AS ZIP
  ,LOWER(REPLACE(city, ' ', '')) AS CT
  ,UPPER(state) AS ST
  ,country AS COUNTRY
  ,birthdate AS BIRTH
  ,SUBSTR(LOWER(gender),1,1) AS GEN
FROM Merged_Customers
WHERE email IS NOT NULL
OR phone IS NOT NULL
OR (given_name IS NOT NULL
    AND surname IS NOT NULL
    AND postal IS NOT NULL
)

and then run the customer 360 database to update that table. After the table has run, you can use a visual query to define a query that filters by matching records in the Facebook table. This will return any record in the Facebook table that matches the query and will return the results with all of the fields in the Facebook table.

For example, you could define a query that finds all transactions in the past week for all email addresses, and then match records against the Facebook table. The results will contain the columns in the Facebook table for all emails associated with a transaction in the past week.

Use a SQL query

A SQL query can output results that map to fields in the Facebook Marketing API. This process is similar to building a table, but instead of the output to a table, it is to a query. This approach does not require a data template, but may not be reusable for multiple downstream workflows.

Add a SQL table with a SELECT statement similar to:

SELECT
  amperity_id
  ,LOWER(email) AS EMAIL
  ,CONCAT('+1','',REGEXP_REPLACE(Phone,'[$\D\s]','')) AS PHONE
  ,REGEXP_REPLACE(FirstName, '[^\w\s]', '') AS FN
  ,REGEXP_REPLACE(LastName, '[*@/.,-_]', '') AS LN
  ,REGEXP_EXTRACT(PostalCode,'^(\d{5})') AS ZIP
  ,LOWER(REPLACE(City, ' ', '')) AS CT
  ,UPPER(State) AS ST
  ,'US' AS COUNTRY
  ,Birthdate AS BIRTH
  ,SUBSTR(LOWER(Gender),1,1) AS GEN
FROM customer360
WHERE DATE_DIFF('day', LastPurchase, Now()) = 1
LIMIT 2000

and then assign this query to a destination that sends results to Meta Ads Manager.

Send offline events

Send offline events to Meta Ads Manager to help your brand track offline conversions from your marketing campaigns. Offline events are matched with audiences in Facebook, Facebook Messenger, Instagram, and WhatsApp.

Transaction events that occurred within the previous seven days and contain positive values for product quantity are sent to Meta Ads Manager using the Conversions API for offline events .

Important

The first time transaction events are sent to Meta Ads Manager, seven days of data is sent, after which Amperity should be configured to send daily updates, which will maintain a 7-day rolling window of transaction events.

Use a query to build a combination of data from the Unified Itemized Transactions and Customer 360 tables to represent the set of offline events that your brand wants to use within Meta Ads Manager.

A query that returns a collection offline events for use in Meta Ads Manager is similar to:

SELECT
  c360.amperity_id AS extern_id
  ,c360.email AS email
  ,c360.phone AS phone
  ,c360.given_name AS given_name
  ,c360.surname AS surname
  ,c360.birthdate AS birthdate
  ,c360.gender AS gender
  ,c360.city AS city
  ,c360.state AS state
  ,c360.postal AS postal
  ,c360.country AS country
  ,uit.order_id AS order_id
  ,uit.item_quantity AS quantity
  ,uit.product_id AS product_id
  ,uit.order_datetime AS timestamp
  ,CAST(uit.item_revenue / uit.item_quantity AS DOUBLE) AS price
  ,'USD' AS currency
  ,action_source AS 'physical_store'
FROM Unified_Itemized_Transactions uit
LEFT JOIN Customer_360 c360 ON uit.amperity_id = c360.amperity_id
WHERE uit.order_datetime > (CURRENT_DATE - interval '7' day)
GROUP BY order_id

The query MUST contain the following fields: extern_id, order_id, quantity, email (OR phone), product_id, timestamp, price, and currency. When action_source is not specified the default value is “physical_store”.

You may include any of the following customer profile fields to help improve match rates in Meta Ads Manager: given_name, surname, birthdate, gender, city, state, postal, and country.

Tip

Extend the WHERE clause to filter query results by purchase channel, purchase brand, purchase quantity, and to remove items that were returned and/or canceled.

For example:

WHERE uit.order_datetime > (CURRENT_DATE - interval '7' day)
AND uit.purchase_channel = 'channel'
AND uit.purchase_brand = "ACME Essentials"
AND uit.item_quantity > 0
AND (c360.email IS NOT NULL OR c360.phone IS NOT NULL)
AND (
    (is_cancellation IS NULL)
    OR (NOT is_cancellation)
)
AND (
    (is_return IS NULL)
    OR (NOT is_return)
)

Review the Conversions API parameters section for detailed information about the columns that must be (or may be) returned by your query.

Add orchestration

An orchestration defines the relationship between query results and a destination, including the location to which those query results will be sent and the frequency at which the orchestration will be run.

Note

Meta Ads Manager must be enabled before you can configure an orchestration to send query results. Ask your DataGrid Operator or Amperity representative to enable Meta Ads Manager for your tenant.

To add an orchestration

  1. From the Destinations tab, click Add Orchestration. This opens the Add Orchestration dialog box.

  2. From the Object Type drop-down, select Query.

  3. From the Object drop-down, select the query for which results will be sent to Meta Ads Manager.

  4. From the Destination drop-down, select a destination that is configured for sending data to Meta Ads Manager.

  5. From the Data Template drop-down, select a data template.

  6. Verify all settings.

  7. Set the workflow to Manual. (You can change this to automatic later, after verifying the end-to-end workflow.)

  8. Click Save.

Run orchestration

Run the orchestration manually to validate that it works.

Important

Amperity requires access to Meta Ads Manager. This access may expire or be removed periodically, depending on how OAuth is managed at Meta Ads Manager. If Amperity is unable to send data to Meta Ads Manager ask your DataGrid Operator to reauthorize access to Meta Ads Manager.

To add an orchestration

  1. From the Destinations tab, click Add Orchestration. This opens the Add Orchestration dialog box.

  2. From the Object Type drop-down, select Query.

  3. From the Object drop-down, select the query for which results will be sent to Meta Ads Manager.

  4. From the Destination drop-down, select a destination that is configured for sending data to Meta Ads Manager.

  5. From the Data Template drop-down, select a data template.

  6. Verify all settings.

  7. Set the workflow to Manual. (You can change this to automatic later, after verifying the end-to-end workflow.)

  8. Click Save.

Warning

If the Terms of Service (ToS) for Custom Audiences have not been accepted you will get a permissions error with a link to the ToS. Someone with access to the Facebook account will need to accept the ToS before Custom Audiences will be sent.

Facebook Marketing API keys

The following Amperity columns should be mapped to the corresponding Facebook Marketing API keys when they are present in query data:

Amperity Column

Facebook API Key

Description

Amperity ID

EXTERN_ID

This value is a unique ID used by the advertiser, such as a loyalty ID, a customer ID, an external cookie ID, or the Amperity ID.

For this key, the connector trims leading and trailing whitespace.

Tip

A query can alias the Amperity ID directly in a query: SELECT amperity_id AS EXTERN_ID from custom_table. This approach can be helpful for queries that are dedicated to returning data to be sent only to Facebook Ads.

Email Addresses

EMAIL

For this key, the connector:

  • Trims leading and trailing whitespace

  • Converts to lower-case

  • Hashes data as SHA-256

Phone Numbers

PHONE

Converts each phone number to E.164 format which represents a phone number as a number up to fifteen digits in length (without spaces) that starts with a + symbol. For example: +12061234567.

For this key, the connector:

  • Trims leading and trailing whitespace

  • Removes symbols, letters, and any leading zeros

  • Hashes data as SHA-256

Gender

GEN

For this key, the connector:

  • Trims leading and trailing whitespace

  • Converts to lower-case

  • Converts to m and f

  • Hashes data as SHA-256

Birth Date

BIRTH

The birth date in Amperity date format.

For this key, the connector splits this value into three fields: birth year (DOBY), birth month (DOBM), and birth day (DOBD).

Birth Year

DOBY

Warning

Do not pass this column. For this key, the connector will use birthdate to split out the value for DOBY with a format of YYYY and a range from 1900 to the current year.

Birth Month

DOBM

Warning

Do not pass this column. For this key, the connector will use birthdate to split out the value for DOBM with a format of 01 to 12.

Birth Day

DOBD

Warning

Do not pass this column. For this key, the connector will use birthdate to split out the value for DOBD with a format of 01 to 31.

Last Name

LN

This key supports special characters and non-Roman alphabet characters. For this key, the connector:

  • Trims leading and trailing whitespace

  • Converts to lower-case

  • Removes punctuation

  • Updates special characters to UTF-8 format

  • Hashes data as SHA-256

First Name

FN

This key supports special characters and non-Roman alphabet characters. For this key, the connector:

  • Trims leading and trailing whitespace

  • Converts to lower-case

  • Removes punctuation

  • Updates special characters to UTF-8 format

  • Hashes data as SHA-256

First Initial

FI

Warning

Do not pass this column. The connector will use the first character of the normalized first name.

US States

ST

A two-character ANSI abbreviation code for US states.

For this key, the connector:

  • Trims leading and trailing whitespace

  • Converts to lower-case

  • Normalizes states located outside of the United States

  • Removes punctuation, special characters, and whitespace

  • Hashes data as SHA-256

City

CT

For this key, the connector:

  • Trims leading and trailing whitespace

  • Converts to lower-case

  • Removes punctuation, special characters, and whitespace

  • Hashes data as SHA-256

Zip Code

ZIP

Use only the first five digits for the United States. Use postcodes (area, district, sector) format for United Kingdom.

For this key, the connector:

  • Trims leading and trailing whitespace

  • Converts to lower-case

  • Removes whitespace from lower-case for United Kingdom

  • Trims to five digits for United States

  • Hashes data as SHA-256

Country Code

COUNTRY

A two-letter country code in ISO 3166-1 alpha-2 format.

For this key, the connector:

  • Trims leading and trailing whitespace

  • Converts to lower-case

  • Hashes data as SHA-256

Mobile Advertiser ID

MADID

For this key, the connector:

  • Trims leading and trailing whitespace

  • Converts to lower-case

  • Keeps hyphens

  • Hashes data as SHA-256

Conversions API parameters

The following table describes each of the parameters that are required by Meta Ads Manager for offline events. The final row lists the optional fields your brand may include to extend the customer profile information that is associated with offline events that are returned by the query and sent to Meta Ads Manager.

The fields are listed alphabetically, but may be returned by a query in any order.

Field name

Description

action_source

Optional

Action sources group offline events into categories and enable ad measurmeent and custom audience creation abilities from within the Meta Ads Manager user interface.

Add action_source to your query and then set a value:

,action_source AS 'physical_store'

The value for action_source must be one of the following:

app

Use when the offline conversion was made from a mobile app.

business_messaging

Use when the offline conversion was made from ads associated with Facebook Messenger, Instagram, or WhatsApp.

chat

Use when the offline conversion was made over a messaging app, SMS, or online messaging feature.

email

Use when the offline conversion happened over email.

other

Use when the offline conversion occurred by some other workflow.

phone_call

Use when the offline conversion was made over a phone call.

physical_store

Default Use when the offline conversion was made in-person at a physical store location.

system_generated

Use when the offline conversion occurred automatically, such as from a subscription renewal or monthly auto-pay.

website

Use when the offline conversion was made on a website.

The value for action_source is used by the Conversions API to categorize offline conversions within the Meta Ads Manager user interface and may not be customized. Use the action source that best associates how your brand wants to use offline conversions within Meta Ads Manager.

When action_source is not specified the default value is “physical_store”.

currency

Required

A value for currency is required by the Conversions API for offline events. Currency must be a valid ISO 4217 three-digit currency code, such as “USD” (United States dollar), “AUD” (Australian dollar), “CAD” (Canadian dollar), “EUR” (Euro), “JPY” (Japanese yen) or “MXN” (Mexican peso).

Add currency to your query, and then set a value:

,'USD' AS currency

Note

When viewing parameters in the Meta Ads Manager user interface, price, quantity, and currency are combined to be shown as value, which represents the sum of price times quantity, shown in the currency used for the transaction.

email and/or phone

Required

You must send an email address or a phone number to Meta Ads Manager; you may configure the query to send both.

Add at least one of email or phone to your query:

,c360.email AS email
,c360.phone AS phone

Note

Amperity performs the same actions for email addresses and phone numbers when sending to the Conversions API as when sending to the Marketing API.

event_name

Optional

Identifies an offline event within Meta Ads Manager.

Note

The default value for event_name is “Purchase”.

This value may be set to one of: “ViewContent”, “Search”, “AddToCart”, “AddToWishlist”, “InitiateCheckout”, “AddPaymentInfo”, “Purchase”, “Lead”, or “Other”.

external_id

Recommended

The amperity_id field MUST be renamed to extern_id.

Add extern_id to your query:

,c360.amperity_id AS extern_id

Note

Amperity performs the same actions for the external ID when sending to the Conversions API as when sending to the Marketing API.

order_id

Optional

The order ID that is associated with the offline event.

When transactions data is available

Use the Order ID field that is available from the Unified Itemized Transactions table:

,uit.order_id AS order_id

Important

The number of rows that results from the query will not be the same as the number of events that are uploaded to Meta Ads Manager.

This is because itemized transactions within the query are grouped by Order ID as the data is sent to Meta Ads Manager.

Grouping by Order ID ensures that individual events are combined to describe a complete transaction.

Amperity performs the GROUP BY action automatically if a GROUP BY clause is not set to “order_id”.

phone

See email.

price

Required

The price that is associated with the offline event.

Note

When viewing parameters in the Meta Ads Manager user interface, price, quantity, and currency are combined to be shown as value, which represents the sum of price times quantity, shown in the currency used for the transaction.

When transactions data is available

Calculate price by dividing item revenue by item quantity. These fields are available from the Unified Itemized Transactions table:

,CAST(
  uit.item_revenue / uit.item_quantity AS DOUBLE
) AS price

product_id

Optional

A unique product identifier that can be associated with the offline event.

When transactions data is available

Use the Product ID field that is available from the Unified Itemized Transactions table:

,uit.product_id AS product_id

quantity or value

Required

A field that describes a quantity or a value amount associated with the offline event.

Note

When viewing parameters in the Meta Ads Manager user interface, price, quantity (or value), and currency are combined to be shown as value, which represents the sum of price times quantity, shown in the currency used for the transaction.

When transactions data is available

Use the Item Quantity field from the Unified Itemized Transactions table to define quantity:

,uit.item_quantity AS quantity

timestamp

Required

A Unix timestamp (in seconds) that indicates when the offline event occurred.

Note

When viewing parameters in the Meta Ads Manager user interface, timestamp is shown as event_time.

When transactions data is available

Use the Order Datetime field from the Unified Itemized Transactions table to define timestamp:

,uit.order_datetime AS timestamp

Use a WHERE clause to limit the number of days to a maximum of seven:

WHERE uit.order_datetime > (
  CURRENT_DATE - interval '7' day
)

value

See quantity.

Optional profile attributes

You may include any of the profile attributes that are supported by the Marketing API, including Gender, Birthdate, First Name, Last Name, City, State, Zip Code, and Country Code.