Send query results to LiveRamp

LiveRamp allows clients to combine customer data from various online and offline sources, centering around the use of web cookies that allow websites to remember visitors.

You can associate records in Amperity to an audience in LiveRamp by sending a combination of identifier data and query data. LiveRamp uses:

  • Identifier data to match records to other identifiers in the LiveRamp Identity Graph.

  • Segment data to group records into segments based on certain attributes for downstream targeting, measurement, and personalization.

LiveRamp requires an audience ID. This may be a client customer ID from another application, it may be the LiveRamp audience ID itself (from LiveRamp data that was imported to Amperity), or it may be the Amperity ID.

This topic describes the steps that are required to send CSV, TSV, or PSV files to LiveRamp from Amperity:

  1. Build a query

  2. Add orchestration

  3. Run orchestration

Note

LiveRamp must be enabled before you can configure an orchestration to send query results.

Build query

You may need to build a query that shapes the data so that fields in the output are mapped to the fields required by LiveRamp. Data shaping is typically required only one time.

You can do this in the following ways:

  1. Add a SQL table (using Spark SQL) to the customer 360 database that selects PII fields from the Customer 360 table, and then outputs them to a table with columns that map to the required naming patterns.

    Note

    PII fields may require SHA-256 hashing or conversion to a UUID using the CAST() function.

  2. Add a SQL query (using Presto SQL) that filters within the query, and then outputs results that map to the required patterns.

    Note

    PII fields may require SHA-256 hashing or conversion to a UUID using the CAST() function.

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

Important

Uploads to LiveRamp may fail when:

  • The headers in the output file do not match the headers in LiveRamp.

  • Two (or more) headers in the output file are identical.

  • The audience key in the output file does not match the audience key in LiveRamp.

  • The audience key is missing.

  • Mismatched identifiers. For example, if LiveRamp expects first and last names to be in the same field, but the output contains first and last names in separate fields.

LiveRamp and PII data

LiveRamp terms of service restrict PII data – government-issued identification numbers (like social security numbers), financial and customer account numbers, birthdates, gender, email addresses, names (first, last, and full), and similar data – should be excluded from query data that is sent to LiveRamp.

Because the data sent to LiveRamp contains the Amperity ID, should you need to associate data with PII, you can use other systems that allow PII data along with the Amperity ID to make these associations.

Hash fields that must be sent to LiveRamp by using one-way SHA-256 hashing or by casting the value to a random unique identifier (UUID).

One-way SHA-256 hash

Apply one-way SHA-256 hashes to fields that contain PII data. A one-way hash ensures that data can no longer be recognizable as valid PII, yet still allows that data to applied to segments that report on users who were flagged for removal or deletion and helps ensure that removed or obfuscated data is not sent from Amperity.

A one-way SHA-256 hash has the following syntax:

TO_HEX(SHA256(TO_UTF8(UPPER(TRIM(FIELD)))))

and uses the following Presto SQL functions:

  • TRIM() removes whitespace from the field.

  • UPPER() sets all characters to upper-case.

  • TO_UTF8() converts the data into a binary format.

  • SHA256() hashes data with a one-way SHA-256 hash.

  • TO_HEX() converts the binary data into a string.

For example:

TO_HEX(SHA256(TO_UTF8(UPPER(TRIM(email)))))

CAST as UUID

Use the CAST(value AS type) function to cast the value of value as type.

For example:

,CAST(email AS UUID) AS "EMAIL1"

Map to audience

A SQL query can output results that map to an audience in LiveRamp. Add a SQL query with a SELECT statement similar to:

SELECT
  amperity_id AS CCID
  ,REGEXP_REPLACE(given_name, '[*@/.,_-]', '') AS FIRSTNAME
  ,REGEXP_REPLACE(surname, '[*@/.,_-]', '') AS LASTNAME
  ,address AS ADDRESS1
  ,address2 AS ADDRESS2
  ,city AS CITY
  ,UPPER(state) AS ST
  ,REGEXP_EXTRACT(postal,'^(\d{5})') AS ZIP
  ,CONCAT('+1','',REGEXP_REPLACE(phone,'[\D]','')) AS PHONE1
FROM customer360
LIMIT 2000

and then assign this query to a destination that sends results to LiveRamp.

Note

The CCID field is optional, but may be used as an audience key. The example shows using the Amperity ID as the audience key.

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.

Important

Sending data to LiveRamp should follow recommendations for uploading data. Review LiveRamp file limitations. LiveRamp prefers GPG encryption and GZip compression. LiveRamp supports the following file formats:

  • CSV

  • PSV

  • TSV

Enable the Include header row in output files option for the Amperity destination.

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 LiveRamp.

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

  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.

To run the orchestration

  1. From the Destinations tab, under Orchestrations, open the    menu for the LiveRamp orchestration, and then select Run.

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

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