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
Solved
Journey Orchestra Powerlist - how to specify a blank date field from Salesforce
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 originalInstead 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.
+2Hi 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.
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.
+5Isn'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
Sign up
If you ever had a profile with us, there's no need to create another one.
Don't worry if your email address has since changed, or you can't remember your login, just let us know at community@gainsight.com and we'll help you get started from where you left.
Else, please continue with the registration below.
Welcome to the Gainsight Community
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.