Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

jas0012qlik
New Contributor II

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.

1 Reply
hassulka
New Contributor III

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:

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

Community Browser