V1.2 - Campaign Results

Views are out-of-the-box data queries on top of the base structure of the data, providing easy access to common use cases required datasets and additional data useful to exploit the data lakes product fully.

Using views, Data lakes evolve while providing you with data contracts that will be kept and enable stable integrations with your internal platforms.

VW_CAMPAIGN_RESULTS (v1.2)

The table provides detailed insights into each campaign occurrence, tracking various KPIs across different lifecycle stages. It organizes campaign results data for better analysis and can be integrated with additional tools for enhanced functionality.

Each row represents an action within a specific occurrence of a campaign, making it possible to analyze the performance of a campaign occurrence by summing the [INCREASE_IN] values for a selected KPI across all relevant lifecycle stages.

Version 1.2 Updates:

New Column Added: CAMPAIGN_TYPE_NAME - Represents the name of the campaign type for each occurrence. Learn more about Campaign Types
New Column Added: LIFECYCLE_STAGE_ID - Provides a unique identifier for each lifecycle stage. Learn more about Lifecycle Stages
Column Renamed: LIFECYCLE_STAGE to LIFECYCLE_STAGE_NAME - The previous LIFECYCLE_STAGE column has been renamed to better clarify its purpose as the name of the lifecycle stage.
New Column Added: TARGET_GROUP_ID - Also known as TARGET_GROUP_SERIAL, this column provides a unique identifier for each target group.

Summary per Campaign and Marketing Action view

This query provides a summary of campaign performance metrics by campaign (PLAN_ID), KPI and marketing action, including total campaigns, group sizes, respondents, and KPI increases. It helps compare the effectiveness of different marketing actions against control groups.

SELECT 
    a.PLAN_ID,
    CASE 
        WHEN b.MARKETING_ACTION_NAME = 'DN' THEN 'Control'
        ELSE b.MARKETING_ACTION_NAME
    END AS MARKETING_ACTION_NAME,
    KPI,
    COUNT(a.PLAN_DETAIL_ID) AS Total_Campaigns,
    SUM(a.PLAN_GROUPSIZE) AS Total_Group_Size,
    SUM(a.PLAN_DETAIL_NUMBER_OF_RESPONDENTS) AS Total_Respondents,
    SUM(a.INCREASE_IN) AS Total_Increase,
    AVG(a.INCREASE_PER_CUSTOMER) AS Average_Increase_Per_Customer,
    AVG(a.PLAN_DETAIL_RESPONSE_RATE) AS Average_Response_Rate,
    AVG(a.PLAN_DETAIL_AVGVALUE) AS Average_Value,
    SUM(a.PLAN_DETAIL_NUMBER_OF_CLICKED_AND_RESPOND) AS Total_Clicked_And_Respond,
    AVG(a.PLAN_DETAIL_AVG_VALUE_OF_CLICKED_AND_RESPOND) AS Average_Value_Of_Clicked_And_Respond
    
FROM 
    VW_CAMPAIGN_RESULTS_V1_2 a
JOIN 
    VW_SCHEDULED_CAMPAIGNS b
ON 
    a.PLAN_ID = b.PLAN_ID 
    AND a.PLAN_DETAIL_ID = b.PLAN_DETAIL_ID
GROUP BY 
    a.PLAN_ID, 
    CASE 
        WHEN b.MARKETING_ACTION_NAME = 'DN' THEN 'Control'
        ELSE b.MARKETING_ACTION_NAME
    END,
    KPI
ORDER BY 
    a.PLAN_ID, MARKETING_ACTION_NAME
LIMIT 1000;

Marketing Action Cross-Campaign Performance

This view provides an aggregated data over each marketing action (MARKETING_ACTION_NAME) across the different campaigns it appears in.

SELECT 
    CASE 
        WHEN b.MARKETING_ACTION_NAME = 'DN' THEN 'Control'
        ELSE b.MARKETING_ACTION_NAME
    END AS MARKETING_ACTION_NAME,
    a.KPI,
    COUNT(DISTINCT a.PLAN_ID) AS Number_of_Campaigns,
    COUNT(a.PLAN_DETAIL_ID) AS Total_Campaign_Actions,
    SUM(a.PLAN_GROUPSIZE) AS Total_Group_Size,
    SUM(a.PLAN_DETAIL_NUMBER_OF_RESPONDENTS) AS Total_Respondents,
    SUM(a.INCREASE_IN) AS Total_Increase,
    AVG(a.INCREASE_PER_CUSTOMER) AS Average_Increase_Per_Customer,
    AVG(a.PLAN_DETAIL_RESPONSE_RATE) AS Average_Response_Rate,
    AVG(a.PLAN_DETAIL_AVGVALUE) AS Average_Value,
    SUM(a.PLAN_DETAIL_NUMBER_OF_CLICKED_AND_RESPOND) AS Total_Clicked_And_Respond,
    AVG(a.PLAN_DETAIL_AVG_VALUE_OF_CLICKED_AND_RESPOND) AS Average_Value_Of_Clicked_And_Respond
    
FROM 
    VW_CAMPAIGN_RESULTS_V1_2 a
JOIN 
    VW_SCHEDULED_CAMPAIGNS b
ON 
    a.PLAN_ID = b.PLAN_ID 
    AND a.PLAN_DETAIL_ID = b.PLAN_DETAIL_ID
GROUP BY 
    CASE 
        WHEN b.MARKETING_ACTION_NAME = 'DN' THEN 'Control'
        ELSE b.MARKETING_ACTION_NAME
    END,
    a.KPI
ORDER BY 
    MARKETING_ACTION_NAME, KPI
LIMIT 1000

Table Description

Field Name	Description	Data Type	Format
PLAN_ID	A Campaign's ID	String
EXECUTION_DATE	Campaign Occurrence day	Date	yyyy-mm-dd
END_TEST_DATE	Last day of testing that is calculated into this campaign metrics	Date	yyyy-mm-dd
TARGET_GROUP_ID	Unique identifier for each target group (same as TARGET_GROUP_SERIAL)	String
TARGET_GROUP_NAME	Name of the target group	String
LIFECYCLE_STAGE_ID	Unique identifier for each lifecycle stage	String
LIFECYCLE_STAGE_NAME	Name of the lifecycle stage	String
KPI	Name of the KPI represented in each row	String
IS_DEFAULT_KPI	Flag to indicate if KPI is default for this campaign	Boolean
KPI_DISTRIBUTION_TYPE	Type of KPI distribution	String
CAMPAIGN_TYPE	Type of campaign	String
CAMPAIGN_TYPE_NAME	Name of the campaign type	String
TARGET_GROUP_PRIORITY	Priority of target group	Integer
IS_AUTO_PRIORITY	Flag to indicate if priority is auto-generated	Boolean
PLAN_GROUPSIZE	Size of target group according to plan	Integer
IS_AVERAGE_SIGNIFICANCE	Flag to indicate if average value is significant	Boolean
IS_RESPONSE_RATE_SIGNIFICANCE	Flag to indicate if response rate is significant	Boolean
INCREASE_IN	Increase in KPI	Float
INCREASE_PER_CUSTOMER	Increase in KPI per customer	Float
IS_COMPLETED	Flag to indicate if campaign is completed	Boolean
PLAN_DETAIL_ID	Campaign action ID	String
IS_CONTROL	Flag to indicate if target group is a control group	Boolean
PLAN_DETAIL_GROUP_SIZE	Size of target group according to plan occurrence	Integer
PLAN_DETAIL_NUMBER_OF_RESPONDENTS	Number of customers who responded to campaign occurrence	Integer
PLAN_DETAIL_RESPONSE_RATE	Response rate of campaign occurrence	Float
PLAN_DETAIL_AVGVALUE	Average value of campaign occurrence	Float
PLAN_DETAIL_STDEV_VALUE	Standard deviation of campaign occurrence	Float
PLAN_DETAIL_AVG_LOG_VALUE	Average logarithmic value of campaign occurrence	Float
PLAN_DETAIL_VAR_LOG_VALUE	Variance of logarithmic value of campaign occurrence	Float
PLAN_DETAIL_NUMBER_OF_CLICKED_AND_RESPOND	Number of customers who clicked and responded to campaign occurrence	Integer
PLAN_DETAIL_AVG_VALUE_OF_CLICKED_AND_RESPOND	Average value of customers who clicked and responded to campaign occurrence	Float