Views - Targeted Customers

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

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

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.

Field Name	Description	Data Type	Format
EXECUTION_DATE	Date of execution	Date	YYYY-MM-DD
CUSTOMER_ID	Unique identifier for customers	String
LIFECYCLE_STAGE	Stage in the lifecycle	String
PLAN_ID	Unique identifier for the plan	Integer
PLAN_DETAIL_ID	Unique identifier for plan detail	Integer
ACTION_NAME	Name of the action	String
TAKEN_FROM_PLAN_ID	Plan ID from which the action was taken. If this is NULL this is the plan this customer was targeted with	Integer
EXCLUSION_REASON_ID	Unique identifier for exclusion reason	Integer
EXCLUSION_REASON	Reason for exclusion	String
EXCLUSION_REASON_DESC	Description of exclusion reason	String
LAST_UPDATED_TIME	Timestamp of the last update	DateTime	YYYY-MM-DD HH:MM

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
    YourTableName
WHERE
    EXECUTION_START_DATE = DATEADD(DAY, 1, CURRENT_DATE());