Power BI and (can't find the) Prospect Status and Classification

Reuben Schmitz

I am looking through the data sets avaialble with the Power BI and Blackbaud integration. I don't see the Prospect Status and Classification fields as options. Am I just missing them?

Ben Regier

I don't see them either. They don't seem to be included in the Constituent Entity. There is an endpoint for Prospect Status, but it's not one of the list endpoints so it wouldn't show up in the BI connector.

Reuben Schmitz

Thanks for responding Ben. I wanted to make sure I wasn't missing something. Again, an endpoint you would think would be included. I will have to see how we can pull this in another way - unless someone else has a workable solution to this they can share.

Matt Thacker

The prospects status is available in the current connector.

Unfortunately, the Prospect Status endpoint is a single call API endpoint, not a paginated endpoint like the others. I'm working on figuring out how to utilize this functionality within the existing connector. I'll keep you updated on the status. But I'd definitely consider the Power Automate workaround in the interim.

Reuben Schmitz

Thanks for this info Matt - I will likely use an Excel import in the meantime to make this data available.

Hallie Guiseppe

@Reuben Schmitz
Hi Reuben, any update on this end point that you have discovered?

Alex Wong

@Reuben Schmitz
I think there is really only 2 options

1. use the API to loop through each constituent to get their prospect status
   1. benefit is you get info about days_elapsed meaning how long the constituent is in that status
   2. problem is if you have 100K constituent, that's 100K API calls you have to make, which is way over the normal API limit of 25k per 24hr, even if you only do sync your report once a day
2. use RE database view to export the prospect status, if you have RE:Queue, you can automate the export and get the data in csv that you can then store somewhere to be “pulled” up by Power BI
   1. benefit, no problem with API limits, sync as often as you want
   2. problem is, you will not get how long the constituent was in this status, as there is no way (at least I did not find it in query, export, not import/create file) to get the date/time of when the status was created/last modified.

Mark Haiar

@Matt Thacker
I am currently dealing with this issue, what is the power automate workaround? We need the date and comment out. Thank you!

Alex Wong

@Mark Haiar @Reuben Schmitz
Another option has become available this year (2024), Query API. This is a API programmable option compare to #2 I mentioned. Basically removing the need for RE:Queue (scheduling of exporting of a query in dbview), instead call SKY API endpoint to run a dbview query and then get its results. I am currently using Query API to get prospect classification and status of constituent.

However, query from DB view will not get you date nor comment, as they are exclusive to webview. (these info does not exists in the original RE database table schema).

for “date” you can probably work this out more easily by building a logic to calcuate this based on query api data of the prospect status that you get every night (at least once in a 24hr period). However, there is no way to get the comment without (1) using the option #1 I mentioned in my earlier post or (2) wait for Blackbaud to expand SKY API to include these info in a “list” endpoint (you can search and voteup on the IdeaBank.

Sarah StarrZechman

@Alex Wong
Are there still limits on the API Calls? Can you talk more about the process to connect power BI using queue and power automate? Is there a how to anywhere?

Alex Wong

@Sarah StarrZechman
There are many limits with SKY API. so it is hard to list them. It is best to ask question of what you trying to do and if SKY API supports it. You can check out developer.blackbaud.com to get a glimpse of what SKY API has available, too.

As for “how to”, there are many post that share how to do certain thing, or someone is trying to do something and ask for help b/c they are stuck, these are all good post for you to look into.