Build audiences using SQL

Use the SQL audience extension to build audiences with data from the data lake, including any existing dimension entities (such as customer attributes or product information).

Using this SQL extension improves your ability to create audiences as you don’t need raw data in your profiles when defining audience segments. Audiences created using this method are automatically registered in the Audience workspace, where you can further target them to file-based destinations.

This document covers how to use the SQL audience extension in Adobe Experience Platform’s Data Distiller to create, manage, and publish audiences using SQL commands.

Audience creation lifecycle in Data Distiller audience-creation-lifecycle

Follow these steps to create, manage, and activate your audiences. Created audiences integrate seamlessly into the ‘audience flow,’ so you can build segments from base audiences and target file-based destinations (for example, CSV uploads or cloud storage locations) for customer outreach. ‘Audience flow’ refers to the complete process of creating, managing, and activating audiences, ensuring seamless integration across destinations.

As part of your ‘audience flow,’ use the following SQL commands to create, modify, and delete audiences within Adobe Experience Platform.

Create an audience create-audience

Use the CREATE AUDIENCE AS SELECT command to define a new audience. The created audience is saved in a dataset and registered in the Audiences workspace under Data Distiller.

CREATE AUDIENCE table_name
WITH (primary_identity='IdentitycolName', identity_namespace='Namespace for the identity used', [schema='target_schema_title'])
AS (select_query)

Parameters

Use these parameters to define your SQL audience creation query:

Example:

The following example demonstrates how to structure your SQL audience creation query:

CREATE Audience aud_test
WITH (primary_identity=userId, identity_namespace=lumaCrmId)
AS SELECT userId, orders, total_revenue, recency, frequency, monetization FROM profile_dim_customer;

In this example, the userId column is identified as the identity column, and an appropriate namespace (lumaCrmId) is assigned. The remaining columns (orders, total_revenue, recency, frequency, and monetization) are enriched attributes that provide additional context for the audience.

Limitations:

Be aware of the following limitations when using SQL for audience creation:

Add profiles to an existing audience add-profiles-to-audience

Use the INSERT INTO command to add profiles (or entire audiences) to an existing audience.

INSERT INTO table_name
SELECT select_query

Parameters

The table below explains the parameters required for the INSERT INTO command:

Example:

The following example demonstrates how to add profiles to an existing audience with the INSERT INTO command:

INSERT INTO Audience aud_test
SELECT userId, orders, total_revenue, recency, frequency, monetization FROM customer_ds;

RFM model audience example rfm-model-audience-example

The following example demonstrates how to create an audience using the Recency, Frequency, and Monetization (RFM) model. This example segments customers based on their recency, frequency, and monetization scores to identify key groups, such as loyal customers, new customers, and high-value customers.

The following query creates a schema for the RFM audience. The statement sets up fields to hold customer information such as userId, days_since_last_purchase, orders, total_revenue, and so on.

CREATE Audience adls_rfm_profile
WITH (primary_identity=userId, identity_namespace=lumaCrmId) AS
SELECT
    cast(NULL AS string) userId,
    cast(NULL AS integer) days_since_last_purchase,
    cast(NULL AS integer) orders,
    cast(NULL AS decimal(18,2)) total_revenue,
    cast(NULL AS integer) recency,
    cast(NULL AS integer) frequency,
    cast(NULL AS integer) monetization,
    cast(NULL AS string) rfm_model
WHERE false;

After creating the audience, populate it with customer data and segment the profiles based on their RFM scores. The SQL statement below uses the NTILE(4) function to rank customers into quartiles based on their RFM (Recency, Frequency, Monetization) scores. These scores categorize customers into six segments, such as ‘Core,’ ‘Loyal,’ and ‘Whales.’ The segmented customer data is then inserted into the audience adls_rfm_profile table."

INSERT INTO Audience adls_rfm_profile
SELECT
    userId,
    days_since_last_purchase,
    orders,
    total_revenue,
    recency,
    frequency,
    monetization,
    CASE
        WHEN Recency=1 AND Frequency=1 AND Monetization=1 THEN '1. Core - Your Best Customers'
        WHEN Recency IN(1,2,3,4) AND Frequency=1 AND Monetization IN (1,2,3,4) THEN '2. Loyal - Your Most Loyal Customers'
        WHEN Recency IN(1,2,3,4) AND Frequency IN (1,2,3,4) AND Monetization=1 THEN '3. Whales - Your Highest Paying Customers'
        WHEN Recency IN(1,2,3,4) AND Frequency IN(1,2,3) AND Monetization IN(2,3,4) THEN '4. Promising - Faithful Customers'
        WHEN Recency=1 AND Frequency=4 AND Monetization IN (1,2,3,4) THEN '5. Rookies - Your Newest Customers'
        WHEN Recency IN (2,3,4) AND Frequency=4 AND Monetization IN (1,2,3,4) THEN '6. Slipping - Once Loyal, Now Gone'
    END AS rfm_model
FROM (
    SELECT
        userId,
        days_since_last_purchase,
        orders,
        total_revenue,
        NTILE(4) OVER (ORDER BY days_since_last_purchase) AS recency,
        NTILE(4) OVER (ORDER BY orders DESC) AS frequency,
        NTILE(4) OVER (ORDER BY total_revenue DESC) AS monetization
    FROM (
        SELECT
            userid,
            DATEDIFF(current_date, MAX(purchase_date)) AS days_since_last_purchase,
            COUNT(purchaseid) AS orders,
            CAST(SUM(total_revenue) AS double) AS total_revenue
        FROM (
            SELECT DISTINCT
                ENDUSERIDS._EXPERIENCE.EMAILID.ID AS userid,
                commerce.`ORDER`.purchaseid AS purchaseid,
                commerce.`ORDER`.pricetotal AS total_revenue,
                TO_DATE(timestamp) AS purchase_date
            FROM sample_data_for_ootb_templates
            WHERE commerce.`ORDER`.purchaseid IS NOT NULL
        ) AS b
        GROUP BY userId
    )
);

Delete an audience (DROP AUDIENCE) delete-audience

Use the DROP AUDIENCE command to delete an existing audience. If the audience does not exist, an exception occurs unless IF EXISTS is specified.

DROP AUDIENCE [IF EXISTS] [db_name.]table_name

Parameters

The table contains the parameters required for the DROP AUDIENCE command:

Example:

The following example demonstrates how to delete an audience using the DROP AUDIENCE command:

DROP AUDIENCE IF EXISTS aud_test;

Automatic audience registration and availability registration-and-availability

Audiences created using the SQL extension are automatically registered under the Data Distiller Origin in the Audience workspace. Once registered, these audiences are available for targeting in file-based destinations, enhancing segmentation and targeting strategies. This process requires no additional configuration, streamlining audience management. For more details on how to view, manage, and create audiences within the Platform UI, see the Audience Portal overview.

Activate audiences to destinations activate-audiences

Activate your audiences by targeting them to any file-based destination, such as Amazon S3, SFTP, or Azure Blob. The enriched audience attributes are available for further refinement and filtering as needed.

Feature clarifications faqs

This section addresses frequently asked questions about creating and managing external audiences using SQL in Data Distiller.

Questions:

Next steps

After reading this document, you have learned how to use the SQL audience extension in Data Distiller to effectively create, manage, and publish audiences using SQL commands. You can now customize audience definitions based on your unique business requirements and activate them across various destinations, optimizing your marketing strategies and data-driven decisions.

Next, you could read the following documentation to further develop and optimize your Platform audience management strategies: