1 Reply Latest reply: Jan 18, 2018 5:46 PM by KRISTINA HASULOVA RSS

    Donor Retention

    Jana Ingram

      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
          KRISTINA HASULOVA

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