Expand my Community achievements bar.

SOLVED

Set ID for Exclusion

Avatar

Level 2

Hello!

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?

For Example:

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?

1 Accepted Solution

Avatar

Correct answer by
Employee

I'm not 100% sure I understand the use case, but here is what I'd try:

Query A: recipients where type = prospect

Query B:

- 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.

View solution in original post

3 Replies

Avatar

Employee

I think you can do so in the query activity itself.

  • Drop a query activity
  • Set Targeting Dimension as Recipient
  • Set Filtering Dimension as Household (as you said it is linked to Recipient)
  • Then move to next screen to define the filtering condition
  • Specify householdID not equal to xyz (your input value)
  • You can use Not In operator for multiple householdIDs.

Output will be all recipients that do not associate themselves with a particular householdID, exactly what you are looking for.

Regards,
Vipul

Avatar

Level 2

Hi Vipul,

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.

Lindsay

Avatar

Correct answer by
Employee

I'm not 100% sure I understand the use case, but here is what I'd try:

Query A: recipients where type = prospect

Query B:

- 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.