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!
