1 Reply Latest reply: Jan 18, 2018 5:46 PM by Kristina Hasulova

# Donor Retention

We are looking for a way to do donor retention, as well and acquisition of new donors. Has anyone done anything like this in Qlik Sense? Initially we want to try 3 and 5 years rates.

• ###### Re: Donor Retention

Hi,

I was just dealing with a similar case. What exactly is it you need to calculate / what is the methodology ?

Try this script to calculate Retention amount for year 1  and 2. it may help.

OriginalData:

Year, Cash Donor, Amount

2009, 1, 302

2009, 1, 300

2010, 1, 353

2011, 1, 350

2012, 1, 358

2010, 2, 304

2012, 2, 305

2013, 2, 11

2016, 2, 30

2017, 2, 15

2017, 2, 20

2013, 3 ,45

2014,3,55

2015,3,65

];

//Calculate retention:

Aggr:

*,

[# Years Donated] / [# Years Total] as [Years Retention %]

;

[Cash Donor],

Min(Year) as FirstYearDonation,

Count(Distinct Year) as [# Years Donated],

Year(Today()) - Min(Year) as [# Years Total]

Resident OriginalData

Group by [Cash Donor];

Map:

[Cash Donor],

FirstYearDonation

Resident Aggr;

//1Y Retention:

[Retention]:

*,

If([@1Y Retention]=1,'1Y Retention',

If([@2Y Retention]=1,'2Y Retention', Null())) as Retention //to be used as a Dimension

;

Year,

[Cash Donor],

If(Year=FirstYearDonation+1,1,0) as [@1Y Retention], //Flag to be used in set analysis

If(Year=FirstYearDonation+2,1,0) as [@2Y Retention],

If(Year=FirstYearDonation+3,1,0) as [@3Y Retention]

;

[Cash Donor],

Year,

ApplyMap('Map',[Cash Donor],Null()) as FirstYearDonation,

Sum(Amount) as AmountAggr

Resident OriginalData

Group by [Cash Donor],Year

Order by [Cash Donor],Year;

Left Join(OriginalData) Load * Resident Retention;

Drop table [Retention];