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
toLIFECYCLE_STAGE_NAME
- The previousLIFECYCLE_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 asTARGET_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 |
Updated about 1 month ago