Send query results to Vibes

Vibes is a mobile marketing company that provides a variety of mobile marketing products and services, such as text message marketing (SMS/MMS), mobile wallet marketing, push notifications, and mobile web experiences.

You can use a flat-file (CSV) batch process to send query results from Amperity to Vibes to update recipient list data. This process supports downstream workflows, such as for:

  • Users who interact with campaigns from mobile devices

  • Interactive campaigns, including incentives, contents, and info-on-demand

  • Loyalty programs and offers

Files that are sent to Vibes are routed based on a required filename pattern. After the files are processed, an SMS message or push notification is sent to the recipient list from the Vibes platform.

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

  1. Build a query

  2. Add orchestration

  3. Run orchestration

Note

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

Build query

You will need to build a query that shapes data to the fields required by a Vibes recipient list. A recipient list is customizable, which may require a unique query for each recipient list.

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

Choose the approach that works best for your use case (or use cases) and downstream workflows.

Map to Recipient List

A Vibes recipient list is a collection of references used as a target filter for messages. A recipient list must include one of the following fields that acts as the unique ID:

  1. person_id; must be lowercase

  2. person_key; must be lowercase

  3. external_person_id; a unique ID that exists in an external system

  4. mdn; the mobile directory number (MDN)

    Important

    The mobile directory number for phone numbers in the USA may use the 10-digit MDN format.

    The MDN for international numbers must be in E.164 format, which is a plus symbol ( + ) followed by the country code and phone number without spaces. For example: “+12205551234”.

The unique ID acts as the row identifier and rows may include additional columns that act as message variables.

A SQL query built using Presto SQL can return results for use with any Vibes recipient list. For a recipient list with custom fields, add a SQL table with a SELECT statement similar to:

SELECT
  amperity_id AS "external_person_id"
  phone AS "mdn"
  given_name AS "first_name"
  surname AS "last_name"
  loyalty_points as "points"
FROM customer360
LIMIT 2000

For a recipient list that applies personalization for first name and city:

SELECT
  amperity_id AS "external_person_id"
  phone AS "mdn"
  given_name AS "personalization_1"
  city AS "personalization_2"
FROM customer360
LIMIT 2000

For a recipient list that only requires a mobile device number:

SELECT
  phone-mobile AS "mdn"
FROM customer360
LIMIT 2000

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

Refer to the Vibes documentation if you plan to import a recipient list with included headers.

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

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

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