SQL-CASE-WHEN STATEMENT- TASK

First of all we created the Source DE : Push_Case_When_DE 


Target DE created Named : Push_CaseWhen_Target_DE


TASK : 1 

1. Age Group Segmentation:

Query :

SELECT FirstName,
    CASE
        WHEN Age < 18 THEN 'Minor'
        WHEN Age between 18 and 40 THEN 'Major'
        ELSE 'Senior'
    END AS AgeGroup
FROM Push_Case_When_DE




Result fetched with Column AgeGroup stating : Condition per Age group




Task 2

2. Engagement Level :

Query :
SELECT  FirstName,
    CASE
        WHEN OpenCount > 10 THEN 'High'
        WHEN OpenCount between 5 and 10 THEN 'Medium'
        ELSE 'Low'
    END AS Engagement
FROM Push_Case_When_DE







Result fetched with Column Engagement stating : OpenCount numbers Level




Task 3 : 

3. Greeting :

Query :

SELECT FirstName,
CASE
        WHEN Country = 'India' THEN 'Namaste'
        WHEN Country = 'USA' THEN 'Hello'
ELSE 'Hi'
END AS [Greeting]
FROM Push_Case_When_DE





Result fetched with Column Greeting stating : Greetings as per Country




Task 4 

Status Mapping:
SELECT FirstName,
CASE
        WHEN Status = 'A' THEN 'Active'
        WHEN Status = 'I' THEN 'Inactive'
ELSE 'Unknown'
END AS StatusLabel
FROM Push_Case_When_DE

Query :




Result fetched with Column StatusLabel stating : Active or Inactive Status



Task 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 Push_Case_When_DE




Result fetched with Column StatusLabel stating : Active or Inactive Status


Task 6:

Re-engagement : 

Query :

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




Result fetched::: with Column CampaignType 



Task 7 :   Youth Campaign :

Query :

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






Result : Column with Name - Campaign Returned with records


Task 8. VIP Segment

Query:

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




Result : 


Task 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 Push_Case_When_DE



Result :



TASK : 10

Risk Identification

Query:

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



Result :

















































Comments

Post a Comment

Popular posts from this blog

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

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