SQL Case When Advanced Real time Use Case

 

SFMC SQL CASE Statement Use Cases

5 normal CASE use cases and 5 advanced real-time enterprise use cases in Salesforce Marketing Cloud SQL.


1. Customer Segmentation:

Customer Table :


Query for Customer Segmentation Based on Total Spent amount

SELECT  SubscriberKey,

    CASE
        WHEN TotalSpent >= 10000 THEN 'VIP'
        WHEN TotalSpent >= 5000 THEN 'Gold'
        ELSE 'Regular'
    END AS CustomerSegment

FROM Push_Cusotmer_de


Resultant Target Table :


Output :  Based on Amount spent we segmented the data in different categories like Gold, Regular.


2. Gender Formatting:

Contact Table :





Target_Table :


Query :

SELECT   SubscriberKey,

    CASE Gender
        WHEN 'M' THEN 'Male'
        WHEN 'F' THEN 'Female'
        ELSE 'Unknown'  
  END AS GenderName

FROM Push_Contact_DE




Resultant Table :



 3. Email Engagement Status


Table : 



Query:

SELECT  SubscriberKey,
    
CASE
        WHEN OpenCount >= 10 THEN 'Highly Engaged'
        WHEN OpenCount >= 5 THEN 'Moderate'
        ELSE 'Low'
    END AS EngagementLevel

FROM EngagementData



Output : 





4. Country Mapping


Customers Table


Target Table







 

Query:

SELECT  SubscriberKey,
    
CASE CountryCode
        WHEN 'IN' THEN 'India'
        WHEN 'US' THEN 'United States'
        ELSE 'Other'
    END AS CountryName

FROM Push_Customers_DE


Resultant Table :




5. Mobile App Status

Source Table


Target Table




Query:


 SELECT
    ContactKey,
    CASE
        WHEN AppInstalled = 1 THEN 'Installed'
        ELSE 'Not Installed'
    END AS AppStatus
FROM Push_MobileUser_DE


Resultant Table



6. Multi-Channel Intelligent Routing

Source_Table


Target Table


Query
 


SELECT    ContactKey,

    CASE
        WHEN PushOptIn = 1 THEN 'Push'
        WHEN EmailOptIn = 1 THEN 'Email'
        WHEN SMSOptIn = 1 THEN 'SMS'
        ELSE 'Do Not Contact'
    END AS BestChannel

FROM MasterCustomer_DE


Resultant Table




7. AI-Based Customer Lifecycle Stage

Source Table




Target Table




Query


SELECT
    CustomerID,
    CASE
        WHEN TotalOrders = 0 THEN 'Prospect'
        WHEN TotalOrders = 1 THEN 'New Customer'
        ELSE 'Active Customer'
    END AS LifecycleStage
FROM Push_EcommerceCustomers



Resultant Table :


To be  Continued































Comments

Post a Comment

Popular posts from this blog

SQL_Joins

Image
                                                                                                        JOINS JOIN in Salesforce Marketing Cloud In SFMC, a JOIN is used to combine data from multiple Data Extensions (tables) using a common field. In Salesforce Marketing Cloud , data is usually stored in multiple Data Extensions (DEs) instead of one single table. JOINs are important because they help combine related data from different DEs using a common field. Why JOINs Are Important Without JOINs: 👉 data remains separated reports become difficult personalization is limited segmentation becomes hard JOINs help marketers connect subscriber data, order data, journey d...
Read more

List Assignment

Image
                                                                                                    List Assignment Task 1: Create a New List in SFMC Fist we created the List Named Push_ Monthly Newsletter Subscribers. Defined the below fields : Subscriber Key (Primary Key) Email Address First Name Subscription Status (Active/Inactive) Task 2: Import Initial Data Now need to import the data into this List. Now Import data completed and Data added Task 3: Update & Add New Subscribers: Add new and Update Existing Subscribers Only Verify that: Mark’s Subscription Status is updated to ‘Inactive’ Ethan’s Subscription Status is updated to ‘Active’ Emily is added to the list (since we chose ...
Read more

SQL-Automation Query

Image
 For SQL Query Automation :  Select - Journey Builder > Automation Studio > New Automation  Select : Starting Source Schedule  and drag it to Starting source and drag SQL Query . click on choose and select : Create new Query Activity - Name the Query Write the Query on editor and validate the Query syntax  and also create the New target data extension before moving ahead with all fields. Now select the target data extension with action data as overwrite : Then finish and save and name the Automation: Now Save and Run Once the automation : Click "Select All activity" and Click on Run Click on RUN ONCE Query Automation Run successfully. checked the Targeted DE found all the records are successfully updated.
Read more