Skip to main content
Solved

Journey Orchestra Powerlist - how to specify a blank date field from Salesforce


nick_emmett
Hi guys, I'm trying to build a Power list that shows Community Users that have not posted/logged in or whatever in either a) the Last 30 Days OR b) Ever!



So one field needs to show the Last 30 days filter and another needs to filter if the field is actually blank as it's never happened.



Can't figure this one out for the life of me!



Cheers in advance



Nick


Best answer by dan_ahrens

Hi Nick, I don't believe you'll be able to do this with a powerlist, as what you're really trying to do is report on absence of data, which a powerlist can't really handle.



Instead use a bionic query and accomplish this via three data tasks:



1 - fetch all users (and any other fields you'd want from those users to use in your outreach)



2 - fetch user name and SUM of post count (those two fields only), with a filter of 30 days (or whatever timeframe you want to search for). This will give you a count of the number of posts in that timeframe.



3 - merge those two data sets doing a left merge with fetch 1 on the left. This will give you a field added to your fetch 1 dataset that shows the count of posts in that timeperiod defined in step 2.



The reason this works is that you want to have a separate data set that shows all users regardless of post activity. Then you can filter to send this outreach only to users that have a post count of 0.


View original
Did you find this topic helpful?

2 replies

dan_ahrens
Forum|alt.badge.img+2
  • Expert ⭐️⭐️⭐️
  • 1984 replies
  • Answer
  • April 18, 2019
Hi Nick, I don't believe you'll be able to do this with a powerlist, as what you're really trying to do is report on absence of data, which a powerlist can't really handle.



Instead use a bionic query and accomplish this via three data tasks:



1 - fetch all users (and any other fields you'd want from those users to use in your outreach)



2 - fetch user name and SUM of post count (those two fields only), with a filter of 30 days (or whatever timeframe you want to search for). This will give you a count of the number of posts in that timeframe.



3 - merge those two data sets doing a left merge with fetch 1 on the left. This will give you a field added to your fetch 1 dataset that shows the count of posts in that timeperiod defined in step 2.



The reason this works is that you want to have a separate data set that shows all users regardless of post activity. Then you can filter to send this outreach only to users that have a post count of 0.



john_apple
Forum|alt.badge.img+5
  • Expert ⭐️
  • 468 replies
  • April 18, 2019
Isn't it possible to have the query look for Null values on the last login/post field and then just use an OR in the logic (e.g. Last Post < Subtract 30 days from Rule Date OR Last Post = Null)?



Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings