Sharing my Power Automate Flow: Moves Management Time in Stage

Dan Snyder

Summary
This Flow will allow you to more easily track time in stage by adding an action to a constituent's record on the day that their status changes. I am also attaching a sample of the Power BI report we created to show what could be done once you are more easily tracking this information.

Required Connections
Raiser's Edge NXT

Optional Connection
Power BI

Suggested Skill Level
Beginner for the Flow, Intermediate for Power BI report

Configuration Needs
For the Flow you will need to set up a constituent list in RENXT for current assigned prospects (for us that is Fundraiser Type = Prospect Manager) and select the status or stages you want to track (if this is all of them you do not need to add this filter). Additionally, this Flow adds an action custom field to track the actual stage so you will need to set up the appropriate tables to match your status fields.

If you take on the Power BI report as well, I do not currently use the SKY API connection (just have not had the time to investigate) to create the report, but Excel files from export. I have attached an export control report which will get you the basic time in stage information and if you are interested in the other areas of information (prospect count, active proposals, actions in the past 12 months), please reach out.

MovesManagementTimeinStage_20210930005642.zip
Moves Management Power BI Sample.pdf
Moves Management Control Report.pdf

Jennifer Vincent

@Allison Kiglics - want to try your hand at some Power BI stuff? @Dan Snyder - this is a very intriguing solution to a very common issue. There was nothing that could be done from the actual status itself? It had to be tied to an action?

Dan Snyder

@Jennifer Vincent You can get the start date of the current status via Power Automate, but since we are using exports to Excel as data sources for Power BI and wanted other information from RE this was the best solution. It also allows for a historical picture of their status history by having these actions entered. Happy to chat more if you have additional questions.

Allison Kiglics

Ooooooooh Yes I do! I was playing with the power BI connection a few months ago and then things got busy and I abandoned it. I should definitely try this!

Edward Hohlbein

Howdy all. I just had a request come in that it feels like this process will help with. Any updates or tips on this since last October? THANK YOU!

Nicole Holt

We are just digging into revamping our prospect management process…and this popped up! So great!

Dan Snyder

@Edward Hohlbein Not sure much has changed with this. I have not checked if you can get the days in stage from some of the new connectors to avoid the need for actions, but this report has also not been updated for us so I have not looked either.

Edward Hohlbein

Howdy, @Dan Snyder This flow is working so well. Thank you! Do you have a template of the Power BI report you use? That would be so awesome. ?

Dan Snyder

@Edward Hohlbein I connect to csv documents so it is not as easy as plugging into the Sky API or anything, but I will try to anonymize the documents I use. I will also say that some of these files I use for other reports so there are more columns than you need.

Dan Snyder

@Edward Hohlbein Here are some additional documents that might be of use, anonymized of course and as I mentioned, these exports are used for other reports so not all fields are needed for this particular report. Hope it helps.

Edward Hohlbein

You're the best @Dan Snyder Thank you!

Nicole Holt

Hey, @Dan Snyder, have you used anything similar to calculate time in stage for Opportunity status? I'm wondering if this would be a flow we could adapt…

Dan Snyder

@Nicole Holt I have not, but a quick look at the API endpoints and opportunity status time in stage does not appear to be something that you can get. It also does not appear to be something you can get via a webhook either.

Nicole Holt

@Dan Snyder that should have been my first check. Thank you!

…and bummer. Off to the ideas bank for me!

Potique Johnson

@Dan Snyder
Dan,

I just read your response on Moves Management. Unfortunately, I am not yet familiar with Power BI. Starting to learn it. Can you explain further how you use an ACTION Attribute to set up the “stages” that a prospect is in within their “status”?

I read your post on Moves Management. We are just now utilizing NXT webview to its extent.

Currently, I need to track 4 things for each account in order to ensure that we are moving our donors through the cycle so that we are churning the donors when needed.

Here is what I am currently using:

We have Target Analytics so we just identified our top 150 donors for Tier 1 donors and Tier 2 donors. The difference between the two is that The tier one have a higher gift target range and higher capacity.

I track this with a Custom Rating of TA Tier 1 or TA Tier 2. We then have an internal ranking on those donors that are assigned. That is a A B or a C. This rating is strictly for the Fundraisers to rank their donors within each of their own portfolios.

We also use the Prospect Status under the Prospect Management Tool. Naturally I have: Qualification, Identification, Cultivation, Solicitation/Negotiation, Pledge/Gift, Stewardship, and Eternal Stewardship.

My leadership now wants me to create a procedure for “moves management” within the assigned donors - In other words, if a donor is in Cultivation, where in the “stage” is it in Cultivation. Is it in Stage 1 where we have only had 1 meeting or is it in Stage 3 where we have had 3 or 4 meetings and are close to moving it to Solicitation? Same as with other status' including Stewardship. Did it just go into Stewardship Stage 1 or has it been in Stewardship a longer period- Stage 3?

I would normally look to possibly create a classification of Stage 1 Stage 2 Stage 3 etc. but I wanted to check with others to see what they do? I want to make this as visible to the Fundraisers as possible, but I also need to be able to create a report that reports to leadership exactly what you are currently reporting using Power BI. Any assistance you can provide me would be greatly appreciated.

I probably won't be proficient in Power BI for several months.

Thanks

Potique Johnson (nickname: Tiki) (her, She)

Dan Snyder

@Potique Johnson Tiki, sorry to be so long in responding and let me see if I can make any sense here.

First, you do not need to use Power BI here, you could likely create much of this in Excel so don't worry if that is not something you are familiar with, I just adapted the process someone shared with me into Power BI since our other reporting was there already.

I should also clarify that I used “stage” and “status” interchangeably which is not the case. Time in stage as the post is titled is really time in a status, meaning the prospect status just as you use that field in RE. That being said, the action attribute is just a place holder to repeat the prospect status, but with a date field that can be used to calculate the difference between two dates (largely today and when the status changed). No one really is looking at the action attribute, if they need to see a time in a particular stage they can just look at the built in NXT display within the prospect management tile.

Now, to your question about a “stage” within the prospect status. You are clearly thinking about this correctly in wanting to make sure it is visible and classification could be an option if you are not using it for anything else. The only thing we have used classification for in the past is to differentiate between an annual fund leadership prospect, major gift prospect, principal gift prospect, or planned gift prospect. We don't currently use classification at all, but that is by no means best practice, just where our office is at.

Back to the question, I think it would be possible to calculate this “stage” within a “status”, but I suspect the criteria would be different for each stage which could get pretty complicated to determine and keep updated/accurate. As part of the data export for our Power BI report, we export the number of meetings in the past 12 months, and included that as a column in a larger table I created in my Power BI report. I wonder if that might be a solution and based on those “counts” you could get your “stages”? Happy to continue the conversation or hear what others have to say. - Dan

Dummy data used for sharing publicly

Potique Johnson

@Dan Snyder
Good afternoon Dan. Thank you for your insight and knowledge on this topic. I definitely can use this informaton to our advantage and greatly appreciate you sharing it with me.