Send offline conversion events to Snapchat

The Snapchat Conversions API lets your brand send offline conversion events — such as in-store purchases — to Snapchat. Snapchat uses these events to attribute conversions that happen away from your website to your campaigns, and to optimize campaigns against them.

Send offline conversion events to Snapchat to attribute in-store and other offline purchases to your Snapchat campaigns, and to optimize those campaigns against conversions that happen away from your website. Each row returned by your query is sent as one offline event, except PURCHASE rows that share an order_id, which are combined into one event. Each event carries an event type, an event timestamp, and one or more customer identifiers.

Amperity hashes customer identifiers with SHA-256 before sending them. The one exception is the mobile advertising ID (madid), which Snapchat requires unhashed; Amperity lowercases it and sends it as is. See the Snapchat Conversions API reference for more information.

Note

This topic describes the connector that sends offline conversion events directly to the Snapchat Conversions API. To build a CSV file of offline conversions and upload it to Snapchat yourself, see Send offline conversions to Snapchat. The two paths require different queries. The query on that page returns pre-hashed values and the column names that Snapchat requires in an uploaded file. The connector hashes values itself, so a pre-hashed value sent through the connector is hashed twice and cannot be matched.

Caution

The values for email, phone, fn, ln, ge, ct, st, zp, country, and external_id sent to Snapchat are SHA-256 hashed automatically by Amperity before sending. Do not use the TO_HEX() function with the email, phone, fn, ln, ge, ct, st, zp, country, and external_id fields for queries that return results for Snapchat.

Caution

This destination is available for sending query results to Snapchat after it is configured by a Datagrid Operator or your Amperity representative.

If this destination cannot be selected for orchestrations ask your Datagrid Operator or Amperity representative to configure a destination for sending query results to Snapchat.

Build a query

Use a query to build a combination of data — typically from the Merged Customers and Unified Itemized Transactions tables — that returns offline conversion events to send to Snapchat. Each row must include an event timestamp and at least one customer identifier. Adding identity columns such as name, city, state, and postal code can improve the match rate, and event attributes such as conversion value, currency, and order ID are sent with each event.

Review the Conversions API parameters section for the columns your query must and may return, and the Supported event types section for the allowed event_name values.

Important

Amperity adds the following parameters and applies values automatically:

• action_source is automatically set to OFFLINE

• event_id is an automatically generated SHA-256 hash of event_name, event_time, and the customer identifier, unless the query returns an event_id column. For a combined purchase event, the order_id is used instead

• event_tag is applied from the Event Tag setting, when one is configured

Bound the query to recent events so each orchestration sends new conversions instead of re-sending the full transaction history; choose a window that matches how often the orchestration runs. Snapchat does not attribute events that are more than 28 days old, and Amperity does not send them.

You can return one row per order, or one row per line item and let Amperity combine them. Both produce the same value for an order.

Order-level events

A query that returns one row per order aggregates the line items itself. A query that returns a collection of recent in-store purchase events for use in Snapchat is similar to:

SELECT
  LOWER(TRIM(mc.email))                                 AS email       -- mc.email
  ,uit.order_id                                         AS order_id    -- uit.order_id
  ,uit.order_id                                         AS event_id    -- uit.order_id, a stable deduplication key
  ,CAST(SUM(uit.item_revenue) AS DECIMAL(18,2))         AS value       -- uit.item_revenue is a line total, so SUM is the order total
  ,'USD'                                                AS currency
  ,CAST(TO_UNIXTIME(MAX(uit.order_datetime)) AS BIGINT) AS event_time  -- uit.order_datetime, as Unix seconds
  ,'PURCHASE'                                           AS event_name
FROM Merged_Customers mc
JOIN Unified_Itemized_Transactions uit ON mc.amperity_id = uit.amperity_id
WHERE mc.email IS NOT NULL
  AND uit.purchase_channel = 'STORE'
  AND uit.order_datetime >= CURRENT_DATE - INTERVAL '2' day
  AND COALESCE(uit.is_return, false) = false
  AND COALESCE(uit.is_cancellation, false) = false
  AND uit.item_quantity > 0
  AND uit.item_revenue > 0
GROUP BY 1, 2

Note

The values in purchase_channel vary by tenant. Review the values in your own Unified Itemized Transactions table to identify the ones that represent offline purchases.

Caution

Use COALESCE when filtering is_return and is_cancellation. Both columns are null when the status is unknown, and a bare is_return = false comparison drops those rows instead of keeping them.

Line-item events

A query that returns one row per line item omits the aggregation. Amperity combines the rows that share an order_id into a single event, sums value across them, and sends each row’s value as an item entry with the event:

SELECT
  LOWER(TRIM(mc.email))                            AS email       -- mc.email
  ,uit.order_id                                    AS order_id    -- uit.order_id
  ,CAST(uit.item_revenue AS DECIMAL(18,2))         AS value       -- uit.item_revenue, the total for this line item
  ,'USD'                                           AS currency
  ,CAST(TO_UNIXTIME(uit.order_datetime) AS BIGINT) AS event_time  -- uit.order_datetime, as Unix seconds
  ,'PURCHASE'                                      AS event_name
FROM Merged_Customers mc
JOIN Unified_Itemized_Transactions uit ON mc.amperity_id = uit.amperity_id
WHERE mc.email IS NOT NULL
  AND uit.purchase_channel = 'STORE'
  AND uit.order_datetime >= CURRENT_DATE - INTERVAL '2' day
  AND COALESCE(uit.is_return, false) = false
  AND COALESCE(uit.is_cancellation, false) = false
  AND uit.item_quantity > 0
  AND uit.item_revenue > 0

Important

Return either one row per order or one row per line item, not both. Returning a pre-aggregated order total alongside the line items for the same order reports that order’s revenue more than once.

How purchase events are grouped

When a query returns both an event_name and an order_id column, Amperity combines PURCHASE rows that share an order_id into a single event before sending it to Snapchat. This lets a query return one row per line item without reporting one conversion per item.

For a combined event:

• value is the sum of value across the grouped rows.

• event_time is the earliest event_time among them.

• currency is taken from one of the grouped rows, so return the same currency for every row of an order.

• Each grouped row’s value is sent as one entry in the event’s item contents. Because value is a line total, each entry is the line total rather than a unit price. No quantity or product ID is sent.

• Customer identifiers are taken from one of the grouped rows, not combined. Return the same identifiers on every row of an order. If the row used has no usable identifier, the order is not sent and is reported as “PURCHASE event stale or missing identifier”.

• event_id is the order_id when event_id is not mapped.

Rows are sent one event per row when any of the following is true:

• The query does not return both event_name and order_id.

• The event is not a PURCHASE.

• The row is a PURCHASE with an empty order_id.

• The row’s event_name is empty and the event type comes from the Default Event Name setting, even when that setting is PURCHASE.

Deduplication and query lookback

Snapchat deduplicates events by event_id, but only for a limited time after it first receives an event:

• A PURCHASE event that carries an order_id is deduplicated for 30 days.

• ADD_CART and PAGE_VIEW events are deduplicated for only 1 second.

• Every other event is deduplicated for 48 hours.

Amperity keeps no record of which events it has already sent, so an orchestration that re-reads the same rows sends them again. Snapchat discards the repeats while the window is open and counts them as new conversions once it has closed.

This matters when runs overlap — when a query looks back further than the time between orchestration runs. A query with a seven-day lookback that runs daily sends each event in seven consecutive runs. For most events, every send after the first 48 hours is counted again. For ADD_CART and PAGE_VIEW, every send after the first is counted again.

To avoid double counting, keep the query lookback shorter than the deduplication window that applies to the events you send, or schedule runs so they do not overlap. For ADD_CART and PAGE_VIEW, the window is too short to rely on, so make sure no two runs return the same event.

Note

When the events in a run span more than 48 hours, Amperity reports a warning on the orchestration naming the span. PURCHASE events that carry an order_id are excluded from that span, because Snapchat deduplicates them for 30 days. The warning does not account for the 1-second window that applies to ADD_CART and PAGE_VIEW, so overlapping runs that send those events can double count without a warning.

Supported event types

event_name must match one of the values below. Matching is case-insensitive, so purchase and PURCHASE are both accepted and are sent as PURCHASE.

A query does not have to return event_name. When it is not returned, every row is sent using the Default Event Name setting. A row whose event_name is empty or unrecognized is also sent using the Default Event Name setting, so a misspelled value is sent as the default event type rather than reported. When there is no usable event_name and no Default Event Name is configured, the row is not sent and is reported as failed.

Supported event_name values (31)

ACHIEVEMENT_UNLOCKED, AD_CLICK, AD_VIEW, ADD_BILLING, ADD_CART, ADD_TO_WISHLIST, APP_INSTALL, APP_OPEN, COMPLETE_TUTORIAL, CUSTOM_EVENT_1, CUSTOM_EVENT_2, CUSTOM_EVENT_3, CUSTOM_EVENT_4, CUSTOM_EVENT_5, INVITE, LEVEL_COMPLETE, LIST_VIEW, LOGIN, PAGE_VIEW, PURCHASE, RATE, RESERVE, SAVE, SEARCH, SHARE, SIGN_UP, SPENT_CREDITS, START_CHECKOUT, START_TRIAL, SUBSCRIBE, VIEW_CONTENT

Add orchestration

An orchestration defines the relationship between query results and a destination, including the destination and the frequency at which an orchestration runs.

To add an orchestration

1. Open the Activations page, select the Orchestrations tab, and then click the Add orchestration button. This opens the Add orchestration dialog box.

2. From the Object type dropdown, select Query.

3. From the Object dropdown, select the query for which results is sent to Snapchat.

4. From the Destination dropdown, select a destination that is configured for sending data to Snapchat.

5. Verify all settings.

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

7. Click Save.

Run orchestration

Run the orchestration manually to validate that it works.

To run the orchestration

1. Open the Activations page, select the Orchestrations tab, and then open the menu for the Snapchat orchestration. Select Run.

2. The Status column for the orchestration updates to say “Waiting to start”, after which the notifications pane updates to include a notification that shows the current status.

3. When the orchestration has run successfully, the status is updated to “Completed”.

Data validation

Amperity validates the columns your query returns before the orchestration runs, and then validates each row before sending it.

Before the run starts

The orchestration fails and no events are sent when:

• event_time is not returned by the query. The workflow reports “event_time must be selected as a Destination Attribute.”

• event_time is not an integer column. Convert a date or datetime column to Unix seconds, for example with CAST(TO_UNIXTIME(uit.order_datetime) AS BIGINT).

• None of email, phone, or madid is returned by the query.

• email, phone, or event_name is returned as something other than a string column.

For each row

Amperity drops rows that Snapchat would reject, so that one invalid row does not cause an entire batch of events to be rejected. Dropped rows are reported as failed with the reason. A row is dropped when:

• event_time is empty.

• The event is more than 28 days old. Snapchat attributes offline events within a 28-day window and cannot attribute events outside it.

• event_name is empty or unrecognized and no Default Event Name is configured.

• None of email, phone, or madid holds a usable value. A phone number that cannot be parsed does not count as a usable identifier.

Optional identity columns that cannot be used — such as an unrecognized ge value — are dropped from that row individually and the rest of the event is still sent.

After the events are sent

Snapchat validates the events it receives. When it rejects part of a batch, the rejected events are reported as failed and the rest of the batch is sent. When it rejects an entire batch, every event in that batch is reported as failed with the reason Snapchat returned.

Caution

When more than 90 percent of the events in the first five batches are rejected, the orchestration stops and reports an error. This indicates that a column is mapped incorrectly rather than that individual rows are bad. Review the email, phone, and event_name values returned by your query. A batch holds up to 2,000 events, so a run that sends fewer than five batches is not checked.

Conversions API parameters

The following table describes each column Amperity sends to Snapchat. A query must return columns with the same name as listed in the “Amperity name” column; Amperity maps them to the Snapchat field names automatically. Return the column names in lowercase, exactly as listed in the “Amperity name” column.

Important

A query must return event_time, along with at least one of email, phone, or madid. All other columns are optional. Snapchat requires value and currency for PURCHASE events; currency can come from the Default Currency setting instead of the query.

Amperity name	Snapchat field	Source column	Description
event_time	event_time	order_datetime	Required When the event occurred, as a Unix timestamp in seconds. The column must be an integer; convert a datetime column with CAST(TO_UNIXTIME(uit.order_datetime) AS BIGINT). Do not return milliseconds: Amperity’s 28-day check and lookback warning assume seconds. Events more than 28 days old are not sent.
email	em	email	Required, unless phone or madid is returned. An email address. Amperity trims and lowercases the value and applies SHA-256 hashing before sending. Return the raw address.
phone	ph	phone	Required, unless email or madid is returned. A phone number. Amperity normalizes the number to E.164, removes the leading +, and applies SHA-256 hashing before sending. A number without a country code is treated as a US number, so include the country code for numbers outside the US. A number that cannot be parsed is dropped.
madid	madid	—	Required, unless email or phone is returned. A mobile advertising ID (IDFA on iOS, GAID on Android). Amperity trims and lowercases the value and sends it unhashed, as Snapchat requires. Return the raw ID; do not hash it.
event_name	event_name	—	Optional The type of conversion event, such as PURCHASE. Must be one of the 31 supported values (see Supported event types). When the query does not return it, or a row’s value is empty or unrecognized, the Default Event Name setting is used.
value	value	item_revenue	Optional. Snapchat requires it for PURCHASE events. The monetary value of the event. This is a total, not a per-unit price: return the total for the order, or the total for the line item and let Amperity sum them. Do not return a per-unit price.
currency	currency	—	Optional. Snapchat requires it for PURCHASE events. The currency for value, as a three-character ISO 4217 code such as USD. When the query does not return it, or a row’s currency is empty, the Default Currency setting is used. Note The currency column in Unified Itemized Transactions holds descriptive values such as “dollar” rather than ISO 4217 codes. Return a literal code, or map your values to codes in the query.
order_id	order_id	order_id	Optional The order identifier. When the query also returns event_name, PURCHASE rows sharing an order_id are combined into one event (see How purchase events are grouped).
event_id	event_id	order_id	Optional A unique identifier Snapchat uses to deduplicate events. Return a stable value such as the order ID so that repeated sends of the same event are not counted twice. When the query does not return it, Amperity generates one from the event name, event time, and identifier; for a combined purchase event, the order_id is used.
fn	fn	given_name	Optional A first name. Amperity lowercases the value, removes punctuation, and applies SHA-256 hashing before sending.
ln	ln	surname	Optional A last name. Amperity lowercases the value, removes punctuation, and applies SHA-256 hashing before sending.
ge	ge	gender	Optional A gender identifier. Amperity accepts m, male, man, and 1 for male, and f, female, woman, w, and 2 for female, and applies SHA-256 hashing before sending. Any other value is dropped from the row.
ct	ct	city	Optional A city. Amperity lowercases the value, removes spaces and punctuation, and applies SHA-256 hashing before sending.
st	st	state	Optional A state. Amperity normalizes a full state name to its two-letter code and applies SHA-256 hashing before sending.
zp	zp	postal	Optional A postal code. Amperity removes spaces and hyphens, shortens a value of six or more digits to its first five, and applies SHA-256 hashing before sending.
country	country	country	Optional A country. Amperity normalizes a full country name to its two-letter ISO code and applies SHA-256 hashing before sending.
external_id	external_id	loyalty_id	Optional Your own identifier for the customer, such as a loyalty ID. Amperity applies SHA-256 hashing before sending.

Note

fn, ln, ge, ct, st, zp, country, and external_id are sent when your query returns them, but they are not checked before the orchestration runs. A misspelled column name for one of these is not reported as an error; the column is simply not sent. Return each of them as a string column: a numeric column, such as ge returned as 1 or 2, causes the orchestration to fail.