How can I do a hierarchy with dates?

[Javi_Landa]

Hi, i need to send 40.000 emails to a group of recipients. 

My criteria is to send first to recipients who have never been contacted (empty broadlog) and if i do not complete my limit of 40.000, i want to include recipients contacted before but prioritizing the oldest event date. So i select first the recipients that i didnt contacted for a while.

I am struggling to find a way to do it. Because the split is limiting those 40,000 randomly...

Do you guys know a way of solving this case?

Thank you!

[aggabhi · Accepted solution]

Hi @Javi_Landa ,

Seems you are struggling to build a logic for same. As per my understanding logic goes like :-

It goes like below :-

1. Start the main query activity on recipient table and selecting the people who are eligible.

2. To check if those exist in broadlog or not double check with any broadlog field for those records that that fields should be empty for same record as same record has not been targeted so there should not be any delivery associated with same customer.

3. Now use Test activity that records are more than 40000 or less than 40000, if more than 40k than you can simply go to delivery activity and use split before delivery activity for restricting the same to 40k.

4. If counts are less than 40k, then use one more query activity which will run in parallel using fork activity and from there you can pick the people whose broadlog exist and then using split to sort the population on basis of event date and then do union with actual population.

This is basic design, you can also use javascript to check the counts and based on counts we can pick the leftover population in main target.

I am happy to discuss more,

Regards

Abhishek

[Javi_Landa]

Great!

What  i have it all clear until the 4th step. Once i have the test bellow 40,000, how can i be sure that i am including the oldest event dates?

If i do a query with my recipients event dates, even if i create a split and i sort it, once i do a union activity with my empty dates (never contacted), the union will be random and is not going to pick the oldest.

Thank you!

[aggabhi]

Hi @Javi_Landa ,

Basic design will go like below :

Now in split activity you have to sort on basic of event date following below steps :-

1. Choose below Option

2, Now choose below (add event date as variable on which descending sort will happen and person with latest event date will come on top or vice versa)

You can also make changes as per your requirement, again this is basic design, lots of improvements can be done which might take some time here so will not able to explain untill I myself give some time.

Regards

Abhishek

[Javi_Landa]

Thank you very much, i will be very grateful if you could invest some time here. I am almost there but the problem is that i am not getting the right results. I have got my recipients that have not been contacted (almos 35,000) and then i have a lot of contacted recipients (almost 700,000). 

I´ve sorted this 700,000 by event date by oldest date but when i do an union and a slpit limiting 40,000, i am getting random dates...

[aggabhi]

Hi @Javi_Landa ,

No when you got 700000, you should only take 5000 from split activity doing sorting on event in descending order and then add the same population with union on 35000.

Now this will make 40k. 

This will solve your case when you know that you have to choose 5k only, now number can be dynamic, for that dyncamic number you can he javascript to calculate number on every run and use that global variable to pick only those records from split activity.

Regards

Abhishek

[Javi_Landa]

That's it! the issue is that i want to do run this campaign every day so i cant predict the numbers....