Actions linked to Opportunities and calculating close rates

James Parsons · February 4

I'm working on some conversion rates in Power BI; one I recently completed was the close rate, which we define as the number of actions (with a type indicating a qualified prospect) divided by the number of funded opportunities.

We realized early on that because a constituent can have any amount of both actions and opportunities, there's no clear way to correlate these except when they're linked. This hadn't been consistently done so we're backfilling what we can.

The calculation depends on this link now and we got a number (~29%) that seems close to industry standard (1:4). But I'm having trouble confirming accuracy based on raw data from NXT.

This is because although I can pull all qualified actions that are linked (i.e. in query, the Action Opportunity Name is not blank), I can't pull the opportunity ID linked to that action. I also can't pull the Opportunity ID from a Constituent query. Export is messy because I need to know how many opportunities exist on a given record.

I tried a merged query and the best I seem to be able to do is to pull the opportnity name where an action is linked to it — but because some opportunties/proposals are shared between constituents there's no unique key to pull.

Has anyone come across this? Any way to pull the opportunity system record ID consistently? I'm confident in the calculation, but it would be nice to verify!

Dan Snyder · February 5

@James Parsons Congrats on getting this set up and you have identified what I consider to be a pretty significant reporting gap with the inability to get any sort of opportunity id with the native query/export tools. The only place I am aware of that you can pull the opportunity system record id is via the API but that would require calls to Get Opportunity and Get Action as they both can output the id you are looking for. Not sure how you are getting your data out for your Power BI reports, but you could likely get this information using Power Automate.

Also vote for these ideas:
https://renxt.ideas.aha.io/ideas/RENXT-I-8907
https://renxt.ideas.aha.io/ideas/RENXT-I-7994
https://renxt.ideas.aha.io/ideas/RENXT-I-8337
https://renxt.ideas.aha.io/ideas/RENXT-I-8804

James Parsons · February 5

Ah — yes I wondered about Power Automate. That's unfortunate; PA would work but it's just one more data source for an already crowded report. I'm looking at ways to better use the Query API so maybe I'll consider this as part of that project. In the meantime it would do well enough for testing/verification of my numbers. Thanks!

Alex Wong · February 6

the only way to achieve what you want to do is by using list API, not Query (nor Query API).

List Opportunity

List Action

The options:

use the unofficial Power BI connector for Blackbaud SKY API, which requires manual refresh OR setup a gateway for automated refresh
use Power Automate flow to run SKY API and pump the data into Power BI, does not require gateway for auto-refresh, but you will need Power Automate license and experience.

Lucy Ruiz · February 10

@Dan Snyder - thank you for linking the idea bank links. Yes, this has been the bane of my existence. In a pinch, I have created my own Opp record ID's by using Op Name (assuming its unique), opp date created and cons id. Its not pretty but it helps.

James Parsons · February 12

@Alex, thanks, this is bout what I ended up doing. I now have a Power Automate flow that pulls the Action ID and Opportunity ID linked to that action and pushes it to a csv in Sharepoint which I use as a source in my Power BI report. It's not perfect but it's working and I was able to confirm my calculations. It's too bad there's not a native way to do this in NXT but at least I've got this solution in my back pocket now.

@Lucy I'd be happy to share this flow if you're interested and have a license for Power Automate.

Actions linked to Opportunities and calculating close rates

Answers

Categories