Lost donors calculations

DAX DAX Calculations
1

Hi @Melissa
As per Donor loyalty calculation - #14 by BrianJ - Donor loyalty calculation, we need to take this a step further.

We need to identify who were the donors lost in a time period. In this case, we have the loyal donors for 2017, 2018, 2019, and we can tell that the number of loyal donors dropped in 2020. In many cases they have just been too busy to remember and we would like to identify those individuals who have been loyal, but have not made a donation in the last year - in this case 2020

Attached is the donor calculation report we have used thus far. All data is anonymised and names have been randomised.

The sample dataset contains 50,000 rows, but it will need to work in the several million zone.

Thanks
Donor loyalty.pbix (466.5 KB)

2

Hi @Synergetic,

Didn’t have any data for this file so I worked of the previous one…

In the Transformed step I’ve added some additional logic to create a “Reminder” flag: so if last consecutive number of years >=3 AND the max year = this year-1 then it returns true else false.

let
    Source = Excel.Workbook(File.Contents(FileLocation), null, true),
    AllRecordedDonationsTable_Sheet = Source{[Item="AllRecordedDonationsTable",Kind="Sheet"]}[Data],
    PromoteHeaders = Table.PromoteHeaders(AllRecordedDonationsTable_Sheet, [PromoteAllScalars=true]),
    ChangeType = Table.TransformColumnTypes(PromoteHeaders,{{"Donor ID", Int64.Type}, {"Receipt Amount", Int64.Type}, {"Receipt Date", type datetime}, {"Constuency Codes", type text}, {"Column5", type text}, {"Column6", type text}}),
    ExtractYear = Table.TransformColumns(ChangeType,{{"Receipt Date", Date.Year, Int64.Type}}),
    RenameColumn = Table.Buffer( Table.RenameColumns(ExtractYear,{{"Receipt Date", "Receipt Year"}})),
    GroupRows = Table.Group(RenameColumn, {"Donor ID"}, {{"AllRows", each _, type table [Donor ID=nullable number, Receipt Amount=nullable number, Receipt Year=number, Constituency Codes=text]}}),
    Transformed = Table.AddColumn(GroupRows, "Temp", each
        let
            myTable = [AllRows],
            cYear = Date.Year( DateTime.FixedLocalNow()),
            GroupRows = Table.Group(myTable, {"Donor ID", "Receipt Year"}, {}),
            SortRows = Table.Sort(GroupRows,{{"Receipt Year", Order.Ascending}}),
            AddIndex = Table.Buffer( Table.AddIndexColumn(SortRows, "Index", 0, 1, Int64.Type)),
            AddCustom = Table.AddColumn(AddIndex, "Custom", each 
                try if [Index] <> List.Max(AddIndex[Index]) 
                    then [Receipt Year] = AddIndex[Receipt Year]{[Index]+1}-1 
                    else [Receipt Year] = AddIndex[Receipt Year]{[Index]-1}+1 
                otherwise false),
            NewGroup = Table.Group(AddCustom, {"Donor ID", "Custom"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"AllRows", each _, type table [Donor ID=nullable number, Receipt Year=number, Index=number, Custom=logical]}}, GroupKind.Local),
            RemindTest = Table.AddColumn(NewGroup, "RemindTest", each let LastDonation = if [Custom] = true then List.Max( List.Buffer([AllRows][Receipt Year] )) else null in if [Custom] = true and LastDonation = cYear-1 and [Count] >= 3 then true else false, type logical),
            RemindResult = Table.AddColumn(RemindTest, "Remind", each List.AnyTrue(RemindTest[RemindTest]), type logical ),
            ConsecutiveYears = Table.AddColumn(RemindResult, "Consecutive Years", each if [Custom] = true then [Count] else 1, Int64.Type)
        in
            ConsecutiveYears, type table [Donor ID=Int64.Type, Custom=logical, Count=Int64.Type, AllRows=table, RemindTest=logical, Remind=logical, Consecutive Years=Int64.Type])[[Temp]],
    ExpandTable = Table.ExpandTableColumn(Transformed, "Temp", {"Donor ID", "Consecutive Years", "Remind"}, {"Donor ID", "Consecutive Years", "Remind"}),
    MaxConsecutiveYears = Table.Group(ExpandTable, {"Donor ID", "Remind"}, {{"Max Consecutive Years", each List.Max([Consecutive Years]), type nullable number}})
in
    MaxConsecutiveYears

.
I hope performance wise it’s still okay… let me know.

3

Thanks @Melissa
Sorry about the lack of data.
I take it that the remind column is for those donors who have not made a donation this year.
I have added that in and will see how it performs in production today.
Thanks

1 Like
4

Hi @Melissa
Thanks for this. As my boss said, “Analysis porn”.

However, they would like a change, and that is the ability to set the date that we are looking at. So we can see who are the lost donors as at 31/12/2019 vs 31/12/2018

They also want to see just those who donated last year and not this year

5

Hi @Synergetic,

.

:thinking: that is an entirely different matter…

Once again I’ll request you to create a new thread for that one but to first determine the scope with your stakeholders, so their is a mutual understanding of the requirement before you post. While you do that, might I suggest to include user stories from how to interact, to how to present the data - that might bring other elements to light you will have to cover.

It does again sound like a DAX solution is required but setting up the data for that analysis remains key. I remember you already had a new vs. lost customer DAX solution but felt that wasn’t working for you. Can you elaborate more on that?

Feel free to @mention me in your new thread because I do find this interesting.

All the best.

6

I can create a new request. The thing is everytime we find something, they think of a new question because the data highlights the need.