Prospect Status Tracking - Days in Status using Dashboard Builder

Elisabeth Falls

Shoutout to @Joe Moretti here for originally bringing this topic up (re: using dashboard builder to track prospect statuses). I've got a refresh of it if you read on…

Note: This does require Insight Designer, which I believe has an extra cost associated with it

Step 1: Build a custom insight

Filters: Constituent prospect status (I was able to select up to 8 statuses), fundraiser (if you're just looking at one portfolio), constituent IDs = all (if you want to look at all constituents and their prospect statuses)

Metrics: Sum of prospect days in status

Rows: Constituent name, constituent ID

Columns: Constituent Prospect status

Results: A wide table of constituents and their sum of days in prospect status

Step 2: Drop that insight into a dashboard

It doesn't have to look pretty, it just has to be there.

Step 3: Save and close the dashboard, then visit it from the front-end view under "Fundraising dashboards"

Step 4: Export the data using the download button in the top right corner of the insight

Step 5: Work some Excel magic to unpivot the data

At this point, you should have an Excel file (either CSV or XLSX) with some wonky looking rows and columns. Now, we're going to take our wide table and make it narrow.

The Excel magic you want to work is a nested IF formula. For me, it was: =IF(C3<>"",C1,IF(D3<>"",D1… and so on and so forth until you hit the end of where your statuses are. C3 here is where the number of days are, C1 is where the prospect status name lives. Then, I just did a sum of the prospect status days columns.

Copy and paste values only, then delete your extra columns. All you want left are your constituent identification columns plus the two you just created using formulas.

Result: A clear dataset with columns for your constituent identifiers, prospect status, and number of days in status.

Happy to answer any questions about this!

Carlene Johnson

While I'm really hoping that BB will allow for much better reporting, querying, and exporting of this information once they sunset database view, this is a really smart workaround in the meantime. Thanks so much for sharing your efforts with such a great detailed explanation!