Expand my Community achievements bar.

SOLVED

Typology rule - get recipients who have received 'n' deliveries

Avatar

Level 3

How can we create a filter typology rule to exclude recipients that have received less or more than 'n' deliveries?

 

campaignap_1-1639368801345.png

campaignap_2-1639369095760.png

 

 

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

Hello @campaignap ,

You will need to set your query as follows

First screen:

Marcel_Szimonisz_0-1640036900888.png

Select filtering condition:

Marcel_Szimonisz_1-1640036956607.png

How you do it?

  • Click on select field and find the link recipient broad log

Marcel_Szimonisz_2-1640037009826.png

 

  • will get you something like

Marcel_Szimonisz_3-1640037044267.png

 

  • double click on the expression and add any aggregation you like. In this case we add COUNT

Marcel_Szimonisz_4-1640037112942.png

 

  • change the operator and right hand side of the condition

Marcel_Szimonisz_5-1640037157978.png

  • add more conditions  to your selection. You can add time restrictions as well as status restrictions. Or if not needed remove the empty line or click on finish

Marcel_Szimonisz_6-1640037197645.png

 

  • See the preview or double check the SQL if you have  got what you wanted (if you feel like so)

 

SELECT   R0.iRecipientId FROM NmsRecipient R0 WHERE (SELECT   COUNT(*) FROM NmsBroadLogRcp B1 WHERE (B1.iRecipientId = R0.iRecipientId) AND (((B1.iStatus = 1)))) < 5 LIMIT 10001
  • ...

 

 

  • profit

 

Marcel

View solution in original post

2 Replies

Avatar

Level 2

Hi,

 

Hope you are doing well.

 

You could query the Brodlog table and see in how many deliveries a particular recipient is targeted, the same could be done on profile schema as well.

 

Regards,

Akshey Malhotra

Avatar

Correct answer by
Community Advisor

Hello @campaignap ,

You will need to set your query as follows

First screen:

Marcel_Szimonisz_0-1640036900888.png

Select filtering condition:

Marcel_Szimonisz_1-1640036956607.png

How you do it?

  • Click on select field and find the link recipient broad log

Marcel_Szimonisz_2-1640037009826.png

 

  • will get you something like

Marcel_Szimonisz_3-1640037044267.png

 

  • double click on the expression and add any aggregation you like. In this case we add COUNT

Marcel_Szimonisz_4-1640037112942.png

 

  • change the operator and right hand side of the condition

Marcel_Szimonisz_5-1640037157978.png

  • add more conditions  to your selection. You can add time restrictions as well as status restrictions. Or if not needed remove the empty line or click on finish

Marcel_Szimonisz_6-1640037197645.png

 

  • See the preview or double check the SQL if you have  got what you wanted (if you feel like so)

 

SELECT   R0.iRecipientId FROM NmsRecipient R0 WHERE (SELECT   COUNT(*) FROM NmsBroadLogRcp B1 WHERE (B1.iRecipientId = R0.iRecipientId) AND (((B1.iStatus = 1)))) < 5 LIMIT 10001
  • ...

 

 

  • profit

 

Marcel