@heather_hansen, I’ve done this using the rules engine in the past. If you query all your customers and query all health scores, you can merge the data together and pull a list of companies with and without the manual scores. From there, you can add a flag or the actual score on all customer company records. If you want to pull in the date it was last updated, I would recommend bringing that in with the rule as well onto the company. Once you have the data, you should be able to pull it all into a report.
Would love to hear if anyone has any easier solutions for this.
Thanks, @jean.nairon! I’m still struggling with the rules engine. So, I have one dataset that’s a list of companies and then a second dataset from the scorecard with measure names and scores. So, let’s say my measure that I need to add to accounts with NULL is Sentiment. The Sentiment measure (name or score) doesn’t show up in the scorecard dataset for accounts that don’t have one. So, when I do the merge, I keep all accounts from the left dataset, and then, for the actions, try to set the filters to look for accounts where the input type is MANUAL and the measure name doesn’t equal Sentiment. (So, I don’t overwrite any that do have a score). When I do that, I get 0 even though I know there should be about 200.
I would use Data Designer here considering that your end result is a report.
First dataset: All customers (Left)
Second dataset: All accounts with an update on Sentiment dataset (Right)
Merge: Left join
Resultant records will have a null entry on activist id (So filter on activity id = Null).
Good trick. Thanks @meenal_shukla! This option might be your easiest bet if you are just looking to create the report. If you are looking to trigger something like a CTA to CSMs, you will need the rule so I’ll give you the steps for that as well.
For the rule, you mentioned:
So, when I do the merge, I keep all accounts from the left dataset, and then, for the actions, try to set the filters to look for accounts where the input type is MANUAL and the measure name doesn’t equal Sentiment. (So, I don’t overwrite any that do have a score). When I do that, I get 0 even though I know there should be about 200.
I’m not sure I fully follow what you mean here but here’s what I would recommend. For the first data set, you should have a list of all companies filtered down to just your customers. This should include Let’s use that as the left dataset in the merge.
For the second data set, I recommend to pull the score and the Company GSID. You will need to filter down the query to just one measure. If you want to check multiple measures, you can do that but you will need multiple separate queries. This data set will be the right data set.
In the merge task, retain all records on the left. So in the end you will have a table that includes theses fields:
- Company GSID (or you can use any other UID you like)
- Manual Measure 1 Score
- And you can add additional measures if you do a 3rd or 4th query.
Let’s say you have a 1000 customers and only 600 have scores filled out. The end results of your query should include all 1000 customers and 600 of them will have the scores. The other 400 will have empty fields.
If you want to trigger a CTA from here, you can filter down to the score = Null in the Action tab.
Hope this makes sense and helps you find the right solution for your challenge.
Thanks, @jean.nairon and @meenal_shukla! I was able to get it to work finally! I really appreciate it!
Great conversation @heather_hansen. I didn’t even know that I needed to know this!
*Bookmarked* 
You’re welcome @heather_hansen!
@darkknight created an idea for this that might help. Here it is: