Send query results to Optimizely

Optimizely is an experimentation platform for testing, learning, and deploying positive digital experiences.

This topic describes the steps that are required to send CSV files to Optimizely from Amperity:

  1. Build a query

  2. Add orchestration

  3. Run orchestration

Note

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

Build query

You will need to build a query that can send results to Optimizely as a CSV file. This may be done for one of the following typically done for one of the following types:

Warning

Queries and database exports from Amperity sent to |destination-name| should not include PII data.

Optimizely and PII data

Optimizely terms of service prohibit PII data – names, social security numbers, email addresses, and similar data – from being collected by or sent to Optimizely services for use with customer profiles or any other feature. It is important to ensure that queries and database exports from Amperity comply with requirements for using Optimizely services.

Because the data sent to Optimizely 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.

If email addresses or other types of fields that contain PII must be provided, use one-way SHA-256 hashing or cast 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.

The following example applies a one-way SHA-256 hash to email addresses:

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.

The following example casts email addresses as a UUID:

,CAST(email AS UUID) AS "email_address"

Customer profiles

customer profiles are a collection of customer attributes, such as demographic data, behavioral characteristics, or any other information particular to your industry and customers. Customer profiles provide a consolidated, dynamic view of your customers, enabling you to refine this view as you obtain more information and to take action based on this view.

You can export any combination of attributes from Amperity for use with customer profiles. This requires exporting an ID that is mapped to the Optimizely customerId field, after which you can join the Amperity ID, and any collection of customer profile attributes to that output result, before sending to Optimizely.

Customer profile requirements

Data that is output from Amperity and sent to Optimizely must be shaped to the following requirements:

  1. The header row of the CSV file must include a column named customerId. This name is not case-sensitive. All rows in the CSV file must contain a valid value for this column. The value of this column should be unchanged from the original value. For example. if the customer ID field is a UUID in Amperity, then it should be sent to Optimizely as the same UUID.

  2. Each column in the header row must be a registered attribute name in Optimizely. A CSV may contain a subset of the registered attributes, it does not need to contain all of the registered attributes. There is no requirement for a minimum number of registered attributes.

  3. Each column header for a registered attribute in the CSV file must correspond to the correct data type in Optimizely and attribute name.

    Note

    If a column header doesn’t correspond to a registered attribute name, the upload will fail. If an attribute value doesn’t respect the attribute’s data type and format, the upload will fail.

Map ID to Optimizely

Optimizely requires data to contain a customerID field. This field should not be associated with the Amperity ID, but instead be associated with a field that Optimizely is already aware of by way of other data sources.

  1. Choose an ID that is available in the Amperity customer 360 database that IS NOT the Amperity ID.

  2. Build a query that outputs that ID as the customerId field for Optimizely.

  3. Add any other attributes you want to that data. Each of these attributes should be mapped in the query to a known field in Optimizely.

  4. Any number of fields in Amperity, from any number of individual tables, can be mapped to fields in Optimizely.

  5. Output the results of this query to Optimizely as a CSV file using Amazon S3 and the customer’s Optimizely credentials.

The customerID must be at least 8 characters. Use a CASE statement to ensure that the ID that is associated to the customerId in Optimizely is at least 8 characters. For example:

CASE
  WHEN LENGTH(id_from_amperity_table) < 8
  THEN lpad(id_from_amperity_table, 8, '0')
  ELSE id_from_amperity_table
END AS "customerId"

and then join attributes to this ID, including the Amperity ID (as an attribute in the output).

Define a query

The following SELECT statement collects all the distinct IDs from Table O to associate with the Optimizely customerId, ensures they are at least 8 characters, collects attributes from the customer 360 profile (represented by C) and Table A (represented by A), and then joins the attributes, along with the Amperity ID to a table that uses the customerId as the unique ID.

Important

For each row in the example, the field names that are represented by AS “Optimizely_abc” and AS “Optimizely_123” represent the field names to which the query maps data as they are defined in Optimizely. These must match.

SELECT DISTINCT
  CASE
    WHEN LENGTH(id_to_associate_to_customerId) < 8
    THEN lpad(id_to_associate_to_customerId, 8, '0')
    ELSE id_to_associate_to_customerId
  END AS "customerId"

  ,C.amperity_id AS "Optimizely_ampID"
  ,C.given_name AS "Optimizely_FirstName"
  ,C.gender AS "Optimizely_Gender"
  ,C.city AS "Optimizely_City"
  ,C.state AS "Optimizely_State"
  ,C.postal AS "Optimizely_Postal"
  ,C.country AS "Optimizely_Country"
  ,C.profile_attribute_a AS "Optimizely_A"
  ,C.profile_attribute_b AS "Optimizely_B"
  ,C.profile_attribute_c AS "Optimizely_C"

  ,A.pk AS "Optimizely_pk"
  ,A.guid AS "Optimizely_guid"
  ,A.country AS "Optimizely_country"
  ,A.table_attribute_1 AS "Optimizely_1"
  ,A.table_attribute_2 AS "Optimizely_2"
  ,A.table_attribute_3 AS "Optimizely_3"

FROM Customer360 C
JOIN TableO O ON O.amperity_id = C.amperity_id
LEFT JOIN TableA A ON A.amperity_id = C.amperity_id
WHERE O.id_to_associate_to_customerId IS NOT NULL

Note

There are many ways to build a SELECT or SELECT DISTINCT statement that maps data in the Amperity customer 360 database to fields in Optimizely. The only absolute requirement is to map an ID that is not the Amperity ID to the customerID field in Optimizely. Attributes from tables in Amperity that you want to send to Optimizely must be mapped to the field names as they are defined in Optimizely, but there is no requirement to map to any particular attribute.

List attributes

A list attributes targets users who are already part of a defined audience that exists in a downstream system that is not in Optimizely. All the list attribute requires is a CSV file that contains unique IDs in a single column. This may be the Amperity ID or it may be some other unique ID. The ID that is set to the Optimizely list attribute must be the ID that is required by the external downstream system.

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.

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

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

  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 Optimizely 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”.