Advanced donor loyalty

Hi @Melissa
Thanks for your interest in this.
Couple of definitions.

  • Donor: One who has donated in a calendar year
  • Loyal donor: One who has donated for the last 3 consecutive years. (also need to know those who have donated for the last 5 consecutive and 10 consecutive years.
  • Lost donor: One who donated last year but not this year
  • New donor: one who has not donated before
  • Returned donor: one who has donated before, but not last year, and has donated this year.
  • Staff donor: a donor who has in the list of constituency code the code *STF

We need to understand the numbers for each of these donors for any given year.

Not only do we need aggregated information, but also need to be able to individually identify members of the categories.

For example, if we select 2019, then we want the information as at 2019. So we will want to know those who donated in 2018, but not in 2019. Those that never donated before 2019. Those that donated previously, but not in 2018, but again in 2019.

I have attached the sample data set containing 50K records. Production has several million records.

Donor loyalty.pbix (861.9 KB)

Hi @Synergetic, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.

  • When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.
    image

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.

Hi @Synergetic,

Thanks. I’m swamped during the today but will have a look tonight.

Meanwhile could you check the attached PBIX?
I’m getting the “There are pending changes that haven’t been applied” warning.

Donor loyalty.pbix (853.8 KB)
Hi @Melissa
Thanks for looking at this. I have uploaded the dashboard again without the error message
Regards

Hi @Synergetic,

Just a quick note, haven’t forgotten about this, but need a bit more time. Will get back to you as soon as I can.

1 Like

Hi @Synergetic,

Can you see if this if this meets your requirement and how this performs?
There are however some things to consider/choices to make, for example at the moment:

  • nYrs creates a table with all years in the date table, this might need to be restricted.
  • yTable creates a table from 0-10 to help determine the number of consecutive years
  • dTable creates a table with Donor ID’s, which now is limited to former/current staff

This is all brought together and in the end filtered down to only combinations, where the distinct count of receipt years equals the value from the number series in the yTable to help determine the max number of consecutive years.
.

Loyal Donors = 
VAR nYrs = CALCULATETABLE( ALLNOBLANKROW( Dates[Year Offset] ))
VAR yTable = GENERATESERIES( 0, 10, 1)
VAR dTable = 
    CALCULATETABLE( VALUES( Donors[Donor ID] ),
        Donors[Donor ID] IN VALUES('Former And Current Staff'[ID])
    )
VAR nTable =
FILTER(
    ADDCOLUMNS(
        ADDCOLUMNS(
            GENERATE( GENERATE( dTable, nYrs ), yTable ),
            "@Offset", [Year Offset]
        ), "@Donor", 
            CALCULATE( DISTINCTCOUNT( 'All Recorded Donations'[Receipt Year] ), 
                FILTER( ALL( Dates ), Dates[Year Offset] >= [@Offset] -[Value] && Dates[Year Offset] < [@Offset]+1 )
        )
    ),  [Value] = [@Donor] &&
        [Donor ID] IN VALUES( Donors[Donor ID] ) &&
        [@Offset] IN VALUES( Dates[Year Offset] )
)
VAR myTable = 
    TOPN( 1, 
        ADDCOLUMNS(
            nTable,
            "@Rank", RANKX( FILTER( nTable, [@Offset] = EARLIER( [@Offset] )), [Value], , ASC, Dense)
        ), [@Rank], ASC
    )
VAR Result = MAXX( myTable, [@Donor] )

RETURN Result 

.
image

Also created a What if - parameter called: Consecutive Num of Years comprised of a list {3, 5, 10} that can be used to identify/count the number of donors that meet that number when place in a table or Slicer.

Loyal Donors N consecutive years = 
VAR nYrs = CALCULATETABLE( ALLNOBLANKROW( Dates[Year Offset] ))
VAR yTable = GENERATESERIES( 0, 10, 1)
VAR dTable = 
    CALCULATETABLE( VALUES( Donors[Donor ID] ),
        Donors[Donor ID] IN VALUES('Former And Current Staff'[ID])
    )
VAR nTable =
FILTER(
    ADDCOLUMNS(
        ADDCOLUMNS(
            GENERATE( GENERATE( dTable, nYrs ), yTable ),
            "@Offset", [Year Offset]
        ), "@Donor", 
            CALCULATE( DISTINCTCOUNT( 'All Recorded Donations'[Receipt Year] ), 
                FILTER( ALL( Dates ), Dates[Year Offset] >= [@Offset] -[Value] && Dates[Year Offset] < [@Offset]+1 )
        )
    ),  [Value] = [@Donor] &&
        [@Donor] IN VALUES( 'Consecutive Num of Years'[Consecutive Years] ) &&
        [Donor ID] IN VALUES( Donors[Donor ID] ) &&
        [@Offset] IN VALUES( Dates[Year Offset] )
)
VAR myTable = 
    TOPN( 1, 
        ADDCOLUMNS(
            nTable,
            "@Rank", RANKX( FILTER( nTable, [@Offset] = EARLIER( [@Offset] )), [Value], , ASC, Dense)
        ), [@Rank], ASC
    )
VAR Result = COUNTROWS( myTable )

RETURN Result

.
image

One thing I haven’t been able to fix is the flag measure… but I expect that will produce the correct result once you’ve updated the Dates table to include all dates in 2020 (now partialy covered).

Flag Donors = 
VAR dTable = 
    EXCEPT(
        CALCULATETABLE( VALUES( 'All Recorded Donations'[Donor ID]  ), 'All Recorded Donations'[Donor ID] IN VALUES( 'Former And Current Staff'[ID] ), DATEADD( Dates[Date], -1, YEAR )),
        CALCULATETABLE( VALUES( 'All Recorded Donations'[Donor ID]  ), 'All Recorded Donations'[Donor ID] IN VALUES( 'Former And Current Staff'[ID] ))
    )
VAR vTable =
    FILTER(
        ADDCOLUMNS( dTable,
            "@pYr", [Loyal Donors LY]
        ),
        [@pYr] >= 3 &&
        [Donor ID] IN VALUES( Donors[Donor ID] )
    )
RETURN

    COUNTROWS( vTable )

.
image

.
Here’s your sample file eDNA - Donor loyalty.pbix (813.7 KB)
I hope this is helpful.

2 Likes

Hi @Synergetic, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

Hi @Melissa
Thanks for your work on this. I have not had a chance to review it yet but will do so as soon as I can scratch. On the surface it looks like it could work