Power BI Connector for Retention, Recapture, Acquisition Report

Rebecca Sundquist

Retention_Recapture_methods.zip

More recent post has been added as a template: Sharing my Power BI DAX for Annual Lifecycle and Giving Level Analysis - Blackbaud Community

Original post:

I am trying to replicate RENXT's Retention, Recapture, and Acquisition reports (found under Fundraising > Reports) in Power BI using only the Power BI Connector. I hope that by cracking the code behind these reports, we can leverage the DAX for additional year over year and year-to-date comparisons.

I am attaching my current draft, but it still has some holes! Do you have suggestions to share with me and the community?

Here are some key insights:

1. A calculated table is calculated when the report opens and does not respond to slicers.
2. Measures respond to the slicer.

I chose to use a calculated table to classify constituents by lifecyle because the retention rate in my RENXT demo environment seemed to suggest that all gift types on hard credits are included when identifying who is available to retain. I also tried using a measure, but when my retention universe shifted based on gift types for received vs committed revenue, the measures do not tie out to the donor and revenue rates in RENXT's Retention or Recapture reports. The Recapture Available value reported in RENXT does not change with the received/committed filter. However, this could be due to my limited demo data. I thought I had Revenue and Recapture spot-on, but I am off by one count on the Available for Recapture measure. Not sure why.

For the Acquisition Rates (donor and revenue), RENXT documentation says the denominator is the past five years of distinct donors and revenue. In the attached workbook, you'll find I'm playing with filters in the CALCULATE() function to identify the right gifts. I think that the denominator is supposed to be filtered by the Received/Committed slicer, but I am having trouble combining that with the five-year range from the reported fiscal year. I think my calculation is missing gifts from the prior five years when the gift type doesn't also show up in the reported fiscal year. See DAX in the attached workbook.

TIP - If you have the Power BI connector set-up, you should be able to refresh this workbook with data from your own environment just by hitting the “Refresh” button.

Alex Wong

@Rebecca Sundquist
Hi, first thing first.. you probably want to take the attachment down… it has your data in it that you probably do not want to put on this community post. Instead export as power bi template.

question 2 is.. do you know Blackbaud way of crediting constituent and how it is used in these reports? looking at your tables, it does not look like you are considering soft credit. In summary of giving, Blackbaud do take soft credit into consideration, but I am not sure about the retention/recapture reports. So unless your org does not use soft credit at all, that may be where your discrepancy is

Rebecca Sundquist

@Alex Wong, thanks for looking out for me! This is demo data, so I am happy to share it with folks who might not have the Power BI connector ready to run, but are interested in seeing the DAX in this workbook. Good point that we wouldn't want customers to share .pbix files with their own data! Saving as a .pbit file removes the data, but allows others to use their own Power BI Connector to populate the workbook.

Secondly, the RENXT documentation, Donor Retention Analysis (blackbaud.com), says that the Donor Retention report excludes soft credits:

“Under Year-to-date retention, you can quickly analyze constituents who gave gifts of any type — excluding soft credits — during both the current and previous fiscal years — and their giving — compared to the previous fiscal year.”

One reason I want to “crack the code” behind this report is so that we have the freedom to re-write it in Power BI to include soft credits, if desired.

Rebecca Sundquist

I've answered some of my questions by learning more about how the RENXT reports work.

I found in RENXT the Donor Counts do not change with the filter on Received vs. Committed gift types. Only the revenue metrics respond to those filters in the RENXT Retention and Recapture reports. I had set up Power BI to apply that filter to both types of measures. Also, I had been excluding $0 transactions from Donor Summary lifecycle classifications, but it looks like these records might count when RENXT assigns lifecycles.

Making progress! But is this calculated table method a good one?

Alex Wong

@Rebecca Sundquist
I think you will do fine with Calculated Columns as I have used for it for while before moving on to use measures.

The difference with Calculated Columns (CC) vs Measure (M) is something I did not fully test out, though I currently do not have the time to revisit this at the moment. CC will do what you want to achieve though will take up more “storage”, while M will take up more “memory" is the more “official” difference out there, also CC can be used as filter vs M cannot. There are those that swears on M being better choice, and I can see why after starting to use M.

As you are trying to mimic what RE NXT define as various donor lifecycle, it is hard to know if your BI and it's DAX is doing it right or not, as I don't know what the official definition is. Which one are you still trying to validate and would like to know if DAX is doing it?