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

Query:-

SELECT  SubscriberKey,

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

Result:- 

2. Gender Formatting

Query:-

SELECT SubscriberKey,
CASE Gender
        WHEN 'M' THEN 'Male'
        WHEN 'F' THEN 'Female'
        ELSE 'Unknown'
END AS GenderName
FROM Sub_Customer_Contacts

Result:-

3. Email Engagement Status

Query:-

SELECT SubscriberKey,
CASE

        WHEN OpenCount >= 10 THEN 'Highly Engaged'
        WHEN OpenCount >= 5 THEN 'Moderate'
        ELSE 'Low'
END AS EngagementLevel
FROM Sub_Customer_EngagementData

Result:-

4. Country Mapping

Query:-

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

Result:-

5. Mobile App Status

Query:-

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

Result:-

6. Multi-Channel Intelligent Routing

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 Push_MasterCustomer_DE

Result:-

7. AI-Based Customer Lifecycle Stage

Query:-

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

Result:-

8. Fraud Detection Logic

Query:-

SELECT CustomerID,
CASE
            WHEN OrderAmount > 100000 THEN 'High Fraud Risk'
            WHEN OrderAmount > 50000 THEN 'Medium Risk'
            ELSE 'Low Risk'
END AS FraudStatus
FROM Ecommerce_Orders_DE

Result:-