Donor Retention

jas0012qlik — Fri, 04 Aug 2017 15:13:48 GMT

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

Anonymous — Thu, 18 Jan 2018 22:46:21 GMT

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:

LOAD * INLINE [

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:

Noconcatenate Load

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

;

Load

[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:

Mapping Load

[Cash Donor],

FirstYearDonation

Resident Aggr;

//1Y Retention:

[Retention]:

Noconcatenate Load

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

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

;

Load

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]

;

Load

[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];

topic Donor Retention in App Development

Donor Retention

Re: Donor Retention