Customers In Control Groups

How to Identify Control Group Allocations in Optimove Data Share

In Optimove’s Data Share, identifying control group allocations for both individual campaigns and streams is essential for analyzing customer behaviors. This guide explains how to pinpoint control group allocations using tables VW_TARGETED_AND_EXCLUDED_CUSTOMERS_V1_2 and VW_SCHEDULED_CAMPAIGNS_V1_2.

Step 1: Identify Campaign Control Groups in VW_SCHEDULED_CAMPAIGNS_V1_2

In order to find which customers are allocated to control groups, query should start with identifying PLAN_DETAIL_ID values that are associated with control groups, which are a necessary link to customers targeting records.

In the context of scheduled single campaigns (which are not part of a stream), control group are implemented as an action within the campaign, represented by PLAN_DETAIL_ID, where ACTION_ID=1 and/or ACTION_NAME="DN".

Locate the compatible view: Use VW_SCHEDULED_CAMPAIGNS_V1_2 to view all campaigns metadata, along with their actions, action types, channels, target groups, and allocation details.
Filter for Control Group Actions:
- In each campaign, campaign actions that are control group are represented by ACTION_ID=1 and/or ACTION_NAME="DN"
- Filter the view based on these values to identify control group customers in a given campaign.
Check Plan Details:
- Use PLAN_ID and PLAN_DETAIL_ID to filter and check which customers are assigned to the control group in a recurring campaign or in specific campaign instances, respectively.

Here is an example to a query that might help here -

SELECT 
    PLAN_ID,
    PLAN_DETAIL_ID,
    TARGET_GROUP_ID,
    TARGET_GROUP_NAME,
    MARKETING_ACTION_NAME,
    CAMPAIGN_ACTION_ID,
    CAMPAIGN_ACTION_NAME,
    CAMPAIGN_TYPE,
    CAMPAIGN_TYPE_NAME
FROM 
    VW_SCHEDULED_CAMPAIGNS_V1_2
WHERE 
    CAMPAIGN_ACTION_ID = 1
    AND CAMPAIGN_ACTION_NAME = 'DN'
    AND CAMPAIGN_TYPE <> 4

Step 2: Identify Control Stream Allocations in VW_TARGETED_AND_EXCLUDED_CUSTOMERS_V1_2

For customers in streams, the Control Stream designation ensures a persistent control across multiple campaigns within a stream. Here’s how to find these allocations:

Locate the compatible view: Open VW_TARGETED_AND_EXCLUDED_CUSTOMERS_V1_2, which details targeted and excluded customers in campaigns and streams.
Filter for Super Control Group Exclusions:
- To identify customers allocated to the Super Control Group, filter the data by EXCLUSION_REASON.
- Set EXCLUSION_REASON = "CONTROL STREAM" or EXCLUSION_REASON_ID = 5. This identifies users excluded from all campaigns in a stream, meaning they belong to the Control Stream.
- This query will get customers that are allocated to control stream on any given day
- ```
SELECT 
    CUSTOMER_ID,
    EXECUTION_DATE,
    PLAN_ID,
    PLAN_DETAIL_ID,
    CAMPAIGN_ACTION_NAME,
    EXCLUSION_REASON,
    EXCLUSION_REASON_DESC
FROM 
    VW_TARGETED_AND_EXCLUDED_CUSTOMERS_V1_2
WHERE 
    EXCLUSION_REASON_ID = 5
```
Stream Control Group vs. Campaign Control Group:
- In streams, control group allocations prevent participation in all underlying campaigns. The VW_TARGETED_AND_EXCLUDED_CUSTOMERS_V1_2 table shows customers excluded due to Control Stream designation by marking their EXCLUSION_REASON accordingly.
- This differs from single campaigns where control group designation is represented by ACTION_ID = 1 in VW_SCHEDULED_CAMPAIGNS_V1_2.

Here is a SQL query that will return all control group customers. It is recommended to add filtering by EXECUTION_DATE

SELECT
    CUSTOMER_ID,
    EXECUTION_DATE,
    PLAN_ID,
    PLAN_DETAIL_ID,
    CAMPAIGN_ACTION_NAME,
    EXCLUSION_REASON,
    EXCLUSION_REASON_DESC
FROM 
    VW_TARGETED_AND_EXCLUDED_CUSTOMERS_V1_2
WHERE 
    EXCLUSION_REASON_ID = 5
    OR PLAN_DETAIL_ID IN (SELECT  
    PLAN_DETAIL_ID,
FROM 
    VW_SCHEDULED_CAMPAIGNS_V1_2
WHERE 
    CAMPAIGN_ACTION_ID = 1
    AND CAMPAIGN_ACTION_NAME = 'DN'
    AND CAMPAIGN_TYPE <> 4)

Additional Notes

For clear data interpretation, refer to the Data Shares Glossary in This section. It maps terms used in Optimove’s UI to corresponding data properties, enhancing understanding and cross-referencing.