Case statement in SQL

 In Salesforce Marketing Cloud (SFMC), we can use the CASE statement in SQL (Query Activities in Automation Studio) to apply conditional logic—similar to IF-ELSE.

🔹 Basic Syntax

CASE 
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE default_result
END



                                     Source DE 


Target DE 




1. Age Group Segmentation


Query - 
SELECT FirstName,
CASE
    WHEN Age < 18 THEN 'Minor'
    WHEN Age BETWEEN 18 AND 40 THEN 'Adult'
    ELSE 'Senior'
END AS AgeGroup
FROM Customer_master_Abu

Query in SQL :-




Target DE after query :-


2. Engagement Level


Query in SQL 
- 
SELECT FirstName, 
CASE 
    WHEN OpenCount > 10 THEN 'High' 
    WHEN OpenCount BETWEEN 5 AND 10 THEN 'Medium' 
    ELSE 'Low' 
END AS Engagement 
From Customer_master_Abu

Query :-

Target DE after query run :-


3. Greeting 

Query -
SELECT FirstName,
CASE 
 WHEN Country = 'India' THEN 'Namaste'
 WHEN Country = 'USA' THEN 'Hello'
 ELSE 'Hi'
END AS Greeting
FROM Customer_master_abu


Query in SQL :-


Target DE after query 



4. Status Mapping 

Query -
SELECT FirstName,
CASE 
 WHEN Status = 'A' THEN 'Active'
 WHEN Status = 'I' THEN 'Inactive'
 ELSE 'Unknown'
END AS StatusLabel
FROM Customer_master-abu

Query 


Target DE after query 


5. Purchase Segmentation 

Query 

SELECT FirstName,
CASE 
 WHEN PurchaseAmount > 2000 THEN 'Premium'
 WHEN PurchaseAmount BETWEEN 500 AND 2000 THEN 'Standard'
 ELSE 'Low Value'
END AS CustomerType
FROM Customer_master_Abu

Query in SQL 


Target DE after query 




6. Re-engagement 

Query 

SELECT FirstName,
CASE 
 WHEN OpenCount = 0 THEN 'Re-engage'
 ELSE 'Active User'
END AS CampaignType
FROM Customer_master_Abu

Query in SQL 



Target DE after query 




7. Youth Campaign 

Query 

SELECT FirstName,
CASE 
 WHEN Age < 25 THEN 'Youth Campaign'
 ELSE 'General Campaign'
END AS Campaign
FROM Customer_master_Abu

Query in SQL 


Target DE after query 




8. VIP Segment 

Query 
SELECT FirstName,
CASE 
 WHEN PurchaseAmount > 1000 AND OpenCount > 10 THEN 'VIP'
 ELSE 'Normal'
END AS Segment
FROM Customer_master_Abu

Query in SQL 


Target DE after query 




9. Email Strategy 

Query 

SELECT FirstName,
CASE 
 WHEN OpenCount > 10 THEN 'Daily Emails'
 WHEN OpenCount BETWEEN 5 AND 10 THEN 'Weekly Emails'
 ELSE 'Monthly Emails'
END AS EmailStrategy
FROM Customer_master_Abu

Query in SQL 


Target DE after query 




10. Risk Identification 

Query 

SELECT FirstName,
CASE 
 WHEN OpenCount < 3 AND PurchaseAmount < 500 THEN 'At Risk'
 ELSE 'Safe'
END AS RiskLevel
FROM Customer_master_Abu

Query in SQL 


Target DE after query 







👉  SQL CASE - Important Notes (Advanced
Trainer Guide)

1. Use Searched CASE 

    Use CASE WHEN condition instead of simple CASE. 
    Supports complex business logic, such as engagement and purchase segmentation. 
    Used in real marketing decision-making. 

2. Always Use ELSE 

    Without ELSE, SQL returns NULL. 
    NULL values can break journeys and reports. 
    Always define the default output. 

3. Avoid CASE in WHERE 

    CASE in WHERE slows down queries. 
    SFMC processes rows row by row, so performance degrades. 
    Use direct filters instead. 

4. Maintain Data Type Consistency 

    All THEN and ELSE outputs must be the same data type. 
    Mixing text and numbers causes errors. 
    Ensure uniform return values. 

5. CASE for Segmentation 

    Used to divide users into groups. 
    Example: High, Medium, Low engagement. 
    Core logic for marketing campaigns. 

6. Combine with Aggregation 

    Use CASE with SUM/COUNT for conditional counting. 
    Useful in reporting and dashboards. 
    Example: count engaged users. 

7. Keep Logic Simple 

    Avoid deeply nested CASE statements. 
    Split logic into multiple columns if needed. 
    Improves readability and maintenance. 

8. Use Meaningful Aliases 

    Use clear column names like EngagementLevel. 
    Helps marketing and analytics teams understand output. 
    Improves professionalism. 

9. Test Before Production 

    Always validate logic on a small dataset. 
    Avoid wrong campaign targeting. 
    Prevents costly mistakes. 

10. Optimize Performance 

    CASE runs row-by-row. 
    Keep conditions minimal and efficient. 
    Avoid unnecessary complexity




Comments

Post a Comment

Popular posts from this blog

Use Cases of Standard Data Extensions

Use Cases of Standard Data Extensions 1. Customer Data Storage Scenario : A retail business wants to store customer information, such as names, email addresses, and purchase history, for email marketing campaigns. Solution : Use a standard data extension to store and manage customer data securely and efficiently. Data Extension Structure Field Name Data Type Length Required Primary Key SubscriberKey Text 50 Yes Yes FirstName Text 50 Yes No LastName Text 50 Yes No EmailAddress EmailAddress N/A Yes No PurchaseHistory Decimal N/A No No Email - Marketing Cloud
Read more

Data retention policy

Image
📌 Task: Create & Monitor Data Extensions with Different Retention Policies 🚀 By ElearningIT Feb 18, 2025 Your objective is to: ✅ Create three separate Data Extensions (DEs). ✅ Apply a different retention policy on each DE. ✅ Monitor & analyze the impact over time. 🔹 Step 1: Create Three Data Extensions (DEs) 1️⃣ Navigate to: Email Studio > Subscribers > Data Extensions. 2️⃣ Click "Create" → Select Standard Data Extension. 3️⃣ Define the fields for all DEs using the structure below: Field Name Data Type Length Primary Key SubscriberKey Text 50 ✅ Yes Name Text 50 ❌ No Email EmailAddress 254 ❌ No Phone Phone 50 ❌ No SignupDate Date - ❌ No 4️⃣ Create three Data Extensions with unique names: DE_IndividualRetention (For Individual Record Retention) DE_AllRecordsRetention (For All Records Retention) DE_FullDeletion (For Data Extension & Records Retention) 5️⃣ Import at least 100 records in each DE (via CSV or manual entry). 🔹 Step 2: Apply Different Retention P...
Read more