How to Identify Control Group Allocations in Optimove Data Share

Overview

To measure true campaign uplift, calculate incrementality, and maintain accurate statistical models, it is essential to accurately identify every customer isolated into a control group.

Optimove handles Scheduled Campaigns, Triggered Campaigns, and Streams differently at the execution level. Consequently, control group allocation data is routed to distinct views within the Data Share (V1_3 schema).

This guide outlines how to locate control group users across these specific views and provides a unified SQL query to consolidate them into a single, comprehensive list.

1. Scheduled Campaign Control Groups

In regular scheduled campaigns, customers are randomly isolated into control groups at the exact time the campaign target group is generated. These customers are logged as "targeted" to establish a statistical baseline, even though no communication is dispatched.

In the context of single campaigns, a control group is implemented as an action within the campaign.

• Target View: VW_TARGETED_AND_EXCLUDED_CUSTOMERS_V1_3
• How to Identify: Filter for the "Do Nothing" action.
  • CAMPAIGN_ACTION_ID = 1
  • CAMPAIGN_ACTION_NAME = 'DN'

2. Triggered Campaign Control Groups

For triggered campaigns, customers are evaluated and allocated to control groups in real-time as they meet the campaign's trigger conditions.

Because triggered campaigns are event-driven, the engagement record is stored in the engaged customers view. To associate this engagement with the parent campaign's metadata, it must be joined with the triggered campaigns view.

• Target Views: * VW_ENGAGED_CUSTOMERS_V1_3 (Contains the customer event and action ID)

• VW_TRIGGERED_CAMPAIGNS_V1_3 (Contains the parent campaign metadata)

• Join Key: CAMPAIGN_DETAIL_ID (Engaged view) = CAMPAIGN_DETAILID (Triggered view)

• How to Identify: Filter the engaged customers view for the "Do Nothing" action (CAMPAIGN_ACTION_ID = 1 or CAMPAIGN_ACTION_NAME = 'DN').

3. Stream-Level Control Groups (Control Treatments)

A Stream is a sequential series of campaigns. When defining a stream, you can configure a global "Control Treatment" (e.g., a 10% holdout of the initial stream audience). Customers allocated to this "Super Control Group" are entirely excluded from participating in any underlying campaigns within that stream.

Because these control groups are persistent entities defined at the overarching stream level, they are tracked in dedicated treatment tables rather than daily campaign execution logs.

• Target Views:

• VW_STREAMS_METADATA_V1_3 (Contains stream and treatment definitions)

• VW_CUSTOMERS_IN_TREATMENT_V1_3 (Contains the exact customers allocated to those treatments)

• Join Key: TREATMENT_ID

• How to Identify: Join the tables and filter for treatments where the name indicates a control group.

• LOWER(TREATMENT_NAME) = 'control'

4. The Unified View: Extracting the Complete List

To provide a comprehensive reporting foundation, you can stitch the permutations together using a UNION ALL.

The following SQL query standardizes the output across the batch, real-time, and stream treatment views. The result is a single, definitive table containing the control group customers, the date of their execution, the associated Reference ID (Campaign ID or Treatment ID), and the exact type of control group they fall into.

WITH Unified_Control_Groups AS (

    -- 1. Scheduled Campaign Controls
    SELECT 
        CUSTOMER_ID,
        EXECUTION_DATE,
        PLAN_ID AS REFERENCE_ID,
        'Scheduled Campaign Control' AS CONTROL_TYPE
    FROM 
        VW_TARGETED_AND_EXCLUDED_CUSTOMERS_V1_3
    WHERE 
        CAMPAIGN_ACTION_ID = 1 OR CAMPAIGN_ACTION_NAME = 'DN'

    UNION ALL

    -- 2. Triggered Campaign Controls
    SELECT 
        ec.CUSTOMER_ID,
        ec.ENGAGED_DATE AS EXECUTION_DATE,
        tc.CAMPAIGN_ID AS REFERENCE_ID,
        'Triggered Campaign Control' AS CONTROL_TYPE
    FROM 
        VW_ENGAGED_CUSTOMERS_V1_3 ec
    JOIN
        VW_TRIGGERED_CAMPAIGNS_V1_3 tc 
        ON ec.CAMPAIGN_DETAIL_ID = tc.CAMPAIGN_DETAILID
    WHERE 
        ec.CAMPAIGN_ACTION_ID = 1 OR ec.CAMPAIGN_ACTION_NAME = 'DN'

    UNION ALL

    -- 3. Stream-Level Controls
    SELECT 
        cit.CUSTOMER_ID,
        cit.ALLOCATION_DATE AS EXECUTION_DATE,
        cit.TREATMENT_ID AS REFERENCE_ID,
        'Stream Control' AS CONTROL_TYPE
    FROM 
        VW_CUSTOMERS_IN_TREATMENT_V1_3 cit
    JOIN
        VW_STREAMS_METADATA_V1_3 sm
        ON cit.TREATMENT_ID = sm.TREATMENT_ID
    WHERE 
        LOWER(sm.TREATMENT_NAME) = 'control'
)

-- Final Output: The complete list of isolated customers
SELECT 
    CUSTOMER_ID,
    EXECUTION_DATE,
    REFERENCE_ID AS CAMPAIGN_OR_STREAM_TREATMENT_ID,
    CONTROL_TYPE
FROM 
    Unified_Control_Groups
ORDER BY 
    EXECUTION_DATE DESC, 
    CUSTOMER_ID;