[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

DDA 200: Build Data Distiller Audiences on Data Lake Using SQL

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

PREP 500: Ingesting CSV Data into Adobe Experience Platform

We will be using the following data to create segments:

1MB

email_campaign_dataset.csv

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:

Campaign Performance Tracking: Track and analyze key metrics such as email open rates, click-through rates, and bounce rates to assess campaign success.
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.
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.
Improve Email Deliverability: Track failed email deliveries and understand bounce reasons (soft or hard bounces) to optimize email lists and improve overall deliverability rates.
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:

Identify High-Value Customers: Focus on customers who have a high purchase frequency and loyalty score, engaging them with personalized offers.
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).
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).
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.

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.

CREATE AUDIENCE no_engagement_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;

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

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.

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.

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.

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.

Last updated 9 months ago