Question:

Calculating donor attrition rate

by  |  earlier

0 LIKES UnLike

 Tags:

   Report

2 ANSWERS


  1.  DO YOU NEED A LOAN TO START UP OR EXPAND YOUR BUSINESS? Zion financial services is a sincere and certified private Loan company  approved by the Canada Government, we give out international and local loans to all countries in the world, Amount given out $2,500 to $10 Million. We offer loans with a dependable guarantee to all of our clients. Our loan interest rates are very low and affordable with a negotiable duration.


    Available now are

    MORTGAGE LOANS, PERSONAL, START-UP & EXPANSION OF BUSINESS LOANS AND NEW ARE UNSECURED & SECURE LOANS, CONSOLIDATE LOANS


    Apply for a loan today with your loan amount and duration, Its Easy and fast to get 3% interest rates and monthly installment payments. email us now to apply: zionloanfirm.ltd@aol.com


    TO APPLY TO APPLY:

    Regards

    Peter James

    General Manager

    zion capital investment, Canada

    Email: zionloanfirm.ltd@aol.com


  2. Attrition rate in period i = (donors who gave in period i and not i+1)/(donors who gave in period i)... but you will need some substantial querying to get these numbers.


    Our organization uses an Access database (IM is based on Access) and use 2 different method for calculating attritition. For both, you will need to be reasonably well-versed with Access/SQL/query building.


    First Method:


    First, I stup queries of all donations in Year1, Year2, and Year3. I use the SQL "GROUP BY" clause to ensure that donors who made repeat donations are not double counted in that year. Then I include a calculation- even though I don't need it, such as Sum of donation amounts. I include a where clause to filter the query for year i (where i is either year1, year2, or year3).


    In our case, I've setup a seperate database for reporting- donors and donations are included in the same table (ie: donor information is repeated throughout the table - this is OK since we use it exclusively for analysis). The SQL code to build the first query (in our case) is:




    Our organization uses an Access database (IM is based on Access) and use 2 different method for calculating attritition. For both, you will need to be reasonably well-versed with Access/SQL/query building.


    In the first, I setup queries of all donations in Year1, Year2, and Year3. I use the SQL "GROUP BY" clause to ensure that donors who made repeat donations are not double counted in that year. Then I include a calculation- even though I don't need it, such as Sum of donation amounts. I include a where clause to filter the query for year i (where i is either year1, year2, or year3). In our case, I've setup a seperate database for reporting- donors and donations are included in the same table (ie: donor information is repeated throughout the table




    SELECT dw.DonorId, Sum(dw.Received) AS SumOfReceived

    FROM dw

    WHERE (((dw.PaymentDate) Like '*2007'))

    GROUP BY dw.DonorId;




    I repeat this query for 3 different years.


    Next, I build a query based on these 3 queries. Instead of selecting tables such as donors or donations, I choose the 3 queries I setup. I link them all using the donor's ID number and specify that Access should select all records from year 2, and only records from year 1 that match (and likewise, select all records from year 2 and only records from year 3 that match). I like to setup an expression that returns "NEW" if the donor did not give in year 1, and "Attrited" if the donor did not give in year 3- although this may not be totally necessary. In our case, this query would look like:


    SELECT [Level A: 2006 donations].DonorId AS 06id, [Level A: 2007 donations].DonorId, [Level A: 2008 donations].DonorId AS 08id, IIf(IsNull([06id]),"New") AS 07new, IIf(IsNull([08id]),"Attrited") AS 07attrite, 2007 AS [Year]

    FROM ([Level A: 2006 donations] RIGHT JOIN [Level A: 2007 donations] ON [Level A: 2006 donations].DonorId=[Level A: 2007 donations].DonorId) LEFT JOIN [Level A: 2008 donations] ON [Level A: 2007 donations].DonorId=[Level A: 2008 donations].DonorId;




    The last query I do is also based on a query (the query I just described) and counts the number of instances of "NEW", "ATTRITED", and the number of rows in the previous query. I "GROUP BY" year only because I have to (SQL queries that include aggregates, such as counts, must be grouped by something). In our case, it looks like this:


    SELECT [Level A: 07 new and attrited].Year AS Expr1, Count([Level A: 07 new and attrited].[07new]) AS CountOf07new, Count([Level A: 07 new and attrited].[07attrite]) AS CountOf07attrite, Count([Level A: 07 new and attrited].DonorId) AS CountOfDonorId

    FROM [Level A: 07 new and attrited]

    GROUP BY [Level A: 07 new and attrited].Year;




    The output of this last query is based on the second query I described, which is based on the first query I described. I end up with three numbers: total number of donors, number of new donors, and number of attrited donors. Attrition rate is just attrited donors/total donors.


    I like this method for forcasting and projections, since I get a sense of who will donate next year.


    Other method:


    If you want to expand the time periods your including (ie: if your definition of attrition means not giving in the next 5 years, as opposed to the next year as I did) then the three queries you start with should not be year1 donations, year2 donations and year3 donations... rather is should be donations before year 2, donations at year 2, and donations after year 3.


    Read up on subqueries and aggregate queries for Microsoft Access if this is unclear- it does require some fairly substantial data management skills.


     

Question Stats

Latest activity: 4 years, 5 month(s) ago.
This question has 2 answers.

BECOME A GUIDE

Share your knowledge and help people by answering questions.