Skip to main content

Use Cases: % Like Operator - Pattern Matching || Not/Exclusion Logic

 %(LIKE OPERATOR) - Use Cases

% is used for pattern matching (Partial Text Search)

Source DE:



1. Find Gmail Users: 

SQL Query: Select *

From [Source DE_Like Operator]

Where EmailAddress LIKE '%@gmail.com'



Target DE:


2. Users whose name starts with 'A'

SQL Query:  Select *

From [Source DE_Like Operator]

Where FirstName LIKE 'A%'

Target DE:



3. Users whose name ends with 'Kumar'

SQL Query: Select *

From [Source DE_Like Operator]

Where LastName Like '%Kumar'

Target DE:




4. Find users with 'Sale' in email (Campaing Filtering)

SQL Query: Select *

From [Source DE_Like Operator]

Where Emailaddress LIKE '%Sale%'

Target DE:



5. Find customers with cities contacining "Pur"

SQL Query: Select *

From [Source DE_Like Operator]

Where City LIKE '%pur%' 

Target DE:



6. Target users with mobile number starting with "9"

SQL Query: Select *

From [Source DE_Like Operator]

Where PhoneNumber LIKE '9%' 

Target DE:



7. Filter product names containing "Bottle"

SQL Query: Select *

From [Source DE_Like Operator]

Where ProductName LIKE '%Bottle%' 

Target DE:

8. Journey Re-entry check (Email pattern)

SQL Query: Select *

From [Source DE_Like Operator]

Where EmailName LIKE '%Welcome%' 

Target DE:



9. Identify Test Records:

SQL Query: Select *

From [Source DE_Like Operator]

Where EmailAddress LIKE '%test%' 

Target DE:



10. Domain-Based Segmentation:

SQL Query: Select *

From [Source DE_Like Operator]

Where EmailAddress LIKE '%@yahoo.com' 

Target DE:


Not/Exclusion Logic

In SQL, -Itself is subtraction, but in SFMC context, people mean Not/Exclusion Logic (very common in segmentation)

Question: Difference between Not Like & !=

Can we not use two Where syntax in the same automation?

11. Exclude Gmail Users:

SQL Query: Select *

From [Source DE_Like Operator]

Where EmailAddress NOT LIKE '%@gmail.com' 

Target DE:



12. Exclude Unsubscribed Users:

SQL Query: Select *

From [Source DE_Like Operator]

Where Status NOT LIKE '%Active%'

Select *

From [Source DE_Like Operator]

Where Status != 'Unsubscribed'

Target DE



13. Remove Bounced Emails:

SQL Query: Select *

From [Source DE_Like Operator]

Where BounceCount = 0

Target DE:



14. Exclude Test Data: 

SQL Query: Select *

From [Source DE_Like Operator]

Where EmailAddress NOT LIKE '%Test%'

Target DE:


15. Exclude Specific Domain:

SQL Query: Select *

From [Source DE_Like Operator]

Where EmailAddress NOT LIKE '%@gmail.com%'

Target DE:


16. Exclude Duplicate records(Basic):

SQL Query: Select SubscriberKey, FirstName, LastName, EmailAddress, PhoneNumber, City, ProductName, EmailName, Status, BounceCount, CustomerID, LastLoginDate

From [Source DE_Like Operator]

Group By SubscriberKey, FirstName, LastName, EmailAddress, PhoneNumber, City, ProductName, EmailName, Status, BounceCount, CustomerID, LastLoginDate

Having Count (*) = 1

Target DE:


This code has also worked for 16 by creating a separate Target DE Fields:

Select SubscriberKey, 

EmailAddress

From [Source DE_Like Operator]

Group By SubscriberKey, 

EmailAddress

Having Count (*) = 1


17. Exclude Customer who already purchased: - Need to create a separate DE for purchases



SQL Query: Select *

From [Source DE_Like Operator]

Where CustomerID Not in (Select CustomerID From [SQL_NotLike_OrderDetails])

Target DE:




18. Supression List Inclusion (Very common in SFMC)

3 DE used in this. 1- Source DE, 2. Supression DE with only email address field and records and 3. Target DE





SQL Query: Select *

From [Personalisation DE test]

Where EmailAddress Not in (

Select EmailAddress

From SupressionDE)

Target DE:  It sees your existing DE and source DE email address and find the already existing subscribers.




19. Exclude any inactive users (Last 90 days)

SQL Query: Select *

From [Source DE_Like Operator]

Where LastLoginDate > DATEADD(day, -90, GETDATE())

Target DE:


20. Exclude Specific Campaign Audience

SQL Query: Select *

From [Source DE_Like Operator]

Where SubscriberKey Not In (Select SubscriberKey

    From [Personalisation DE test])


Personalisation DE Test:



Target DE: 



**WHERE SubscriberKey NOT IN (

   SELECT SubscriberKey FROM Sent_Data_Extension

)

🔥 Interview Tip (Very Important)

👉 Combine % + exclusion (real-world scenario):




SELECT *

FROM Contacts

WHERE EmailAddress LIKE '%@gmail.com'

AND EmailAddress NOT LIKE '%test%'

💡 Use case:

 Target real Gmail users but remove test data → very common in campaigns


(SFMC Specific)

% → Used heavily in Audience Segmentation 


NOT / exclusion → Used in Suppression, Journey filtering, Deduplication 


 Both together → Production-ready queries



Comments

Post a Comment

Popular posts from this blog

A/B Testing

Confluence link 1:  https://elearningit24.atlassian.net/wiki/external/MzA3YWUxNjI3YzBkNGZiZmFlZTc1ZGIzYmU1ZmM1MmQ   What is A/B Testing? A/B testing, also known as split testing , is a controlled experiment where two versions of a webpage, email, ad, or any digital asset are tested against each other to determine which one performs better. Why? Higher open rates Better Engagement Increased Sales and Lead conversion Reduced unsubscribed rates Date Driven- No assumption & Proven Results Test Type Subject Line Email Content Area From Name Send Date & Time Pre Header   Email Test Template: Friendly and Personalised Straight to the point & Action Oriented Confluence Link 2:  https://elearningit24.atlassian.net/wiki/external/NDk0Zjg0ODk3YjAwNDI5ZjkxOTVmMTAxNTQ3ZjlhNmQ Subject Line A/B Test: A/B Testing Use Case: Optimizing Email Subject Lines for Higher Open Rates Scenario- Subject Line: A company wants to increase the open rates of its promotional emails in Sa...
Post a Comment
Read more

Email Marketing Campaings

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 Sample Records SubscriberKey FirstName LastName EmailAddress PurchaseHistory 001 John Doe john.doe@example.com 1500.50 002 Jane Smith jane.smith@example.com 200.00 https://mc.s8.exacttarget.com/cloud/#app/Email/C12/Default.aspx?entityType=none&entityID=0&ks=ks%23Subscribers
Post a Comment
Read more

SQl Query

  Workflow: Journey Builder> Automation Studio> New Automation Enter Query: Drag and drop Schedule in  Start with a Starting Source and the SQL Query> Choose Query> Create New Query Activity> Name your file and add your query (Field name thats part of the DE you create separately- Example: FirstName, LastName, Subscriber Key) Create a new DE in Email Studio with the query field> Select The test DE which was created: Select Target DE and Save & Run Workflow: Select All Activities and Run Workflow: Activity Details: Run Completed with updated DE Edit/ add another query: Copy Source DE and create a new DE with same fields that we are running in SQL: Run the updated SQL Query added Activity and DE updated with Records: Completed Status: Received error: When the Query field and the DE created field does not match When an existing Query is being run, if we try to run another query Fixed the error by matching same field Waiting for existing query to complete
Post a Comment
Read more