V1.2 - Targeted Customers

VW_TARGETED_AND_EXCLUDED_CUSTOMERS (v1.2)

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

Overview

This view provides an organized representation of customers who were either targeted or excluded from scheduled campaigns. It includes details about the campaigns, customers, and reasons for exclusion if applicable. The structure remains consistent for easy integration with additional tools and queries.

Useful Queries

Which customers were excluded from a specific campaign and why?

This query helps identify the customers who were excluded from certain campaigns' occurences and the reason behind their exclusion.

SELECT 
    CUSTOMER_ID,
    PLAN_ID,
    PLAN_DETAIL_ID,
    CAMPAIGN_ACTION_NAME,
    EXCLUSION_REASON,
    EXCLUSION_REASON_DESC,
    EXECUTION_DATE
FROM 
    BI.VW_TARGETED_AND_EXCLUDED_CUSTOMERS
WHERE 
    EXCLUSION_REASON IS NOT NULL
ORDER BY 
    EXECUTION_DATE DESC;

This query retrieves customers who were eventually targeted by certain campaigns occurences

SELECT 
    CUSTOMER_ID,
    PLAN_ID,
    PLAN_DETAIL_ID,
    CAMPAIGN_ACTION_NAME,
    EXCLUSION_REASON,
    EXCLUSION_REASON_DESC,
    EXECUTION_DATE
FROM 
    BI.VW_TARGETED_AND_EXCLUDED_CUSTOMERS
WHERE 
    EXCLUSION_REASON IS NULL  -- Customers who were targeted but not excluded
ORDER BY 
    EXECUTION_DATE DESC;

Which lifecycle stage were customers in during campaign execution?
- Helps understand customer behavior and campaign effectiveness based on their lifecycle stage.

Table Description (Updated)

Field Name	Description	Data Type	Format
`EXECUTION_DATE`	Date when the campaign was executed	Date	YYYY-MM-DD
`CUSTOMER_ID`	Unique identifier for the customer	String
`LIFECYCLE_STAGE`	The stage of the customer within the lifecycle when the campaign was executed	String
`PLAN_ID`	Unique identifier for the campaign plan	Integer
`PLAN_DETAIL_ID`	Unique identifier for the specific detail within the campaign plan	Integer
`CAMPAIGN_ACTION_NAME`	Name of the campaign action	String
`TAKEN_FROM_PLAN_ID`	Plan ID from which the campaign action was taken. If `NULL`, this is the plan the customer was targeted with	Integer
`EXCLUSION_REASON_ID`	Unique identifier for the reason a customer was excluded from the campaign	Integer
`EXCLUSION_REASON`	The exclusion reason	String
`EXCLUSION_REASON_DESC`	A detailed description of why the customer was excluded	String
`LAST_UPDATED_TIME`	The timestamp of the last update to this record	DateTime	YYYY-MM-DD HH:MM

Key Changes (Version Update):

ACTION_NAME is now CAMPAIGN_ACTION_NAME to better align with campaign-related tables.
Other column names remain the same, with minor formatting adjustments for consistency and clarity.