I am trying to exclude records using an ID from a table linked to the targeting Dimension. Is it possible to set the ID used in the exclusion activity?
I have recipients table linked to a household table. I want to exclude any recipients who are part of a household using the household ID, though the household ID is not defined as the primary key of the table.
What is the best way to do this?
I'm not 100% sure I understand the use case, but here is what I'd try:
Query A: recipients where type = prospect
- Targeting dimension: Recipient
- Filtering dimension: Contract
Conditions: contract = (whatever type of contract that you want to exclude)
In both queries, select the "Household Id" as an additional field.
Join the two queries together into an exclusion and make Query A your primary set.
Under the "Exclusion rules for incompatible targeting dimensions", add a new row and choose Query B as your Exclusion set.
Set "Change dimensions" to "Joins" and under Source, choose the Household field from Query B and under Destination, choose the Household field from Query A.
If I understood the use case correctly, I think this will do what you're looking for.
I think you can do so in the query activity itself.
Output will be all recipients that do not associate themselves with a particular householdID, exactly what you are looking for.
Thank you for your response. I am actually looking to exclude a number of households based on a field in another table linked to recipient. I apologize I didn't provide all requirements initially. A little more detail:
Select all recipients that are type = prospect. Exclude any prospects that have the same household ID as recipients that have a certain type of contract. Contract is a one-to-one linked table to recipients. The primary key is ID.
Does that clarify what i am trying to do? I wouldn't want to hard code the household IDs as they may change as new contracts are added.
Thanks in advance for your insight.