Views - Scheduled Campaigns

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_SCHEDULED_CAMPAIGNS (v1.1)

-- FOR V1.1
SELECT *
FROM [SHARED_DB].[BI].[VW_SCHEDULED_CAMPAIGNS]

This view provides a more organized view of scheduled campaigns data. This view will be kept in this structure and can be used to integrate additional tools to it.

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
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,
    PLAN_DETAIL_CHANNEL_ID,
    MARKETING_ACTION_NAME,
    EXECUTION_START_DATE,
    EXECUTION_END_DATE
FROM
    VW_SCHEDULED_CAMPAIGNS
WHERE
    EXECUTION_START_DATE = DATEADD(DAY, 1, CURRENT_DATE());

Table Description

Field NameDescriptionData TypeFormat
PLAN_IDA Campaign's IDInteger
MARKETING_ACTION_NAMEName of the marketing actionString
TARGET_GROUP_NAMEName of the target groupString
CAMPAIGN_TYPE_NAMEName of the campaign typeString
LEAD_TIMELead time for the campaignString
SELECTED_GAIN_KPISelected gain key performance indicatorString
IS_IMPACT_ENABLEDFlag to indicate if impact is enabledBooleanTrue/False
EXECUTION_START_DATEStart date of the campaign executionDateYYYY-MM-DD
EXECUTION_END_DATEEnd date of the campaign executionDateYYYY-MM-DD
TAGSTags associated with the campaignString
IS_REAL_SCHEDULED_CAMPAIGNFlag to indicate if it's a real scheduled campaignBooleanTrue/False
EXCLUDE_BY_CHANNEL_FLAGFlag to exclude by channelBooleanTrue/False
IMPACT_LENGTH_PLANNED_ACTIONS_CONFIGURATIONConfiguration for planned actions impact lengthString
PLAN_DETAIL_IDUnique identifier for plan detailInteger
ACTION_NAMEName of the actionString
IS_ACTION_GROUPFlag to indicate if it's an action groupBooleanTrue/False
ACTION_TYPEType of actionString
ISOLATION_VOLUMEIsolation volumeString
PLAN_DETAIL_CHANNEL_IDChannel ID associated with the plan detailString
EXECUTION_METHOD_IDExecution method IDString
PROMOTION_IDPromotion IDString
CONDITIONAL_EXECUTION_NAMEName of the conditional executionString
DISPLAY_ORDERDisplay orderInteger
UI_PANEL_IDUI panel IDString
RECURRENCE_PATTERNRecurrence patternString
NEXT_RUNTIMENext runtimeDateYYYY-MM-DD HH:MM
REPEAT_TIMESNumber of times to repeatInteger
NOTESNotes associated with the campaignString
LAST_MODIFY_USERNAMEUsername of the last modifierString
LAST_MODIFY_DATEDate of the last modificationDateYYYY-MM-DD HH:MM