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

Question: Cant we use just 1 field from the DE in the query for Select & Group by, like just check for duplicate email address, Why do we have to enter the complete fields.

Target DE:

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)

19. Exclude any inactive users (Last 90 days)

20. Exclude Specific Campaign Audience

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

External Content Block/ Enhance Dynamic/ Send Management/ Code Snippet

 External Content Block (Repair Pricing) https://mc.s10.exacttarget.com/cloud/#app/Email/C12/Default.aspx?entityType=none&entityID=0&ks=ks%23Content External content is any content stored outside your platform (e.g., websites, APIs, content management systems, external databases). It can include: ✅ Product descriptions & pricing (from an e-commerce website) ✅ Blog posts & news feeds (from external websites) ✅ Social media posts (Twitter, Instagram, LinkedIn feeds) ✅ Meta descriptions & templates ✅ Weather updates & live data feeds ✅ Customer-specific data (from CRM or CDP systems) In Salesforce Marketing Cloud ( SFMC ) , the External Content Block allows marketers to fetch real-time content from external URLs (APIs, websites, or content servers) and insert it into email templates dynamically. Updated URL> Link Validated> Retrieival Method: Immediately Retrieval Options: Subscriber Level( Content is retrieved separately for each recipient ) ...
Post a Comment
Read more