Skip to main content
nick_emmett
nick_emmett
Contributor ⭐️⭐️
April 18, 2019
Solved

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

  • April 18, 2019
  • 2 replies
  • 73 views
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.

2 replies

dan_ahrens
dan_ahrensAnswer
Expert ⭐️⭐️⭐️
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.

VP of Customer Success at Forcepoint
john_apple
john_apple
Expert ⭐️
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)?

Terms & ConditionsAccessibility statement