Legacy Views - Scheduled Campaigns

VW_SCHEDULED_CAMPAIGNS (v1.2)

-- FOR V1.2
SELECT *
FROM [SHARED_DB].[BI].[VW_SCHEDULED_CAMPAIGNS_V1_2]

This updated view provides a more organized and comprehensive view of scheduled campaign data, incorporating several new columns and renaming some existing ones for clarity and additional insights. This structure is designed to facilitate better integration with additional tools and improve the analysis of scheduled campaign data.

Common Use Cases

What campaigns are planned for next week?

SELECT DISTINCT
    PLAN_ID,
    MARKETING_ACTION_NAME,
    EXECUTION_START_DATE,
    EXECUTION_END_DATE
FROM
    VW_SCHEDULED_CAMPAIGNS_V1_2
WHERE
    EXECUTION_START_DATE >= DATEADD(DAY, 7, CURRENT_DATE())
    AND EXECUTION_START_DATE < DATEADD(DAY, 14, CURRENT_DATE());

Which channels are about to execute campaigns tomorrow?

SELECT DISTINCT
    PLAN_ID,
    PLAN_DETAIL_ID,
    CHANNEL_ID,
    MARKETING_ACTION_NAME,
    EXECUTION_START_DATE,
    EXECUTION_END_DATE
FROM
    VW_SCHEDULED_CAMPAIGNS_V1_2
WHERE
    EXECUTION_START_DATE = DATEADD(DAY, 1, CURRENT_DATE());

Table Description

Field Name	Description	Data Type	Format
PLAN_ID	A Campaign's ID	Integer
TARGET_GROUP_ID	Unique identifier for the target group	String
TARGET_GROUP_NAME	Name of the target group	String
MARKETING_ACTION_NAME	Name of the marketing action	String
CAMPAIGN_ACTION_ID	Unique identifier for the campaign action	Integer
CAMPAIGN_ACTION_NAME	Name of the campaign action	String
IS_CAMPAIGN_ACTION_GROUP	Flag to indicate if it's a campaign action group	Boolean	True/False
CAMPAIGN_ACTION_TYPE	Type of campaign action	String
CHANNEL_ID	Channel ID associated with the plan detail	String
CHANNEL_NAME	Name of the channel	String
CAMPAIGN_TYPE	Type of the campaign	String
CAMPAIGN_TYPE_NAME	Name of the campaign type	String
LEAD_TIME	Lead time for the campaign	String
SELECTED_GAIN_KPI	Selected gain key performance indicator	String
IS_IMPACT_ENABLED	Flag to indicate if impact is enabled	Boolean	True/False
EXECUTION_START_DATE	Start date of the campaign execution	Date	YYYY-MM-DD
EXECUTION_END_DATE	End date of the campaign execution	Date	YYYY-MM-DD
TAG_NAMES	Tags associated with the campaign	String	"Tag1","Tag2","Tag3"
IS_REAL_SCHEDULED_CAMPAIGN	Flag to indicate if it's a real scheduled campaign	Boolean	True/False
EXCLUDE_BY_CHANNEL_FLAG	Flag to exclude by channel	Boolean	True/False
IMPACT_LENGTH_PLANNED_ACTIONS_CONFIGURATION	Configuration for planned actions impact length	String
PLAN_DETAIL_ID	Unique identifier for plan detail	Integer
ISOLATION_VOLUME	Isolation volume	String
CAMPAIGN_ACTION_ID	Unique identifier for campaign action	Integer
PROMOTION_ID	Promotion ID	String
CONDITIONAL_EXECUTION_NAME	Name of the conditional execution	String
DISPLAY_ORDER	Display order	Integer
UI_PANEL_ID	UI panel ID	String
RECURRENCE_PATTERN	Recurrence pattern	String
NEXT_RUNTIME	Next runtime	Date	YYYY-MM-DD HH:MM
REPEAT_TIMES	Number of times to repeat	Integer
TEMPLATE_ID	Unique identifier for the template	String
TEMPLATE_NAME	Name of the template	String
NOTES	Notes associated with the campaign	String
LAST_MODIFY_USERNAME	Username of the last modifier	String
LAST_MODIFY_DATE	Date of the last modification	Date	YYYY-MM-DD HH:MM

Changes in Version 1.2

New Columns Added:
- CHANNEL_NAME: Added immediately after CHANNEL_ID to provide the name of the channel.
- TEMPLATE_ID and TEMPLATE_NAME: Provide identifiers and names for templates used.
- CAMPAIGN_TYPE: Added to specify the type of the campaign.
- CAMPAIGN_ACTION_ID: Added to uniquely identify each campaign action.
- TARGET_GROUP_ID: Added before TARGET_GROUP_NAME to uniquely identify target groups.
Renamed Columns:
- EXECUTION_METHOD_ID has been renamed to CHANNEL_ID.
- ACTION_NAME has been renamed to CAMPAIGN_ACTION_NAME.
- IS_ACTION_GROUP has been renamed to IS_CAMPAIGN_ACTION_GROUP.
- ACTION_TYPE has been renamed to CAMPAIGN_ACTION_TYPE.
- TAGS has been renamed to TAG_NAMES
Other Modifications:
- Tags are now translated into their real values and presented in double quotes, separated by commas (e.g., "Tag1","Tag2","Tag3").

V1.0 (Legacy)

SELECT *
FROM [SHARED_DB].BI.PLANNED_CAMPAIGNS

Table Description

Column Name	Description	Type	Format
`PLAN_ID`	A Campaign's ID	Number
`MARKETING_ACTION_NAME`	Name of the marketing action	String
`TARGETGROUPNAME`	Name of the target group for this campaign	String
`CAMPAIGN_TYPE_NAME`	Campaign Type	String
`LEAD_TIME`	Lead time for the campaign	String
`SELECTED_GAIN_KPI`	KPI that was selected for the campaign	String
`IS_IMPACT_ENABLED`	Flag to indicate if impact is enabled	Boolean
`EXECUTION_START_DATE`	Start date of the campaign	Date	yyyy-mm-dd
`EXECUTION_END_DATE`	End date of the campaign	Date	yyyy-mm-dd
`TAGS`	Tags associated with the campaign	String
`IS_REAL_SCHEDULED_CAMPAIGN`	Flag to indicate if the campaign is real or scheduled	Boolean
`EXCLUDE_BY_CHANNEL_FLAG`	Flag to indicate if some channels were excluded	Boolean
`IMPACT_LENGTH_PLANNEDACTIONSCONFIGURATION`	Length of the impact of the campaign	Integer
`PLAN_DETAIL_ID`	Occurrence ID of this campaign	String
`ACTION_NAME`	Name of the action	String
`IS_ACTION_GROUP`	Flag to indicate if this is a group action	Boolean
`ACTIONTYPE`	Type of the action	String
`ISOLATION_VOLUME`	Isolation volume for the action	Integer
`PLAN_DETAIL_CHANNEL_ID`	Channel ID for the action	String
`EXECUTION_METHOD_ID`	Execution method for the action	String
`PROMOTION_ID`	Promotion ID for the action	String
`CONDITIONAL_EXECUTION_NAME`	Name of the conditional execution	String
`DISPLAY_ORDER`	Display order for the action	Integer
`UI_PANEL_ID`	ID of the UI panel	String
`RECURRENCE_PATERN`	Recurrence pattern for the campaign	String
`NEXT_RUNTIME`	Next runtime of the campaign	Date	yyyy-mm-dd
`REPEAT_TIMES`	Number of times the campaign should repeat	Integer
`NOTES`	Notes associated with the campaign	String
`LAST_MODIFY_USERNAME`	Name of the user who last modified the campaign	String
`LAST_MODIFY_DATE`	Date the campaign was last modified	Date	yyyy-mm-dd