> For the complete documentation index, see [llms.txt](https://data-distilller.gitbook.io/adobe-data-distiller-guide/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://data-distilller.gitbook.io/adobe-data-distiller-guide/unit-6-data-distiller-audiences/draft-dda-202-data-distiller-audience-orchestration.md).

# \[DRAFT]DDA 202: Data Distiller Audience Orchestration

## Prerequisites

You can skip the remaining prerequisites if you've already followed the steps in the tutorial below

{% content-ref url="/pages/phtPT1n6vW0bdpg4uxzc" %}
[DDA 200: Build Data Distiller Audiences on Data Lake Using SQL](/adobe-data-distiller-guide/unit-6-data-distiller-audiences/dda-200-build-data-distiller-audiences-on-data-lake-using-sql.md)
{% endcontent-ref %}

If you have not done the above tutorial, you will need this to upload the test data:

{% content-ref url="/pages/18sUwAAMNfJUC8nrl4uh" %}
[PREP 500: Ingesting CSV Data into Adobe Experience Platform](/adobe-data-distiller-guide/prep-500-ingesting-csv-data-into-adobe-experience-platform.md)
{% endcontent-ref %}

We will be using the following data to create segments:

{% file src="/files/NjimCQuPgr65rToLUI7V" %}

## Retail Case Study: Optimizing Email Marketing Campaigns with Audience Segmentation and A/B Testing

In this use case, we aim to simulate and optimize an email marketing campaign by leveraging **audience segmentation**, **performance tracking**, and **A/B testing**. The primary goal is to improve customer engagement, maximize conversions, and refine campaign strategies based on real-time customer interactions.

**Key Marketing Objectives:**

1. **Campaign Performance Tracking**: Track and analyze key metrics such as email open rates, click-through rates, and bounce rates to assess campaign success.
2. **Customer Segmentation**: Segment the customer base into various categories, such as highly engaged customers, moderately engaged customers, and unengaged customers. This allows marketers to target their messaging more effectively.
3. **A/B Testing**: Perform A/B tests by splitting the audience into two groups and testing different versions of the email content (e.g., subject lines, calls to action). This helps identify which version performs better in terms of engagement and conversion.
4. **Improve Email Deliverability**: Track failed email deliveries and understand bounce reasons (soft or hard bounces) to optimize email lists and improve overall deliverability rates.
5. **Personalized Marketing**: Use engagement metrics (like open and click counts) to create personalized follow-up campaigns, offering exclusive deals or reminders based on customer interaction behavior.

**Specific Use Case:**

A retail brand is running a series of email marketing campaigns for its **Spring Sale**, **Holiday Offers**, and **New Arrivals**. The marketing team wants to:

1. **Identify High-Value Customers**: Focus on customers who have a high purchase frequency and loyalty score, engaging them with personalized offers.
2. **Segment the Audience Based on Engagement**: Create tailored messaging for those who have opened emails but haven't clicked (i.e., warm leads) vs. those who haven't engaged at all (cold leads).
3. **A/B Test Subject Lines**: Compare two email subject lines for the same campaign to see which one drives more engagement (open and click rates).
4. **Monitor and Reduce Email Bounces**: Track and reduce email bounces by analyzing hard and soft bounces to refine the email list and improve targeting.

**Expected Outcome:**

* **Higher Engagement**: By tracking open and click rates, the marketing team can focus on the most effective content, leading to higher engagement and ultimately increased sales.
* **Improved Targeting**: Customer segmentation based on interaction helps in tailoring future messages, leading to better personalization and increased likelihood of conversion.
* **Optimized Content**: A/B testing results will provide insights into what content or subject lines resonate most with the audience, enabling the brand to optimize its messaging.
* **Reduced Bounce Rates**: Understanding bounce types (hard or soft) will allow the marketing team to clean up the email list, ensuring better deliverability and engagement metrics.

## Focus of this Tutorial

We will focus on the thir objectives:

* **A/B Test Subject Lines**: Compare two email subject lines for the same campaign to see which one drives more engagement (open and click rates).

#### **Opened but No Click Audience**

This audience includes customers who have opened emails but did not click on any links.

```sql
CREATE AUDIENCE opened_no_click_audience
WITH (primary_identity=email, identity_namespace=Email)
AS SELECT 
    customer_id, 
    email, 
    campaign_name, 
    open_count, 
    click_count 
FROM email_campaign_dataset_20241001_050033_012
WHERE open_count > 0 AND click_count = 0;
```

The result is:

#### **No Engagement Audience**

This audience includes customers who neither opened nor clicked on the emails.

<pre class="language-sql"><code class="lang-sql">CREATE AUDIENCE no_engagement_audience
WITH (primary_identity=email, identity_namespace=Email)
<strong>AS SELECT 
</strong>    customer_id, 
    email, 
    campaign_name, 
    open_count, 
    click_count 
FROM email_campaign_dataset_20241001_050033_012
WHERE open_count = 0;
</code></pre>

### **A/B Testing Queries**

### **Split Testing by Subject Line**

Compare the engagement metrics between two different groups in an A/B test (using subject lines as the test variable).

```sql
WITH ab_testing_split AS (
    SELECT 
        customer_id,
        email,
        email_subject,
        campaign_name,
        open_count,
        click_count,
        CASE 
            WHEN MOD(ROW_NUMBER() OVER (PARTITION BY campaign_name ORDER BY customer_id), 2) = 0 THEN 'Group A'
            ELSE 'Group B'
        END AS test_group
    FROM adobe_campaign_dataset
)
SELECT 
    test_group,
    email_subject,
    campaign_name,
    COUNT(*) AS total_emails_sent,
    SUM(open_count) AS total_opens,
    SUM(click_count) AS total_clicks,
    ROUND(SUM(open_count) / COUNT(*), 2) AS open_rate,
    ROUND(SUM(click_count) / SUM(open_count), 2) AS click_through_rate
FROM ab_testing_split
GROUP BY test_group, email_subject, campaign_name
ORDER BY campaign_name, test_group;
```

This query allows you to compare the performance between Group A and Group B for an A/B test.

#### 4. **Email Delivery and Bounce Queries**

**a) Track Email Delivery Success**

Track how well the emails are being delivered across campaigns by monitoring delivery status.

```sql
SELECT 
    campaign_name,
    COUNT(*) AS total_emails_sent,
    SUM(CASE WHEN delivery_status = 'Delivered' THEN 1 ELSE 0 END) AS emails_delivered,
    SUM(CASE WHEN delivery_status = 'Failed' THEN 1 ELSE 0 END) AS emails_failed,
    ROUND(SUM(CASE WHEN delivery_status = 'Delivered' THEN 1 ELSE 0 END) / COUNT(*), 2) AS delivery_rate
FROM adobe_campaign_dataset
GROUP BY campaign_name
ORDER BY delivery_rate DESC;
```

This query helps you monitor the delivery success rate and identify potential issues in campaigns with high failure rates.

**b) Analyze Bounce Rates**

Identify campaigns with high bounce rates and distinguish between hard and soft bounces.

```sql
SELECT 
    campaign_name,
    COUNT(*) AS total_emails_sent,
    SUM(CASE WHEN bounce_type = 'Hard Bounce' THEN 1 ELSE 0 END) AS hard_bounces,
    SUM(CASE WHEN bounce_type = 'Soft Bounce' THEN 1 ELSE 0 END) AS soft_bounces,
    ROUND(SUM(CASE WHEN bounce_type != 'None' THEN 1 ELSE 0 END) / COUNT(*), 2) AS bounce_rate
FROM adobe_campaign_dataset
GROUP BY campaign_name
ORDER BY bounce_rate DESC;
```

This query will show you which campaigns have high bounce rates and whether those bounces are hard or soft, helping you clean up email lists and improve deliverability.

#### 5. **General Engagement Trends**

**a) Engagement Over Time**

Analyze how customer engagement changes over time by tracking the number of days since the customer’s last purchase.

```sql
SELECT 
    last_purchase_days_ago,
    AVG(open_count) AS avg_open_count,
    AVG(click_count) AS avg_click_count
FROM adobe_campaign_dataset
GROUP BY last_purchase_days_ago
ORDER BY last_purchase_days_ago;
```

This query shows if there’s a correlation between how recently a customer made a purchase and their engagement with email campaigns.


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://data-distilller.gitbook.io/adobe-data-distiller-guide/unit-6-data-distiller-audiences/draft-dda-202-data-distiller-audience-orchestration.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.