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!
