SQL Use Cases

 

1. Age Group Segmentation

SQL Query

Select CustomerID, 

FirstName, 

Age,

Case

When Age < 18 Then 'Minor'

WHEN AGE BETWEEN 18 AND 40 THEN 'ADULT'

Else 'Senior'

END AS AgeGroup 

FROM [Customer DE- Age SQL]

2. Engagement Level

SQL Query

SELECT FirstName,

OpenCount, 

CASE

WHEN OPENCOUNT > 10 THEN 'HIGH'

WHEN OPENCOUNT BETWEEN 5 AND 10 THEN 'MEDIUM'

END AS ENGAMENTLEVEL

FROM [Customer DE- Age SQL]

*Missed to use the  ELSE 'Low' in query so the records shows "0" as record count which is not defined. And field also was not defined as per the “end engagement”

Check the last SQL query with target DE (combined) to find the right target DE

3. Greeting

SQL Query

SELECT FirstName,

Country,

CASE

WHEN COUNTRY = 'INDIA' THEN 'NAMASTE'

WHEN COUNTRY = 'USA' THEN 'HELLO'

ELSE 'HI'

END AS GREETING

FROM [Customer DE- Age SQL]

4. Status Mapping

SQL Query

SELECT FirstName, 

Status,

CASE 

 WHEN Status = 'A' THEN 'Active'

  WHEN Status = 'I' THEN 'Inactive'

   ELSE 'Unknown'

   END AS StatusLabel

   FROM [Customer DE- Age SQL]

5. Purchase Segmentation

SQL 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 DE- Age SQL]

6. Re-engagement

SQL Query

SELECT FirstName,

CASE

 WHEN OpenCount = 0 THEN 'Re-engage'

  ELSE 'Active User'

  END AS CampaignType

  FROM [Customer DE- Age SQL]

7. Youth Campaign

SQL Query

SELECT FirstName,

CASE

 WHEN Age < 25 THEN 'Youth Campaign'

  ELSE 'General Campaign'

  END AS Campaign

  FROM [Customer DE- Age SQL]

8. VIP Segment

SQL Query

SELECT FirstName,

CASE

 WHEN PurchaseAmount > 1000 AND OpenCount > 10 THEN 'VIP'

  ELSE 'Normal'

  END AS Segment

  From [Customer DE- Age SQL]

9. Email Strategy

SQL 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 DE- Age SQL]

10. Risk Identification

SQL Query

SELECT FirstName,

CASE

 WHEN OpenCount < 3 AND PurchaseAmount < 500 THEN 'At Risk'

 ELSE 'Safe'

 END AS RiskLevel

 From [Customer DE- Age SQL]

** Tried entering 3 combined SQL Query and target DE with three fields

Select

FirstName,

CASE

 WHEN Age < 18 THEN 'Minor'

  WHEN Age BETWEEN 18 AND 40 THEN 'Adult'

   ELSE 'Senior'

   END AS AgeGroup,

   

   CASE

   WHEN OpenCount > 10 THEN 'High'

    WHEN OpenCount BETWEEN 5 AND 10 THEN 'Medium'

     ELSE 'Low'

     END AS Engagement,

    

     CASE

      WHEN Country = 'India' THEN 'Namaste'

       WHEN Country = 'USA' THEN 'Hello'

       ELSE 'Hi'

       END AS Greeting

       From [Customer DE- Age SQL]