Skip to main content
Temas#Filter Data Extension

#Filter Data Extension4 debatiendo

Formular una pregunta...
Taylor Thomas preguntó en #Marketing Cloud
Ayer, 15:06
Supressing contacts from data extension

I pulled a list of unengaged subscribers that have not opened at least one of our last 3 monthly sends. I want to suppress these contacts from our filtered data extension which we use to send to. How do I do this? And how would I keep this automated to continuously remove unegaged contacts. 

 

#Marketing Cloud  #Filter Data Extension

1 respuesta
  1. Digbijaya Paikaray (CONFIDENTIAL)
    Hoy, 08:36

    Hi Taylor,

     

    The most robust, automated way to handle this in Marketing Cloud is using Automation Studio with a SQL Query Activity or by using an Exclusion/Suppression List at send time.

     

    Here are the two industry-standard approaches depending on how your sends are orchestrated:

     

    Option 1: Automation Studio + SQL Query (Recommended for Ongoing Automation)

    Instead of manually maintaining a filtered Data Extension (DE), let Automation Studio calculate the sendable list on a daily or monthly schedule.

     

    1. Create a "Sendable" Data Extension

     

    • Create a DE that contains the subscribers eligible to receive the newsletter (e.g., Monthly_Newsletter_Audience).

     

    2. Write a SQL Query Activity

     

    • Query your master audience and use a LEFT JOIN or NOT EXISTS against the _Open Data View for the past 90 days (3 months):

     

    SQL

     

    SELECT 
        m.SubscriberKey,
        m.EmailAddress
    FROM [Your_Master_Sendable_DE] m
    WHERE NOT EXISTS (
        SELECT 1 
        FROM _Open o
        JOIN _Job j ON o.JobID = j.JobID
        WHERE o.SubscriberKey = m.SubscriberKey
          AND j.EmailName IN ('Monthly_Send_1', 'Monthly_Send_2', 'Monthly_Send_3') -- or filter by Category/Date
          AND o.EventDate >= DATEADD(month, -3, GETDATE())
          AND o.IsUnique = 1
    )

    3. Automate in Automation Studio

     

    • Build a scheduled automation:
      • Step 1: SQL Query (Data Action: Overwrite to your Monthly_Newsletter_Audience DE).
      • Step 2: Trigger your Email Send or Journey.

     

    Option 2: Use an Auto-Suppression Configuration or Send-Time Exclusion

    If you prefer to keep your existing Filtered Data Extension as-is and simply suppress those unengaged contacts at the moment of send:

     

    • Send-Time Exclusion:
      1. Store your unengaged subscribers in a dedicated DE (e.g., Unengaged_Suppression_DE).
      2. In your Email Send Definition or Journey Builder Email Activity, expand Delivery Options.
      3. Select Unengaged_Suppression_DE in the Exclusion List / Exclusion Script field.
    • Auto-Suppression List (Account-Wide):
      • If you never want these unengaged subscribers receiving any commercial email, go to Email Studio > Subscribers > Auto-Suppression Lists, create a list, and populate it via an Automation Studio query.
Escribir una respuesta...
0/9000
Intkhab Samani (Salesforce Developer en Hytech professionals) publicó en #Marketing Cloud
23 jul, 16:41

How can I get all the Order Items in data Extension  

 

Query :  

SELECT 

    p.[FirstName], 

    p.[LastName], 

    p.[Id], 

    p.[Name], 

    p.[PersonContactId], 

    p.[PersonEmail], 

    p.[Membership_End_Date__c], 

    m.[Auto_Renew_Formula__c], 

    p.[Is_Member__c], 

    p.[PersonId__c], 

    p.[Deceased__c], 

    p.[Exclude_From_Renewal_Letters__c], 

    p.[Credit_Card_Expiration_Date__c], 

    p.[Member_Type__c], 

    p.[Membership_status__c], 

    p.[IsPersonAccount], 

    p.[Continuous_Member_Since_At_Least_Date__c], 

    p.[Exclude_From_Mail__c], 

    p.[AANP_Membership__c], 

    p.[Exclude_From_Email__c], 

    p.[PersonHasOptedOutOfEmail], 

 

    o.[TotalAmount], 

    o.[OrderNumber], 

 

    oi.[OrderId], 

    oi.[Product2Id], 

 

    pr.[Name] AS Product_Name, 

 

    pm.[Name] AS Payment_Method_Name, 

    pm.[ChargentBase__Card_Expiration_Month__c], 

    pm.[ChargentBase__Card_Expiration_Year__c], 

    pm.[ChargentBase__Card_Last_4__c], 

    pm.[ChargentBase__Card_Type__c], 

 

    

    co.[ChargentOrders__Charge_Amount__c], 

    co.[ChargentOrders__Payment_Start_Date__c], 

    co.[ChargentOrders__Payment_Status__c], 

 

    DATEDIFF(day, GETDATE(), p.[Membership_End_Date__c]) AS [Days_To_Expire] 

 

FROM [Person Account Master DE] p 

 

LEFT JOIN Membership__c_Salesforce m 

    ON p.[AANP_Membership__c] = m.[Id] 

 

LEFT JOIN ChargentBase__Payment_Method__c_Salesforce pm 

    ON p.[Auto_Renewal_Payment_Method__c] = pm.[Id] 

 

LEFT JOIN Order_Salesforce o 

    ON m.[Order__c] = o.[Id] 

 

LEFT JOIN OrderItem_Salesforce oi 

    ON o.[Id] = oi.[OrderId] 

 

LEFT JOIN Product2_Salesforce pr 

    ON oi.[Product2Id] = pr.[Id] 

 

 

LEFT JOIN ChargentOrders__ChargentOrder__c_Salesforce co 

    ON o.[Id] = co.[Standard_Order__c] 

 

WHERE 

    DATEDIFF(day, GETDATE(), p.[Membership_End_Date__c]) = 14 

    AND p.[Auto_Renew__c] = 'true' 

    AND p.[Is_Member__c] = 'true' 

AND p.[Deceased__c] ='false' 

    AND p.[Membership_Status__c] = 'Active' 

    AND p.[IsPersonAccount] = 'true' 

    AND p.[PersonEmail] IS NOT NULL 

    AND p.[Credit_Card_Expiration_Date__c] IS NOT NULL 

    AND p.[Credit_Card_Expiration_Date__c] > p.[Membership_End_Date__c] 

 

Based on abovve Query I m getting Exact Record In Query Editor the Same resullt I want to have In DE as well but what happening instaed i m Getting Only 1 Result in DE  

 

How can I get all the Order Items in data Extension Query : SELECT p.[FirstName], p.[LastName], p.[Id], p.[Name], p.[PersonContactId], p.[PersonEmail], p.[Membership_End_Date__c], m.

 

 

Data Extension Congiguration.png

 

 

Query Studio - Multiple Entries.png

 

 

 

#Marketing Cloud  #Filter Data Extension

1 comentario
  1. Mradul Srivastava (Pepsico)
    24 jul, 05:35

    @Intkhab Samani

    Query Studio results doesn't include primary key. However, the Data Extension where you are storing the data has only 1 primary key field - ParentContactID. 

    So, if you want all three records to be stored in the Data Extension, you will need to either -

    • Remove the primary key, or
    • Create a composite primary key using additional fields.
Escribir un comentario...
0/9000