Targeted Yesterday

To understand the targeting and prioritization of campaigns executed on a specific day, Optimove offers a structured way to query and analyze which customers were reached by each campaign. This guide will help you retrieve both campaign-level and customer-level data for campaigns launched on a specified day, providing insights into targeting scope, campaign impact, and customer engagement.

Understanding Campaign Execution and Targeting

Each day, campaigns are launched based on pre-configured execution schedules in Optimove. By querying the VW_SCHEDULED_CAMPAIGNS_V1_2 and VW_TARGETED_AND_EXCLUDED_CUSTOMERS_V1_2 tables, you can gather details about which customers were targeted by specific campaigns, along with execution times and targeting priority.

Campaign-Level Data for Customers Targeted Yesterday

The following query retrieves campaigns executed yesterday, with conditions to ensure the campaign’s start date was before yesterday and that the end date is either NULL or in the future. This query counts the number of customers targeted by each campaign action on that day.

SELECT 
    TAEC.EXECUTION_DATE, 
    SC12.PLAN_ID, 
    SC12.PLAN_DETAIL_ID, 
    SC12.CAMPAIGN_ACTION_NAME, 
    COUNT(*) AS NUMBER_OF_CUSTOMERS
FROM 
    VW_SCHEDULED_CAMPAIGNS_V1_2 AS SC12
INNER JOIN 
    VW_TARGETED_AND_EXCLUDED_CUSTOMERS_V1_2 AS TAEC 
ON 
    SC12.PLAN_ID = TAEC.PLAN_ID 
    AND SC12.PLAN_DETAIL_ID = TAEC.PLAN_DETAIL_ID  
WHERE 
    TAEC.EXECUTION_DATE = DATEADD(day, -1, CURRENT_DATE)
    AND SC12.EXECUTION_START_DATE < DATEADD(day, -1, CURRENT_DATE)
    AND (SC12.EXECUTION_END_DATE IS NULL OR SC12.EXECUTION_END_DATE >= CURRENT_DATE)
GROUP BY 
    TAEC.EXECUTION_DATE, 
    SC12.PLAN_ID, 
    SC12.PLAN_DETAIL_ID, 
    SC12.CAMPAIGN_ACTION_NAME;

Explanation of Query Parameters

• Execution Date Filtering: The TAEC.EXECUTION_DATE field dynamically filters for yesterday's date using DATEADD(day, -1, CURRENT_DATE).

• Campaign Start and End Dates: The query filters for campaigns that started before yesterday (SC12.EXECUTION_START_DATE < DATEADD(day, -1, CURRENT_DATE)) and either have no end date (ongoing) or end on or after today.

• Campaign Action Name and Counts: The query groups results by PLAN_ID, PLAN_DETAIL_ID, and CAMPAIGN_ACTION_NAME to provide a breakdown of each campaign action and a count of the customers reached.

Customer-Level View of Targeted Campaigns

To get a detailed view at the customer level, including which campaigns each customer received, as well as the campaign action and execution date, use the following query. This view can help analyze individual customer responses and engagement with specific campaigns.

SELECT 
    TAEC.EXECUTION_DATE,
    TAEC.CLIENT_CUSTOMER_ID,
    SC12.PLAN_ID,
    SC12.PLAN_DETAIL_ID,
    SC12.CAMPAIGN_ACTION_NAME,
    SC12.TARGET_GROUP_NAME,
    SC12.CHANNEL_NAME,
    SC12.TEMPLATE_NAME,
    SC12.PROMOTION_ID,
    SC12.TAG_NAMES
FROM 
    VW_TARGETED_AND_EXCLUDED_CUSTOMERS_V1_2 AS TAEC
INNER JOIN 
    VW_SCHEDULED_CAMPAIGNS_V1_2 AS SC12 
ON 
    SC12.PLAN_ID = TAEC.PLAN_ID 
    AND SC12.PLAN_DETAIL_ID = TAEC.PLAN_DETAIL_ID 
WHERE 
    TAEC.EXECUTION_DATE = DATEADD(day, -1, CURRENT_DATE)
    AND SC12.EXECUTION_START_DATE < DATEADD(day, -1, CURRENT_DATE)
    AND (SC12.EXECUTION_END_DATE IS NULL OR SC12.EXECUTION_END_DATE >= CURRENT_DATE)
ORDER BY 
    TAEC.EXECUTION_DATE DESC,
    TAEC.CLIENT_CUSTOMER_ID
LIMIT 1000;

Analyzing Customer-Level Campaign Data

The customer-level view offers granular insights into:

1. Customer Reach per Campaign Action: You can see which specific customers were reached by each campaign action, making it easier to track individual engagement.
2. Targeting Precision and Impact: Analyzing targeted customer data helps refine future campaign targeting by revealing patterns in customer responsiveness and engagement.
3. Segmentation and Personalization Insights: Combining campaign action details (e.g., CHANNEL_NAME, TARGET_GROUP_NAME) can inform segmentation strategies for more personalized marketing.